Database Access Patterns in Depth: Connections, Pools, Transactions, and ORMs

Part 7 ended with a promise: most interesting failures start in the database. A request handler that looks correct in isolation can still corrupt a balance, exhaust a connection limit, or take a table lock for a minute, and none of it shows up in a code review of a single function. It shows up when requests overlap.

This part is about the layer between your handlers and the database: how connections are managed, what a transaction does and does not protect you from, and which query habits make a system slow or wrong at scale. The examples use PostgreSQL, node-postgres (pg) and Drizzle ORM, but most of the reasoning carries to other databases and drivers.

I did not want to describe concurrency from memory, because it is the topic where confident explanations are most often wrong. I ran a local PostgreSQL 18.4 server and tested each behavior below: pool queueing and exhaustion, transactions through a pool, lost updates, isolation levels, deadlocks, job queues, N+1 queries, indexing and pagination, and Drizzle's transaction API. Where I give a number, it came from that server on one machine. Absolute timings will differ on your hardware; the counts, the errors and the ratios are the useful part.

Quick answer

• A connection is expensive and scarce. In PostgreSQL each connection is a server process, and the server has a hard cap (max_connections, typically 100 by default).
• A pool lends a small number of connections to many requests. Size it against the total across all your app instances, and always return what you borrow.
• A transaction lives on one connection. Running BEGIN, your statements and COMMIT through a pool as separate calls is a bug that can leave half-written data.
• Read-modify-write in application code loses updates. Make the database do the arithmetic, lock the row, or use a stricter isolation level and retry.
• Keep transactions short. An open transaction holds a connection and its locks.
• Most slow queries are one of three things: a missing index, an N+1 pattern, or deep OFFSET pagination.
• An ORM does not remove these problems. It gives you convenient syntax and, if you are careful, safe defaults.

requests --> [ app instance 1: pool of N ] --\
requests --> [ app instance 2: pool of N ] ---+--> [ PostgreSQL: max_connections ]
requests --> [ app instance 3: pool of N ] --/         one process per connection
                                                        N x instances must stay under the cap

What a connection costs

The PostgreSQL documentation explains that the server uses a supervisor process, the postmaster, which spawns a new backend process whenever a client connects. So a connection is not a lightweight handle; it is a process on the database server, with its own memory and its own place in a fixed-size table.

The server limits how many it will accept. The documentation says max_connections defaults to "typically 100", determined at initdb time, and can only be changed by restarting the server. A few slots are reserved for superusers (superuser_reserved_connections, default 3), so an administrator can still get in when ordinary clients are locked out.

That limit is shared by everything: every instance of your app, your migration job, your admin tool and your analytics query. The failure mode is abrupt. Once the limit is reached, new connections are refused.

Pools: borrowing a few connections for many requests

Opening a connection per request is slow and quickly runs into the cap. A pool keeps a small set of connections open and lends them out. In node-postgres:

import pg from 'pg'

const pool = new pg.Pool({
  host, user, password, database,
  max: 10,                       // default is 10
  idleTimeoutMillis: 10_000,     // default is 10 seconds
  connectionTimeoutMillis: 2_000 // default is 0, meaning wait forever
})

pool.on('error', (err) => logger.error({ err }, 'idle client error')) // required, see below

Those defaults are from the node-postgres documentation, and one deserves a second look: connectionTimeoutMillis defaults to 0, which means no timeout. I tested what that does.

Requests queue when the pool is busy

With max: 3, I sent six queries that each took half a second. Total time was about 1.1 seconds, not 0.5. The extra three queries waited in the pool's queue for a free connection. This is the pool doing its job; it is also why a slow query affects other requests: it occupies a connection they needed.

A leaked connection can freeze your whole app

pool.connect() gives you a client you must give back. The docs are blunt: "You must release a client when you are finished with it." I checked out every connection in a pool of two and never released them, then ran another query:

default settings              -> still waiting after 1500 ms (it would wait forever)
connectionTimeoutMillis: 500  -> error: "timeout exceeded when trying to connect"

