Your Database URL Is a Production Outage Waiting to Happen

Today

Every backend has one line that has more production decisions compressed into it than any other line of code: the database URL. It looks like config. It is actually your pool sizing, your TLS policy, your connection lifetime, your auth, and sometimes your routing — hidden behind a string you copied from a dashboard and never read again.

I have debugged more outages that trace back to this string than to any slow query. The queries are usually fine. The connection is the problem. Here is what actually goes wrong, in the order it bites you after deploy.

TL;DR

  • max: 10 is per process, not per app. Ten instances is 100 connections before your pooler even starts.
  • connectionTimeoutMillis defaults to 0, which means hang forever instead of failing fast.
  • sslmode=require encrypts the wire but does not prove you are talking to the right server.
  • Transaction-mode poolers (PgBouncer, Supavisor) break prepared statements and session state.
  • A password containing @, /, #, or ? silently changes what your URL means.

1. The default pool size is a per-process multiplier

If you construct a pg pool and never set max, you get 10. That feels small. It is not, because it multiplies by every process that can talk to Postgres at once.

pool max 10 × 20 app instances = 200 open connections
Postgres default max_connections = 100

You hit the ceiling before you hit load. New requests fail with remaining connection slots are reserved or sorry, too many clients already, and the error rate climbs even though your CPU is idle.

Serverless makes this worse, because "instances" are per invocation and you do not control how many exist. Each warm function instance keeps its own pool alive. Under a traffic spike you can manufacture hundreds of connections from code that looks perfectly innocent.

The rule: your total connection budget is the product, not the max.

(app processes × pool max) + migrations + admin + pooler headroom
         must stay under max_connections, with room to breathe

If you are on serverless, the answer is almost never "bigger pool." It is a pooler in front of Postgres (PgBouncer, Supavisor, RDS Proxy) and a tiny per-instance pool — often max: 1 for a one-shot function.

2. connectionTimeoutMillis defaults to no timeout at all

From the pg defaults: connectionTimeoutMillis is 0. Zero does not mean "instant." It means the client will wait forever for a connection that is never coming.

So when Postgres or the pooler is saturated, your requests do not fail — they hang. Connection attempts pile up, sockets stay open, and the process leaks file descriptors until it dies. You get a 100% error rate with no errors in the log, because nothing ever threw.

Set it. There is no production argument for the default.

connectionTimeoutMillis: 5_000

The same idea applies server-side. Postgres will happily run a query until the heat death of the universe, and it will happily leave a transaction open and idle while holding locks:

-- Set these at the role level so every connection inherits them.
alter role app_user set statement_timeout = '10s';
alter role app_user set idle_in_transaction_session_timeout = '30s';

idle_in_transaction_session_timeout is the one people forget. A single bug that opens a transaction and forgets to commit can block every write to a table while the connection sits there looking idle.

3. sslmode=require is not the guarantee you think it is

sslmode=require means "encrypt the connection." In the libpq family it does not mean "verify that the server is who it claims to be." The certificate is accepted without checking the chain, so a network attacker who can redirect your traffic can terminate TLS themselves and read everything.

verify-full is the one that actually validates the hostname against a trusted CA. Hosted providers usually publish the CA cert you need to pin.

One honest caveat: Node's Postgres drivers do not all interpret this parameter identically, and behavior has changed across versions. Do not assume — assert. Connect once with the setting you intend to ship and confirm the TLS state. If your driver silently upgrades require into ssl: true, you have encryption and no identity check, which is fine inside a private network and not fine over the public internet.

4. Transaction-mode poolers eat prepared statements and session state

Putting PgBouncer or Supavisor in transaction mode in front of Postgres is the right call for high concurrency. It is also where a lot of apps break in a new, confusing way.

In transaction mode, a server-side connection is handed to whichever client needs it for the duration of one transaction. Session state does not survive. That breaks:

  • Prepared statements. A statement prepared on connection A may be executed on connection B. You get prepared statement "s1" already exists, prepared statement "s1" does not exist, or a bind error about parameters.
  • SET and session variables. Anything you set for "this connection" disappears.
  • Cursors and portals. Same reassignment problem.

If you use an ORM, this is why you see flags like Prisma's ?pgbouncer=true in the connection string — it disables the prepared-statement path. If you are on pg directly, you are mostly fine because it uses the unnamed statement, but any library that names statements is not.

