Does ATTACH PARTITION lock the parent table in PostgreSQL?

Short answer

ALTER TABLE events ATTACH PARTITION events_2026_10 FOR VALUES FROM ('2026-10-01') TO ('2026-11-01') takes a SHARE UPDATE EXCLUSIVE lock on events, so queries on the partitioned table keep running, and an ACCESS EXCLUSIVE lock on events_2026_10, which usually nobody is using yet.

While holding those locks it reads every row of the new partition to check it falls in the range: 0.2 s for our 3 million rows. Add a CHECK constraint that matches the range first, validated without blocking, and the attach takes a few milliseconds.

Two things make it heavier:

• A default partition. It gets an ACCESS EXCLUSIVE lock too, and is scanned to make sure none of its rows belong in the new range. Queries that need the default partition wait.
• Indexes on the parent that the new table lacks. They’re built during the attach, under the lock.

To remove a partition, use DETACH PARTITION … CONCURRENTLY (PostgreSQL 14 and later). Plain DETACH PARTITION takes ACCESS EXCLUSIVE on the parent and blocks every query on it.

What it locks

What other sessions could do while each was held, identical on all five versions:

Does it rewrite the table?

No. Nothing is copied. ATTACH reads the new partition (and the default partition, if any) to check the rows; DETACH doesn’t read anything.

The scan of the new partition is skipped when it has a valid CHECK constraint that proves every row is in range. A NOT VALID one doesn’t count. If the partition key column allows NULL, the CHECK must rule NULL out as well, because a range partition never accepts NULL:

CHECK (created IS NOT NULL AND created >= '2026-10-01' AND created < '2026-11-01')

Without created IS NOT NULL (on a nullable column) the attach still scanned. The same trick works for the default partition: a valid CHECK on it that excludes the new range skips its scan.

Run it safely

1. Prepare the new table outside the partitioned table. Load it, then create the indexes the parent has, concurrently, so the attach doesn’t build them under its lock:

CREATE TABLE events_2026_10 (LIKE events INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-- load data …
CREATE INDEX CONCURRENTLY events_2026_10_user_id_idx ON events_2026_10 (user_id);

A matching index on the new table is attached to the parent’s index instead of being built: 3 to 6 ms in our test, against 0.7 to 0.8 s when the attach had to build it.

2. Add the range as a CHECK, without blocking, and validate it:

ALTER TABLE events_2026_10 ADD CONSTRAINT events_2026_10_range
  CHECK (created IS NOT NULL AND created >= '2026-10-01' AND created < '2026-11-01') NOT VALID;
ALTER TABLE events_2026_10 VALIDATE CONSTRAINT events_2026_10_range;

3. Attach with lock_timeout, then drop the now-redundant constraint:

SET lock_timeout = '2s';
ALTER TABLE events ATTACH PARTITION events_2026_10
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
ALTER TABLE events_2026_10 DROP CONSTRAINT events_2026_10_range;

If it fails with ERROR: canceling statement due to lock timeout, nothing changed; retry after a pause. See lock timeout.

Detaching: use CONCURRENTLY, outside a transaction:

ALTER TABLE events DETACH PARTITION events_2025_01 CONCURRENTLY;

It works in two transactions. First it takes SHARE UPDATE EXCLUSIVE on the parent and marks the partition as being detached; then it waits, holding no table locks, for every transaction that was using the parent; finally it takes SHARE UPDATE EXCLUSIVE on the parent and ACCESS EXCLUSIVE on the partition and finishes. Its limits:

• ERROR: ALTER TABLE ... DETACH CONCURRENTLY cannot run inside a transaction block
• ERROR: cannot detach partitions concurrently when a default partition exists
• If it’s interrupted, the partition is left DETACH PENDING (shown by \d+ events). Finish it with ALTER TABLE events DETACH PARTITION events_2025_01 FINALIZE.

It leaves a CHECK constraint on the detached table that matches its old range, so attaching it again later skips the scan (0 scans in our test).

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac), each ATTACH in a rolled-back transaction:

CREATE TABLE seo_mig.ev (id bigint, created date NOT NULL, n int) PARTITION BY RANGE (created);
CREATE TABLE seo_mig.ev_2026_09 PARTITION OF seo_mig.ev
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE seo_mig.ev_2026_10 (LIKE seo_mig.ev INCLUDING ALL);
INSERT INTO seo_mig.ev_2026_10
SELECT g, date '2026-10-01' + (g % 31), g % 1000 FROM generate_series(1, 3000000) g;

BEGIN;
ALTER TABLE seo_mig.ev ATTACH PARTITION seo_mig.ev_2026_10
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
SELECT relation::regclass, mode FROM pg_locks
WHERE pid = pg_backend_pid()
  AND relation IN (SELECT oid FROM pg_class WHERE relnamespace = 'seo_mig'::regnamespace);
SELECT seq_scan FROM pg_stat_xact_user_tables WHERE relid = 'seo_mig.ev_2026_10'::regclass;
ROLLBACK;

With a default partition, ev_default also had AccessExclusiveLock; a CHECK on it excluding the October range took its scan to 0. Blocking was tested by holding each statement open in one session and running statements with lock_timeout = '1s' in another. For DETACH … CONCURRENTLY, we held it up with another session’s open transaction on the parent (and, for the last step, one on the partition), and printed its locks from a third session at three moments:

  at 0.8 s: virtualxid 71/377 ExclusiveLock; relation seo_mig.ev ShareUpdateExclusiveLock (waiting)
  at 2.3 s: virtualxid 71/378 ExclusiveLock; virtualxid 74/223 ShareLock (waiting)
  at 3.8 s: virtualxid 71/378 ExclusiveLock; relation seo_mig.ev ShareUpdateExclusiveLock; relation seo_mig.ev_2026_09 AccessExclusiveLock (waiting)

(PostgreSQL 18.6; the same three steps on 14, 15, 16 and 17.) Plain DETACH PARTITION took AccessExclusiveLock on both ev and ev_2026_09 in 3 to 4 ms. An interrupted concurrent detach (cancelled by statement_timeout) showed (DETACH PENDING) in \d+ on all five versions, and FINALIZE completed it in under 8 ms.

In Inlet

While an attach or detach is waiting, the Activity monitor shows which session it’s waiting for and what’s queued behind it, and lets you cancel or terminate it.