Modeling Time-Series Data
1. Designing Time-Series Schemas
| Column | Purpose |
|---|---|
| time TIMESTAMPTZ | Sample time (always UTC) |
| entity_id | Series identifier (device, metric) |
| tags (jsonb / cols) | Low-cardinality dimensions |
| value (numeric) | Measurement |
| PK | (entity_id, time) — note order for clustering |
2. Partitioning by Time
| Granularity | Use |
|---|---|
| Daily | High-volume metrics |
| Weekly | Mid-volume |
| Monthly | Lower-volume series |
| Tools | TimescaleDB hypertables, pg_partman, native PG RANGE |
| Benefit | Cheap retention via DROP PARTITION |
3. Implementing Downsampling Strategies
| Approach | Detail |
|---|---|
| Continuous aggregates (Timescale) | Auto-materialized rollups |
| time_bucket('1 hour', ts) | Bucket function |
| Rollup tables | Per-bucket avg/min/max/count |
| Multi-tier | 1s → 1m → 1h → 1d retention pipeline |
| LTTB algorithm | Visual downsampling preserving shape |
4. Designing Data Retention Policies
| Tier | Retention | Resolution |
|---|---|---|
| Hot | 7 days | Raw |
| Warm | 30 days | 1-minute |
| Cool | 1 year | 1-hour |
| Cold | 5+ years | 1-day |
5. Indexing Time-Based Queries
| Index | Use |
|---|---|
| (entity_id, time DESC) | Latest per entity |
| BRIN on time | Tiny size on append-only data |
| Per-partition local indexes | Small + cache-friendly |
| Covering INCLUDE(value) | Index-only scans |
6. Handling Out-of-Order Data
| Challenge | Mitigation |
|---|---|
| Late arrivals | Allow back-dated writes; recompute rollups |
| Watermarks | Process windows once events past watermark |
| Idempotent UPSERT | Tolerate duplicate writes |
| Compaction | Merge late points into bucket aggregates |
7. Implementing Compression Strategies
| Method | Detail |
|---|---|
| Delta + Gorilla (Facebook) | Float compression for metrics |
| TimescaleDB compression | 5–20× on column segments |
| Columnar storage | ClickHouse, Parquet |
| Run-length encoding | For repeating values |
8. Designing Rollup Tables
| Pattern | Detail |
|---|---|
| Time bucket + dimensions | PK: (bucket_ts, entity_id, dim...) |
| Pre-computed aggs | sum, count, min, max, sum_of_squares |
| Re-aggregation | Higher-tier rollups built from lower-tier |
| Backfill | Replay historical raw to rebuild |
9. Managing Hot and Cold Data Storage
| Strategy | Detail |
|---|---|
| Tiered tables | Detach old partition → cold storage |
| S3 + foreign data wrapper | Query cold as external table |
| Columnar archive | Parquet on S3 + Athena/Trino |
| Auto-policy | TimescaleDB add_retention_policy |
10. Optimizing Time-Series Query Patterns
| Pattern | Optimization |
|---|---|
| Latest point per entity | SELECT DISTINCT ON (entity_id) ... ORDER BY time DESC |
| Range scan | WHERE time BETWEEN ... + partition pruning |
| Aggregation | Query rollup tier matching granularity |
| Gap-fill | generate_series + LEFT JOIN |
| Last-observation-carried-forward | LAG / window functions |