Why Does a Backend Need Database Connection Pooling?

Ever hit Too many connections while server CPU is still lounging at 30%? The problem is not your queries, it is how the application talks to the database. Meet connection pooling.

Read in Indonesian

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/sql in 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:

  1. TCP 3-way handshake: the exchange of SYN, SYN-ACK, and ACK packets over the network.
  2. TLS/SSL handshake: negotiation of the security certificate and encryption, when the connection is secured.
  3. Authentication and authorization: verification of the username, password, and permissions of the database user.
  4. 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:

  1. Initialization: when the backend first boots, the pool creates a set of initial connections, say 10, and keeps them alive.
  2. 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.
  3. Execution: the SQL query runs over that connection.
  4. 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.

Connection pooling can sit at the application code level or at an intermediary proxy level:

  1. Application-level pooling.
    • Python (SQLAlchemy): ships a built-in QueuePool that 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 the SetMaxOpenConns and SetMaxIdleConns settings.
  2. 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.

PYTHON
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. 👋

Inva

Writer

Get better at shipping code.

Join the other developers getting tech updates, tips, and relaxed tutorials in their inbox every week.

Stay in the loop

Tech updates, coding tips, and relaxed insights straight to your inbox every week.

No spam. Unsubscribe anytime.

Read next

See all

For developers who learn by struggling

© 2026 Inva.dev.