Efficient Time Series with PostgreSQL
summary
Argues PostgreSQL handles high‑volume time series by using a normalized schema (metrics table + values table), JSONB + GIN for tags, and a composite B‑tree index (metric_id, timestamp) to avoid expensive bitmap scans; ingestion uses a triggered roll‑up with INSERT ... ON CONFLICT DO UPDATE to maintain pre‑aggregated buckets (e.g., 10× reduction) so range queries stay under ~100 ms even as raw rows reach tens or hundreds of millions; recommends partitioning for fast retention and further scaling.
AI-written summary · about
chapters (17)
- 0:00Introduction
- 3:20Use Case Monitoring
- 4:53Common Components
- 8:24Prometheus
- 13:55Time Series
- 19:13Mapping Time Series
- 25:41Indexing
- 26:31Btrees
- 28:59Index
- 30:31Data Volume
- 31:36Adding Indexes
- 32:41Normalisation
- 37:05Index Scan
- 40:09Increasing Data Volume
- 48:51Clearing a billion rows
- 50:32Partitioning
- 51:39Splitting by Metric
related talks
51mPostgreSQL, The Time-Series Database You Want
51mBuilding a scalable time-series database on PostgreSQL
49mPostgreSQL, The Time-Series Database You Actually Want
39mTimescaleDB: Re-engineering PostgreSQL as a time-series database
43mJust Use Postgres.
25mData-intensive PostgreSQL: Three ways to scale
42mAccessible Time Series data
30mIngesting A Million Time Series Per Second On A Single Instance