Home / Databases / PostgreSQL

DatabasesHow-toAdvanced

Optimization: PostgreSQL declarative partitioning for large tables - pruning, fast purges and easier vacuum

Huge time-series and event tables make vacuum slow, purges painful and indexes bloated. Partition by time with declarative partitioning, automate partition creation, drop or detach old partitions instead of deleting rows, and migrate existing tables with minimal downtime.

Deepak Bhardwaj2 min read

Applies toPostgreSQL 13 and later (self-managed and managed services); pg_partman extension where available

When to partition

Signal Benefit
Table hundreds of GB+, growing steadily Smaller per-partition indexes and vacuum
Queries mostly filter by time range Partition pruning
Retention purge with DELETE DROP/DETACH PARTITION instead
Autovacuum can't keep up on one giant table Vacuum works per partition

Don't partition small tables or tables queried mostly without the partition key; planning overhead can make things slower.

Create a partitioned table

CREATE TABLE events (
  event_id   bigint GENERATED ALWAYS AS IDENTITY,
  created_at timestamptz NOT NULL,
  device_id  bigint NOT NULL,
  payload    jsonb,
  PRIMARY KEY (event_id, created_at)        -- PK must include the partition key
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_10 PARTITION OF events
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE events_default PARTITION OF events DEFAULT;

CREATE INDEX ON events (device_id, created_at);   -- created on each partition

Automate partition management

pg_partman (where available) creates future partitions and handles retention:

CREATE EXTENSION IF NOT EXISTS pg_partman;
SELECT partman.create_parent(p_parent_table => 'public.events',
                             p_control => 'created_at',
                             p_interval => '1 month',
                             p_premake => 3);
-- run maintenance on a schedule (pg_cron or external scheduler)

Check the pg_partman version on your platform; function signatures differ between major versions. Without it, a scheduled job that creates next month's partition works too.

Purge by partition

ALTER TABLE events DETACH PARTITION events_2024_01 CONCURRENTLY;
DROP TABLE events_2024_01;    -- or archive first

Migrating an existing big table

Approach Notes
Create new partitioned table, copy in batches, swap names Needs a short write freeze or dual-write/trigger for changes
Attach the old table as a single historic partition Quick start; new data goes to new partitions; old partition shrinks over time with purges
Logical replication into the new structure Minimal downtime for large tables

Attaching an existing table needs a CHECK constraint matching the range first, so the attach doesn't scan the whole table under lock.

Verify pruning

EXPLAIN SELECT count(*) FROM events WHERE created_at >= now() - interval '1 day';
-- plan should show only recent partitions

Tip

Keep the number of partitions reasonable (hundreds, not tens of thousands). Monthly or weekly partitions usually balance pruning benefit and planning cost.

Checklist

  • Partition key matches query filters and retention
  • Primary/unique keys include the partition key
  • Future partitions created automatically
  • Purges by detach/drop
  • Migration approach tested
  • Pruning verified with EXPLAIN

Related fixes