Building an Options Database: Schema, Storage and the Mistakes That Bite Later
If you are considering storing Indian options data yourself, this is what the shape of the problem looks like from the inside. Some of it is obvious in hindsight; the parts that are not tend to be found in production.
The row count is the problem, not the bytes
Start with the arithmetic, because it determines every later decision.
One index. Roughly 100 strikes with meaningful activity per expiry, calls and puts, so ~200 contracts. Around 375 trading minutes per session. Weekly and monthly expiries overlapping means several live at once.
That is comfortably tens of millions of 1-minute rows per index per year. Across NIFTY, BANK NIFTY and SENSEX since 2021, the MoneyTicks archive is around 250 million candles.
The disk footprint is manageable — a few hundred gigabytes compressed. What actually hurts is the row count, because it changes which queries are viable. Anything that scans the whole table stops being something you can do casually.
Schema
The natural shape:
CREATE TABLE option_candle_1m (
symbol TEXT NOT NULL, -- NIFTY, BANKNIFTY, SENSEX
expiry DATE NOT NULL,
strike NUMERIC NOT NULL,
option_type TEXT NOT NULL, -- CE or PE
ts TIMESTAMPTZ NOT NULL,
open NUMERIC,
high NUMERIC,
low NUMERIC,
close NUMERIC,
volume BIGINT,
oi BIGINT,
PRIMARY KEY (symbol, expiry, strike, option_type, ts)
);
Three decisions worth explaining.
All five identity columns in the key. A contract is not identified by symbol alone, nor by strike alone. Putting all five in the primary key means the dominant access pattern — one contract’s full series — is a contiguous range scan.
Open interest on the candle row. OI is a point-in-time snapshot rather than an aggregate over the minute, so it is arguably a different kind of measurement. Store it on the same row anyway. Every meaningful query wants price and OI together, and separating them buys a join you will pay for on every read.
No synthetic candles. If a contract did not trade in a minute, store nothing for that minute. Do not carry the previous close forward. The gaps are information — they are how you identify illiquid strikes, and a backtest that cannot see them will assume fills that were never available.
Timestamps
Store UTC. Convert on the way out.
Indian markets run 09:15–15:30 IST, and the session is unambiguous in local time, which tempts people into storing IST. It works until you compare against something in a different zone or hit a DST-adjacent library bug. TIMESTAMPTZ, UTC, convert at the boundary.
Query with AT TIME ZONE 'Asia/Kolkata' when you need a session date:
SELECT (ts AT TIME ZONE 'Asia/Kolkata')::date AS session_date, ...
TimescaleDB, and the failure mode nobody warns you about
At this scale a hypertable is worth it — automatic chunking by time, compression on older chunks, and continuous aggregates for rollups.
But chunking changes how queries fail, and this is the part worth internalising because it took our public data pages down.
A query with no bound on ts touches every chunk and takes a lock on each. Across years of 1-minute data that is thousands of chunks, and it exhausts max_locks_per_transaction:
error: out of shared memory
On a plain Postgres table, the equivalent query would have been slow. On a hypertable it fails outright — and it fails fast, in a couple of seconds, which makes it look like a connection problem rather than a query problem. That misdirection cost us real diagnostic time.
The specific query that did it looked harmless:
-- Every chunk. Every time.
SELECT DISTINCT expiry FROM option_candle_1m WHERE symbol = $1;
An upper bound alone does not save you either — WHERE ts <= now() still starts the scan at the beginning of the table.
Two rules follow:
Bound ts on both sides, always. For options this is easy, because a contract only trades in a window before its own expiry. The expiry itself gives you the bound:
WHERE symbol = $1 AND expiry = $2
AND ts >= $2::date - INTERVAL '400 days'
AND ts <= $2::date + INTERVAL '2 days'
Precompute the metadata queries. “Which expiries exist for this symbol” should never scan the candle table. Keep a small manifest table, refreshed nightly, and answer from that:
CREATE TABLE option_expiry_manifest (
symbol TEXT NOT NULL,
expiry DATE NOT NULL,
strike_count INTEGER NOT NULL,
first_day DATE,
last_day DATE,
PRIMARY KEY (symbol, expiry)
);
Building that manifest is itself a whole-table aggregate, so build it in windows — a month at a time — rather than one statement. Each window commits independently, so a failure part-way resumes instead of restarting.
Rollups
Store 1-minute as the base and derive everything else. Independent tables per interval drift out of agreement, and reconciling them is worse than computing them.
Whether you materialise the rollups or compute on the fly depends on read patterns. One caution: do not assume a rollup exists just because you configured it. Check for the relation before querying it and fall back to the base table — we were bitten by code assuming a daily rollup that had never been created.
Ingestion
Idempotency is not optional. Feeds redeliver, jobs get re-run, backfills overlap. Every write should be an upsert on the primary key.
Reconcile against end-of-day. Capture intraday, then check the day’s closing figures against the exchange’s published file. Discrepancies are usually your capture, occasionally the feed, and finding out weeks later is much worse than finding out that evening.
Expect gaps and record them as gaps. Holidays, halts, feed outages. A missing row that means “did not trade” and a missing row that means “we were down” look identical later unless you track the second separately.
Should you build this?
Honestly: probably not, unless the pipeline is the product.
The engineering is not especially hard, but it is continuous. Every trading day, indefinitely, with reconciliation and monitoring, and the value only materialises after years of accumulation. Miss six months and that gap is permanent — the data cannot be recovered afterwards.
Reasonable reasons to build it: you need something no vendor sells, you have compliance requirements about data custody, or the capture itself is your differentiator.
Reasonable reasons not to: you want to backtest, and the archive is a means rather than an end.
If you do want to skip the pipeline, MoneyTicks is exactly this database with an API on it — the schema above is essentially ours. And if you would rather build it, the failure modes described here are the ones worth designing around from the start, because each of them cost us something to learn.
Further reading: where to get expired NIFTY and Bank Nifty options data compares the alternatives, and backtesting an options strategy in Python covers what to do once the data is in place.
Frequently asked questions
How much storage does per-minute options data need?
More than most people estimate. A single index's option chain across all strikes and expiries generates tens of millions of 1-minute rows per year. MoneyTicks holds roughly 250 million candles across three indices, which is a few hundred gigabytes with compression — the row count causes more trouble than the disk usage.
Should I use TimescaleDB or plain Postgres for options data?
Either works, but a hypertable gives you chunk pruning and compression that matter at this scale. The catch is that chunking changes how queries fail — a query with no bound on the timestamp touches every chunk and takes a lock on each, which can exhaust shared memory even when a plain Postgres table would merely have been slow.
What should the primary key be for an options candle table?
Symbol, expiry, strike, option type and timestamp together. All five are needed to identify a row uniquely, and having them in the index means the common access pattern — one contract's series over time — is a range scan rather than a filter.
How do I store open interest alongside price?
On the same row as the candle. Open interest is a point-in-time snapshot rather than a bar, but keeping it beside the OHLC avoids a join on every query and makes it trivial to compute change in OI per interval.
Do I need to store every interval separately?
No. Store 1-minute as the base and derive 5-minute, 15-minute, hourly and daily from it, either as materialised rollups or on the fly. Storing each independently invites them to drift out of agreement.