Database Connection Management

Orientation

This page covers the lifecycle of database connections from an application’s perspective: how connections are opened, kept alive, reused, and terminated. It matters whenever an application talks to a database—web services, batch jobs, admin tools, everything. A misconfigured connection is the most common root cause of intermittent failures, leaked resources, and silent data staleness. If your service suddenly slows down, returns 502s, or starts hitting “too many connections” errors at 3 AM, this is where to look.

The reader is likely a backend engineer who has database access, runs migrations, deploys services, or debugs production latency. The focus is on practical levers: connection strings, pool settings, health-check patterns, and the failure modes that catch people off guard.

Establish a Connection

Every database client starts with a connection string or its equivalent. The format is driver-specific but follows the same pattern: host, port, database name, authentication, and optional parameters that control behavior at connect time.

PostgreSQL via psql

psql "host=db.internal port=5432 dbname=prod user=app_reader sslmode=require"

The sslmode=require flag forces an encrypted handshake before any data is sent. Omitting it defaults to whatever the server permits, which may be cleartext depending on postgresql.conf. If your client and server are in different trust domains, this is the first place to check.

MySQL/mariadb via mysql

mysql -h db.internal -P 3306 -u app_reader -p prod

The -P flag is the port; note the capitalization. A common confusion is passing -p (lowercase) without a value, which prompts for a password interactively, versus -pMyPassword on the same line, which passes the literal string to the binary. The latter is convenient for scripts but visible in process listings.

HTTP-based APIs that masquerade as a database

Some platforms expose a relational interface over HTTP. Connection here means an authenticated request, typically with a bearer token in an Authorization header and a Content-Type: application/json header. The “connection” is ephemeral—each request opens a session, executes, and closes. There is no persistent socket, so pooling does not apply in the traditional sense.

Connection Pooling

Most long-running applications do not open a new socket for every request. They keep a pool of idle sockets, hand one out per request, and return it when the request finishes. The pool has parameters that govern its size, lifespan, and garbage collection.

PgBouncer (de facto standard pooler for PostgreSQL)

PgBouncer sits between your application and the PostgreSQL server. It is configured via a text file, typically /etc/pgbouncer/pgbouncer.ini.

[databases]
prod = host=db.internal port=5432 dbname=prod user=app_reader

[pool_mode]
+ = transaction

[auth_type]
md5 = pg_md5

[max_client_conn]
1000

[default_pool_size]
20

pool_mode = transaction means the connection is returned to the pool after every transaction, not after the entire request. This reduces wait time for the next request but adds the overhead of a BEGIN/COMMIT round-trip per transaction. session mode holds the connection for the lifetime of the client session, which is faster if you have long-running requests but risks holding idle connections if clients disconnect without cleaning up.

max_client_conn caps the total number of client-facing connections PgBouncer will accept. If your application spawns more than this, new connection attempts fail with FATAL: too many connections. default_pool_size is the number of server-side connections PgBouncer maintains per database group. If the server’s max_connections is 200 and you set default_pool_size = 25 across eight groups, you’ll hit the server limit and get FATAL: too many connections from PostgreSQL itself.

Connection pooling in Node.js (pg package)

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                   // maximum number of clients in the pool
  idleTimeoutMillis: 30000,  // how long a client is allowed to remain idle
  connectionTimeoutMillis: 2000, // how long to wait for a connection before giving up
});

idleTimeoutMillis is often the culprit behind “connection leaks” that aren’t actually leaks. If your application closes a client but the pool keeps it alive until it’s been idle for 30 seconds, you’ll see the pool size stay constant while pg_stat_activity on the server shows connections in sleep state. Set this to a few seconds in development, or disable it entirely (false) if you manage your own lifecycle.

connectionTimeoutMillis is the timeout your application gives up after, returning an error like Error: connect ETIMEDOUT or, from the pg package, Error: connection timeout. If your database sits behind a load balancer or NAT, this may need to be increased beyond the default 2 seconds.

Health Checks and Liveness Probes

A connection that is “open” is not necessarily “healthy.” A socket can be allocated, bytes can have been exchanged, but the server process may have restarted, the network may be partitioned, or the database may be in a read-only state due to a failed migration. Applications need a way to probe aliveness without executing a full transaction.

Kubernetes liveness probe (generic)

livenessProbe:
  exec:
    command:
    - pg_isready
    - -h
    - $(DB_HOST)
    - -p
    - $(DB_PORT)
    - -U
    - $(DB_USER)
  initialDelaySeconds: 15
  periodSeconds: 10

