Your events table hit 800 million rows and every query planner decision now involves scanning billions of index entries. VACUUM takes 6 hours. Dropping old data means a table lock. PostgreSQL declarative partitioning was built for exactly this — here's how to implement it correctly before your table becomes unmanageable.
- PostgreSQL declarative partitioning (PG 10+) splits one logical table into physical child tables
- Range partitioning by date is ideal for time-series and event data
- Constraint exclusion and partition pruning mean queries only scan relevant partitions
- Attach/detach partitions for zero-downtime old data archival and purging
PostgreSQL Partitioning Strategies
Range Partitioning (Most Common)
CREATE TABLE events (
id bigserial,
user_id bigint NOT NULL,
event_type text NOT NULL,
payload jsonb,
created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2025_01
PARTITION OF events
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02
PARTITION OF events
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- Index applies to all partitions (PG 11+)
CREATE INDEX ON events (created_at, user_id);Hash Partitioning (Horizontal Sharding)
CREATE TABLE user_events (
user_id bigint NOT NULL,
event_type text NOT NULL,
created_at timestamptz NOT NULL
) PARTITION BY HASH (user_id);
CREATE TABLE user_events_p0 PARTITION OF user_events
FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE user_events_p1 PARTITION OF user_events
FOR VALUES WITH (MODULUS 8, REMAINDER 1);
-- repeat for p2 through p7Automating Partition Creation with pg_partman
CREATE EXTENSION pg_partman SCHEMA partman;
SELECT partman.create_parent(
p_parent_table => 'public.events',
p_control => 'created_at',
p_interval => '1 month',
p_premake => 3
);
UPDATE partman.part_config
SET retention = '12 months',
retention_keep_table = false,
automatic_maintenance = 'on'
WHERE parent_table = 'public.events';
-- Schedule via pg_cron
SELECT cron.schedule(
'partition-maintenance',
'0 0 * * *',
'SELECT partman.run_maintenance_proc()'
);Query Performance with Partitioning
Verifying Partition Pruning
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), event_type
FROM events
WHERE created_at BETWEEN '2025-06-01' AND '2025-06-30'
GROUP BY event_type;
-- Good: "Partitions selected: 1 out of 18"
-- Bad: all partitions scanned
-- WRONG (breaks pruning):
-- WHERE created_at::date = '2025-06-01'
-- RIGHT:
-- WHERE created_at >= '2025-06-01' AND created_at < '2025-06-02'Implicit type casts on the partition key column defeat partition pruning. If created_at is timestamptz, always compare it with timestamptz literals — not with ::date casts or DATE_TRUNC() on the column side.
Zero-Downtime Data Archival
-- Detach partition without dropping data (instant)
ALTER TABLE events DETACH PARTITION events_2024_01 CONCURRENTLY;
-- Drop old data instantly (no row-by-row delete)
DROP TABLE events_2024_01;
-- Or attach to archive table
ALTER TABLE events_archive ATTACH PARTITION events_2024_01
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');Use DETACH PARTITION ... CONCURRENTLY (PostgreSQL 14+) to detach without acquiring an ACCESS EXCLUSIVE lock on the parent table. Safe to run during business hours.
- Declarative range partitioning by date is the standard approach for time-series and event tables exceeding 100M rows.
- Use pg_partman to automate partition creation and retention — manually managing partitions at scale is error-prone.
- Avoid type casts on the partition key in WHERE clauses — they disable partition pruning and cause full-table scans.
- DETACH PARTITION CONCURRENTLY (PG 14+) enables zero-downtime archival without locking the parent table.
Working with JusDB on PostgreSQL Partitioning
JusDB designs and implements PostgreSQL partitioning strategies for engineering teams dealing with billion-row tables. We handle pg_partman configuration, partition pruning validation, and zero-downtime migration from unpartitioned tables.
Explore JusDB PostgreSQL Services → | Talk to a DBA
Related reading: