𝗣𝗼𝘀𝘁𝗴𝗿𝗲𝗦𝗤𝗟 𝗖𝗼𝗻𝗻𝗲𝗰𝘁𝗶𝗼𝗻 𝗣𝗼𝗼𝗹𝗶𝗻𝗴 𝗘𝘅𝗽𝗹𝗮𝗶𝗻𝗲𝗱
Opening a new database connection for every query is slow. It costs your backend too much memory and time.
Why connections cost you:
- Postgres starts a new OS process for each connection.
- Each process uses 5 to 10 MB of RAM.
- TCP and SSL handshakes take 20 to 100 milliseconds.
Without a pool, a request with three queries takes 86ms. With a pool, it takes 6ms. Setup waste is 93 percent of your time.
High connection counts hurt your server:
- 500 connections use 5 GB of RAM.
- Too many processes cause CPU context switching.
- Your database spends more time managing connections than running queries.
The solution is connection pooling. A pool is a cache of open connections. Your app borrows a connection, runs a query, and returns it.
PgBouncer is the industry standard. It sits between your app and Postgres. It decouples app requests from database connections.
Choose your pool mode carefully:
- Session mode: One connection per client. Safe but slow.
- Transaction mode: One connection per transaction. High scale.
Sizing your pool: Do not set your pool too high. Formula: (CPU cores x 2) + disk spindles. Too many connections reduce throughput.
How to monitor: Connect to PgBouncer and run SHOW POOLS. Look at cl_waiting. If this number is above zero, your pool is too small.