Range partitioning and overlap

On Fri, Nov 13, 2020 at 1:29 PM Edson Richter <edsonrichter@hotmail.com>
wrote:

"Range Partitioning

The table is partitioned into “ranges” defined by a key column or set of
columns, with no overlap between the ranges of values assigned to different
partitions. For example, one might partition by date ranges, or by ranges
of identifiers for particular business objects."
Is there a misinterpretation from my side, or examples are inconsistent
with the paragraph above?

Further on the documentation: "When creating a range partition, the lower
bound specified with FROM is an inclusive bound, whereas the upper bound
specified with TO is an exclusive bound."

David J.

De: David G. Johnston <david.g.johnston@gmail.com>
Enviado: sexta-feira, 13 de novembro de 2020 17:32
Para: Edson Richter <edsonrichter@hotmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Assunto: Re: Range partitioning and overlap

On Fri, Nov 13, 2020 at 1:29 PM Edson Richter <edsonrichter@hotmail.com<mailto:edsonrichter@hotmail.com>> wrote:
"Range Partitioning

The table is partitioned into “ranges” defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions. For example, one might partition by date ranges, or by ranges of identifiers for particular business objects."

Is there a misinterpretation from my side, or examples are inconsistent with the paragraph above?

Further on the documentation: "When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound."

David J.

I'm pretty sure I cannot find this statement in PostgreSQL 13 documentation page about partitioning. May be this statement is in another page?
Would you mind to share the URL with this statement?

Thanks,

Edson

Edson Richter <edsonrichter@hotmail.com> writes:

Further on the documentation: "When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound."

I'm pretty sure I cannot find this statement in PostgreSQL 13 documentation page about partitioning. May be this statement is in another page?

It's in the CREATE TABLE reference page. Seems like it would be a good
idea to have it also in ddl.sgml's discussion of partitioning, though.

regards, tom lane

De: Tom Lane <tgl@sss.pgh.pa.us>
Enviado: sexta-feira, 13 de novembro de 2020 17:58
Para: Edson Richter <edsonrichter@hotmail.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-general <pgsql-general@postgresql.org>
Assunto: Re: Range partitioning and overlap

Edson Richter <edsonrichter@hotmail.com> writes:

Further on the documentation: "When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound."

I'm pretty sure I cannot find this statement in PostgreSQL 13 documentation page about partitioning. May be this statement is in another page?

It's in the CREATE TABLE reference page. Seems like it would be a good
idea to have it also in ddl.sgml's discussion of partitioning, though.

regards, tom lane

Thanks!

Regards,

Edson

[ redirecting to pgsql-docs ]

I wrote:

Edson Richter <edsonrichter@hotmail.com> writes:

Further on the documentation: "When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound."

I'm pretty sure I cannot find this statement in PostgreSQL 13 documentation page about partitioning. May be this statement is in another page?

It's in the CREATE TABLE reference page. Seems like it would be a good
idea to have it also in ddl.sgml's discussion of partitioning, though.

I went to do that, and soon decided that section 5.11 (Table Partitioning)
really could stand a fair amount of editorial attention. There's a lot
of less than pitch-perfect English, paragraphs that were evidently written
with only minimal attention to nearby existing material, diving into the
weedy details in even the earliest introductory paras, and so on. I
propose the attached.

regards, tom lane

On Fri, Nov 13, 2020 at 7:13 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

[ redirecting to pgsql-docs ]

I wrote:

Edson Richter <edsonrichter@hotmail.com> writes:

Further on the documentation: "When creating a range partition, the

lower bound specified with FROM is an inclusive bound, whereas the upper
bound specified with TO is an exclusive bound."

I'm pretty sure I cannot find this statement in PostgreSQL 13

documentation page about partitioning. May be this statement is in another
page?

It's in the CREATE TABLE reference page. Seems like it would be a good
idea to have it also in ddl.sgml's discussion of partitioning, though.

I went to do that, and soon decided that section 5.11 (Table Partitioning)
really could stand a fair amount of editorial attention. There's a lot
of less than pitch-perfect English, paragraphs that were evidently written
with only minimal attention to nearby existing material, diving into the
weedy details in even the earliest introductory paras, and so on. I
propose the attached.

Looks good to me (just read the patch), Thanks! Just one suggestion toward
the top:

...allows you to (specify declaratively => declare) that a table is divided
into partitions. (specification becomes declaration further down)

I am curious as to your thoughts on unique indexes and whether/how to
better incorporate advice regarding the use of ON CONFLICT with
partitioning [1]/messages/by-id/CAKFQuwYLtjoPh6Crrr1b2e92NSWJDLPE1W08C63u3JU9RBjooA@mail.gmail.com vis-a-vis the overview's claim of:

"The partitioning substitutes for leading columns of indexes, reducing
index size and making it more likely that the heavily-used parts of the
indexes fit in memory" [2]https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-OVERVIEW

David J.

[1]: /messages/by-id/CAKFQuwYLtjoPh6Crrr1b2e92NSWJDLPE1W08C63u3JU9RBjooA@mail.gmail.com
/messages/by-id/CAKFQuwYLtjoPh6Crrr1b2e92NSWJDLPE1W08C63u3JU9RBjooA@mail.gmail.com
[2]: https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-OVERVIEW
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-OVERVIEW

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Looks good to me (just read the patch), Thanks! Just one suggestion toward
the top:

...allows you to (specify declaratively => declare) that a table is divided
into partitions. (specification becomes declaration further down)

Sure. That's a little further from the previous wording than what
I had, but it's probably better.

I am curious as to your thoughts on unique indexes and whether/how to
better incorporate advice regarding the use of ON CONFLICT with
partitioning [1] vis-a-vis the overview's claim of:
"The partitioning substitutes for leading columns of indexes, reducing
index size and making it more likely that the heavily-used parts of the
indexes fit in memory" [2]

From a semantic standpoint, there's no doubt that requiring unique indexes
to include the partition key is fine and necessary. (I tried to explain
why in this rewrite.) In the case of list partitioning with a single
value per partition, it's conceivable that we could drop the partition key
column from the index implementing the constraint on that partition, but
it'd be a bit of a wart and I'm not sure that it'd be worth the trouble.
Anyplace where a partition can have more than one value of the partition
key column, you still need that column in the index.

I think what the docs are talking about here is indexes that are *not*
declared unique, or that are declared unique but are created per-partition
rather than globally. In those cases you can leave off the partition key
and the index will still do what you need.

Possibly a better way to write that claim is that partitioning can
substitute for the upper levels of a huge index, rather than "leading
columns" per se. That way of looking at it is still sensible when
a partition covers more than one value of the key column.

regards, tom lane

I wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

I am curious as to your thoughts on unique indexes and whether/how to
better incorporate advice regarding the use of ON CONFLICT with
partitioning [1] vis-a-vis the overview's claim of:
"The partitioning substitutes for leading columns of indexes, reducing
index size and making it more likely that the heavily-used parts of the
indexes fit in memory" [2]

Possibly a better way to write that claim is that partitioning can
substitute for the upper levels of a huge index, rather than "leading
columns" per se. That way of looking at it is still sensible when
a partition covers more than one value of the key column.

I changed it like that and pushed.

regards, tom lane

On Fri, Nov 13, 2020 at 2:08 PM Edson Richter <edsonrichter@hotmail.com>
wrote:

*De:* Tom Lane <tgl@sss.pgh.pa.us>
*Enviado:* sexta-feira, 13 de novembro de 2020 17:58
*Para:* Edson Richter <edsonrichter@hotmail.com>
*Cc:* David G. Johnston <david.g.johnston@gmail.com>; pgsql-general <
pgsql-general@postgresql.org>
*Assunto:* Re: Range partitioning and overlap

Edson Richter <edsonrichter@hotmail.com> writes:

Further on the documentation: "When creating a range partition, the

lower bound specified with FROM is an inclusive bound, whereas the upper
bound specified with TO is an exclusive bound."

I'm pretty sure I cannot find this statement in PostgreSQL 13

documentation page about partitioning. May be this statement is in another
page?

It's in the CREATE TABLE reference page. Seems like it would be a good
idea to have it also in ddl.sgml's discussion of partitioning, though.

Patch thread with commit:

/messages/by-id/DM6PR13MB3988736CF8F5DC5720440231CFE60@DM6PR13MB3988.namprd13.prod.outlook.com

David J.