Re: Examples required in || 5.10. Table Partitioning - Mailing list pgsql-docs

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 +