With the default, the next request hangs silently, and so does every request after it: from the outside, the app looks frozen with no error. With a timeout you get an error you can see, log, and alert on (Part 7). The habit that prevents the leak is try/finally:

const client = await pool.connect()
try {
  // ... use client
} finally {
  client.release() // runs even when the code above throws
}

For single queries, pool.query(...) checks a client out and back in for you, so there is nothing to leak.

Sizing the pool: the sum matters, not the number

The error that most often surprises teams is not a slow query but "too many clients". I started the test server with max_connections=20, then ran three pools of up to ten connections each, as three app instances would, and issued thirty one-second queries:

3 pools x max 10  vs  server max_connections = 20
  -> 11 of 30 queries failed
  -> error: "sorry, too many clients already"

(One of the 20 slots was held by my own monitoring connection.) Each pool was individually reasonable. The total was not. The rule to apply:

(pool max) x (number of app instances) + (jobs, migrations, admin tools) <= max_connections - reserved

Autoscaling makes this worse: ten more instances start under load, which is exactly when the database can least afford ten more pools. Choose a pool size from the database's capacity divided by your maximum instance count, not from the default.

There is no universal "right" pool size. More connections do not mean more throughput, because the database can only do so much work at once, so a smaller pool with a queue in front often performs better than a large one. Measure your own system rather than copying a number.

Idle errors, shutdown and the pooler question

Two more behaviors from the docs, both easy to miss:

• Idle clients can fail. A client sitting in the pool is still connected to a live server, so it can emit errors (a database restart, a network drop). The pool re-emits these on its error event, and if nothing is listening, Node can throw an uncaught error and crash the process. Attach an error listener as shown above.
• Drain on shutdown. pool.end() disconnects all clients and stops timers. Call it in the graceful shutdown sequence from Part 7, after the HTTP server has finished its in-flight requests.

Finally, when many short-lived instances each hold a pool (serverless functions are the extreme case), the total can exceed the server's limit no matter how small each pool is. The usual answer is a connection pooler between the app and the database. PgBouncer, for example, has three modes. In session pooling a server connection is assigned for as long as the client is connected and all PostgreSQL features work. In transaction pooling a server connection is assigned only during a transaction, which multiplies how many clients one connection can serve, but breaks features that depend on session state. PgBouncer's feature table lists these as not working in transaction mode: SET/RESET, LISTEN, WITH HOLD cursors, session-level advisory locks, PREPARE/DEALLOCATE, and some temporary-table behaviors. Protocol-level prepared statements work only if you set max_prepared_statements to a non-zero value. If you adopt transaction pooling, check that your driver and ORM do not depend on any of those.

Transactions

A transaction groups statements so they succeed or fail together. You need one whenever an invariant spans more than one statement: moving money between two accounts, creating an order and its lines, or inserting a row and an audit record.

The trap: transactions are scoped to one connection

BEGIN, COMMIT and ROLLBACK apply to the connection that receives them. The node-postgres documentation says so directly: "Do not use pool.query if you are using a transaction." Each call to pool.query may use a different connection, so your BEGIN can land on one connection and your insert on another.

I tested how bad this is. Each of 30 concurrent "transactions" inserted a row, then inserted a duplicate that violates a unique constraint, which must fail and roll everything back. The correct outcome is zero rows left. Done through pool.query:

// WRONG: each call may run on a different connection
await pool.query('begin')
await pool.query('insert into orders(ref, line) values ($1, $2)', [ref, 'a'])
await pool.query('insert into orders(ref, line) values ($1, $2)', [ref, 'a']) // fails
await pool.query('commit')   // never reached; rollback below
// catch: await pool.query('rollback')

via pool.query              -> 25 partial rows left behind (should be 0)
via one checked-out client  ->  0 rows left

Twenty-five of thirty transactions left committed partial data. The likely mechanism is that an insert ran on a connection that never received BEGIN, so it committed on its own, while the later ROLLBACK went to a different connection. Nothing threw an error about it. The correct pattern uses one client for the whole transaction:

async function withTransaction<T>(fn: (c: pg.PoolClient) => Promise<T>) {
  const client = await pool.connect()
  try {
    await client.query('begin')
    const result = await fn(client)
    await client.query('commit')
    return result
  } catch (err) {
    await client.query('rollback')
    throw err
  } finally {
    client.release()
  }
}

What an ORM gives you here

Drizzle's db.transaction does that bookkeeping. I verified the behaviors its documentation describes, on version 0.45.4 with the node-postgres driver:

await db.transaction(async (tx) => {
  await tx.insert(posts).values({ authorId: 1, title: 'kept' })
})                                                   // committed

await db.transaction(async (tx) => {
  await tx.insert(posts).values({ authorId: 1, title: 'thrown' })
  throw new Error('boom')                            // rolled back, error rethrown
})

await db.transaction(async (tx) => {
  await tx.insert(posts).values({ authorId: 1, title: 'rolled-back' })
  tx.rollback()                                      // throws TransactionRollbackError, rolled back
})

The table afterward contained only the first row, and no connections were left stuck in a transaction. Nested tx.transaction(...) calls become savepoints: when the inner block threw, the outer transaction still committed its own insert and the inner insert was undone. The SQL the driver sent was begin, savepoint sp1, rollback to, commit. A PostgreSQL transaction can also be configured, and I confirmed the options become real SQL:

await db.transaction(async (tx) => { /* ... */ },
  { isolationLevel: 'serializable', accessMode: 'read only' })
// begin isolation level serializable read only

One caution. Not every driver or platform supports interactive transactions. Cloudflare's D1 documents that batched statements are SQL transactions: a batch runs sequentially and, if a statement fails, the whole batch is rolled back. A 2022 Cloudflare engineering post, written while D1 was in beta, said that running BEGIN TRANSACTION there returns an error, because only one write transaction can be open at once in SQLite and one request could otherwise block everyone. The Drizzle page I read does not say which drivers lack transaction support. If you use an HTTP-based or edge database, check its current documentation, and design the invariant so one statement or one batch enforces it, instead of reading data, deciding in JavaScript and writing back.

Keep transactions short

An open transaction holds its connection (so the pool shrinks) and the locks it has taken. Two practical rules:

• Do not make network calls inside a transaction. A payment API call that takes three seconds is three seconds of held locks and a held connection, multiplied by your traffic.
• Set timeouts as a safety net. PostgreSQL has lock_timeout (abort a statement that waits too long for a lock), statement_timeout (abort one that runs too long) and idle_in_transaction_session_timeout (terminate a session that sits idle inside an open transaction). All three default to zero, meaning disabled. The documentation advises against setting lock_timeout and statement_timeout in the server-wide configuration file because that would affect all sessions, so apply them per session or per role. It also notes that an idle open transaction can also block cleanup of dead rows and contribute to table bloat.

I tested the first of these. One connection opened a transaction, updated a row, and then did nothing (a forgotten transaction). A second connection tried to update the same row with lock_timeout set to 300 ms:

error 55P03: canceling statement due to lock timeout   (after 314 ms)

Without that timeout, the second update would have waited indefinitely, as the PostgreSQL docs say a transaction seeking a conflicting lock "will wait indefinitely". The forgotten transaction appears in pg_stat_activity with state idle in transaction, which is the first place to look when requests hang.

Concurrency bugs that only appear under load

This is the section where I trusted nothing until I saw it fail.

Lost updates: the read-modify-write trap

A very common handler reads a value, computes a new one in JavaScript, and writes it back:

const { rows } = await client.query('select bal from acct where id = 1')
await client.query('update acct set bal = $1 where id = 1', [rows[0].bal + 1])

Under PostgreSQL's default isolation level, Read Committed, each statement sees data committed before it started. Two requests can both read 0, both compute 1, and both write 1. One increment vanishes with no error. I ran twelve concurrent increments on a warmed-up pool:

