Why Connections Are Expensive

Stand up PostgreSQL with a tight connection limit, watch it refuse clients at the wall, then add pgbouncer as a connection pool and watch thirty clients share five backend processes — all from first principles.

Most apps connect to Postgres and never think about it — until the database starts refusing connections at the worst moment. This course builds the mental model from scratch: we spin up a Postgres with max_connections=20, open more sessions than it allows, and capture the FATAL error first-hand. Then we add pgbouncer as a connection pool in the same docker-compose stack, route traffic through it, and use its admin console to watch thirty clients share five backend processes in real time. Every number in this course came off a real running stand.

What you'll build

• Explain why each PostgreSQL connection spawns an OS process and what that costs
• Hit the max_connections wall and read the error in pg_stat_activity
• Add pgbouncer to a docker-compose stack with correct ini and userlist
• Connect to a database transparently through pgbouncer on port 6433
• Read SHOW POOLS, SHOW CLIENTS, SHOW SERVERS and SHOW STATS in the admin console

Contents

1. A database with a deliberately low connection limit
2. Start it
3. Connect directly to PostgreSQL
4. The table we will fill and query
5. Create the table
6. Two hundred thousand events, repeatably
7. Load the rows
8. Reading the live configuration
9. Confirm the limit is twenty
10. Seeing connections as OS processes
11. One connection in the view
12. Twenty-five clients trying to connect at once
13. The wall
14. The survivors in pg_stat_activity
15. Twenty backends at the wall
16. The per-process memory cost
17. Four megabytes, multiplied by every connection
18. Connect through the pool
19. The pool is transparent
20. PostgreSQL answers through pgbouncer
21. Thirty clients hitting the pool at once
22. Thirty clients, zero rejections
23. Five backends served thirty clients
24. The pgbouncer admin console
25. The admin console command set
26. The pool state at a glance
27. The pool under load
28. All client connections in detail
29. The server-side connections pgbouncer holds
30. Aggregate throughput counters
31. What you built and what comes next