5 Postgres Schema Mistakes That Quietly Kill Performance

2026-08-08

5 Postgres Schema Mistakes That Cost You Nothing on Day One

Most Postgres slowdowns aren't caused by a bad query that showed up yesterday. They're caused by a schema decision made eighteen months ago, in a migration that took four seconds to run. If your database used to be fine and now burns three times the I/O for the same workload, the answer usually isn't in pg_stat_statements — it's in the table definition.

Below are the five schema mistakes I keep finding when someone hands me a slow database. They pass code review because they look reasonable. They pass tests because tests run against a hundred rows. They cost real money at fifty million rows, and by then the fix involves a lock, a maintenance window, and an argument with the team that owns the service.

1. Unbounded VARCHAR and the TOAST threshold nobody thinks about

VARCHAR without a length is legal — it accepts up to 1 GB per value. Most people write it because they don't want to guess a limit and get bitten later, which is a defensible instinct. The problem shows up when someone actually stores something large in it.

Postgres wants tuples to fit comfortably within an 8 kB page. When a row grows past roughly 2 kB (TOAST_TUPLE_THRESHOLD, defined as 2048 bytes in the source), the TOAST machinery kicks in — TOAST being, semi-officially, "The Oversized-Attribute Storage Technique." It compresses variable-length attributes, and if that isn't enough, it moves them out of line into the table's TOAST relation, leaving an 18-byte pointer in the main heap tuple. That adds disk I/O and CPU overhead on every access to postgres toast storage.

Here's the part that catches people: TOAST operates on the whole row, not on individual columns. One oversized description field drags the whole tuple over the threshold, and Postgres starts shuffling the largest attributes out until the row fits. Your SELECT id, status FROM orders WHERE ... doesn't touch the description at all, but the heap page it lives on now holds fewer rows, and the toast table is a second relation with its own index that has to be maintained, vacuumed, and cached.

Concretely: a table with 200-byte rows fits about 40 rows per 8 kB page. If a subset of rows carries a 30 kB blob in an unbounded VARCHAR, those rows now cost a heap tuple plus a chain of 2 kB TOAST chunks plus TOAST index entries. A sequential scan that used to read 250 MB now reads that plus whatever the toast table has grown to, and your buffer cache hit ratio drops for everything else on the box.

You don't need to change the column type to fix this. On a large table, retrofitting a length is cheap:

ALTER TABLE public.products
  ADD CONSTRAINT products_description_len
  CHECK (length(description) <= 4000) NOT VALID;

-- validate later, takes a ShareUpdateExclusiveLock, not an ACCESS EXCLUSIVE one
ALTER TABLE public.products VALIDATE CONSTRAINT products_description_len;

NOT VALID means new and updated rows are checked immediately; existing rows are left alone. VALIDATE CONSTRAINT scans the table under a weaker lock that doesn't block reads or writes. If validation fails, you've just learned something useful about your data.

Postgres varchar vs text performance: does it actually matter?

Not the way people think. TEXT and VARCHAR(n) have identical storage behavior in Postgres — VARCHAR(n) just adds a length check. There's no performance benefit to VARCHAR(255) over TEXT except that the check stops a 30 kB payload from ever landing. Use TEXT freely for genuinely free-form content where you've made a conscious call that TOAST is acceptable. Use a bound on anything that's supposed to be a name, a slug, an email, a status code, or an external ID. Worry less about the type name and more about the size distribution of the data going into it.

2. Nullable columns inside multi-column UNIQUE constraints

Forget performance for a second — this one is a correctness bug, and usually surfaces during an incident.

The rule: for unique constraints and unique indexes, two NULL values are never considered equal. That's documented SQL semantics, and it means a postgres nullable unique constraint covering a nullable column doesn't actually guarantee uniqueness.

CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL,
  tenant_id  bigint          -- nullable, because "some users are global"
);

CREATE UNIQUE INDEX users_email_tenant_uniq ON users (email, tenant_id);