The host and port are not cosmetic. On Supabase, for example, port 6543 is transaction mode and port 5432 is session mode. Migrations, advisory locks, and long-lived session work belong on the direct/session URL; the app's query traffic belongs on the transaction pooler. Paste the pooler URL into your migration script and it will fail in a way that has nothing to do with migrations.

5. The percent-encoding landmine

A connection URL is a URL. The password field is not exempt from URL rules, but every password generator on earth will eventually produce one that breaks the parse.

postgres://app:p@ssw0rd@db.internal:5432/app
             ^^^^^^^^      ^
             the real password   parsers split here

The parser sees the host as ssw0rd@db.internal. The fix is percent-encoding:

// encodeURIComponent, not string concatenation.
const url = `postgres://${user}:${encodeURIComponent(password)}@${host}:${port}/${database}`;

You need to encode at least @, /, #, ?, and :. Prefer the form that takes fields separately (user, password, host) over building a string by hand — that is what the pg config object is for.

This is the class of bug I built the database URL parser for: paste a connection string and it shows you exactly what each part parses to, flags an unencoded password, and never leaves your browser. If your string parses differently than you expect, everything downstream is guessing.

6. The dashboard handed you the wrong URL

Connection strings are generated by dashboards that do not know your deployment. Common mismatches:

  • Internal vs public host. The internal hostname works from inside the provider's network and not from your laptop, or the reverse.
  • Pooled vs direct port. See above. The pooled URL for app traffic, the direct URL for migrations.
  • Read replica host. Writes fail — sometimes hours later, on the one code path that writes.
  • Provider-specific query params. ?schema=public and ?connection_limit=5 are Prisma concepts. They do nothing for a raw pg pool, which is a silent difference between "I configured the pool" and "I configured nothing."
  • Credentials that rotate. The password in the string is now the old one, and the failures look random because only some instances restarted.

Treat the URL as versioned config per environment, and diff it against the provider's current recommendation instead of trusting the tab you copied from six months ago.

7. The 60-second preflight

Before you blame the query, look at the connections.

-- Are you near the ceiling right now?
select
  (select setting::int from pg_settings where name = 'max_connections') as max_connections,
  count(*) as open_connections
from pg_stat_activity;
 
-- Who is holding a connection and doing nothing useful?
select
  pid,
  usename,
  state,
  now() - state_change as in_state_for,
  left(query, 80) as last_query
from pg_stat_activity
where state = 'idle in transaction'
   or (state = 'idle' and now() - state_change > interval '5 minutes')
order by in_state_for desc;

If open_connections is close to max_connections, your problem is the pool, not the query. If you see rows idle in transaction, you have a leak, not a load problem. Both are cheaper to fix than adding indexes.

8. A pool config that behaves

Long-lived server (Express, Fastify, a worker):

import { Pool } from "pg";
 
// One pool per process, created once and shared. Never per request.
export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: Number(process.env.PG_POOL_MAX ?? 10),
  // Fail fast instead of hanging a request forever.
  connectionTimeoutMillis: 5_000,
  // Recycle idle clients so they do not sit on a slot.
  idleTimeoutMillis: 30_000,
  // Defense in depth; the role-level ALTER above is the real guardrail.
  statement_timeout: 10_000,
});
 
// An error on an idle client is a process-level event. Unhandled, it crashes Node.
pool.on("error", (err) => {
  console.error("idle postgres client error", err);
});
 
// Let in-flight queries finish before the process dies.
process.on("SIGTERM", async () => {
  await pool.end();
  process.exit(0);
});

Serverless function: do not copy the above. Keep the pool tiny and put a pooler in front:

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL, // points at the transaction pooler
  max: 1,
  connectionTimeoutMillis: 5_000,
  idleTimeoutMillis: 10_000,
});

And never log the URL. It contains a live credential. Strip it from error reports before they reach Sentry or your analytics — "redact the password" is a one-liner you will be glad you wrote.

When this does not apply

If you run a single long-lived process against a single Postgres with comfortable max_connections, most of this is background noise. You can leave the defaults. The moment any of these is true — serverless, horizontal scaling, a pooler in the string, or one Postgres shared by several services — the connection string becomes the most likely place your next outage starts.

The lesson I keep relearning: read the string. Set the timeout. Count the connections. It is fifteen minutes of work that prevents an afternoon of "the database is down," when the database was fine the whole time and your pool was not.

If you want this checked against a real app, I run a free 60-second backend audit over Node.js connection and shutdown patterns, and I fix backend bugs for a fixed price when the audit finds something you would rather hand off. The connection-string checks above are part of the same rule set as the production bugs I keep finding.

Gumroad