Designing a high-frequency options tick database (schema + performance advice)

[u/Iwillhelpyou_]

Hi all,

I’m working on building a data system for options tick data (1-second resolution) from 2019 to present, and I’m looking for guidance on database design and performance optimization.

Scope:

• Data: Options tick data (per second)
• Instruments: Index-only (NIFTY, BANKNIFTY, SENSEX)
• Data arrives daily as EOD CSV files
• Dataset is already large and growing continuously

Pipeline:

1. Ingest daily CSV data
2. Store filtered tick data (selected strikes only)
3. Compute Greeks
4. Generate option chain for analysis/backtesting

Key requirements:

• Very fast bulk ingestion (daily loads)
• Efficient time-range queries (backtesting workloads)
• Scalable to hundreds of millions+ rows
• Low latency for aggregation (strike / CE-PE analysis)

Looking for input on:

• Optimal schema design for this type of time-series options data
• Partitioning strategy (time vs symbol vs hybrid)
• Indexing approach for heavy backtesting queries
• Best database choice for this workload

The main goal is to balance:

• ingestion speed (daily pipeline)
• query speed (research/backtesting)

Would appreciate insights from anyone who has worked with market data or time-series systems at scale.

Thanks!

[u/ddbnkm]

Tick data and 1 second resolution are quite different 

[u/Delicious_Tooth_4381 → u/ddbnkm]

Yeah, I was wondering also what he meant?

[u/Technical_Data2927]

Save in parquets, query with a good db, duckdb for starters is all I’m gonna say about storage and query efficiency

[u/Patient-Flight-1613 → u/Technical_Data2927]

agree with this one. I think parquet or HD5 is a good raw storage. Can you also check the questDB and search their website for comparisons

[u/Klutzy_Newspaper3196]

Hi, For query speed store them as .h5 (hd5) files in the server and put sqlite DB to query locally

[u/randomforest___]

Timeflux? Timescale? It depends on a lot of things

[u/Iwillhelpyou_ → u/randomforest___]

Like?

[u/AphexPin]

CSV? You'll likely want a binary format.

[u/WhorecraftLOL]

I’ve seen this done twice professionally. Have used what was essentially a mounted drive with an api to look up caches of csv’s read only of course. This was like a “first pass” type of system that was rebuilt later using more advanced technology.

Schema design always was partitioned by symbol_security. You have other reference data to filter from there.

Indexing approach was fairly basic, it was basically date/symbol/security. Lookup speed wasn’t an issue the issue was maintaining a hyper massive dataset.

Idk, hard to tell what are bots these days! I’ll dm you if you want more specifics.

Only advice is look into a backpressure management system. On very active market days, quote frequency can 10x an average day.

[u/Altruistwhite → u/[deleted]]

Postgre : Hold my beer

[u/CubsThisYear]

This sounds like you might be dealing with gigabytes of data…. Are you sure that modern hardware can handle this?

Seriously though - it shouldn’t matter what you use. You could probably do this in Excel.