Time-Series Basics

Time-series workloads in PostgreSQL benefit from range partitioning on timestamps, BRIN indexes for append-only scans, and retention policies that detach or drop old partitions. Native partitioning covers many metrics use cases before TimescaleDB.

Recipe

CREATE TABLE metrics (
  device_id   int NOT NULL,
  ts          timestamptz NOT NULL,
  value       double precision NOT NULL
) PARTITION BY RANGE (ts);
 
CREATE TABLE metrics_2026_07 PARTITION OF metrics
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
 
CREATE INDEX metrics_2026_07_device_ts ON metrics_2026_07 (device_id, ts DESC);
 
-- BRIN for large append-only partitions (optional complement)
CREATE INDEX metrics_2026_07_ts_brin ON metrics_2026_07 USING brin (ts);

When to reach for this: High-volume inserts with time-bounded queries (dashboards, IoT, audit metrics) where sequential scans on one giant heap are failing.

Working Example

INSERT INTO metrics (device_id, ts, value)
SELECT (random() * 100)::int,
       timestamptz '2026-07-01' + (g * interval '1 minute'),
       random()
FROM generate_series(0, 10000) g;
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('hour', ts) AS hour,
       avg(value) AS avg_value
FROM metrics
WHERE ts >= timestamptz '2026-07-08'
  AND ts <  timestamptz '2026-07-09'
  AND device_id = 42
GROUP BY 1
ORDER BY 1;
 
-- Retention: drop old month partition
DROP TABLE IF EXISTS metrics_2025_01;

What this demonstrates:

• Partition pruning limits scanned data to July 2026 child table
• Composite (device_id, ts DESC) supports filter + time range
• BRIN optional for very large partitions with time-correlated heap
• DROP TABLE on partition is fast retention vs DELETE millions of rows

Deep Dive

Partitioning Strategy

-- Create next month partition (automate in production)
CREATE TABLE metrics_2026_08 PARTITION OF metrics
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

BRIN on Timestamps

BRIN excels when rows are physically correlated with ts (append-only). Tiny index size vs B-tree.

CREATE INDEX ON metrics_2026_07 USING brin (ts) WITH (pages_per_range = 128);

Combine BRIN with partition pruning: each month partition gets its own BRIN.

B-tree vs BRIN

Often use both: btree on (device_id, ts) for point/range per device, BRIN on ts for wide aggregates if needed.

Timestamptz Discipline

-- Always timestamptz for series (never timestamp without time zone)
ALTER TABLE metrics ALTER COLUMN ts TYPE timestamptz USING ts AT TIME ZONE 'UTC';

Store UTC; convert in application for display.

Gotchas

• Default partition catch-all - Everything lands in DEFAULT, pruning fails. Fix: Monitor partition bounds; alert before DEFAULT fills.
• DELETE retention - Million-row deletes bloat table and WAL. Fix: DROP PARTITION or DETACH + archive.
• Missing future partition - Inserts fail at month boundary. Fix: Automate partition creation 2 months ahead.
• timestamp without time zone - DST bugs in aggregates. Fix: timestamptz only; see defect scenarios article.
• BRIN on random insert order - BRIN useless if heap not correlated. Fix: Append-only load pattern or skip BRIN.
• Global index on parent - Cannot create unique index across all partitions without partition key inclusion (PG 11+ rules). Fix: Include ts in unique keys.

Alternatives

FAQs

Related

• Partitioning Basics - native partitioning
• BRIN - block range indexes
• TimescaleDB - hypertables extension
• Time-Series Best Practices - retention as code
• OLAP Offload ADR - when to leave Postgres

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, and Patroni 3.x.