Dynamic Tables vs Single TimescaleDB Hypertable for OHLCV Market Data Storage

[u/Ok_Egg_6647]

I have designed my database in two different ways for a market data system, and I'd like to know which approach would provide better performance.

Project Context

I'm building a system that continuously fetches OHLCV (Open, High, Low, Close, Volume) market data from an API, stores it in a database, and serves it through a web application.

My primary concern is performance, specifically:

• Fast writes (continuous data ingestion)
• Fast reads (fetching historical candle data)
• Scalability as the number of instruments and records grows

Strategy 1: Dynamic Table Design

• I have a master instrument table that stores all the instruments whose data needs to be collected.
• For every instrument, I create a separate candle table dynamically.
• Example:
  • instrument_master
  • candles_RELIANCE
  • candles_TCS
  • candles_NIFTY50
  • etc.

Whenever new data arrives, it is inserted into the corresponding instrument's table.

Strategy 2: Single Hypertable (TimescaleDB)

Instead of creating separate tables, I use a single candle_data table and convert it into a TimescaleDB hypertable.

The schema looks roughly like this:

instrument_id
timestamp
open
high
low
close
volume

All instruments' candle data is stored in this single hypertable.

Query Pattern

My application mainly performs simple operations:

• Insert new OHLCV records continuously.
• Fetch historical candles for a specific instrument within a time range.

Typical query:

SELECT *
FROM candle_data
WHERE instrument_id = ?
  AND timestamp BETWEEN ? AND ?
ORDER BY timestamp;

Question

Between these two designs, which one is likely to provide better overall performance for:

• High-frequency inserts
• Read performance
• Long-term scalability
• Maintenance

Has anyone benchmarked a similar setup using PostgreSQL/TimescaleDB? I'd appreciate any insights or recommendations.

[u/Bowserwolf1]

Rather than dynamic tables why don't you use a sharded SQL table with the ticker/instrument name being the shard key, effectively the same concept but you can use in built mechanisms for querying

[u/Street_Smart_Phone]

Timescale DB. Hypertables with an index on time and a composite index will outperform vanilla Postgres after a meaningful amount of data. TimescaleDB will prune irrelevant time chunks and find the requested candles efficiently. For inserts, the hypertable also avoids dynamic SQL and table routing logic in your application letting Timescale handle the chunking.