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 <br...@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 <br...@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 + >