How We Debugged a 300+ Million Row PostgreSQL Table and Turned a 2 Second Query into a Millisecond Query
As your application grows, database performance problems stop being about "adding an index" and become about understanding how the PostgreSQL query planner thinks.
Recently, I had to debug a production PostgreSQL database that had just crossed 300 million rows. A seemingly simple query that should have returned 20 rows was taking over 2 seconds, and one migration accidentally resulted in a 12-hour running UPDATE.
This post walks through the complete debugging journey from identifying the problem to redesigning the schema and why this only became visible once the table crossed the 300M-row mark.
The Problem
We had a table similar to this:
ShortUrl
---------
id (Primary Key)
createdBy
orgId
originalUrl
...
The query looked innocent:
SELECT *
FROM "ShortUrl"
WHERE "createdBy" IN (...)
ORDER BY id DESC
LIMIT 20;
At smaller table sizes this query was fine. It only started misbehaving once the table passed roughly 300 million rows which is what made it so hard to catch in staging.
Surprisingly:
Removing LIMIT made it fast.
Removing ORDER BY made it instant.
Adding both together made the query extremely slow.
Step 1 Looking at the Execution Plan
The first thing we checked was:
EXPLAIN
SELECT ...
The planner returned:
Limit
-> Index Scan Backward using ShortUrl_pkey
Filter: createdBy = ANY(...)
This was the first clue. Instead of using our composite index
(createdBy, id)
PostgreSQL decided to scan the Primary Key backwards.
Why PostgreSQL Did This
The planner assumed it could find 20 matching rows quickly by scanning the newest IDs first.
At 300 million rows, only a small slice actually matched our filter:
300+ million rows total
Only ~10,300 matching rows
The planner estimated:
~211,000 rows
Reality:
~10,300 rows
Because of this bad estimate, PostgreSQL scanned an enormous number of rows from the primary key before finding just 20 matches — and at 300M+ rows, that gap between estimate and reality was large enough to turn a fast query into a multi-second one.
Why the Composite Index Wasn't Used
We already had:
CREATE INDEX idx_shorturl_createdby_id
ON "ShortUrl"(createdBy, id);
So why wasn't PostgreSQL using it?
Because our query was:
WHERE createdBy IN (...)
ORDER BY id DESC
A B-Tree ordered as (createdBy, id) groups rows like this:
User A
id 1
id 5
id 20
User B
id 2
id 9
id 30
Our query wanted a single global ordering:
30
20
9
5
2
1
That ordering doesn't exist inside a multi-user composite index — each user's IDs are only sorted within their own group. So PostgreSQL preferred the primary key, since it was already globally ordered by id.
The First Optimization
Instead of filtering by multiple users, we realized something important: every user belongs to exactly one organization.
Instead of:
WHERE createdBy IN (...)
we could simply query:
WHERE orgId = ?
This dramatically simplified the access pattern — filtering by a single value instead of a list of users.
The Second Problem
We added an orgId column and changed the query:
SELECT *
FROM "ShortUrl"
WHERE orgId = ?
ORDER BY id DESC
LIMIT 20;
Yet PostgreSQL was still using ShortUrl_pkey instead of the new column.
Why? Because we had only created:
CREATE INDEX idx_shorturl_orgid
ON "ShortUrl"(orgId);
That index helps filtering. It does not help ordering.
The Correct Index
The correct index was:
CREATE INDEX CONCURRENTLY idx_shorturl_orgid_id
ON "ShortUrl"(orgId, id DESC);
Now PostgreSQL can:
Jump directly to the organization
Read the newest IDs
Stop after 20 rows
No sorting. No filtering through hundreds of millions of rows.
A Production Lesson About Prisma
One thing we discovered during the migration was that Prisma generates:
CREATE INDEX ...
instead of:
CREATE INDEX CONCURRENTLY ...
The reason is simple: CREATE INDEX CONCURRENTLY cannot run inside a transaction. Prisma wraps migrations in transactions to guarantee atomicity, so it can't safely emit concurrent index creation by default.
On a 300M+ row table, a regular CREATE INDEX would have held a lock long enough to block writes for an unacceptable amount of time. For large production databases, it's often better to:
Generate the migration
Edit the SQL manually
Create large indexes concurrently
The Backfill Disaster
Next we needed to populate the new orgId column:
UPDATE "ShortUrl"
SET "orgId" = ?
WHERE "createdBy" = ?
AND "orgId" IS NULL;
One user alone owned nearly 90 million URLs.
The update had been running for 12 hours.
Investigating the Running Query
Checking pg_stat_activity:
SELECT *
FROM pg_stat_activity;
showed:
wait_event_type = LWLock
wait_event = WALBufMapping
This was extremely valuable. The query wasn't blocked. It wasn't deadlocked. It wasn't waiting on disk I/O. It was waiting on a lock related to mapping pages in the WAL buffer a sign that a single massive transaction was generating WAL faster than it could be flushed and recycled.
Why Huge Transactions Are Dangerous
Updating tens of millions of rows inside a single transaction causes:
Huge WAL generation
Large rollback segments
Long recovery times if the server crashes mid-transaction
Autovacuum pressure (dead tuples pile up until the transaction commits)
Checkpoint pressure
WAL contention, as we saw here
Eventually we terminated the update:
SELECT pg_terminate_backend(pid);
The Better Backfill Strategy
Instead of updating tens of millions of rows at once, we switched to batches:
WITH batch AS (
SELECT id
FROM "ShortUrl"
WHERE createdBy = ?
AND orgId IS NULL
LIMIT 10000
)
UPDATE "ShortUrl" s
SET orgId = ?
FROM batch
WHERE s.id = batch.id;
Repeat. Commit. Repeat.
This keeps each transaction small, dramatically reduces WAL generation per commit, and lets autovacuum keep up as it goes instead of facing a single 90-million-row cleanup at the end.
Monitoring PostgreSQL
Throughout the investigation we relied heavily on PostgreSQL's system views.
Active queries:
SELECT * FROM pg_stat_activity;
Vacuum progress:
SELECT * FROM pg_stat_progress_vacuum;
Index creation progress:
SELECT * FROM pg_stat_progress_create_index;
Execution plans:
EXPLAIN
EXPLAIN ANALYZE
These views are invaluable when diagnosing production issues at this scale.
Lessons Learned
1. Never assume PostgreSQL will choose your index. Always verify with EXPLAIN especially once a table crosses into the hundreds of millions of rows, where planner misestimates get punished much harder.
2. ORDER BY can completely change the execution plan. Adding one clause caused PostgreSQL to abandon an otherwise good index.
3. Composite indexes should match your access pattern. A query filtering by orgId and ordering by id DESC needs (orgId, id DESC) not just (orgId).
4. Massive UPDATE statements are rarely a good idea at scale. What's harmless at a few million rows becomes a 12-hour incident at 300 million. Batch updates are almost always safer in production.
5. CREATE INDEX CONCURRENTLY is essential for large production databases. It avoids blocking writes while building indexes.
6. PostgreSQL's wait events are incredibly useful. Seeing WALBufMapping immediately redirected our investigation instead of chasing locks, we focused on WAL contention.
Final Thoughts
Performance tuning isn't about adding random indexes. It's about understanding:
How the PostgreSQL planner estimates costs
Why it chooses one index over another
How data distribution affects execution plans
How large transactions impact WAL, checkpoints, and autovacuum
How to design indexes around real query patterns, not just columns
In our case, the biggest improvement didn't come from adding more hardware it came from changing the data model from createdBy-centric queries to orgId-centric queries, building the right composite index, and redesigning the backfill strategy.
Crossing 300 million rows is where these architectural decisions stop being theoretical and start being the difference between a millisecond query and a multi-second one.
Published with LeafPad[ END_OF_POST ]