How should crypto candlestick data be stored?

Good question — this is a data-engineering design problem with several real trade-offs. Here's the honest landscape:

The core tension

Candlestick data is append-only (history never changes once a bar closes) but gets queried repeatedly for charting, backtesting, and indicators. So the "correct" storage depends on whether you prioritize write throughput (ingesting live ticks) or read performance (serving charts fast).

Options, in rough order of where they fit

1. Columnar/OLAP (Parquet + object storage, or ClickHouse/DuckDB) The best default for analytics workloads. Candle data compresses extremely well column-wise (OHLCV repeats patterns), and columnar engines let you scan millions of bars in milliseconds. For backtesting and research, Parquet files partitioned by symbol+date are cheap and standard.

• Best when: backtesting, research, big historical datasets.

2. Time-series databases (TimescaleDB, InfluxDB) Optimized for exactly this shape — time as the primary index, continuous aggregates for pre-rolled higher timeframes. TimescaleDB (Postgres extension) is a popular middle ground because you keep SQL.

• Best when: you need live ingestion and flexible queries, moderate scale.

3. Plain relational (Postgres/MySQL) Fine at small-to-medium scale. A candles table with a composite primary key of (symbol, timeframe, open_time) and a UNIQUE constraint for upserts. It degrades as rows grow into the billions, which is why most serious systems move off it.

• Best when: small datasets, single-digit symbols, you want simplicity.

4. Redis/other caches in front of any of the above Candles are read far more often than written, so a hot cache (last N candles per symbol/timeframe) massively cuts DB load. This is almost always worth doing regardless of backing store.

Key design decisions that matter more than the engine

• Store open time, not close time as the canonical timestamp (industry convention — the candle represents the interval starting at that time).
• Upsert by (symbol, timeframe, open_time) — a just-closed candle gets corrected a few times by exchanges before finalizing, so you need idempotent writes, not pure appends.
• Don't build higher timeframes on the fly — pre-aggregate them into separate tables/partitions. Reading 12 hourly bars to draw a daily candle is wasteful.
• Keep ticks separate from candles. Store raw trades as an append-only event log (for rebuilding or tick-accurate backtests), and candles as the derived, aggregated layer. Same for funding rates / open interest — separate tables.
• Compress aggressively. Candle data is near-perfectly repetitive; dictionary/run-length encoding typically crushes it 10–20×.
• Store the exchange + symbol explicitly, not just a ticker — the same "BTC/USDT" differs across venues.

My pragmatic take

For most projects: Parquet in object storage for historical/research + TimescaleDB (or Postgres) for the live, queryable layer + Redis in front. If you're building a backtester/researcher, lean columnar. If you're building a live charting product, lean time-series with aggressive pre-aggregation and caching.

There's no single right answer — it's dictated by whether your workload is write-heavy (live ingestion) or read-heavy (serving charts/backtests), and by your scale. Want me to look up which crypto data APIs or exchanges expose the cleanest historical candle endpoints for whatever you're building?