Choosing a database for tick data

The problem is not that market data is large. Disk is cheap and a laptop scans a gigabyte a second. What breaks a Postgres table a few hundred million rows in is that market data is shaped in a way a general-purpose row store is built to ignore: append-only and time-ordered, almost never updated; enormous cardinality on one column — symbol — and almost none on the rest; read as time ranges and as-of joins rather than point lookups; numeric, repetitive and sorted, so it compresses by one to two orders of magnitude as columns and barely at all as rows.

You probably do not need tick data

A few questions genuinely need every print: execution and slippage analysis, where you compare a fill to the quote that existed at that microsecond; microstructure research; anything about queue position or book dynamics. Everything else — does this signal work, what is the drawdown, how does this portfolio behave — is a strategy question that minute or daily bars answer.

The gap is not marginal. NYSE's Daily TAQ specification lists 11,700 symbols in the daily master file; at 390 minutes in a US session, a day of one-minute bars for every listed name is 4.6 million rows, against 1.9 billion consolidated quotes. A year of minute bars is smaller than one day of quotes.

How it works

"Postgres is slow for time series" is usually said as folklore. It has specific causes.

PostgreSQL stores each row with a fixed header of 23 bytes on most machines plus a 4-byte item identifier in the page, before any of your data. A quote — timestamp, symbol, bid, bid size, ask, ask size — is perhaps 40 bytes of payload, so roughly 40 per cent of what you write is bookkeeping. Multiply by 1.9 billion.

Then the index. A B-tree is maintained on every insert, turning a sequential append into scattered writes into interior pages, and it is built to find a few rows out of many. This workload does the opposite: it scans an ordered range, and where heap order does not match index order the engine walks the index and then fetches each row from a random page. A row store also reads whole rows to answer a question about two columns. None of that is a bug: it is a design for selective point lookups and row-at-a-time updates under many concurrent transactions, and tick data is neither.

What it costs

The number that decides the architecture is the one nobody states.

NYSE's Daily TAQ Client Specification version 4.3, dated 3 March 2026, publishes typical sizes for one trading day of consolidated US equities — the whole market, not one venue: trades 2.4 GB across 69 million records, quotes 38 GB across 1.9 billion records, NBBO 11 GB across 330 million. The specification states that all of it is gzipped, so each is a compressed figure.

The published samples agree: in NYSE's public samples directory, the 26 quote files for 1 April 2026 total 47.6 GB. Fetching the first 16.9 MB of one and unzipping it gave 87.4 MB of pipe-delimited text — 5.2 to 1 on that sample, which puts a day of quotes near 250 GB uncompressed and a year of them near 12 TB compressed, 60 TB raw.

Options are the extreme case, and the byte figure is the one nobody publishes: no plan states a daily file size for OPRA. What the participants publish is message volume, and the revised capacity projections of 15 September 2025 project 13.575 billion messages a day effective July 2026, rising to 14.964 billion by July 2027 — for one stream, of two redundant ones. Against roughly 2 billion consolidated equity trade and quote records a day, that is seven times the message count from one asset class. Reason from the ratio until you have measured your own bytes.

Little of what this costs is storage — 12 TB a year on object storage is a modest monthly bill. The licence on the data costs more than the disks by a wide margin, and is settled before any of this: why real-time stock data is so expensive.

The as-of join is the one thing to test first

Test one operation before choosing anything, and make it this one.

Aligning each trade to the quote prevailing at that instant is the market data operation — what execution analysis is, what slippage measurement is, and what every "what was the state of the world when this happened" question reduces to. DuckDB's documentation names the semantics directly — equality on the key columns, an inequality on time, at most one match from the right side, which is why the left table cannot grow. In SQL that has it, a day of trades against a day of quotes is four lines.

Without it, the same query is a correlated subquery — for each trade, the last quote at or before this time — or a window function over the union of both tables. Both are correct, and both do work proportional to the product of two tables with billions of rows each: the difference between a query that returns in seconds and one you kill the next morning.

Support varies enormously, and not only in whether the keyword exists. kdb+'s reference for aj says the join "should run at a million or two trade records per second" — then spends most of the page on the conditions: search columns in the order `sym`time, a parted attribute on symbol on disk, time sorted within symbol, and the note that "departure from this incurs a severe performance penalty". That is the honest version of every engine's as-of join: fast when the data is laid out for it, and the layout is your job. Test it on your own data before reading any benchmark — on every side of this field, a published benchmark is an argument rather than a measurement.

