diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ddl.sgml | 19 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_table.sgml | 65 |
2 files changed, 82 insertions, 2 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index f9f2778b3d7..8ff9a520ca9 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -4400,6 +4400,25 @@ ALTER TABLE measurement measurement_y2006m03) INTO measurement_y2006q1; </programlisting> </para> + + <para> + Similarly to merging multiple table partitions, there is an option for + splitting a single partition into multiple using the + <link linkend="sql-altertable-split-partition"><command>ALTER TABLE ... SPLIT PARTITION</command></link>. + This feature could come in handy when one partition grows too big + and needs to be split into multiple. It's important to note that + this operation is not supported for hash-partitioned tables and acquires + an <literal>ACCESS EXCLUSIVE</literal> lock, which could impact high-load + systems due to the lock's restrictive nature. For example, we can split + the quarter partition back to monthly partitions: +<programlisting> +ALTER TABLE measurement SPLIT PARTITION measurement_y2006q1 INTO + (PARTITION measurement_y2006m01 FOR VALUES FROM ('2006-01-01') TO ('2006-02-01'), + PARTITION measurement_y2006m02 FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'), + PARTITION measurement_y2006m03 FOR VALUES FROM ('2006-03-01') TO ('2006-04-01')); +</programlisting> + </para> + </sect3> <sect3 id="ddl-partitioning-declarative-limitations"> diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index e0a98bc5385..a1703155854 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -38,6 +38,10 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> DETACH PARTITION <replaceable class="parameter">partition_name</replaceable> [ CONCURRENTLY | FINALIZE ] ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> + SPLIT PARTITION <replaceable class="parameter">partition_name</replaceable> INTO + (PARTITION <replaceable class="parameter">partition_name1</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }, + PARTITION <replaceable class="parameter">partition_name2</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT } [, ...]) +ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> MERGE PARTITIONS (<replaceable class="parameter">partition_name1</replaceable>, <replaceable class="parameter">partition_name2</replaceable> [, ...]) INTO <replaceable class="parameter">partition_name</replaceable> @@ -1121,6 +1125,44 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </listitem> </varlistentry> + <varlistentry id="sql-altertable-split-partition"> + <term><literal>SPLIT PARTITION <replaceable class="parameter">partition_name</replaceable> INTO (PARTITION <replaceable class="parameter">partition_name1</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }, PARTITION <replaceable class="parameter">partition_name2</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT } [, ...])</literal></term> + + <listitem> + <para> + This form splits a single partition of the target table. Hash-partitioning + is not supported. Bounds of new partitions should not overlap with new and + existing partitions (except <replaceable class="parameter">partition_name</replaceable>). + If the split partition is a DEFAULT partition, one of the new partitions must be DEFAULT. + In case one of the new partitions or one of existing partitions is DEFAULT, + new partitions <replaceable class="parameter">partition_name1</replaceable>, + <replaceable class="parameter">partition_name2</replaceable>, ... can have spaces + between partitions bounds. If the partitioned table does not have a DEFAULT + partition, the DEFAULT partition can be defined as one of the new partitions. + </para> + <para> + In case new partitions do not contain a DEFAULT partition and the partitioned table + does not have a DEFAULT partition, the following must be true: sum bounds of + new partitions <replaceable class="parameter">partition_name1</replaceable>, + <replaceable class="parameter">partition_name2</replaceable>, ... should be + equal to bound of split partition <replaceable class="parameter">partition_name</replaceable>. + One of the new partitions <replaceable class="parameter">partition_name1</replaceable>, + <replaceable class="parameter">partition_name2</replaceable>, ... can have + the same name as split partition <replaceable class="parameter">partition_name</replaceable> + (this is suitable in case of splitting a DEFAULT partition: we split it, but after + splitting we have a partition with the same name). + Only simple, non-partitioned partition can be split. + </para> + <note> + <para> + This command acquires an <literal>ACCESS EXCLUSIVE</literal> lock. + This is a significant limitation, which limits the usage of this + command with large partitioned tables under a high load. + </para> + </note> + </listitem> + </varlistentry> + <varlistentry id="sql-altertable-merge-partitions"> <term><literal>MERGE PARTITIONS (<replaceable class="parameter">partition_name1</replaceable>, <replaceable class="parameter">partition_name2</replaceable> [, ...]) INTO <replaceable class="parameter">partition_name</replaceable></literal></term> @@ -1188,7 +1230,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM All the forms of ALTER TABLE that act on a single table, except <literal>RENAME</literal>, <literal>SET SCHEMA</literal>, <literal>ATTACH PARTITION</literal>, <literal>DETACH PARTITION</literal>, - and <literal>MERGE PARTITIONS</literal> can be combined into + <literal>SPLIT PARTITION</literal>, and <literal>MERGE PARTITIONS</literal> + 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 @@ -1432,7 +1475,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <listitem> <para> 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. + or the name of split partition, or the name of the new merged partition. </para> </listitem> </varlistentry> @@ -1849,6 +1892,24 @@ ALTER TABLE measurement </programlisting></para> <para> + To split a single partition of the range-partitioned table: +<programlisting> +ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2023 INTO + (PARTITION sales_feb2023 FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'), + PARTITION sales_mar2023 FOR VALUES FROM ('2023-03-01') TO ('2023-04-01'), + PARTITION sales_apr2023 FOR VALUES FROM ('2023-04-01') TO ('2023-05-01')); +</programlisting></para> + + <para> + To split a single partition of the list-partitioned table: +<programlisting> +ALTER TABLE sales_list SPLIT PARTITION sales_all INTO + (PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'), + PARTITION sales_east FOR VALUES IN ('Bejing', 'Delhi', 'Vladivostok'), + PARTITION sales_central FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv')); +</programlisting></para> + + <para> To merge several partitions into one partition of the target table: <programlisting> ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_central) |