PgBouncer fundamentos: el pool externo y sus tres modos

Descripción de la cápsula

Hasta acá el pool vive dentro de tu app: SQLAlchemy maneja conexiones, asyncpg habla con PostgreSQL directamente. Funciona perfecto hasta cierto punto. Después choca contra paredes:

• 4 instancias de FastAPI × pool_size=20 = 80 conexiones potenciales. Si max_connections=100 en PostgreSQL, ya estás al límite (cápsula 02).
• Cada conexión real cuesta ~10-30MB de RAM en PostgreSQL. Subir max_connections no escala (cápsula 02).
• Conexiones idle de tu pool no se aprovechan globalmente — la instancia A puede tener 15 idle mientras la B necesita 5 más y no las tiene.

PgBouncer resuelve esto. Es un proceso externo, ligero, que se sienta entre tu app y PostgreSQL:

[App: 100 conexiones del cliente] → [PgBouncer] → [25 conexiones reales a PostgreSQL]

Los clientes ven 100 conexiones disponibles. PostgreSQL solo ve 25. PgBouncer hace la multiplexación.

Esta cápsula te enseña:

• Arquitectura de PgBouncer: cómo se interpone, qué problema resuelve.
• Los tres modos de pooling (session, transaction, statement): qué hace cada uno, qué features se rompen, cuándo conviene cada uno.
• Setup mínimo con docker-compose para tu bookstore.
• Comandos administrativos críticos: SHOW POOLS, SHOW STATS, SHOW CLIENTS, RELOAD.
• Decision matrix explícita: dado tu caso, ¿qué modo elegir?

Al terminar vas a tener PgBouncer corriendo localmente, conectarte a través de él, y entender qué consecuencias tiene cada modo elegido.

Modelo mental: el concierge del hotel

Recuerda el modelo de la cápsula 02: PostgreSQL es un hotel con 100 habitaciones limitadas. Cada huésped (conexión) consume una habitación.

PgBouncer es el concierge del hotel:

• Los visitantes (apps cliente) llegan al concierge, no al hotel directamente.
• El concierge tiene una sala de espera grande (puede manejar muchos clientes simultáneos).
• Cuando el concierge necesita una habitación real, la pide al hotel.
• Cuando el huésped ya no necesita la habitación, el concierge la devuelve para que otro la use.

El hotel solo ve "el concierge ocupó X habitaciones reales" — no le importa cuántos visitantes hay del lado del concierge.

Los tres modos de PgBouncer son tres políticas distintas del concierge:

• Session pooling: el concierge asigna una habitación al visitante por toda su estadía. Si llegan 100 visitantes al mismo tiempo, necesita 100 habitaciones. No multiplexa nada.
• Transaction pooling: el concierge asigna habitación solo durante la transacción (un check-in para una operación específica). Cuando termina, la libera. Otro visitante puede entrar a la misma habitación segundos después. Multiplexa eficientemente.
• Statement pooling: el concierge cambia la habitación por cada statement. Eficiente al máximo pero rompe cualquier cosa que necesite continuidad entre statements (como una transacción).

La arquitectura: dónde se sienta PgBouncer

┌─────────────────────────────────────────────────────────┐
│  4 instancias de FastAPI                                │
│  pool_size=20 cada una                                  │
│  → Hasta 80 conexiones al "siguiente paso"             │
└─────────────────────────────────────────────────────────┘
                    ↓ (TCP, puerto 6432)
┌─────────────────────────────────────────────────────────┐
│  PgBouncer (proceso ligero, ~10MB RAM total)           │
│  max_client_conn=200 (puede aceptar hasta 200 cliente) │
│  default_pool_size=25 (máximo conexiones reales a PG)  │
└─────────────────────────────────────────────────────────┘
                    ↓ (TCP, puerto 5432)
┌─────────────────────────────────────────────────────────┐
│  PostgreSQL                                             │
│  max_connections=100                                    │
│  Solo ve 25 conexiones de PgBouncer                     │
└─────────────────────────────────────────────────────────┘

Beneficios:

