pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedExamples required in || 5.10. Table Partitioning
4+ messages / 3 participants
[nested] [flat]
* Examples required in || 5.10. Table Partitioning
@ 2020-02-06 12:23 PG Doc comments form <noreply@postgresql.org>
2020-03-14 19:42 ` Re: Examples required in || 5.10. Table Partitioning Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: PG Doc comments form @ 2020-02-06 12:23 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: tanay.purnaye@gmail.com
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Examples required in || 5.10. Table Partitioning
2020-02-06 12:23 Examples required in || 5.10. Table Partitioning PG Doc comments form <noreply@postgresql.org>
@ 2020-03-14 19:42 ` Bruce Momjian <bruce@momjian.us>
2020-03-24 07:06 ` Re: Examples required in || 5.10. Table Partitioning Tanay Purnaye <tanay.purnaye@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Bruce Momjian @ 2020-03-14 19:42 UTC (permalink / raw)
To: tanay.purnaye@gmail.com; pgsql-docs@lists.postgresql.org
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 +
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Examples required in || 5.10. Table Partitioning
2020-02-06 12:23 Examples required in || 5.10. Table Partitioning PG Doc comments form <noreply@postgresql.org>
2020-03-14 19:42 ` Re: Examples required in || 5.10. Table Partitioning Bruce Momjian <bruce@momjian.us>
@ 2020-03-24 07:06 ` Tanay Purnaye <tanay.purnaye@gmail.com>
2020-03-24 14:23 ` Re: Examples required in || 5.10. Table Partitioning Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: Tanay Purnaye @ 2020-03-24 07:06 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs@lists.postgresql.org
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 +
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Examples required in || 5.10. Table Partitioning
2020-02-06 12:23 Examples required in || 5.10. Table Partitioning PG Doc comments form <noreply@postgresql.org>
2020-03-14 19:42 ` Re: Examples required in || 5.10. Table Partitioning Bruce Momjian <bruce@momjian.us>
2020-03-24 07:06 ` Re: Examples required in || 5.10. Table Partitioning Tanay Purnaye <tanay.purnaye@gmail.com>
@ 2020-03-24 14:23 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 4+ messages in thread
From: Bruce Momjian @ 2020-03-24 14:23 UTC (permalink / raw)
To: Tanay Purnaye <tanay.purnaye@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
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 +
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-03-24 14:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-06 12:23 Examples required in || 5.10. Table Partitioning PG Doc comments form <noreply@postgresql.org>
2020-03-14 19:42 ` Bruce Momjian <bruce@momjian.us>
2020-03-24 07:06 ` Tanay Purnaye <tanay.purnaye@gmail.com>
2020-03-24 14:23 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox