Re: What needs to be done for real Partitioning? - Mailing list pgsql-performance

Alvaro, Greg, Tom,

> Hmm.  You are right, but without that we won't be able to enforce
> uniqueness on the partitioned table (we could only enforce it on each
> partition, which would mean we can't partition on anything else than
> primary keys if the tables have one).  IMHO this is something to
> consider.

Sure.  However, for most partitioned use cases, the partition column will be
part of the real key of the table (for example, for a security log, the real
key might be (timestamp, machine, application, event_type) with the partition
on extract(hour from timestamp)).   As a result, there is no need to enforce
inter-partition uniqueness; the paritioning scheme enforces it already.

The only need for inter-partition uniqueness is on surrogate integer keys.
This can already be enforced de-facto simply by using a sequence.  While it
would be possible to create a uniqueness check that spans partitions, it
would be very expensive to do so, thus elminating some of the advantage of
partitioning in the first place.   I'm not saying that we won't want this
some day as an option, I just see it as a Phase III refinement.

Greg, first of all, thanks for helping clean up my muddy thinking about
implementing partitions.  Comments below:

> Phase I should be an entirely manual system where you add and remove
> partitions manually and create and drop indexes you want manually. You need
> these low level interfaces anyways for a complete system, it doesn't make
> sense to have everything automatic and then later try to wedge in a low
> level interface. Only once you have that do you then start offering options
> to do these things automatically.

This makes sense.  Thanks!

> whole system make a lot more sense: individual partitions are really
> tables. The partitioned tables themselves are just meta-objects like views.

So, like the current pseudo-partitioning implementation, partitions would be
"full tables" just with some special rules for query-rewriting when they are
pulled.  This makes sense, I think I just got carried away in another
direction.

> It's also clear that the last thing you want is an index on the partition
> key. A big part of the advantage of partitioned tables is precisely that
> you get the advantage of an index on a column without the extra expense.

Well, you need it with the current pseudo-partitioning.   What would allow us
to eliminate indexing the partition key is special re-writing rules that only
pull the partitions compliant with the outer query.  Until that step takes
place, the indexes are very much needed.  So maybe the advanced planner
rewriting is a Phase I item, not a Phase II item?

> The $64 question is how to specify the partitioning rules. That is, the
> rule for determining which partition an insert should go into and which
> partitions to look for records in. Oracle handles this by specifying a list
> of columns when creating the partitioned table and then specifying either a
> range or specific values for each individual partition. I can imagine other
> approaches but none that allow for the planner and optimizer to take as
> much advantage of the information.

Well, I would think that specifying an expression that defines a new partition
at each change in value (like EXTRACT(day FROM timestamp) on a time-based
partitioning) would cover 90% of implemenations and be a lot simpler to
administer.   The Oracle approach has the advantage of allowing "custom
paritioning" at the expense of greater complexity.

>   A command to remove a partition from the partitioned table and turn it
> into a regular table.
>
>   A command to take a regular table and turn it into a partition. Again
> here you specify the range or value of the partition key. There has to be
> some verification that the table really holds the correct data though.
> Perhaps this could be skipped by providing a table with a properly
> constructed constraint in place.

Like Tom, I don't see the point in these.  What do they do that CREATE TABLE
AS and/or INSERT INTO do not?

--
Josh Berkus
Aglio Database Solutions
San Francisco