It is almost always the database

Connections run out before anything else

Postgres defaults to max_connections = 100, and each connection is a separate operating system process using several megabytes. This is not a setting to raise casually; a thousand connections mostly means a thousand processes competing to do nothing.

Now count what your application opens. Three containers, each with a pool of 20, is 60. Add a background worker, a migration job, your own psql session and a metrics exporter and you are near the limit while serving very little traffic.

Serverless makes this dramatically worse: every concurrent function instance opens its own connection, and there is no shared pool. A hundred concurrent invocations is a hundred connections, and the error is immediate and total:

FATAL: sorry, too many clients already

The fix is a pooler, not a bigger max_connections. PgBouncer is the classic; managed equivalents include Supabase’s pooler, Neon’s pooled endpoint, and Cloudflare Hyperdrive, which is what this project uses from Workers. A pooler holds a small number of real connections and multiplexes many clients over them.

One caveat that appears only under load, which is the worst time to learn it: in transaction pooling mode a client gets a different backend for each transaction. So session-level state (SET statements, session variables, LISTEN, temporary tables, and historically server-side prepared statements) does not survive between statements. Your ORM may need a flag. It works perfectly in development, where you have one client, and fails in production.

The missing index

A sequential scan over 1,000 rows is instantaneous. The same scan over 5,000,000 rows is seconds, and it reads the whole table into memory, evicting everything useful from the cache on the way.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 8412;

Read three things from the output: Seq Scan versus Index Scan, the actual time, and the gap between estimated and actual rows. A large gap means the planner’s statistics are wrong, which is what makes a query that was fast for a year suddenly slow.

Two rules that cover most cases. Index the columns you filter and join on. And for a composite index, column order matters: an index on (customer_id, created_at) serves a query filtering on customer_id alone, but an index on (created_at, customer_id) does not.

Indexes are not free: each one is written on every insert and update, and takes disk. Find the ones earning nothing:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes WHERE idx_scan = 0;

Find the expensive query, not the slow one

Enable pg_stat_statements and sort by total execution time:

SELECT calls, round(total_exec_time) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

The result is usually a surprise. The 900ms report somebody complained about runs four times a day. The 3ms query at the top runs 40,000 times an hour and is consuming most of your database. Optimise by total time and you fix the thing that is actually holding you back.

Two settings that contain the damage

ALTER ROLE app SET statement_timeout = '15s';
ALTER ROLE app SET idle_in_transaction_session_timeout = '30s';

The first stops one runaway query from occupying a connection for an hour. The second kills a client that opened a transaction and wandered off, which is the single most destructive thing a client can do, because an open transaction blocks VACUUM from cleaning up, so the table grows and grows and every query on it gets slower. Bloat from a forgotten transaction is a common cause of “the database got slow over the weekend”.

Read replicas are not a free win

A replica moves read load off the primary, and introduces replication lag: usually milliseconds, occasionally seconds under write pressure. Which means: a user posts a comment, the write goes to the primary, the page reloads from the replica, and the comment is not there.

The fix is to route reads that must be current back to the primary, per request. That is a real design decision, not a configuration flag, and it is why replicas belong later in a project’s life than most people put them.

The free path

All of the above is Postgres and free. PgBouncer is free. pgAdmin, DBeaver and psql are free. Neon and Supabase have free tiers that suspend when idle, so the first query after a quiet period takes around a second: fine for a side project, wrong for a checkout page. The paid tiers mostly sell you connections, uptime and backups, and by the time you need them you will know which.