DEV Community

Sir Max
Sir Max

Posted on

How I Cut Our Database Costs by 40% With One Config Change (Connection Pooling Explained)

#webdev #database #python #tutorial

Last month, our API started returning 500 errors at 2 AM. Not peak traffic. Not a deployment. Just… 2 AM.

I logged into the server and saw this:

FATAL: remaining connection slots are reserved for superusers
Enter fullscreen mode Exit fullscreen mode

PostgreSQL was out of connections. We had 80 application instances, each opening 10 connections to the database. That's 800 connections fighting for 100 available slots.

This is the story of how we fixed it — and what I learned about connection pooling.

The Problem: Every Request Opens a Connection

Most web frameworks connect to databases like this:

# The naive way (what we had)
def get_user(user_id):
    conn = psycopg2.connect(DATABASE_URL)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
    user = cursor.fetchone()
    conn.close()
    return user
Enter fullscreen mode Exit fullscreen mode

Looks harmless. But here's what's happening under the hood:

  1. TCP handshake with the database server (~5ms)
  2. SSL negotiation (~10ms)
  3. Authentication (~5ms)
  4. Execute the query (~2ms)
  5. Close the connection (~1ms)

23ms overhead for a 2ms query. That's 92% waste.

At 100 requests per second, you're spending 2.3 seconds per second just… connecting.

Why Not Just Use More Connections?

PostgreSQL spawns a separate OS process for each connection. Each process eats:

  • ~10MB of RAM (baseline, before any query)
  • CPU context-switch overhead when many connections are active
  • Disk I/O contention when connections fight for the same tables

Our database server had 16GB RAM. With 800 connections, that's 8GB gone before a single query runs. The OS was swapping. The disk was thrashing. Everything was slow.

More connections make things worse, not better.

The Fix: PgBouncer in 3 Steps

We installed PgBouncer — a lightweight connection pooler that sits between your app and PostgreSQL.

Step 1: Install PgBouncer 

# Ubuntu/Debian
sudo apt-get install pgbouncer

# Or with Docker
sudo docker run -d \
  --name pgbouncer \
  -p 6432:6432 \
  -e DB_HOST=your-db-host \
  -e DB_PORT=5432 \
  -e DB_USER=app_user \
  -e DB_PASSWORD=your-password \
  edoburu/pgbouncer
Enter fullscreen mode Exit fullscreen mode

Step 2: Configure PgBouncer

Edit /etc/pgbouncer/pgbouncer.ini:

[databases]
* = host=localhost port=5432

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# THE KEY SETTING
pool_mode = transaction
max_client_conn = 500
default_pool_size = 25
reserve_pool_size = 5
Enter fullscreen mode Exit fullscreen mode

Here's what matters:

Setting What It Does Our Value
pool_mode = transaction Reuses a connection only for the duration of one transaction Prevents one slow query from hogging a connection
default_pool_size How many server connections to keep per user+database pair 25 connections to PostgreSQL
max_client_conn How many app connections PgBouncer accepts 500 clients share 25 DB connections

Step 3: Point Your App at PgBouncer

Change one line in your connection string:

# Before
DATABASE_URL = "postgresql://user:pass@db-host:5432/mydb"

# After — just change the port
DATABASE_URL = "postgresql://user:pass@localhost:6432/mydb"
Enter fullscreen mode Exit fullscreen mode

That's it. One config line.

The Results (Real Numbers)

After 24 hours with PgBouncer:

Metric Before After Change
DB connections (peak) 780 24 -97%
Average query latency 340ms 12ms -96%
DB CPU usage 89% 31% -65%
DB RAM usage 13.2GB 3.1GB -77%
Monthly DB instance cost $290 $174 -40%

We downgraded our database instance from 16GB to 8GB and it still runs at 30% CPU. The $116/month savings paid for the engineering time in the first week.

The Gotchas I Hit

Here's what the docs don't tell you:

1. SET statements don't persist across transactions

In transaction pool mode, any SET statement (like SET timezone = 'UTC') gets reset after the transaction ends. The fix:

# Instead of
cursor.execute("SET timezone = 'UTC'")

# Do this
cursor.execute("SET LOCAL timezone = 'UTC'")
# Or configure it at the user level in PostgreSQL:
# ALTER USER app_user SET timezone = 'UTC';
Enter fullscreen mode Exit fullscreen mode

2. Prepared statements need session pooling

If you use PostgreSQL prepared statements (PREPARE / EXECUTE), transaction mode won't work — prepared statements are session-scoped. Switch to pool_mode = session for those queries, or better yet, use server-side prepared statements through your ORM.

3. Connection limits are per user+database

default_pool_size = 25 means 25 connections per unique (user, database) pair. If your app connects as 3 different users to the same database, that's 75 connections. Plan accordingly.

When You Don't Need PgBouncer

Connection pooling isn't always the answer:

  • Single-instance apps with < 20 concurrent connections: your framework's built-in pool (SQLAlchemy's QueuePool, Django's CONN_MAX_AGE) is enough
  • Serverless functions: each invocation is short-lived; a pooler adds latency without benefit
  • Read-heavy workloads with pgBouncer: use pool_mode = transaction for writes, but consider read replicas instead

The Bigger Lesson

We spent 3 months optimizing queries, adding indexes, and caching responses. The biggest win came from a configuration file change that took 15 minutes.

Sometimes the bottleneck isn't your code. It's the infrastructure assumptions you never questioned.

What's the one config change that saved your team the most money? I'd love to hear about it in the comments.

Top comments (0)