Re: Automating Partitions in PostgreSQL - Query on syntax

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 ?).

Cheers,
Csaba.