Migracion de JSON a SQLite para Almacenamiento OHLC — Engineering

Notas de Engineering — Vol. 1

Migración de JSON a SQLite para Almacenamiento OHLC

El Problema

DaPex Terminal muestra gráficos de velas OHLC en 7 marcos temporales (1m, 5m, 15m, 1h, 4h, 1d, 1w) para más de 30 instrumentos. Detrás de cada gráfico hay datos históricos de precios que deben:

• Consultarse lo suficientemente rápido para cargas de página inferiores a 50ms
• Actualizarse cada minuto con nuevos ticks desde MT5
• Agregarse en marcos temporales superiores sobre la marcha
• Conservarse durante 3 meses y luego podarse

Nuestra primera implementación almacenaba cada combinación de instrumento-marco temporal como un archivo JSON separado:

data/klines/
  XAUUSD_1m.json    (2.1 MB, 28,000 filas)
  XAUUSD_5m.json    (0.5 MB, 5,600 filas)
  XAUUSD_1h.json    (0.1 MB, 720 filas)
  EURUSD_1m.json    (1.8 MB, 24,000 filas)
  ...
  // 30 instrumentos x 7 marcos temporales = 210 archivos

Esto funcionó bien al inicio. Pero para la tercera semana, las grietas comenzaron a aparecer.

Tres Modos de Falla

1. Contención de Escritura

Cuando MT5 enviaba un nuevo tick, un worker de Python abría el archivo JSON, analizaba los 28,000 registros, añadía uno, serializaba todo el array de vuelta al disco y cerraba el archivo. Durante mercados volátiles con más de 6 ticks por segundo en 10 instrumentos activos, la E/S del sistema de archivos se convertía en el cuello de botella. El hilo de solicitud de Flask se bloqueaba esperando el bloqueo del archivo, causando que los tiempos de respuesta de la API aumentaran de 50ms a más de 2 segundos.

2. Fallos de Atomicidad

La escritura de un archivo JSON no es atómica. Si el servidor se bloqueaba a mitad de la escritura (lo que ocurrió dos veces durante una fluctuación de energía en la instancia en la nube), el archivo terminaba truncado, perdiendo todos los datos desde la última copia de seguridad. Tuvimos que reproducir desde el historial de MT5 para recuperarlos, lo que tomó más de 45 minutos por instrumento.

3. Colapso del Rendimiento de Consultas

Construir una vela de 1 hora a partir de 60 velas de 1 minuto implicaba analizar 60 archivos JSON, fusionarlos en memoria y agregarlos. Para un gráfico que mostraba 100 velas horarias a lo largo de 6,000 minutos de datos, el frontend esperaba 850ms en promedio. Para contexto, los Core Web Vitals de Google marcan cualquier valor por encima de 100ms.

Qué Consideramos

SQLite ganó en tres aspectos: está embebido (sin proceso separado), es compatible con ACID (sin más pérdida de datos) y usa 600KB de memoria en modo WAL, el 0.06% de nuestro servidor de 1GB.

La Migración

Diseño del Esquema

La idea clave fue separar los ticks sin procesar de las velas agregadas. Almacenamos solo las velas de 1 minuto como datos fuente y calculamos todos los marcos temporales superiores mediante agregación SQL:

CREATE TABLE kline_1m (
    symbol    TEXT NOT NULL,    -- XAUUSD, EURUSD
    ts        INTEGER NOT NULL, -- Marca de tiempo Unix de apertura de la vela
    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);

Con este esquema, calcular cualquier marco temporal superior es una sola consulta:

-- Velas de 1 hora a partir de datos de 1 minuto
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);

El Script de Migración

Escribimos un script de migración único que:

1. Lee cada archivo JSON en fragmentos (no todo a la vez, para evitar picos de memoria)
2. Desduplica por (símbolo, marca de tiempo) — los archivos JSON habían acumulado un 3% de duplicados debido a casos extremos de reinicio
3. Inserta en lotes de 500 filas usando transacciones BEGIN/COMMIT
4. Verifica que los recuentos de filas coincidan después de la migración
5. Mantiene los archivos JSON como copia de seguridad durante 72 horas, luego los elimina

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 = []

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

print(f"Se migraron {total} filas")

El script se ejecutó en 12 segundos en el servidor de producción. Verificamos que los recuentos de filas coincidieran entre JSON y SQLite usando una consulta paralela, luego eliminamos el directorio JSON 72 horas después.

Resultados

Qué Haríamos Diferente

1. Comenzar con SQLite desde el primer día. Perdimos 3 semanas depurando bloqueos de archivos JSON que SQLite maneja de forma nativa. El costo de memoria de 600KB es insignificante incluso en un servidor de 1GB.
2. Usar modo WAL inmediatamente. Comenzamos con el modo de diario DELETE, que bloqueaba a los lectores durante las escrituras. Cambiar a WAL (Write-Ahead Log) nos dio lecturas concurrentes durante las escrituras, algo crítico para servir gráficos mientras llegan los ticks.
3. Inserciones por lotes. Nuestra primera implementación hacía INSERT individual por tick. Agrupar 500 filas por transacción mejoró el rendimiento de escritura en 40x.
4. Añadir un cron de retención desde el día uno. Olvidamos podar los datos antiguos durante el primer mes. Un simple DELETE FROM kline_1m WHERE ts < strftime('%s','now','-3 months') en crontab ahora maneja esto automáticamente.

¿Por Qué No PostgreSQL?

Recibimos esta pregunta a menudo. PostgreSQL es una base de datos excelente. Pero para un terminal de trading de un solo servidor que necesita ejecutarse en un VPS de $2/mes con 1GB de RAM, es la herramienta equivocada. La huella de memoria mínima viable de PostgreSQL es de ~200MB solo para búferes compartidos. SQLite se ejecuta en proceso con ~600KB. Esa es una diferencia de 333x para nuestro caso de uso.

La compensación es que SQLite no maneja bien escritores concurrentes. Pero nuestro patrón de escritura es de un solo escritor (un proceso de bombeo de datos MT5), que es exactamente para lo que SQLite es excelente. Si alguna vez necesitamos múltiples escritores, evaluaríamos PostgreSQL, pero a nuestra escala actual de 1-2 ticks/segundo en 30 instrumentos, SQLite no es el cuello de botella.