Re: On partitioning

Josh Berkus wrote:

Hi,

> Pardon me for jumping into this late. In general, I like Alvaro's
> approach.

Please don't call this "Alvaro's approach" as I'm not involved in this
anymore. Amit Langote has taken ownership of it now. While some
resemblance to what I originally proposed might remain, I haven't kept
track of how this has evolved and this might be a totally different
thing now. Or not.

Anyway I just wanted to comment on a single point:

> 6. Unique Index Problem
> Cannot create a unique index across multiple partitions, which prevents
> the partitioned table from being FK'd.
> Not Addressed
> (but could be addressed in the future)

I think it's unlikely that we will ever create a unique index that spans
all the partitions, actually. Even if there are some wild ideas on how
to implement such a thing, the number of difficult issues that no one
knows how to attack seems too large. I would perhaps be thinking in
allowing foreign keys to be defined on column sets that are prefixed by
partition keys; unique indexes must exist on all partitions on the same
columns including the partition keys. (Perhaps make an extra exception
that if a partition allows a single value for the partition column, that
column need not be part of the unique index.)

> 10. Scaling Problem
> Inheritance partitioning becomes prohibitively slow for the planner at
> somewhere between 100 and 500 partitions depending on various factors.
> No idea?

At least it was my intention to make the system scale to huge number of
partitions, but this requires some forward thinking (such as avoiding
loading the index list of all of them or evern opening all of them at
the planner stage) and I think would be defeated if we want to keep
all the generality of the inheritance-based approach.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services