From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Re: Adding foreign key constraint holds exclusive lock for too long (on production database)
Date: Wed, 30 Oct 2013 11:20:30 -0700
Message-ID: <52714DEE.9040105@agliodbs.com> (raw)
In-Reply-To: <WM!6f3208f7070805062461b9d530d8553480e9f1ab6d123f013f3da57c917e3a722be3f080fa9ec03ded02ad74b8a648af!@asav-3.01.com>
References: <CAL9jXCFwKXt67+fSGq2N4NDMEadAe=DtKdUGU2gFSS0t-crFxA@mail.gmail.com>
<WM!6f3208f7070805062461b9d530d8553480e9f1ab6d123f013f3da57c917e3a722be3f080fa9ec03ded02ad74b8a648af!@asav-3.01.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: Adding foreign key constraint holds exclusive lock for too long (on production database)
In-Reply-To: <52714DEE.9040105@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox