be commonly determined by SELECTing from other tables or modifying data in target tables. First, let’s start with a FOREIGN KEY constraint creation: begin;alter table t1 add column c123 int8;alter table t1 add constraint fk_c123 foreign key(c123) references t0(id);commit; This code demonstrates a typical failing scenario. If the table t1 has many rows, and table t0 has many rows too, this means we are about to compare every row from t1 with every row from t0 to ensure referential integrity. If our tables contain hundreds of millions of rows, this can really take a long time – locking the table t0 for reads and blocking any updates or even SELECTs. If the engine decides to scan t0 first, every modification (INSERT/DELETE/UPDATE) there will create a new version of a row, potentially leading to bloat issues, increased IO, and so the risks of performance degradation. What to do here? Think twice before you add FOREIGN KEY constraints: sometimes, database-enforced referential integrity is not as important as many developers think. Remember that SELECT FOR UPDATE can be an alternative to heavy FK creation. You should always consider introducing indexes on the columns participating in the
https://postgres.ai/blog/20220525-common-db-schema-change-mistakes