constraint-missing-not-valid

problem#

By default new constraints require a table scan and block writes to the table while that scan occurs.

solution#

Using NOT VALID with a later VALIDATE CONSTRAINT call prevents the table scan and results in the validation step only requiring a SHARE UPDATE EXCLUSIVE lock.

https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-NOTES

Instead of:

ALTER TABLE "accounts" ADD CONSTRAINT "positive_balance" CHECK ("balance" >= 0);

Use:

ALTER TABLE "accounts" ADD CONSTRAINT "positive_balance" CHECK ("balance" >= 0) NOT VALID;
ALTER TABLE accounts VALIDATE CONSTRAINT positive_balance;

solution for alembic and sqlalchemy#

Instead of:

# migrations/*.py
from alembic import op

def schema_upgrades():
    op.create_check_constraint(
        constraint_name="positive_balance",
        table_name="accounts",
        condition='"balance" >= 0',
    )

def schema_downgrades():
    op.drop_constraint(
        constraint_name="positive_balance",
        table_name="accounts",
    )

Use:

# migrations/*.py
# First migration
from alembic import op
import sqlalchemy as sa

def schema_upgrades():
    op.create_check_constraint(
        constraint_name="positive_balance",
        table_name="accounts",
        condition='"balance" >= 0',
        postgresql_not_valid=True,
    )

def schema_downgrades():
    op.drop_constraint(
        constraint_name="positive_balance",
        table_name="accounts",
    )

# migrations/*.py
# Second migration
from alembic import op
import sqlalchemy as sa

def schema_upgrades():
    op.execute(
        sa.text("ALTER TABLE accounts VALIDATE CONSTRAINT positive_balance"),
    )

how "not valid, validate" works#

When we add this constraint, writes to the accounts table will be blocked while the table is scanned to verify all positive_balance column entries match the check constraint "balance" >= 0.

ALTER TABLE "accounts" ADD CONSTRAINT "positive_balance" CHECK ("balance" >= 0);

By adding the check constraint as NOT VALID, existing columns are not checked against the constraint, only new rows or updates to existing rows. This constraint can be applied without blocking writes.

Afterwards, we can call VALIDATE CONSTRAINT to verify that the existing positive_balance entries meet the constraint. This will require a table scan of accounts, but writes will not be blocked.

Postgres doesn't need to block writes for VALIDATE CONSTRAINT since Postgres will prevent any new rows or modifications from violating the constraint. So Postgres can scan accounts and depend on other transactions enforcing the constraint in concurrent updates.