Philip McClarence

Posted on Jul 19

Five specific schema patterns — unbounded VARCHAR, nullable columns inside multi

#postgres #sql #database #performance

Title: The 5 Postgres Schema Mistakes That Kill Performance at Scale (and How to Fix Them)

Target keyword: postgres schema mistakes performance

I recorded a video walking through these five patterns on a whiteboard—how they look at 10k rows and why they fall apart at 10M. This post is the companion: exact copy‑paste SQL, concrete migration steps, and the trade‑offs you’ll actually weigh in production. If you’ve inherited a Rails or Django schema, you’ve probably seen at least three of these.

TL;DR

Unbounded VARCHAR → set a length: VARCHAR(255). Block new oversized rows with a CHECK constraint (NOT VALID), then clean up later.
Nullable column in a multi‑column UNIQUE → either make the column NOT NULL DEFAULT … or use a partial unique index WHERE nullable_col IS NOT NULL.
Missing NOT NULL → add CHECK (col IS NOT NULL) NOT VALID, validate, then convert to NOT NULL (PG12+ avoids the full table scan if the check is already marked valid).
TEXT for JSON data → ALTER COLUMN … TYPE JSONB USING col::jsonb. Index with GIN (col jsonb_path_ops) when you need to query inside the document.
UUIDv4 primary key → switch to bigint identity or, if you truly need client‑generated UUIDs, use UUIDv7 (app‑side or an extension until PG adds native support).

Why these are “quiet” killers

None of these throws an error. All pass the strictest code review when the table has 10 000 rows. At 10 million rows they turn into CPU‑grinding, I/O‑bloating, cache‑unfriendly monsters—and since the degradation is gradual, teams usually notice only after the pain is acute. The fixes aren’t exotic. Most are one or two ALTER TABLE statements. You can ship them today.

1. Unbounded VARCHAR (no length limit)

Defining a column as VARCHAR (or character varying) without a length specifier looks harmless. The PostgreSQL docs confirm: it “accepts strings of any length.” The trouble starts when a single row stores a 20 KB string. PostgreSQL keeps values inline up to about 2 KB—the TOAST threshold. Anything larger gets compressed and/or broken into chunks stashed in a separate TOAST table. Every read of that column now carries pointer indirection and extra I/O. Index the column? The btree entry has to wrap the TOAST pointer, and that one huge row can fragment the index, wasting buffer cache across millions of reads.

You can see the size spillover yourself:

SELECT pg_column_size(repeat('a', 3000));  -- well over the inline limit, triggers TOAST

The fix: Set an explicit limit unless you truly have a legitimate, unbounded text field. Almost every time I’ve encountered an unbounded VARCHAR in a Rails or Django app, the real maximum was known (email, name, token, URL). Use:

ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);

But this grabs an ACCESS EXCLUSIVE lock and rewrites the whole table. For a live table, put a check constraint in place first without validating old rows:

ALTER TABLE users ADD CONSTRAINT email_length_chk CHECK (length(email) &lt;= 255) NOT VALID;

Then, during a quiet window:

ALTER TABLE users VALIDATE CONSTRAINT email_length_chk;

Once the constraint is valid, the column can be altered without a rewrite in many cases, or you can just leave the constraint in place permanently—it prevents future bloat and gives you the same length guarantee. If you already have oversized data, you’ll need a background job to truncate or relocate those values before setting the limit.

Default rule: use VARCHAR(255) unless you have a real reason not to. It’s short, predictable, and keeps TOAST out of your indexes.

2. Nullable columns inside multi‑column UNIQUE constraints

This one’s sneaky because it looks correct in every code review. A unique constraint on (tenant_id, email) where tenant_id can be NULL. The docs state: “For the purpose of a unique constraint, null values are not considered equal.” So two rows with tenant_id = NULL and the same email slip right through. I’ve seen this bite SaaS apps where a “global” admin user has tenant_id IS NULL and duplicate emails create chaos.

Concrete example:

CREATE TABLE users (
    id bigint GENERATED BY DEFAULT AS IDENTITY,
    tenant_id bigint,
    email text,
    UNIQUE (tenant_id, email)
);

INSERT INTO users (tenant_id, email) VALUES (NULL, 'dupe@example.com');
INSERT INTO users (tenant_id, email) VALUES (NULL, 'dupe@example.com');
-- No error. Two rows, same email, constraint silent.

Two fixes, two trade‑offs:

Make the column NOT NULL DEFAULT … if NULL really means “missing data” that you can replace with a sentinel. After that, the standard unique constraint works as expected.
Use a partial unique index if NULL has business meaning and you want uniqueness only for non‑null values:

CREATE UNIQUE INDEX users_tenant_email_uni_idx ON users (tenant_id, email) WHERE tenant_id IS NOT NULL;

With this index, any number of rows can share the same email when tenant_id is NULL, but no two rows with the same non‑null tenant and email are allowed. If your app logic requires global email uniqueness even for null‑tenant rows, you’ll need a separate UNIQUE (email) constraint—just watch out for interaction with the partial index.

Usually option 1 is cleaner: a well‑chosen default removes ambiguity entirely.

3. Missing NOT NULL where it matters

