🌐 AI搜索 & 代理 主页
Skip to content

Commit 4b3d173

Browse files
akorotkovtenderwgguofengrichardilmariMasaoFujii
committed
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>
1 parent f2e4cc4 commit 4b3d173

File tree

20 files changed

+4585
-40
lines changed

20 files changed

+4585
-40
lines changed

‎doc/src/sgml/ddl.sgml‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4764,6 +4764,25 @@ ALTER TABLE measurement
47644764
measurement_y2006m03) INTO measurement_y2006q1;
47654765
</programlisting>
47664766
</para>
4767+
4768+
<para>
4769+
Similarly to merging multiple table partitions, there is an option for
4770+
splitting a single partition into multiple using the
4771+
<link linkend="sql-altertable-split-partition"><command>ALTER TABLE ... SPLIT PARTITION</command></link>.
4772+
This feature could come in handy when one partition grows too big
4773+
and needs to be split into multiple. It's important to note that
4774+
this operation is not supported for hash-partitioned tables and acquires
4775+
an <literal>ACCESS EXCLUSIVE</literal> lock, which could impact high-load
4776+
systems due to the lock's restrictive nature. For example, we can split
4777+
the quarter partition back to monthly partitions:
4778+
<programlisting>
4779+
ALTER TABLE measurement SPLIT PARTITION measurement_y2006q1 INTO
4780+
(PARTITION measurement_y2006m01 FOR VALUES FROM ('2006-01-01') TO ('2006-02-01'),
4781+
PARTITION measurement_y2006m02 FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'),
4782+
PARTITION measurement_y2006m03 FOR VALUES FROM ('2006-03-01') TO ('2006-04-01'));
4783+
</programlisting>
4784+
</para>
4785+
47674786
</sect3>
47684787

47694788
<sect3 id="ddl-partitioning-declarative-limitations">

‎doc/src/sgml/ref/alter_table.sgml‎

Lines changed: 115 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,10 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
3939
DETACH PARTITION <replaceable class="parameter">partition_name</replaceable> [ CONCURRENTLY | FINALIZE ]
4040
ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
4141
MERGE PARTITIONS (<replaceable class="parameter">partition_name1</replaceable>, <replaceable class="parameter">partition_name2</replaceable> [, ...]) INTO <replaceable class="parameter">partition_name</replaceable>
42+
ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
43+
SPLIT PARTITION <replaceable class="parameter">partition_name</replaceable> INTO
44+
(PARTITION <replaceable class="parameter">partition_name1</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT },
45+
PARTITION <replaceable class="parameter">partition_name2</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT } [, ...])
4246

4347
<phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase>
4448

@@ -1258,14 +1262,103 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
12581262
</listitem>
12591263
</varlistentry>
12601264

