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

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-long-on-production-database-tp5776313p5776315.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.