Is is not a surprise that applications change over time. New tables get added, columns need to be changed or new ones are required, and sometimes you need to add check constraints or foreign keys to already existing tables which contain data. The issue with adding constraints to tables that already contain data is, that PostgreSQL needs to scan the whole table, to validate that existing data does not violate the constraints you are adding. Is that really an issue, and, if yes, how can it be avoided? <\/p>\n
<\/p>\n
As always, let’s start with a very simple test setup:<\/p>\n
\npostgres=# create table t1 as select 5 as a from generate_series(1,100000000);\nSELECT 100000000\npostgres=# d t1\n Table \"public.t1\"\n Column | Type | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a | integer | | | \n<\/pre>\nNothing fancy here: One table containing one column, 100’000’000 rows. What happens to concurrent sessions touching this table if we add a check constraint:<\/p>\n
\n-- session 1\npostgres=# alter table t1 add constraint c1 check ( a > 4 );\n-- session 2, started right after session 1 started to add the check constraint\npostgres=# insert into t1 (a) values(6);\n<\/pre>\nThe second session will block until the first session committed. Why is that? PostgreSQL can not allow the second session to complete until the constraint is fully in place. Otherwise another session could write data that would violate the constraint. The bigger the table, the longer other sessions will be blocked and this can have a huge impact on your workloads. If tables only contain a dozens of rows you probably never notice anything, but for large tables you need to be careful.<\/p>\n
The question now is: Can we avoid the blocking? The good news is, yes you can. Same test as above but with the “NOT VALID” keywords:<\/p>\n
\n-- session 1\npostgres=# alter table t1 drop constraint c1;\nALTER TABLE\npostgres=# alter table t1 add constraint c1 check ( a > 4 ) not valid;\n-- session 2, started right after session 1 started to add the check constraint\npostgres=# insert into t1 (a) values(6);\n<\/pre>\nBoth sessions complete immediately and new data is validated from now on:<\/p>\n
\npostgres=# insert into t1 (a) values(2);\nERROR: new row for relation \"t1\" violates check constraint \"c1\"\nDETAIL: Failing row contains (2).\n<\/pre>\nFor already existing data there might be more work to do: Consider this:<\/p>\n
\npostgres=# alter table t1 drop constraint c1;\nALTER TABLE\npostgres=# insert into t1 (a) values(2);\nINSERT 0 1\npostgres=# alter table t1 add constraint c1 check ( a > 4 ) not valid;\nALTER TABLE\npostgres=# \n<\/pre>\nThe “NOT VALID” clause tells PostgreSQL to not validate any existing data. If you try to validate the data now it will fail:<\/p>\n
\npostgres=# alter table t1 validate constraint c1;\nERROR: check constraint \"c1\" of relation \"t1\" is violated by some row\n<\/pre>\nYou need to do the cleanup manually:<\/p>\n
\npostgres=# delete from t1 where a < 4;\nDELETE 1\n<\/pre>\nNow the validation will succeed and it will not block concurrent sessions:<\/p>\n
\n-- session 1\npostgres=# alter table t1 validate constraint c1;\n-- session 2, started right after session 1 started to add the check constraint\npostgres=# insert into t1 (a) values(6);\n<\/pre>\nFor best performance and no interruption of other sessions the procedure therefore is:<\/p>\n
Is is not a surprise that applications change over time. New tables get added, columns need to be changed or new ones are required, and sometimes you need to add check constraints or foreign keys to already existing tables which contain data. The issue with adding constraints to tables that already contain data is, that […]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-16733","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\n