Time-Series Databases

Time-series databases are optimized for data that arrives in chronological order — metrics, sensor readings, financial ticks, log entries. While you can store time-series data in PostgreSQL, a dedicated TSDB handles billions of data points with:compression ratios of 10-20x, query speeds 100x faster, and automated data lifecycle management.

---

Why Time-Series Databases

Regular database characteristics:
  - Optimized for random reads/writes
  - Row-level updates common
  - Indexes on arbitrary columns
  
Time-series characteristics:
  - Write-heavy (high ingestion rate)
  - Reads are time-range scans
  - Updates are rare (append-only)
  - Recent data queried more than old data
  - Data has natural expiration (retention)

TSDB optimizations:
  ✓ Columnar storage (compress similar values)
  ✓ Time-based partitioning (efficient range queries)
  ✓ Automatic downsampling (1-second → 1-minute → 1-hour)
  ✓ Built-in retention policies (auto-delete old data)
  ✓ Specialized query language for time operations

---

TSDB Comparison

Database	Type	Best For	Scale
TimescaleDB	PostgreSQL extension	SQL familiarity, relational + time-series	Moderate (TB)
InfluxDB	Purpose-built	DevOps metrics, IoT	High (PB)
Prometheus	Pull-based metrics	Kubernetes monitoring	Moderate
ClickHouse	Column-oriented analytical	Analytics + time-series	Very high (PB)
QuestDB	Purpose-built (SQL)	Low-latency financial data	High
VictoriaMetrics	Prometheus-compatible	Long-term Prometheus storage	Very high (PB)

---

Data Model

-- TimescaleDB: Create hypertable (auto-partitioned by time)
CREATE TABLE metrics (
    time        TIMESTAMPTZ NOT NULL,
    host        TEXT NOT NULL,
    metric_name TEXT NOT NULL,
    value       DOUBLE PRECISION,
    tags        JSONB
);

SELECT create_hypertable('metrics', 'time');

-- Compression policy: Compress chunks older than 7 days
ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'host,metric_name'
);

SELECT add_compression_policy('metrics', INTERVAL '7 days');

-- Retention policy: Drop data older than 90 days
SELECT add_retention_policy('metrics', INTERVAL '90 days');

-- Continuous aggregate (materialized rollup)
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    host,
    metric_name,
    AVG(value) AS avg_value,
    MAX(value) AS max_value,
    MIN(value) AS min_value,
    COUNT(*) AS sample_count
FROM metrics
GROUP BY bucket, host, metric_name;

-- Auto-refresh every hour
SELECT add_continuous_aggregate_policy('metrics_hourly',
    start_offset => INTERVAL '3 hours',
    end_offset   => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour'
);

---

Anti-Patterns

Anti-Pattern	Consequence	Fix
PostgreSQL for high-cardinality metrics	Slow queries, massive storage	Purpose-built TSDB or TimescaleDB
No retention policy	Storage grows forever	Automated retention + tiered storage
No downsampling	Querying years of 1-second data = slow	Continuous aggregates at multiple granularities
String tags with high cardinality	Index explosion, slow writes	Bounded tag values, tag caching
No pre-aggregation	Dashboard queries scan raw data	Materialized views for common queries

Time-series databases trade flexibility for performance. They are not general-purpose databases — they are specialized engines for a specific data pattern. Use the right tool for the right job.