Enforcing Validity With Check Constraints

Problem Statement

The repair run has finished and the table is clean. Six weeks later the audit finds four hundred new invalid parcels, because the nightly shapefile import never changed. This page closes the loop: adding a validity constraint to a table that is serving live traffic, without the multi-minute exclusive lock that makes people avoid constraints entirely.

Why the Naive Approach Fails

ALTER TABLE parcels ADD CONSTRAINT parcels_geom_valid CHECK (ST_IsValid(geom)); is one statement and it does exactly what you asked. It also takes an ACCESS EXCLUSIVE lock, holds it while it evaluates the full GEOS validity algorithm on all forty-one million rows, and blocks every reader and writer for the ninety minutes that takes.

One statement or two, and what each one blocks The single ADD CONSTRAINT takes an exclusive lock for ninety minutes while it scans the table. The two-step version takes an exclusive lock for milliseconds to add the constraint NOT VALID, then a weaker share update exclusive lock during VALIDATE, which readers and writers can work alongside. The lock, not the work, is what you are managing ADD CONSTRAINT … CHECK (…) ACCESS EXCLUSIVE for 90 minutes — the table is down every SELECT queues behind it, including health checks ADD … NOT VALID, then VALIDATE CONSTRAINT ACCESS EXCLUSIVE, milliseconds SHARE UPDATE EXCLUSIVE — readers and writers continue new writes are checked from the first millisecond; history is checked at leisure Same end state, same total CPU, and one of them is deployable on a Tuesday.

There is also an ordering trap. If a single invalid row remains anywhere in the table, VALIDATE CONSTRAINT fails at whatever point it encounters it — after having scanned everything before it. Repair and quarantine must genuinely be complete first, which is why the audit table and the quarantine table exist.

Production-Ready Implementation

The full sequence, with the lock guard that stops a blocked ALTER from queueing the whole table behind it:

sql
-- Step 1: prove there is nothing left to violate the constraint.
SELECT count(*) AS violations
FROM parcels
WHERE geom IS NOT NULL AND NOT ST_IsValid(geom);
-- must be 0 before continuing

-- Step 2: add the constraint NOT VALID, with a bounded lock wait.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE parcels
    ADD CONSTRAINT parcels_geom_valid
    CHECK (geom IS NULL OR ST_IsValid(geom)) NOT VALID;
COMMIT;

-- Step 3: validate history, under a lock that lets traffic through.
SET lock_timeout = '3s';
ALTER TABLE parcels VALIDATE CONSTRAINT parcels_geom_valid;

If step 2 times out, nothing has changed and the statement can simply be retried — that is the entire point of the timeout. A retry loop belongs in the migration runner:

python
import time
import psycopg
from psycopg import errors

DDL = """
    ALTER TABLE parcels
        ADD CONSTRAINT parcels_geom_valid
        CHECK (geom IS NULL OR ST_IsValid(geom)) NOT VALID
"""

def add_constraint(dsn: str, attempts: int = 20) -> None:
    """Add the constraint, retrying while other transactions hold the table."""
    for attempt in range(1, attempts + 1):
        try:
            with psycopg.connect(dsn) as conn, conn.cursor() as cur:
                cur.execute("SET lock_timeout = '3s'")
                cur.execute(DDL)
                conn.commit()
            return
        except errors.LockNotAvailable:
            wait = min(2 ** attempt, 60)
            print(f"lock busy, retrying in {wait}s (attempt {attempt})")
            time.sleep(wait)
    raise RuntimeError("could not acquire the lock after 20 attempts")

Note the geom IS NULL OR clause. Without it the constraint also enforces NOT NULL, which is a separate decision and one that will surprise whoever adds a row that legitimately has no geometry yet — for example during the add-column phase of a migration.

The three constraints worth having together

Validity is one of three properties worth pinning down, and they compose:

Three guarantees, three different mechanisms Geometry type and SRID are enforced by the column's type modifier and cost nothing at write time. Validity is enforced by a check constraint and costs a GEOS call per write. Together they mean any row in the table can be trusted by every downstream query without defensive checks. What a trustworthy geometry column guarantees type geometry(Polygon, …) a LineString cannot be stored in this column free — type modifier SRID geometry(…, 4326) a Web Mercator geometry is rejected at insert free — type modifier validity CHECK (ST_IsValid(geom)) a bowtie is rejected at insert costs a GEOS call per write The first two are almost always worth it. The third is worth it everywhere except a bulk-ingest hot path.

