When we started building our trading platform, the database decision seemed obvious: Postgres. It is the default choice for production systems, has excellent tooling, and handles concurrent writes well. We chose SQLite instead. After two years, 20.8 million candle rows, 19 tables, and a 5 GB database file, we have not regretted it once.
This is not a "SQLite is always better than Postgres" argument. It is an explanation of why SQLite is the right choice for a single-operator trading platform that runs on one machine.
The Architecture Constraint
Our platform is a single Python process running on a Mac. The FastAPI server, 45 trading bots, the scheduler, the event bus, and the AI service all run in one process. There is no cluster. There is no horizontal scaling. There is one operator (us) and one machine.
In this architecture, Postgres would mean running a separate daemon, managing connections through a pool, handling authentication, and configuring memory allocation. All of that infrastructure serves one purpose: handling multiple concurrent writers from multiple machines. We have one machine and one process. The complexity is pure overhead.
SQLite runs in-process. No daemon. No TCP connections. No authentication. The database is a single file on disk. Our application opens it with a file path and starts querying. Deployment is copying one file.
WAL Mode: The Key Enabler
SQLite's default journal mode (rollback) blocks readers during writes. For a trading platform where 45 bots might update positions while the dashboard queries portfolio state, this is unacceptable.
WAL (Write-Ahead Logging) mode changes the concurrency model. Readers never block writers. Writers never block readers. Multiple readers can proceed concurrently. Only multiple simultaneous writers contend, and even then, the busy_timeout parameter (we set it to 5,000 milliseconds) makes the second writer wait rather than fail immediately.
# Our SQLite engine configuration
pragmas = {
"journal_mode": "WAL",
"busy_timeout": 5000,
"foreign_keys": "ON",
"synchronous": "NORMAL",
}
The synchronous=NORMAL setting deserves explanation. In WAL mode, NORMAL means SQLite fsyncs at each checkpoint but not at every transaction commit. This is a tradeoff: you could lose the last few transactions in a power failure, but transaction throughput increases significantly. For a trading platform where we can always re-fetch candle data from the exchange and bot state recovers from the last checkpoint, this tradeoff is acceptable.
In practice, we have never lost data. The Mac has battery backup, the UPS handles power events, and SQLite's WAL checkpointing is frequent enough that the window of vulnerability is seconds, not minutes.
19 Tables, One File
Our schema covers the full trading lifecycle.
The candle storage is the largest table by far. With 25 symbols, 11 timeframes, and data going back to 2021 (or listing date for newer tokens), the candles table holds 20.8 million rows. Each row is a timestamp, open, high, low, close, volume, plus foreign keys to the exchange and metadata. A unique constraint on (symbol, timeframe, timestamp) prevents duplicates, and INSERT OR IGNORE makes idempotent inserts trivial.
Derivatives data adds four more tables: funding_rates (8-hour settlement intervals from Binance), open_interest (hourly snapshots), long_short_ratios (4-hour from Binance and Coinglass), and premium_index (spot-futures basis spreads). These are smaller but growing. The Coinglass integration adds another 24 tables for macro indicators, on-chain data, and extended derivatives metrics.
Bot state is tracked across bots, positions, orders, and trades tables. Each bot has a row in the bots table with its configuration, strategy, symbol, and current capital. Positions track the state machine (FLAT, OPENING, OPEN, CLOSING, CLOSED) with entry price, quantity, and unrealized PnL. Orders log every simulated or real execution. Trades record completed round-trips with PnL.
Risk events get their own table for the audit trail. Every circuit breaker firing and risk rejection is logged with the event type, severity, trigger source, and a JSON details column. The Risk Log tab in the dashboard queries this table with pagination and filtering.
Backtest results, job tracking, and equity snapshots round out the schema. The backtest_runs table stores every backtest result with its parameters, metrics (Sharpe, drawdown, profit factor), and a JSON column containing per-trade PnL lists for Monte Carlo simulation.
All 19 tables live in one 5 GB file. No tablespace configuration. No vacuum scheduling. No connection pool tuning.
Alembic Migrations on SQLite
One perceived disadvantage of SQLite is schema migration. Postgres has ALTER TABLE ... ADD COLUMN, DROP COLUMN, ALTER TYPE, and other DDL that Alembic migrations use directly. SQLite's ALTER TABLE is limited: you can add columns and rename tables, but you cannot drop columns (in older versions) or change column types.
Alembic handles this with batch mode. When a migration needs to drop or modify a column, Alembic creates a new table with the desired schema, copies data from the old table, drops the old table, and renames the new one. This is slower than Postgres's in-place DDL, but for a 5 GB database, even the largest migration completes in under a minute.
We have run 30+ migrations over the life of the project without issues. The pattern is straightforward: write the migration, test on a copy of the database, run alembic upgrade head on the real one. Rollback with alembic downgrade -1 if something goes wrong. The workflow is identical to Postgres.
Concurrent Access Patterns
Our platform has specific access patterns that SQLite handles well.
The dominant read pattern is the dashboard polling bot status and risk metrics every 10 seconds. These are SELECT queries that touch the bots, positions, and equity_snapshots tables. WAL mode ensures these reads never block or are blocked by bot ticks writing position updates.
The dominant write pattern is bot ticks updating position state and creating orders. With 45 bots on staggered schedules (we start them 2 seconds apart), write contention is rare. Two 15-minute bots ticking at the same second would contend, but the busy_timeout of 5 seconds means the second writer waits briefly rather than failing.
The heavy write pattern is backtest result insertion. During a tournament run, the backtest engine writes hundreds of results to backtest_runs. This happens in bursts (8 parallel workers finishing around the same time) and is the closest we get to write contention. Even here, the busy_timeout handles it gracefully. We have never seen a SQLITE_BUSY error in production logs.
The backup pattern is the one place where SQLite requires care. You cannot simply copy the database file while the application is running, because the WAL sidecar files (.wal and .shm) might be out of sync with the main file. We use SQLite's .backup command, which creates an atomic snapshot that includes any pending WAL changes. This is the same approach we use when syncing the candle database to our distributed backtest workers.
Performance Numbers
Some concrete numbers from our production database.
A query for the last 500 candles of a single symbol and timeframe takes 2-3 milliseconds. This runs every tick for every bot, so it executes around 45 times every 15 minutes for the 15m bots plus additional times for 4h and 1h bots. Total: roughly 200 candle queries per hour, all returning in single-digit milliseconds.
The full-table scan for backtest results (the Results tab in the dashboard) uses server-side pagination with LIMIT/OFFSET. Fetching page 1 (20 results sorted by Sharpe descending) across 3,000+ backtest runs takes 15 milliseconds. Page 100 takes 45 milliseconds. Acceptable for a dashboard that refreshes on user interaction, not on a timer.
Writing a single backtest result (INSERT into backtest_runs with a JSON results column containing trade PnL lists) takes 1-5 milliseconds depending on the size of the results_json blob.
The equity snapshot job runs hourly and inserts one row per active bot plus one portfolio-level row. That is 46 INSERTs in a transaction, completing in under 10 milliseconds.
When SQLite Would Not Work
We would switch to Postgres if any of these conditions became true.
Multiple machines writing simultaneously. If we ran bots on separate servers that all needed to write to the same database, SQLite's single-writer model would break. Postgres's multi-writer concurrency would be necessary.
The database exceeding 50-100 GB. SQLite handles large databases technically (the limit is 281 TB), but backup, migration, and filesystem operations become slow. Our 5 GB database backs up in 8 seconds. At 100 GB, that becomes minutes.
High-frequency write throughput. If we needed to process thousands of writes per second (like a full order book tick stream), SQLite's single-writer serialization would bottleneck. Our current write throughput peaks at maybe 10 writes per second during backtest result bursts.
Multiple services needing independent access. If the API server and bot engine ran as separate processes on separate machines, they could not share a SQLite file. Postgres would provide the network-accessible database they need.
None of these conditions apply to our architecture. We are one process on one machine with moderate write throughput and a 5 GB database. SQLite is not a compromise. It is the optimal choice for this problem.