✂️ Use Cases for Merging and Splitting Partitions With Minimal Locking in PostgreSQL 17 | Andrew Atkinson
This post looks at some interesting new capabilities managing Partitioned Tables coming in PostgreSQL 17, expected for release Fall 2024. The current major version is 16.
To combine multiple partitions into a single one, or to “subdivide” a single partition into multiples, we’d need to design a new structure then migrate all data rows to it. That’s a lot of steps!
From version 17, we have more options. We can now perform a SPLIT PARTITION operation on an existing singular partition, into two or more new ones.
If we wish to do the reverse, we’ve got that option as well. Starting from two or more partitions, we can perform a MERGE PARTITIONS (plural) operation to combine them into one.
We’ll need a way to run pre-release PostgreSQL 17. Fortunately, I’ve recently compiled PostgreSQL from source code on my macOS laptop, and will use that instance and some test tables within the default postgres DB.
I use Postgres.app to run PostgreSQL 16 for most of my local development, and instances on different ports. I stop the instance on port 5432 so I can start up the one based on the compiled source code like this:
