How should I store historical stock data?

In layers. Keep the raw files from the source exactly as delivered, one per day, and never edit them. Build typed Parquet from them, one file per day or month. Load a serving table from the Parquet only if something needs to query it over a network. Store raw prices and corporate actions separately and compute adjusted prices yourself, so a rebuild gives the same numbers. Key every row on symbol, exchange and date.

Should I store stock data in Parquet partitioned by date or by symbol?

By date, for data that arrives a day at a time. Each night then writes one new file and touches nothing else, which makes re-runs and backfills simple. Reading one symbol's history across many date files is still fast in DuckDB or Polars, because both read only the columns they need and skip row groups using file statistics. Partitioning by symbol means rewriting thousands of files every day, and gives you thousands of tiny files.

Do I need FINAL with ClickHouse ReplacingMergeTree?

Yes, if you need exact answers. ReplacingMergeTree removes rows with the same sorting key only when it merges parts in the background, at a time you cannot control, so a query without FINAL can see both the old and the new version of a row. FINAL deduplicates at query time. Its cost grows with the number of unmerged parts, so keep parts per partition low, for example by running OPTIMIZE on a partition after a large insert.

Is a time-series database better for stock market data?

Not for daily bars. A time-series database earns its keep on high-rate data such as ticks or intraday quotes arriving continuously. End-of-day prices for a whole exchange are a few thousand rows a day, a few million rows over years, and any columnar store or even Postgres handles that. Decide on the write pattern instead: how you overwrite, deduplicate and rebuild.

Best database for stock data: what I store NSE prices in, and what broke

Q: What is the best database for stock data?

For daily bars, most databases work, because the data is small: a whole exchange is a few thousand rows a day. Pick by who reads it. For one person doing research, Parquet files queried with DuckDB or Polars are enough and cost nothing to run. For a website, an API or many jobs reading at once, a server database helps; I use ClickHouse with ReplacingMergeTree, and Postgres works too at this size. What matters more than the engine is the key you choose, how a re-run overwrites old rows, and keeping the raw files so you can rebuild.

The question "what is the best database for stock data" assumes the data is big. For daily bars it is not. The NSE's full end-of-day file for 30 September 2026 has 3,529 rows. My pipeline's whole price table, years of every Indian equity, is about 3.5 million rows. Postgres, DuckDB, ClickHouse, SQLite or a folder of Parquet files can all hold that.

So the engine is not where my problems came from. They came from three choices that any database forces on you: what the key is, what happens when you write a row that already exists, and what happens to a row you do not write again. I store NSE prices in Parquet and ClickHouse. This post is how, and the four things that broke, including a FINAL query that ran a 14 GiB server out of memory on a table of a few million rows.

How much stock data is there, really?

Some sizes from my own system, so you can size yours:

One day of NSE cash-market data: 3,529 rows across all series (2,669 of them normal equity), about 400 KB as CSV.
All of 2026 so far (185 sessions, 1 January to 1 October): 601,637 rows, 68.3 MB as CSV, 28.3 MB as Parquet with one file per day.
The consolidated price table (eod_prices), after a rebuild in September 2026: 3,518,862 rows.

That is small. A laptop holds decades of it in memory. If your data is tick-level or intraday, the numbers change by orders of magnitude and so does this advice. For daily bars, choose for readers and write patterns, not volume.

How to store stock data: three layers

My pipeline keeps three copies, each with one job:

Raw files, as delivered. The exchange's CSV for each day, never edited. Every serious fix I have made was a re-parse of these.
Typed Parquet, one file per day. Built from the raw files by a daily partitioned job, on a NAS. DuckDB and Polars read these directly for research and for rebuilding.
A serving table in ClickHouse. ReplacingMergeTree, keyed on (nse_symbol, exchange, date). It exists because many things read prices at once: the pipeline's own jobs, and a REST API behind a website.

The third layer is optional. If you are the only reader, stop at Parquet and query it with DuckDB. I explain when that stops being enough near the end.

Partition Parquet by date, not by symbol

Daily data arrives a day at a time, so partition by date. Each night writes one new file and touches nothing else. A re-run of one day rewrites one file. Partitioning by symbol means rewriting thousands of files every night, and gives you thousands of small ones.

Reading one symbol across many date files is still quick, because DuckDB reads only the columns it needs. This query over the 185 daily files of 2026 took about 30 milliseconds on my desktop:

