Re: Syntax for partitioning

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.

> However, in the very special case where the drop boundaries explicitly
> match the dataset, you can simply drop all the indexes.

That's the idea with partial indexes too, right?

> Now, if someone cames up with an efficient way to drop a huge number of
> rows quickly, then I admit one of the major issues is fixed. But
> recovering the disk space is much harder. Yes, recent versions of
> Linux come with ways to punch holes in existing files, but that doesn't
> make it quick or efficient.

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 hope so, but I'm not sure I'd like partitioning support to wait on
> someone hitting on the right idea.

I would think that's exactly what's been happening to us for several
years already.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support