Indexes, Constraints, and Why NULL Is Weird
You can build a surprising amount of software without ever looking at your database directly. GORM does the talking, Postgres does the remembering, and everyone gets along. Then one Tuesday your app slows to a crawl, or two users end up with the same username, or a report shows 40 customers when you clearly have 41, and you discover that the quiet roommate in your architecture has opinions.
This article is a guided tour of the three parts of PostgreSQL that matter most to a Go backend developer: indexes (how the database finds things fast), constraints (how it refuses nonsense), and NULL (the strangest value in computing, which deserves its own therapy session).
Postgres is not just a place to dump data
Beginners often treat the database as a filing cabinet and the Go code as the brain. So all the checking happens in Go: "is this email unique? is this price positive? does this user exist?" The database just obediently stores whatever it's handed.
That works right up until two requests arrive at the same millisecond. Both check "is this email taken?" Both get "no." Both insert. Congratulations, you now have two accounts with the same email, and a support ticket titled "I can't log in, or maybe I'm someone else."
The lesson: your Go code is polite and well-meaning, but it's one of many clients that can touch the database. Scripts, migrations, admin tools, and future you at 2 a.m. can all write data without going through your validation. The database is the last line of defense, so let it defend. Constraints are how.
Constraints: the rules the database enforces on its own
Think of constraints as house rules written on the wall. Nobody can break them, no matter how they enter the building.
NOT NULL
The simplest rule: this column must have a value.
CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, email TEXT NOT NULL, password_hash TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); If any code tries to insert a user without an email, Postgres refuses. Use NOT NULL by default and allow nulls only when "unknown" is a real, meaningful state. We'll come back to why.
UNIQUE
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email); Now the duplicate-email race is solved properly. When two simultaneous requests both try to insert the same email, one succeeds and the other gets an error. You catch that error in Go and return a friendly "email already registered." The check happens where it can't be raced.
A good pattern is to let the database be the judge, then translate its verdict:
var pgErr *pgconn.PgError if errors.As(err, &pgErr) && pgErr.Code == "23505" { // unique_violation return ErrEmailTaken } Yes, you still might do a friendly pre-check in your service for a nicer experience. But the constraint is the real guarantee, and the pre-check is the courtesy.
CHECK
Rules about the values themselves:
ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price_cents > 0); ALTER TABLE orders ADD CONSTRAINT valid_status CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')); That second one is a quiet hero. A typo like 'shiped' in some far corner of your Go code gets rejected at the door instead of becoming a mystery status that breaks your reports six months later.
FOREIGN KEY
A foreign key says "this value must point at a real row somewhere else."
CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id), total BIGINT NOT NULL ); Try to create an order for user 9999 when no such user exists, and Postgres says no. Without this, you'd get orphans: orders pointing at nobody, like letters addressed to a house that was demolished.
You also choose what happens when the parent is deleted:
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE CASCADE deletes the children along with the parent. RESTRICT (the default behavior) blocks the deletion if children exist. SET NULL keeps the children but clears the link. Pick on purpose. CASCADE on the wrong table is how a single delete button erases a company's entire history, which is a memorable way to learn.
Indexes: the back-of-the-book lookup
Imagine a 600-page textbook with no index. You want to find every mention of "photosynthesis." Your only option is to read every page from the start. That's what Postgres does without an index: a sequential scan, reading the whole table row by row.
With ten rows, nobody notices. With ten million, you notice. Your customers notice. Your cloud bill notices.
An index is the book's index: a separate, sorted structure that says "photosynthesis: pages 41, 212, 388." Postgres jumps straight to the answer.
CREATE INDEX idx_orders_user_id ON orders (user_id); Now SELECT * FROM orders WHERE user_id = 42 is fast, because the database doesn't read the whole table. It follows the index.
The catch: indexes aren't free
Every index is a second copy of some of your data, and it has to be kept up to date. That means:
- Writes get slower. Every insert, update, or delete also updates each relevant index.
- Disk usage grows.
- Too many indexes turn your database into a librarian who spends all day re-sorting cards and never helps a patron.
So the rule isn't "index everything." It's "index what you actually search, filter, join, or sort by."
What deserves an index?
Start with these:
- Foreign key columns. Postgres does not automatically index them (primary keys and unique constraints, yes; foreign keys, no). Joins and cascades on unindexed foreign keys get painfully slow. This is the single most common missing index.
- Columns in frequent
WHEREclauses, likeemail,status, orslug. - Columns used in
ORDER BYon big tables, likecreated_atfor "latest first" feeds.
Composite indexes: order matters
Say your query is "show me this user's pending orders":
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending'; A composite index covers both columns:
CREATE INDEX idx_orders_user_status ON orders (user_id, status); The order of columns matters, like sorting a phone book by last name, then first name. You can quickly find all the Okonkwos, and all the Okonkwos named Chidi. But you can't easily find everyone named Chidi, because they're scattered across every last name.
So this index helps queries filtering on user_id alone, or user_id and status, but not on status alone. Put the column you filter on most often, with equality, first.
Partial indexes: index only what matters
If 95% of your orders are completed and you only ever query the active ones, why index the dead weight?
CREATE INDEX idx_orders_active ON orders (user_id) WHERE status IN ('pending', 'paid'); Smaller, faster, cheaper. It's like keeping a small "currently open cases" folder on your desk instead of searching the entire archive.
EXPLAIN: ask the database what it's thinking
How do you know whether your index is being used? Don't guess. Ask.
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42; You'll get a plan with lines like these:
Index Scan using idx_orders_user_id on orders (cost=0.43..8.45 rows=1 ...) Execution Time: 0.062 ms versus the grim one:
Seq Scan on orders (cost=0.00..18334.00 rows=12 ...) Execution Time: 94.311 ms Index Scan means the librarian used the index. Seq Scan means she read every page. You don't need to decode the whole output at first. Just learn to spot those two phrases and the timing. When a query is slow, run EXPLAIN ANALYZE before touching anything else. Optimizing without measuring is like fixing a car by guessing which part feels sad.
A fun wrinkle: on a tiny table, Postgres may deliberately choose a sequential scan even when an index exists, because reading five rows directly is faster than consulting an index. That's not a bug. It's the planner being sensible. Test on realistic amounts of data.
NULL: the value that isn't a value
Now to the part of the tour where people get quiet.
NULL doesn't mean zero. It doesn't mean an empty string. It means unknown. And unknown behaves in ways that feel illogical until you absorb one idea: comparing anything to an unknown gives an unknown.
Ask yourself: "Is the age of the mystery guest equal to 30?" You don't know the age. So the honest answer isn't "yes" or "no." It's "unknown." Postgres agrees.
SELECT NULL = NULL; -- NULL (not true!) SELECT NULL <> 5; -- NULL SELECT 5 > NULL; -- NULL Is one unknown equal to another unknown? Maybe! Who knows! Postgres shrugs and returns NULL, and a WHERE clause only keeps rows where the condition is true. Unknown gets thrown out with the bathwater.
The famous bugs
The disappearing rows. Say your users table has a nullable banned column, and some rows have NULL.
SELECT * FROM users WHERE banned <> true; You expect every user who isn't banned. But rows where banned is NULL evaluate to unknown, and they silently vanish. Your "not banned" list is missing people. The fix is to say what you mean:
SELECT * FROM users WHERE banned IS DISTINCT FROM true; -- or, better: make the column NOT NULL DEFAULT false The wrong equality. WHERE column = NULL never matches anything, ever. You must write WHERE column IS NULL. Burn that into your memory.
The vanishing count. COUNT(column) skips NULLs, while COUNT(*) counts rows. Same table, different numbers, and a very confusing meeting.
The unique that isn't. By default, a UNIQUE constraint treats each NULL as different from every other, since they're all unknowns. So you can insert many rows with a NULL in a unique column. If you wanted "only one NULL allowed," you need a newer option (UNIQUE NULLS NOT DISTINCT in recent Postgres versions) or a partial index.
NULL and Go: an awkward handshake
Go has no native NULL. A string can't be "unknown"; it's just empty. So when a nullable column meets a Go struct, you need a strategy.
Option one: use pointers.
type User struct { ID uint Nickname *string // nil means NULL } Option two: use sql.NullString and its relatives, which carry a Valid flag.
type User struct { ID uint Nickname sql.NullString } Both work. Pointers serialize to JSON more naturally (null appears as null), while sql.Null* types need extra handling for clean JSON.
But the best strategy is often to avoid nullable columns whenever "unknown" isn't truly meaningful. Does a user without a nickname really have an "unknown" nickname? Or just an empty one? If empty is fine, use NOT NULL DEFAULT '' and skip the whole headache. Reserve NULL for when absence carries real meaning, like deleted_at (not deleted yet) or shipped_at (hasn't shipped).
Putting it together in a migration
Here's a table that uses everything we've covered:
CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT, status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')), total BIGINT NOT NULL CHECK (total >= 0), shipped_at TIMESTAMPTZ, -- NULL means "not yet" created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_orders_user_id ON orders (user_id); CREATE INDEX idx_orders_user_status ON orders (user_id, status); Read it like a contract: every order has a real owner, a valid status, a non-negative total, and a creation time. Only shipped_at is allowed to be unknown, and for a good reason. Bad data has a hard time getting in.
Common mistakes
Validating only in Go. Go checks are the courtesy. Database constraints are the guarantee. Use both.
Forgetting to index foreign keys. Joins crawl, cascades crawl, and you blame the network.
Indexing every column "just in case." You've bought slower writes and a fatter disk for nothing.
Using = NULL. It never matches. IS NULL it is.
Nullable columns by default. Each one is a small tax on every future query. Make NOT NULL your starting position.
Optimizing without EXPLAIN. Measure, then change, then measure again.
Quick recap
- The database is the last line of defense. Let constraints (
NOT NULL,UNIQUE,CHECK,FOREIGN KEY) enforce the rules even when Go code is bypassed. - Catch constraint errors in Go and translate them into friendly messages.
- Indexes make reads fast and writes slower. Index foreign keys, frequent filters, and sort columns.
- In composite indexes, column order matters.
- Use
EXPLAIN ANALYZEto see whether a query uses an index. - NULL means unknown, and comparing with it gives unknown. Use
IS NULL, and preferNOT NULLcolumns wherever "unknown" isn't meaningful.
