Volver al blog

Blog

Optimización de consultas SQL en PostgreSQL para aplicaciones SaaS

HlgCodes
['postgresql''sql''optimizacion''saas''performance']

El problema

Las consultas lentas son la causa principal de degradación de rendimiento en aplicaciones SaaS PostgreSQL. Un query que tarda 500ms en desarrollo puede convertirse en timeout de 30s en producción con datos reales y concurrencia. Los síntomas típicos: timeouts en endpoints críticos, CPU al 100% en la base de datos, y usuarios que experimentan lentitud intermitente que "se arregla sola" al reiniciar la app.

Por qué importa

En SaaS, la base de datos es el cuello de botella más común. Una query mal optimizada no solo afecta un endpoint: satura conexiones, dispara el pool, y cascada a timeouts en cascada. El coste no es solo técnico: cada segundo de latencia extra reduce conversiones un 7% (estudio de Google), y un timeout hace que el cliente pierda confianza y cancele su suscripción.

La solución

1. Diagnóstico con EXPLAIN ANALYZE (no EXPLAIN)

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM pedidos
WHERE cliente_id = 123
  AND creado_en > NOW() - INTERVAL '30 days';

Busca en el plan:

  • Seq Scan en tablas grandes → falta índice
  • Rows Removed by Filter alto → filtro ineficiente
  • Loops alto en Nested Loop → JOIN ineficiente
  • Actual Time vs Planned Rows dispares → estadísticas desactualizadas

2. Índices que sí importan

-- Índice compuesto para filtros + ordenamiento
CREATE INDEX idx_pedidos_cliente_fecha
  ON pedidos (cliente_id, creado_en DESC);

-- Índice parcial para consultas frecuentes
CREATE INDEX idx_pedidos_activos
  ON pedidos (cliente_id)
  WHERE estado = 'activo';

-- Índice covering para queries solo lectura
CREATE INDEX idx_pedidos_cover
  ON pedidos (cliente_id, creado_en)
  INCLUDE (total, estado);

3. Connection Pooling (PgBouncer)

# pgbouncer.ini
[databases]
saas_db = host=postgres port=5432 dbname=saas

[pgbouncer]
pool_mode = transaction
max_client_conn = 500
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 10

Regla: max_client_conn ≥ suma de max_connections de tus workers. default_pool_size ≈ cores de CPU.

4. Estadísticas frescas

-- Ejecutar después de cargas masivas o cambios de esquema
ANALYZE VERBOSE pedidos;

-- Autovacuum agresivo para tablas activas
ALTER TABLE pedidos SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

5. Patrones anti-patrones a evitar

-- ❌ Mal: SELECT * + filtro en app
SELECT * FROM pedidos WHERE cliente_id = 123;

-- ✅ Bien: Solo columnas necesarias
SELECT id, total, estado FROM pedidos WHERE cliente_id = 123;

-- ❌ Mal: OFFSET grande para paginación
SELECT * FROM pedidos ORDER BY creado_en LIMIT 20 OFFSET 10000;

-- ✅ Bien: Cursor-based pagination
SELECT * FROM pedidos
WHERE creado_en < '2024-01-15' AND id < 5000
ORDER BY creado_en DESC, id DESC LIMIT 20;

Errores comunes / Tips pro

Error Solución
LIMIT 1 sin índice Índice en columna filtrada + LIMIT 1
OR en WHERE UNION ALL o índice compuesto
ILIKE '%texto%' pg_trgm + GIN index o full-text search
COUNT(*) en tabla grande pg_class.reltuples o tabla de conteos

Queda pendiente

  • Particionamiento de tablas > 100GB
  • Read replicas para queries analíticas
  • Monitoreo automático de pg_stat_statements

Referencias

Volver al blog