Time-Series Data in PostgreSQL Without TimescaleDB

The moment a SaaS starts collecting metrics — device telemetry, user events, API latencies — someone suggests reaching for TimescaleDB or standing up a ClickHouse cluster. Usually too early. Adding a heavy extension complicates managed-cloud deployments, can stall Postgres version upgrades, and tangles your backups, all to solve a scale problem you don't have yet. The truth is that time series PostgreSQL done natively handles hundreds of millions of append-only rows just fine — the unlock isn't a different database, it's structuring the table to match how the data actually behaves.

Here's the whole approach: a composite primary key led by time, declarative range partitioning, lean composite and BRIN indexes, generate_series for gapless charts, and retention by dropping partitions instead of deleting rows. All native, all boring, all the reason you can skip the extension until you genuinely outgrow it. Here are the seven rules.

Time Series PostgreSQL Rule 1: Append-Only, Time-Led Key

Time-series tables are append-only — written once, almost never updated — so keep rows small and lead the key with the timestamp. Never use a random UUIDv4 as the key: it scatters writes across the whole index and wrecks cache locality as the table grows. A composite key that doubles as the partition key:

1CREATE TABLE device_metrics (
2    recorded_at TIMESTAMPTZ NOT NULL,
3    tenant_id   UUID NOT NULL,
4    device_id   VARCHAR(64) NOT NULL,
5    metric_name VARCHAR(50) NOT NULL,
6    metric_value DOUBLE PRECISION NOT NULL,
7    PRIMARY KEY (recorded_at, tenant_id, device_id)
8) PARTITION BY RANGE (recorded_at);

If you need a surrogate key, use a time-ordered UUIDv7 so new rows still land at the hot end of the index instead of randomly.

Rule 2: Range Partition by Time

Past tens of millions of rows, a single table's indexes stop fitting in RAM and performance falls off a cliff as Postgres swaps pages to disk. Declarative range partitioning splits the logical table into time-bucketed child tables:

1CREATE TABLE metrics_y2026m06 PARTITION OF device_metrics
2    FOR VALUES FROM ('2026-06-01+00') TO ('2026-07-01+00');
3
4CREATE TABLE metrics_y2026m07 PARTITION OF device_metrics
5    FOR VALUES FROM ('2026-07-01+00') TO ('2026-08-01+00');

When a query filters by date, partition pruning skips every child table outside the range, so you scan a small slice instead of the whole dataset — and each partition's indexes stay small enough to cache.

Rule 3: Keep Indexes Lean (and Consider BRIN)

Every index is a write tax on an append-heavy table, so don't index every column — the same query-and-index discipline that fixes a slow dashboard applies here. Build a composite B-tree that matches how you actually query:

1CREATE INDEX idx_metrics_lookup ON device_metrics (tenant_id, metric_name, recorded_at DESC);

And for ultra-high-volume tables where rows arrive in timestamp order, evaluate a BRIN index. BRIN stores only the min/max value per block range, so it's a tiny fraction of a B-tree's size — ideal when the physical order already matches the time column.

Rule 4: Bucket With DATE_TRUNC

Charts need raw logs rolled into consistent buckets. Native date truncation does it:

1SELECT DATE_TRUNC('day', recorded_at) AS day,
2       metric_name,
3       AVG(metric_value) AS avg_value,
4       COUNT(*) AS points
5FROM device_metrics
6WHERE tenant_id = $1 AND recorded_at >= $2
7GROUP BY day, metric_name
8ORDER BY day DESC;

This is the same bucketing your reporting engine leans on — keep it on the read replica when the volume's heavy.

Rule 5: Fill Chart Gaps With generate_series

A device that goes quiet between 2am and 5am produces no rows, and a plain GROUP BY omits those buckets — leaving holes in the chart. Build a gapless timeline and LEFT JOIN onto it:

1WITH timeline AS (
2    SELECT generate_series(
3        '2026-06-29 00:00+00'::timestamptz,
4        '2026-06-29 23:00+00'::timestamptz,
5        '1 hour'
6    ) AS hour
7)
8SELECT t.hour, COALESCE(AVG(m.metric_value), 0) AS value
9FROM timeline t
10LEFT JOIN device_metrics m
11       ON DATE_TRUNC('hour', m.recorded_at) = t.hour AND m.tenant_id = $1
12GROUP BY t.hour
13ORDER BY t.hour;

COALESCE defaults the empty buckets to zero, so the frontend draws a continuous line instead of vanishing where the data went quiet.

Rule 6: Retention by Dropping Partitions

Here's where partitioning really pays off. Running DELETE WHERE recorded_at < NOW() - INTERVAL '90 days' on a huge table is hours of WAL churn, bloat, and locks — we once watched a one-line migration lock a production table for 45 minutes during business hours, and a bulk delete is that, deliberately. With partitions, retention is metadata:

1ALTER TABLE device_metrics DETACH PARTITION metrics_y2026m01;
2DROP TABLE metrics_y2026m01;

Near-instant, no row-by-row overhead, disk freed immediately — the same "work at the table level, not the row level" discipline behind zero-downtime migrations.

Rule 7: Roll Up Before You Drop

If you need the history but not the granularity, summarize a partition before dropping it:

1INSERT INTO daily_rollups (day, tenant_id, metric_name, avg_value)
2SELECT DATE_TRUNC('day', recorded_at), tenant_id, metric_name, AVG(metric_value)
3FROM metrics_y2026m01
4GROUP BY 1, 2, 3;
5
6DROP TABLE metrics_y2026m01;

You keep long-term trends in a tiny summary table and reclaim the bulk of the storage. (Automate partition creation ahead of time with pg_partman or a small cron worker — Postgres prunes automatically but won't create next month's table for you.)

When to Actually Leave Native PostgreSQL

The honest read: partition, index leanly, and drop old partitions, and native Postgres carries your metrics far longer than the "you need a time-series database" crowd will admit. Reach for TimescaleDB when you've genuinely outgrown this — billions of rows, heavy columnar compression — not because a benchmark blog made you nervous at ten million. Pick the boring database you already run; future-you, not babysitting an extension through a major-version upgrade, will be grateful.