The Desi Architect
Notes / Docker Sandboxes / Database Scaling: pgBouncer Session vs Transaction Pooling Modes
Docker Sandboxes · Tier 2: Data & Scale · Lab 72

Database Scaling: pgBouncer Session vs Transaction Pooling Modes

PostgreSQL forks a dedicated operating system process for every client connection, consuming 5 to 10 megabytes of memory and triggering severe CPU context switching above a few hundred concurrent connections. Watch pgBouncer session pooling starve when 10 idle clients hold connections hostage, scale to 100 concurrent clients across only 5 physical database connections using transaction pooling, and prevent dangerous session variable state leakage using SET LOCAL.

Time25 minutes
StackPostgreSQL 16Ports 18390, 18391, 18392
MemoryAbout 120 MB
Before thisLab 04SQL Basics with PostgreSQL
Download lab (.zip) View steps
Verified runWindows, Mac, LinuxBash and PowerShell
  1. 1PrepareWhat you will learn
  2. 2PredictGuess before you run
  3. 3RunStep by step
  4. 4ObserveWhat you should see
  5. 5Break itChange one thing
  6. 6Clean upLeave nothing running
On this page
  1. 00 / What You Will Learn
  2. 01 / Why It Matters
  3. 02 / What We Are Building
  4. 03 / Words You Need
  5. 04 / Predict First
  6. 05 / Step by Step
  7. 06 / What You Should See
  8. 07 / Look Inside
  9. 08 / Break It, Fix It
  10. 09 / In Real Projects
  11. 10 / Interview Corner
  12. 11 / Clean Up

00 /What You Will Learn

By the end you will be able to
  • Explain PostgreSQL process-per-connection architecture and why direct connections degrade database performance under high client counts.
  • Reproduce session pool starvation: observe how idle client connections lock physical backend connections and block new queries.
  • Multiplex hundreds of concurrent client connections over a tiny pool of physical connections using transaction pooling.
  • Identify the transaction pooling caveat: why session variables, temporary tables, and prepared statements behave differently when connections recycle after every COMMIT.
  • Apply SET LOCAL to safely scope configuration parameters to a single transaction without leaking state across tenants.

Before you start

Complete Lab 01 (Docker Compose Basics) and Lab 13 (Database Connection Pool Exhaustion) first. You should understand database connection limits and transaction boundaries (BEGIN, COMMIT, ROLLBACK). Docker Desktop must be running.