Examples required in || 5.10. Table Partitioning

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Examples required in || 5.10. Table Partitioning

apt.postgresql.org Repository Update
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 .

Kind Regards,
Tanay Purnaye.
Reply | Threaded
Open this post in threaded view
|

Re: Examples required in || 5.10. Table Partitioning

Bruce Momjian
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  <[hidden email]>        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 | Threaded
Open this post in threaded view
|

Re: Examples required in || 5.10. Table Partitioning

Tanay Purnaye
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 <[hidden email]> 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  <[hidden email]>        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 | Threaded
Open this post in threaded view
|

Re: Examples required in || 5.10. Table Partitioning

Bruce Momjian
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 <[hidden email]> 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  <[hidden email]>        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  <[hidden email]>        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 +