# Database Scaling: Indexes, Replicas, and Pooling

> Most scaling problems end at the database. Indexes, connection pooling, read replicas and cursor pagination keep it standing.

- Author: Syed Wajeeh Haider
- Published: Jul 14, 2026
- Series: Scaling to 1M Requests, part 4 of 10
- Category: Databases
- Tags: PostgreSQL, MongoDB, Indexes, Pooling
- Reading time: 3 min
- URL: https://syedwajeehhaider.com/database-scaling-indexes-replicas-pooling

You can add app servers in minutes. Your primary database is much harder to multiply, which is why it is usually the component that decides how far a system scales. The good news is that most database pain comes from a short list of causes, and each has a well-understood fix.

- **100**: default max_connections in PostgreSQL
- **O(log n)**: index lookup instead of a full scan
- **1 query**: instead of N+1 for a list page

## Indexes that match your queries

An index is only useful if it matches how you filter and sort. A query like "latest orders for a user" filters on `user_id` and sorts on `created_at`, so it wants a **compound index** in that order. In PostgreSQL, build it with `CONCURRENTLY` on a live table so writes are not blocked while it builds.

```sql
-- PostgreSQL: build without locking writes
CREATE INDEX CONCURRENTLY idx_orders_user_created
  ON orders (user_id, created_at DESC);

-- Verify the planner uses it
EXPLAIN ANALYZE
SELECT id, total, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;
```

*The MongoDB equivalent*

```js
db.orders.createIndex({ userId: 1, createdAt: -1 });

db.orders
  .find({ userId: 42 })
  .sort({ createdAt: -1 })
  .limit(20)
  .explain('executionStats'); // look for IXSCAN, not COLLSCAN
```

Always read the plan. In Postgres, a `Seq Scan` on a large table in a hot query is a red flag; in MongoDB, look for `IXSCAN` rather than `COLLSCAN` and compare `totalDocsExamined` with the number of documents returned. Remember that every index also slows writes a little, so index for real query patterns, not every column.

## Kill the N+1 queries

The N+1 problem is a list page that runs one query for the list and then one more query per row to fetch related data. Twenty rows become twenty-one round trips. It is invisible in development and devastating under load. Fetch related data in one go with a join, an `IN` query, or your ORM's include or populate feature, and log query counts per request in development so regressions are obvious.

## Connection pooling

Each Postgres connection is a separate server process with its own memory, so the database can only hold a limited number. Multiply instances by pool size and you can exhaust it long before CPU is an issue: 20 instances with a pool of 10 is already 200 connections.

- **Size pools deliberately.** Total connections across all instances must stay under the database limit with headroom for admin and migrations.
- **Use a pooler.** PgBouncer in transaction mode lets thousands of client connections share a small set of server connections.
- **Configure the ORM.** With Prisma, set `connection_limit` in the connection string; with serverless, use a proxy such as RDS Proxy.

> **Transaction mode caveat:** In PgBouncer transaction pooling, session-level features such as session variables and advisory locks held across transactions do not behave as they would on a dedicated connection. Keep each unit of work inside a single transaction.

## Read replicas and replication lag

Most applications read far more than they write. Read replicas let you send those reads to copies of the database and keep the primary for writes. The catch is **replication lag**: a replica may be milliseconds or occasionally seconds behind.

That breaks read-your-writes. A user updates their profile, the next page reads from a replica, and the old name appears. The usual fix is simple: route reads that immediately follow a user's own write to the primary, for example for a few seconds after a write or for specific flows such as checkout, and let everything else use replicas.

## Paginate with cursors, not OFFSET

`OFFSET 100000` makes the database read and discard a hundred thousand rows before returning twenty. It gets slower the deeper users go, and it shows duplicates or gaps when rows are inserted between pages. Cursor pagination uses the last seen value instead, and pairs perfectly with the compound index above.

```sql
-- Next page: pass the (created_at, id) of the last row you returned
SELECT id, total, created_at
FROM orders
WHERE user_id = 42
  AND (created_at, id) < ('2026-07-01T10:00:00Z', 98231)
ORDER BY created_at DESC, id DESC
LIMIT 20;
```

## Key takeaways

- Build compound indexes that match filter and sort order, and confirm with EXPLAIN.
- Eliminate N+1 queries; count queries per request during development.
- Treat connections as a budget: size pools, and use PgBouncer or RDS Proxy.
- Use replicas for reads, but route read-your-writes to the primary.
- Replace OFFSET with cursor pagination on anything users can scroll deeply.

Next up, part 5: **Node.js Under Load: Event Loop, Clustering, Backpressure**, where we make sure the application layer itself does not become the bottleneck.