I have a table with about 220 million records :( and I need to add a foreign key constraint.

My command looks something like this:

ADD CONSTRAINT events_visitor_id_fkey 
FOREIGN KEY (visitor_id) 
REFERENCES visitors(id) 

It's been running for probably an hour now.

I ran this before hand:

set maintenance_work_mem='1GB';

What's the fastest way to do this, and about how long should it take. The table it references is only 25 million.

I'm running it on an RDS instance of db.r3.large (15 GB of RAM).


Just cancelled the command and got this:

ERROR:  canceling statement due to user request
CONTEXT:  SQL statement "SELECT fk."visitor_id" FROM ONLY "public"."events" fk LEFT OUTER JOIN ONLY "public"."visitors" pk ON ( pk."id" OPERATOR(pg_catalog.=) fk."visitor_id") WHERE pk."id" IS NULL AND (fk."visitor_id" IS NOT NULL)"
  • 4
    As this doesn't write anything but just reads every row, this shouldn't take hours - maybe it's just waiting for a lock: wiki.postgresql.org/wiki/Lock_Monitoring
    – user1822
    Jan 8, 2016 at 21:51
  • @a_horse_with_no_name there doesn't appear to be any locks. The DB is a dump instance with no other connections and no other queries or processes being run.
    – Sam
    Jan 8, 2016 at 21:59
  • I mean there doesn't appear to be any non granted locks.
    – Sam
    Jan 8, 2016 at 22:08

2 Answers 2


about how long should it take

It depends on the amount of data, your indexes, and the speed of your IO sub-system (the latter particularly if supporting indexes aren't in place).

As you already have 220 million rows in the child table, it must check each of them against the parent table to make sure they are all valid values. Without a useful index on events.visitor this will mean scanning the whole table.

Note that creating a foreign key does not automatically create a supporting index, because depending on your access patterns such an index may not be needed so would be a waste of space.

What's the fastest way to do this,

You can tell the DB not to validate the foreign key constraint for existing rows by adding NOT VALID (see the documentation at https://www.postgresql.org/docs/current/sql-altertable.html for the details and caveats associated with this). That is the fastest way but can lead to you having rows that are invalid that you do not know about until they cause a problem.
(NOT VALID is postgres specific syntax, other DBs that offer the option may name it differently, for instance with MS SQL Server the equivalent is WITH NOCHECK)

The second fastest method is to make sure there is an index on that column before defining the foreign key, though creating that index will itself take a notable amount of time on a table of that many rows.

I tried again and it ended up completing in around half an hour. Not sure what was going on before.

It may be that at the point of the later attempt much of the table was in memory after being read during the earlier aborted create, so scanning to validate existing rows was faster second time around due to fewer disk reads being needed.

  • @ypercubeᵀᴹ - sorry, slip of the brain. I of course meant to write "foreign key" there, as is the actual subject of the question! Oct 9, 2019 at 17:47

I tried again and it ended up completing in around half an hour. Not sure what was going on before.

  • 3
    Then it was probably waiting for a lock
    – user1822
    Oct 9, 2019 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.