1. Tu app puede escalar a más instancias sin agotar max_connections de PostgreSQL. Pasas de 4 instancias a 20 sin tocar la DB.
2. Conexiones reales a PostgreSQL son menos → menos RAM consumida en el servidor de DB → más disponible para shared_buffers y queries.
3. Pooling es global, no por instancia. Si la instancia A no usa sus 20 conexiones, las puede aprovechar la B.
4. Reconnect rápido del lado del cliente. PgBouncer mantiene conexiones reales a PostgreSQL siempre tibias; si una instancia de app se reinicia, no paga el handshake completo.

Costos:

1. Una pieza más en tu infra. Hay que monitorearla, deployarla, mantenerla.
2. Latencia adicional muy chica (~0.1-1ms por proxy hop si está en el mismo host).
3. Algunos features de PostgreSQL se rompen según el modo (el punto crítico de los próximos apartados).

Los tres modos de pooling

Este es el corazón de la cápsula. Cada modo tiene un comportamiento radicalmente distinto y rompe distintas cosas.

Modo 1: Session pooling

Comportamiento: la conexión real a PostgreSQL se asigna al cliente cuando se conecta y no se libera hasta que el cliente se desconecte. Si tu app abre una conexión y la mantiene en su pool por 1 hora, esa conexión real está dedicada a tu app durante esa hora.

Cliente A se conecta → PgBouncer abre conexión real #5 a PG, la asigna a A.
Cliente A hace queries durante 30 minutos.
Cliente A cierra la conexión → PgBouncer libera la conexión real #5 (puede asignarla a B).

Compatibilidad: 100%. Funciona exactamente como conexión directa a PostgreSQL. Todos los features:

• Prepared statements ✅
• LISTEN/NOTIFY ✅
• SET LOCAL ✅
• Advisory locks ✅
• Cursors WITH HOLD ✅
• Temp tables ✅

Cuándo usar:

• Apps con conexiones de larga duración que mantienen estado de sesión (poco común en FastAPI, común en aplicaciones de escritorio o conectores legacy).
• Si tu app necesita features que se rompen en transaction mode (LISTEN/NOTIFY, prepared statements no parametrizables, etc.).
• Migración inicial: empieza con session mode mientras adoptas PgBouncer, después evalúa transaction mode.

Cuándo NO usar:

• Apps async de alto throughput (FastAPI con asyncpg). El cliente mantiene conexiones idle entre requests, así que session mode no multiplexa — es como tener PgBouncer sin sus beneficios.

Modo 2: Transaction pooling

Comportamiento: la conexión real se asigna al cliente solo durante la transacción. Cuando el cliente hace COMMIT (o ROLLBACK, o termina sin transacción explícita), PgBouncer libera la conexión real para que otro cliente la use.

Cliente A inicia transaccion (BEGIN) → PgBouncer le asigna conexion real #5.
Cliente A hace queries.
Cliente A hace COMMIT → PgBouncer libera #5, la pone disponible.
Cliente B inicia transaccion → PgBouncer le asigna #5 (la misma conexion real).

Multiplexación máxima: 100 clientes en idle entre transacciones pueden compartir 25 conexiones reales mientras solo 5-10 están en transacciones activas.

Compatibilidad: PARCIAL. Estos features se rompen:

• ❌ Prepared statements (más detalle abajo, es el gotcha de asyncpg).
• ❌ LISTEN/NOTIFY (depende de sesión persistente).
• ❌ SET LOCAL ... persistente (solo dura la transacción actual).
• ❌ Cursors WITH HOLD (necesitan sesión).
• ❌ Temp tables persistentes entre transacciones.
• ❌ Algunos advisory locks de sesión (pg_advisory_lock no pg_advisory_xact_lock).
• ❌ Variables de sesión (SET application_name = ... solo dura la transacción).

Estos sí funcionan:

• ✅ Queries simples con parámetros.
• ✅ Transacciones completas (BEGIN ... COMMIT).
• ✅ SET LOCAL dentro de la transacción.
• ✅ Advisory locks de transacción (pg_advisory_xact_lock).
• ✅ Temp tables que viven solo en la transacción.

Cuándo usar:

• Default recomendado para FastAPI async. Es el modo que aprovecha al máximo PgBouncer.
• Apps de alto throughput sin necesidad de features de sesión persistente.
• Cuando puedes pagar el costo de adaptar tu código (cápsula 06: statement_cache_size=0).

Cuándo NO usar:

