Enforce NOT VALID in CHECK: A SQL Review Rule Explained

Bytebase SQL Review contains a rule to enforce including "NOT VALID" option when adding "CHECK" constraints:

PostgreSQL lets you add CHECK constraints to enforce data quality rules on a table. That part is useful. The problem is that adding one the obvious way can block reads and writes and cause downtime you didn't plan for. This rule makes sure every new CHECK constraint goes in using the safe, non-blocking approach.

How CHECK Constraints Work#

A CHECK constraint makes sure a column's value satisfies a given condition, for example:

CHECK (amount > 0)

PostgreSQL will reject any insert or update that violates this rule.

To add a CHECK constraint, you typically run:

ALTER TABLE orders
ADD CONSTRAINT orders_positive CHECK (amount > 0);

This enforces the rule on future writes. But it also immediately validates all existing rows, and that is where the risk comes from.

Why Adding CHECK Constraints Can Cause Downtime#

To validate an existing table, PostgreSQL has to scan every row to confirm none of them violate the constraint. While it does that, it holds an ACCESS EXCLUSIVE lock. This is the strongest lock in PostgreSQL, and it blocks:

• Reads
• Writes
• Other schema changes

On a small table you'll never notice. On a large table or a busy production database, that same lock can turn into:

• Query timeouts
• Application errors
• Service degradation
• A full outage

This is exactly the kind of blocking change that slips into a deployment unnoticed, which is what the rule is there to catch.

The Safe Approach: Use NOT VALID#

PostgreSQL gives you a safer way to add a CHECK constraint to an existing table: separate creating the constraint from validating it.

Step 1: Create the constraint without validating existing rows

ALTER TABLE orders
ADD CONSTRAINT orders_positive CHECK (amount > 0) NOT VALID;

• Only a brief catalog lock is required
• The constraint is enforced for all new inserts and updates
• Existing rows are not scanned yet

Step 2: Validate at a convenient time

ALTER TABLE orders
VALIDATE CONSTRAINT orders_positive;

Validation here uses a lighter lock that does not block reads and writes. You can run it during a low-traffic window, or fold it into a controlled rollout.

Example#

Unsafe pattern (not allowed):

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

Safe pattern (recommended):

ALTER TABLE accounts
ADD CONSTRAINT check_balance CHECK (balance >= 0) NOT VALID;

Validate separately:

ALTER TABLE accounts
VALIDATE CONSTRAINT check_balance;

Same end result, the constraint is applied without disrupting user traffic.

Summary#

Add a CHECK constraint without NOT VALID and you risk locking the table long enough to block reads and writes, which on a busy database means downtime. The rule enforces the two-step PostgreSQL practice instead:

• Add the constraint using NOT VALID
• Validate it later using a non-blocking operation

You get the same constraint correctness, without betting production stability on how big the table happens to be.