Do you need NOT VALID when adding new foreign key column

Question

I know that adding a foreign key constraint requires a table scan and a SHARE ROW EXCLUSIVE lock on both tables.

To prevent the possibly lengthy table scan the constraint can be added with the NOT VALID approach. But I'm wondering, when adding a new column, should you also use NOT VALID or is Postgres smart enough to recognize it's a new column and thus the whole table doesn't need to be scanned?

I'm using Django, and the generated SQL of adding a foreign key column looks like this:

ALTER TABLE
    "example"
ADD
    COLUMN "new_column_id" integer NULL CONSTRAINT "example_new_column_id_fk" REFERENCES "another_table"("id") DEFERRABLE INITIALLY DEFERRED;

SET
    CONSTRAINTS "example_new_column_id_b781b6be_fk" IMMEDIATE;

Answer 1

NOT VALID can only be added to ALTER TABLE ... ADD CONSTRAINT, not to ALTER TABLE ... ADD COLUMN directly. A valid SQL script would be (possible in a single transaction or even a single statement):

ALTER TABLE example
  ADD COLUMN new_column_id integer NULL
, ADD CONSTRAINT example_new_column_id_fk FOREIGN KEY (new_column_id) REFERENCES another_table(id) DEFERRABLE INITIALLY DEFERRED NOT VALID;

I tested that in Postgres 16 by locking the target table in ROW EXCLUSIVE mode - the lightest lock conflicting with SHARE ROW EXCLUSIVE that creating a FK constraint takes on the target table - in one transaction:

BEGIN;
LOCK TABLE usr IN SHARE ROW EXCLUSIVE MODE;

-- waiting idle in transaction ...

-- ROLLBACK;  -- later

Then I ran above script in a second, concurrent transaction.

The result: The second transaction had to wait until the first transaction released the ROW EXCLUSIVE lock - with or without NOT VALID. So adding the FK takes a SHARE ROW EXCLUSIVE lock on the target table in any case.

From what Laurenz commented, the following check is either very fast or not actually conducted due to missing values in the referencing column.

So:

• If the bare cost or the duration of the lock is the issue, I'd say, NOT VALID is unnecessary in this particular case.
• If the lock itself is the issue, NOT VALID does not avoid it, unfortunately.

Answer 2

There is an optimization if a foreign key is added in one subcommand when adding a column, then validation of this FK is skipped and considered true.

The FK validation on alter table is visible with debug1 message level:

postgres=# set client_min_messages to debug1;
SET
postgres=# begin; alter table foo_ref add column foo_id int references foo(id); rollback;
BEGIN
ALTER TABLE
ROLLBACK
-- default not null value
postgres=# begin; alter table foo_ref add column foo_id int default 0 references foo(id); rollback;
BEGIN
DEBUG:  validating foreign key constraint "foo_ref_foo_id_fkey"
ALTER TABLE
ROLLBACK
-- ADD CONSTRAINT .. NOT VALID as second subcommand
postgres=# begin;
ALTER TABLE foo_ref
  ADD COLUMN foo_id integer NULL
, ADD CONSTRAINT foo_foo_id_fk FOREIGN KEY (foo_id) REFERENCES foo(id) NOT VALID; 
rollback;
BEGIN
ALTER TABLE
ROLLBACK
-- without NOT VALID there is validation
postgres=# begin;
ALTER TABLE foo_ref
  ADD COLUMN foo_id integer NULL
, ADD CONSTRAINT foo_foo_id_fk FOREIGN KEY (foo_id) REFERENCES foo(id);
rollback;
BEGIN
DEBUG:  validating foreign key constraint "foo_foo_id_fk"
ALTER TABLE
ROLLBACK

Please note that adding FK as a separate subcommand of the same alter table performs validation, optimization only works in ADD COLUMN.

If you add FK with another ADD CONSTRAINT ... NOT VALID subcommand, then you need to call VALIDATE CONSTRAINT afterwards. What is important in the context of table locking - in another transaction. So,

begin;
-- probably some other commands by app migration
ALTER TABLE foo_ref
  ADD COLUMN foo_id integer NULL
, ADD CONSTRAINT foo_foo_id_fk FOREIGN KEY (foo_id) REFERENCES foo(id) NOT VALID;
alter table foo_ref validate CONSTRAINT foo_foo_id_fk;
commit;

is not an error from the DBMS's point of view, but will hold an exclusive lock on foo_ref for the duration of validate process.

begin;
ALTER TABLE foo_ref
  ADD COLUMN foo_id integer NULL
, ADD CONSTRAINT foo_foo_id_fk FOREIGN KEY (foo_id) REFERENCES foo(id) NOT VALID;
commit;
alter table foo_ref validate CONSTRAINT foo_foo_id_fk;

In this case, the table modification transaction (which requires AccessExclusive) will be completed and the locks will be released, then in another transaction the validate constraint will work - it will not conflict with normal select/insert/update/delete.

---

In summary, if you need to add a column default NULL and create an FK on it, do it with one ALTER TABLE ADD COLUMN.

If you need to add only an FK or add a column with a non-NULL default value, you need NOT VALID to avoid a table scan while holding an AccessExclusive lock on this table.