Visión General de los Patrones de Producción

En proyectos backend reales, el rendimiento de PostgreSQL rara vez se reduce a una única consulta lenta. Es la combinación de cómo se conecta tu aplicación, cómo consulta y cómo evoluciona tu esquema con el tiempo. Esta lección cubre cuatro patrones de producción que impactan directamente en el rendimiento de extremo a extremo: connection pooling con PgBouncer, estrategias de paginación para conjuntos de resultados grandes, flujos de trabajo de migración de esquema usando herramientas como sqitch o Flyway, y un enfoque sistemático de optimización de rendimiento que parte de la consulta y baja hasta la configuración de la base de datos.

El objetivo es pasar de "funciona en mi máquina" a un comportamiento predecible bajo carga real.

Connection Pooling con PgBouncer

Cada conexión a PostgreSQL genera un proceso backend, lo cual consume memoria y CPU. Un backend con cientos de peticiones concurrentes agotará rápidamente el límite de conexiones. PgBouncer se sitúa entre tu aplicación y PostgreSQL, multiplexando muchas conexiones de aplicación sobre un grupo más pequeño de conexiones reales a la base de datos.

El modo de producción más común es transaction pooling, donde una conexión se asigna a un cliente solo durante la duración de una transacción:

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

En tu aplicación, diriges el host de la base de datos al puerto 6432 en lugar del 5432. Una advertencia importante con transaction pooling: las prepared statements, los comandos `SET` y características a nivel de sesión como `LISTEN/NOTIFY` no funcionan de forma fiable porque la conexión subyacente puede cambiar entre transacciones. Usa prepared statements del lado del servidor solo cuando trabajes en modo session pooling.

Estrategias de Paginación

La paginación por offset con `LIMIT ... OFFSET ...` es práctica, pero tiene un rendimiento pobre en tablas grandes porque PostgreSQL sigue escaneando y descartando todas las filas saltadas. En una tabla con millones de filas, la página 1000 es notablemente más lenta que la página 1.

La paginación por keyset (también llamada paginación por cursor) evita por completo el offset usando el último valor visto de una columna indexada:

-- Bad: offset pagination
SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

-- Good: keyset pagination
SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

La segunda consulta usa un índice compuesto sobre `(created_at, id)` y puede saltar directamente a la posición correcta. También devuelve resultados estables incluso si se insertan nuevas filas entre cargas de página, algo esencial para feeds y scroll infinito.

Migraciones de Esquema

En producción, los cambios de esquema deben ser repetibles, revisables y reversibles. Herramientas como Flyway, Liquibase o sqitch almacenan las migraciones como archivos SQL versionados que se ejecutan en orden, con una tabla de seguimiento que registra qué migraciones se han aplicado.

Un archivo de migración típico tiene este aspecto:

-- V002__add_user_email_index.sql
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Tres reglas son las más importantes. Primero, usa siempre `CREATE INDEX CONCURRENTLY` en tablas existentes para no bloquear escrituras durante la creación del índice. Segundo, nunca mezcles cambios destructivos (eliminar una columna) con nuevas funcionalidades en la misma migración, para que el rollback sea posible. Tercero, prueba las migraciones contra una copia de los datos de producción, porque las estadísticas del planner de PostgreSQL pueden revelar problemas que una pequeña base de datos de desarrollo nunca muestra.

Optimización de Rendimiento de Extremo a Extremo

La optimización sigue un enfoque por capas. Primero, identifica la consulta lenta con `pg_stat_statements` o con logging a nivel de aplicación. Segundo, ejecuta `EXPLAIN (ANALYZE, BUFFERS)` para ver el plan real, los recuentos de filas y los aciertos de buffer frente a las lecturas de disco. Tercero, corrige la causa raíz, que suele ser un índice faltante, un orden de join incorrecto o una consulta que obtiene más datos de los necesarios.

Solo después de que la capa de consultas esté limpia deberías tocar los parámetros de la base de datos. Las mejoras habituales incluyen aumentar `shared_buffers` a alrededor del 25 por ciento de la RAM, establecer `effective_cache_size` a aproximadamente el 75 por ciento, y ajustar `work_mem` por consulta si hay ordenaciones o hashes grandes que están spilling a disco. Pero recuerda: la configuración no puede salvar una consulta que escanea una tabla entera en cada petición.

-- Finding the top 10 slowest queries by total execution time
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Resumen

El rendimiento de PostgreSQL en producción es una propiedad del sistema, no un único knob. Aplica connection pooling con PgBouncer para manejar la concurrencia sin agotar la base de datos, reemplaza la paginación por offset con paginación por keyset en tablas grandes, gestiona los cambios de esquema mediante herramientas de migración versionadas y sigue un flujo de optimización que prioriza las consultas antes de tocar la configuración del servidor. En conjunto, estos patrones te ofrecen latencia predecible y un camino claro cuando la carga aumenta.