Thread: Examples required in || 5.10. Table Partitioning
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.
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 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 +
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 +