8000 Add hash partitioning. · postgrespro/postgres_cluster@1aba8e6 · GitHub
[go: up one dir, main page]
Skip to content

Commit 1aba8e6

Browse files
robertmhaasrobertmhaas
committed
Add hash partitioning.
Hash partitioning is useful when you want to partition a growing data set evenly. This can be useful to keep table sizes reasonable, which makes maintenance operations such as VACUUM faster, or to enable partition-wise join. At present, we still depend on constraint exclusion for partitioning pruning, and the shape of the partition constraints for hash partitioning is such that that doesn't work. Work is underway to fix that, which should both improve performance and make partitioning pruning work with hash partitioning. Amul Sul, reviewed and tested by Dilip Kumar, Ashutosh Bapat, Yugo Nagata, Rajkumar Raghuwanshi, Jesper Pedersen, and by me. A few final tweaks also by me. Discussion: http://postgr.es/m/CAAJ_b96fhpJAP=ALbETmeLk1Uni_GFZD938zgenhF49qgDTjaQ@mail.gmail.com
1 parent e7397f0 commit 1aba8e6

30 files changed

+1420
-120
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 20 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -2875,6 +2875,19 @@ VALUES ('Albany', NULL, NULL, 'NY');
28752875
</para>
28762876
</listitem>
28772877
</varlistentry>
2878+
2879+
<varlistentry>
2880+
<term>Hash Partitioning</term>
2881+
2882+
<listitem>
2883+
<para>
2884+
The table is partitioned by specifying a modulus and a remainder for
2885+
each partition. Each partition will hold the rows for which the hash
2886+
value of the partition key divided by the specified modulus will
2887+
produce the specified remainder.
2888+
</para>
2889+
</listitem>
2890+
</varlistentry>
28782891
</variablelist>
28792892

28802893
If your application needs to use other forms of partitioning not listed
@@ -2901,9 +2914,8 @@ VALUES ('Albany', NULL, NULL, 'NY');
29012914
All rows inserted into a partitioned table will be routed to one of the
29022915
<firstterm>partitions</firstterm> based on the value of the partition
29032916
key. Each partition has a subset of the data defined by its
2904-
<firstterm>partition bounds</firstterm>. Currently supported
2905-
partitioning methods include range and list, where each partition is
2906-
assigned a range of keys and a list of keys, respectively.
2917+
<firstterm>partition bounds</firstterm>. The currently supported
2918+
partitioning methods are range, list, and hash.
29072919
</para>
29082920

29092921
<para>
@@ -3328,11 +3340,11 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
33283340

33293341
<listitem>
33303342
<para>
3331-
Declarative partitioning only supports list and range partitioning,
3332-
whereas table inheritance allows data to be divided in a manner of
3333-
the user's choosing. (Note, however, that if constraint exclusion is
3334-
unable to prune partitions effectively, query performance will be very
3335-
poor.)
3343+
Declarative partitioning only supports range, list and hash
3344+
partitioning, whereas table inheritance allows data to be divided in a
3345+
manner of the user's choosing. (Note, however, that if constraint
3346+
exclusion is unable to prune partitions effectively, query performance
3347+
will be very poor.)
33363348
</para>
33373349
</listitem>
33383350

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1431,6 +1431,13 @@ ALTER TABLE cities
14311431
ATTACH PARTITION cities_partdef DEFAULT;
14321432
</programlisting></para>
14331433

1434+
<para>
1435+
Attach a partition to hash partitioned table:
1436+
<programlisting>
1437+
ALTER TABLE orders
1438+
ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
1439+
</programlisting></para>
1440+
14341441
<para>
14351442
Detach a partition from partitioned table:
14361443
<programlisting>

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 71 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
2828
[, ... ]
2929
] )
3030
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
31-
[ PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
31+
[ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
3232
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
3333
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
3434
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
@@ -39,7 +39,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
3939
| <replaceable>table_constraint</replaceable> }
4040
[, ... ]
4141
) ]
42-
[ PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
42+
[ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
4343
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
4444
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
4545
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
@@ -50,7 +50,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
5050
| <replaceable>table_constraint</replaceable> }
5151
[, ... ]
5252
) ] { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }
53-
[ PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
53+
[ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
5454
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
5555
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
5656
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
@@ -88,7 +88,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
8888

8989
IN ( { <replaceable class="parameter">numeric_literal</replaceable> | <replaceable class="parameter">string_literal</replaceable> | NULL } [, ...] ) |
9090
FROM ( { <replaceable class="parameter">numeric_literal</replaceable> | <replaceable class="parameter">string_literal</replaceable> | MINVALUE | MAXVALUE } [, ...] )
91-
TO ( { <replaceable class="parameter">numeric_literal</replaceable> | <replaceable class="parameter">string_literal</replaceable> | MINVALUE | MAXVALUE } [, ...] )
91+
TO ( { <replaceable class="parameter">numeric_literal</replaceable> | <replaceable class="parameter">string_literal</replaceable> | MINVALUE | MAXVALUE } [, ...] ) |
92+
WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REMAINDER <replaceable class="parameter">numeric_literal</replaceable> )
9293

9394
<phrase><replaceable class="parameter">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase>
9495

@@ -256,16 +257,18 @@ FROM ( { <replaceable class="parameter">numeric_literal</replaceable> | <replace
256257
Creates the table as a <firstterm>partition</firstterm> of the specified
257258
parent table. The table can be created either as a partition for specific
258259
values using <literal>FOR VALUES</literal> or as a default partition
259-
using <literal>DEFAULT</literal>.
260+
using <literal>DEFAULT</literal>. This option is not available for
261+
hash-partitioned tables.
260262
</para>
261263

262264
<para>
263265
The <replaceable class="parameter">partition_bound_spec</replaceable>
264266
must correspond to the partitioning method and partition key of the
265267
parent table, and must not overlap with any existing partition of that
266268
parent. The form with <literal>IN</literal> is used for list partitioning,
267-
while the form with <literal>FROM</literal> and <literal>TO</literal> is used for
268-
range partitioning.
269+
the form with <literal>FROM</literal> and <literal>TO</literal> is used
270+
for range partitioning, and the form with <literal>WITH</literal> is used
271+
for hash partitioning.
269272
</para>
270273

271274
<para>
@@ -363,6 +366,29 @@ FROM ( { <replaceable class="parameter">numeric_literal</replaceable> | <replace
363366
partition.
364367
</para>
365368

369+
<para>
370+
When creating a hash partition, a modulus and remainder must be specified.
371+
The modulus must be a positive integer, and the remainder must be a
372+
non-negative integer less than the modulus. Typically, when initially
373+
setting up a hash-partitioned table, you should choose a modulus equal to
374+
the number of partitions and assign every table the same modulus and a
375+
different remainder (see examples, below). However, it is not required
376+
that every partition have the same modulus, only that every modulus which
377+
occurs among the partitions of a hash-partitioned table is a factor of the
378+
next larger modulus. This allows the number of partitions to be increased
379+
incrementally without needing to move all the data at once. For example,
380+
suppose you have a hash-partitioned table with 8 partitions, each of which
381+
has modulus 8, but find it necessary to increase the number of partitions
382+
to 16. You can detach one of the modulus-8 partitions, create two new
383+
modulus-16 partitions covering the same portion of the key space (one with
384+
a remainder equal to the remainder of the detached partition, and the
385+
other with a remainder equal to that value plus 8), and repopulate them
386+
with data. You can then repeat this -- perhaps at a later time -- for
387+
each modulus-8 partition until none remain. While this may still involve
388+
a large amount of data movement at each step, it is still better than
389+
having to create a whole new table and move all the data at once.
390+
</para>
391+
366392
<para>
367393
A partition must have the same column names and types as the partitioned
368394
table to which it belongs. If the parent is specified <literal>WITH
@@ -486,20 +512,28 @@ FROM ( { <replaceable class="parameter">numeric_literal</replaceable> | <replace
486512
</varlistentry>
487513

488514
<varlistentry>
489-
<term><literal>PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [, ...] ) </literal></term>
515+
<term><literal>PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [, ...] ) </literal></term>
490516
<listitem>
491517
<para>
492518
The optional <literal>PARTITION BY</literal> clause specifies a strategy
493519
of partitioning the table. The table thus created is called a
494520
<firstterm>partitioned</firstterm> table. The parenthesized list of
495521
columns or expressions forms the <firstterm>partition key</firstterm>
496-
for the table. When using range partitioning, the partition key can
497-
include multiple columns or expressions (up to 32, but this limit can be
498-
altered when building <productname>PostgreSQL</productname>), but for
522+
for the table. When using range or hash partitioning, the partition key
523+
can include multiple columns or expressions (up to 32, but this limit can
524+
be altered when building <productname>PostgreSQL</productname>), but for
499525
list partitioning, the partition key must consist of a single column or
500-
expression. If no B-tree operator class is specified when creating a
501-
partitioned table, the default B-tree operator class for the datatype will
502-
be used. If there is none, an error will be reported.
526+
expression.
527+
</para>
528+
529+
<para>
530+
Range and list partitioning require a btree operator class, while hash
531+
partitioning requires a hash operator class. If no operator class is
532+
specified explicitly, the default operator class of the appropriate
533+
type will be used; if no default operator class exists, an error will
534+
be raised. When hash partitioning is used, the operator class used
535+
must implement support function 2 (see <xref linkend="xindex-support">
536+
for details).
503537
</para>
504538

505539
<para>
@@ -1647,6 +1681,16 @@ CREATE TABLE cities (
16471681
name text not null,
16481682
population bigint
16491683
) PARTITION BY LIST (left(lower(name), 1));
1684+
</programlisting></para>
1685+
1686+
<para>
1687+
Create a hash partitioned table:
1688+
<programlisting>
1689+
CREATE TABLE orders (
1690+
order_id bigint not null,
1691+
cust_id bigint not null,
1692+
status text
1693+
) PARTITION BY HASH (order_id);
16501694
</programlisting></para>
16511695

16521696
<para>
@@ -1701,6 +1745,19 @@ CREATE TABLE cities_ab_10000_to_100000
17011745
PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
17021746
</programlisting></para>
17031747

1748+
<para>
1749+
Create partitions of a hash partitioned table:
1750+
<programlisting>
1751+
CREATE TABLE orders_p1 PARTITION OF orders
1752+
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
1753+
CREATE TABLE orders_p2 PARTITION OF orders
1754+
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
1755+
CREATE TABLE orders_p3 PARTITION OF orders
1756+
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
1757+
CREATE TABLE orders_p4 PARTITION OF orders
1758+
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
1759+
</programlisting></para>
1760+
17041761
<para>
17051762
Create a default partition:
17061763
<programlisting>

0 commit comments

Comments
 (0)
0