Re: Examples required in || 5.10. Table Partitioning

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(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)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 +