Message-ID: <52714DEE.9040105@agliodbs.com>
Date: Wed, 30 Oct 2013 11:20:30 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Adding foreign key constraint holds exclusive lock
 for too long (on production database)
References: 
 <CAL9jXCFwKXt67+fSGq2N4NDMEadAe=DtKdUGU2gFSS0t-crFxA@mail.gmail.com>
 <WM!6f3208f7070805062461b9d530d8553480e9f1ab6d123f013f3da57c917e3a722be3f080fa9ec03ded02ad74b8a648af!@asav-3.01.com>
In-Reply-To: 
 <WM!6f3208f7070805062461b9d530d8553480e9f1ab6d123f013f3da57c917e3a722be3f080fa9ec03ded02ad74b8a648af!@asav-3.01.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance