Migrating from JSON to SQLite for OHLC Storage — GFIL Engineering

GFIL Engineering Notes — Vol. 1

Migrating from JSON to SQLite for OHLC Storage

The Problem

GFIL Terminal displays OHLC candlestick charts across 7 timeframes (1m, 5m, 15m, 1h, 4h, 1d, 1w) for 30+ instruments. Behind every chart is historical price data that needs to be:

• Queried fast enough for sub-50ms page loads
• Updated every minute with new ticks from MT5
• Rolled up into higher timeframes on the fly
• Retained for 3 months, then pruned

Our first implementation stored each instrument-timeframe combination as a separate JSON file:

data/klines/
  XAUUSD_1m.json    (2.1 MB, 28,000 rows)
  XAUUSD_5m.json    (0.5 MB, 5,600 rows)
  XAUUSD_1h.json    (0.1 MB, 720 rows)
  EURUSD_1m.json    (1.8 MB, 24,000 rows)
  ...
  // 30 instruments x 7 timeframes = 210 files

This worked fine at launch. But by week three, the cracks started showing.

Three Failure Modes

1. Write Contention

When MT5 pushed a new tick, a Python worker opened the JSON file, parsed all 28,000 records, appended one, serialized the entire array back to disk, and closed the file. During volatile markets with 6+ ticks per second across 10 active instruments, the filesystem I/O became the bottleneck. The Flask request thread would block waiting for the file lock, causing API response times to spike from 50ms to 2+ seconds.

2. Atomicity Failures

A JSON file write is not atomic. If the server crashed mid-write (which happened twice during a power fluctuation on the cloud instance), the file ended up truncated — losing all data since the last backup. We had to replay from MT5 history to recover, which took 45+ minutes per instrument.

3. Query Performance Collapse

Building a 1-hour candle from 60 one-minute candles meant parsing 60 JSON files, merging them in memory, and aggregating. For a chart showing 100 hourly candles across 6,000 minutes of data, the frontend waited 850ms on average. For context, Google's Core Web Vitals flags anything above 100ms.

What We Considered

SQLite won on three counts: it's embedded (no separate process), it's ACID-compliant (no more data loss), and it uses 600KB of memory in WAL mode — 0.06% of our 1GB server.

The Migration

Schema Design

The key insight was separating raw ticks from aggregated candles. We store only the 1-minute candles as source data and compute all higher timeframes via SQL aggregation:

CREATE TABLE kline_1m (
    symbol    TEXT NOT NULL,    -- XAUUSD, EURUSD
    ts        INTEGER NOT NULL, -- Unix timestamp of candle open
    open      REAL NOT NULL,
    high      REAL NOT NULL,
    low       REAL NOT NULL,
    close     REAL NOT NULL,
    volume    INTEGER DEFAULT 0,
    PRIMARY KEY (symbol, ts)
);

CREATE INDEX idx_kline_symbol_ts ON kline_1m(symbol, ts);

With this schema, computing any higher timeframe is a single query:

-- 1-hour candles from 1-minute data
SELECT
    (ts / 3600) * 3600 AS hour_ts,
    symbol,
    FIRST_VALUE(open) OVER w AS open,
    MAX(high) OVER w AS high,
    MIN(low) OVER w AS low,
    LAST_VALUE(close) OVER w AS close,
    SUM(volume) OVER w AS volume
FROM kline_1m
WHERE symbol = ? AND ts BETWEEN ? AND ?
WINDOW w AS (PARTITION BY (ts / 3600) * 3600 ORDER BY ts);

The Migration Script

We wrote a one-shot migration script that:

1. Reads each JSON file in chunks (not all at once, to avoid memory spikes)
2. Deduplicates by (symbol, timestamp) — the JSON files had accumulated 3% duplicates from restart edge cases
3. Inserts in batches of 500 rows using BEGIN/COMMIT transactions
4. Verifies row counts match after migration
5. Keeps the JSON files as backup for 72 hours, then deletes them

import json, sqlite3, os, glob

conn = sqlite3.connect("klines.db")
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA synchronous=NORMAL")

batch = []
total = 0

for fpath in sorted(glob.glob("data/klines/*.json")):
    symbol = os.path.basename(fpath).split("_")[0]
    with open(fpath) as f:
        rows = json.load(f)

    for row in rows:
        batch.append((
            symbol, row["ts"], row["o"], row["h"],
            row["l"], row["c"], row.get("v", 0)
        ))

        if len(batch) >= 500:
            conn.executemany(
                "INSERT OR IGNORE INTO kline_1m VALUES (?,?,?,?,?,?,?)",
                batch
            )
            conn.commit()
            total += len(batch)
            batch = []

# Final batch
if batch:
    conn.executemany("INSERT OR IGNORE INTO kline_1m ...", batch)
    conn.commit()

print(f"Migrated {total} rows")

The script ran in 12 seconds on the production server. We verified row counts matched between JSON and SQLite using a parallel query, then removed the JSON directory 72 hours later.

Results

What We Would Do Differently

1. Start with SQLite from day one. We wasted 3 weeks debugging JSON file locks that SQLite handles natively. The 600KB memory cost is negligible even on a 1GB server.
2. Use WAL mode immediately. We started with DELETE journal mode, which blocked readers during writes. Switching to WAL (Write-Ahead Log) gave us concurrent reads during writes — critical for serving charts while ticks arrive.
3. Batch inserts. Our first implementation did individual INSERT per tick. Batching 500 rows per transaction improved write throughput by 40x.
4. Add a retention cron on day one. We forgot to prune old data for the first month. A simple DELETE FROM kline_1m WHERE ts < strftime('%s','now','-3 months') in crontab now handles this automatically.

Why Not PostgreSQL?

We get this question often. PostgreSQL is an excellent database. But for a single-server trading terminal that needs to run on a $2/month VPS with 1GB RAM, it's the wrong tool. PostgreSQL's minimum viable memory footprint is ~200MB for shared buffers alone. SQLite runs in-process at ~600KB. That's a 333x difference for our use case.

The trade-off is that SQLite doesn't handle concurrent writers well. But our write pattern is single-writer (one MT5 data pump process), which is exactly what SQLite excels at. If we ever need multi-writer, we'd evaluate PostgreSQL — but at our current scale of 1-2 ticks/second across 30 instruments, SQLite is not the bottleneck.