Have you ever seen your backend suddenly throw FATAL: remaining connection slots are reserved for non-replication superuser connections or Too many connections right when traffic peaks?
Meanwhile the application server CPU sits comfortably at 30%, and the database queries being run are simple. How does it fall over?
This classic problem is usually not about the query. It is about how your application talks to the database: open a new connection for every incoming HTTP request, then close it the moment the query finishes. Wasteful, right? 😅
Key takeaways
- Opening a fresh database connection on every request is expensive: TCP handshake, TLS negotiation, authentication, and memory allocation on the database server.
- A connection pool keeps a set of live, authenticated connections ready to be borrowed and returned by workers.
- The pool prevents too many connections errors and keeps query latency steady when traffic spikes.
- The options live at the application level (QueuePool in SQLAlchemy, HikariCP,
database/sqlin Go) and at the infrastructure level (PgBouncer, RDS Proxy).
The Hidden Cost of a Single Connection
For many junior developers, calling db.connect() feels like an instant operation with no cost. Behind the scenes, every time an application opens a new connection to a relational database like PostgreSQL or MySQL, a long series of heavy steps has to run:
- TCP 3-way handshake: the exchange of SYN, SYN-ACK, and ACK packets over the network.
- TLS/SSL handshake: negotiation of the security certificate and encryption, when the connection is secured.
- Authentication and authorization: verification of the username, password, and permissions of the database user.
- Memory allocation on the server: in PostgreSQL, every new connection spawns its own backend process, which consumes server RAM specifically for it.
If your application serves 500 requests per second and each one opens a new connection, the database server runs out of time just opening and closing connections, not executing the queries.
To picture it, imagine commuting to the office.
- Without a connection pool: for every passenger who wants to leave, you order a new car from the factory, sort out the paperwork, drive to the destination, then crush the car once the passenger steps out. A waste of time and money.
- With a connection pool: the garage already holds a fleet of 10 taxis ready to go. A passenger arrives, one taxi leaves. When the trip is done, the driver returns to the garage and the car is ready for the next passenger.
A connection pool works exactly like those taxis in the garage. Connections are created and authenticated once at the start (warm connections), then kept in the pool to be borrowed in turn by workers.
How a Connection Pool Works in a Backend
The life cycle of a connection in the pool has roughly four stages:
- Initialization: when the backend first boots, the pool creates a set of initial connections, say 10, and keeps them alive.
- Acquire: when an endpoint needs to read data, the application asks the pool for one idle connection. The operation is nearly instant, under 1 millisecond.
- Execution: the SQL query runs over that connection.
- Release: once finished, the connection is not shut down. It goes back to idle status inside the pool.
When every connection is busy serving a query, new requests wait briefly in the queue pool, instead of forcing the database to open a new connection that risks bringing the server down.
Popular Tools, at the Application and Infrastructure Level
Connection pooling can sit at the application code level or at an intermediary proxy level:
- Application-level pooling.
- Python (SQLAlchemy): ships a built-in
QueuePoolthat manages pool size (pool_size) and burst tolerance (max_overflow). - Java/Kotlin (HikariCP): the gold standard connection pool in the JVM ecosystem, known for speed and minimal overhead.
- Go (
database/sql): the Go standard library already includes pooling through theSetMaxOpenConnsandSetMaxIdleConnssettings.
- Python (SQLAlchemy): ships a built-in
- Infrastructure-level pooling.
- PgBouncer: a lightweight PostgreSQL proxy that can manage tens of thousands of client connections and funnel them into a few dozen real database connections through transaction pooling.
- AWS RDS Proxy / GCP Cloud SQL Auth Proxy: managed cloud services that handle pooling and failover automatically for serverless architectures like AWS Lambda.
A Configuration Example in SQLAlchemy (FastAPI)
Here is an example of a balanced connection pool size in a SQLAlchemy-based Python application.
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
DATABASE_URL = "postgresql://user:***@localhost:5432/my_database"
# Engine configuration with an optimal connection pool
engine = create_engine(
DATABASE_URL,
pool_size=10, # Jumlah koneksi persisten yang selalu aktif
max_overflow=20, # Tambahan koneksi sementara saat ada traffic spike
pool_timeout=30, # Maksimal waktu tunggu pinjam koneksi (detik)
pool_recycle=1800, # Refresh koneksi setiap 30 menit, cegah stale connection
pool_pre_ping=True # Cek koneksi masih hidup sebelum dipakai
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
The pool_pre_ping=True parameter is very useful in cloud environments: a connection dropped by a firewall or a database restart gets detected and rebuilt automatically, without triggering a 500 error for the user.
One thing to remember: if your application runs on many workers or many replicas, the pool size multiplies. Ten workers with pool_size=10 each means your database must be ready to hold 100 connections. Also account for the max_connections limit on the PostgreSQL side.
So, What Pool Size Is Right?
This is the question that comes up most, and the answer is always "it depends". But there is a reasonable starting point.
Start small, say pool_size=5 to 10 per worker, then increase gradually while monitoring active connections in the database and endpoint latency. What you want to avoid is an oversized pool: idle connections still eat memory on the database server, and if the total crosses max_connections, the error is the same, only slower to appear.
Ignoring database connection management is like postponing a time bomb. With a well-configured connection pool, your application escapes connection exhaustion and can process thousands of transactions with far steadier response times.
Happy databasing, and may your connections always be available. 👋