Examples required in || 5.10. Table Partitioning

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 +

Hello Bruce,

Apologies for late reply.
Thank you for acknowledging my email.

If I have any doubts regarding partition,indixing or query tuning in
feature may I email you?

Kind regards,
Tanay

On Sun, Mar 15, 2020, 1:12 AM Bruce Momjian <bruce@momjian.us> wrote:

On Tue, Mar 24, 2020 at 12:36:14PM +0530, Tanay Purnaye wrote:

Hello�Bruce,

Apologies for late reply.
Thank you�for acknowledging my�email.

If I have any doubts regarding partition,indixing or query tuning in feature
may I email you?

No, I suggest you sent it to the appropriate email list:

https://www.postgresql.org/community

You can also try the #postgresql IRC channel on irc.freenode.net. See
the PostgreSQL FAQ for more information.

---------------------------------------------------------------------------

Kind regards,
Tanay�

On Sun, Mar 15, 2020, 1:12 AM Bruce Momjian <bruce@momjian.us> wrote:

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 +

--
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 +