From: David Johnston <polobo@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Adding foreign key constraint holds exclusive lock for too long (on production database)
Date: Tue, 29 Oct 2013 17:18:09 -0700 (PDT)
Message-ID: <1383092289576-5776315.post@n5.nabble.com> (raw)
In-Reply-To: <CAL9jXCFwKXt67+fSGq2N4NDMEadAe=DtKdUGU2gFSS0t-crFxA@mail.gmail.com>
References: <CAL9jXCFwKXt67+fSGq2N4NDMEadAe=DtKdUGU2gFSS0t-crFxA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Ben Hoyt wrote
> * http://www.postgresql.org/message-id/> 51A11C97.90209@
> --
> indicates that the db ignores the index when add constraints
As noted in the referenced thread (and never contradicted) the current
algorithm is "for each record does the value in the FK column exist in the
PK table?" not "do all of the values currently found on the FK table exist
in the PK table?". The later question being seemingly much faster (if table
statistics imply a small-ish number of bins and the presence of an index on
the column) to answer during a bulk ALTER TABLE but the former being the
more common question - when simply adding a single row.
You need to figure out some way to avoid continually evaluating the FK
constraint on all 20M row - of which most of them already were previously
confirmed. Most commonly people simply perform an incremental update of a
live table and insert/update/delete only the records that are changing
instead of replacing an entire table with a new one. If you are generally
happy with your current procedure I would probably continue on with your
"live" and "content" schemas but move this table into a "bulk_content"
schema and within that have a "live" table and a "staging" table. You can
drop/replace the staging table from your office database and then write a
routine to incrementally update the live table. The FK references in live
and content would then persistently reference the "live" table and only the
subset of changes introduced would need to be checked.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Adding-foreign-key-constraint-holds-exclusive-lock-for-too-l...
Sent from the PostgreSQL - performance mailing list archive at Nabble.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: polobo@yahoo.com
Subject: Re: Adding foreign key constraint holds exclusive lock for too long (on production database)
In-Reply-To: <1383092289576-5776315.post@n5.nabble.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