- 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.