Re: Auto Partitioning

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:

> On Wed, 2007-04-04 at 14:20 +0200, Markus Schiltknecht wrote:
>> Both proposals do not have much to do with the missing multi-table
>> indices. It's clear to me that we have to implement those someday,
>> anyway.
>
> I agree with much of your post, though this particular point caught my
> eye. If you'll forgive me for jumping on an isolated point in your post:
>
> Multi-table indexes sound like a good solution until you consider how
> big they would be.

Put another way, multi-table indexes defeat the whole purpose of having
partitioned the table in the first place. If you could have managed a single
massive index then you wouldn't have bothered partitioning.

However there is a use case that can be handled by a kind of compromise index.
Indexes that have leading columns which restrict all subtrees under that point
to a single partition can be handled by a kind of meta-index. So you have one
index which just points you to the right partition and corresponding index.

That lets you enforce unique constraints as long as the partition key is part
of the unique constraint. In practice people are usually pretty comfortable
not having the database enforce such a constraint since it's easy to have the
application enforce these types of constraints anyways.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com