TimescaleDB — Time-Series Database Built on PostgreSQL

Introduction

TimescaleDB is what happens when a team asks "what if time-series had all of PostgreSQL behind it?" The answer is an extension that transforms Postgres into a first-class time-series database — with chunking, compression, continuous aggregates, retention policies, and Postgres features (JSONB, full-text search, PostGIS) all in one engine.

With over 22,000 GitHub stars, TimescaleDB is used by Bloomberg, Walmart, Toyota, and thousands of IoT/observability teams. It's the path of least friction for anyone who already knows SQL and doesn't want to stand up a dedicated TSDB.

What TimescaleDB Does

TimescaleDB adds hypertables — logical tables auto-partitioned into chunks by time (and optionally space). Queries look exactly like regular SQL; under the hood, chunks are skipped via time-range pruning. Continuous aggregates materialize pre-computed rollups. Columnar compression (ALTER TABLE ... SET (timescaledb.compress)) shrinks old chunks by 90%+.

Architecture Overview

[PostgreSQL]
   + TimescaleDB extension
        |
  [Hypertable abstraction]
  one table name, N chunks
   chunk_1 (Jan 1–7)
   chunk_2 (Jan 8–14)
   chunk_3 (Jan 15–21)  <-- compressed, columnar
   chunk_4 (Jan 22–28)  <-- hot, row-store
        |
  [Query Planner]
   time-range pruning + chunk exclusion
        |
  [Continuous Aggregates]
   materialized views of rollups
   refresh_policy keeps them fresh
        |
  [Retention + compression policies]
   drop chunks > 30 days, compress > 7 days

Self-Hosting & Configuration

-- Continuous aggregate: hourly avg from raw metrics
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  host,
  metric,
  AVG(value) AS avg_value,
  MAX(value) AS max_value
FROM metrics
GROUP BY bucket, host, metric;

SELECT add_continuous_aggregate_policy('metrics_hourly',
  start_offset => INTERVAL '1 day',
  end_offset   => INTERVAL '1 hour',
  schedule_interval => INTERVAL '15 minutes'
);

-- Compression policy: compress chunks older than 7 days
ALTER TABLE metrics SET (timescaledb.compress,
  timescaledb.compress_segmentby = 'host, metric',
  timescaledb.compress_orderby   = 'time DESC'
);
SELECT add_compression_policy('metrics', INTERVAL '7 days');

-- Retention policy: drop chunks older than 1 year
SELECT add_retention_policy('metrics', INTERVAL '1 year');

Key Features

• PostgreSQL superset — all Postgres features, drivers, tools work
• Hypertables — auto-partitioning by time (and space)
• Continuous aggregates — incrementally maintained rollups
• Columnar compression — 90%+ size reduction for old data
• Retention policies — drop old chunks automatically
• Hyperfunctions — percentile_approx, time_bucket, gap filling
• PostGIS + JSONB — mix time-series with geo and document data
• Replication + HA — Patroni, Timescale Cloud, logical replication

Comparison with Similar Tools

FAQ

Q: TimescaleDB vs InfluxDB — which should I use? A: TimescaleDB if you value SQL, joins with relational data, and the Postgres ecosystem (drivers, BI tools, Postgres extensions). InfluxDB if you want a purpose-built TSDB with its own query language and are happy outside Postgres.

Q: Does Timescale slow down regular Postgres queries? A: No. Non-hypertables use Postgres's regular planner. Hypertables add overhead only for time-series tables, and typically speed up time-range queries significantly via chunk exclusion.

Q: Can I use Timescale on managed Postgres (RDS, Cloud SQL)? A: AWS RDS and GCP Cloud SQL do not offer the Timescale extension. Use Timescale Cloud (managed) or self-host on EC2/GCE. Or pick a managed Postgres that includes Timescale (e.g., Aiven).

Q: Is TimescaleDB open source? A: Yes. Apache-2.0 core ("Community Edition") plus some features (multi-node, gapfilling, etc.) under TSL (source-available). Community Edition is sufficient for most teams.

Sources

• GitHub: https://github.com/timescale/timescaledb
• Docs: https://docs.timescale.com
• Company: Timescale
• License: Apache-2.0 (core) + TSL (extras)