Postgres Can't Add Foreign Key Constraint

Question

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

My command looks something like this:

ALTER TABLE events 
ADD CONSTRAINT events_visitor_id_fkey 
FOREIGN KEY (visitor_id) 
REFERENCES visitors(id) 
ON DELETE CASCADE;

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).

EDIT:

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)"

• user1822: 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
• Sam: @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: I mean there doesn't appear to be any non granted locks.

Answer 1

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.

• David Spillett: @ypercubeTM - sorry, slip of the brain. I of course meant to write "foreign key" there, as is the actual subject of the question!

Answer 2

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

• user1822: Then it was probably waiting for a lock