Moving JSON Time Series to Columns Without Changing the Dashboard

A system I built to collect readings from factory equipment grew much faster than I had estimated. When I designed it, I stored each entire frame as a JSON array in a single column. My reasoning at the time was that I did not yet know what each register meant, so keeping everything seemed better than regretting it later. Two weeks later, the disk started complaining. I sat down to count and realized I was storing one row per second, with seventy-four values per row, but the web interface used only seven of them. I had to rebuild the schema across the system while the dashboard and API were in constant use. This article describes how I migrated it without requiring callers to change a single line.

The table names, column names, and IP addresses in this article are all fictional. The measured sizes and timings are real results from the work.

Symptoms: the disk grew by 50 MB a day without anyone noticing

The original structure looked like this. It was very simple: whatever I read, I put the whole thing in.

-- schema เดิม: เก็บทั้งเฟรมเป็น JSON array 74 ค่า ทุก 1 วินาที
CREATE TABLE raw_dump (
  ts_unix   BIGINT       NOT NULL,
  slave     TINYINT      NOT NULL,
  regs      JSON         NOT NULL,   -- [0,0,152,...] ยาว 74 ช่อง
  frames    TEXT         NULL,       -- hex ดิบของ response ไว้ debug
  PRIMARY KEY (ts_unix, slave)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

I could check the growth directly through information_schema without running du on the datadir.

-- ดูขนาดจริงรายตาราง ก่อนตัดสินใจอะไร
SELECT TABLE_NAME,
       ROUND((DATA_LENGTH + INDEX_LENGTH)/1024/1024) AS mb,
       TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'edge_db'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

The figures at the time were 183 MB on the endpoint machine in the factory and 625 MB on the server receiving the pushed data. Both held only 14 days of history. The difference came from the server storing both the original JSON and another set of decoded columns: it was unintentionally storing the same data twice.

The bigger problem was that JSON_EXTRACT could not use an index

I could live with the size; buying more disk space would solve that. What I could not live with was query speed. The monthly summary query looked like this.

-- query เดิม: ทุกแถวต้องถูกอ่านขึ้นมา parse JSON ก่อนถึงจะรู้ว่าเอาหรือไม่เอา
SELECT FLOOR((ts_unix + 25200)/86400) AS d,
       AVG(JSON_EXTRACT(regs, '$[8]')) AS comp_amp
FROM raw_dump
WHERE slave = 20
  AND ts_unix BETWEEN ? AND ?
GROUP BY d;

With a recent enough MariaDB version, I could create generated columns and index them. But when I listed how many generated columns I would need to cover everything in use, the answer was seven. Each one would take up more space, too. So why keep the JSON in the first place if I had to split it into columns anyway?

Before making changes: which registers contain actual data?

Before deciding what to keep and what to discard, I counted the distinct values in each slot. A slot that held just one value all month was an inactive slot with nothing wired to it.

-- นับ distinct รายช่อง: ช่องที่ distinct = 1 คือช่องตาย ตัดทิ้งได้เลย
SELECT 8  AS reg, COUNT(DISTINCT JSON_EXTRACT(regs,'$[8]'))  AS n FROM raw_dump WHERE slave=20
UNION ALL SELECT 9,  COUNT(DISTINCT JSON_EXTRACT(regs,'$[9]'))  FROM raw_dump WHERE slave=20
UNION ALL SELECT 10, COUNT(DISTINCT JSON_EXTRACT(regs,'$[10]')) FROM raw_dump WHERE slave=20
UNION ALL SELECT 11, COUNT(DISTINCT JSON_EXTRACT(regs,'$[11]')) FROM raw_dump WHERE slave=20
UNION ALL SELECT 24, COUNT(DISTINCT JSON_EXTRACT(regs,'$[24]')) FROM raw_dump WHERE slave=20;

From 57,000 samples, I had a clear answer: only five slots changed. The rest stayed completely still all month. Adding two more slots from another temperature sensor brought the total to seven values I needed to keep collecting. As for the frames column, which held raw hex for debugging, I had last opened it when I finished writing the decoder. That was six months earlier. I could drop it without concern.

A key decision: store raw values, not converted values

I spent a long time thinking about this. The readings from the devices were integers that needed to be divided by a scale factor to get actual units. Current, for example, needed to be divided by a hundred, and temperature by ten. The question was whether to store the raw numbers or the values after division.

The rule I have followed since then is that the storage layer remembers; it does not interpret. Interpretation belongs in a layer where changes can apply to historical data: the API, not the table. This really helped later when I discovered that a register I had labeled as condenser coil temperature was actually something else. I only had to fix the conversion layer, without touching the historical data.

The new schema and a migration without downtime

-- schema ใหม่: เก็บเฉพาะ register ที่ใช้จริง เป็นค่าดิบ คงสเกลเดิม
CREATE TABLE raw_dump__new (
  ts_unix BIGINT   NOT NULL,
  slave   TINYINT  NOT NULL,
  comp    INT      NULL,   -- reg 8  กระแสคอมเพรสเซอร์ ดิบ (÷100 ตอนแสดงผล)
  cond    INT      NULL,   -- reg 9
  evap    INT      NULL,   -- reg 10
  heat    INT      NULL,   -- reg 11
  freq    INT      NULL,   -- reg 24
  room    SMALLINT NULL,   -- อุณหภูมิห้อง จากอุปกรณ์อีกตัว (÷10)
  pipe    SMALLINT NULL,
  PRIMARY KEY (ts_unix, slave),
  KEY rd_slave_ts (slave, ts_unix)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

The PK was (ts_unix, slave) from the start, which helped a lot. When a second device joined the same bus, the table could support it immediately without any changes. The second index, (slave, ts_unix), serves queries that filter by slave first and then scan a time range, which is the pattern the web interface uses most often.

I migrated the historical data in time-based batches rather than all at once, because writes were still coming into the table every second.

-- backfill ทีละวัน ไม่ล็อกยาว ; รันวนจากสคริปต์ข้างนอก
INSERT IGNORE INTO raw_dump__new (ts_unix, slave, comp, cond, evap, heat, freq)
SELECT ts_unix, slave,
       JSON_EXTRACT(regs,'$[8]'),  JSON_EXTRACT(regs,'$[9]'),
       JSON_EXTRACT(regs,'$[10]'), JSON_EXTRACT(regs,'$[11]'),
       JSON_EXTRACT(regs,'$[24]')
FROM raw_dump
WHERE slave = 20 AND ts_unix >= ? AND ts_unix < ?;

# วนย้อนหลัง 14 วัน วันละก้อน แล้วหน่วงให้ดิสก์ได้หายใจ
for d in $(seq 14 -1 0); do
  FROM=$(date -d "-$d day 00:00" +%s); TO=$(date -d "-$((d-1)) day 00:00" +%s)
  mariadb edge_db -e "SET @f=$FROM; SET @t=$TO; SOURCE /opt/app/ops/backfill.sql;" || exit 1
  echo "$(date '+%F %T') backfill OK $(date -d @$FROM +%F)"
  sleep 5
done

-- สลับแบบ atomic คำสั่งเดียว ไม่มีช่วงที่ไม่มีตารางชื่อ raw_dump
RENAME TABLE raw_dump TO raw_dump__old, raw_dump__new TO raw_dump;

The core of the job: keep the dashboard unaware of the schema change

The existing web interface read values through L.regs[8] and L.regs[9] in dozens of places, including cards, charts, and the ticker at the top. Changing every one of those references to the new column names would create unnecessary opportunities for mistakes. I chose the opposite approach: have the API rebuild the original array in sparse form, populating only the indexes with actual values and leaving the rest as null.

// API ประกอบ regs[] กลับคืนแบบ sparse ให้ client เดิมใช้ต่อได้ไม่ต้องแก้
function regsFromColumns(object $r): array
{
    $map  = [8 => 'comp', 9 => 'cond', 10 => 'evap', 11 => 'heat', 24 => 'freq'];
    $regs = array_fill(0, 25, null);
    foreach ($map as $i => $col) {
        $regs[$i] = isset($r->$col) ? (int) $r->$col : null;
    }
    return $regs;
}

This let me migrate the schema by deploying only the backend, then gradually update the web interface later when I had time, without a deadline forcing the work. I consider this the biggest benefit of having an API layer in between. If the web interface had talked directly to the database, I would have had to change both sides in the same minute, which is exactly the kind of job where mistakes are easiest to make.

The senders need changes too, and the receiver must accept the old format

The reader on the endpoint machine and the push process sending data to the server also needed to change their payload format. The trap here was that both sides would not deploy at the same time. The receiver needed to understand both the new and old formats for a while.

# ตัวอ่าน: ส่งค่าดิบเป็นฟิลด์ แทนที่จะส่ง regs ทั้งก้อน
REG_MAP = {"comp": 8, "cond": 9, "evap": 10, "heat": 11, "freq": 24}

def row_from_frame(ts: int, slave: int, regs: list[int]) -> dict:
    row = {"ts_unix": ts, "slave": slave}
    for col, idx in REG_MAP.items():
        row[col] = regs[idx] if idx < len(regs) else None
    return row

// ตัวรับฝั่งเซิร์ฟเวอร์: รองรับทั้ง payload ใหม่และ payload เก่าที่ยังส่ง regs มา
$cols = ['comp' => 8, 'cond' => 9, 'evap' => 10, 'heat' => 11, 'freq' => 24];
$row  = ['ts_unix' => (int) $in['ts_unix'], 'slave' => (int) $in['slave']];

foreach ($cols as $col => $idx) {
    if (array_key_exists($col, $in)) {
        $row[$col] = is_numeric($in[$col]) ? (int) $in[$col] : null;   // payload ใหม่
    } elseif (isset($in['regs'][$idx])) {
        $row[$col] = (int) $in['regs'][$idx];                          // legacy
    } else {
        $row[$col] = null;
    }
}

# ลิสต์ผู้บริโภคของ schema ก่อนแตะอะไร — ห้ามข้ามขั้นนี้
grep -rn --include='*.py' --include='*.php' --include='*.js' --include='*.sh' \
     -e 'regs' -e 'JSON_EXTRACT' -e 'raw_dump' /opt/app | sort -u

Measured results and the cache layer needed alongside them

I need to be clear that the reduction from 5 seconds to 0.7 seconds did not come from the schema change alone. It also came from adding a daily cache table: I calculated daily totals in advance and had the monthly page read that table instead of recalculating from raw rows every time. The schema change made the daily aggregation much faster and made the summary SQL straightforward to write without JSON_EXTRACT.

-- ตาราง cache รายวัน: สรุปไว้ล่วงหน้า หน้ารายเดือนอ่านจากตรงนี้
CREATE TABLE energy_day (
  d_epoch   INT     NOT NULL,  -- day-epoch ตามเวลาไทย
  slave     TINYINT NOT NULL,
  kwh_peak  DECIMAL(12,4) NOT NULL DEFAULT 0,
  kwh_off   DECIMAL(12,4) NOT NULL DEFAULT 0,
  PRIMARY KEY (d_epoch, slave)
) ENGINE=InnoDB;

For retention, I decided that keeping three days of raw rows was enough. Before deleting them, I always had to finish backfilling the cache table. That order must not be reversed.

# retention: backfill cache ให้ครบ แล้วค่อยลบแถวดิบ — ลำดับสำคัญมาก
curl -fsS "http://127.0.0.1/internal/api?q=energy&days=7" >/dev/null || exit 1
mariadb edge_db -e "DELETE FROM raw_dump WHERE ts_unix < UNIX_TIMESTAMP() - 3*86400;"
mariadb edge_db -e "SELECT COUNT(*) FROM raw_dump;"

A short procedure for following along

1. Measure the actual size through information_schema first. Do not guess whether a table is large based on how it feels.
2. Count distinct values in each slot to find actual data and inactive slots that never change.
3. Make a clear decision about storing raw or converted values. I recommend raw values, with interpretation moved to the API.
4. Use grep to find every schema consumer across the repo, including cron scripts and rarely used debugging tools.
5. Create the new table, backfill one day at a time, then use RENAME for an atomic switch. Keep the old table for another three days.
6. Have the API rebuild the original format for the client so the backend can migrate without changing the web interface at the same time.
7. Add a cache for summaries alongside the migration, then set retention to delete raw rows, always backfilling before deletion.

The point I want to leave you with is step three. Keeping everything at the start of a project is a sound decision, because at that point I do not know anything yet. The mistake is continuing to keep everything after I know what I actually use. My own rule is to review the system after its first full month of data collection and see how far apart the data I store and the data I use really are. If you run a system like this and have a better approach, I would like to hear about it. I am also curious whether anyone has found a dedicated time-series database more worthwhile. If I have left gaps in the explanation or made any typos, I will come back and fix them later.