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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
