Vailati
Go back

Creating new partitions without locking the root table

The obvious PostgreSQL command holds an ACCESS EXCLUSIVE lock on the whole table until commit. There is a path that holds nothing that matters, and it is two commands instead of one.

Context

I needed to create a new set of partitions in three partitioned tables that receive millions of rows a day, without any in-flight query or write having to wait. The decision was to replace CREATE TABLE … PARTITION OF with CREATE TABLE (LIKE …) followed by ATTACH PARTITION, which trades an exclusive lock on the root for a lock that conflicts with neither reads nor writes. The cost was a longer script, one more validation step and an operational rule: never run it while ANALYZE is running on the root.

The setting is a points ledger for a loyalty platform. The main table, which I will call ledger_entry, is partitioned at two levels. The first level is LIST by account_group, an integer derived from the account id: each group holds two million accounts. The second level is RANGE by created_at, one partition per year. Two sibling tables, credit and debit, follow the same design. Every query on these tables carries account_group and created_at in the WHERE clause; otherwise it turns into a scan over dozens of leaves.

The group is a generated column:

account_group integer GENERATED ALWAYS AS (((id - 1) / 2000000) + 1) STORED

This means that, as the account base grows, one day the INSERT of a new account lands in a group that has no partition yet. Without a DEFAULT partition, the INSERT fails. So the partitions for the next group must exist before the group is born, and creating them is a recurring task, done with the application running.

Problem

The natural way to create the sub-level of a new group is this:

CREATE TABLE ledger_entry_g15
    PARTITION OF ledger_entry FOR VALUES IN (15)
    PARTITION BY RANGE (created_at);

And then the yearly leaves:

CREATE TABLE ledger_entry_g15_2026
    PARTITION OF ledger_entry_g15
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

It is what the documentation shows first, it is what EF Core generates in a migration, and it is what I had in a script that created six groups at once, each group in its own transaction. It works. The problem is what happens while the transaction is open.

CREATE TABLE … PARTITION OF needs ACCESS EXCLUSIVE on the parent table. That is the strongest lock in PostgreSQL: it conflicts with everything, including SELECT. And the lock lasts until COMMIT, not until the end of the statement. In a script where each group is a transaction with several statements, the root ledger_entry is inaccessible from the first CREATE of the group until that group’s COMMIT. Every read and every write on any existing partition gets in line.

With an empty root, that is milliseconds. With a root receiving hundreds of requests per second, a queue of a few seconds becomes a timeout in the application, and a timeout in the application becomes an error for whoever called. My criterion was explicit: the new partition may be unavailable, nobody uses it yet. Partitions already in use may not become inaccessible, not even for an instant.

There is an aggravating factor I had not considered. Even if the CREATE is fast, it first has to acquire the lock. If some session holds any lock on the root, the CREATE waits. And while the CREATE waits in the queue, everything that arrives after it waits too, because PostgreSQL does not let new requests jump ahead of a pending ACCESS EXCLUSIVE. An hour-long ANALYZE on the root, fired by a daily job, turns a millisecond CREATE into an hour-long block.

What I measured

I measured on PostgreSQL 18.6, on a local copy with the production DDL, looking at pg_locks from a second session while the script’s transaction sat open before COMMIT.

With CREATE TABLE … PARTITION OF on the root:

RelationLock held by the script’s transaction
ledger_entry (root)AccessExclusiveLock
credit (root)AccessExclusiveLock
debit (root)AccessExclusiveLock

In that state, a SELECT count(*) FROM ledger_entry WHERE account_group = 3 AND created_at >= '2026-01-01' in a third session sits in wait_event = relation until the commit. It does not matter that group 3 has nothing to do with group 15: the lock is on the root, and the query has to go through the root.

With the two-command version, shown in the next section:

RelationLock held by the script’s transaction
ledger_entry (root)AccessShareLock, ShareUpdateExclusiveLock
credit (root)AccessShareLock, ShareUpdateExclusiveLock
debit (root)AccessShareLock, ShareUpdateExclusiveLock
ledger_entry_g15 (new)AccessExclusiveLock