1265+
<varlistentry id="sql-altertable-split-partition">
1266+
<term>
1267+
<literal>SPLIT PARTITION <replaceable class="parameter">partition_name</replaceable> INTO (
1268+
PARTITION <replaceable class="parameter">partition_name1</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT },
1269+
PARTITION <replaceable class="parameter">partition_name2</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }
1270+
[, ...])</literal>
1271+
</term>
1272+
1273+
<listitem>
1274+
<para>
1275+
This form splits a single partition of the target table into new
1276+
partitions. Hash-partitioned target table is not supported.
1277+
Only a simple, non-partitioned partition can be split.
1278+
If the split partition is the <literal>DEFAULT</literal> partition,
1279+
one of the new partitions must be <literal>DEFAULT</literal>.
1280+
If the partitioned table does not have a <literal>DEFAULT</literal>
1281+
partition, a <literal>DEFAULT</literal> partition can be defined as one
1282+
of the new partitions.
1283+
</para>
1284+
1285+
<para>
1286+
The bounds of new partitions should not overlap with those of new or
1287+
existing partitions (except <replaceable class="parameter">partition_name</replaceable>).
1288+
The combined bounds of new partitions <literal>
1289+
<replaceable class="parameter">partition_name1</replaceable>,
1290+
<replaceable class="parameter">partition_name2</replaceable>[, ...]
1291+
</literal> should be equal to the bounds of the split partition
1292+
<replaceable class="parameter">partition_name</replaceable>.
1293+
One of the new partitions can have the same name as the split partition
1294+
<replaceable class="parameter">partition_name</replaceable>
1295+
(this is suitable in case of splitting the <literal>DEFAULT</literal>
1296+
partition: after the split, the <literal>DEFAULT</literal> partition
1297+
remains with the same name, but its partition bound changes).
1298+
</para>
1299+
1300+
<para>
1301+
New partitions will have the same owner as the parent partition.
1302+
It is the user's responsibility to setup <acronym>ACL</acronym> on new
1303+
partitions.
1304+
</para>
1305+
1306+
<para>
1307+
<command>ALTER TABLE SPLIT PARTITION</command> uses the partitioned
1308+
table itself as the template to construct new partitions.
1309+
New partitions will inherit the same table access method, persistence
1310+
type, and tablespace as the partitioned table.
1311+
</para>
1312+
1313+
<para>
1314+
Constraints, column defaults, column generation expressions,
1315+
identity columns, indexes, and triggers are copied from the partitioned
1316+
table to the new partitions. But extended statistics, security
1317+
policies, etc, won't be copied from the partitioned table.
1318+
Indexes and identity columns copied from the partitioned table will be
1319+
created afterward, once the data has been moved into the new partitions.
1320+
</para>
1321+
1322+
<para>
1323+
When a partition is split, any objects that depend on this partition,
1324+
such as constraints, triggers, extended statistics, etc, will be dropped.
1325+
This occurs because <command>ALTER TABLE SPLIT PARTITION</command> uses
1326+
the partitioned table itself as the template to reconstruct these
1327+
objects later.
1328+
Eventually, we will drop the split partition
1329+
(using <literal>RESTRICT</literal> mode) too; therefore, if any objects
1330+
are still dependent on it, <command>ALTER TABLE SPLIT PARTITION</command>
1331+
would fail (see <xref linkend="ddl-depend"/>).
1332+
</para>
1333+
1334+
<note>
1335+
<para>
1336+
Split partition acquires an <literal>ACCESS EXCLUSIVE</literal> lock on
1337+
the parent table, in addition to the <literal>ACCESS EXCLUSIVE</literal>
1338+
lock on the table being split.
1339+
</para>
1340+
</note>
1341+
<note>
1342+
1343+
<para>
1344+
<command>ALTER TABLE SPLIT PARTITION</command> creates new partitions and
1345+
moves data from the split partition into them, which can take a long
1346+
time. So it is not recommended to use the command for splitting a
1347+
small fraction of rows out of a very big partition.
1348+
</para>
1349+
</note>
1350+
</listitem>
1351+
</varlistentry>
1352+
12611353
</variablelist>
12621354
</para>
12631355

12641356
<para>
12651357
All the forms of <command>ALTER TABLE</command> that act on a single table,
12661358
except <literal>RENAME</literal>, <literal>SET SCHEMA</literal>,
12671359
<literal>ATTACH PARTITION</literal>, <literal>DETACH PARTITION</literal>,
1268-
and <literal>MERGE PARTITIONS</literal>, can be combined into
1360+
<literal>MERGE PARTITIONS</literal>, and <literal>SPLIT PARTITION</literal>
1361+
can be combined into
12691362
a list of multiple alterations to be applied together. For example, it
12701363
is possible to add several columns and/or alter the type of several
12711364
columns in a single command. This is particularly useful with large
@@ -1509,7 +1602,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
15091602
<listitem>
15101603
<para>
15111604
The name of the table to attach as a new partition or to detach from this table,
1512-
or the name of the new merged partition.
1605+
or the name of split partition, or the name of the new merged partition.
15131606
</para>
15141607
</listitem>
15151608
</varlistentry>
@@ -1519,7 +1612,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
15191612
<term><replaceable class="parameter">partition_name2</replaceable></term>
15201613
<listitem>
15211614
<para>
1522-
The names of the tables being merged into the new partition.
1615+
The names of the tables being merged into the new partition or split into
1616+
new partitions.
15231617
</para>
15241618
</listitem>
15251619
</varlistentry>
@@ -1952,6 +2046,24 @@ ALTER TABLE measurement
19522046
DETACH PARTITION measurement_y2015m12;
19532047
</programlisting></para>
19542048

2049+
<para>
2050+
To split a single partition of the range-partitioned table:
2051+
<programlisting>
2052+
ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2023 INTO
2053+
(PARTITION sales_feb2023 FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'),
2054+
PARTITION sales_mar2023 FOR VALUES FROM ('2023-03-01') TO ('2023-04-01'),
2055+
PARTITION sales_apr2023 FOR VALUES FROM ('2023-04-01') TO ('2023-05-01'));
2056+
</programlisting></para>
2057+
2058+
<para>
2059+
To split a single partition of the list-partitioned table:
2060+
<programlisting>
2061+
ALTER TABLE sales_list SPLIT PARTITION sales_all INTO
2062+
(PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'),
2063+
PARTITION sales_east FOR VALUES IN ('Bejing', 'Delhi', 'Vladivostok'),
2064+
PARTITION sales_central FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv'));
2065+
</programlisting></para>
2066+
19552067
<para>
19562068
To merge several partitions into one partition of the target table:
19572069
<programlisting>

0 commit comments

Comments
 (0)