Optimización de PostgreSQL en Producción: Guía Práctica para Equipos Tech · Daniel Tinizaray

Cuando tu aplicación comienza a crecer, el cuello de botella casi siempre es la base de datos. No es que PostgreSQL sea lento — es que la configuración por defecto está pensada para una laptop, no para producción.

Este artículo cubre las optimizaciones que realmente importan en PostgreSQL en producción, basadas en experiencia operando bases de datos Django con cientos de miles de registros y consultas complejas.

1. Connection Pooling: El Error Más Común

Cada conexión a PostgreSQL consume entre 5-10 MB de RAM. Con 100 conexiones directas desde una app Django, son ~1 GB solo en overhead de conexiones. psql muestra client connections activas; el problema es cuando el app server abre y cierra conexiones constantemente.

La solución: PgBouncer en modo transaction. Las conexiones de aplicación se mantienen en un pool reducido, y PgBouncer las multiplexa contra PostgreSQL.

# pgbouncer.ini
[databases]
* = host=localhost port=5432

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 200
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3.0
server_idle_timeout = 300

Esta configuración permite que 25 conexiones al servidor manejen el tráfico de 200 clientes de aplicación, reduciendo drásticamente el consumo de memoria.

2. Índices: Calidad sobre Cantidad

Un índice mal diseñado pesa más de lo que ayuda. PostgreSQL lo mantiene en cada INSERT/UPDATE/DELETE, ralentizando escrituras aunque acelere lecturas.

Índices compuestos y parciales

Los índices más efectivos son los que cubren columnas en el orden correcto. Si filtras por user_id y luego por status, el índice debe empezar por user_id.

-- Índices compuestos vs individuales
-- ❌ Así no funciona
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

-- ✅ Así sí: índice compuesto que cubre el filtro
CREATE INDEX idx_orders_user_status ON orders(user_id, status)
WHERE status IN ('pending', 'processing');

-- Partial index para datos calientes
CREATE INDEX idx_orders_last_30d ON orders(created_at)
WHERE created_at > now() - interval '30 days';

Los partial indexes son particularmente útiles cuando solo consultas un subconjunto de datos (ej: pedidos activos de los últimos 30 días). El índice es más pequeño y rápido de escanear.

3. Diagnóstico: pg_stat_statements

No optimices lo que no mides. La extensión pg_stat_statements es la herramienta de diagnóstico más valiosa para PostgreSQL en producción. Te dice exactamente qué queries consumen más tiempo, qué tablas tienen cache hit ratio bajo, y dónde están los cuellos de botella.

-- Diagnóstico rápido de queries lentas
SELECT
  queryid,
  calls,
  mean_exec_time::numeric(10,2),
  total_exec_time::numeric(10,2),
  rows / calls AS avg_rows,
  shared_blks_hit::numeric / (shared_blks_hit + shared_blks_read) * 100
    AS cache_hit_ratio
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_%'
ORDER BY total_exec_time DESC
LIMIT 10;

Habilitarla requiere agregarla a shared_preload_libraries y reiniciar PostgreSQL. Vale la pena el downtime de 30 segundos.

4. Autovacuum: Tu Mejor Aliado

PostgreSQL usa MVCC (Multi-Version Concurrency Control): cuando actualizas una fila, no la modifica — crea una versión nueva y marca la anterior como "muerta" (dead tuple). Sin vacuum, las tablas crecen indefinidamente, y las queries escanean dead tuples innecesarios.

El autovacuum por defecto funciona, pero en tablas grandes no es suficiente. La configuración por defecto escala con scale_factor: cuando el 20% de las filas son dead tuples, dispara vacuum. En una tabla de 10M registros, eso significa 2M dead tuples antes de limpiar.

-- Configuración de autovacuum por tabla
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_limit = 1000
);

-- Monitoreo de dead tuples
SELECT
  relname,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 2)
    AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Monitorea n_dead_tup semanalmente. Si ves tablas con más de 10% de dead tuples, ajusta el autovacuum.

5. Configuración de Memoria

PostgreSQL con configuración default asigna solo 128 MB de shared buffers. En una instancia con 8 GB de RAM, eso es desperdiciar capacidad de caché.

El cambio más impactante: random_page_cost = 1.1. El default (4) asume discos mecánicos. En SSD/NVMe, el planner infravalora los index scans. Reducirlo a 1.1 hace que PostgreSQL prefiera índices sobre sequential scans.

6. Patrones Anti-Comunes en Django + PostgreSQL

• N+1 queries: El clásico. select_related y prefetch_related no son opcionales en producción.
• SELECT * sin límite: Django .all() sin paginación. Siempre usa .iterator() para datasets grandes.
• Transacciones largas: Mantener una transacción abierta por segundos bloquea el vacuum y acumula dead tuples.
• Campos TEXT sin índice: LIKE '%termino%' no usa índices B-tree. Necesitas pg_trgm o un search engine.
• JSONB sin GIN index: Las queries dentro de JSONB escanean toda la tabla sin un índice GIN.

Resumen: Checklist de Optimización PostgreSQL

• ☑ PgBouncer en modo transaction para pooling
• ☑ Índices compuestos en orden correcto
• ☑ Partial indexes para subsets de datos
• ☑ pg_stat_statements habilitado
• ☑ Autovacuum configurado por tabla
• ☑ shared_buffers = 25% de RAM
• ☑ random_page_cost = 1.1 en SSD
• ☑ Monitorizar dead tuples semanalmente
• ☑ Sin N+1 en producción

PostgreSQL es un motor increíblemente capaz, pero su configuración default está pensada para entornos mínimos. Aplicar estos ajustes convierte una base de datos que "funciona" en una que rinde.