A column like shipped_at that, once set, should never be NULL again, but nobody added NOT NULL. The main cost isn’t the few bytes of null bitmap; it’s the planner. Without a guarantee that the column is non‑null, PostgreSQL must account for nulls in every query, adding a filter step even when 99.9 % of rows have a value. That extra step can prevent an index‑only scan and force heap fetches, burning I/O.

Run EXPLAIN (ANALYZE, BUFFERS) on something like SELECT * FROM orders WHERE shipped_at IS NOT NULL. Before the constraint, you’ll see a filter node after the index scan, peeking at heap pages. After adding NOT NULL, the planner knows shipped_at IS NOT NULL is always true and drops the filter—the index can be used directly (or even skipped entirely if other conditions are selective).

Safe migration on a large table:

-- 1. Add a NOT VALID CHECK to prevent new NULLs immediately.
ALTER TABLE orders ADD CONSTRAINT shipped_at_chk CHECK (shipped_at IS NOT NULL) NOT VALID;

-- 2. Validate it later. If all existing rows already satisfy the condition, this is fast.
ALTER TABLE orders VALIDATE CONSTRAINT shipped_at_chk;

-- 3. Convert the CHECK into a real NOT NULL. In PG12+, if a valid CHECK (col IS NOT NULL) exists,
--    ALTER TABLE can set NOT NULL without a full table scan.
ALTER TABLE orders ALTER COLUMN shipped_at SET NOT NULL;

Now the schema matches reality, and the planner gets to optimise accordingly.

4. TEXT vs JSONB — and JSONB vs normalized columns

Two anti‑patterns here, both common.

TEXT for JSON: Storing API payloads as TEXT and then writing queries like data->>'email' means PostgreSQL re‑parses the entire JSON document on every access, every filter, every row. The docs are blunt: “The json data type stores an exact copy of the input text, which processing functions must reparse … jsonb data is stored in a decomposed binary format that makes it significantly faster to process.” CPU cycles that add up fast.

Fix:

ALTER TABLE events ALTER COLUMN payload TYPE JSONB USING payload::jsonb;

Then, if you frequently query inside the blob, add a targeted GIN index:

CREATE INDEX events_payload_gin ON events USING GIN (payload jsonb_path_ops);

jsonb_path_ops is smaller and quicker for containment searches (@>) than the default jsonb_ops. It indexes only leaf nodes, not the full hierarchy, which makes the index much more compact—but it does not support the “key exists” (?) operator. If you need both containment and key‑existence queries, use the default jsonb_ops. Don’t go adding GIN indexes on columns you barely query—the index itself bloats under heavy writes.

JSONB as a dumping ground: A metadata JSONB column holding dozens of keys that are always present and heavily filtered. Writes to JSONB are slower than plain columns because of the parsing step, key order isn’t preserved, and GIN indexes grow large. My rule: if a key appears in >90 % of rows and you filter or join on it, extract it to a real column. You can keep a JSONB “catch‑all” for the genuinely variable, sparse attributes. For everything with a stable schema, use normalized columns. The database does a better job with them.

5. UUID primary keys without v7 (or without thinking about it)

UUIDv4 values are random 16‑byte chunks. Insert a few hundred rows per second and each new key lands somewhere in the middle of the B‑tree index, forcing leaf‑page splits. Over months the index becomes bloated (often 30‑50 % larger than necessary), fill factor drops, and writes amplify. A monotonically increasing key—bigint identity or UUIDv7—inserts at the rightmost edge of the index, avoiding splits and keeping the index compact and cache‑friendly.

PostgreSQL’s built‑in gen_random_uuid() produces UUIDv4. As of PG17 there’s no native UUIDv7; you need an extension like pg_uuidv7 or generate them in your application. PG18 may bring native support, but until then default to bigint identity (or bigserial) unless you truly need client‑generated, globally unique IDs (distributed ingestion, offline clients, security against enumeration). If you must have UUIDs, use UUIDv7 from the application layer. Changing a UUIDv4 PK column to bigint is a heavy migration, so catch this early.

How to catch these before they bite you

These patterns are invisible to EXPLAIN on a dev database because the pain only emerges at scale. An automated schema check—the kind that flags missing NOT NULL on columns that are never actually null, unbounded VARCHAR, and missing FK indexes—finds them in seconds. I run MyDBA’s free health check (https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=five-specific-schema-patterns-unbounded-varchar-nullable-columns-insid) quarterly on production schemas; it generates the exact ALTER TABLE and CREATE INDEX statements you need, no guesswork. Just copy, review, and apply. It’s the blunt‑yet‑effective tool that keeps these “quiet killers” from ever waking up.

Wrap‑up

None of these five are theoretical edge cases. They’re mistakes I see in reviews of Rails, Django, and microservice backends every week. All of them pass code review at day one and then degrade silently. The good news: they’re all a few ALTER TABLE statements away from fixed. Do a schema audit quarterly—it’s one of the highest‑leverage things a DBA (or an ops‑minded developer) can do. For a whiteboard walkthrough of exactly how these patterns hit scaling databases, watch the companion video linked below. Then pick the worst one in your own schema and go fix it.

DEV Community