PostgreSQL table partitioning splits one logical table into many physical tables, called partitions, based on a key such as a date. Applications still query one table, and PostgreSQL routes rows and queries to the right partitions. On a multi-terabyte events or audit table it is often the difference between a nightly DELETE that runs for hours and a DROP TABLE that takes a second.
It is not a general speed-up, though. Partitioning adds planning work and puts hard limits on unique constraints. This guide covers when it is worth it, how to pick the key, how to run it day to day without long locks, and how to convert a table that already holds billions of rows.
When partitioning is worth it
The PostgreSQL documentation gives a rule of thumb: the benefits are normally worthwhile only when a table would otherwise be very large, roughly when it exceeds the physical memory of the server. In our experience the real triggers are more specific:
- Retention. You keep 13 months of data and delete the oldest month every month. Dropping a partition avoids the huge
DELETE, the WAL it generates and the bloat it leaves behind. - Most queries hit recent data. If almost every query filters on the last day or week, the hot partitions and their indexes stay in memory while old ones sit on disk.
- Maintenance on one huge table hurts. Vacuum, index rebuilds and anti-wraparound work run per partition, so each run is smaller and can finish. We cover why big tables suffer in our autovacuum tuning guide.
If none of these apply, a well-indexed single table is usually simpler and just as fast.
Choosing the partition key and method
PostgreSQL supports three methods. Range partitions by intervals, almost always time. List partitions by explicit values such as region or tenant group. Hash spreads rows by modulus and remainder when there is no natural range but you want smaller pieces.
The key should be in the WHERE clause of most queries. Partition pruning only works when the planner or executor can tell from the query which partitions are relevant. A query that does not filter on the key has to look at every partition, and with hundreds of partitions that is slower than one big table.
CREATE SEQUENCE events_id_seq;
CREATE TABLE events (
id bigint NOT NULL DEFAULT nextval('events_id_seq'),
created_at timestamptz NOT NULL,
account_id bigint NOT NULL,
payload jsonb
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_11 PARTITION OF events
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE TABLE events_2026_12 PARTITION OF events
FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');
CREATE INDEX ON events (account_id, created_at);
Range bounds are inclusive at the lower end and exclusive at the upper end, so '2026-12-01' belongs to the December partition. An index created on the parent is created on every partition automatically. We use a plain sequence default here because identity columns on partitioned tables are only allowed since PostgreSQL 17.
PRIMARY KEY (id) is not possible on a table partitioned by created_at; it has to be (id, created_at). PostgreSQL then enforces uniqueness of the pair, not of id alone. Decide early whether a sequence-generated id is enough for you, or whether the application must look rows up by id without knowing the date.Creating partitions without blocking the table
CREATE TABLE ... PARTITION OF takes an ACCESS EXCLUSIVE lock on the parent. On a busy table that can queue every query behind one long transaction. The documentation describes a gentler route: create the table on its own, add a CHECK constraint that matches the partition bounds and then attach it. ATTACH PARTITION needs only SHARE UPDATE EXCLUSIVE on the parent, and the matching constraint lets PostgreSQL skip the validation scan.
-- Create next month's partition as a plain table first
CREATE TABLE events_2027_01 (LIKE events INCLUDING DEFAULTS);
ALTER TABLE events_2027_01 ADD CONSTRAINT events_2027_01_range
CHECK (created_at >= '2027-01-01' AND created_at < '2027-02-01');
-- Attach: SHARE UPDATE EXCLUSIVE on the parent, no validation scan
SET lock_timeout = '5s';
ALTER TABLE events ATTACH PARTITION events_2027_01
FOR VALUES FROM ('2027-01-01') TO ('2027-02-01');
ALTER TABLE events_2027_01 DROP CONSTRAINT events_2027_01_range;
Create partitions days or weeks before they are needed, not at midnight when the first row arrives. Without a matching partition, an insert fails with an error. Tools such as pg_partman automate this; whatever you use, alert when the newest partition is less than a few days ahead.
Be careful with a DEFAULT partition. It catches rows that fit nowhere else, which sounds safe, but attaching a new partition then has to scan the default partition under an ACCESS EXCLUSIVE lock unless a CHECK constraint rules the new range out. It also blocks DETACH ... CONCURRENTLY, which is not allowed when the table has a default partition. We prefer creating partitions in time and alerting, and keeping the default partition empty or absent.
Indexes on a partitioned table
CREATE INDEX on the parent cannot use CONCURRENTLY, so on a large table it would lock writes for the whole build. The documented workaround is to create the parent index with ON ONLY, build each partition's index concurrently and attach them:
CREATE INDEX events_payload_idx ON ONLY events USING gin (payload);
CREATE INDEX CONCURRENTLY events_2026_11_payload_idx
ON events_2026_11 USING gin (payload);
ALTER INDEX events_payload_idx ATTACH PARTITION events_2026_11_payload_idx;
-- repeat per partition; the parent index becomes valid when all are attached
The parent index stays invalid until every partition has an attached index, then PostgreSQL marks it valid automatically.
Dropping old data
This is where partitioning pays for itself. Since PostgreSQL 14 a partition can be detached without blocking queries on the parent:
ALTER TABLE events DETACH PARTITION events_2025_10 CONCURRENTLY;
-- archive it if needed (pg_dump -t events_2025_10), then:
DROP TABLE events_2025_10;
DETACH ... CONCURRENTLY cannot run inside a transaction block. If it is interrupted, finish it with DETACH PARTITION ... FINALIZE. The documentation notes that only one partition per table can be pending detach at a time.
Converting an existing large table
PostgreSQL cannot turn a regular table into a partitioned one in place. You can, however, attach an existing table as a partition. That gives a practical path for a live table without copying billions of rows up front:
- Create the new partitioned table
events_newwith the same columns and future partitions, starting at the cut-over date. - Create indexes on the parent that match the indexes already on the old table. When you attach, the documentation says a valid equivalent index on the partition is reused; otherwise a new one is built, which you do not want on a huge table.
- Add a
CHECKconstraint on the old table that proves its range, asNOT VALIDfirst, then validate it. Validation scans the table but does not block reads and writes. - In one short transaction with a low
lock_timeout, rename the tables and attach the old one as a single big partition.
-- 1. Prove the range on the old table without blocking writes
ALTER TABLE events_legacy ADD CONSTRAINT events_legacy_range
CHECK (created_at IS NOT NULL AND created_at < '2026-12-01') NOT VALID;
ALTER TABLE events_legacy VALIDATE CONSTRAINT events_legacy_range;
-- 2. Short switch: rename, attach the old table as one big partition
BEGIN;
SET LOCAL lock_timeout = '5s';
ALTER TABLE events_legacy RENAME TO events_old;
ALTER TABLE events_new RENAME TO events;
ALTER TABLE events ATTACH PARTITION events_old
FOR VALUES FROM (MINVALUE) TO ('2026-12-01');
COMMIT;
New rows now go to the monthly partitions, and the old table ages out as one block when its data passes the retention window. Check the details on a copy first: sequence ownership and column defaults, foreign keys pointing at the table (the documentation says they are not considered when attaching), views and permissions all need attention. The general technique of short lock windows is covered in our zero-downtime migrations guide.
What changes on large databases
- Partition count is a real limit. The documentation says the planner handles up to a few thousand partitions fairly well, as long as typical queries prune all but a few. Too many partitions mean longer planning and more memory per query. Daily partitions kept for ten years are 3650 tables; monthly would be 120.
- Statistics on the parent need manual care. According to the vacuuming documentation, autovacuum does not process partitioned tables and does not run
ANALYZEon them. Partitions are analyzed normally, but plans that use statistics of the whole table can suffer. RunANALYZEon the parent after big loads and on a schedule. - Queries without the key get worse. Audit your top queries in
pg_stat_statementsbefore you partition. Anything that looks up byidalone will scan an index in every partition. - Locks still matter at scale. Attach, detach and partition creation all take locks somewhere. Run them with a low
lock_timeoutand retry logic, and check for long-running transactions first. - Old partitions become cheap to keep. Once an old partition stops changing and has been vacuumed and frozen, it costs little in maintenance. Big archive partitions can even move to cheaper storage with a tablespace, if your setup supports it.
- Test the conversion end to end. On multi-terabyte tables the
VALIDATE CONSTRAINTscan alone can take hours. Measure it on a restored copy, together with the rename and attach, before you schedule production.
Key takeaways
- Partition when the table is very large and you have retention, time-focused queries or maintenance pain, not as a default.
- Pick a key that appears in most
WHEREclauses; queries without it touch every partition. - Primary keys and unique constraints must include the partition key.
- Create partitions ahead of time with
CHECKandATTACH, and avoid relying on a default partition. - Drop old data with
DETACH ... CONCURRENTLYandDROP TABLEinstead ofDELETE. - Convert a live table by attaching it as one big partition, after a validated
CHECKconstraint and matching indexes. - Run
ANALYZEon the parent yourself; autovacuum will not.