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

Cuando las bases de datos PostgreSQL enfrentan altos volúmenes de conexión, el rendimiento puede degradarse rápidamente. Cada conexión de cliente se asigna a un proceso backend de PostgreSQL, por lo que una aplicación web ocupada puede gastar demasiada memoria y CPU solo manteniendo sesiones abiertas. El pooling de conexiones PgBouncer reduce esa presión al permitir que muchas conexiones de cliente reutilicen un conjunto más pequeño de conexiones de servidor.

Por Qué es Importante el Pooling de Conexiones

El Problema de las Conexiones

Sobrecarga de recursos: Cada conexión de PostgreSQL tiene un proceso backend y una sobrecarga de memoria.
Límites de conexión: max_connections es finito, y aumentarlo demasiado puede hacer que la base de datos sea menos estable en lugar de más rápida.
Costo de inicio: Crear nuevas conexiones de base de datos agrega latencia.
Cambio de contexto: Demasiados procesos backend activos pueden desperdiciar CPU.

Beneficios de PgBouncer

Permite que muchos clientes de aplicación compartan menos conexiones de servidor PostgreSQL.
Pone en cola a los clientes cuando el pool está ocupado en lugar de abrumar la base de datos.
Soporta modos de pooling de sesión, transacción y declaración.
Te proporciona comandos operativos como SHOW POOLS, SHOW CLIENTS y RELOAD.

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

Ubicaciones Comunes de Archivos

/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
miapp = host=localhost port=5432 dbname=base_produccion

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

; Conexiones máximas
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

Los valores predeterminados del paquete varían según la distribución, así que verifica el archivo de servicio instalado y la ruta de configuración después de la instalación. En muchos paquetes de Linux, /etc/pgbouncer/pgbouncer.ini es el archivo principal.

Entendiendo los Modos de Pooling

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

Comportamiento: Conexión asignada al cliente durante toda la sesión
Caso de Uso: Aplicaciones que usan tablas temporales, declaraciones preparadas
Eficiencia: Baja (proporción de conexión 1:1)

pool_mode = session

2. Pooling de Transacciones (pool_mode = transaction) - Recomendado

Comportamiento: Conexión devuelta al pool después de cada transacción
Caso de Uso: La mayoría de las aplicaciones web con transacciones cortas
Eficiencia: Alta, cuando tu aplicación no depende del estado de la sesión

pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

El pooling de transacciones es un valor predeterminado sólido para aplicaciones web de solicitud/respuesta, pero puede romper suposiciones sobre características a nivel de sesión. Ten cuidado con tablas temporales, variables de sesión, bloqueos de asesoramiento mantenidos fuera de una transacción, LISTEN/NOTIFY y declaraciones preparadas a nivel de controlador a menos que las hayas probado con tu versión y configuración de PgBouncer.

3. Pooling de Declaraciones (pool_mode = statement)

Comportamiento: Conexión devuelta después de cada declaración
Caso de Uso: Consultas simples de solo lectura sin transacciones
Eficiencia: Máxima (pero muy restrictiva)

pool_mode = statement
; Usar con precaución - rompe transacciones de múltiples declaraciones

Configuración de Autenticación

Creando userlist.txt

PgBouncer requiere un archivo de autenticación separado. Genera el hash MD5 y agrégalo a userlist.txt.

Ejemplo de userlist.txt:

"usuario_app" "md5d8578edf8458ce06fbc5bb76a58c5ca4"
"usuario_sololectura" "md5a3c7f5e89d24e7c8b1f9d2e4a6c8b0d2"

Para contraseñas MD5 al estilo PostgreSQL, el valor es md5 más el hash MD5 de contraseña + nombredeusuario. No pegues hashes falsos en producción; genera entradas a partir de tus nombres de usuario y contraseñas reales, o usa un método de autenticación más seguro compatible con tu entorno.

Usando auth_query de PostgreSQL (Avanzado)

PgBouncer puede consultar a PostgreSQL por credenciales de usuario cuando un usuario no se encuentra en auth_file, pero esto requiere un auth_user con el que PgBouncer pueda iniciar sesión. Un ejemplo mínimo se ve así:

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

Limita los privilegios del usuario de autenticación y sigue las pautas de tu versión de PostgreSQL. Muchos equipos usan una función SECURITY DEFINER en lugar de otorgar acceso directo a los datos de contraseña del catálogo.

