Yesterday, a colleague of mine opened a simple PR. The business needed a
region on an existing table, so he added a text column to hold it. Clean,
small, ready to review and merge. Database internals? No problem. This was going
to be a five-minute approval.
I did not approve it.
Not because the code was wrong. The code was fine. I challenged the content:
why a free-text column? Why not a domain, an enum, a text column with a check
constraint, or a reference table?
His answer made me very tired: “We want to enforce data integrity inside the code instead.”
No, my friends. A thousand times no.
I’ve seen too many production database disasters born from exactly that kind of
thinking. The application is not the final guardian of your data’s integrity.
Your database is. Because guess what happens to that shiny application rule the
day another application, a script, a psql query, or a batch import shows up?
The rule is gone. Your data is whatever someone typed.
So let’s talk about the real options. It depends, of course. But let’s break down each one, with the pros and cons I actually care about.
The four options 🔗
1. A domain 🔗
A domain is a reusable wrapper around a type, with its own constraints baked in. You define it once and use it on every table.
create domain region_name as text
check (value in ('europe', 'apac', 'americas'));
Pros: consistency across every table that uses it. One place to define the rule. Clean and self-documenting.
Cons: the big one for us is drop domain ... cascade. When you run it,
Postgres drops every object that depends on the domain, including the columns
that use it. That means the column, and the 24 million data inside it, go away
in a single careless command. We don’t want that kind of footgun near our
production table. Plus, domains are still a niche concept to many developers,
and changing the allowed set later is a schema change in every place that uses
the domain, whether you want it or not.
2. An enum 🔗
Postgres enums give you a real typed value.
create type region as enum ('europe', 'apac', 'americas');
Pros: strict typing, nice to read, sortable in declaration order, stored compactly.
Cons: this is the one I’m most wary of. You can append to an enum but you
cannot remove a value. Ever. That value lives in your data forever and the
enum code stays, even if you’d love to retire it. Renaming a value is easier
these days, alter type ... rename value exists since PostgreSQL 10, but
removing one is simply not on the menu. Enums are great when the set is truly
fixed forever. Business regions are usually not fixed forever.
3. A text column with a check constraint 🔗
alter table my_table
add column region text not null default 'europe',
add constraint region_is_known
check (region in ('europe', 'apac', 'americas'));
Pros: the database enforces the rule, it’s simple, every developer
understands check, and, crucially, you can add, drop, and re-constrain
values without rewriting data or retiring anything.
Cons: nothing protecting you from adding the constraint in a way that takes the table offline. You must be careful how you add it on a big table (see below).
4. A reference table 🔗
create table region (
id integer generated always as identity primary key,
code text unique not null
);
alter table my_table
add column region_id integer
references region(id);
Pros: you get a real list, a natural home for extra metadata about each
region, and full relational rigor. This is the “correct” answer for anything
with attributes of its own or a changing set. Note the design: an integer
primary key id, plus a unique constraint on the code text, and the column
stores the id. Joining my_table to region on integers is significantly
faster than joining on text, and the unique on code guarantees no two
regions share the same name. Best of both worlds.
Cons: you need a join, a lookup, an insert before use, and discipline about keeping the reference table clean. Also, the referencing column needs an index: PostgreSQL does not create one for you, and without it, every delete or update on the parent table has to scan the referencing table to check for orphaning rows. For a single flat code, it can feel like a lot of machinery.
So what did we pick? 🔗
We picked the text column with a check constraint. Why? Because it has the
integrity we want, it’s the least surprising for the team, and it’s the most
malleable. The set of regions will grow and change, and check constraints are
easy to evolve.
The safe pattern on a large table 🔗
But, we didn’t just write the
alter table and pray. There are actually two situations to think about on
a table with over 24 million rows, and they need different recipes.
Situation 1: the very first time (our case) 🔗
The table already has data and we know there is currently only one region. So two questions jump out.
First, the rewrite question you must always ask on a big table: does adding that column with a default mean Postgres rewrites all 24 million rows?
Only if your Postgres is old. Since PostgreSQL 11, adding a column with a constant default value does not cause a full table rewrite. It’s optimized away at the catalog level. So we double-checked our version: we’re comfortably past 11. No rewrite.
Second, the validation question. And here I need to come clean about my own expectation: I assumed Postgres would be smart enough to understand that a brand new column with a constant default that satisfies the check means we’re safe, so no scan needed. Guess what? It is not smart enough. That cleverness is still on the PostgreSQL To-Do list, under “Do not scan the table when a check constraint is added in the same command that adds the column”. The idea was raised back in 2016 by Jeff Janes on pgsql-hackers, Robert Haas even confirmed it was feasible (“we can test the CHECK constraint just once against the value that all new rows will have”), and yet it was never implemented. The scan still happens.
So we take the documented route instead: we add the constraint not valid,
which tells Postgres to skip the scan entirely, right now, in the same
alter table as the new column:
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;
That command is as fast as it looks: no rewrite, no scan. Then we run the
validate step to actually check the old rows:
alter table my_table
validate constraint region_is_known;
Let’s be honest about this one too: validate does scan the whole table,
because Postgres has no idea all rows share the same default value. It’s the
very optimization from the To-Do list that would have saved us. We timed it
anyway, like any other heavy operation on 24 million rows. At least it only
takes a SHARE UPDATE EXCLUSIVE lock: that blocks other DDL and vacuum, but
reads and writes keep flowing. The good news: every
row shares the constant default 'europe', which satisfies the check, so the
scan finds nothing to complain about. If you ever want to check whether the
constraint really is validated, ask the catalog:
select convalidated
from pg_constraint
where conname = 'region_is_known';
t and you’re done. No table rewrite. No surprise downtime on the add. The
whole thing was almost as painless as the PR was supposed to be.
Situation 2: when you need to change the values (the dev’s next task) 🔗
Now here’s the thing the developer will hit in a few weeks, when the business
opens a brand new middle-east region. That’s the case where you do have 24
million rows with real values, and the constraint depends on them. This is
where the not valid + validate framework earns its keep. And note the
intention here: the change is purely additive. The new list keeps every old
value and gains one, so every existing row still satisfies the new constraint.
Nothing to migrate, nothing to fear from the validate.
The recipe has two transactions, and the whole point is that the expensive work is kept far away from the definition change. Here’s the shape of it:
-- Transaction 1: atomic. We want the old rule off and the new rule on
-- to happen together, so the table is never left without a constraint
-- on it. New and updated rows must obey the new rule. The lock_timeout
-- makes us bail out instead of queueing behind a long query and
-- blocking everything else while we wait.
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;
-- Transaction 2: separately, at a time we choose. It takes a
-- SHARE UPDATE EXCLUSIVE lock: other DDL and vacuum wait, but
-- reads and writes don't.
alter table my_table
validate constraint region_is_known;
Why two transactions instead of one big alter? A plain add constraint on a
24-million-row table has to scan every row while holding an ACCESS EXCLUSIVE
lock, which blocks reads as well as writes. We don’t want that. So we split the
work: the first transaction swaps the constraints atomically (near-instant, but
under that heavy lock, so the lock_timeout protects us), and the second runs
validate on our own schedule, under the much lighter SHARE UPDATE EXCLUSIVE
lock. We hold the costly lock for as short a time as possible.
Now, what does not valid actually mean? It’s simple: the constraint is
checked for new and updated rows, but not for old, untouched rows that
were already there since before the change. That is exactly why we validate
afterwards: to force Postgres to walk the old rows and confirm they all obey
the new constraint. Until validate runs, the constraint is enforced for new
writes and pending for the old ones. After it runs, it’s fully enforced
everywhere.
A note of caution: don’t add a brand new region to the business without
running the second transaction. Skipping the validate is the classic way to
end up with a constraint that claims to protect your data but silently ignores
the old rows. And if a change ever removes a value instead of adding one,
migrate the rows off it first, or the validate will fail on the rows that
still carry the retired value.
That’s the framework. Situation 1 bought us a clean start; situation 2 is what the dev will reach for every time the region list grows.
The takeaway 🔗
The moral of the story isn’t just “domains vs enums vs check vs reference tables.” It’s this: the database is where your data lives, so the database is where your integrity rules belong. Put them in code and you’ve built a fence only one of your many doors respects.
And there’s a second lesson hiding in here: defense in depth. For data integrity, just like for security, two lines of defense are way better than one. Nothing is ever perfectly enforced at a single layer. The application check catches typos at the UI, and the database constraint catches everything else, every client, every script, every import. When the application check fails, the database is still standing behind it. When the application team thinks “we handle that in code,” the database quietly keeps the promise they forgot.
Yes, it depends on your version, your table size, your set of values, your team. But “we’ll do it in the application” is not a choice. It’s a promise that someone, eventually, will break. With the constraint in the database, that broken promise stops being a disaster.
And a third lesson, this one from writing this very post: verify, don’t assume.
I spent this whole article challenging my colleague’s assumption that the
application could carry data integrity. Then, double-checking my own, I found
I was the one trusting a fairy tale: Postgres does not skip that validation
scan. Jeff Janes assumed it in 2016, it’s still on the To-Do list, and the
search engine has never been smarter than the engine. Our happy ending wasn’t
Postgres being clever. It was the boring, documented path: not valid, then
a well-timed validate. The To-Do list is not a promise. Test, measure, and
read the source before you trust what you think you know.
Now go validate those constraints, my friends.