Skip to content
Manvir Heer
← Writing

Reducing PostgreSQL Connection Time from 329 ms to 2 ms

A practical guide to PgBouncer connection pooling

2025.10.15·8 min read
PostgreSQLPerformanceInfrastructure

The Problem

Our baseline benchmark recorded an average connection time of 329ms. Establishing database connections was a significant source of overhead in the platform.

A new PostgreSQL connection involves network setup and authentication. I investigated connection pooling to reduce how often the application needed to pay that cost.

Why Connection Pooling?

Connection pooling maintains a set of pre-established database connections that can be reused across requests. Instead of paying the connection overhead every time, you grab an existing connection from the pool, use it, and return it.

PgBouncer is a lightweight connection pooler for PostgreSQL. It sits between your application and database, managing connection lifecycle transparently.

The Implementation

Step 1: Baseline Measurement

Before optimizing anything, I needed numbers. My benchmarking work covered:

  • Connection time: How long to establish a new connection
  • Query execution: Time for actual database operations
  • Concurrent load: Performance under realistic multi-user scenarios
# Minimal connectivity check, not the full benchmark harness
for i in {1..100}; do
  time psql -h localhost -c "SELECT 1"
done

This small example times the complete client invocation and query; it does not isolate connection establishment. The recorded benchmark results below describe connection time and load testing separately.

Baseline results:

  • Average connection time: 329ms
  • p99 connection time: 412ms
  • Max concurrent connections before degradation: ~50

Step 2: PgBouncer Configuration

The key configuration decisions:

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5

# Authentication
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

Why transaction mode? In transaction pooling, connections are assigned per-transaction rather than per-session. This provides the best connection reuse for typical web applications where requests are short-lived.

Step 3: Application Changes

The application needed minimal changes—just point to PgBouncer instead of PostgreSQL directly:

// Before
const pool = new Pool({
  host: 'postgres.internal',
  port: 5432
})

// After
const pool = new Pool({
  host: 'pgbouncer.internal',
  port: 6432
})

The Results

After deployment:

Metric Before After Improvement
Connection time 329ms 2ms 99.4%
Concurrent capacity 50 280+ 458%
Throughput (qps) baseline +6.3% -
Complex queries baseline 13% faster -

The 2ms connection time is essentially just the network round-trip to PgBouncer. The actual PostgreSQL connection is already established and waiting.

Lessons Learned

1. Measure Before You Optimize

Without baseline numbers, I couldn't have quantified the improvement or justified the infrastructure change. The benchmarking script became a permanent part of our toolkit.

2. Transaction Mode Has Tradeoffs

Transaction pooling needs care around features that depend on a particular server session:

  • Protocol-level named prepared statements require a supported PgBouncer version and a nonzero max_prepared_statements setting. SQL-level PREPARE and EXECUTE have different limitations.
  • Session-level settings (SET commands)
  • Session-level advisory locks

See the PgBouncer configuration reference for the version-specific behavior. This is distinct from transaction-scoped advisory locks.

We had to audit our codebase for these patterns before deploying.

3. Documentation is Part of the Deliverable

I created runbooks for:

  • How to monitor PgBouncer (SHOW POOLS, SHOW STATS)
  • How to run the benchmark suite
  • Troubleshooting common issues

This ensures the next engineer can maintain and improve the system.

Conclusion

Connection pooling is one of those optimizations with an outsized impact-to-effort ratio. A few hours of configuration yielded a 99.4% latency reduction that improves every database operation in the system.

The key is approaching it systematically: measure first, understand the tradeoffs, and document everything for the engineers who come after you.

← All posts