SELECT date, CLOSE_PRICE AS close, TTL_TRD_QNTY AS volume, DELIV_PER AS deliv_pct
FROM read_parquet('lake/eod/*/*.parquet', hive_partitioning = true)  -- lake/eod/date=2026-09-30/data.parquet
WHERE SYMBOL = 'RELIANCE' AND SERIES = 'EQ'
ORDER BY date DESC
LIMIT 3;

┌────────────┬────────┬──────────┬───────────┐
│    date    │ close  │  volume  │ deliv_pct │
├────────────┼────────┼──────────┼───────────┤
│ 2026-10-01 │ 1167.7 │ 16771221 │     61.24 │
│ 2026-09-30 │ 1187.0 │ 16376789 │     69.68 │
│ 2026-09-29 │ 1182.0 │ 24191416 │     72.35 │
└────────────┴────────┴──────────┴───────────┘

Choose the key before anything else

A price row is "one instrument, one exchange, one day". The hard part is "one instrument", because Indian stocks have two identifiers and both change.

My price table was first keyed on the ISIN. It was rekeyed to the NSE symbol, because an ISIN can change when a company splits or restructures its shares, and the same stock then appeared as two keys that never deduplicated against each other. The migration says so in its header: "ISIN is time-varying".

But symbols change too, when a company renames itself. My answer was to key on the symbol and keep a separate rename map that rewrites old tickers to current ones before anything is computed. How that map is built, from prices and from ISINs, is in survivorship bias in backtesting. Whatever you pick, write down why, because the next person will want to "fix" it to the other one.

One ClickHouse detail: columns in the sorting key should not be Nullable. My symbol column went from Nullable(String) to String DEFAULT '' in the same migration.

ReplacingMergeTree keeps duplicates until it merges

ClickHouse's ReplacingMergeTree is the usual way to make re-runs safe: insert the same key again with a newer version column and the newer row wins. But ClickHouse's own documentation is plain about when: "Data deduplication occurs only during a merge. Merging occurs in the background at an unknown time, so you can't plan for it." Until then, both rows are in the table.

Here is that, run with chDB, an embedded ClickHouse (version 26.9) you can install with pip. Night 1 writes three symbols. Night 2 rewrites two of them with a new adjustment:

from chdb import session

s = session.Session()
s.query("CREATE DATABASE IF NOT EXISTS demo")
s.query("""
CREATE TABLE demo.eod_prices (
    symbol String, date Date, close Float64,
    adj_close Nullable(Float64), updated_at DateTime
) ENGINE = ReplacingMergeTree(updated_at)
PARTITION BY toYYYYMM(date)
ORDER BY (symbol, date)
""")
s.query("""INSERT INTO demo.eod_prices VALUES
 ('ACME', '2026-09-30', 100.0, 100.0, '2026-10-01 02:30:00'),
 ('BETA', '2026-09-30',  50.0,  50.0, '2026-10-01 02:30:00'),
 ('GONE', '2026-09-30',  10.0,  10.0, '2026-10-01 02:30:00')""")
s.query("""INSERT INTO demo.eod_prices VALUES
 ('ACME', '2026-09-30', 100.0, 50.0, '2026-10-02 02:30:00'),
 ('BETA', '2026-09-30',  50.0, 25.0, '2026-10-02 02:30:00')""")

SELECT count() FROM demo.eod_prices              -> 5 rows in 2 parts
SELECT count() FROM demo.eod_prices FINAL        -> 3 rows

Five rows without FINAL, three with it. Any aggregate you run without FINAL counts the old rows too: an average close, a row count, a return. That is why the pipeline's rule is one line: use FINAL on every ReplacingMergeTree read.

FINAL ran a 14 GiB server out of memory

FINAL is not free. It merges the table's parts at query time, and its cost grows with the number of parts. Every insert creates at least one new part, and my price table is partitioned by month, so a nightly insert adds a part to the current month and background merges decide when to tidy up.

On 11 May 2026 the nightly price consolidation failed with ClickHouse error 241, out of memory against a 14.40 GiB limit. The table had drifted to 264 active parts across 89 partitions, and a FINAL read fans out across all of them. A manual OPTIMIZE TABLE ... FINAL collapsed it to 89 parts, one per partition, in 9 seconds. The same drift had happened to three other monthly-partitioned tables. In September, a design note recorded the price table at 180 active parts, 6.7 million raw rows against 3.9 million after deduplication, and noted that it had run the website's API out of memory too.

