Configuración de PostgreSQL con PgBouncer para Aplicaciones de Alto Tráfico

Introducción

Cuando las bases de datos PostgreSQL enfrentan volúmenes altos de conexiones, el rendimiento puede degradarse rápidamente. Cada conexión a la base de datos consume recursos del sistema, y PostgreSQL tiene límites prácticos en conexiones concurrentes. PgBouncer, un pooler de conexiones ligero, soluciona este problema al mantener un pool de conexiones a la base de datos y distribuirlas eficientemente entre las aplicaciones cliente.

Por qué Importa el Agrupamiento de Conexiones

El Problema de las Conexiones

• Superficie de recursos: Cada proceso posterior de PostgreSQL consume entre 5 y 10 MB de memoria
• Límites de Conexiones: La configuración predeterminada max_connections suele ser de 100‑200
• Costo de Inicio: Crear nuevas conexiones consume entre 1 y 5 ms cada una
• Cambio de Contexto: Demasiados procesos provocan chutes de CPU

Beneficios de PgBouncer

• Reduce la cantidad de conexiones a la base de datos en un 10‑100 x
• Permite miles de conexiones de clientes con bajo costo operativo
• Proporciona cola de conexiones durante cargas pico
• Soporta varios modos de agrupamiento para distintos casos de uso

Instalación y Configuración Básica

Instalando PgBouncer

En Ubuntu/Debian:

sudo apt update
sudo apt install pgbouncer

En CentOS/RHEL:

sudo yum install pgbouncer

En macOS:

brew install pgbouncer

Estructura de Directorios

/etc/pgbouncer/
  ├── pgbouncer.ini        # Configuración principal
  └── userlist.txt         # Credenciales de autenticación

Configuración del Archivo de Configuración

Configuración Básica de pgbouncer.ini

[databases]
; database_name = host=hostname port=5432 dbname=actual_db
myapp = host=localhost port=5432 dbname=production_db

[pgbouncer]
; Modo de agrupamiento de conexiones
pool_mode = transaction

; Tamaño máximo de conexiones
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

; Redes
listen_addr = 0.0.0.0
listen_port = 6432

; Autenticación
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Registro
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Rendimiento
max_prepared_statements = 0

Comprendiendo los Modos de Agrupamiento

1. Agrupamiento de Sesión (pool_mode = session)

• Comportamiento: Una conexión se asigna al cliente durante toda la sesión
• Caso de Uso: Aplicaciones que usan tablas temporales, sentencias preparadas
• Eficiencia: Baja (relación 1:1 de conexiones)

pool_mode = session

2. Agrupamiento de Transacción (pool_mode = transaction) – Recomendado

• Comportamiento: La conexión se devuelve al pool después de cada transacción
• Caso de Uso: La mayoría de las aplicaciones web con transacciones cortas
• Eficiencia: Alta (reducción de 10‑100 x)

pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

3. Agrupamiento de Sentencia (pool_mode = statement)

• Comportamiento: La conexión se devuelve después de cada sentencia
• Caso de Uso: Consultas de solo lectura simples sin transacciones
• Eficiencia: Máxima (pero muy restrictiva)

pool_mode = statement
; Usar con precaución - rompe transacciones multi‑sentencia

Configuración de Autenticación

Creación de userlist.txt

PgBouncer requiere un archivo de autenticación separado. Generar el hash MD5 y añadir a userlist.txt.

Ejemplo de userlist.txt:

"app_user" "md5d8578edf8458ce06fbc5bb76a58c5ca4"
"readonly_user" "md5a3c7f5e89d24e7c8b1f9d2e4a6c8b0d2"

Uso de PostgreSQL auth_query (Avanzado)

Consultar directamente PostgreSQL para autenticación:

auth_type = md5
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

Configuración Óptima para Tráfico Alto

Dimensionamiento del Pool de Conexiones

Fórmula para dimensionar el pool:

default_pool_size = (num_cores × 2) + effective_spindle_count

Para un servidor de 4 núcleos con SSD:

default_pool_size = 20
reserve_pool_size = 5
max_client_conn = 1000

Configuración Completa para Producción

[databases]
production = host=db.example.com port=5432 dbname=prod_db pool_size=30
analytics = host=db-replica.example.com port=5432 dbname=prod_db pool_size=15

[pgbouncer]
pool_mode = transaction

; Límites de conecciones
max_client_conn = 2000
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 8
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 600

; Redes
listen_addr = 0.0.0.0
listen_port = 6432
so_reuseport = 1
pkt_buf = 8192

; Seguridad
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
ignore_startup_parameters = extra_float_digits,options

; Registro
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

; Rendimiento
max_prepared_statements = 0
query_timeout = 30
query_wait_timeout = 120

Cadena de Conexión de la Aplicación

Antes de PgBouncer

# Conexión directa a PostgreSQL
DATABASE_URL = "postgresql://user:[email protected]:5432/mydb"

Después de PgBouncer

# Conexión a través de PgBouncer
DATABASE_URL = "postgresql://user:[email protected]:6432/mydb"

Monitoreo y Gestión

Comandos de la Consola de Administración

Conectar a la consola de administración de PgBouncer:

psql -h localhost -p 6432 -U pgbouncer pgbouncer

Comandos esenciales:

-- Mostrar estadísticas del pool
SHOW POOLS;

-- Mostrar conexiones activas
SHOW CLIENTS;
SHOW SERVERS;

-- Mostrar configuración
SHOW CONFIG;

-- Recargar configuración
RELOAD;

Solución de Problemas Comunes

Problema 1: "no more connections allowed"

Solución:

max_client_conn = 5000
default_pool_size = 50

Problema 2: Alto contador de cl_waiting

Soluciones:

1. Incrementar el tamaño del pool
2. Optimizar consultas lentas
3. Añadir pool de reserva

Problema 3: Errores de Sentencias Preparadas

Solución:

max_prepared_statements = 0

Impacto en el Rendimiento Ejemplos

Antes de PgBouncer

• 500 solicitudes concurrentes → 500 conexiones a PostgreSQL
• Carga de la base de datos: 95 % de CPU, 8 GB de RAM
• Tiempo de respuesta: 250 ms promedio

Después de PgBouncer

• 500 solicitudes concurrentes → 25 conexiones a PostgreSQL
• Carga de la base de datos: 35 % de CPU, 1 GB de RAM
• Tiempo de respuesta: 80 ms promedio
• Resultado: 3 veces más rápido, 70 % menos uso de recursos
• Resultado: 3 veces más rápido, 70 % menos uso de recursos

Conclusión

PgBouncer es esencial para escalar aplicaciones PostgreSQL. Reduce el costo de conexión en más del 90 %, soporta 10‑100 veces más clientes y mejora dramáticamente los tiempos de respuesta. Comience con el modo de agrupamiento de transacciones y ajuste según el monitoreo.