INSERT INTO users (email, tenant_id) VALUES ('ana@example.com', NULL);
INSERT INTO users (email, tenant_id) VALUES ('ana@example.com', NULL);
-- INSERT 0 1
-- INSERT 0 1

Both succeed. You now have two rows your application believes cannot exist, protected by an index whose name says uniq. Login code that does SELECT ... WHERE email = $1 starts throwing "multiple rows returned" errors, or worse, silently picks one — and a report somewhere quietly double-counts a customer until someone notices.

Two fixes, and they're not interchangeable

If a NULL tenant_id isn't a valid business state, make it explicit:

ALTER TABLE users ALTER COLUMN tenant_id SET NOT NULL;

If NULL genuinely means something (a global user with no tenant), the unique index should only cover rows where the constraint is meaningful, plus a second index for the global case:

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

CREATE UNIQUE INDEX users_email_global_uniq
  ON users (email)
  WHERE tenant_id IS NULL;

Duplicates are now impossible in both branches, and this postgres partial unique index pair is smaller than the original full index as a bonus.

Postgres 15+ supports UNIQUE NULLS NOT DISTINCT, which treats NULLs as equal and is fewer lines. I still prefer the two partial indexes on anything that predates 15 or runs on multiple versions, because the intent is visible in the DDL rather than in a modifier people forget exists.

3. Nullable columns that can never legitimately be NULL

Every table I audit has a created_at timestamptz with no NOT NULL. Every one. The application sets it on every insert. There has never been a NULL in it. It's nullable because the migration generator defaulted to nullable.

The storage argument is the weakest reason to care. If any attribute in a tuple is NULL, the header carries a null bitmap of one bit per column, rounded up to a byte and then to the alignment boundary — a handful of bytes, only on rows that actually contain a NULL. If someone tells you NOT NULL is a storage optimization, they're technically correct and practically irrelevant.

The real postgres not null performance win is in the planner

NOT NULL is a proof the planner gets to use. It lets Postgres discard IS NOT NULL quals entirely instead of evaluating them per row. It enables anti-join transformations for NOT IN subqueries, which are otherwise unsafe because a single NULL on the inner side changes the semantics of the entire predicate:

SELECT * FROM orders o
WHERE o.customer_id NOT IN (SELECT c.id FROM customers c);

If customers.id is nullable, Postgres can't turn that into a hash anti-join — if any c.id is NULL, the correct answer is zero rows. You get a plan with a filter over a materialized subplan that degrades non-linearly as customers grows. Mark the column NOT NULL and the same query becomes an anti-join. Same SQL, same data, order-of-magnitude difference.

Why nobody fixes it — and the safe way to

A naive ALTER TABLE ... ALTER COLUMN ... SET NOT NULL takes an ACCESS EXCLUSIVE lock and scans the whole table to verify. On a 400 million row table that's minutes of downtime for that relation, and if the lock queues behind a long-running transaction it blocks every subsequent query too.

The safe pattern, available since Postgres 12:

-- 1. Add the check as NOT VALID. Instant, brief lock only.
ALTER TABLE orders
  ADD CONSTRAINT orders_created_at_not_null
  CHECK (created_at IS NOT NULL) NOT VALID;

-- 2. Validate under a weaker lock. Scans, but doesn't block reads or writes.
ALTER TABLE orders VALIDATE CONSTRAINT orders_created_at_not_null;

-- 3. SET NOT NULL is now cheap: the planner uses the validated CHECK
--    as proof and skips the full scan.
ALTER TABLE orders ALTER COLUMN created_at SET NOT NULL;

-- 4. Drop the now-redundant check.
ALTER TABLE orders DROP CONSTRAINT orders_created_at_not_null;

Step 3 still takes an ACCESS EXCLUSIVE lock, but holds it for milliseconds instead of minutes because the scan is skipped. Set a short lock_timeout before running it and retry rather than letting the request pile up behind a slow query.

If step 2 fails, you have NULLs. Backfill first, then re-validate — that's exactly the behavior you want: it forces you to deal with the data before the constraint goes live.

This kind of audit is boring and mechanical, which is why MyDBA's free health check does it for you. It scans your catalog, flags columns that are nullable but contain zero NULLs, flags foreign keys with no supporting index, and hands you the exact ALTER TABLE and CREATE INDEX statements to run — no agent, no writes to your database.

4. TEXT vs JSONB, wrong in both directions

Two failure modes here, and I see roughly equal numbers of each in the jsonb vs text postgres debate.

Direction one: JSON stored as TEXT

Someone stores a serialized payload in a text column because "we just need to keep it." Six months later a feature needs to filter on a key inside it. The query becomes WHERE payload::jsonb ->> 'status' = 'failed', which parses every string in the table on every execution and can't use an index unless you build an expression index on that exact cast. There's no validation either, so a malformed payload from one bad deploy sits in the table until the cast throws at query time. JSONB supports GIN indexes for efficient path queries; text can't leverage them at all.

Direction two: everything shoved into JSONB

JSONB isn't free. It parses and normalizes at write time, converting to a decomposed binary form — that costs CPU on insert. It also discards key order and removes duplicate keys, keeping the last value. If you're archiving webhook payloads and someone later needs to verify a signature computed over the original byte sequence, JSONB has already destroyed your ability to do that. The bytes you get back are not the bytes you received.

The decision rule

You filter, index, or aggregate on keys inside the document: JSONB.
You store it, retrieve it whole, and never look inside it in SQL: text (or json, for parse-time validation and byte-faithful storage).

When you go JSONB, index it properly:

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

jsonb_path_ops produces a smaller index than the default jsonb_ops because it hashes full paths rather than indexing every key and value separately. The tradeoff: it only supports containment operators (@>, and @@/@? for jsonpath), not key-existence checks like ?, ?|, or ?&. If your access pattern is WHERE payload @> '{"status":"failed"}', which it usually is, jsonb_path_ops is the right call and often half the size.

For a single hot key, skip GIN entirely and use a plain B-tree expression index:

CREATE INDEX events_status_btree
  ON events ((payload ->> 'status'));

Cheaper to maintain, cheaper to plan, and it gives you real statistics on that expression.

5. UUIDv4 primary keys

Random UUIDs as primary keys are the most expensive item on this list, and the one people defend hardest.

Postgres B-tree indexes are optimized for an append-mostly pattern. With a bigint identity column, every insert lands on the rightmost leaf page. That page is hot in shared buffers, the split logic has a fast path for rightmost splits that fills pages close to full, and the working set for insertion stays a handful of pages regardless of table size.

A uuid v7 primary key postgres rows on wouldn't have this problem, but UUIDv4 destroys the pattern entirely. Every generated value is uniformly random across the keyspace, so every insert targets a different leaf page. At 500 million rows the index no longer fits in memory, so each insert becomes a random read to fetch the target leaf, a modification, and a dirty page to write out. Page splits happen mid-index rather than at the right edge, leaving both halves roughly 50% full instead of packing them. The index ends up substantially larger than the same data in insertion order, with worse cache locality on every subsequent lookup.

Your monitoring shows this as rising write I/O and falling buffer hit ratio with no change in query volume. That's the signature.

Why UUIDv7 fixes it

UUIDv7, defined in RFC 9562, puts a 48-bit Unix millisecond timestamp in the leading bits. Values generated close in time sort close together, so inserts append to the right-hand edge like a sequence, while keeping the 128-bit unguessable-enough tail and client-side generation that made UUIDs attractive in the first place.

Postgres 18 ships uuidv7() natively. Before that, use the pg_uuidv7 extension, generate them in your application, or use a SQL function:

CREATE OR REPLACE FUNCTION uuid_generate_v7() RETURNS uuid AS $
  SELECT encode(
    set_bit(
      set_bit(
        overlay(
          uuid_send(gen_random_uuid())
          PLACING substring(
            int8send(floor(extract(epoch FROM clock_timestamp()) * 1000)::bigint)
            FROM 3
          )
          FROM 1 FOR 6
        ),
        52, 1
      ),
      53, 1
    ),
    'hex')::uuid;
