Most Postgres performance disasters start with a schema decision nobody thought

by

Most Postgres performance disasters start with a schema decision nobody thought twice about

If you’ve watched the accompanying video, you already know the premise: Postgres rarely blows up in smoke. It bleeds. The query that’s slow today has a root cause that was laid down six months ago in a CREATE TABLE nobody reviewed. The schema decisions that hurt you don’t crash anything. They just make everything a little worse, every day, until someone pings you about “latency” and you’re staring at an index twice the size of the data.

Most Postgres performance disasters start with a schema decision nobody thought

I fix these for a living. The same five mistakes show up in SaaS startups, enterprise monoliths, and everything in between. They’re never exotic. They’re usually the result of someone picking the first type that came to mind, or copying a MySQL pattern, or not having a migration review checklist. This article isn’t a gentle introduction. It’s a field guide from production, with exact SQL you can run right now—and enough edge cases to keep you out of trouble when the tables are large.

Mistake 1: Unbounded VARCHAR (and the myth of VARCHAR(255))

Most teams define text columns as VARCHAR(255) because that’s what an ORM defaulted to, or because they came from MySQL and expect VARCHAR(255) to behave differently than TEXT. It doesn’t. In PostgreSQL, VARCHAR(n) and TEXT are essentially the same type. The only difference is that VARCHAR(n) has a length-check constraint, which costs a few extra CPU cycles on write or update. Both store data as variable-length “varlena” structures and both can hold up to 1 GB of text. So the real question isn’t “Should I use VARCHAR or TEXT?”—it’s “What happens when someone puts the entire contents of a CSV file into a column?”

Large column values—anything that would push the row past the 8 KB page boundary—get handled by TOAST (The Oversized-Attribute Storage Technique). The threshold for TOAST to kick in is roughly 2 KB (TOAST_TUPLE_THRESHOLD). Once a value crosses that line, PostgreSQL compresses it and/or moves it out of line into a separate TOAST table. Every read of that column now becomes extra I/O. Worse, if you index that column (even accidentally, as part of a composite index), the index copies the entire toasted value. I’ve seen B-tree indexes where a single key entry consumed 300 KB because someone stored raw HTML in a VARCHAR(5000) column that was part of a UNIQUE constraint. The index was larger than the table.

The fix is straightforward: prevent oversized data from reaching the column. You don’t need a type change; a CHECK constraint does the job without a table rewrite.

Find the unbounded VARCHAR columns in your database:

SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
  AND data_type = 'character varying'
  AND character_maximum_length IS NULL;

This returns columns that accept a 1 GB value. Not every one of them is a problem—a notes field that never goes beyond a few sentences is fine—but if you see payload, raw_data, or response_body, you’ve found a candidate.

Safely add a length constraint on a large existing table:

-- add a not-valid check so existing big rows are still allowed, but new ones will be blocked.
ALTER TABLE orders
  ADD CONSTRAINT raw_data_max_len CHECK (octet_length(raw_data) <= 4096) NOT VALID;

-- validate in a non-blocking step (doesn’t take an ACCESS EXCLUSIVE lock).
ALTER TABLE orders VALIDATE CONSTRAINT raw_data_max_len;

Now new inserts or updates that try to store > 4 KB will fail. If you need to enforce the limit on old rows as well, you’ll have to clean them up first, then the VALIDATE CONSTRAINT will succeed. This pattern keeps the table available while you’re hardening the schema. If you want the constraint to be tight and can afford a brief rewrite, you could change the type to VARCHAR(4096) with an ALTER TABLE … ALTER COLUMN … SET DATA TYPE. That’s a full scan and table rewrite, though—use pg_repack or a maintenance window.

Mistake 2: Nullable columns in multi-column UNIQUE constraints

This one turns into duplicate rows that your application then tries to handle with “unique by convention” application code, and it always ends in a cleanup script at 2 a.m.

PostgreSQL treats NULL as not equal to any other NULL. In a UNIQUE constraint, that means (email, tenant_id) with a nullable tenant_id will happily accept multiple rows with the same email and NULL tenant. It’s correct per the SQL standard, but it’s a surprise for anyone coming from MySQL or an ORM that lies about uniqueness.

Reproduce the bug:

CREATE TABLE user_membership (
  email TEXT NOT NULL,
  tenant_id INTEGER REFERENCES tenants(id),
  UNIQUE (email, tenant_id)
);

INSERT INTO user_membership (email, tenant_id) VALUES ('alice@acme.com', NULL);
INSERT INTO user_membership (email, tenant_id) VALUES ('alice@acme.com', NULL);
-- Success. Now you have two rows that represent “no tenant” but duplicate email.

The multi-tenant SaaS scenario is where this bites hardest. A tenant_id might be NULL for global admin accounts, or during a data migration phase. Suddenly every deployment has duplicate admins.

The fix: a partial unique index.

-- Remove the broken UNIQUE constraint
ALTER TABLE user_membership DROP CONSTRAINT user_membership_email_tenant_id_key;

-- Create a partial index on non-null combinations
CREATE UNIQUE INDEX idx_user_membership_email_tenant_uniq
  ON user_membership (email, tenant_id)
  WHERE tenant_id IS NOT NULL;

-- Optionally, enforce that email alone is unique when tenant_id IS NULL
CREATE UNIQUE INDEX idx_user_membership_email_uniq_null
  ON user_membership (email)
  WHERE tenant_id IS NULL;

That last index ensures that you can’t insert two global admin accounts with the same email. Use it only if your logic demands that. You can also take the NOT NULL approach—make tenant_id non-nullable and use a sentinel value (like 0 or -1) for the global rows. That lets you keep a single, conventional UNIQUE (email, tenant_id) constraint, which is simpler and more optimizer-friendly.

Mistake 3: Leaving columns nullable that will never hold a NULL

I’ve seen nullable columns that haven’t seen a NULL since the Cretaceous. Every row’s null bitmap wastes one bit per row (23 bits per row in a typical 8-byte header), but that’s not the real cost. The real cost is what you lose: the planner cannot rely on IS NOT NULL checks if the column isn’t constrained to be NOT NULL. This can lead to suboptimal join strategies, unnecessary filtering, and missed opportunities for index-only scans. A nullable boolean column is a particularly insidious example: a three-state boolean (true, false, NULL) is almost never what the domain model intended, yet it’s the default when you write BOOLEAN without NOT NULL.

Leave a Comment