Re: WIP: Deferrable unique constraints

On Tue, 2009-07-07 at 19:38 +0100, Dean Rasheed wrote:
> This approach works well if the number of potential conflicts is
> small.

[...]

> Curing the scalability problem by spooling the queue to disk shouldn't
> be too hard to do, but that doesn't address the problem that if a
> significant proportion of rows from the table need to be checked, it
> is far quicker to scan the whole index once than check row by row.

Another approach that might be worth considering is to build a temporary
index and try to merge them at constraint-checking time. That might work
well for unique.

However, there are some potential issues. I didn't think this through
yet, but here is a quick list just to get some thoughts down:

1. It would be tricky to merge while checking constraints if we are
supporting more general constraints like in my proposal
( http://archives.postgresql.org/pgsql-hackers/2009-07/msg00302.php ).

2. Which indexes can be merged efficiently, and how much effort would it
take to make this work?

3. A related issue: making indexes mergeable would be useful for bulk
inserts as well.

4. At the end of the command, the index needs to work, meaning that
queries would have to search two indexes. That may be difficult (but
check the GIN fast insert code, which does something similar).

5. The temporary index still can't be enforcing constraints if they are
deferred, so it won't solve all the issues here.

Regards,
Jeff Davis