Time-Series Best Practices

How to Use This List

Apply when designing metrics, events, or IoT ingestion tables.
Automate partition and retention jobs before the first production month rollover.
Revisit when data volume 10x or BI users complain about dashboard latency.

A - Schema and Time

Use timestamptz for all event timestamps. Store UTC; convert at display.
Include time in primary access paths. (device_id, ts DESC) btree common pattern.
Partition by time range on high-volume tables. Monthly or daily per ingest rate.
Pre-create partitions two months ahead. Cron or pg_partman; never rely on DEFAULT partition silently.
Avoid timestamp without time zone. DST and session TZ bugs (see defect scenarios).

B - Indexes and Queries

Add BRIN on ts for append-only partitions. Complement, not replace, btree for device+time.
Scope dashboards to time windows. No unbounded SELECT on raw history.
Use continuous aggregates or materialized rollups. Hourly/daily views for BI traffic.
Route long-range BI to warehouse when ADR says so. Postgres hot window only.
EXPLAIN large aggregates before shipping. Confirm partition pruning in plans.

C - Retention

Retention policy as code. DROP TABLE partition or Timescale add_retention_policy.
Prefer DROP/DETACH over DELETE millions of rows. Saves vacuum and WAL pain.
Archive cold partitions to object storage before drop if compliance requires. DETACH then export Parquet.
Document legal hold override. Some partitions frozen; tag in metadata table.
Test restore from archive annually. Cold data is useless if corrupt.

D - Operations

Monitor partition boundary failures. Alert on insert errors at month end.
Track table and index size per partition. Forecast disk before auto-drop surprises.
Run ANALYZE after bulk backfill. Planner needs stats per child partition.
Evaluate Timescale only via allowlist ADR. Preload and license implications.
Revisit OLAP offload ADR at 10x growth. pg_stat_statements evidence bundle.

FAQs

When to start partitioning?

When time-range queries or retention deletes miss SLO; often 50GB+ or high daily insert rate.

DEFAULT partition?

Use only as safety net with monitoring; not primary strategy.

pg_cron for maintenance?

Schedule partition create/drop and MV refresh; extension allowlist required.

BRIN only?

No for device point lookups; yes as helper on time-only scans.

Compression?

Timescale or columnar warehouse; native Postgres toast not a time-series compression strategy.

Read replicas for dashboards?

Yes for hot path; still need rollups for huge scans.

Gap detection?

generate_series for chart continuity; separate monitoring job.

Multi-tenant metrics?

Partition by time first; subpartition or tenant_id index second.

JSONB events?

Extract indexed columns; keep raw jsonb for debugging with TTL.

CI migration checks?

Assert partition DDL and retention job registered in same release.

Time-Series Basics - partition + BRIN
TimescaleDB - hypertables
Continuous Aggregates - rollups
OLAP Offload ADR - ClickHouse/BigQuery
Partitioning Best Practices - native partition hygiene

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.

Time-Series Best Practices

How to Use This List

Apply when designing metrics, events, or IoT ingestion tables.
Automate partition and retention jobs before the first production month rollover.
Revisit when data volume 10x or BI users complain about dashboard latency.

A - Schema and Time

Use timestamptz for all event timestamps. Store UTC; convert at display.
Include time in primary access paths. (device_id, ts DESC) btree common pattern.
Partition by time range on high-volume tables. Monthly or daily per ingest rate.
Pre-create partitions two months ahead. Cron or pg_partman; never rely on DEFAULT partition silently.
Avoid timestamp without time zone. DST and session TZ bugs (see defect scenarios).

B - Indexes and Queries

Add BRIN on ts for append-only partitions. Complement, not replace, btree for device+time.
Scope dashboards to time windows. No unbounded SELECT on raw history.
Use continuous aggregates or materialized rollups. Hourly/daily views for BI traffic.
Route long-range BI to warehouse when ADR says so. Postgres hot window only.
EXPLAIN large aggregates before shipping. Confirm partition pruning in plans.

C - Retention

Retention policy as code. DROP TABLE partition or Timescale add_retention_policy.
Prefer DROP/DETACH over DELETE millions of rows. Saves vacuum and WAL pain.
Archive cold partitions to object storage before drop if compliance requires. DETACH then export Parquet.
Document legal hold override. Some partitions frozen; tag in metadata table.
Test restore from archive annually. Cold data is useless if corrupt.

D - Operations

Monitor partition boundary failures. Alert on insert errors at month end.
Track table and index size per partition. Forecast disk before auto-drop surprises.
Run ANALYZE after bulk backfill. Planner needs stats per child partition.
Evaluate Timescale only via allowlist ADR. Preload and license implications.
Revisit OLAP offload ADR at 10x growth. pg_stat_statements evidence bundle.

FAQs

When to start partitioning?

When time-range queries or retention deletes miss SLO; often 50GB+ or high daily insert rate.

DEFAULT partition?

Use only as safety net with monitoring; not primary strategy.

pg_cron for maintenance?

Schedule partition create/drop and MV refresh; extension allowlist required.

BRIN only?

No for device point lookups; yes as helper on time-only scans.

Compression?

Timescale or columnar warehouse; native Postgres toast not a time-series compression strategy.

Read replicas for dashboards?

Yes for hot path; still need rollups for huge scans.

Gap detection?

generate_series for chart continuity; separate monitoring job.

Multi-tenant metrics?

Partition by time first; subpartition or tenant_id index second.

JSONB events?

Extract indexed columns; keep raw jsonb for debugging with TTL.

CI migration checks?

Assert partition DDL and retention job registered in same release.

Time-Series Basics - partition + BRIN
TimescaleDB - hypertables
Continuous Aggregates - rollups
OLAP Offload ADR - ClickHouse/BigQuery
Partitioning Best Practices - native partition hygiene

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.

Time-Series Best Practices

Search Documentation

Time-Series Best Practices