The fix is a few lines in the code that writes to ClickHouse. After every insert, it reads system.parts, finds partitions with more than 3 active parts, and runs OPTIMIZE TABLE ... PARTITION ID '<id>' FINAL on just those. A failed compaction logs a warning but does not fail the insert, because the data is already written. Large inserts also go in chunks of 100,000 rows, because writing the roughly 4-million-row price table in one insert had hit the same memory error.

Running OPTIMIZE ... FINAL once over all the price tables in June 2026 (together with the compression change below) collapsed about 2.5 million stale duplicate rows in eod_prices alone: 6.0 million rows down to 3.5 million.

A rebuild cannot delete what it does not write

Look at the demo output again, with FINAL:

┌─symbol─┬─close─┬─adj_close─┬──────────updated_at─┐
│ ACME   │   100 │        50 │ 2026-10-02 02:30:00 │
│ BETA   │    50 │        25 │ 2026-10-02 02:30:00 │
│ GONE   │    10 │        10 │ 2026-10-01 02:30:00 │
└────────┴───────┴───────────┴─────────────────────┘

GONE was not in night 2's run, so nothing replaced it. It still carries night 1's adjustment, and nothing in the table says it is stale. ReplacingMergeTree replaces keys you write again. It cannot retire a key you stop writing.

That is how my price table ended up on two bases at once. Symbols that Yahoo stopped returning kept their April 2026 adjustment, while every bhavcopy row was rewritten in September. Mixed bases produce fake price jumps; adjusted close vs close has the count.

The fix, for a table that is rebuilt whole: build the full replacement first, then delete every NSE row synchronously (mutations_sync = 2), check the delete finished, then insert. The window in which the table is short is the insert, not the computation. For tables rebuilt one date at a time, the pipeline deletes rows older than the run for exactly the dates the run rewrote.

Compression: codecs for price columns

ClickHouse compresses columns with LZ4 by default. On price data that gave ratios of 1.6 to 2.9 in my tables. A migration in June 2026 set column codecs instead: ZSTD(3) for floats, Delta then ZSTD for dates, DoubleDelta then ZSTD for timestamps. Across seven price tables, compressed size went from about 2.22 GiB to 1.63 GiB, about 27% smaller. Codecs change storage only, not values, and you can switch back.

Worth doing, but note the scale: the whole price history is under 2 GiB. That is another reason the engine choice matters less than people think.

Do you need a database server for stock data at all?

For research, no. One person, one machine, daily bars: Parquet files and DuckDB or Polars are faster to set up, free to run and simpler to rebuild. DuckDB vs ClickHouse goes through the line in detail.

I added ClickHouse for readers, not for size. A website API and dozens of scheduled jobs query prices at the same time, over the network, while the nightly load writes. That is a server's job. Postgres would also handle a few million rows; ClickHouse vs Postgres covers that trade.

A dedicated time-series database is built for data that arrives continuously at high rates, such as ticks. End-of-day bars arrive once a day.

A checklist for storing stock data

Keep the raw files as delivered. Never edit them.
Build typed Parquet partitioned by date, one file per day.
Key on symbol, exchange and date. Keep a rename map. Make key columns non-nullable.
Store raw prices and corporate actions; compute adjusted prices yourself.
With ReplacingMergeTree: FINAL on every read, and compact partitions above a few parts after each insert.
A full rebuild deletes, verifies, then inserts. A partial rebuild retires stale rows for the dates it rewrote.
Insert in chunks. Set column codecs.
Add a server only when more than one thing needs to read at once.

So: what is the best database for stock data?

For daily bars, the one whose write behaviour you understand. Mine is Parquet for the record and ClickHouse for serving, and every failure I had was about duplicates, parts or rows a rebuild did not touch, never about speed or size. If you are on your own, start with Parquet and DuckDB, and add a server when a second reader appears.

The Parquet half of this is what Local-First Analytics is about: a raw zone kept as delivered, date-partitioned Parquet built from it, and DuckDB as the warehouse over those files, including when it stops being enough. It covers ClickHouse only in passing and does not cover market data. Chapter 1 is free to read; the rest is on Amazon.

More in this series: NSE bhavcopy in Python, the NSE trading calendar in Python, survivorship bias in backtesting, adjusted close vs close and look-ahead bias and point-in-time data.