Re: Automating Partitions in PostgreSQL - Query on syntax

On Tue, Apr 21, 2009 at 11:50 AM, Csaba Nagy <nagy(at)ecircle-ag(dot)com> wrote:
> On Tue, 2009-04-21 at 11:43 -0400, Robert Haas wrote:
>> This doesn't sound like a very good idea, because the planner cannot
>> then rely on the overflow table not containing tuples that ought to be
>> within some other partition.
>>
>> The big win that is associated with table partitioning is using
>> constraint exclusion to avoid unnecessary partitions scans.
>
> Well it could always check 2 partitions: the overflow and the one
> selected by the constraint exclusion. If the overflow is kept empty by
> properly setting up the partitions so that all insertions always go to
> one of the active partitions, that would be cheap enough too while still
> providing a way to catch unexpected data. Then when a new partition is
> defined, there's no need to shuffle around data immediately, but there
> could be a maintenance command to clean up the overflow... not to
> mention that you could define a trigger to create the new partition once
> you get something in the overflow (how cool would that be if it would
> work ?).

Sure, you could do it that way. But it will cause problems for people
who want to have a million rows in each of 100 partitions, and another
million rows in the overflow partition. Now all operations that can
be done on a single partition must scan 2 million rows instead of 1
million, just on the off chance that someone executed a DDL command
and didn't clean up after themselves.

...Robert