• Apps que dependen de LISTEN/NOTIFY (sistema de notificaciones realtime sobre PostgreSQL).
• Apps con SET de variables de sesión persistente (poco común, algunas integraciones legacy).
• Si no puedes desactivar el statement cache de asyncpg (raro, casi siempre se puede).

Modo 3: Statement pooling

Comportamiento: la conexión real se asigna por cada statement individual. Cuando termina el statement, la conexión vuelve al pool.

Cliente A: SELECT 1 → PgBouncer le da #5, ejecuta, libera #5.
Cliente A: SELECT 2 → PgBouncer le da #7, ejecuta, libera #7.

Compatibilidad: ROTA. Solo funcionan queries individuales sin estado:

• ❌ Transacciones multi-statement (BEGIN ... statement ... COMMIT).
• ❌ Todo lo que rompe transaction mode también rompe acá.
• ❌ Prepared statements igual que transaction.

Cuándo usar:

• Casi nunca. Casos muy específicos de cargas de pure read sin transacciones.
• Algunas integraciones de cache (Redis-style usage de PostgreSQL).

Cuándo NO usar:

• 99% de los casos. Si tu código tiene cualquier BEGIN ... COMMIT, no es para ti.

Decision matrix: qué modo elegir

Heurística general: empieza en transaction mode (es lo que recomienda Supabase, AWS RDS Proxy, Neon, y la mayoría de proveedores). Si encuentras un feature que necesitas y se rompe, antes de cambiar a session mode, evalúa si puedes refactorizar la app — la mayoría de los rompimientos tienen workarounds.

Setup mínimo con docker-compose

El bookstore ya tiene PostgreSQL en un contenedor. Vamos a agregar PgBouncer.

# docker-compose.yml
version: "3.9"

services:
  bookstore-pg:
    image: postgres:16
    environment:
      POSTGRES_USER: bookstore
      POSTGRES_PASSWORD: bookstore
      POSTGRES_DB: bookstore
    ports:
      - "5432:5432"
    volumes:
      - bookstore_data:/var/lib/postgresql/data
      - ./pg-config/postgresql.conf:/etc/postgresql/postgresql.conf
    command: postgres -c config_file=/etc/postgresql/postgresql.conf

  pgbouncer:
    image: edoburu/pgbouncer:1.22.1
    environment:
      DB_USER: bookstore
      DB_PASSWORD: bookstore
      DB_HOST: bookstore-pg
      DB_PORT: "5432"
      DB_NAME: bookstore
      POOL_MODE: transaction          # session | transaction | statement
      MAX_CLIENT_CONN: "200"
      DEFAULT_POOL_SIZE: "25"
      RESERVE_POOL_SIZE: "5"
      RESERVE_POOL_TIMEOUT: "3"
      AUTH_TYPE: scram-sha-256
      ADMIN_USERS: bookstore
      STATS_USERS: bookstore
    ports:
      - "6432:5432"
    depends_on:
      - bookstore-pg

volumes:
  bookstore_data:

Levantar:

docker compose up -d
docker compose ps
# Debe mostrar bookstore-pg y pgbouncer running

Conectarte a PgBouncer (puerto 6432):

psql -h localhost -p 6432 -U bookstore -d bookstore
# Password: bookstore

Internamente PgBouncer abre la conexión real al PostgreSQL (puerto 5432 dentro del network de Docker). Tú nunca te conectas directo a PostgreSQL — siempre vía PgBouncer.

Cambiar el endpoint de tu app:

# Antes
DATABASE_URL = "postgresql+asyncpg://bookstore:bookstore@localhost:5432/bookstore"

# Despues, apuntando a PgBouncer
DATABASE_URL = "postgresql+asyncpg://bookstore:bookstore@localhost:6432/bookstore"

Tu app ahora habla con PgBouncer. PgBouncer habla con PostgreSQL.

⚠️ Importante: si pones POOL_MODE: transaction en el docker-compose, necesitas el fix de statement_cache_size=0 en asyncpg (cápsula 06) o tu app va a romper con prepared statements.

Parámetros importantes del docker-compose

MAX_CLIENT_CONN: cuántos clientes (apps) puede aceptar simultáneamente. 200 significa que tus 4 instancias de FastAPI con pool_size=20 cada una (= 80 totales) caben con margen.

