Data Access

A service runs perfectly for weeks, then freezes on a Tuesday afternoon. No errors, no load spike: just every request waiting, indefinitely. The diagnosis is almost always the same: a code path borrows a connection from the pool and never returns it, because an exception occurred between the checkout and the release. After a few hundred occurrences, the pool is empty, and every new request waits for a connection that will never come back.

The other major failure in this layer is SQL injection, and it almost always happens for the same reason: someone built a query by string concatenation because parameterization didn't work for their case — a dynamic column name, a variable-length IN clause, a configurable sort order. All three cases have clean solutions, and this lesson gives them. The common thread: a connection is borrowed and returned always, and a value coming from the client never becomes part of the query text.

The Connection Pool

Opening a PostgreSQL connection costs several milliseconds — TCP handshake, authentication, creation of a server process on the database side. A pool keeps a set of connections open and lends them out.