$ LANGUAGE sql VOLATILE;

ALTER TABLE events ALTER COLUMN id SET DEFAULT uuid_generate_v7();

That overlays the millisecond timestamp into the first six bytes of a random UUID and sets the version nibble to 7. Verify the output against uuidv7() if you're on 18, or against your language's implementation, before you commit to it.

Random UUIDv4 is still fine in specific places: low insert rate tables where the index stays in cache regardless, external-facing identifiers where you actively don't want the creation timestamp leaking (UUIDv7 exposes it to millisecond precision), or distributed generation where clock skew across nodes would produce misleading ordering anyway. What's rarely fine is v4 as the primary key on your highest-volume table because the ORM scaffolded it that way.

The checklist you can run right now

Five queries. Run them against a replica if you want to be careful, though all of them are catalog reads.

1. Unbounded VARCHAR and TEXT columns with no length check

SELECT c.table_schema, c.table_name, c.column_name, c.data_type
FROM information_schema.columns c
JOIN pg_class cl ON cl.relname = c.table_name
JOIN pg_namespace n ON n.oid = cl.relnamespace AND n.nspname = c.table_schema
WHERE c.data_type IN ('text', 'character varying')
  AND c.character_maximum_length IS NULL
  AND c.table_schema NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (
    SELECT 1 FROM pg_constraint con
    WHERE con.conrelid = cl.oid
      AND con.contype = 'c'
      AND pg_get_constraintdef(con.oid) LIKE '%length(' || c.column_name || '%'
  )
ORDER BY 1, 2, 3;

2. Unique indexes and constraints containing a nullable column

SELECT n.nspname AS schema, t.relname AS table, i.relname AS index,
       a.attname AS nullable_column
FROM pg_index x
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_class t ON t.oid = x.indrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY (x.indkey)
WHERE x.indisunique
  AND NOT a.attnotnull
  AND x.indpred IS NULL           -- ignore partial indexes, likely already fixed
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2, 3;

3. Nullable columns that contain no NULLs (NOT NULL candidates)

SELECT schemaname, tablename, attname, null_frac, n_distinct
FROM pg_stats s
JOIN pg_class c ON c.relname = s.tablename
JOIN pg_namespace nsp ON nsp.oid = c.relnamespace AND nsp.nspname = s.schemaname
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = s.attname
WHERE s.null_frac = 0
  AND NOT a.attnotnull
  AND s.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY s.schemaname, s.tablename, s.attname;

Run ANALYZE first. null_frac = 0 from stale statistics is a lie. Confirm with a COUNT(*) WHERE col IS NULL before you write the migration.

4. TEXT columns that are probably JSON

SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE data_type IN ('text', 'character varying')
  AND (column_name ~* '(payload|body|data|meta|attributes|config|json)')
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2, 3;

Crude heuristic, but it surfaces the candidates fast. Spot check each one with SELECT col FROM t LIMIT 5.

5. UUID primary keys, ranked by table size

SELECT n.nspname AS schema, c.relname AS table,
       a.attname AS pk_column,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       c.reltuples::bigint AS approx_rows
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY (x.indkey)
WHERE x.indisprimary
  AND a.atttypid = 'uuid'::regtype
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC;

Anything at the top of that list with a high insert rate is where your random I/O is going. Check whether the values are actually v4 by looking at the version nibble: SELECT substring(id::text from 15 for 1) FROM t LIMIT 10; returns 4 for v4 and 7 for v7.

None of these five items will page you tonight. That's precisely the problem — they accumulate quietly, and the cost of fixing them scales with how long you wait. Run the checklist, fix the two or three that apply, and put the queries in a scheduled job so the next migration that reintroduces one of these postgres schema mistakes gets caught in a week rather than in an incident review.