pg_isready is a standard PostgreSQL utility that exits with 0 if the server is accepting connections, 1 if it is in recovery or single-user mode, and 2 if it is down. Many other databases have equivalent utilities: mysqladmin ping, mongosh --eval "db.runCommand({ping:1})", psql \conninfo from within a psql session.

If the probe fails Kubernetes will restart the container. This is the first line of defense against a stale database socket.

Application-level health check endpoint

Many web frameworks expose a /healthz or /readyz endpoint. Under the hood, this usually executes a lightweight query like SELECT 1 or SELECT current_timestamp. If the query returns, the endpoint returns 200 OK; if it fails, 503 Service Unavailable. Be explicit about what query is run. Some implementations simply check if the TCP socket connects, which does not detect a server that accepted the connection but is deadlocked or out of shared memory.

A subtle failure mode: the health check succeeds because the query returns quickly, but the connection is not returned to the pool. Subsequent requests queue up, the pool fills, and the health check continues to pass because it always gets a fresh connection from the pool’s max limit. The symptom is a gradual slowdown that looks like a throughput problem but is actually a pooling configuration problem.

Detecting and Recovering Dead Connections

A connection is dead when the underlying TCP socket is closed, the server process exited, or the network path broke. The client may still hold a socket that appears open (no I/O error yet) but any attempt to send a query will block or return an error.

Detecting dead connections in PgBouncer

When pool_mode = transaction, PgBouncer detects dead connections automatically: it runs SELECT 1 before handing a connection to the client. If the server responds with an error, PgBouncer removes that connection from the pool and creates a new one. This is invisible to the application, except that the next request after a server restart will have a slight delay while the new connection is established.

When pool_mode = session, dead connections are not detected unless you configure reset_query or cleanup_interval. A session-long connection that becomes invalid will stay in the pool until someone tries to use it, at which point the query fails with ERROR: connection does not exist. The application must handle the retry, typically by catching the error and reconnecting.

Detecting dead connections in application code

Most database drivers will surface the problem immediately. In Python with psycopg2:

try:
    cur = conn.cursor()
    cur.execute("SELECT 1")
except psycopg2.OperationalError as e:
    print(f"Connection dead: {e}")
    conn = psycopg2.connect(dsn)

The error text psycopg2.OperationalError: connection closed means the server dropped the socket, likely due to a timeout or server restart. psycopg2.InterfaceError: connection already closed means the client closed it but the socket was never explicitly shut down on the OS level.

In Go with database/sql and the pgx driver:

func ping(db *sql.DB) error {
    ctx := context.Background()
    return db.PingContext(ctx)
}

If the connection is dead, db.PingContext returns sql.ErrConnDone. This is the signal to close the connection, discard it, and fetch a fresh one from the pool.

Configuration Keys That Are Confused for Each Other

Several flags and config keys sound similar but control different parts of the connection lifecycle. Mixing them up leads to under-provisioned pools or connections that never get reused.

Key / Flag	What It Controls	Typical Default	What Happens If Set Too High
`max_connections` (PostgreSQL server)	Maximum simultaneous server‑side worker processes	100 (compiled-in)	If your pool size + PgBouncer overhead exceeds this, the server rejects new connections with `FATAL: too many connections`.
`max_pool_size` (application driver)	Maximum number of client‑side connections the driver will give out	10‑20 (driver‑specific)	If each client holds a connection for the entire request, you’ll hit `max_connections` quickly under load.
`max_client_conn` (PgBouncer)	Maximum number of clients PgBouncer will accept	100	Exceeding this returns `FATAL: too many connections` from PgBouncer, never reaching the database.
`idle_timeout` (pool)	How long a connection may sit idle before being evicted	10 minutes (varies)	If set too low, healthy connections are dropped and must be re‑established, increasing latency. If set too high, dead connections linger until the next probe or pool restart.
`connection_lifetime` (some drivers)	Absolute age a connection may reach before being retired	0 (no limit)	Without a lifetime cap, a connection that developed a subtle bug (e.g., session‑level SET commands that accumulate) will never be rotated out.

A common pattern: set max_pool_size to a number that comfortably fits under max_connections even when PgBouncer is in front, then set max_client_conn on PgBouncer to a value higher than your peak client count, and finally set an idle_timeout that matches your deployment’s scale‑down rhythm (e.g., 5 minutes for rapid autoscaling, 30 minutes for steady‑state workloads).

Sharp Edges — What Silently Fails

Idle connections that are not truly idle

Some drivers keep a TCP socket open and mark it as “idle” from the application’s perspective, but the database server may have already written to it (e.g., a wal_keep_segments fill‑up event) or the OS may have sent a RST due to a firewall timeout. The next query succeeds because the kernel’s TCP stack retransmits, but the round‑trip time jumps from sub‑millisecond to several seconds. This is especially common on cloud VMs where the network stack aggressively drops idle connections after 5–20 minutes of inactivity.

