Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1

On Thu, Nov 27, 2008 at 7:04 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Nikhil Sontakke escribió:
>
>> The status has always being WIP, because what has not happened is that we
>> have not had consensus on whether this is a logical first baby step ahead
>> with partitioning. I haven't seen core members commenting on whether trying
>> to aggregate the current set of manual operations together via this approach
>> is worth spending further efforts, to get it into commitable shape.
>
> There was a lenghty, interesting discussion about this topic in the
> developer meeting in Ottawa.
> http://wiki.postgresql.org/wiki/PgCon_2008_Developer_Meeting#Partitioning_Roadmap

Interesting - too bad there aren't some mode detailed notes.

The semantics of PARTITION ON (<expr>) are unclear to me.  I was
thinking maybe it would make sense to do something like:

CREATE PARTITION <name> ON <table> WHERE <expr>

Then you could:

CREATE PARTITION transaction_2008_11 ON transaction WHERE record_date
BETWEEN '2008-11-01' AND '2008-11-30';

I like the idea of using table inheritance as a foundation for this
feature, but I think it's not going to be very useful for real-world
applications without cross-table indexes.  Suppose for example that I
have five years worth of data (thus, 60 partitions) and each
transaction has a unique identifier of some sort that is unrelated to
the date.  It's bad enough that a query like this has to check every
partition:

SELECT * FROM transaction WHERE uuid = ?

What's even worse (at least IMHO) is that there's no way to use
transaction (uuid) as a reference for a foreign key.

...Robert