DEFAULT_POOL_SIZE: cuántas conexiones reales abre a PostgreSQL por base de datos. Si tienes solo bookstore, 25 significa máximo 25 procesos backend en PG.

RESERVE_POOL_SIZE: conexiones extras que puede abrir bajo presión sostenida. Default 0; setear a 5-10 da margen para picos.

POOL_MODE: el modo (session/transaction/statement). Por default es session — tienes que cambiarlo explícitamente si quieres transaction.

AUTH_TYPE: protocolo de autenticación. scram-sha-256 es el moderno (PostgreSQL 14+).

Comandos administrativos: la consola de PgBouncer

PgBouncer expone una "base de datos" virtual llamada pgbouncer para administración. Conéctate como un usuario en ADMIN_USERS:

psql -h localhost -p 6432 -U bookstore pgbouncer
# Password: bookstore

Una vez adentro, no es un PostgreSQL normal — son comandos especiales.

SHOW POOLS

Muestra el estado de cada pool (uno por DB):

SHOW POOLS;

Output ejemplo:

 database  | user     | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
-----------+----------+-----------+------------+-----------+---------+---------+-----------+----------+---------+------------+-------------
 bookstore | bookstore|        15 |          0 |         3 |       7 |       0 |         0 |        0 |       0 |          0 | transaction
 pgbouncer | pgbouncer|         1 |          0 |         0 |       0 |       0 |         0 |        0 |       0 |          0 | statement

Cómo leerlo:

• cl_active: clientes con sesión activa conectados a PgBouncer (en este momento, 15 conexiones del cliente). Esto es lo que tu app abrió hacia PgBouncer.
• cl_waiting: clientes esperando una conexión real (porque el pool está saturado). Si esto es > 0 sostenidamente, sube default_pool_size.
• sv_active: conexiones reales a PostgreSQL que están ejecutando algo en este momento (3 queries activas).
• sv_idle: conexiones reales a PG idle (7 listas para asignar).
• sv_used: conexiones reales que se usaron pero no han sido reset (transición).
• sv_tested: en proceso de health check.
• sv_login: en proceso de autenticación.
• maxwait: segundos que el cliente más viejo en cl_waiting lleva esperando. Si esto crece, hay saturación.

Diagnóstico rápido:

cl_active=15, sv_active=3, sv_idle=7  → Saludable. Pool tiene margen (10 disponibles).
cl_active=80, cl_waiting=20, sv_active=25, sv_idle=0  → SATURADO. Sube default_pool_size.
cl_active=15, sv_active=3, sv_idle=22  → Sobre-dimensionado. Considera bajar default_pool_size.

SHOW STATS

Métricas acumuladas desde que arrancó PgBouncer:

SHOW STATS;

 database  | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | total_wait_time | avg_xact_time | avg_query_time
-----------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+---------------+-----------------
 bookstore |          1234567 |           5678901 |     1234567890 | 9876543210 |        12345678 |          9876543 |             123 |           500 |             80

Lo que importa:

• total_query_count: queries totales procesadas. Útil para tracking de throughput.
• avg_query_time: tiempo promedio por query (microsegundos). Si crece, hay queries lentas.
• total_wait_time / avg_wait_time: tiempo que clientes pasaron esperando conexión. Si crece, pool insuficiente.

SHOW CLIENTS

Lista detallada de clientes conectados:

SHOW CLIENTS;

Útil para identificar qué cliente específico está saturando. Cada fila muestra IP, query actual, tiempo conectado, etc.

RELOAD

Recarga la configuración sin reiniciar PgBouncer (sin perder conexiones):

RELOAD;

Útil después de editar pgbouncer.ini (o variables de entorno en el contenedor).

PAUSE / RESUME

Para mantenimiento:

PAUSE;   -- detiene aceptar queries nuevas, espera a que termine las en curso
-- haces tu mantenimiento (restart de PG, switchover, etc.)
RESUME;  -- vuelve a procesar

Útil para reinicios cero-downtime de PostgreSQL.

Por qué importa esto en el trabajo real

1. PgBouncer es estándar de facto. Supabase, RDS Proxy, Neon, Crunchy Bridge, Postgres.app — todos usan PgBouncer (o forks compatibles) internamente. Conocerlo es prerequisito para trabajar con cualquier PostgreSQL gestionado moderno.

