Postgres for Time-Series in 2026 — Vanilla vs TimescaleDB vs ClickHouse

Time-series data — metrics, logs, events, IoT — has specific needs: high write rates, time-windowed queries, automatic retention. Postgres can handle it surprisingly far; TimescaleDB extends the runway; ClickHouse takes over at OLAP scale. This post is the decision guide.

Vanilla Postgres + partitioning

For under 100M rows / month, plain Postgres with time partitioning is enough.

CREATE TABLE events (
    id BIGSERIAL,
    ts TIMESTAMPTZ NOT NULL,
    user_id BIGINT,
    payload JSONB
) PARTITION BY RANGE (ts);

CREATE TABLE events_2026_q2 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

Drop old partitions instantly when retention expires. Indexes per partition. Works.

TimescaleDB

Postgres extension. Adds:

• Hypertables: auto-partitioning by time chunks (~7-day chunks default).
• Continuous aggregates: materialized views that auto-refresh.
• Compression: 90%+ size reduction on time-series data.
• Retention policies: drop chunks older than X days automatically.

CREATE TABLE metrics (
    ts TIMESTAMPTZ NOT NULL,
    sensor_id BIGINT,
    value DOUBLE PRECISION
);

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

-- Continuous aggregate
CREATE MATERIALIZED VIEW metrics_5m
WITH (timescaledb.continuous) AS
SELECT time_bucket('5 minutes', ts) AS bucket,
       sensor_id,
       AVG(value) AS avg_value
FROM metrics
GROUP BY bucket, sensor_id;

-- Auto-refresh
SELECT add_continuous_aggregate_policy('metrics_5m',
    start_offset => INTERVAL '1 day',
    end_offset => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes');

-- Compression
ALTER TABLE metrics SET (timescaledb.compress, timescaledb.compress_segmentby = 'sensor_id');
SELECT add_compression_policy('metrics', INTERVAL '7 days');

-- Retention
SELECT add_retention_policy('metrics', INTERVAL '90 days');

For typical time-series workloads (IoT, monitoring, observability), this stack handles billions of rows on a single beefy machine.

ClickHouse

Different category. Columnar OLAP. For:

• Aggregations across years of data.
• Query latencies measured in milliseconds for billion-row scans.
• Heavy analytical workloads that aren’t OLTP-shaped.

Pair with Postgres for OLTP, ClickHouse for analytics. Sync via CDC .

Decision matrix

For most products in 2026: start vanilla; add Timescale when you outgrow; add ClickHouse for analytics.

Common patterns

Downsampling

Keep raw data for 7 days; 5-minute averages for 90 days; hourly averages forever. Continuous aggregates make this automatic.

Bucket queries

SELECT date_trunc('hour', ts) AS hour, COUNT(*)
FROM events
WHERE ts > now() - interval '7 days'
GROUP BY hour
ORDER BY hour;

Vanilla. Works on a partitioned table. With Timescale’s time_bucket, more efficient and flexible.

Late-arriving data

If events can arrive hours after their ts, partitioning still works (Postgres routes by ts, not by current time). Timescale handles this natively.

Common mistakes

1. UUID primary keys

For time-series, sequential IDs (BIGSERIAL or snowflake) cluster better. UUIDs scatter inserts across the index.

2. Indexes on every column

Time-series tables often have one or two access patterns. Index those. Extra indexes cost insert speed.

3. Reading raw data when an aggregate exists

Most queries should hit the rolled-up aggregate, not raw rows. Materialized views or Timescale CAGGs.

4. No retention policy

Storage grows forever. Decide retention up front; enforce via partition drops.

5. Mixing time-series and relational in one table

Big time-series table joined to small relational table is fine. A time-series table with constant updates and joins everywhere is the wrong shape.

Read this next

• Postgres Partitioning in 2026
• Postgres Performance Tuning Cheat Sheet
• DuckDB in Production
• Postgres CDC, Logical Replication, Debezium

If you want my Timescale + Grafana time-series template, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .