Horton

Thread

Commits

  1. Doc: improve partitioning discussion in ddl.sgml.

  1. Range partitioning and overlap

    Edson Richter <edsonrichter@hotmail.com> — 2020-11-13T20:29:22Z

    Hi,

Using PostgreSQL 13.1 - I need your guidance about corretly implementing partition by timestamp ranges.

Looking at documentation ( https://www.postgresql.org/docs/13/ddl-partitioning.html ) there a statement saying explicit

"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."


But afterwards, looking into examples, the ranges overlaps:


CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
    FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

CREATE TABLE measurement_y2007m12 PARTITION OF measurement
    FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
    TABLESPACE fasttablespace;

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

Thanks,

Edson

  2. Re: Range partitioning and overlap

    David G. Johnston <david.g.johnston@gmail.com> — 2020-11-13T20:32:52Z

    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.

  3. RE: Range partitioning and overlap

    Edson Richter <edsonrichter@hotmail.com> — 2020-11-13T20:46:43Z

    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

  4. Re: Range partitioning and overlap

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-11-13T20:58:04Z

    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

  5. RE: Range partitioning and overlap

    Edson Richter <edsonrichter@hotmail.com> — 2020-11-13T21:08:06Z

    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

  6. Partitioning docs (was Re: Range partitioning and overlap)

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-11-14T02:13:34Z

    [ 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

  7. Re: Partitioning docs (was Re: Range partitioning and overlap)

    David G. Johnston <david.g.johnston@gmail.com> — 2020-11-14T02:43:00Z

    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] 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]

David J.

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

  8. Re: Partitioning docs (was Re: Range partitioning and overlap)

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-11-14T03:15:48Z

    "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

  9. Re: Partitioning docs (was Re: Range partitioning and overlap)

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-11-14T18:11:29Z

    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

  10. Re: Range partitioning and overlap

    David G. Johnston <david.g.johnston@gmail.com> — 2020-11-15T04:53:28Z

    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:

https://www.postgresql.org/message-id/DM6PR13MB3988736CF8F5DC5720440231CFE60@DM6PR13MB3988.namprd13.prod.outlook.com

David J.