Your Schema is a Bigger Performance Problem Than Your Queries

TL;DR

• Your biggest performance and cost drivers aren't in pg_stat_statements; they're in your CREATE TABLE statements from five years ago.
• A randomly generated primary key (like UUIDv4) can double your write I/O, inflate index sizes by 40%+, and destroy cache efficiency on high-volume tables.
• Unlike a bad code commit, you can't just revert a bad schema decision on a 5TB table. Prevention is the only sane strategy.

---

Everyone loves to be the hero who rewrites the slow query. You find the top offender in pg_stat_statements, spend a day rearranging joins or adding an index, deploy it, and watch the query time drop by 80%. It's a quick, satisfying win.

It's also, most of the time, a waste of your talent.

You're tidying the living room while the foundations of the house are cracking. The most expensive, intractable, and systemic performance problems I see in Postgres and MySQL deployments are not the result of a missing index. They are the direct, inevitable consequence of poor schema modeling and data preparation choices made years prior.

At Oracle, I owned components of the query engine, including temp tables and Zonemaps. Zonemaps are a brilliant piece of engineering: metadata stored per data block (or per partition in Postgres) holding the min/max values for columns within that block. A query like WHERE sales_date > '2024-06-01' can use this metadata to skip reading 99% of the table's blocks entirely. But here's the lesson from shipping that code: the world's most sophisticated optimizer can't save you from a bad schema. If your sales_date is a random string or your partition key is customer_id but you query by date, those features do nothing. The optimizer is downstream of the data's physical layout.

This is the core of database liability. You can roll back a bad application deployment in minutes. You cannot easily change the primary key of a 10-billion-row table from UUID to BIGINT. That's not a deployment; it's a year-long migration project with massive risk. Your schema isn't code; it's concrete.

The Canonical Mistake: Random vs. Sequential Primary Keys

Let's get specific. The most common and damaging schema error I see is using standard UUIDs (v4, random) as the primary key on a high-write-volume table.

On the surface, it makes sense. They are globally unique and don't require a central coordinator. But they are catastrophic for the physical storage layer of any database that uses a B-Tree index, which is nearly all of them.

Here’s why. A B-Tree index is sorted. When you INSERT a new row, the database must place its key in the correct sorted position in the index. A UUIDv4 is random. This means every new row you insert has an equal chance of landing on any page in the index.

This has two disastrous effects:

1. Massive Write Amplification & Index Bloat: To insert the new key, Postgres must fetch the target leaf page from disk into the buffer cache. If the page is full, it must be split into two pages, and the parent node must be updated. This is a write-intensive operation. Because keys are random, you are constantly splitting pages that are, on average, only 50-70% full. This leads to what we call index bloat. The index on disk can become 2x larger than it needs to be, consisting of half-empty pages. Autovacuum has to work overtime to clean this up, generating even more I/O.

2. Destroyed Cache Locality: When you query for the 100 most recent orders (ORDER BY created_at DESC LIMIT 100), if your PK is time-ordered, those 100 entries are likely on the same 1-2 index leaf pages, which are probably already hot in the buffer cache. If your PK is a random UUID, those 100 entries are scattered across 100 different leaf pages all over the index. The database has to do 100 random reads from the buffer cache. The probability of a cache miss skyrockets. You're constantly churning your buffer cache for no reason.

Here's a simplified plan for fetching a single recent row using a secondary index, but the real cost is hidden in the random I/O to get to the index page itself:

-- The plan looks innocent, but the cost is in how many *different* pages
-- Postgres has to touch for a range of recent_event_id values.
EXPLAIN ANALYZE SELECT * FROM events WHERE event_id = 'some-uuid-v4-value';

                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Index Scan using events_pkey on events  (cost=0.43..8.45 rows=1 width=128) (actual time=0.045..0.046 rows=1 loops=1)
   Index Cond: (event_id = 'some-uuid-v4-value'::uuid)
 Planning Time: 0.112 ms
 Execution Time: 0.065 ms

The fix is to use a time-ordered UUID like a ULID or the new UUIDv7 standard. These keys are still unique but are lexicographically sortable by time. Every INSERT becomes an append to the "right edge" of the B-Tree. Pages are filled to 99-100% capacity before splitting, which is a rare, efficient operation. Index size is minimized. Queries for recent data find all their rows co-located on a few hot pages.

On a recent engagement, for a 3 billion row events table, switching from UUIDv4 to ULIDs for the primary key — without changing a single query — resulted in a 40% reduction in the pkey index size and cut the buffer cache miss ratio for that index by more than half during peak ingestion. That's a massive reduction in I/O and cloud spend, achieved by fixing the schema, not the SQL.

The Thousand Other Cuts

This isn't just about primary keys. This liability accrues everywhere:

• Unbounded jsonb: You have a users table with a 5MB jsonb metadata column. A high-frequency query SELECT id, email FROM users becomes SELECT * FROM users. Postgres now has to de-TOAST and drag that 5MB blob off disk and through the network for every row, just to throw it away. Putting hot email and name columns into their own VARCHAR columns is data preparation 101, yet it's constantly ignored.
• timestamp as TEXT: Storing dates or timestamps as TEXT is a cardinal sin. Every comparison, sort, or range scan (WHERE created_at > '2024-01-01') becomes an expensive string operation, often mangled by collation rules, and kills the effectiveness of an index scan. A TIMESTAMPTZ is an 8-byte integer comparison. It's orders of magnitude faster.
• Over-reliance on CTEs for transforms: The WITH clause is not a free lunch. In Postgres, it's an optimization fence. A complex CTE might be materialized entirely into memory (or spilled to disk if it exceeds work_mem). At Oracle, we spent ages optimizing how and when to materialize intermediate results. For multi-stage data prep, explicitly using a TEMPORARY TABLE gives the planner better stats and you more control, often resulting in far less memory pressure and faster execution than a monstrous, nested CTE.

What DeepSQL does about this

Most query advisors are stuck in the past, running regexes on SQL text. DeepSQL was built on the premise that you cannot analyze a query without understanding the physical structure of the data it's accessing. Our platform connects to your database and analyzes the schema, the statistical distribution of your data, and your live query workload as a unified whole. It flags a UUIDv4 primary key on a high-write table and projects the I/O and storage cost of leaving it. It sees a query pattern that consistently extracts two keys from a wide jsonb column and quantifies the wasted CPU and I/O from de-TOASTing. This isn't about finding a single slow query; it's about identifying and preventing the multi-million dollar architectural mistakes before they get baked into your foundation.