{"course":{"slug":"cloud-and-shipping","title":"Shipping It","url":"https://addaly.com/learn/cloud-and-shipping","level":"intermediate","track":"build"},"lesson":{"slug":"the-database-is-the-bottleneck","title":"It is almost always the database","url":"https://addaly.com/learn/cloud-and-shipping/the-database-is-the-bottleneck","position":34,"of":74,"minutes":10,"answer":"The first ceiling is nearly always connections and the second is one missing index, and both are found with two queries you can run today.","keyIdea":"The first ceiling is nearly always connections and the second is one missing index, and both are found with two queries you can run today."},"body":{"format":"markdown","text":"## Connections run out before anything else\n\nPostgres 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.\n\nNow 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.\n\nServerless 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:\n\n```\nFATAL: sorry, too many clients already\n```\n\n**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.\n\nOne 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.\n\n## The missing index\n\nA 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.\n\n```sql\nEXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 8412;\n```\n\nRead 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.\n\nTwo 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.\n\nIndexes are not free: each one is written on every insert and update, and takes disk. Find the ones earning nothing:\n\n```sql\nSELECT relname, indexrelname, idx_scan\nFROM pg_stat_user_indexes WHERE idx_scan = 0;\n```\n\n## Find the expensive query, not the slow one\n\nEnable `pg_stat_statements` and sort by total execution time:\n\n```sql\nSELECT calls, round(total_exec_time) AS total_ms,\n       round(mean_exec_time::numeric, 2) AS mean_ms, query\nFROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;\n```\n\nThe 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.\n\n## Two settings that contain the damage\n\n```sql\nALTER ROLE app SET statement_timeout = '15s';\nALTER ROLE app SET idle_in_transaction_session_timeout = '30s';\n```\n\nThe 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\".\n\n## Read replicas are not a free win\n\nA 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.\n\nThe 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.\n\n## The free path\n\nAll 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."},"check":{"question":"Your API runs as serverless functions. Under a traffic spike, requests start failing with \"too many clients already\" from Postgres, even though database CPU is low. What is the right first move?","options":[{"text":"Increase `max_connections` on the database to match peak concurrency","correct":false,"tempting":"The instinct everyone has, and it trades one problem for a worse one: each connection is a process with real memory cost, and a thousand of them degrade the database itself."},{"text":"Put a connection pooler in front of the database so many function instances share a small number of real connections","correct":true},{"text":"Add a read replica so connections are spread across two databases","correct":false,"tempting":"Halves the symptom at best, doubles the infrastructure, and does nothing about a model where concurrency and connections are one-to-one."},{"text":"Reduce the pool size configured inside each function instance","correct":false,"tempting":"Sounds right, but each serverless instance typically holds a single connection already; the count scales with instances, not with per-instance pool settings."}]},"prev":{"slug":"scaling-out-and-the-state-problem","title":"The day you run two copies","url":"https://addaly.com/learn/cloud-and-shipping/scaling-out-and-the-state-problem"},"next":{"slug":"queues-and-background-work","title":"Work that does not belong in a request","url":"https://addaly.com/learn/cloud-and-shipping/queues-and-background-work"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'It is almost always the database', in Shipping It. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/cloud-and-shipping/the-database-is-the-bottleneck (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"It is almost always the database","courseTitle":"Shipping It","module":{"index":4,"slug":"load-cost-and-abuse","title":"Load, cost and the traffic that is not customers"},"answer":null,"keyIdea":"The first ceiling is nearly always connections and the second is one missing index, and both are found with two queries you can run today.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"connections-run-out-before-anything-else","runs":[{"text":"Connections run out before anything else"}]},{"t":"p","runs":[{"text":"Postgres defaults to "},{"text":"max_connections = 100","code":true},{"text":", 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."}]},{"t":"p","runs":[{"text":"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 "},{"text":"psql","code":true},{"text":" session and a metrics exporter and you are near the limit while serving very little traffic."}]},{"t":"p","runs":[{"text":"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:"}]},{"t":"code","lang":"","label":"Text","text":"FATAL: sorry, too many clients already"},{"t":"p","runs":[{"text":"The fix is a pooler","b":true},{"text":", not a bigger "},{"text":"max_connections","code":true},{"text":". 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."}]},{"t":"p","runs":[{"text":"One caveat that appears only under load, which is the worst time to learn it: in "},{"text":"transaction pooling","b":true},{"text":" mode a client gets a different backend for each transaction. So session-level state ("},{"text":"SET","code":true},{"text":" statements, session variables, "},{"text":"LISTEN","code":true},{"text":", 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."}]},{"t":"h2","id":"the-missing-index","runs":[{"text":"The missing index"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"code","lang":"sql","label":"SQL","text":"EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 8412;"},{"t":"p","runs":[{"text":"Read three things from the output: "},{"text":"Seq Scan","code":true},{"text":" versus "},{"text":"Index Scan","code":true},{"text":", 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."}]},{"t":"p","runs":[{"text":"Two rules that cover most cases. Index the columns you filter and join on. And for a composite index, "},{"text":"column order matters","b":true},{"text":": an index on "},{"text":"(customer_id, created_at)","code":true},{"text":" serves a query filtering on "},{"text":"customer_id","code":true},{"text":" alone, but an index on "},{"text":"(created_at, customer_id)","code":true},{"text":" does not."}]},{"t":"p","runs":[{"text":"Indexes are not free: each one is written on every insert and update, and takes disk. Find the ones earning nothing:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT relname, indexrelname, idx_scan\nFROM pg_stat_user_indexes WHERE idx_scan = 0;"},{"t":"h2","id":"find-the-expensive-query-not-the-slow-one","runs":[{"text":"Find the expensive query, not the slow one"}]},{"t":"p","runs":[{"text":"Enable "},{"text":"pg_stat_statements","code":true},{"text":" and sort by total execution time:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT calls, round(total_exec_time) AS total_ms,\n       round(mean_exec_time::numeric, 2) AS mean_ms, query\nFROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;"},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"two-settings-that-contain-the-damage","runs":[{"text":"Two settings that contain the damage"}]},{"t":"code","lang":"sql","label":"SQL","text":"ALTER ROLE app SET statement_timeout = '15s';\nALTER ROLE app SET idle_in_transaction_session_timeout = '30s';"},{"t":"p","runs":[{"text":"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 "},{"text":"VACUUM","code":true},{"text":" 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”."}]},{"t":"h2","id":"read-replicas-are-not-a-free-win","runs":[{"text":"Read replicas are not a free win"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-free-path","runs":[{"text":"The free path"}],"role":"free"},{"t":"p","runs":[{"text":"All of the above is Postgres and free. PgBouncer is free. "},{"text":"pgAdmin","b":true},{"text":", "},{"text":"DBeaver","b":true},{"text":" and "},{"text":"psql","code":true},{"text":" 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."}]}],"check":{"question":[{"text":"Your API runs as serverless functions. Under a traffic spike, requests start failing with “too many clients already” from Postgres, even though database CPU is low. What is the right first move?"}],"options":[{"text":[{"text":"Increase "},{"text":"max_connections","code":true},{"text":" on the database to match peak concurrency"}],"correct":false,"tempting":[{"text":"The instinct everyone has, and it trades one problem for a worse one: each connection is a process with real memory cost, and a thousand of them degrade the database itself."}]},{"text":[{"text":"Put a connection pooler in front of the database so many function instances share a small number of real connections"}],"correct":true,"tempting":null},{"text":[{"text":"Add a read replica so connections are spread across two databases"}],"correct":false,"tempting":[{"text":"Halves the symptom at best, doubles the infrastructure, and does nothing about a model where concurrency and connections are one-to-one."}]},{"text":[{"text":"Reduce the pool size configured inside each function instance"}],"correct":false,"tempting":[{"text":"Sounds right, but each serverless instance typically holds a single connection already; the count scales with instances, not with per-instance pool settings."}]}],"order":[3,2,1,0],"why":null},"prev":{"slug":"scaling-out-and-the-state-problem","title":"The day you run two copies","minutes":9},"next":{"slug":"queues-and-background-work","title":"Work that does not belong in a request","minutes":10}}}