Re: Transparent table partitioning in future version of PG?

On 5/7/09 7:52 PM, "david(at)lang(dot)hm" <david(at)lang(dot)hm> wrote:

>
>
> I believe that the isolation plan is probably more common than the load
> balancing plan, but I don't see them as being that different for the
> database engine point of view. To tune a system that can handle the
> isolation plan for load balancing, the key thing to do would be to have a
> knob to disable the partition planning, and just blindly send the search
> out to every partition.

Lots of good points. However, implicit in the above is that the process of
identifying which partitions contain the data is expensive.
Right now it is (1.5 sec if 6000 partitions with the most simple possible
constraint (column = CONSTANT).

But identifying which partitions can contain a value is really nothing more
than an index. If you constrain the possible partitioning functions to
those where a single partition key can only exist in one partition, then
this index and its look up should be very fast even for large partition
counts. From what I can tell empirically, the current system does this in
more of a sequential scan, running the constraint checks for each
possibility.
Furthremore, the actual tables don't have to contain the data if the key is
a column identity function (date = X ) rather than a range or hash.

At the core, partitioning is really just a form of 'chunky' indexing that
doesn't fragment, or need re-indexing, or have much MVCC complexity.

>
> David Lang
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>