Re: Transparent table partitioning in future version of PG?

On 5/1/09 7:32 AM, "henk de wit" <henk53602(at)hotmail(dot)com> wrote:

> Hi,
>
> I was looking at the support that PostgreSQL offers for table partitioning
> at http://www.postgresql.org/docs/8.4/static/ddl-partitioning.html. The
> concept looks promising, but its maybe fair to say that PG itself doesn't
> really supports partitioning natively, but one can simulate it using some of
> the existing PG features (namely inheritance, triggers, rules and constraint
> exclusion). This simulating does seem to work, but there are some
> disadvantages and caveats. 
>
> A major disadvantage is obviously that you need to set up and maintain the
> whole structure yourself (which is somewhat dangerous, or at least involves a
> lot of maintenance overhead). Next to that, it seemingly becomes hard to do
> simple queries likes 'select * from foo where bar> 1000 and bar < 5000', in
> case the answer to this query spans multiple partitions. constraint exclusion
> works to some degree, but the document I referred to above tells me I can no
> longer use prepared statements then.

More caveats:

Query plans go bad pretty quickly because the planner doesn't aggregate
statistics correctly when scanning more than one table.

Constraint exclusion code is completely and utterly broken if the table
count gets large on DELETE or UPDATE queries -- I can get the query planner
/ constraint exclusion stuff to eat up 7GB of RAM trying to figure out what
table to access when the number of partitions ~=6000.
The same thing in select form doesn't consume that memory but still takes
over a second.

This is "not a bug".
http://www.nabble.com/8.3.5:-Query-Planner-takes-15%2B-seconds-to-plan-Updat
e-or-Delete-queries-on-partitioned-tables.-td21992054.html

Its pretty much faster to do merge joins or hash joins client side on
multiple tables -- basically doing partitioning client side -- after a point
and for any more complicated aggregation or join.

There is a lot of talk about overly complicated partitioning or
auto-partitioning, but two much more simple things would go a long way to
making this fairly workable:

Make stat aggregation across tables better -- use weighted average for
estimating row width, aggregate distinct counts and correlations better.
Right now it mostly assumes the worst possible case and can end up with very
unoptimal plans.

Make a special case for "unique" child inheritance constraints that can be
checked much faster -- nobody wants to partition and have overlapping
constraint regions. And whatever is going on for it on the update / delete
side that causes it to take so much longer and use so much more memory for
what should be the same constraint exclusion check as a select needs to be
attended to.

There would still be manual work for managing creating partitions, but at
this point, that is the _least_ of the problems.

>
> I wonder if there are any plans to incorporate 'native' or 'transparent'
> partitioning in some future version of PG? With this I mean that I would
> basically be able to say something like (pseudo): "alter table foo partition
> on bar range 100", and PG would then simply start doing internally what we now
> have to do manually.
>
> Is something like this on the radar or is it just wishful thinking of me?
>
> Kind regards
>
>
>
>
> What can you do with the new Windows Live? Find out
> <http://www.microsoft.com/windows/windowslive/default.aspx>