2. La elección del modo define qué features están disponibles. "Activar PgBouncer" no es atómico — la decisión de modo cambia cómo escribes código. Saber qué se rompe en transaction mode evita "agregamos PgBouncer y la app explotó" en el siguiente sprint.

3. SHOW POOLS es debugging table stakes en producción. Cuando hay un incidente, abrir psql a la consola de PgBouncer y correr SHOW POOLS es uno de los tres primeros pasos. Si no entiendes la salida, no puedes diagnosticar.

4. Multiplexación cambia tu cálculo de capacidad. Antes de PgBouncer: pool_size × instancias < max_connections. Después: pool_size × instancias < MAX_CLIENT_CONN (mucho más alto). PostgreSQL solo ve default_pool_size. Esto te permite escalar horizontalmente sin preocuparte por el cap del servidor.

5. La decision matrix te ahorra incidentes. "El equipo de notificaciones agregó LISTEN/NOTIFY y ahora la app no recibe eventos." Si activaste transaction mode sin saber que LISTEN/NOTIFY se rompe, ese es tu próximo postmortem.

Trampas y errores comunes

Error 1 (conceptual): asumir que "todos los modos son intercambiables"

Síntoma: "Activé transaction mode y mi app rompió. Vuelvo a session mode y ya."

Por qué pasa: session mode no aprovecha PgBouncer — es como no tenerlo. Si bajas a session porque transaction rompió tu LISTEN/NOTIFY, perdiste el beneficio de PgBouncer pero seguís pagando el overhead de tenerlo.

Cómo distinguir: si tu pool_size (en SQLAlchemy) ya no satura max_connections (PostgreSQL) gracias a PgBouncer, estás bien. Si seguís en el límite con session mode, PgBouncer no está ayudando.

Cómo corregir: identifica qué feature específicamente te rompió. Para LISTEN/NOTIFY: usa una conexión directa separada (sin PgBouncer) solo para los listeners. Para prepared statements: cápsula 06. Casi siempre hay workaround.

Error 2 (operativo): no monitorear cl_waiting y maxwait

Síntoma: "PgBouncer estaba bien hasta que un día la app empezó a tardar mucho. Sin info de qué pasaba."

Por qué pasa: sin SHOW POOLS periódico (o métricas exportadas), no sabes que el pool está saturado hasta que los usuarios reportan latencia.

Cómo distinguir: ejecuta SHOW POOLS. Si cl_waiting > 0 o maxwait > 1 segundo durante minutos, hay saturación.

Cómo corregir: o subes default_pool_size, o bajas la carga (queries más rápidas, refactor), o agregas más capacidad de PostgreSQL. Setea alertas en cl_waiting > 5 para detectar antes de que sea problema.

Error 3 (conceptual): mezclar pool_size de SQLAlchemy con default_pool_size de PgBouncer

Síntoma: "Subí default_pool_size a 100 pero mi app sigue sin escalar."

Por qué pasa: default_pool_size es el límite de PgBouncer hacia PostgreSQL. Si tu app tiene pool_size=20 en SQLAlchemy, sigue limitada a 20 conexiones por instancia hacia PgBouncer, independientemente del límite de PgBouncer hacia PG.

Cómo distinguir: si subes default_pool_size y SHOW POOLS no muestra más sv_active, el cuello no está ahí.

Cómo corregir: entiende los dos pools como capas independientes:

• pool_size (SQLAlchemy) = capa cliente.
• default_pool_size (PgBouncer) = capa multiplexada.

Para escalar throughput, sube ambas en proporción. Cápsula 07 cubre el sizing detallado.

Error 4 (operativo): apuntar tu app a PostgreSQL directo "para test rápido" y olvidar volver a PgBouncer

Síntoma: "El staging está apuntado a PostgreSQL directo. Cuando promovieron a producción, las 4 instancias agotaron max_connections y todo rompió."

Por qué pasa: trampa común en setups con varios entornos. Cambiaste el endpoint en staging por debugging, deploy automático llevó la config a producción.

Cómo distinguir: revisa el endpoint de tu DATABASE_URL en producción. Debe terminar en el puerto de PgBouncer (típicamente 6432), no el de PG (5432).