(My first attempt, with a cold pool, did not reproduce the bug: connections were created one at a time, so the transactions barely overlapped. Races need real concurrency to show up, which is also why they pass in tests and fail in production.)

Three fixes, in order of preference:

1. Let the database do the arithmetic. SET bal = bal + 1 is atomic within the statement.
2. Lock the row you read. SELECT ... FOR UPDATE makes other transactions wait. The documentation notes that a second SELECT FOR UPDATE on a locked row waits, then locks and returns the updated row.
3. Use optimistic concurrency. Store a version number, update with WHERE id = $1 AND version = $2, and treat zero affected rows as a conflict the caller must retry. This is a common pattern (my recommendation; I did not benchmark it here).

Isolation levels and the retry loop

PostgreSQL's default is Read Committed. It implements three distinct levels internally: a request for Read Uncommitted behaves as Read Committed. At Repeatable Read a transaction works from a single snapshot, and if it tries to change a row that another transaction has already changed since, it fails. At Serializable, PostgreSQL additionally watches for patterns that could not have happened in any one-at-a-time ordering.

The cost of the stricter levels is that you must be ready to retry. The docs say applications "must be prepared to retry transactions due to serialization failures", and that Serializable failures use SQLSTATE 40001. I ran ten read-modify-write transactions at Repeatable Read, each wrapped in a retry loop:

final balance: 10 (correct)
attempts: 49, serialization failures: 39
SQLSTATE 40001: "could not serialize access due to concurrent update"

The result was right, but ten logical operations needed 49 attempts. Under heavy contention on a single hot row, retrying is expensive, which is why the atomic-update fix is usually better than a stricter isolation level for a counter.

Isolation levels matter most for a subtler bug, write skew, where two transactions read the same condition and each makes a change that is individually fine but jointly breaks a rule. My test: "at least one person must stay on call". Two transactions each check that two people are on call, then each take a different person off:

REPEATABLE READ : both succeeded -> 0 people on call (the rule is broken)
SERIALIZABLE    : one failed with 40001, the other succeeded -> 1 on call

Repeatable Read did not catch it, since the two transactions changed different rows. Serializable did, at the price of a retry. If a rule spans several rows, either lock the rows involved, put the rule in a constraint the database enforces, or use Serializable and retry.

A retry helper is small and worth having once:

async function retryable<T>(fn: () => Promise<T>, attempts = 5): Promise<T> {
  for (let i = 0; ; i++) {
    try { return await fn() }
    catch (e: any) {
      const retriable = e.code === '40001' || e.code === '40P01'
      if (!retriable || i + 1 >= attempts) throw e
      await sleep(Math.random() * 10 * 2 ** i) // jittered backoff
    }
  }
}

Only retry when the whole transaction function is safe to run again, which means no external side effects inside it (another reason to keep network calls out).

Deadlocks

If two transactions each hold a lock the other needs, neither can proceed. PostgreSQL detects this and aborts one. I reproduced the classic case, where two transfers touch the same two accounts in opposite order:

transfer 1->2 and transfer 2->1 at the same time
  -> one succeeded; the other: 40P01 "deadlock detected"
same transfers, locking rows in id order first
  -> both succeeded

The PostgreSQL documentation's advice matches what the second run shows: "the best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order." It also says which transaction gets aborted is hard to predict and should not be relied upon, and that when you cannot guarantee ordering, retrying the aborted transaction is the fallback. Code 40P01 belongs in the same retry helper as 40001.

A job queue with SKIP LOCKED

A table used as a work queue has an obvious problem: several workers must not claim the same job. The usual approach is to select a row and skip rows another worker has locked:

select id from jobs
where state = 'new'
order by id
limit 1
for update skip locked;

I ran three workers against ten jobs. Each job was claimed exactly once, split 4, 3 and 3 across the workers, with no worker blocked waiting on another. Claim and update the job in the same transaction, and combine it with a lease or timeout so a crashed worker's job returns to the queue.

Query patterns that decide performance

