An algorithmic trading platform that always trades in simulation

What it is

A system that ingests market data in real time, stores it by the million rows, lets me test strategies across that whole history, and acts on signals under enforced risk limits — in simulation, always. The mode that would touch real money ships switched off, writes down what it would have done, and has to be enabled by hand.

The three pieces are deliberately one system: the rule engine that evaluates a signal against history is the exact same engine that evaluates it live, so a backtest result and a live session cannot drift apart by having two implementations. Risk limits are checked before any position is opened, and size is derived from the risk taken rather than from a fixed number.

Underneath the domain, this is a data problem: capture time series with no gaps and no duplicates, be able to reprocess a range without corrupting what is already stored, and query millions of rows fast enough to decide in the moment.

Technical stack

Python 3.12 with FastAPI and asyncio on top of TimescaleDB — Postgres with its time-series extension — asyncpg to talk to the database, Alembic for migrations, and Redis pub/sub as the bus for live alerts. The hard rules — what each row is, what must never be duplicated — live in the database schema rather than in application code: both the ingestor and the backfill write with ON CONFLICT DO NOTHING against the primary key, so replaying a range duplicates nothing. It deploys with Docker Compose on my own server, and logs through structlog.

That is where the big number in the header comes from. The query that looks up the last candle before each backfill filtered on a field inside the JSONB column (raw->>'source'), which is not indexable: the planner fell back to a row-by-row filter and detoasted every row’s JSONB to read a single field, discarding roughly 2.6 million rows before reaching the first useful one. The obvious fix was a partial index on the expression — one line of SQL, no migration — and I turned it down: it treats the symptom and leaves the rule buried in a free-text field, where any new writer can silently remix the two populations again. What went in instead was a NOT NULL discriminator column with a CHECK constraint and a partial index on top: the rule now lives in the one place it cannot be bypassed. It cost a migration and changes to all three writers; in exchange, 44.7 s down to 2.4 ms under EXPLAIN (ANALYZE, BUFFERS) on the real database, worst case.