Partitioning Live Tables Without Downtime

Some tables only grow: events, audit logs, metrics, messages, orders past their return window. At a few hundred million rows the costs become operational rather than theoretical — VACUUM runs for hours, index builds take an afternoon, deleting last year’s data generates a storm of WAL and bloat, and every schema change on the table is a major project. Partitioning splits such a table into many smaller physical tables behind one logical name, so old data can be dropped by detaching a partition instead of deleting rows, maintenance runs per partition, and queries that filter on the partition key touch only the partitions they need. The problem is getting there: PostgreSQL cannot convert an existing table into a partitioned one in place, and MySQL’s PARTITION BY rebuilds the whole table. This part of Zero-Downtime Schema Evolution Patterns covers how to introduce partitioning to a live table, and how to operate partitions afterwards without long locks. It serves engineers who own high-volume tables and the DBAs who maintain them.

The core trick on PostgreSQL is to stop thinking of partitioning as a conversion and start thinking of it as an adoption: create a new partitioned parent, attach the existing table to it as one partition covering all historical data, and create ordinary new partitions for everything that follows. With the right preparation, every step holds its heavy lock for only a moment.

Concept & Mechanism

PostgreSQL declarative partitioning (10+, with most of the important features from 11–14) defines a parent table with PARTITION BY RANGE, LIST or HASH on a key, and child tables that each hold a range of key values. The parent stores no rows; inserts are routed to the right child, and queries with a predicate on the key are pruned to the relevant children. Three operations matter for migrations:

• ATTACH PARTITION makes an existing table a child. Since PostgreSQL 12 it takes only SHARE UPDATE EXCLUSIVE on the parent, but ACCESS EXCLUSIVE on the table being attached, and it must prove every row fits the partition bounds — a full scan under that lock — unless a validated CHECK constraint on the table already implies the bounds, in which case the scan is skipped. If the parent has a default partition, PostgreSQL also scans the default partition to make sure none of its rows belong to the new range.
• DETACH PARTITION ... CONCURRENTLY (PostgreSQL 14+) removes a child using two transactions and only SHARE UPDATE EXCLUSIVE on the parent, so queries on the parent continue. It cannot run inside a transaction block or when a default partition exists.
• Creating a new, empty partition with CREATE TABLE ... PARTITION OF needs a lock on the parent but no scan (other than of a default partition, if one exists), so it is fast.

Partitioning imposes structural rules. Primary keys and unique constraints on a partitioned table must include all partition key columns, so a table whose primary key is (id) must move to (id, created_at) or give up uniqueness on id alone at the database level. Foreign keys referencing a partitioned table are supported from PostgreSQL 12. Indexes created on the parent cascade to every partition.

MySQL partitioning is a property of the table: ALTER TABLE ... PARTITION BY RANGE (...) rebuilds the table with the COPY algorithm, and every unique key, including the primary key, must include the partitioning columns. Once partitioned, ADD PARTITION (for range and list) and DROP PARTITION are cheap metadata-level operations, and EXCHANGE PARTITION swaps a partition with an ordinary table. Introducing partitioning to a large existing MySQL table is therefore usually done with an online schema change tool that builds the partitioned copy in the background, as in Online Schema Change Tools.

Prerequisites & Decision Criteria

Partitioning is not a general performance fix, and adopting it changes key design. Decide deliberately:

Before adopting partitioning on a live table:

• The partition key appears in the predicates of the queries that matter, confirmed from pg_stat_statements.
• The primary key and unique constraints can include the partition key, and the application tolerates that.
• Foreign keys to the table are compatible with the new composite key, or will be redesigned.
• A process exists to create future partitions before they are needed (pg_partman, a cron job, or application code).
• The plan includes a validated CHECK constraint on the legacy table before it is attached.

The choice of key is the most consequential decision and is covered in choosing a partition key for migration-friendly tables.

Step-by-Step Procedure

The procedure converts orders (partition key created_at) on PostgreSQL 14+. Each step is short; the only long-running step, validating the check constraint, does not block writes.

1. Constrain the legacy table’s range online. Add a CHECK that matches the partition bounds it will receive, as NOT VALID, then validate it. Verify convalidated is true before continuing.

-- PostgreSQL 14+ · migration role · brief lock, then an online scan
SET lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT orders_legacy_range
  CHECK (created_at IS NOT NULL AND created_at < '2026-10-01') NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_legacy_range;
-- ROLLBACK PATH: ALTER TABLE orders DROP CONSTRAINT orders_legacy_range;

2. Create the partitioned parent and future partitions. Same columns and types as the existing table; primary key including the partition key.

-- PostgreSQL 14+ · migration role · new objects only, no impact on the live table
CREATE TABLE orders_p (LIKE orders INCLUDING DEFAULTS INCLUDING GENERATED)
  PARTITION BY RANGE (created_at);
ALTER TABLE orders_p ADD PRIMARY KEY (id, created_at);
CREATE TABLE orders_2026_10 PARTITION OF orders_p FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE orders_2026_11 PARTITION OF orders_p FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

3. Make the legacy table’s key compatible. Its unique index must match the parent’s key (id, created_at) to be attached; build it concurrently if needed. Verify the index is valid.

4. Swap names and attach in one short transaction. Rename the live table to orders_legacy, rename the parent to orders, and attach the legacy table. Because the validated check proves the bounds, the attach skips its scan.

-- PostgreSQL 14+ · migration role · brief ACCESS EXCLUSIVE on the legacy table
-- WARNING: sequences, views and grants referencing "orders" must be checked; renames move them with the table.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders RENAME TO orders_legacy;
ALTER TABLE orders_p RENAME TO orders;
ALTER TABLE orders ATTACH PARTITION orders_legacy FOR VALUES FROM (MINVALUE) TO ('2026-10-01');
COMMIT;
-- ROLLBACK PATH: BEGIN; ALTER TABLE orders DETACH PARTITION orders_legacy; ALTER TABLE orders RENAME TO orders_p; ALTER TABLE orders_legacy RENAME TO orders; COMMIT;

5. Automate future partitions. Hand partition creation to pg_partman or a scheduled job so writes never arrive for a range with no partition — see automating partition creation with pg_partman.

6. Retire old data by detaching. From now on, old ranges are removed with DETACH PARTITION ... CONCURRENTLY and DROP TABLE, as in dropping old data with partition detach instead of DELETE. The large legacy partition can later be split by moving its data into range partitions in batches, or simply left to age out.

Verification & Observability

After the swap, confirm the structure and that routing works:

-- PostgreSQL · read-only
SELECT c.relname AS partition, pg_get_expr(c.relpartbound, c.oid) AS bounds
FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'orders'::regclass ORDER BY 1;
EXPLAIN SELECT * FROM orders WHERE created_at >= '2026-10-05' AND created_at < '2026-10-06';
-- the plan should touch only orders_2026_10

Ongoing, monitor the most important partitioning failure mode — writes for a range with no partition — which fails with no partition of relation "orders" found for row. Alert well before it can happen by checking that partitions exist for the next several intervals. Watch per-partition sizes, autovacuum activity and query plans that scan all partitions because they lack a key predicate. The dashboards in Migration Observability can carry a “days of partitions remaining” metric.

One operational habit changes after partitioning: index builds. CREATE INDEX CONCURRENTLY cannot be run on a partitioned parent. The online pattern is to create the index on the parent with ON ONLY (which creates an invalid parent index and no child indexes), build each child’s index with CREATE INDEX CONCURRENTLY, and attach each with ALTER INDEX parent_idx ATTACH PARTITION child_idx; once every partition’s index is attached, the parent index becomes valid automatically. It is more steps, but each one is online, and pg_partman or a small script can generate them. The same per-partition thinking applies to other maintenance: VACUUM, ANALYZE, REINDEX CONCURRENTLY and pg_repack all run partition by partition, which is exactly what makes them tractable on a table that was previously too large to maintain as a whole. Schedule them for the partitions that change — usually only the most recent few — and leave the historical ones alone.

Rollback Path

Until old data is dropped, the adoption is fully reversible: detach the legacy partition and swap the names back in one short transaction, as shown in step 4. Rows written after the swap live in the new partitions and would need to be copied back into the legacy table before reverting — which is why it is worth keeping the new partitions small by swapping shortly before a partition boundary, and why the rollback decision should be made quickly.

Rollback is safe while (a) no old partitions have been dropped and (b) the application does not yet depend on partition-only features. After that, fix forward. The general policy lives in Rollback Automation.

Common Errors & Fixes

ERROR: no partition of relation "orders" found for row. Root cause: no partition covers the inserted key — usually because future partitions were not created in time. Fix: create the missing partition immediately; automate creation with a margin of several intervals.

ERROR: unique constraint on partitioned table must include all partitioning columns. Root cause: trying to keep a primary key on (id) alone. Fix: use (id, created_at) and, if global uniqueness of id matters, rely on the sequence or a separate lookup table.

ATTACH PARTITION runs for minutes and blocks the legacy table. Root cause: no validated CHECK constraint matching the bounds, so PostgreSQL scans. Fix: cancel, add and validate the check first, then attach.

DETACH PARTITION CONCURRENTLY fails with cannot detach partitions concurrently when a default partition exists. Root cause: a default partition is defined. Fix: avoid default partitions on tables that retire data by detaching, or detach non-concurrently in a quiet window.

Child Page Index

Five guides take partitioning into detail. Converting a Postgres table to declarative partitioning expands the procedure above, including sequences, grants and views. Attaching partitions without long locks explains the CHECK-constraint trick and default-partition scans. Automating partition creation with pg_partman sets up premade partitions and retention. Dropping old data with partition detach instead of DELETE covers retention operations. And choosing a partition key for migration-friendly tables is the design decision to make before any of it.

Related techniques: moving data between tables in batches is covered in Backfill Optimization, and index management on large partitioned tables in Online Index Management.

Frequently Asked Questions

Can PostgreSQL convert an existing table to a partitioned table in place? No. A table is created either partitioned or not. The online path is to create a new partitioned parent and attach the existing table to it as a partition, which moves no data.

Why does ATTACH PARTITION sometimes take minutes? It must prove every row of the attached table fits the partition bounds. Without a validated CHECK constraint implying those bounds, that proof is a full scan under an ACCESS EXCLUSIVE lock on the attached table. With the constraint, the scan is skipped.

Do I have to change my primary key? On PostgreSQL and MySQL, unique constraints on a partitioned table must include the partition key, so a primary key on id alone must become (id, created_at) or similar. Plan for this before starting.

How do I partition a large MySQL table online? ALTER TABLE ... PARTITION BY copies the table, so for large tables use an online schema change tool that builds the partitioned copy in the background and swaps it in, after making every unique key include the partition columns.