Scaling PostgreSQL to 10M Rows: Indexing Strategies, Connection Pooling & Query Profiling
Overcoming database latency bottlenecks: fixing N+1 queries, setting up PgBouncer connection pools, and utilizing partial indexes for sub-15ms queries.
By Uttam Thapa · · Database
⚡ Executive Summary (TL;DR)
Nothing changes when a table crosses ten million rows except the cost of what you were already doing wrong. Three patterns account for almost every incident:
N+1 queries from an ORM, connection exhaustion from serverless functions, and deep OFFSET pagination.
The fixes are eager loading, a pooler in front of the database, and keyset pagination.
Figure 1: The plan does not change as a table grows. The price of the same plan does.
Why "Suddenly" Slow Is Never Sudden
A sequential scan over ten thousand rows takes about a millisecond, which is invisible. The same scan over ten million takes seconds. Nothing about the query
changed and no deployment caused it — the query was always doing the wrong thing, and the table finally grew large enough for it to matter. This is why database
performance work feels like archaeology: you are looking for decisions made long before the symptom.
🚨 The three anti-patterns behind most incidents
- N+1 loops. Fetch 100 parent rows, then issue 100 more queries for their children.
- Connection exhaustion. Hundreds of serverless invocations each opening a direct connection.
- Deep
OFFSET. OFFSET 50000 makes the database read and discard fifty thousand rows to return twenty.
N+1: The Cost Is Round Trips, Not Rows
An ORM makes lazy relation access look free. Iterating a list of orders and reading order.customer inside the loop issues one query per iteration —
each individually fast, and each carrying a full network round trip. A hundred one-millisecond queries with two milliseconds of latency each is 300 ms of
mostly waiting.
// N+1: one query, then one more per row.
const orders = await db.order.findMany({ take: 100 });
for (const order of orders) {
const customer = await db.customer.findUnique({ where: { id: order.customerId } });
}
// One round trip. The database does the join it was built for.
const orders = await db.order.findMany({
take: 100,
include: { customer: true },
});
N+1 is invisible in code review because the loop looks perfectly ordinary. Log query counts per request in development, and alert when a single request issues
more than a handful — that catches it at the moment it is introduced rather than at ten million rows.
Connection Pooling: The Serverless Tax
Every PostgreSQL connection is a separate backend process with its own memory. A traditional server holds a small pool for its lifetime. Serverless functions
have no such lifetime: each invocation opens its own connection, and a hundred concurrent invocations means a hundred processes on a database sized for twenty.
A pooler such as PgBouncer sits in front and multiplexes many client connections onto a few real ones. In transaction mode, a backend is only held for the
duration of a transaction rather than a whole session, which is what makes hundreds of clients feasible on a small instance.
💡 Transaction mode has rules
In transaction mode a connection is not yours between statements, so anything relying on session state breaks — SET outside a transaction,
session-scoped variables, prepared statements, and LISTEN/NOTIFY. If you use PostgreSQL row-level security, this is precisely why
the tenant variable must be set with transaction scope.
Keyset Pagination: Constant Time at Any Depth
OFFSET is not a seek — the database produces every row up to the offset and throws them away. Page one is instant, page five hundred reads ten
thousand rows to return twenty, and page five thousand is a full scan. The cost grows with how far the user has scrolled, which is the opposite of what anyone
expects.
Keyset pagination remembers where the last page ended and asks for rows after it. Backed by an index on the sort column, every page costs the same:
-- Slow: reads 50,020 rows to return 20.
SELECT id, total_amount, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 50000;
-- Constant time: seeks the index and reads exactly 20.
SELECT id, total_amount, created_at
FROM orders
WHERE created_at < '2026-08-24T10:00:00Z' -- last row of the previous page
ORDER BY created_at DESC
LIMIT 20;
Two caveats. The cursor column must be unique or paired with a tiebreaker — with duplicate timestamps, rows are skipped or repeated across pages, so sort on
(created_at, id) and compare on the pair. And keyset pagination gives next and previous, not "jump to page 400"; in practice infinite scroll and
"load more" fit it perfectly, while numbered pages do not.
Read the Plan Before Changing Anything
EXPLAIN ANALYZE shows what the database actually did rather than what you assume it did. Three things to look for, in order:
Seq Scan on a big table
A missing or unusable index. On a small table it is correct and faster than an index — check the row count first.
Rows estimate vs actual
A large gap means stale statistics. Run ANALYZE — the planner is choosing badly on bad information.
Sort with external merge
The sort spilled to disk. Either raise work_mem or add an index matching the sort order.
Indexes are not free. Each one is updated on every insert and update, so an unused index makes writes slower for no benefit. Query
pg_stat_user_indexes for indexes with zero scans and drop them — most mature databases carry several.
✅ Key takeaways
- ✓Growth reveals problems, it does not create them. The bad plan was there all along.
- ✓Count queries per request in development. It is the only reliable way to catch N+1 early.
- ✓Put a pooler in front of serverless. Connections are processes, and functions do not share them.
- ✓Replace
OFFSET with a keyset cursor, with a tiebreaker column to avoid skipped rows.
- ✓Read
EXPLAIN ANALYZE first. Indexes added on a hunch slow writes and fix nothing.
- ✓Drop unused indexes. Every one is a tax on every write.
Start with the fundamentals in PostgreSQL indexing and query performance, and see
multi-tenant database architectures for why pooling mode and row-level
security interact.
Frequently asked questions
What breaks first when a PostgreSQL table reaches 10 million rows?
Queries that were doing a sequential scan all along. At small volumes a scan is invisible; at ten million rows it dominates. The plan did not change — the cost of the same plan did.
Why do I need PgBouncer?
Each PostgreSQL connection is a backend process with real memory cost, and serverless platforms open many short-lived connections. A pooler multiplexes them onto a small set of real connections instead of exhausting the server.
Is OFFSET pagination a problem at scale?
Yes. OFFSET makes the database walk and discard every skipped row, so page 5,000 is thousands of times more expensive than page one. Cursor pagination keyed on an indexed column stays constant.
Home · Projects · Blog · Services · Résumé · Contact