Mitigation: Set an idle_timeout on the pool that is shorter than the OS/network firewall timeout, or run a periodic “ping” job that executes SELECT 1 on every checked‑out connection before the request proceeds.

Connection strings that embed passwords

A connection string like postgres://user:pass@host/db is convenient for scripts and one‑off psql calls, but it becomes visible in process listings (ps aux), in shell history, and in any log that captures the environment. If your CI system prints environment variables, or your Dockerfile RUN commands echo the string, the password is leaked.

Mitigation: Use environment variables or a .pgpass file (host:port:db:user:password) with restricted file permissions (chmod 0600). The .pgpass file is read by libpq and never appears in process args.

SSL certificate mismatch

Many production setups enforce sslmode=verify-full or sslmode=verify-ca. If the client’s CA bundle does not match the server’s certificate, the connection fails with FATAL: SSL connection does not exist or, from the client, Error: x509: certificate signed by unknown authority. The error message does not always clearly state that the certificate chain is the problem—it may look like a network issue.

Mitigation: Keep the CA bundle path explicit (sslrootcert=/etc/ssl/certs/rds-combined-ca-bundle.pem on AWS RDS) and rotate bundles alongside certificate rotations. Test with psql --version and psql -h host -U user -d db -c "SELECT 1" after any CA update.

Connection storm after a deployment

When a service restarts, all clients attempt to open new connections simultaneously. If the pool has no pre-warmed connections, every request hits the database with a new socket. If the application’s connectionTimeoutMillis is too low, the first batch of requests fail with timeout errors, which triggers a retry loop, which opens more connections, amplifying the storm.

Mitigation: Use a connection‑pool initializer that opens N connections at container start‑up (often called “warming the pool”). In Kubernetes, an initContainer that runs psql -c "SELECT 1" before the main container starts can pre‑warm the pool if the driver supports it. Alternatively, configure PgBouncer with reserve_pool_size or use the driver’s afterConnect hook to run a lightweight query and cache the connection.

Production Lessons Learned the Hard Way

“Too many connections” is usually a pool‑size mismatch, not a query‑volume problem. Before adding read‑replicas or upgrading the database instance, check max_pool_size, max_client_conn, and default_pool_size in whatever pooler you run. The fix is often a configuration change, not a capacity upgrade.

A health check that only tests TCP connectivity is insufficient. A database can accept a TCP handshake and immediately enter a crash‑loop or read‑only mode. Always run a query (SELECT 1, SELECT current_timestamp) as part of the probe.

Connection strings in environment variables are a deployment risk. If your CI/CD pipeline prints echo $DATABASE_URL as part of a debug step, the password is compromised. Prefer .pgpass or a secret manager that injects individual variables (DB_HOST, DB_USER, DB_PASSWORD) and never logs the concatenated string.

Pooling does not protect against bad queries. A runaway SELECT * FROM huge_table without a WHERE clause will hold a connection from the pool for its entire duration, blocking every other request that needs a connection from the same pool. Pool size limits latency, not query correctness. Add query timeouts at the database level (statement_timeout) and at the application level (idleTimeoutMillis) to prevent a single bad query from draining the pool.

Restarting the database does not close client connections automatically. If the PostgreSQL server is restarted, existing client sockets may remain open on the client side, appearing connected until the next I/O operation. Applications that rely on the server to clean up will experience “zombie” connections until the pool’s idle_timeout expires or a health probe detects the failure.

Summary Checklist

✅	Item
Connection string uses `sslmode=require` (or appropriate verification) for your trust domain
Pool size (`max_pool_size` / `default_pool_size`) is lower than the server’s `max_connections`, accounting for the pooler’s overhead
`idle_timeout` is set below the OS/firewall timeout that would drop idle sockets
Health check runs a query (`SELECT 1` or equivalent), not just a TCP connect
Connection passwords are never embedded in environment variables visible to other processes
`connectionTimeoutMillis` / `connect_timeout` is tuned for your network path (especially behind load balancers/NAT)
A warm‑up strategy exists so that a service restart does not cause a connection storm
Dead‑connection handling is coded (retry / reconnect) rather than assumed away

Database connection management is rarely a “set and forget” operation. The parameters that feel like noise (idle_timeout, pool_mode, connection_timeout) are the ones that surface at 3 AM when traffic spikes, a deployment lands, or the network hiccups. Keep these levers documented, test them in staging, and treat the pool as a resource with hard limits—just like memory or disk.

This page was rewritten on 18 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.