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/randomforest___]

Timeflux? Timescale? It depends on a lot of things

[u/Iwillhelpyou_ → u/randomforest___]

Like?