Re: Adding foreign key constraint holds exclusive lock for too long (on production database)

Ben,

> A couple of questions about the ADD CONSTRAINT. The foreign key column
> on the local table is indexed, and there are only ~50 unique values,
> so the db *could* come up with the unique values pretty quickly and
> then check them.

This would indeed be a nice optimization, especially now that we have
index-only scans; you could do a VACUUM FREEZE on the tables and then
add the constraint.

> b) Delete old rows from the table so it's not so big. Feels a bit
> hacky just to fix this issue.
>
> c) Get rid of this foreign key constraint entirely and just check it
> in code when we insert. Pragmatic solution, but not ideal.

d) add a trigger instead of an actual FK. Slower to execute on
subsequent updates/inserts, but doesn't need to be checked.

e) do something (slony, scripts, whatever) so that you're incrementally
updating this table instead of recreating it from scratch each time.

> * We're running "PostgreSQL 9.0.2, compiled by Visual C++ build 1500,
> 64-bit" on 64-bit Windows Server 2008 SP1 (6.0.6001)

I will point out that you are missing a whole ton of bug fixes,
including two critical security patches.

> * The "ratesrequests" table has two text columns, one of which often
> contains a few hundred to a couple of KB of data in the field. It is
> added to rapidly. We regularly VACCUM ANALYZE it.
> * As expected, the ADD CONSTRAINT has gotten slower over time as this
> table grew. However -- I'm not 100% sure of this, but it seems to have
> jumped recently (from 3-4 minutes to 7 minutes).
> * http://www.postgresql.org/message-id/20030323112241.W14634-100000@megazone23.bigpanda.com

Probably the table just got larger than RAM.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com