On 14th of December 2025, Alexander Korotkov committed patch:
Implement ALTER TABLE ... MERGE PARTITIONS ... command This new DDL command merges several partitions into a single partition of the target table. The target partition is created using the new createPartitionTable() function with the parent partition as the template. This commit comprises a quite naive implementation which works in a single process and holds the ACCESS EXCLUSIVE LOCK on the parent table during all the operations, including the tuple routing. This is why this new DDL command can't be recommended for large partitioned tables under a high load. However, this implementation comes in handy in certain cases, even as it is. Also, it could serve as a foundation for future implementations with less locking and possibly parallelism. Discussion: https://postgr.es/m/c73a1746-0cd0-6bdd-6b23-3ae0b7c0c582%40postgrespro.ru Author: Dmitry Koval <d.koval@postgrespro.ru> Co-authored-by: Alexander Korotkov <aekorotkov@gmail.com> Co-authored-by: Tender Wang <tndrwang@gmail.com> Co-authored-by: Richard Guo <guofenglinux@gmail.com> Co-authored-by: Dagfinn Ilmari Mannsaker <ilmari@ilmari.org> Co-authored-by: Fujii Masao <masao.fujii@gmail.com> Co-authored-by: Jian He <jian.universality@gmail.com> Reviewed-by: Matthias van de Meent <boekewurm+postgres@gmail.com> Reviewed-by: Laurenz Albe <laurenz.albe@cybertec.at> Reviewed-by: Zhihong Yu <zyu@yugabyte.com> Reviewed-by: Justin Pryzby <pryzby@telsasoft.com> Reviewed-by: Alvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Robert Haas <rhaas@postgresql.org> Reviewed-by: Stephane Tachoires <stephane.tachoires@gmail.com> Reviewed-by: Jian He <jian.universality@gmail.com> Reviewed-by: Alexander Korotkov <aekorotkov@gmail.com> Reviewed-by: Pavel Borisov <pashkin.elfe@gmail.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Alexander Lakhin <exclusion@gmail.com> Reviewed-by: Kyotaro Horiguchi <horikyota.ntt@gmail.com> Reviewed-by: Daniel Gustafsson <dgustafsson@postgresql.org> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Noah Misch <noah@leadboat.com>
and then, mere seconds later, he also committed second patch:
Implement ALTER TABLE ... SPLIT PARTITION ... command This new DDL command splits a single partition into several partitions. Just like the ALTER TABLE ... MERGE PARTITIONS ... command, new partitions are created using the createPartitionTable() function with the parent partition as the template. This commit comprises a quite naive implementation which works in a single process and holds the ACCESS EXCLUSIVE LOCK on the parent table during all the operations, including the tuple routing. This is why the new DDL command can't be recommended for large, partitioned tables under high load. However, this implementation comes in handy in certain cases, even as it is. Also, it could serve as a foundation for future implementations with less locking and possibly parallelism. Discussion: https://postgr.es/m/c73a1746-0cd0-6bdd-6b23-3ae0b7c0c582%40postgrespro.ru Author: Dmitry Koval <d.koval@postgrespro.ru> Co-authored-by: Alexander Korotkov <aekorotkov@gmail.com> Co-authored-by: Tender Wang <tndrwang@gmail.com> Co-authored-by: Richard Guo <guofenglinux@gmail.com> Co-authored-by: Dagfinn Ilmari Mannsaker <ilmari@ilmari.org> Co-authored-by: Fujii Masao <masao.fujii@gmail.com> Co-authored-by: Jian He <jian.universality@gmail.com> Reviewed-by: Matthias van de Meent <boekewurm+postgres@gmail.com> Reviewed-by: Laurenz Albe <laurenz.albe@cybertec.at> Reviewed-by: Zhihong Yu <zyu@yugabyte.com> Reviewed-by: Justin Pryzby <pryzby@telsasoft.com> Reviewed-by: Alvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Robert Haas <rhaas@postgresql.org> Reviewed-by: Stephane Tachoires <stephane.tachoires@gmail.com> Reviewed-by: Jian He <jian.universality@gmail.com> Reviewed-by: Alexander Korotkov <aekorotkov@gmail.com> Reviewed-by: Pavel Borisov <pashkin.elfe@gmail.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Alexander Lakhin <exclusion@gmail.com> Reviewed-by: Kyotaro Horiguchi <horikyota.ntt@gmail.com> Reviewed-by: Daniel Gustafsson <dgustafsson@postgresql.org> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Noah Misch <noah@leadboat.com>
So, this has happened before – in Pg17 dev cycle. But was rolled back due to some issues.
But now, it looks like it's back.
If you want more description please read the previous blogpost, I'll just verify that everything works as expected and as it worked in previous version:
=$ CREATE TABLE whatever_range ( id int8 generated always as identity, partkey text not null, payload int8 ) PARTITION BY list (partkey); CREATE TABLE =$ CREATE TABLE whatever_range_abc partition OF whatever_range FOR VALUES IN ('a', 'b', 'c'); CREATE TABLE =$ CREATE TABLE whatever_range_de partition OF whatever_range FOR VALUES IN ( 'd', 'e' ); CREATE TABLE =$ \d+ whatever_range Partitioned table "public.whatever_range" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------+--------+-----------+----------+------------------------------+----------+-------------+--------------+------------- id | bigint | | not null | generated always as identity | plain | | | partkey | text | | not null | | extended | | | payload | bigint | | | | plain | | | Partition key: LIST (partkey) Not-null constraints: "whatever_range_id_not_null" NOT NULL "id" "whatever_range_partkey_not_null" NOT NULL "partkey" Partitions: whatever_range_abc FOR VALUES IN ('a', 'b', 'c'), whatever_range_de FOR VALUES IN ('d', 'e') =$ alter table whatever_range SPLIT partition whatever_range_abc into ( partition whatever_range_ab FOR VALUES IN ('a', 'b'), partition whatever_range_c FOR VALUES IN ('c') ); ALTER TABLE =$ \d+ whatever_range Partitioned table "public.whatever_range" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------+--------+-----------+----------+------------------------------+----------+-------------+--------------+------------- id | bigint | | not null | generated always as identity | plain | | | partkey | text | | not null | | extended | | | payload | bigint | | | | plain | | | Partition key: LIST (partkey) Not-null constraints: "whatever_range_id_not_null" NOT NULL "id" "whatever_range_partkey_not_null" NOT NULL "partkey" Partitions: whatever_range_ab FOR VALUES IN ('a', 'b'), whatever_range_c FOR VALUES IN ('c'), whatever_range_de FOR VALUES IN ('d', 'e') =$ alter table whatever_range merge partitions ( whatever_range_c, whatever_range_de ) into whatever_range_cde; ALTER TABLE =$ \d+ whatever_range Partitioned table "public.whatever_range" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------+--------+-----------+----------+------------------------------+----------+-------------+--------------+------------- id | bigint | | not null | generated always as identity | plain | | | partkey | text | | not null | | extended | | | payload | bigint | | | | plain | | | Partition key: LIST (partkey) Not-null constraints: "whatever_range_id_not_null" NOT NULL "id" "whatever_range_partkey_not_null" NOT NULL "partkey" Partitions: whatever_range_ab FOR VALUES IN ('a', 'b'), whatever_range_cde FOR VALUES IN ('c', 'd', 'e')
OK. So all worked fine, just as previously. For sanity purpose. let's also check times and locks:
=$ insert into whatever_range (partkey, payload) select case when random() < .5 then 'a' else 'b' end, (random() * 1000000000)::int8 from generate_series(1,10000000); INSERT 0 10000000 Time: 11057.532 ms (00:11.058) =$ select tableoid::regclass, count(*) from whatever_range group by 1 order by 1; tableoid | count -------------------+---------- whatever_range_ab | 10000000 (1 row) Time: 1040.187 ms (00:01.040) =$ select relname, oid, relfilenode, relkind, pg_relation_size(oid) from pg_class where relname ~ '^whatever_range' and relkind in ('p', 'r'); relname | oid | relfilenode | relkind | pg_relation_size --------------------+--------+-------------+---------+------------------ whatever_range | 608110 | 0 | p | 0 whatever_range_ab | 608129 | 608129 | r | 521789440 whatever_range_cde | 608139 | 608139 | r | 0 (3 rows) Time: 4.078 ms =$ begin; BEGIN Time: 0.087 ms =$ alter table whatever_range SPLIT partition whatever_range_ab into ( partition whatever_range_a FOR VALUES IN ('a'), partition whatever_range_b FOR VALUES IN ('b') ); ALTER TABLE Time: 4968.717 ms (00:04.969) =$ select l.relation, c.relname, c.relkind from pg_locks l left join pg_class c on l.relation = c.oid where l.pid = pg_backend_pid() and l.mode ='AccessExclusiveLock' and l.locktype = 'relation' order by 2; relation | relname | relkind ----------+------------------+--------- 608110 | whatever_range | p 608144 | whatever_range_a | r 608149 | whatever_range_b | r 608129 | | (4 rows) Time: 1.447 ms
Interestingly there is slightly less locking (toast indexes), but I'd assume this can be unrelated to changes in this patch.
Anyway, it seems like great addon, and while it will need some more changes (so that the process will not keep AccessExclusiveLock for the whole duration of operation, it's definitely great thing, and I wish it will make it's way into final Pg19.
Thanks a lot to everyone involved.