Attaching Partitions Without Long Locks

A nightly job bulk-loads the previous day’s clickstream into a standalone table, builds its indexes, and then attaches it to the partitioned clicks table. It used to take seconds. Now the loaded table has 60 million rows and the attach takes four minutes, during which the new table is locked — fine — but lately queries on the whole clicks table have also started stalling. Two separate mechanisms are at work: ATTACH PARTITION scans the incoming table to prove every row fits the partition bounds, and, if the parent has a default partition, it also scans the default partition to prove none of its rows belong in the new range. Both scans are avoidable. This guide explains the locks and scans involved and shows how to make attaching a partition a catalog operation. It belongs to Partitioning Live Tables Without Downtime.

Symptom / Error Signatures

These signals point to an attach doing more work than it should:

• ALTER TABLE clicks ATTACH PARTITION ... runs for minutes; pg_stat_activity shows it active with no lock wait, and pg_stat_user_tables.seq_scan on the incoming table increments.
• Queries touching the default partition stall during the attach, because it is also being scanned under a lock.
• The attach fails at the end:

ERROR:  partition constraint of relation "clicks_2026_09_17" is violated by some row
ERROR:  updated partition constraint for default partition "clicks_default" would be violated by some row

• On PostgreSQL 11 and earlier, the attach takes ACCESS EXCLUSIVE on the parent, so every query on the partitioned table waits for its duration.

Root Cause Analysis

Since PostgreSQL 12, ATTACH PARTITION locks the parent with SHARE UPDATE EXCLUSIVE — reads and writes to other partitions continue — and the incoming table with ACCESS EXCLUSIVE. The expensive part is validation: PostgreSQL must be certain every row in the incoming table satisfies the partition bound. It skips the scan if the table already has a validated CHECK constraint that implies the bound (including NOT NULL on the key for range partitions). Otherwise it scans the table under that exclusive lock.

A default partition adds a second obligation: after attaching, rows belonging to the new range must not remain in the default partition, so PostgreSQL scans the default partition too, holding a lock on it. A validated CHECK on the default partition that excludes the new range skips that scan, but in practice the simplest fix is to not have a default partition on tables where partitions are attached routinely.

Immediate Mitigation

1. Add the constraint while the table is still standalone. For a table that is being loaded, the cheapest moment is right after loading: the constraint can be added normally (validated immediately) because nobody else is using the table yet.

-- PostgreSQL 12+ · loader role · standalone table, nobody else reads it yet
ALTER TABLE clicks_2026_09_17 ALTER COLUMN clicked_at SET NOT NULL;
ALTER TABLE clicks_2026_09_17 ADD CONSTRAINT clicks_2026_09_17_bounds
  CHECK (clicked_at >= '2026-09-17' AND clicked_at < '2026-09-18');

For a table that is live (for example a legacy table being adopted), add the check NOT VALID and VALIDATE it online instead, as in Adding Constraints Without Downtime.

2. Attach with a lock timeout.

-- PostgreSQL 12+ · SHARE UPDATE EXCLUSIVE on parent, brief ACCESS EXCLUSIVE on the child
SET lock_timeout = '3s';
ALTER TABLE clicks ATTACH PARTITION clicks_2026_09_17
  FOR VALUES FROM ('2026-09-17') TO ('2026-09-18');
ALTER TABLE clicks_2026_09_17 DROP CONSTRAINT clicks_2026_09_17_bounds;   -- redundant after attach
-- ROLLBACK PATH: ALTER TABLE clicks DETACH PARTITION clicks_2026_09_17;

3. Deal with the default partition. If one exists and may contain rows for the new range, move those rows out first; if it should be empty, add a validated CHECK to it that excludes the ranges you attach, or remove the default partition altogether.

4. Build indexes before attaching. If the incoming table lacks indexes the parent defines, the attach creates them — under the lock. Create matching indexes on the standalone table first (non-concurrently is fine while nobody uses it) so the attach can adopt them.

Permanent Fix / Long-Term Pattern

Make “load standalone, constrain, index, attach” the standard pipeline for bulk-loaded partitions. The standalone table is private until the attach, so every heavy step — loading, indexing, validating — runs with no contention, and the attach becomes a catalog update. For partitions created empty ahead of time (the usual pattern for continuously written tables), create them directly with CREATE TABLE ... PARTITION OF, which needs no validation at all, well before data arrives — see automating partition creation with pg_partman.

Avoid default partitions on tables that use routine attach or concurrent detach: they add scans to attaches and block DETACH ... CONCURRENTLY entirely. If you need protection against out-of-range inserts, monitor for them and pre-create partitions generously instead. The same constraint trick is at the heart of adopting an existing table, as in converting a Postgres table to declarative partitioning.

Verification Checklist

• The incoming table had a validated CHECK implying the partition bounds (and NOT NULL on the key) before attaching.
• The parent has no default partition, or the default has a validated constraint excluding the new range.
• Indexes matching the parent’s existed on the incoming table before the attach.
• The attach completed in milliseconds with lock_timeout set.
• pg_partition_tree('clicks') lists the new partition with the expected bounds.

Frequently Asked Questions

Does ATTACH PARTITION block queries on the parent? Since PostgreSQL 12 it takes SHARE UPDATE EXCLUSIVE on the parent, so reads and writes to other partitions continue. On PostgreSQL 11 and earlier it takes ACCESS EXCLUSIVE on the parent, blocking everything.

What exactly must the CHECK constraint look like? It must imply the partition bound: for a range partition on clicked_at from A to B, clicked_at >= A AND clicked_at < B, together with NOT NULL on the column (or clicked_at IS NOT NULL in the check). It must be validated.

Why keep the CHECK after attaching? You do not need to. The partition bound enforces the same rule once attached, so the check is redundant and can be dropped to save a little work on each insert.

Should I use a default partition as a safety net? On tables that attach and detach partitions routinely, no. A default partition forces extra scans on attach and prevents concurrent detach. Pre-create partitions well ahead and alert if the horizon gets short.