Re: Partitioning/inherited tables vs FKs

On Thu, May 6, 2010 at 10:38 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> * the index grows as the size of the total data set, it's not limited
> by partition size
>
> * can't cheaply drop one partition any more, you have to vacuum the
> (big) index first

So I wholeheartedly agree with the general sentiment that if you need
global indexes then partitioning just isn't really the right tool for
you.

But it occurs to me that we could defer the vacuum safely. I'm
assuming a index heap-tid pointer for a global index would include a
relid or some other identifier to specify which partition the tuple is
in. If you drop that partition those can all just be left as dangling
pointers as long as we don't reuse that id. So all we would need is
some way to leave a catalog entry reserving that id. The data files
can be truncated and deleted normally and whenever vacuum does run
against the index it can clean up the catalog entries for the deleted
partitions.

But I would rather work on having unique and foreign key constraints
that work on keys which include the partition key than work on global
indexes.
--
greg