What the error message says, and what to do about it

A constraint violation produces a message naming the constraint and nothing about the geometry:

ERROR:  new row for relation "parcels" violates check constraint "parcels_geom_valid"
DETAIL:  Failing row contains (91824, 4, 0103000020E6100000...).

For an operator that is enough. For an API returning a message to a user who just drew a shape on a map, it is useless. The fix is to check in the application before the insert, using the same predicate, and to turn the reason into something actionable:

python
from sqlalchemy import func, select

def validate_geometry(session, wkt: str, srid: int = 4326) -> None:
    """Raise with a human-readable reason before the constraint would fire."""
    geom = func.ST_GeomFromText(wkt, srid)
    valid, reason = session.execute(
        select(func.ST_IsValid(geom), func.ST_IsValidReason(geom))
    ).one()
    if not valid:
        raise ValueError(f"the shape you drew is not usable: {reason}")

This is deliberate duplication. The application check produces the good error message; the constraint guarantees the invariant regardless of which client wrote the row — a migration script, a psql session, a colleague’s notebook. Removing either one is a mistake: the application check alone is a convention, and the constraint alone is a bad user experience.

Configuration and Tuning Knobs

lock_timeout should be short — three to five seconds. It is not there to give the ALTER time to succeed; it is there to make failure cheap so the retry loop can back off and try again when the table is quieter.

For bulk-ingest tables where the per-row GEOS call is genuinely too expensive, keep the constraint NOT VALID permanently. It then documents the invariant and is enforced on ordinary writes, while a loader that uses COPY bypasses it — COPY does check constraints, so in that case validate in the loader instead and drop the constraint to a comment. Measure before assuming: on typical parcel polygons the check costs around forty microseconds, which is invisible next to the WAL write.

If the table is partitioned, add the constraint on each partition rather than on the parent. A constraint on the parent is inherited and validated per partition anyway, but adding it partition by partition lets you spread the validation scan across several maintenance windows.

What the constraint costs per insert Per-insert overhead of ST_IsValid by vertex count: 12 microseconds on a point, 38 on a typical parcel, 340 on a 2,000-vertex polygon and 4.1 milliseconds on a 20,000-vertex coastline. Against a WAL write of roughly 80 microseconds, only the last two are material. Constraint cost per insert, by geometry size point 12 µs — invisible parcel, 40 pts 38 µs — below the WAL write 2,000 pts 340 µs — measurable on a bulk load 20,000 pts 4.1 ms On coastline-scale geometry, validate in the loader and keep the constraint NOT VALID as documentation.

Verification Steps

sql
-- the constraint exists and history has been checked
SELECT conname, convalidated
FROM pg_constraint
WHERE conrelid = 'parcels'::regclass
  AND contype = 'c';
--        conname        | convalidated
-- ----------------------+--------------
--  parcels_geom_valid   | t

-- it actually rejects bad geometry
INSERT INTO parcels (geom)
VALUES (ST_GeomFromText(
    'POLYGON((0 0, 1 1, 1 0, 0 1, 0 0))', 4326));
-- ERROR: new row violates check constraint "parcels_geom_valid"

Running that insert deliberately, in a transaction you roll back, is worth doing once. A constraint that exists but does not fire — because the expression was written against the wrong column, say — looks identical in the catalogue to one that works.

Gotchas Checklist

  • Validation fails on the first bad row, after scanning everything before it. Always run the count query first; discovering a straggler forty minutes into validation wastes the whole scan.
  • convalidated is the column that matters, not conname. A constraint added NOT VALID and never validated looks present in most tooling but guarantees nothing about existing rows.
  • COPY does enforce check constraints. The common belief that it bypasses them is wrong — it bypasses rules and some trigger types, not constraints. If a bulk load slows down after adding this constraint, that is why.
  • Partition parents and children each need attention. Adding the constraint only to the parent still validates each child, so the lock behaviour is per-partition regardless; be explicit about it rather than surprised by it.
  • An ST_IsValid constraint does not imply simple geometry. A valid polygon can still be a MULTIPOLYGON EMPTY, and a valid linestring can still self-intersect — validity is a polygonal concept. Add NOT ST_IsEmpty(geom) separately if emptiness matters.