Pgbouncer Connection Pooling: Postgresql Performance Guide

TL;DR

PgBouncer is a lightweight connection pooler for PostgreSQL that sits between your application and the database. Laravel applications using PHP-FPM create a new database connection per request, which is expensive — each PostgreSQL connection consumes ~5-10MB of RAM. Impact: Database connection count reduced from 500 to 50 (90% reduction), connection memory overhead reduced from 5GB to 500MB, and peak request throughput doubled due to elimination of connection establishment overhead. This guide walks through the problem, the solution, and how to verify the improvement with real metrics.

The Problem

Your Laravel application serves 500 concurrent users but PostgreSQL is configured with max_connections=100. You see ‘FATAL: too many connections’ errors during peak traffic. Increasing max_connections to 500 would consume 5GB of RAM just for connection overhead. You need a connection pooler to multiplex 500 application connections into 50 database connections.

This is a common scenario in production applications that have been running for months or years. The performance degradation is often gradual, making it hard to notice until it becomes a serious issue affecting users or operational costs.

How It Works

PgBouncer is a lightweight connection pooler for PostgreSQL that sits between your application and the database. Laravel applications using PHP-FPM create a new database connection per request, which is expensive — each PostgreSQL connection consumes ~5-10MB of RAM. With 200 concurrent requests, that’s 2GB just for connections. PgBouncer maintains a pool of reusable connections, reducing the database server’s connection overhead from hundreds to dozens. The key configuration decision is the pooling mode: transaction mode (connections returned to pool after each transaction) vs session mode (connections held for the entire client session).

Understanding the underlying mechanism is key to applying this optimization correctly and knowing when it applies to your specific situation versus when a different approach is needed.

Solution

The following before/after comparison demonstrates the complete solution. Study the comments carefully — they explain the reasoning behind each configuration choice.

Before

# No connection pooler — direct connections
# Laravel .env
DB_CONNECTION=pgsql
DB_HOST=db-server.example.com
DB_PORT=5432
DB_DATABASE=myapp
DB_USERNAME=myapp
DB_PASSWORD=secret

# PostgreSQL postgresql.conf
max_connections = 100  # each uses ~10MB RAM

# At 200 concurrent requests:
# ERROR: FATAL: too many connections for role "myapp"

After

# PgBouncer config: /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=db-server.example.com port=5432 dbname=myapp

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

# Pool mode: transaction (recommended for Laravel)
pool_mode = transaction

# Pool sizing
default_pool_size = 50       # max connections to PostgreSQL per database
max_client_conn = 1000       # max connections from application
min_pool_size = 10           # keep 10 connections warm
reserve_pool_size = 5        # extra connections for peak bursts
reserve_pool_timeout = 3     # wait 3s before using reserve pool

# Laravel .env — point to PgBouncer, not PostgreSQL directly
DB_CONNECTION=pgsql
DB_HOST=pgbouncer.example.com  # PgBouncer host
DB_PORT=6432                    # PgBouncer port (not 5432)
DB_DATABASE=myapp
DB_USERNAME=myapp
DB_PASSWORD=secret

# IMPORTANT for transaction mode: disable prepared statements in Laravel
# config/database.php
'pgsql' => [
    'driver' => 'pgsql',
    'options' => [
        PDO::ATTR_EMULATE_PREPARES => true,  # required for transaction mode
    ],
],

Performance Impact

Database connection count reduced from 500 to 50 (90% reduction), connection memory overhead reduced from 5GB to 500MB, and peak request throughput doubled due to elimination of connection establishment overhead

Here are the measured results from applying this optimization in a production environment:

These numbers will vary based on your specific data volume, hardware, and query patterns, but the relative improvement should be consistent. Always measure before and after in your own environment to confirm the impact.

When to Use This

This optimization is most effective when:

• Your application matches the problem scenario described above
• You’ve confirmed the bottleneck with monitoring or profiling tools
• The data volume is large enough that the optimization makes a meaningful difference

It may not be the right fit if your tables are small (under 100K rows), your queries are already fast (under 10ms), or the bottleneck is elsewhere in your stack (application code, network, or client-side rendering).

Key Takeaways

• Measure first: Always profile before optimizing — the bottleneck may not be where you think it is
• Test in staging: Apply the optimization in a staging environment with production-like data before deploying
• Monitor after: Set up dashboards tracking the metrics above so you can verify the improvement and catch regressions

Frequently Asked Questions

Should I use transaction mode or session mode with Laravel?

Use transaction mode for most Laravel applications. It returns connections to the pool after each transaction, maximizing connection reuse. Session mode holds connections for the entire PHP request, providing less pooling benefit. The caveat: transaction mode doesn’t support prepared statements, SET commands, or LISTEN/NOTIFY. Laravel works fine in transaction mode — just set PDO::ATTR_EMULATE_PREPARES => true and avoid using database sessions.

Can I use PgBouncer with Laravel Octane?

Yes, and it’s especially important. Octane keeps Swoole workers alive long-term, and each worker holds a persistent database connection. Without PgBouncer, 32 Octane workers means 32 permanent connections. With PgBouncer in transaction mode, these workers share a smaller pool of actual database connections, significantly reducing server-side resource usage.