Database Design • Time-Series Databases (InfluxDB, TimescaleDB)Medium⏱️ ~3 min
Hot Warm Cold Tiering: Balancing Query Speed and Storage Cost
Time series data value decays with age. Metrics from the last hour power real time dashboards and alerts requiring millisecond latency. Data from last month serves historical analysis tolerating seconds of latency. Data older than a year rarely gets queried but must remain available for compliance. Hot warm cold tiering exploits this pattern to optimize both performance and cost.
The hot tier keeps recent data (hours to days) in memory or fast SSD with row oriented or memory mapped formats enabling sub 10 millisecond queries and rapid updates. Netflix Atlas targets sub second dashboard queries over recent windows by keeping hot data fully in memory with aggressive rollups. The warm tier stores weeks to months of data in columnar formats on local SSD, balancing 10 to 100 millisecond query latency with lower cost per gigabyte. The cold tier archives older data to object storage like Amazon S3 or Google Cloud Storage (GCS) in highly compressed Parquet files where queries take seconds but storage costs drop to roughly 1 to 2 cents per gigabyte per month versus dollars per gigabyte for memory.
Lifecycle policies automate transitions. TimescaleDB compression policies convert row oriented hot chunks to columnar compressed format after a configured age, achieving 10x compression while queries transparently span both formats. Retention policies delete data exceeding maximum age or move it to cheaper tiers. InfluxDB 3.x persists columnar files to object storage with metadata cached locally, querying cold data by downloading only relevant Parquet files based on time and tag predicates.
The architecture requires federation logic to query across tiers. A query for the last 30 days might hit hot memory for the last day, warm SSD for the last week, and cold object storage for the remainder. Query planners push down filters and partial aggregations to minimize data movement. Continuous aggregates further optimize by precomputing rollups, so queries for hourly averages over 6 months read compact aggregated data rather than billions of raw points. The trade-off is complexity in the query layer and some freshness lag in cold tiers, but the cost savings reach 10 to 100x compared to keeping everything hot.
💡 Key Takeaways
•Hot tier uses memory or fast SSD with row formats for sub 10 millisecond queries over recent hours to days costing roughly 100 dollars per gigabyte per month, while cold tier uses object storage with Parquet at 1 to 2 cents per gigabyte per month for 10 to 100x cost savings
•Lifecycle policies automatically transition data by age, with TimescaleDB compressing hot row chunks to columnar format achieving 10x space reduction while maintaining transparent query access across both formats
•Netflix Atlas keeps hot data fully in memory targeting sub second query Service Level Objectives (SLOs) for real time dashboards by using client side aggregation and aggressive rollups before central storage
•Query federation pushes down filters and partial aggregations to each tier minimizing data movement, so a 30 day query reads compact aggregates from cold storage rather than billions of raw points
•Continuous aggregates precompute rollups like hourly averages enabling queries over months to complete in milliseconds by reading compact materialized views instead of scanning raw data
📌 Examples
InfluxDB 3.x persists columnar Parquet files directly to S3 with local metadata caching. Queries download only relevant files based on time range and tag predicates, achieving seconds latency on cold data versus milliseconds on hot.
A financial services firm keeps 1 second granularity hot for 7 days on SSD for real time fraud detection, 1 minute warm for 90 days for operational dashboards, and 1 hour cold indefinitely on S3 for regulatory compliance at 100x lower storage cost.
Datadog implements multi tier retention where customers pay based on granularity and retention duration. High resolution metrics cost more but serve fast dashboards, while downsampled historical data reduces cost for long term trend analysis.