Physical layout is most of the performance

Two decisions matter more than the engine.

Partitioning. Date is the near-universal first key: almost every query is bounded in time, and a date partition lets the engine skip files rather than read and discard them. Symbol as a second partition key is a trade-off rather than a win — single-name research becomes trivially fast, and cross-sectional queries open thousands of files instead of one.

Sort order inside the partition is the decision people get wrong. Sorted by time then symbol, a query for one symbol over a month touches every block; sorted by symbol then time, that same query reads one contiguous run — while a query for all symbols in one minute now touches every block instead. Only one of the two matches your workload, and per-symbol as-of joins want symbol first.

Sort order is also most of your compression. Apache Parquet's encodings include DELTA_BINARY_PACKED, which stores integers as differences from their predecessor, bit-packed to the width actually needed. A timestamp column sorted ascending becomes a run of small, similar deltas packing into a few bits each; shuffled, the same column is full-width 64-bit integers with nothing for the encoder to exploit. The order-of-magnitude gap between a row store and a column store is mostly this — a property of the layout you chose.

Ingest and backfill are two different problems

An engine can be excellent at one and bad at the other.

Backfill is a bulk sorted write. Years of files, the full extent known, nothing late, sortable before writing. What matters is throughput, writing one partition at a time, and whether a failed load leaves a mess.

A live feed is a latency-sensitive append with out-of-order arrivals. Venues' clocks and paths differ, so a message stamped 09:30:01.000 can arrive after one stamped 09:30:01.004, and corrections land hours later to modify rows already written. Ask what the engine does with an out-of-order row: absorb it into an in-memory window, rewrite the partition, or refuse it and make you re-sort. That decides whether your ingest pipeline is ten lines or a project.

The shape of the field

Without a winner: the answer depends on the two sections above. kdb+ is the incumbent because the as-of join and the partitioned on-disk layout were the design rather than later features; for an individual the question is the licence, the free tier being capped and paid pricing quote-only. ClickHouse is a general columnar analytics engine that happens to be excellent here; QuestDB is built for time series specifically, with a live ingest path; TimescaleDB answers "keeping Postgres matters more than throughput"; ArcticDB covers Python research with no server at all. And date-partitioned Parquet queried by an embedded engine is the answer more often than the field's marketing suggests — it is what several of the products above use underneath. The cards compare them with licences read rather than badges quoted: tick data storage.

What you can do about it

In order; the first step is not a choice of database.

1. Compute your daily and annual volume first. Symbols × events per symbol per day × bytes per event × 252; if you do not know events per symbol, load one day and count. Every later decision follows from that number, and most people asking which database to use have never produced it.

2. Under a few terabytes, Parquet plus an embedded engine on a laptop is the correct answer, and costs nothing. Date-partitioned files sorted by symbol then time. No server, no ops. ArcticDB is the same shape with versioning and a Python API on top.

3. Test the as-of join on your own data before reading anyone's benchmark. One day of trades, one day of quotes, timed. An engine with no as-of operator means writing the hard version yourself for the life of the project.

4. Separate the archive from the query layer. Immutable Parquet on object storage as the system of record; ClickHouse or QuestDB over the window you actually query; TimescaleDB if staying inside Postgres is worth more than the last factor of throughput; kdb+ where a shop already runs it. Anything rebuildable from the archive is a reversible decision, and self-hosted and open-source narrow the catalogue to what runs on your own machine.

Whatever holds the bytes, store prices unadjusted alongside a corporate actions table and adjust at query time — why two providers give different returns for the same stock is what a store of pre-adjusted prices cannot undo.

5. Buy the history rather than accumulating it. Databento, AlgoSeek, Tick Data and FirstRate Data sell what capturing live would take years to reach; start recording today and you own nothing before today.

6. Read the licence before designing around it. A storage design quietly assumes what the agreement often forbids: keeping a derived copy indefinitely, letting a colleague query it. Redistribution is the word that ends hobby projects, and it is priced per feed per month — free stock market data maps what is genuinely unencumbered.