Most people meet connection pooling the hard way: the app runs fine in testing, then falls over in production with FATAL: sorry, too many clients already. The database didn't run out of CPU or disk, it ran out of connections. Pooling is the fix, and on WattleDB it's already set up for you. This explains what's happening and how to use it.

Which connection string, at a glance

  • DATABASE_URL, pooled (PgBouncer, transaction mode). Your app, your API routes, serverless functions.
  • DIRECT_URL, direct session. Migrations, psql, long-running jobs, anything using session state.
  • Both are TLS-encrypted Australian endpoints, keep sslmode=require.

Connections are expensive

In PostgreSQL, every client connection is backed by its own server process with its own memory. A few dozen is fine; a few thousand is not. Each database has a max_connections ceiling, and once you hit it, new connections are refused, even though the server has plenty of CPU left. The problem isn't load, it's concurrency of connections.

What a pooler does

A connection pooler like PgBouncer sits between your app and Postgres. It keeps a small pool of real database connections open and lends them out to incoming clients. In transaction mode, what WattleDB's pooled endpoint uses, a real connection is assigned only for the duration of a single transaction, then returned to the pool. Thousands of clients can share a handful of backend connections, because at any instant only a few are mid-transaction.

That's why the pooled DATABASE_URL is the right default for your application: it absorbs spikes that would otherwise exhaust the database.

When you need the direct connection

Transaction-mode pooling has one trade-off: because a connection isn't dedicated to you between transactions, anything that relies on session state won't work through the pooler. That includes:

  • Schema migrations (they create objects and may hold long transactions).
  • Prepared statements that persist across calls, and LISTEN/NOTIFY.
  • Session-level SET commands, advisory locks, and bulk imports.

For those, use DIRECT_URL, a direct session connection that bypasses the pooler. The usual pattern: your app's runtime uses DATABASE_URL, and your migration tool uses DIRECT_URL.

The serverless trap

Serverless and edge functions make the connection problem worse: each invocation can spin up its own instance, and each instance wants its own connection. A burst of traffic becomes a burst of connections, and you hit the ceiling fast. This is exactly what transaction-mode pooling is for, point your serverless functions at DATABASE_URL and the pool soaks up the churn.

One gotcha: with some drivers you must disable prepared statements when going through a transaction pooler (for example, prepare: false in postgres.js). Prepared statements assume a stable session, which transaction mode doesn't guarantee.

Rules of thumb

  • App, API and serverless code → DATABASE_URL.
  • Migrations, psql, long jobs, LISTEN/NOTIFY → DIRECT_URL.
  • Seeing prepared-statement errors through the pooler? Disable prepared statements on the pooled connection.
  • Both strings are in the console under Connection, and both stay inside Australia.