DEV Community

Juma
Juma

Posted on

PostgreSQL for Go Developers

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()
);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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'));
Enter fullscreen mode Exit fullscreen mode

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
);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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 WHERE clauses, like email, status, or slug.
  • Columns used in ORDER BY on big tables, like created_at for "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';
Enter fullscreen mode Exit fullscreen mode

A composite index covers both columns:

CREATE INDEX idx_orders_user_status ON orders (user_id, status);
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

versus the grim one:

Seq Scan on orders  (cost=0.00..18334.00 rows=12 ...)
  Execution Time: 94.311 ms
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

Option two: use sql.NullString and its relatives, which carry a Valid flag.

type User struct {
    ID       uint
    Nickname sql.NullString
}
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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 ANALYZE to see whether a query uses an index.
  • NULL means unknown, and comparing with it gives unknown. Use IS NULL, and prefer NOT NULL columns wherever "unknown" isn't meaningful.

Top comments (0)