Re: On partitioning

Amit Langote wrote:

> From: Robert Haas [mailto:robertmhaas(at)gmail(dot)com]

> > What is an overflow partition and why do we want that?
>
> That would be a default partition. That is, where the tuples that
> don't belong elsewhere (other defined partitions) go. VALUES clause of
> the definition for such a partition would look like:
>
> (a range partition) ... VALUES LESS THAN MAXVALUE
> (a list partition) ... VALUES DEFAULT
>
> There has been discussion about whether there shouldn't be such a
> place for tuples to go. That is, it should generate an error if a
> tuple can't go anywhere (or support auto-creating a new one like in
> interval partitioning?)

In my design I initially had overflow partitions too, because I
inherited the idea from Itagaki Takahiro's patch. Eventually I realized
that it's a useless concept, because you can always have leftmost and
rightmost partitions, which are just regular partitions (except they
don't have a "low key", resp. "high key"). If you don't define
unbounded partitions at either side, it's fine, you just raise an error
whenever the user tries to insert a value for which there is no
partition.

Not real clear to me how this applies to list partitioning, but I have
the hunch that it'd be better to deal with that without overflow
partitions as well.

BTW I think auto-creating partitions is a bad idea in general, because
you get into lock escalation mess and furthermore you have to waste time
checking for existance beforehand, which lowers performance. Just have
a very easy command that users can run ahead of time (something like
"CREATE PARTITION FOR VALUE now() + '30 days'", whatever), and
preferrably one that doesn't fail if the partition already exist; that
way, users can have (for instance) a daily create-30-partitions-ahead
procedure which most days would only create one partition (the one for
30 days in the future) but whenever the odd case happens that the server
is turned off just at that time someday, it creates two -- one belt, 29
suspenders.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services