Configuración Óptima para Alto Tráfico

Dimensionamiento del Pool de Conexiones

No existe una fórmula universal para el tamaño del pool. Comienza con un valor conservador basado en la cantidad de consultas que tu base de datos puede ejecutar activamente bien, luego ajusta según métricas reales.

Para una aplicación web típica, podrías comenzar aquí y ajustar:

default_pool_size = 25
reserve_pool_size = 5
max_client_conn = 1000

Observa la CPU de PostgreSQL, la latencia de las consultas, las esperas de bloqueo y el conteo cl_waiting de PgBouncer. Si los clientes esperan mientras la base de datos está inactiva, el pool puede ser demasiado pequeño. Si la base de datos está saturada, aumentar el pool puede empeorar las cosas.

Configuración Completa de Producción

[databases]
produccion = host=db.ejemplo.com port=5432 dbname=base_prod pool_size=30
analitica = host=db-replica.ejemplo.com port=5432 dbname=base_prod pool_size=15

[pgbouncer]
pool_mode = transaction

; Límites de conexión
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

max_prepared_statements = 0 deshabilita el seguimiento de declaraciones preparadas de PgBouncer. Las versiones recientes de PgBouncer pueden soportar declaraciones preparadas a nivel de protocolo en el pooling de transacciones cuando esto está configurado por encima de cero, pero debes probar tu controlador y carga de trabajo antes de habilitarlo.

Cadena de Conexión de la Aplicación

Antes de PgBouncer

# Conexión directa a PostgreSQL
DATABASE_URL = "postgresql://usuario:contraseñ[email protected]:5432/mibd"

Después de PgBouncer

# Conectar a través de PgBouncer
DATABASE_URL = "postgresql://usuario:contraseñ[email protected]:6432/mibd"

Monitoreo y Gestión

Comandos de la Consola de Administración

Conéctate 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"

Esto puede significar que PgBouncer está rechazando conexiones de cliente o que PostgreSQL está rechazando conexiones de servidor. Verifica dónde aparece el error.

Posibles cambios del lado de PgBouncer:

max_client_conn = 5000
default_pool_size = 50

Antes de aumentar ambos, confirma que tu límite de descriptores de archivo del sistema operativo y max_connections de PostgreSQL puedan soportar los nuevos totales. El propio PgBouncer también necesita suficientes descriptores de archivo para los sockets de cliente y servidor.

Problema 2: Conteo Alto de cl_waiting

Soluciones:

Aumentar el tamaño del pool
Optimizar consultas lentas
Agregar pool de reserva

Problema 3: Errores de Declaraciones Preparadas

Si tu aplicación o controlador usa declaraciones preparadas y estás en modo de pooling de transacciones, puedes ver errores cuando una declaración posterior llega a una conexión de servidor diferente. Las opciones incluyen:

Deshabilitar las declaraciones preparadas del lado del controlador.
Usar pooling de sesión para esa carga de trabajo.
Probar el soporte de declaraciones preparadas de PgBouncer con un valor positivo de max_prepared_statements en una versión reciente de PgBouncer.

Configuración conservadora:

max_prepared_statements = 0

Ejemplo Práctico de Implementación

Imagina que tus servidores de aplicación pueden abrir cientos de solicitudes HTTP concurrentes, pero la base de datos funciona mejor con unas pocas docenas de consultas activas. Apunta la aplicación a PgBouncer en el puerto 6432, establece max_client_conn lo suficientemente alto para las ráfagas de clientes y mantén default_pool_size cerca del número de conexiones de base de datos que realmente deseas activas para ese par de base de datos/usuario.

Luego valida con:

SHOW POOLS;
SHOW STATS;

Si cl_waiting se mantiene por encima de cero durante el tráfico normal, investiga las consultas lentas antes de simplemente aumentar el tamaño del pool. PgBouncer protege a PostgreSQL de tormentas de conexiones, pero no hace que el SQL lento sea económico.

Conclusión Práctica

Comienza con pooling de transacciones para cargas de trabajo web sin estado, mantén los tamaños del pool intencionalmente pequeños y ajusta según las métricas de PgBouncer y PostgreSQL. Si tu aplicación depende del comportamiento de la sesión o de declaraciones preparadas, prueba esas rutas antes de poner PgBouncer frente al tráfico de producción.