Fix Laravel PostgreSQL Timeouts with PgBouncer Pooling
Your Laravel app is hitting PostgreSQL's max_connections limit, causing request timeouts and degraded performance under load. PgBouncer connection pooling solves this by reusing database connections efficiently. Here's how to set it up with Laravel, PostgreSQL, and Docker.
TL;DR / Quick Summary
Configure PgBouncer as a connection pooler between Laravel and PostgreSQL
Adjust Laravel's database config to point to PgBouncer instead of PostgreSQL directly
Choose between session or transaction pooling based on your app's needs
Verify pool sizing with a load test
Prerequisites
Before starting, ensure you have:
Docker and Docker Compose installed
Laravel 11.x application
PostgreSQL 16
PgBouncer 1.22
Docker Compose Setup
Here's a complete docker-compose.yml configuration:
version: '3.8'
services:
postgres:
image: postgres:16
environment:
POSTGRES_DB: laravel
POSTGRES_USER: laravel
POSTGRES_PASSWORD: secret
volumes:
- postgres_data:/var/lib/postgresql/data
healthcheck:
test: ["CMD-SHELL", "pg_isready -U laravel"]
interval: 5s
timeout: 5s
retries: 5
pgbouncer:
image: edoburu/pgbouncer:1.22.0
environment:
DB_HOST: postgres
DB_PORT: 5432
DB_USER: laravel
DB_PASSWORD: secret
POOL_MODE: transaction
MAX_CLIENT_CONN: 100
DEFAULT_POOL_SIZE: 20
ports:
- "6432:5432"
depends_on:
postgres:
condition: service_healthy
volumes:
postgres_data:Configure Laravel Database
Update your Laravel .env file to point to PgBouncer instead of PostgreSQL directly:
DB_CONNECTION=pgsql
DB_HOST=pgbouncer
DB_PORT=5432
DB_DATABASE=laravel
DB_USERNAME=laravel
DB_PASSWORD=secretPgBouncer Configuration
Create a pgbouncer.ini file with the following configuration:
[databases]
laravel = host=postgres port=5432 dbname=laravel
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 5432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
server_reset_query = DISCARD ALL
max_client_conn = 100
default_pool_size = 20Session vs Transaction Pooling
Choose between session or transaction pooling based on your app's needs:
Pooling Mode | Pros | Cons |
|---|---|---|
Session | Simple to configure | Less efficient for short transactions |
Transaction | Better performance for short transactions | More complex configuration |
Health Check Script
Create a health check script to monitor PgBouncer:
#!/bin/bash
PG_BOUNCER_HOST=localhost
PG_BOUNCER_PORT=6432
if nc -z $PG_BOUNCER_HOST $PG_BOUNCER_PORT; then
echo "PgBouncer is running"
exit 0
else
echo "PgBouncer is not running"
exit 1
fiLoad Test Script
Create a load test script to verify pool sizing:
#!/bin/bash
for i in {1..100}
do
curl -s http://localhost/api/endpoint > /dev/null &
done
waitCommon Pitfalls & Troubleshooting
Connection Leaks: Ensure your Laravel app properly closes database connections.
Pool Sizing: Start with conservative pool sizes and adjust based on load testing.
Authentication Issues: Verify the
userlist.txtfile contains the correct credentials.
FAQ
How do I monitor PgBouncer performance?
Use the SHOW STATS command in the PgBouncer admin console or check the log file specified in the configuration.
Can I use PgBouncer with Laravel queues?
Yes, PgBouncer works well with Laravel queues. Just ensure your queue workers properly handle connection management.
How do I handle connection timeouts with PgBouncer?
Adjust the server_lifetime and server_idle_timeout settings in the PgBouncer configuration to match your app's needs.
What's the best way to handle database migrations with PgBouncer?
Run migrations directly against the PostgreSQL server, bypassing PgBouncer, to avoid connection issues.
Connect with KoderSolution 🚀
Master modern software engineering, scalable architectures, and advanced backend systems with KoderSolution:
🌐 Official Website: Read more architectural deep-dives and tutorials on kodersolution.com
📺 YouTube Channel: Subscribe to @kodersolution on YouTube for concise, animated system design breakdowns and shorts.
📱 Facebook Page: Follow our official Facebook Page for daily development tips, architecture discussions, and community updates.
💬 What's your setup? How do you handle connection pooling in your Laravel production environment? What strategies do you use to monitor and adjust pool sizes? Share your thoughts and join the discussion below!