A CSV export came back with blank locale cells for accounts that showed a locale in the UI. The export did SELECT * and wrote each column straight to a row. accounts.locale was NULL for some of them because a backfill job never finished. That was the visible cost. The invisible one was that the column had been nullable for years, so every reader in the system had grown its own opinion about what NULL meant.
A nullable column is a type change for everyone
I added it the cheap way:
ALTER TABLE accounts ADD COLUMN locale text;
No default, no backfill, no long lock. locale was text for about a week. Then a consumer asked what NULL meant, so the Python side became Optional[str]. Then someone needed to distinguish "we never asked" from "no locale applies", so it grew a sentinel and a comment. The Go service passed *string. The TypeScript client typed it string | null | undefined and rendered a fallback in three places. The schema itself did not change again for five years. Everything around it kept changing.
That is the leak. A nullable column is not a local decision about storage. It is a contract that every reader must handle absence, including readers written before the column existed: the generic serializer, the SELECT * export, the audit log that diffs whole rows.
Unknown, not applicable, empty
Under one NULL, locale was doing three jobs:
- Unknown: we never asked the user.
- Not applicable: API-only accounts have no UI locale.
- Empty: someone cleared their preference.
Those are different answers with different correct behaviour. COALESCE(locale, 'en-US') collapses all three into one. WHERE locale = 'en-US' silently drops the first two. Every new consumer re-derived the distinction from a comment in a migration file, or did not, and guessed.
Three-valued logic bites quietly
SQL does not have two-valued booleans. NULL = NULL is UNKNOWN, and WHERE keeps only rows where the predicate is TRUE.
-- accounts whose locale is known and not English
SELECT id FROM accounts WHERE locale NOT IN ('en-US', 'en-GB');
Rows with a NULL locale are absent from the result. Not false, unknown, so the filter drops them. Put a NULL in the list and the whole predicate goes UNKNOWN and the query returns nothing at all. The same applies to <> and !=, and to NOT (locale = 'en-US').
Aggregates behave the same way. count(*) counts every row, count(locale) counts only non-null values, and the two numbers differ by exactly the rows you forgot about. sum and avg skip nulls; sum over only nulls returns NULL, not zero, which becomes a null in the report and another blank cell in the CSV. Unique indexes allow any number of NULLs, so a uniqueness rule you thought you had is not enforced for missing values.
Backfill, then set NOT NULL
The fix is one migration, and it is worth writing. Decide what NULL means, pick an honest value for each case, and make the column non-nullable.
UPDATE accounts SET locale = 'und' WHERE locale IS NULL;
ALTER TABLE accounts ALTER COLUMN locale SET NOT NULL;
und is the BCP 47 tag for an undetermined language, so "we never asked" stays visible instead of being quietly folded into en-US. Run the update in batches, off the hot path. On Postgres, add CHECK (locale IS NOT NULL) as NOT VALID, VALIDATE CONSTRAINT it without blocking writes, then SET NOT NULL can use that constraint and skip a full table scan.
Once the column is NOT NULL, the branches go away: the Optional[str] in Python, the *string in Go, the fallback in the client, and the guard I keep finding in services that did not exist when I wrote the migration.
Model real optionality as absence, not as a null value
Some data is genuinely optional. Keep it out of the required column and put it in a child table, where a missing row is the absence.
CREATE TABLE account_locale (
account_id bigint PRIMARY KEY REFERENCES accounts(id),
locale text NOT NULL,
source text NOT NULL
);
A reader joins and gets zero rows or one row. That is a shape ORMs and planners already understand, and the value itself is never ambiguous. If a child table is more than the case deserves, use an explicit state column that is NOT NULL with a default, such as locale_state text NOT NULL DEFAULT 'unknown', and read the value column only when the state says a value exists. The rule holds either way: absent means absent, and a stored value always means something specific.
I still find if locale is None in code I did not write. Deleting those branches is the migration I should have done first.
I write about production failures in Postgres, queues, and distributed systems.
Top comments (2)
All those null checks are the migration you didn't run, just paid for in a hundred places. I've kept one model per bounded context inside a monolith. The schema still has to be honest or the model inherits the lie. The column gets added nullable, backfilled and set NOT NULL in the same release, or it never gets tightened. What stops the next one, a lint rule against nullable columns in new migrations?
The migration you did not run, paid for in a hundred places — that is a better sentence than anything in the post, and I am going to use it.
On the lint rule: I tried it and it did not survive. A blanket ban on nullable columns also flags the legitimate ones — a genuinely optional relationship, a value that is unknown rather than absent, and a column mid-expand on a table too large to add and tighten in one release — so within a week somebody adds an exception and the rule becomes decoration.
What worked better was narrower and easier to hold:
Adding, backfilling and tightening in one release is the right default, and it is what to do whenever the table is small enough to take the lock. When it is not, the reason is the
ACCESS EXCLUSIVElock rather than the nullability — which is its own post.The bounded-context point is the one I would push further. One model per context keeps the null out of the contexts that never needed it; the lie only spreads through the model that was shared in the first place.