# Choosing a database for tick data

A day of US equity quotes is 1.9 billion records. Why row stores fail on it, why the as-of join decides the engine, and how to size before you pick.

*https://stockmarketstack.com/guides/storing-tick-data · background to Tick Data Storage & Time-Series Databases*

**Answer:** Market data is not simply large. It is shaped in a way general-purpose databases are built to ignore: append-only, time-ordered, enormous cardinality on symbol, read as time ranges and as-of joins rather than point lookups, and compressible by one to two orders of magnitude as columns rather than rows. Size one day of your own data first, then test the as-of join on it. Below a few terabytes, Parquet plus an embedded engine wins.

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](https://www.postgresql.org/docs/current/storage-page-layout.html) 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](https://www.nyse.com/publicdocs/nyse/data/Daily_TAQ_Client_Spec_v4.3.pdf),
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](https://ftp.nyse.com/Historical%20Data%20Samples/DAILY%20TAQ/), 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](https://cdn.opraplan.com/documents/notices/OPRA_Capacity_Projections_Update_0925.pdf)
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](https://stockmarketstack.com/guides/real-time-market-data-fees).

## 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](https://stockmarketstack.com/tools/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`](https://code.kx.com/q/ref/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](https://parquet.apache.org/docs/file-format/data-pages/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](https://stockmarketstack.com/categories/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](https://stockmarketstack.com/tools/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](https://stockmarketstack.com/tools/clickhouse) or [QuestDB](https://stockmarketstack.com/tools/questdb) over the window you actually
query; [TimescaleDB](https://stockmarketstack.com/tools/timescaledb) if staying inside Postgres is worth more than the last
factor of throughput; [kdb+](https://stockmarketstack.com/tools/kdb) where a shop already runs it. Anything rebuildable from the
archive is a reversible decision, and [self-hosted](https://stockmarketstack.com/collections/self-hosted) and
[open-source](https://stockmarketstack.com/collections/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](https://stockmarketstack.com/guides/why-adjusted-close-differs) is what a store of pre-adjusted prices cannot undo.

**5. Buy the history rather than accumulating it.** [Databento](https://stockmarketstack.com/tools/databento),
[AlgoSeek](https://stockmarketstack.com/tools/algoseek), [Tick Data](https://stockmarketstack.com/tools/tickdata) and
[FirstRate Data](https://stockmarketstack.com/tools/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](https://stockmarketstack.com/guides/free-financial-data-sources) maps what is genuinely unencumbered.

## Tools this bears on

- [DuckDB](https://stockmarketstack.com/tools/duckdb.md) — In-process analytical SQL over Parquet tick files, with no server to run.
- [ArcticDB](https://stockmarketstack.com/tools/arcticdb.md) — Versioned Pandas frames written straight onto S3, with no server to run.
- [ClickHouse](https://stockmarketstack.com/tools/clickhouse.md) — Columnar OLAP database that keeps years of ticks on disk cheaply and scans them fast.
- [QuestDB](https://stockmarketstack.com/tools/questdb.md) — Open-source time-series SQL built for tick data — ASOF JOIN, SAMPLE BY, LATEST ON.
- [kdb+](https://stockmarketstack.com/tools/kdb.md) — The tick database trading desks have run for 25 years, queried in q rather than SQL.

## FAQ

### How much storage does a year of US equity tick data need?

Start from the exchange's own numbers. NYSE's Daily TAQ specification version 4.3 puts one day of consolidated quotes at about 38 GB compressed across 26 files and 1.9 billion records, one day of trades at 2.4 GB and 69 million records. At roughly 252 trading days that is 10 TB a year of gzipped text for quotes alone on the specification's estimate, and 12 TB measured from the published sample files.

### Can I store tick data in Postgres?

For tens of millions of rows, yes. Past a few hundred million it stops being a storage question and becomes a layout one. PostgreSQL spends 23 bytes of tuple header plus a 4-byte item identifier on every row, maintains every index on every insert, and stores rows rather than columns, so a scan of two fields reads all of them. TimescaleDB exists to fix exactly that inside the same server.

### What is the single best test when comparing tick-data engines?

Run your own as-of join. Take one day of your trades and one day of your quotes, stamp each trade with the quote prevailing at that instant, and time it. It is the operation this domain runs constantly, support for it varies enormously between engines, and doing it with a correlated subquery or a window function over billions of rows is the difference between a query that returns and one that does not.

### Is Parquet good enough for market data, or do I need a database?

Date-partitioned Parquet on object storage plus an embedded query engine is the right answer more often than people expect, and it is what several databases use as their own storage. Parquet stores columns, encodes sorted integers as deltas, and carries per-chunk statistics an engine can use to skip whole files. A server earns its keep when something writes a live feed while something else reads it.

### How much bigger is options data than equities?

Much. The OPRA participants' capacity projections dated 15 September 2025 put total OPRA traffic at 13.575 billion messages a day effective July 2026, rising to 14.964 billion by July 2027, for one of two redundant streams. Against roughly 2 billion consolidated equity trade and quote records a day, that is about seven times the message count, from one asset class.

## Sources

1. [Daily TAQ Client Specifications, version 4.3](https://www.nyse.com/publicdocs/nyse/data/Daily_TAQ_Client_Spec_v4.3.pdf) — New York Stock Exchange, 2026-03-03
2. [Daily TAQ historical data samples directory](https://ftp.nyse.com/Historical%20Data%20Samples/DAILY%20TAQ/) — New York Stock Exchange, read 2026-09-14
3. [Revised OPRA Capacity Projections, notice to OPRA Multicast Data Subscribers](https://cdn.opraplan.com/documents/notices/OPRA_Capacity_Projections_Update_0925.pdf) — Securities Industry Automation Corporation (OPRA), 2025-09-15
4. [Database Page Layout, PostgreSQL documentation](https://www.postgresql.org/docs/current/storage-page-layout.html) — PostgreSQL Global Development Group, read 2026-09-14
5. [AsOf Join](https://duckdb.org/docs/current/guides/sql_features/asof_join.html) — DuckDB, read 2026-09-14
6. [aj, aj0, ajf, ajf0 — as-of join](https://code.kx.com/q/ref/aj/) — KX Systems, read 2026-09-14
7. [Encodings, Apache Parquet documentation](https://parquet.apache.org/docs/file-format/data-pages/encodings/) — Apache Software Foundation, read 2026-09-14

*Last updated 2026-09-14. A reference page, corrected in place — not a dated post.*