Christophe Pettus: PARTITION MERGE/SPLIT, Once More With Locking

Why It Matters

The new commands simplify partition maintenance but require careful scheduling, as they block all reads and writes, directly affecting availability and operational cost for PostgreSQL users.

Key Takeaways

• PG19 adds MERGE and SPLIT partition commands as single DDL statements.
• Operations acquire AccessExclusiveLock on parent table, blocking all activity.
• Lock duration scales linearly with partition size; 100 GB may need off‑hours.
• Future releases aim to reduce lock scope, targeting PG21.
• Manual scripts remain best for online repartitioning of large tables.

Pulse Analysis

PostgreSQL’s declarative partitioning has long been praised for its flexibility, yet the manual steps required to merge or split partitions have been a persistent pain point for DBAs. Prior to version 19, administrators had to create new partitions, copy data, swap tables, and drop the old ones—a process prone to human error and downtime. By embedding MERGE and SPLIT as native DDL commands, PostgreSQL 19 eliminates the scripting overhead and aligns with modern data‑ops practices, allowing teams to codify partition maintenance directly in migration scripts.

The trade‑off comes in the form of an AccessExclusiveLock on the parent table for the duration of the operation. This lock blocks all SELECT, INSERT, UPDATE, and DELETE activity, effectively taking the table offline. PostgreSQL’s community chose this conservative path because earlier PG 17 patches that attempted finer‑grained locking exposed deadlock scenarios and inconsistencies with foreign‑key handling. While some commercial database vendors ship faster, potentially flaky implementations and patch later, PostgreSQL prioritizes correctness and predictable behavior, reinforcing its reputation for reliability in mission‑critical environments.

Practically, the new commands are best suited for scheduled maintenance windows. A 100 GB partition on modern SSD storage may require several minutes to an hour of exclusive lock time, making it unsuitable for peak traffic periods. Smaller partitions, such as 5 GB, can be handled more comfortably during low‑usage windows. DBAs should still rely on manual scripts for online repartitioning of large, hot tables, and monitor the PostgreSQL roadmap—future releases (targeting PG 21) aim to shrink the lock scope, offering a path toward truly online partition reorganization.