From 1adf16b8fba45f77056d91573cd7138ed9da4ebf Mon Sep 17 00:00:00 2001 From: Alexander Korotkov Date: Sun, 7 Apr 2024 00:57:22 +0300 Subject: Implement ALTER TABLE ... MERGE PARTITIONS ... command This new DDL command merges several partitions into the one partition of the target table. The target partition is created using new createPartitionTable() function with parent partition as the template. This commit comprises quite naive implementation which works in 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 come in handy in certain cases even as is. Also, it could be used as a foundation for future implementations with lesser locking and possibly parallel. Discussion: https://postgr.es/m/c73a1746-0cd0-6bdd-6b23-3ae0b7c0c582%40postgrespro.ru Author: Dmitry Koval Reviewed-by: Matthias van de Meent, Laurenz Albe, Zhihong Yu, Justin Pryzby Reviewed-by: Alvaro Herrera, Robert Haas, Stephane Tachoires --- doc/src/sgml/ddl.sgml | 19 ++++++++++ doc/src/sgml/ref/alter_table.sgml | 77 +++++++++++++++++++++++++++++++++++++-- 2 files changed, 93 insertions(+), 3 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 6bc43ad10ca..f9f2778b3d7 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -4379,6 +4379,25 @@ ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate); ALTER INDEX measurement_city_id_logdate_key ATTACH PARTITION measurement_y2006m02_city_id_logdate_key; ... + + + + + There is also an option for merging multiple table partitions into + a single partition using the + ALTER TABLE ... MERGE PARTITIONS. + This feature simplifies the management of partitioned tables by allowing + administrators to combine partitions that are no longer needed as + separate entities. It's important to note that this operation is not + supported for hash-partitioned tables and acquires an + ACCESS EXCLUSIVE lock, which could impact high-load + systems due to the lock's restrictive nature. For example, we can + merge three monthly partitions into one quarter partition: + +ALTER TABLE measurement + MERGE PARTITIONS (measurement_y2006m01, + measurement_y2006m02, + measurement_y2006m03) INTO measurement_y2006q1; diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 6cdcd779ef8..e0a98bc5385 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -37,6 +37,9 @@ ALTER TABLE [ IF EXISTS ] name ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT } ALTER TABLE [ IF EXISTS ] name DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ] +ALTER TABLE [ IF EXISTS ] name + MERGE PARTITIONS (partition_name1, partition_name2 [, ...]) + INTO partition_name where action is one of: @@ -1118,14 +1121,74 @@ WITH ( MODULUS numeric_literal, REM + + MERGE PARTITIONS (partition_name1, partition_name2 [, ...]) INTO partition_name + + + + This form merges several partitions into the one partition of the target table. + Hash-partitioning is not supported. If DEFAULT partition is not in the + list of partitions partition_name1, + partition_name2 [, ...]: + + + + For range-partitioned tables is necessary that the ranges + of the partitions partition_name1, + partition_name2 [, ...] can + be merged into one range without spaces and overlaps (otherwise an error + will be generated). The combined range will be the range for the partition + partition_name. + + + + + For list-partitioned tables the values lists of all partitions + partition_name1, + partition_name2 [, ...] are + combined and form a list of values of partition + partition_name. + + + + If DEFAULT partition is in the list of partitions partition_name1, + partition_name2 [, ...]: + + + + The partition partition_name + will be the DEFAULT partition. + + + + + For range- and list-partitioned tables the ranges and lists of values + of the merged partitions can be any. + + + + The new partition partition_name + can have the same name as one of the merged partitions. Only simple, + non-partitioned partitions can be merged. + + + + This command acquires an ACCESS EXCLUSIVE lock. + This is a significant limitation, which limits the usage of this + command with large partitioned tables under a high load. + + + + + All the forms of ALTER TABLE that act on a single table, except RENAME, SET SCHEMA, - ATTACH PARTITION, and - DETACH PARTITION can be combined into + ATTACH PARTITION, DETACH PARTITION, + and MERGE PARTITIONS can be combined into a list of multiple alterations to be applied together. For example, it is possible to add several columns and/or alter the type of several columns in a single command. This is particularly useful with large @@ -1368,7 +1431,8 @@ WITH ( MODULUS numeric_literal, REM partition_name - The name of the table to attach as a new partition or to detach from this table. + The name of the table to attach as a new partition or to detach from this table, + or the name of the new merged partition. @@ -1784,6 +1848,13 @@ ALTER TABLE measurement DETACH PARTITION measurement_y2015m12; + + To merge several partitions into one partition of the target table: + +ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_central) + INTO sales_all; + + -- cgit v1.2.3