Cómo corregir: mantén las URLs por entorno explícitas y diferenciadas. Considera un check en startup que valide "estoy hablando con PgBouncer, no PG directo".

Error 5 (conceptual): usar BEGIN; SET LOCAL ...; <queries>; COMMIT; esperando que SET persista entre transacciones

Síntoma: "Pongo SET LOCAL search_path = 'tenant_42' al inicio de cada request. En transaction mode, el SET no persiste entre queries."

Por qué pasa: SET LOCAL solo dura la transacción actual. En transaction mode, cada BEGIN ... COMMIT puede usar una conexión real distinta — lo que SET LOCAL hizo en la previa no aplica a la siguiente.

Cómo distinguir: si tu app usa SET LOCAL fuera de la transacción donde aplica las queries, esto rompe en transaction mode.

Cómo corregir: dos opciones:

1. Cambia a session mode (perdés multiplexación).
2. Aplica el SET LOCAL dentro de cada transacción donde lo necesitás. Refactor a:

async with SessionLocal() as session:
    async with session.begin():  # explícita transacción
        await session.execute(text("SET LOCAL search_path TO :tenant"), {"tenant": "tenant_42"})
        # tus queries aquí, dentro de la misma transacción
        result = await session.scalar(...)
    # COMMIT acá; el SET LOCAL terminó.

Más verboso pero compatible con transaction mode.

Ejercicios

Ejercicio 1: levantar PgBouncer con docker-compose y conectarte

Configura el docker-compose.yml de la cápsula. Levanta los servicios. Conéctate a PgBouncer con psql y verifica que puedes hacer queries.

docker compose up -d
docker compose ps

NAME                    STATUS         PORTS
bookstore-pg            Up 5 seconds   0.0.0.0:5432->5432/tcp
pgbouncer               Up 4 seconds   0.0.0.0:6432->5432/tcp

psql -h localhost -p 6432 -U bookstore -d bookstore

SELECT current_database(), current_user, version();

psql -h localhost -p 6432 -U bookstore pgbouncer

SHOW POOLS;
SHOW DATABASES;

docker compose down

Ejercicio 2: experimentar con los tres modos

Cambia el modo de PgBouncer (POOL_MODE) entre session, transaction y statement. Para cada uno, intenta:

a) Una query simple (SELECT 1). b) Una transacción (BEGIN; SELECT 1; COMMIT;). c) LISTEN test_channel; seguido de un SELECT pg_sleep(2) desde otra sesión que haga NOTIFY test_channel, 'hi';.

Observa qué funciona en cada modo.

# Cambiar POOL_MODE en docker-compose.yml a "session"
docker compose restart pgbouncer

# Conectarse y probar:
psql -h localhost -p 6432 -U bookstore bookstore

SELECT 1;

BEGIN;
SELECT 1;
COMMIT;

ERROR:  cannot insert multiple commands into a prepared statement

LISTEN test_channel;
SELECT pg_sleep(60);  -- mantiene la sesion abierta

NOTIFY test_channel, 'hi from notifier';

Ejercicio 3: identificar saturación con SHOW POOLS

Configura PgBouncer con default_pool_size=2 y max_client_conn=100. Conecta 5 clientes simultáneos que ejecuten SELECT pg_sleep(10). Observa SHOW POOLS durante el experimento.

docker compose restart pgbouncer

# saturate.sh
for i in {1..5}; do
    PGPASSWORD=bookstore psql -h localhost -p 6432 -U bookstore -d bookstore \
        -c "SELECT pg_sleep(10), $i AS client" &
done
wait

while true; do
    echo "--- $(date +%H:%M:%S) ---"
    PGPASSWORD=bookstore psql -h localhost -p 6432 -U bookstore pgbouncer \
        -c "SHOW POOLS;" 2>/dev/null | head -5
    sleep 2
done

chmod +x saturate.sh && ./saturate.sh &

--- 14:30:00 ---
 database  | user      | cl_active | cl_waiting | sv_active | sv_idle | maxwait | pool_mode
-----------+-----------+-----------+------------+-----------+---------+---------+-------------
 bookstore | bookstore |         5 |          3 |         2 |       0 |       4 | transaction

 cl_active | cl_waiting | sv_active | sv_idle | maxwait
-----------+------------+-----------+---------+---------
         3 |          1 |         2 |       0 |       8

Ejercicio 4: hacer el switch de tu app de PG directo a PgBouncer

Toma tu app FastAPI del bookstore (de la cápsula 04) y cámbiala para apuntar a PgBouncer en vez de PostgreSQL directo. Verifica que sigue funcionando con POOL_MODE: session.

# Antes
DATABASE_URL = "postgresql+asyncpg://bookstore:bookstore@localhost:5432/bookstore"

# Despues (puerto 6432 = PgBouncer)
DATABASE_URL = "postgresql+asyncpg://bookstore:bookstore@localhost:6432/bookstore"

pgbouncer:
  environment:
    POOL_MODE: session  # ← compatible con prepared statements

docker compose restart pgbouncer

uvicorn app.main:app --reload

curl http://localhost:8000/books/1

PGPASSWORD=bookstore psql -h localhost -p 6432 -U bookstore pgbouncer \
    -c "SHOW POOLS;"

-- Conectarse a PG directo (puerto 5432) para verificar
psql -h localhost -p 5432 -U bookstore bookstore

SELECT pid, application_name, client_addr, client_port
FROM pg_stat_activity
WHERE datname = 'bookstore';

 pid   | application_name  | client_addr | client_port
-------+-------------------+-------------+-------------
 12345 | bookstore-api     | 172.18.0.1  | 56234       ← tu app via PgBouncer

Ejercicio 5: decision matrix práctica

Para cada caso, decide qué modo de PgBouncer recomendarías y por qué:

a) API REST de e-commerce con SQLAlchemy 2.0 async, alto throughput. b) Sistema de notificaciones realtime que usa LISTEN/NOTIFY como bus interno. c) Aplicación legacy en Java que mantiene 50 conexiones long-lived todo el día. d) Worker de procesamiento batch que ejecuta queries individuales sin transacciones. e) API multitenant que usa SET LOCAL search_path al inicio de cada request.

async with session.begin():  # transaccion explicita
    await session.execute(text("SET LOCAL search_path TO :tenant"), {"tenant": tenant_id})
    # tus queries
# COMMIT

Resumen y siguiente paso

En esta cápsula:

• Construiste el modelo mental de PgBouncer como "concierge" entre app y PostgreSQL.
• Aprendiste los tres modos (session/transaction/statement), qué hace cada uno y qué features se rompen.
• Conociste la decision matrix con criterios concretos.
• Levantaste PgBouncer localmente con docker-compose.
• Practicaste comandos administrativos: SHOW POOLS, SHOW STATS, SHOW CLIENTS, RELOAD.
• Migraste tu app de PostgreSQL directo a PgBouncer (en session mode, paso intermedio).

Antes de avanzar, deberías poder:

• Explicar la diferencia entre los tres modos en menos de 2 minutos.
• Saber qué features se rompen en transaction mode (LISTEN/NOTIFY, prepared statements, SET no LOCAL, etc.).
• Diagnosticar saturación con SHOW POOLS (qué columnas mirar).
• Levantar PgBouncer en docker-compose y conectarte vía puerto 6432.

Siguiente cápsula — PgBouncer + asyncpg gotchas. Ya tienes PgBouncer corriendo (en session mode, donde todo funciona). En la próxima vamos al modo recomendado real (transaction) y enfrentamos el bug que aparece siempre al hacerlo: las prepared statements de asyncpg se rompen aleatoriamente. Vas a ver el error exacto, entender por qué pasa, aplicar el fix statement_cache_size=0, y revisar otros gotchas menores que aparecen al introducir transaction mode. Es la cápsula que te ahorra el incidente de producción más sutil de todo el módulo.

Recursos

1. PgBouncer official documentation — referencia canónica.
2. PgBouncer pooling modes — sección específica de los 3 modos.
3. Supabase — Connection pooling with Supavisor — caso real con PgBouncer-like.
4. AWS RDS Proxy — versión gestionada que usa el mismo paradigma.
5. edoburu/pgbouncer Docker image — la imagen que usamos en docker-compose.
6. Crunchy Data — PgBouncer best practices — operación a escala.
7. PgBouncer admin console reference — todos los comandos SHOW.

Módulo 6 — Database Performance & Query Tuning Guide