Looking Forward to Postgres 19: Split Personality

7 hours ago

Postgres 19 introduces new SPLIT PARTITION and MERGE PARTITIONS syntax for reorganizing partitions, simplifying what previously required multiple manual steps.
SPLIT PARTITION allows dividing a partition into smaller ones with a single command, moving rows automatically to the correct new partitions.
MERGE PARTITIONS combines adjacent partitions into one, reducing overhead and maintaining data integrity without manual migration.
These operations support default partitions, enabling easy redistribution of data from a catch-all default into specific partitions.
Both SPLIT and MERGE require partitions to be adjacent without gaps, with Postgres providing clear error messages for violations.
During split or merge, local indexes and constraints defined on individual partitions are lost, while those inherited from the parent are recreated.
The commands take an ACCESS EXCLUSIVE lock on the entire partitioned table, blocking reads and writes until completion, with no CONCURRENTLY variant yet.
Split and merge are available for RANGE and LIST partitioning but not for HASH partitioning, which may be a limitation for some users.
These features address long-standing requests but should be used during maintenance windows due to locking, pending future improvements like concurrent operations.

Hasty Briefsbeta