Re: What needs to be done for real Partitioning?

On L, 2005-03-19 at 23:47 -0500, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> > 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.
>
> Well, partitioning on the primary key would be Good Enough for 95% or
> 99% of the real problems out there. I'm not excited about adding a
> large chunk of complexity to cover another few percent.

That automatically means that partitioning expression has to be a range
over PK. (you dont want to have every tuple in separate tabel :)

And it also means that you have to automatically create new partitions.

Are you sure that partitioning on anything else than PK would be
significantly harder ?

I have a case where I do manual partitioning over start_time
(timestamp), but the PK is an id from a sequence. They are almost, but
not exactly in the same order. And I don't think that moving the PK to
be (start_time, id) just because of "partitioning on PK only" would be a
good design in any way.

So please don't design the system to partition on PK only.

--
Hannu Krosing <hannu(at)tm(dot)ee>