SHARE UPDATE EXCLUSIVE does not conflict with ROW EXCLUSIVE, which is what INSERT, UPDATE and DELETE take, nor with ACCESS SHARE, which is what SELECT takes. The same query on group 3 runs normally with the script’s transaction open. The ACCESS EXCLUSIVE stayed on the new table only, which nobody uses.

I also measured what happens when something really conflicts. I opened a session with BEGIN; ANALYZE credit; without committing, which holds SHARE UPDATE EXCLUSIVE on the credit root, and ran the script with lock_timeout = '3s'. Result: the script failed after four seconds on the first statement that touched credit, with zero commits and zero loose relations, because the whole group is one transaction. I released the ANALYZE session, re-ran the same script unchanged, and it created the 18 relations. The validation query, which lists missing or incomplete groups, came back empty.

Finally, I pointed the application at the copy and created a ledger entry for an account in group 15 through the API. The row landed in ledger_entry_g15_2026, with the identity value generated, and its reversal landed in debit_g15_2026. That proves the ATTACH propagated everything the routing needs, not just the table.

Solution

The idea is to separate “create the table” from “attach the table to the root”. CREATE TABLE with LIKE touches the root only to read its definition. ATTACH PARTITION, since PostgreSQL 12, takes only SHARE UPDATE EXCLUSIVE on the parent.

\set ON_ERROR_STOP on
SET lock_timeout = '3s';

BEGIN;

-- 1. group sub-level: a copy of the root's definition, still detached
CREATE TABLE IF NOT EXISTS ledger_entry_g15
    (LIKE ledger_entry
        INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING GENERATED
        EXCLUDING IDENTITY EXCLUDING INDEXES)
    PARTITION BY RANGE (created_at);

-- 2. attach to the root: SHARE UPDATE EXCLUSIVE, does not conflict with DML
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_inherits
                   WHERE inhrelid = 'ledger_entry_g15'::regclass) THEN
        EXECUTE 'ALTER TABLE ledger_entry ATTACH PARTITION ledger_entry_g15
                 FOR VALUES IN (15)';
    END IF;
END $$;

-- 3. yearly leaves: PARTITION OF is fine here, the lock is on g15, which is empty
CREATE TABLE IF NOT EXISTS ledger_entry_g15_2026
    PARTITION OF ledger_entry_g15
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')
    WITH (autovacuum_vacuum_scale_factor = 0.02,
          autovacuum_analyze_scale_factor = 0.01,
          autovacuum_vacuum_threshold = 1000);

CREATE TABLE IF NOT EXISTS ledger_entry_g15_2027
    PARTITION OF ledger_entry_g15
    FOR VALUES FROM ('2027-01-01') TO ('2028-01-01')
    WITH (autovacuum_vacuum_scale_factor = 0.02,
          autovacuum_analyze_scale_factor = 0.01,
          autovacuum_vacuum_threshold = 1000);

COMMIT;

Four details make a difference.

EXCLUDING INDEXES and EXCLUDING IDENTITY are deliberate. The ATTACH creates on the new partition the indexes matching the root’s partitioned indexes and attaches them. If the LIKE had already copied the indexes, the ATTACH would find indexes with the same definition and reuse them, which also works, but the naming ends up different from what the ATTACH produces on its own and the validation afterwards gets more annoying. IDENTITY is similar: the root’s identity column is propagated by the ATTACH; copying it in the LIKE creates a separate sequence for the partition, which is not what I want. The NOT NULL constraints, the primary key and the foreign keys come through INCLUDING CONSTRAINTS and through the ATTACH itself.

The ATTACH scans nothing because the partition is new and empty. When you attach a table that has data, PostgreSQL has to verify that every row fits the range, and that is a full scan with the lock held. A freshly created partition has no rows, and the check is immediate. Likewise, if the root had a DEFAULT partition, the ATTACH would have to scan it to make sure no group 15 rows ended up there. I have no DEFAULT precisely for that reason.

