From: Bruce Momjian <bruce@momjian.us>
To: tanay.purnaye@gmail.com
To: pgsql-docs@lists.postgresql.org
Subject: Re: Examples required in || 5.10. Table Partitioning
Date: Sat, 14 Mar 2020 15:42:20 -0400
Message-ID: <20200314194220.GA22336@momjian.us> (raw)
In-Reply-To: <158099182679.1093.11650509001908549339@wrigleys.postgresql.org>
References: <158099182679.1093.11650509001908549339@wrigleys.postgresql.org>
On Thu, Feb 6, 2020 at 12:23:46PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/12/ddl-partitioning.html
> Description:
>
> Hello ,
>
> As I'm searching for the official documentation of Hash Partition and List
> Partition with example with more description the only information is found
> is as below :
>
> List Partitioning
> The table is partitioned by explicitly listing which key values appear in
> each partition.
>
> Hash Partitioning
> The table is partitioned by specifying a modulus and a remainder for each
> partition. Each partition will hold the rows for which the hash value of the
> partition key divided by the specified modulus will produce the specified
> remainder.
>
> But how to create and manage these above 2 partition is not explained in
> documentation properly officially.for further information related to these 2
> partition we need to search private blogs,because of lack of information
> provided in the documentation 5.10. Table Partitioning I only saw the Range
> partition example throughout the Table Partitioning .
>
> I request you to modify the 5.10. Table Partitioning section and make it
> more informative as Table Partition is very important in PostgreSQL .
Well, there are examples in the CREATE TABLE manual page:
https://www.postgresql.org/docs/12/sql-createtable.html
When creating a hash partition, a modulus and remainder must be
specified. The modulus must be a positive integer, and the remainder
must be a non-negative integer less than the modulus. Typically, when
initially setting up a hash-partitioned table, you should choose a
modulus equal to the number of partitions and assign every table the
same modulus and a different remainder (see examples, below). However,
it is not required that every partition have the same modulus, only that
every modulus which occurs among the partitions of a hash-partitioned
table is a factor of the next larger modulus. This allows the number of
partitions to be increased incrementally without needing to move all the
data at once. For example, suppose you have a hash-partitioned table
with 8 partitions, each of which has modulus 8, but find it necessary to
increase the number of partitions to 16. You can detach one of the
modulus-8 partitions, create two new modulus-16 partitions covering the
same portion of the key space (one with a remainder equal to the
remainder of the detached partition, and the other with a remainder
equal to that value plus 8), and repopulate them with data. You can then
repeat this -- perhaps at a later time -- for each modulus-8 partition
until none remain. While this may still involve a large amount of data
movement at each step, it is still better than having to create a whole
new table and move all the data at once.
CREATE TABLE orders (
order_id bigint not null,
cust_id bigint not null,
status text
) PARTITION BY HASH (order_id);
CREATE TABLE orders_p1 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p2 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p3 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p4 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
CREATE TABLE cities (
city_id bigserial not null,
name text not null,
population bigint
) PARTITION BY LIST (left(lower(name), 1));
CREATE TABLE cities_ab
PARTITION OF cities (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('a', 'b');
Is that sufficient?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, tanay.purnaye@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: Examples required in || 5.10. Table Partitioning
In-Reply-To: <20200314194220.GA22336@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox