Architecture

PostgreSQL Table Partitioning: When It Helps and How to Do It Safely

Partitioning turns one huge table into many smaller ones. Done right, it makes data retention instant and keeps vacuum and indexes manageable. Done wrong, it slows every query. Here is when it pays off, how to choose a key, and how to convert a live table.

· 15 min read · AG Data Team

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:

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.

The primary key rule. According to the documentation, a unique or primary key constraint on a partitioned table must include all partition key columns, and the partition key must not contain expressions. So 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:

  1. Create the new partitioned table events_new with the same columns and future partitions, starting at the cut-over date.
  2. 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.
  3. Add a CHECK constraint on the old table that proves its range, as NOT VALID first, then validate it. Validation scans the table but does not block reads and writes.
  4. 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

Key takeaways

PostgreSQL Partitioning Performance Large Databases
Work with us

Table too big to manage?

We design partitioning schemes and convert live multi-terabyte PostgreSQL tables with short lock windows, tested on a copy of your data first.

Get in touch More from the blog