Five schema mistakes I keep finding in migration files. Each one zero alarms, zero errors, and starts costing you real pain once tables cross a few million rows. Here’s the quick list—then a deep dive into why they hurt and exactly how to fix them.
For a visual whiteboard walk‑through of how these patterns degrade storage and memory at the hardware level, see the full session on the MyDBA YouTube channel.
TL;DR — The 5 Mistakes and Their Fixes
| Mistake | Why it hurts at scale | The 5‑Minute Fix |
|---|---|---|
| Unbounded VARCHAR / TEXT | Large column values can cause out‑of‑line TOAST storage, potentially affecting read patterns and write behavior. | Add a CHECK (char_length(col) <= N) constraint. |
| Nullable columns in multi‑column UNIQUE |
NULL != NULL silently lets duplicates slip past your unique constraint. |
Replace with partial unique indexes WHERE col IS NOT NULL (and WHERE col IS NULL if needed). |
| Missing NOT NULL | Null bitmap overhead on every row; query planner can’t optimise away IS NOT NULL checks. |
Analyse actual data, then ALTER TABLE … ALTER COLUMN … SET NOT NULL. |
| TEXT vs JSONB confusion | TEXT forces re‑parsing every read; JSONB‑for‑everything wastes CPU and erodes structure. | Use JSONB only for nested, searchable payloads with GIN indexes; hoist frequently‑filtered keys into real columns. |
| Random UUIDv4 primary keys | Random insert order causes constant B‑tree page splits, index bloat, and poor cache locality under high writes. | Switch to UUIDv7 (or BIGINT sequences) for append‑only B‑tree writes. |
Why Schema Mistakes Are the Quiet Killers
The most dangerous database issues don’t cause a 500 error on day one. They are the ones that quietly degrade your infrastructure.
These aren’t syntax errors. pg_dump won’t complain. pg_restore won’t refuse. The application boots fine. Unit tests pass. Then you go to production.
When a query planner builds an execution plan, it relies on statistics and constraints. If you tell Postgres a column can be nullable when it never actually holds a NULL, or force it to index completely random 128‑bit strings, you limit its ability to optimise memory.
Over time, your indexes bloat, meaning less of your active dataset fits in shared_buffers. Once Postgres has to start swapping index pages from disk to memory just to service basic inserts, your throughput falls off a cliff. A SELECT that used to take 2
Top comments (0)