Install on Debian / Ubuntu

# Add the TimescaleDB apt repo
echo "deb https://packagecloud.io/timescale/timescaledb/debian/ \
      $(lsb_release -cs) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list

curl -L https://packagecloud.io/timescale/timescaledb/gpgkey \
    | sudo apt-key add -

sudo apt update
sudo apt install timescaledb-2-postgresql-16    # match your PG version

# Tune Postgres for the workload — Timescale's tuning tool sets shared_buffers,
# work_mem, max_connections, etc.
sudo timescaledb-tune

sudo systemctl restart postgresql

In any database where you want the extension:

CREATE EXTENSION IF NOT EXISTS timescaledb;
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';

Hypertables

The core abstraction: a hypertable looks like a regular Postgres table but is transparently split into per-time-range chunks. Inserts go into the chunk that owns the row's timestamp; queries automatically use chunk exclusion to skip irrelevant chunks.

CREATE TABLE sensor_readings (
    time        timestamptz NOT NULL,
    sensor_id   bigint      NOT NULL,
    temperature double precision,
    humidity    double precision
);

SELECT create_hypertable('sensor_readings', 'time',
                        chunk_time_interval => INTERVAL '7 days');

-- Index on (sensor_id, time DESC) is the standard pattern
CREATE INDEX ON sensor_readings (sensor_id, time DESC);

Inserts and selects use the same SQL as before:

INSERT INTO sensor_readings (time, sensor_id, temperature, humidity)
VALUES (now(), 1, 21.5, 0.40);

SELECT time, temperature
FROM sensor_readings
WHERE sensor_id = 1
  AND time >= now() - INTERVAL '24 hours'
ORDER BY time DESC;

Continuous aggregates

"Show me hourly averages for the last 7 days" against a 10M-row hypertable is slow. Continuous aggregates pre-compute the result and refresh it automatically:

CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    sensor_id,
    avg(temperature)            AS avg_temp,
    avg(humidity)               AS avg_humidity,
    min(temperature)            AS min_temp,
    max(temperature)            AS max_temp,
    count(*)                    AS sample_count
FROM sensor_readings
GROUP BY bucket, sensor_id
WITH NO DATA;

-- Schedule the auto-refresh
SELECT add_continuous_aggregate_policy('sensor_hourly',
    start_offset => INTERVAL '2 days',
    end_offset   => INTERVAL '1 hour',
    schedule_interval => INTERVAL '15 min');

Now SELECT * FROM sensor_hourly WHERE bucket >= now() - INTERVAL '7 days' is a small index scan instead of a 10M-row aggregation. Updates to the underlying data within the rolling window are recomputed; older buckets stay stable.

Queries also work as "real-time aggregates": for the latest bucket (which the policy hasn't refreshed yet), Timescale computes it on the fly from the underlying hypertable. You always get fresh data.

Compression

Old chunks compress 90–95% with Timescale's columnar compression (rows for a chunk get rewritten as compressed column arrays):

-- Set per-table compression options
ALTER TABLE sensor_readings SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time DESC'
);

-- Schedule auto-compression after 7 days
SELECT add_compression_policy('sensor_readings', INTERVAL '7 days');

Compressed chunks are still queryable — reads decompress on demand. Inserts/updates to compressed chunks are slower (the chunk is decompressed in-memory for the duration of the operation), so the model is "append-mostly, mutate rarely."

Retention

For "keep two years of raw data, forever of aggregates":

-- Drop raw chunks older than 2 years
SELECT add_retention_policy('sensor_readings', INTERVAL '2 years');

-- Keep continuous aggregates forever (no policy = no drop)
-- Or apply a separate, longer retention to the aggregate:
SELECT add_retention_policy('sensor_hourly', INTERVAL '10 years');

Combined: the raw sensor_readings stays small (last 2 years), the hourly aggregate covers everything — useful for "current dashboards" against raw, "year-over-year reports" against the aggregate.

Hyperfunctions

Timescale ships SQL functions tuned for time-series math:

-- First / last in a bucket (PostgreSQL has these now, but Timescale's are faster)
SELECT time_bucket('1 day', time),
       first(temperature, time)  AS opening,
       last(temperature, time)   AS closing,
       max(temperature)          AS high,
       min(temperature)          AS low
FROM sensor_readings
WHERE sensor_id = 1
GROUP BY 1
ORDER BY 1;

-- Gap-filling: rows for buckets with no data become NULL/zero/last-value
SELECT
    time_bucket_gapfill('5 minutes', time) AS bucket,
    sensor_id,
    locf(avg(temperature))                 AS temperature  -- last-observation-carried-forward
FROM sensor_readings
WHERE time >= now() - INTERVAL '1 hour'
  AND time <  now()
GROUP BY bucket, sensor_id
ORDER BY bucket;

The hyperfunctions catalog includes percentile-tracking, frequency-counting (toolkit / approximate sketches), and statistical aggregators.

Where Timescale fits vs alternatives

  • vs Prometheus — Prometheus (see that tutorial) is operationally simpler for metrics scraping, has PromQL, and integrates with the metrics ecosystem. Timescale wins for high-cardinality data, SQL-shaped queries, and durable long-term storage with joins to relational tables.
  • vs InfluxDB — InfluxDB's query language is non-SQL (Flux). Timescale is plain SQL. For mixed time-series + relational workloads, Timescale's "it's just Postgres" advantage compounds.
  • vs ClickHouse — ClickHouse is faster at huge analytical scans, but the operational model is heavier and the SQL dialect is its own. For sub-billion-row workloads where you want to keep one database engine, Timescale wins on operational simplicity.

Backups

Same as any Postgres — pg_dump, pg_basebackup, WAL archiving, or logical replication. Timescale chunks are normal Postgres tables under the hood, so existing backup tooling works unchanged.

Licensing note

The core TimescaleDB extension is Apache 2.0. The Community Edition features (compression, continuous aggregates, retention, gap-filling) ship under the Timescale License (TSL) — free for any use including paid services, except for offering Timescale-as-a-service. Most teams never hit that limit; the open-source-purist alternative is plain Postgres + native partitioning, which works but requires hand-rolling all the above.