The test database here had 1,000 authors and 200,000 posts. Timings come from a local server, so a network round trip, which is where most of the cost lives in production, is nearly free. The counts are what carry over.

Index your foreign keys

PostgreSQL creates an index for a primary key and for unique constraints, but not for the referencing column of a foreign key. After creating posts.author_id REFERENCES authors(id), the only index on posts was posts_pkey. A query for one author's posts used a sequential scan (200 matching rows, 67 ms). After CREATE INDEX on author_id the planner used a bitmap heap scan on the index and the same query took 1.2 ms. Foreign key columns are used in joins and in WHERE, and also when a parent row is deleted, so index them unless you have a reason not to.

To see what the planner does, ask it:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM posts WHERE author_id = 7;

Look for Seq Scan on large tables, large gaps between estimated and actual row counts, and the execution time.

N+1 queries

The N+1 pattern is a list query followed by one query per row:

const list = await pool.query('select id, author_id, title from posts order by id limit 100')
for (const row of list.rows) {
  await pool.query('select name from authors where id = $1', [row.author_id])
}

N+1 (100 posts):                     101 queries, 203 ms
one JOIN:                              1 query,     6 ms
list + one "id = ANY($1)" lookup:      2 queries,  15 ms

On a local server the gap is only milliseconds; across a real network, each of those 101 round trips costs its own latency, and the pool is occupied for the whole time. An ORM can hide the pattern because each lazy property access looks innocent. Drizzle's relational query API fetches relations in one statement; I logged the SQL for db.query.posts.findMany({ with: { author: true }, limit: 3 }) and it issued a single statement returning posts with their author nested. Whatever tool you use, log the number of queries per request in development and watch for it growing with the size of a list.

Pagination: OFFSET gets slower the deeper you go

OFFSET n makes the database produce and discard n rows before returning yours. On 200,000 rows, asking for 20 rows after skipping 190,000:

OFFSET 190000 LIMIT 20      -> 194 ms  (scanned 190,020 index entries)
keyset: WHERE (created_at, id) < (last seen) ... LIMIT 20  -> 0.45 ms  (scanned 20)

Keyset (cursor) pagination remembers where the previous page ended and continues from there. It needs a stable, unique ordering (that is why the example orders by created_at, id) and a matching index, and it cannot jump straight to page 400. For feeds and infinite scroll, that is usually the right trade. Whatever you choose, cap the page size on the server (Part 6) so one request cannot ask for a million rows.

Parameters, always

Use parameterized queries, as in every example above ($1, $2). As the OWASP guidance quoted in Part 6 puts it, parameterization, not input cleaning, is the defense against SQL injection. Dynamic identifiers (table or column names, sort directions) cannot be parameters, so allow-list them.

Schema changes without downtime

Part 10 covers deployment, but one rule belongs here: a migration runs while old code is still serving traffic. Make changes in steps that both versions of the code tolerate. Add the new column or table first, deploy code that writes both, backfill, switch reads, and remove the old structure in a later release. Run migrations as their own step rather than at application start, so ten instances do not race to apply the same change, and be aware of statements that take long locks on large tables.

Debugging checklist

Who is holding connections or locks right now?

SELECT pid, state, wait_event_type, now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY xact_start NULLS LAST;

Look for idle in transaction with an old xact_age: that is a leaked or forgotten transaction. In my test, the holder appeared exactly this way.

How close are you to the connection limit?

SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;

Is a query slow because of the plan? Use EXPLAIN (ANALYZE, BUFFERS). For recurring offenders, log_min_duration_statement can log queries slower than a threshold.

Is the pool starved? If requests hang but the database looks idle, suspect a leaked client or an exhausted pool with no connectionTimeoutMillis. Log pool statistics (pool.totalCount, pool.idleCount, pool.waitingCount) alongside your other metrics from Part 7.

Production checklist:

