TimescaleDB

TimescaleDB est une extension PostgreSQL transformant Postgres en time-series database performant. Founded 2017, popular pour combiner SQL standard Postgres complet (joins, transactions, JSONB, PostGIS) avec optimisations time-series (auto-partitioning hypertables, columnar compression, continuous aggregates, retention policies).

Concept central : **Hypertables** — tables looking like regular Postgres tables to users, mais internally partitioned automatiquement par time intervals (chunks). Query planner optimise pruning de chunks non pertinents.

Fonctionnalités clés :
(1) **Hypertables** — `SELECT create_hypertable('metrics', 'time');` transform table classique.
(2) **Continuous aggregates** — materialized views auto-refreshées avec incremental computation (1h data → 1-min averages cached).
(3) **Compression** — columnar compression sur chunks anciens, 90%+ reduction storage typically. `ALTER TABLE metrics SET (timescaledb.compress, timescaledb.compress_segmentby = 'device_id');`.
(4) **Retention policies** — auto-drop old chunks (`SELECT add_retention_policy('metrics', INTERVAL '90 days');`).
(5) **Data tiering** — move old data vers cheap storage (Timescale Cloud bottomless storage).
(6) **Hyperfunctions** — percentile_agg, time_bucket(), first(), last(), histograms, advanced aggregations.
(7) **Spatial integration** native avec PostGIS.
(8) **Standard SQL** — full Postgres compatibility (joins, transactions, JSONB, foreign keys).

Vs Prometheus/InfluxDB :
- **TimescaleDB pros** : full SQL (impossible match), joins avec relational data ("alert when error rate >X AND customer is premium"), Postgres ecosystem (BI tools, ORMs, replication).
- **TimescaleDB cons** : higher write latency that pure Prometheus, less hyper-optimized for specific patterns (Prometheus pull model + label cardinality handling).

Use cases ideal :
(1) **IoT** avec joining device metadata (devices table, customers table, contracts table) + sensor readings.
(2) **DevOps metrics** mais avec besoin SQL queries complex (compliance, business metrics correlated).
(3) **Financial market data** with reference data joins.
(4) **Energy/utility** monitoring with rich relational metadata.
(5) **Web analytics** with full session attribution.

Deployment : Timescale Cloud (managed, multi-cloud), self-hosted (Docker, Kubernetes operator), pre-installed sur RDS/managed Postgres certaines régions. Open source community + commercial features (compression initially Apache 2.0, now part of open source). Compétences DEA-C01, DP-300.