Fix Laravel PostgreSQL Timeouts with PgBouncer Pooling

Avatar
M

Maksudur Rahman

Software Engineer•
0Views
5mRead
0Reactions

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=secret

PgBouncer 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 = 20

Session 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
fi

Load 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

wait

Common 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.txt file 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!

Related Engineering Guides

Maksudur Rahman
M
Written by•Software Engineer

Maksudur Rahman

Senior Developer Advocate & System Lead Moderator.

React to this article

Recommended Articles

View all articles