Storing market data without slowing down the bot

Two separate problems get conflated. Storing market data for research is a throughput problem. Storing operational state for the running bot is a latency problem. They want different answers, and using one solution for both makes either the research slow or the trading late.

Three paths, three tools

Our microstructure engine keeps features in preallocated RAM ring buffers with no pandas in the realtime loop, and the collect phase writes raw events to Parquet. The two never share a code path, because the requirements are opposed.

Never write from the hot path

A scanner that blocks on a disk write has stopped being a scanner. The pattern is a bounded queue and a writer thread — and the queue has to be bounded, or a slow disk turns into unbounded memory growth.

# Bounded on purpose: dropping telemetry beats stalling the loop.
queue: asyncio.Queue = asyncio.Queue(maxsize=50_000)

def record(event):
    try:
        queue.put_nowait(event)
    except asyncio.QueueFull:
        metrics.increment("telemetry_dropped")     # visible, not silent

async def writer():
    while True:
        batch = [await queue.get()]
        while len(batch) < 1000 and not queue.empty():
            batch.append(queue.get_nowait())
        await asyncio.to_thread(flush, batch)      # one transaction per batch

The dropped-events counter matters. A system that silently discards telemetry under load looks healthy in exactly the conditions you most want data about.

Batch, and mean it

One insert per event is the single most common cause of a storage layer that cannot keep up. A thousand inserts in one transaction is not a thousand times faster, but it is close enough to change what is possible.

Our signal engine stores 5.76 million evaluations over fifteen days — every evaluation with its factor values, not just the ones that fired. That is roughly 384 000 rows a day, which is unremarkable batched and impossible one at a time on the evaluation path.

Index for the queries you run

Not for the queries you imagine. In practice, for a trading system, they are:

• Latest N rows for one symbol — (symbol, id DESC)
• Everything in a time range — (ts)
• Undelivered or failed items — a partial index on the status column
• Aggregate by day for reporting — usually satisfied by the time index

Four indexes covers almost everything. Each additional one slows every write, which on the warm path is the resource you are short of.

Retention, decided in advance

Storage grows faster than expected and the decision to delete is always made under pressure. Set the policy before it becomes urgent:

1. Raw book events: expensive, only useful for microstructure research. Keep a defined window unless you know you need more.
2. Candles: cheap and useful indefinitely. Keep them.
3. Evaluations and decisions: the audit trail. Keep as long as you might need to explain a trade.
4. Trades and equity snapshots: keep forever. They are small and they are the record.

Our candle pipeline runs a rolling seven-day archive with automatic backfill of 1 450 symbol-timeframe pairs. Old data is deleted on a schedule rather than when the disk fills, which is the difference between a maintenance job and an incident.