• Pool size chosen from the database limit and the maximum instance count.
• connectionTimeoutMillis set; an error listener on the pool; pool.end() on shutdown.
• Every checked-out client released in finally.
• Transactions use one client (or the ORM's transaction API), stay short, and contain no network calls.
• lock_timeout, statement_timeout and idle_in_transaction_session_timeout set deliberately.
• No read-modify-write in application code for shared values.
• Retries for 40001 and 40P01, with backoff and a limit.
• Multi-row rules enforced by constraints, locks, or Serializable.
• Foreign keys indexed; slow queries checked with EXPLAIN.
• Query count per request watched; no N+1; page size capped; keyset pagination for deep lists.
• Parameterized queries everywhere.

Interview answer vs production answer

The interview answer: "Use a connection pool, wrap related writes in a transaction, and add indexes."

The production answer is about the failure modes between those sentences:

• A pool is a shared limit, not a free speed-up. Its size is a decision about the whole fleet, and without a timeout it turns a leak into a silent outage.
• "Use a transaction" is incomplete. Transactions are per-connection, the default isolation level allows lost updates, and a stricter level means writing retry logic.
• Races need real concurrency to reproduce. A cold pool hid the lost update in my first test; a warm one showed 1 where 12 was expected.
• Constraints beat application checks. If a rule matters, let the database enforce it.
• ORMs help, but you still own the SQL. Check the queries an ORM actually sends, and know what your driver supports.

FAQ

How big should my connection pool be?

There is no universal number. Start from the database's connection limit divided by the maximum number of app instances (minus headroom for jobs and admin), then measure. A smaller pool with queueing often outperforms a larger one.

Why does my app hang with no errors?

A common cause is exhausted pool connections: clients checked out and never released, with connectionTimeoutMillis at its default of 0 so every new request waits forever. Set a timeout so it fails loudly.

Do I always need a transaction?

No. A single statement is already atomic. Use a transaction when an invariant spans several statements. And do not add one around a single read; it only holds a connection longer.

What is the difference between FOR UPDATE and a higher isolation level?

FOR UPDATE locks the specific rows you read, so others wait. A higher isolation level lets transactions proceed against a snapshot and aborts one on conflict, so you retry. Locking is simpler for a known hot row; Serializable is better when the rule spans rows you cannot easily lock.

Should I retry failed transactions?

Retry serialization failures (40001) and deadlocks (40P01), with backoff and a limit, as long as the transaction has no external side effects.

Is offset pagination ever fine?

Yes, for small tables and shallow pages, and when users need to jump to page numbers. It degrades with depth, so for large or growing lists use keyset pagination.

Does Drizzle (or any ORM) prevent N+1?

Not automatically. Drizzle's relational queries fetch related rows in one statement, but you can still write a loop that queries per row. Count queries per request to catch it.

Can I use transactions on every database platform?

Not always. Some HTTP-based or edge databases offer batched, atomic statements instead of interactive BEGIN/COMMIT. Read your platform's documentation and design the invariant accordingly.

Glossary

The end-to-end mental model

 request
    |
    v
 pool.query(...)  ----------------------------------- single statement: atomic already
    |
    v
 needs several statements to stay consistent?
    |
    v
 db.transaction(...)  /  one checked-out client       (never BEGIN/COMMIT via pool.query)
    |        |
    |        +-- short; no network calls inside; lock_timeout / statement_timeout set
    |
    +-- shared value?      -> atomic UPDATE, or SELECT ... FOR UPDATE
    +-- multi-row rule?    -> constraint, lock the rows, or SERIALIZABLE + retry (40001, 40P01)
    +-- queue of jobs?     -> FOR UPDATE SKIP LOCKED

 pool size x instances  <=  max_connections (minus reserved)     (or add a pooler)

 queries: index foreign keys | no N+1 | keyset pagination | parameterized
 watch:   pool waiting count, idle-in-transaction sessions, slow query log

Parts 1 to 7 covered who may do what, how to guard the entrance, and how to see failures. This part covered the layer that keeps the data true. The remaining two posts are about speed and delivery: the next is caching, where the hardest problem is keeping a fast copy honest about the slow truth underneath, and the last is deployment.