The leaves can use PARTITION OF because their lock is on g15. The ACCESS EXCLUSIVE of CREATE TABLE … PARTITION OF ledger_entry_g15 is on ledger_entry_g15, which was just born and has no readers. The root is not touched. I could repeat LIKE + ATTACH on the leaves, but it would be verbosity without gain.

The WITH (autovacuum_…) has to be on every leaf. Storage parameters are not inherited. Worse: a partitioned table rejects autovacuum WITH options, with the error cannot specify storage parameters for a partitioned table. So there is no “configure it on the root and let it trickle down”. If the partitions in use have aggressive autovacuum, every new leaf must be born with it, or the most write-heavy table in the system ends up with the 20 % default and its first VACUUM is the anti-wraparound one.

The lock flow, side by side:

sequenceDiagram
    participant S as Script
    participant R as ledger_entry (root)
    participant A as Application
    Note over S,A: Version 1: PARTITION OF on the root
    S->>R: CREATE TABLE g15 PARTITION OF root
    Note over R: ACCESS EXCLUSIVE until COMMIT
    A-->>R: SELECT group 3 (waits)
    S->>R: COMMIT
    R-->>A: result (delayed)
    Note over S,A: Version 2: LIKE + ATTACH
    S->>S: CREATE TABLE g15 (LIKE root) PARTITION BY RANGE
    S->>R: ALTER TABLE root ATTACH PARTITION g15
    Note over R: SHARE UPDATE EXCLUSIVE
    A->>R: SELECT group 3 (runs)
    S->>R: COMMIT

Idempotency and failure

Each group is one transaction. If any statement fails, the group exists nowhere, and the script can be run again in full. The IF NOT EXISTS clauses and the pg_inherits check make sure groups already created are skipped. This matters because lock_timeout will fire one day: it is enough for the root ANALYZE job to be running. The right answer is not to raise the timeout; it is to fail fast, wait for the competitor to finish and re-run.

Two kinds of session conflict with the ATTACH: ANALYZE on the root, which also takes SHARE UPDATE EXCLUSIVE, and any DDL on the root. Autovacuum does not conflict, because autovacuum works on the leaves, never on a partitioned table. Before running, I query pg_locks for the three roots and pg_stat_progress_analyze; if there is anything beyond AccessShareLock, I wait.

The three-second lock_timeout was chosen below the application’s smallest timeout, which is ten seconds on queries. In the worst case, a request that arrived while the ATTACH was waiting for the lock is delayed three seconds and moves on; it never hits its own timeout.

Validation

After the commit, a query over pg_inherits and pg_class lists, per root, the expected groups that are missing or incomplete, and _g15 relations that exist without being attached. Expected: zero rows. Another compares the reloptions of each new leaf with the autovacuum profile of its root. A third checks the partitioned indexes: every index on the root must have a valid child on g15, and no index in the family may be indisvalid = false.

The final test is functional, through the application: a ledger entry for an account in the new group, read back from the right leaf. If routing works end to end, the ATTACH did everything it had to.

What I would do differently

I would have measured the lock before writing the first script, not after. The script with PARTITION OF passed every functional test and every structural validation. It was right about everything except what happens to whoever is using the database during the run. Testing DDL on an empty database does not show that. pg_locks from a second session, with the transaction open, is the test that was missing, and it takes a minute.

I would automate the creation of the yearly partitions. The new-group problem is rare. The next-year partition problem is annual, predictable, and every _2027 in the system has to exist before January 1st. A job that creates the next leaf of each sub-level ahead of time, using the same LIKE + ATTACH pattern where it applies, takes that off the list of things someone has to remember in December.

I would not let the ORM generate this migration. EF Core generates CREATE TABLE … PARTITION OF with no thought about locks, and the migration runs in the deploy pipeline, which is not the place to hold the root. Partitions are a database operation, with a window, a pre-flight check and a validation. The ORM migration, in this case, only records that the structure exists.

References

Share this post:
Share this post on LinkedinShare this post on WhatsappShare this post on XShare this post via email
Next Post
Partitioning a ledger at two levels: account group and year