Domain, enum, check, or reference table: how to choose the right guardian of integrity in PostgreSQL

A post published on Planet PostgreSQL, authored on the My DBA Notebook blog, uses the addition of a region column to show why a business rule enforced only in the application is a promise that someone, sooner or later, will break.

A colleague of the person behind the My DBA Notebook blog opened a simple PR: the business needed a region column on an existing table, and he added a bare text column. Clean code, small PR, approval in five minutes. The approval didn't come. In a post published on Planet PostgreSQL, the blog's maintainer explains why, and uses the case to discuss, with unusual depth, the four real ways to enforce data integrity in PostgreSQL.

The colleague's justification was the usual one: "we want to guarantee integrity inside the code." The post is blunt in rejecting that argument, and the reason is technical, not ideological: the application's rule only holds as long as only that application writes to the table. The day another service, a batch load script, or any psql session also writes there, the rule disappears. The data becomes exactly whatever someone typed.

The four options, side by side

The post details four ways to enforce a closed (or semi-closed) set of values on a column, and the real trade-off of each isn't obvious at first glance.

A domain concentrates the rule in a single place, reusable in any table that declares it, something like create domain region_name as text check (value in ('europe', 'apac', 'americas')). The problem is the removal command: a drop domain ... cascade takes down every column that depends on it, data included. On a table with 24 million rows, like the one in the reported case, that's the kind of trigger nobody wants near production.

PostgreSQL's native enum (create type region as enum (...)) gives strong typing and compact storage. The trap is in the lifecycle: you can add a value to the enum, and since PostgreSQL 10 you can even rename an existing value, but removing a value from the type is not an option. For a set that only grows (or that never changes), that's fine. For business regions, which gain and lose entries over time, it's the wrong option.

The reference table, with an integer id as the primary key and a code with unique as a readable label, is the "correct" answer whenever the domain has its own attributes or changes frequently. It also yields faster joins, because comparing integers costs less than comparing text. The cost is the extra machinery: a join, a lookup before inserting, and an index on the referencing column, which PostgreSQL doesn't create on its own, under penalty of every delete or update on the parent table forcing a full scan of the child table to check for orphaned rows.

Why the team chose a check constraint

The team behind the blog chose the text column with a check constraint. The reason isn't performance or elegance: it's the combination of real integrity with the smallest surprise curve for the team and the greatest ease of evolution. A check can be added, swapped, and removed without rewriting the table, without retiring any value, and without requiring every developer to understand a catalog concept rarely used day to day.

We want to enforce data integrity inside the code instead.

colleague, quoted in My DBA Notebook

The post's response to that line sums up the whole argument: the application isn't the final guardian of integrity, the database is.

The safe pattern on a large table

The most useful part of the post, for anyone operating PostgreSQL in production, is the playbook for two distinct situations on a table with 24 million rows: the initial addition of the column, and a future change to the accepted values.

For the first addition, two questions matter. The first is whether adding the column with a default value rewrites the entire table: since PostgreSQL 11, it doesn't, as long as the default is a constant, which is resolved at the catalog level. The second question, more subtle, is about validation: PostgreSQL isn't smart enough to realize that a new column, with a constant default that already satisfies the check, doesn't need a scan.

This optimization has been recorded since 2016 on the pgsql-hackers mailing list and still appears on the project's To-Do list, unimplemented.

we can test the CHECK constraint just once against the value that all new rows will have

Robert Haas, in a discussion on the pgsql-hackers mailing list

The documented, safe path, then, is to add the constraint as not valid in the same transaction that creates the column, which neither rewrites nor scans anything:

alter table my_table
 add column region text not null default 'europe',
 add constraint region_is_known
 check (region in ('europe', 'apac', 'americas'))
 not valid;

Afterward, validation runs separately, with validate constraint region_is_known. That command scans the entire table, but only holds a share update exclusive lock, which blocks other DDL and vacuum, without blocking reads or writes. Since all the old rows already carry the same default value 'europe', the scan finds nothing outside the rule, and the result can be confirmed by querying pg_constraint.convalidated.

The second situation, which the post already anticipates as the developer's next task, is when the business asks for a new region, such as a 'middle-east' one, on a table that already has 24 million real rows. Here the playbook becomes two separate transactions, because a direct alter table add constraint at that scale would require an access exclusive lock, blocking reads and writes while it scans everything.

The first transaction swaps the old constraint for the new one, also as not valid, under a short lock_timeout to give up instead of locking up the database while waiting in a lock queue:

BEGIN;
set local lock_timeout = '2s';
alter table my_table drop constraint region_is_known;
alter table my_table add constraint region_is_known
 check (region in ('europe', 'apac', 'americas', 'middle-east'))
 not valid;
COMMIT;

The second transaction runs validate constraint at a chosen time, under the lighter lock already mentioned. Since the change is purely additive, no old row fails to satisfy the rule, and validation only confirms the obvious. The post is emphatic on one point: skipping that second step leaves a constraint that looks like it protects the table but silently ignores the old rows, and if a future change removes a value instead of adding one, the rows must be migrated first, because validation will fail on the ones still carrying the removed value.

What changes for those operating databases in Brazil

The practical takeaway for anyone dealing with schema in production is clear: none of the four options is universally right, and the choice depends on the volatility of the value set and the size of the table. It's worth noting as a rule of thumb, straight from the post itself: enum serves a set that is truly fixed forever; domain serves when the rule needs to repeat across multiple tables and the team accepts the cascade risk; a reference table serves when the domain carries its own attributes; check serves the common case, of a set that changes over time on a single column.

The post's broader point is about defense in depth: validation in the application catches typos in the interface, but only the constraint in the database protects against every client, every script, and every import that doesn't go through that screen.

No single layer is enough, not even the database's own layer when assumed without checking: the post closes by acknowledging that its own author also wrongly assumed PostgreSQL would optimize validation on its own, and only discovered otherwise by testing. For any team touching the schema of a large table, that's the lesson that matters more than the choice between domain, enum, and check: test the plan, don't trust the assumption, not even your own.