The engine comparison answers “which analytical database?”; this guide answers the question before it: does this data belong in files, a relational database, or a columnar engine at all? The three are archetypes with different contracts, and most storage regret comes from putting a workload in the wrong archetype, not the wrong product.
The three contracts
Files (Parquet + DuckDB/Polars) promise: maximal scan throughput per dollar, zero operations, universal tool compatibility, cheap archival. They decline: concurrent writers, transactions, fine-grained updates, enforcement of anything. Files are immutable facts in bulk — which is precisely what research reads look like.
Relational (Postgres) promises: transactions, constraints, concurrent readers and writers, point lookups and joins, a schema that enforces itself. It declines: full-table analytical scans at columnar speeds. Postgres is mutable truth with rules — orders, positions, experiment logs, the bitemporal reference layer, anything where UNIQUE doing its job saves an account.
Columnar-analytical (ClickHouse-class) promises: file-like scan speeds with a serving layer — concurrent analytical queries over tens of billions of rows, continuous ingestion, materialized aggregation. It declines: transactional semantics, cheap mutation, operational simplicity. It’s files that answer queries while being written — a contract you need later than you think.
The workload → archetype map
| Workload | Archetype | Why |
|---|---|---|
| Backtest scans over history | Files | Read-only bulk; scan speed is the product |
| Orders, fills, positions, run-state | Postgres | Constraints and transactions are the point |
| PIT reference & fundamentals | Postgres | Bitemporal queries, joins, moderate size |
| Feature store (computed, versioned) | Files | Regenerable bulk; version the code |
| Live tick capture + intraday queries | Columnar (or QuestDB-style) | Ingest-while-querying is the defining need |
| Team-shared analytical layer | Columnar | Concurrent scans files handle badly |
| Subscriber/event tracking, app state | Postgres/D1-class | Small, transactional, boring |
The two-tier default
For a one-to-three-person shop: Parquet files for everything bulky and immutable; one Postgres for everything mutable and precious. No third tier until a trigger fires. This split also has a clean backup story — pg_dump for the precious tier, object-storage sync for the bulk tier — and a clean reproducibility story: files are content-addressable; databases are dumps.
Migration triggers — specific, not vibes
To columnar, when: a second person’s queries contend with yours daily; you’re ingesting continuously and querying the same data intraday; single-node scans exceed coffee-length despite layout fixes; or the working set outgrows one machine’s NVMe. To bigger-Postgres/managed, when: ops time exceeds its cost, not before. The anti-trigger worth naming: “we might need it later” is not a trigger — the stack guide’s rule that infrastructure is bought in response to bottlenecks applies doubly to distributed databases, whose operational bill arrives instantly while their benefits wait for scale you may never reach.
Common archetype mistakes
- Ticks in Postgres: row storage, WAL amplification, and vacuum meet billions of rows; scans crawl, the instance swells — the most common first-timer error.
- Positions in Parquet: mutable state in immutable files → last-writer-wins corruption and no constraints; the idempotency machinery has nothing to grip.
- A cluster for a laptop-sized problem: the columnar tier’s admin tax paid on data DuckDB scans in seconds.
- The hybrid nobody versioned: features half in files, half in tables, no lineage — reproducibility dies in the seam.
Questions to ask before placing any dataset
- Is it mutable truth or immutable fact?
- Who writes, who reads, how concurrently?
- What’s the query shape — scan, lookup, or as-of join?
- Which tier’s backup/restore story does it need?
Where this connects
This is the decision above the engine comparison — read that one when a trigger fires and the columnar tier is genuinely due. The two-tier default is the storage half of the research stack and the production stack; the PIT architecture lives in the Postgres tier by design.
The engines: database comparison → · The file tier done right: tick storage → · The versioning seam: datasets & backtests →