TimescaleDB Primer

1. Hypertables

A hypertable is a virtual view over many smaller chunk tables, transparently partitioned by a time column. From the application side it looks identical to a regular table — same INSERT, same SELECT, same indexes — but writes route to the appropriate chunk and queries prune chunks outside the requested time range. Conversion is a single function call against an existing table.

2. Continuous Aggregates

Continuous aggregates are materialized views that refresh incrementally as new data arrives. Define an hourly or daily rollup once and TimescaleDB keeps it current in the background, so dashboards read from pre-computed buckets instead of scanning raw rows. Pair them with time_bucket() for arbitrary window sizes.

3. Compression & Retention

Older chunks can be transparently compressed columnar-style, typically 10x to 20x smaller than the row-store original, while remaining queryable. Retention policies drop chunks past a configured age. Together they keep storage costs flat even as ingest grows linearly — the standard pattern for production metric pipelines.

Quickstart SQL

-- Install TimescaleDB extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Create a regular Postgres table
CREATE TABLE metrics (
  time        TIMESTAMPTZ NOT NULL,
  device_id   TEXT NOT NULL,
  cpu_usage   DOUBLE PRECISION,
  memory_mb   INTEGER
);

-- Convert to hypertable (partitioned by time)
SELECT create_hypertable('metrics', 'time');

-- Insert sample data
INSERT INTO metrics VALUES
  (NOW(), 'edge-01', 42.7, 2048),
  (NOW(), 'edge-02', 18.3, 1024);

-- Continuous aggregate for hourly rollups
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       device_id,
       AVG(cpu_usage) AS avg_cpu,
       MAX(memory_mb) AS peak_mem
FROM metrics
GROUP BY bucket, device_id;