Re: Syntax for partitioning

On Mon, Nov 14, 2011 at 10:33:36AM +0100, Dimitri Fontaine wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > While I agree that explicit partitioning is somewhat of a hack, it's a
> > really useful hack. But for me the most important use of partitioning
> > is "dropping a billion rows efficiently and getting the disk space
> > back". And the biggest problem is always that dropping blocks of a
> > table requires fixing all the indexes.
>
> The problem with partitions that are in fact table is that the index are
> separated and you can't enforce unique globally in the partition set.
>
> Even with that physical map idea (segment based partitioning, but
> allowing a finer control than segments), you could still maintain any
> number of partial indexes, but still use a single primary key e.g.

Ah, well, if you can come up with a way to get the advantages of
partition while still being able to enforce primary keys over
partitions, that would be A Really Cool Idea.

That said, I still don't see how you can enforce a unique index over
multiple segments over something other than the partition key while
still allowing quick dropping of segments. If you can fix that you can
make it work for the current inheritence-style partitioning.

> If you happen to drop a part of the data that fits in one or more
> segments (and with a decent fillfactor you need less than 1GB to get
> there), then you can unlink() whole files at a time. That would be the
> goal here.

I feel uncomfortable with the "happen to". You can add the magic too,
but for scripting purposes I'd feel better if it could be done via DDL
also. That way typos don't end up being 5 day queries all of a sudden.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer