Five Schema Mistakes That Silently Kill Postgres Performance at Scale

Every database starts its life looking pristine. When your tables hold a few thousand rows, almost any schema design works—you can index whatever you want, ignore strict constraints, store random identifiers, and queries still return in single‑digit milliseconds. I can’t count how many times I’ve walked into a prod review, glanced at a table definition, and thought, “This will be fine on day one.” And it always is. Nobody gets paged because a column is VARCHAR instead of varchar(255) when there are only 10 000 rows.

The trouble starts a year later, once those tables cross a few million rows. Minor structural shortcuts turn into CPU spikes, I/O storms, and index bloat that eats disk space faster than you can say pg_repack. These five schema decisions take zero effort to fix early and a tremendous amount of effort to undo later. I’ll show you exactly what each one looks like, why it seems harmless, what it costs at scale, and the copy‑paste SQL to detect and fix it.

This article accompanies my YouTube whiteboard video on the same topic, where I draw out the exact page‑split mechanics of random UUIDs and explain TOAST pointer operations on the board. If you’re more of a visual learner, that video will walk you through the same concepts in a different medium.

TL;DR

Unbounded VARCHAR accepts up to 1 GB per field. One bad input can push a row past the 2 KB TOAST threshold, ballooning reads and bloating the index. Fix: ALTER TABLE … ALTER COLUMN … TYPE varchar(255); plus a CHECK constraint for extra safety.
Nullable columns in a multi‑column UNIQUE constraint let you insert the same email twice if tenant_id is NULL—NULL ≠ NULL. Switch to a partial unique index WHERE (columns) IS NOT NULL or make the column NOT NULL with a default.
Skipping NOT NULL costs a bit of storage per row, but the real pain is the planner losing the ability to drop IS NOT NULL quals and path optimisations. Roll it out safely on big tables with CHECK … NOT VALID → VALIDATE CONSTRAINT → SET NOT NULL.
Storing JSON as TEXT instead of JSONB means every query parses the blob from scratch—no GIN index, no fast path. Conversely, using JSONB for write‑once, never‑query‑into payloads burns CPU on insert and messes with key order.
Random UUIDv4 primary keys scatter B‑tree inserts randomly, causing page splits and index bloat over 50% once you pass a few million rows. UUIDv7 (time‑ordered) packs inserts into the rightmost leaf, keeping the index compact. Postgres 18 has a built‑in uuidv7(); older versions can use the pg_uuidv7 extension.

Mistake 1: Unbounded `VARCHAR` — “It’s just text, right?”

A column defined as VARCHAR without a length limit is functionally identical to TEXT. Both allow values up to 1 GB. That sounds generous, and for a comments field it might be fine. The problem is when someone maps their “first name” column as VARCHAR unconditionally, and a year later a bot sends a 4 KB string as a name. Postgres quietly TOASTs that long value.

Here’s what happens under the hood. Once a field exceeds roughly 2 KB, Postgres cannot fit it directly in the row’s data page. It goes to out‑of‑line TOAST storage—a separate physical table. Every read of that row now requires an extra I/O to fetch the TOAST‑ed chunk. If that column is indexed, the B‑tree key bloats toward the maximum allowed size (~2700 bytes per index entry). Fewer entries fit on a page, the tree gets deeper, and every comparison costs more CPU. I’ve seen a perfectly fine users table turn into a CPU burner because a single ten‑year‑old batch job started writing 8 KB brand‑prefix strings into a VARCHAR column that used to hold 20‑character names.

It looks like this (the guilty definition):

CREATE TABLE users (
    id    bigint PRIMARY KEY,
    email VARCHAR,  -- no length limit
    name  VARCHAR
);

Fix it before the scar tissue forms. On an existing table, adding a length limit with ALTER TABLE … ALTER COLUMN … TYPE varchar(255) rewrites the whole table because Postgres must verify that every row obeys the new length. If you can schedule a maintenance window, that’s the cleanest route:

ALTER TABLE users ALTER COLUMN email TYPE varchar(255);
ALTER TABLE users ALTER COLUMN name  TYPE varchar(255);

On a live, high‑traffic table you don’t want to freeze, do a two‑step dance: add a CHECK constraint that forbids oversized values, validate it (which only needs an ACCESS SHARE lock and can run online), and then later do the ALTER COLUMN … TYPE when you have a window. The CHECK stops new bad data immediately:

ALTER TABLE users
  ADD CONSTRAINT users_name_len CHECK (char_length(name) &lt;= 255) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_name_len;

Now the column still shows as VARCHAR in the catalogue, but no more 4 KB names can creep in. Combine that with a plain varchar(255) on all new tables, and you’ll never fight this fire again.

Mistake 2: Nullable columns inside a multi‑column `UNIQUE` constraint

This one hides in plain sight. You want to make sure (email, tenant_id) is unique per tenant, but you also allow “global” users where tenant_id is NULL. Sounds logical—until you discover that in SQL NULL ≠ NULL. A unique constraint with a nullable column effectively ignores the uniqueness rule for rows that contain NULL.

CREATE TABLE org_users (
    email    text,
    tenant_id integer,
    UNIQUE (email, tenant_id)
);

Now run this:

INSERT INTO org_users (email, tenant_id) VALUES ('alice@example.com', NULL);
INSERT INTO org_users (email, tenant_id) VALUES ('alice@example.com', NULL);

Both rows exist. No error. The reason: the unique index stores NULL entries as distinct, so the B‑tree considers them different keys. The index also grows with every NULL‑keyed row—bloat you don’t need.

Two clean fixes, pick based on your business logic.

NOT NULL + a default value – If there’s truly no global case, a missing tenant_id is a bug. Make the column NOT NULL and provide a default that fits your domain, e.g. DEFAULT 0 for the internal tenant. Then the simple UNIQUE constraint works as intended.

   ALTER TABLE org_users ALTER COLUMN tenant_id SET NOT NULL;
   ALTER TABLE org_users ALTER COLUMN tenant_id SET DEFAULT 0;

Partial unique index – When you genuinely need to allow NULL (say, “system‑wide” records), create a partial unique index that only enforces uniqueness when the nullable column is not NULL:

   CREATE UNIQUE INDEX idx_org_users_email_tenant
       ON org_users (email, tenant_id) WHERE tenant_id IS NOT NULL;

This allows multiple global rows (tenant_id IS NULL) but still guarantees that each tenant‑specific email is unique. If you also need global emails to be unique, add a second partial index:

   CREATE UNIQUE INDEX idx_org_users_email_global
       ON org_users (email) WHERE tenant_id IS NULL;

Mistake 3: Skipping `NOT NULL` where it belongs

Omitting NOT NULL on columns that should never be null costs you more than a few extra bits of storage. Yes, the null bitmap adds a small overhead per row (one bit per nullable column, padded to the nearest byte), but the real headache is the query planner. When a column is nullable, Postgres cannot assume that column IS NOT NULL filters are always true. It must keep those extra qualifiers, which can block optimisations like index‑only scans, and can even prevent the planner from eliminating outer joins or simplifying comparisons.

For example, a query with WHERE email IS NOT NULL on a column defined as TEXT NOT NULL can drop that condition entirely and still produce the same result. Without the constraint, the planner has to account for the possibility of nulls, adding CPU work and occasionally leading to far worse execution plans.

Applying NOT NULL on a huge table isn’t as scary as it looks if you follow a non‑blocking, stepwise approach:

Add a CHECK constraint that prohibits NULL, but mark it NOT VALID so it only applies to new rows and updates—no table scan needed.

   ALTER TABLE large_table ADD CONSTRAINT nn_col1 CHECK (col1 IS NOT NULL) NOT VALID;

Validate the constraint. This reads all existing rows to be sure none violate the constraint, but it only acquires an ACCESS SHARE lock, so writes can continue. It might take a while on a very large table, but it won’t block.

   ALTER TABLE large_table VALIDATE CONSTRAINT nn_col1;

Now that you’re certain no nulls exist, apply the real NOT NULL setting. This is a catalogue change that takes an ACCESS EXCLUSIVE lock but finishes instantly because it trusts the validated check constraint.

   ALTER TABLE large_table ALTER COLUMN col1 SET NOT NULL;

Finally, drop the temporary check constraint.

   ALTER TABLE large_table DROP CONSTRAINT nn_col1;

For new tables, just write NOT NULL from the start. Your future self will thank you.

Mistake 4: Storing JSON as `TEXT` instead of `JSONB`

The json and jsonb types have been around long enough that most people know jsonb is the “better” one. Yet I still see TEXT columns holding JSON payloads that get filtered with -> or ->> operators. “It’s just a string,” the reasoning goes, “and I dump it in and pull it out whole.” That’s fine until someone writes SELECT * FROM events WHERE payload->>'user_id' = '123'. Postgres has to parse the entire JSON blob on every row scan — no GIN index, no fast path, and the planner treats the predicate as a black‑box TEXT function call that disables most optimisations.

Contrast this with JSONB: the binary representation that parses once on insert, strips whitespace and duplicate keys, and stores keys in a length‑prefixed order. Queries can use the @>, ?, and ?| operators against GIN indexes, making WHERE payload @> '{"user_id": "123"}' an index‑assisted lookup even on millions of rows. For anything you’ll ever query into, JSONB is the right answer.

The opposite mistake: using JSONB for write‑only payloads you never query inside (e.g., raw webhook bodies stored for audit). JSONB burns CPU on insert because it does full parsing and canonical ordering, and if you retrieve the blob and expect the original whitespace or key order, you’ll be disappointed. For that pattern, TEXT (or bytea) is actually the lighter choice, so long as you never index into it.

Spotting these mismatches manually across dozens of tables gets tedious. A quick scan with a tool like MyDBA can flag TEXT columns that appear in JSON‑operator queries and JSONB columns that are never indexed — exactly the kind of hands‑off review that keeps schema design consistent as your team grows.

Audit Early, Not When the Database Is on Fire

Schema mistakes are multipliers: they stay invisible through early traffic, then amplify every query once data volume tips over. The five patterns above — unbounded text, nullable unique keys, missing not‑null, JSON stored as raw text, and random UUID keys — all share that same “works on day one, ruins year two” trajectory. Catching them early costs an afternoon of ALTER TABLE; fixing them later costs a weekend of locking, vacuuming, and tense Slack pings.

What you need is a routine schema health check that looks for these anti‑patterns automatically. After every sprint or before every major deploy, run a checklist that includes:

Columns defined as VARCHAR without a length limit that aren’t content or metadata fields.
Multi‑column unique indexes with nullable columns that lack a partial WHERE clause.
High‑traffic tables where NOT NULL isn’t applied to columns that are always populated.
TEXT columns referenced in query plans with -> or ->> operators.
Tables with a UUIDv4 primary key and more than a few million rows.

That’s exactly the kind of automated review we bake into MyDBA, so your team doesn’t have to grep through \d+ output at 2 a.m. Even if you prefer hand‑rolled queries, the important thing is making the audit a repeatable, low‑friction part of your workflow.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=five-schema-mistakes-that-silently-kill-postgres-performance-at-scale

Try it on your own database: Run a free MyDBA health check to catch schema mistakes before they become scale‑out emergencies.