Connections and query load

Before a database runs out of disk or write throughput, it often runs out of something more mundane: connections, or CPU spent on a few bad queries. Both are cheap to fix once you see them.

Connections are expensive

Opening a database connection costs a TCP handshake, TLS, authentication and, in Postgres, a whole operating-system process with its own memory. Servers keep a connection pool so requests borrow an open connection instead of creating one.

The trouble is multiplication. Every app server has its own pool, and they add up:

Poolers

A pooler like PgBouncer or RDS Proxy sits between the app and the database. Apps open as many cheap connections to the pooler as they like; the pooler shares a small set of real database connections among them.

In transaction mode, a client holds a real connection only for the length of a transaction, so a few hundred database connections can serve thousands of clients. The catch: anything tied to a session (prepared statements in older setups, temporary tables, session variables, advisory locks) breaks, because consecutive transactions may run on different connections.

Smaller pools are faster

It's tempting to raise pool sizes when requests wait for a connection. Usually that's backwards. A database with 16 cores does not get faster running 500 queries at once; it gets slower as they fight over CPU, locks and buffers. A rough starting point is a few connections per core in total, with requests queueing in the app where waiting is cheap and visible.

Routing reads and writes

With replicas, something must decide where each query goes:

• In the app: two pools, one for the primary and one for replicas. Simple and explicit; the code knows which reads can tolerate lag.
• In a proxy: the proxy inspects queries and sends reads to replicas. Convenient, but it can't know which reads must see the user's own latest write.

Either way, reads that follow a write by the same user often need the primary, or a replica that has caught up. The next module covers how.

Find the heavy queries

Databases record per-query statistics (pg_stat_statements in Postgres, the Performance Schema in MySQL). Sort by total time, not average: a 2 ms query run 50,000 times a second costs far more than a 2-second report run hourly. Typically a handful of query shapes account for most of the load, and each has a cheap fix: an index, caching the result, batching N+1 queries into one, or moving it to a replica.