Partitions¶

The events table is range-partitioned by timestamp on a monthly cadence. This was introduced in migration 0013 to handle high-ingestion workloads.

Why partition?¶

Benefit	Mechanism
10–100× faster queries	Partition pruning skips irrelevant months
Instant retention	`DROP TABLE events_2026_01` instead of `DELETE`
Smaller indexes	Indexes are per-partition
Better vacuum	Tuned autovacuum per partition

Layout¶

CREATE TABLE events (
    id BIGSERIAL,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT now(),
    … ,
    PRIMARY KEY (id, timestamp)
) PARTITION BY RANGE (timestamp);

Monthly partitions:

CREATE TABLE events_2026_08 PARTITION OF events
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

The pipeline creates 13 future partitions ahead of time during migration and on startup, so there is no lag when the month rolls over.

Partition Pruning¶

The API always bounds queries by time (default: last 30 days). Example access path:

EXPLAIN SELECT * FROM events
WHERE timestamp >= '2026-08-01' AND timestamp < '2026-09-01'
  AND severity = 'ERROR';

Seq Scan on events_2026_08 events  (cost=…)
  Filter: (severity = 'ERROR'::text)

Only events_2026_08 is scanned — no full-table scan.

Composite Primary Key¶

Because the partition column must be part of the primary key, the PK is (id, timestamp) instead of (id). Lookups by id alone therefore also filter on time.

Retention / Purging¶

# Drop an entire month instantly
psql -c "DROP TABLE events_2026_01;"

Retention scripts (backend/scripts/retention.py) list partitions and drop those older than the retention window.

Auto-Partition Maintenance¶

Migration 0013 pre-creates 13 months
A startup task in the pipeline recreates/extends the horizon if needed
autovacuum_vacuum_scale_factor = 0.01 per partition keeps tables healthy under load

Monitoring¶

-- Current partitions
SELECT child.relname, pg_size_pretty(pg_total_relation_size(child.oid))
FROM pg_inherits JOIN pg_class child ON inhrelid = child.oid
WHERE inhparent = 'events'::regclass
ORDER BY child.relname;

Indexes — indexes inherited per partition
Migrations — revision 0013 implementation