Optimización de consultas en PostgreSQL: De lento a sub-milisegundo

El mes pasado recibí un mensaje de Slack a las 2 AM: "El dashboard está haciendo timeout". Me conecté por SSH al servidor de producción, abrí pg_stat_activity, y encontré una sola consulta escaneando 14 millones de filas para devolver 12 resultados. La solución fue un índice parcial que tomó 30 segundos en crear. La consulta pasó de 4.2 segundos a 0.3 milisegundos.

Eso es lo que pasa con el rendimiento de PostgreSQL. Los problemas casi nunca son exóticos. Son índices que faltan, estadísticas desactualizadas, consultas que tenían sentido cuando la tabla tenía 10,000 filas pero ahora tiene 10 millones. Las soluciones generalmente son simples — una vez que sabes dónde buscar.

Este post es todo lo que he aprendido sobre optimización de consultas en PostgreSQL al administrar bases de datos en producción. Sin teoría sin práctica. Cada técnica aquí me ha ahorrado tiempo real en sistemas reales.

La mentalidad de depuración: No adivines, mide#

El error más grande que veo que cometen los desarrolladores con consultas lentas es adivinar. "Quizás necesitamos un índice en esa columna". "Quizás el JOIN es lento". "Quizás deberíamos agregar más RAM".

Deja de adivinar. PostgreSQL tiene uno de los mejores analizadores de consultas de cualquier base de datos. Úsalo.

EXPLAIN — El plano#

Un EXPLAIN simple te muestra lo que PostgreSQL planea hacer, sin realmente ejecutar la consulta:

sql

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
   Index Cond: (customer_id = 42)

Esto te dice el plan — un index scan — y el costo estimado. Pero es una predicción. PostgreSQL podría estar completamente equivocado sobre la cantidad de filas. He visto estimaciones de "1 fila" cuando el resultado real eran 50,000 filas. Ese tipo de error de estimación se propaga en cadena hacia decisiones de plan terribles.

Usa EXPLAIN simple cuando quieras un vistazo rápido al plan sin realmente ejecutar la consulta. Esto importa cuando estás analizando un DELETE que modificaría datos, o una consulta que toma 30 minutos en ejecutar.

EXPLAIN ANALYZE — La verdad#

sql

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
                                                    (actual time=0.021..0.023 rows=1 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.085 ms
 Execution Time: 0.041 ms

Ahora ves el tiempo real de ejecución y la cantidad real de filas. Aquí es donde ocurre la depuración real. Compara rows=1 (estimado) con rows=1 (real) — coinciden, así que el planificador tomó una buena decisión. Cuando no coinciden, esa es tu primera pista.

Advertencia: EXPLAIN ANALYZE realmente ejecuta la consulta. Si estás analizando un UPDATE o DELETE, envuélvelo en una transacción:

sql

BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

EXPLAIN (ANALYZE, BUFFERS) — El panorama completo#

Esto es lo que realmente uso el 90% del tiempo:

sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'completed';

                                    QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=425.00..1893.25 rows=2150 width=48)
            (actual time=2.341..15.892 rows=2347 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=312 read=45
   ->  Bitmap Heap Scan on orders o  (cost=12.50..1450.75 rows=2150 width=32)
                                     (actual time=0.512..12.105 rows=2347 loops=1)
         Recheck Cond: (created_at > '2026-01-01'::date)
         Filter: (status = 'completed')
         Rows Removed by Filter: 893
         Heap Blocks: exact=189
         Buffers: shared hit=195 read=45
         ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..11.96 rows=3043 width=0)
                                                         (actual time=0.287..0.287 rows=3240 loops=1)
               Index Cond: (created_at > '2026-01-01'::date)
               Buffers: shared hit=12
   ->  Hash  (cost=287.50..287.50 rows=10000 width=24)
             (actual time=1.753..1.753 rows=10000 loops=1)
         Buffers: shared hit=117
         ->  Seq Scan on customers c  (cost=0.00..287.50 rows=10000 width=24)
                                      (actual time=0.008..0.892 rows=10000 loops=1)
               Buffers: shared hit=117
 Planning Time: 0.215 ms
 Execution Time: 16.147 ms

La salida de BUFFERS es crítica. shared hit=312 significa que 312 páginas vinieron del buffer cache (RAM). read=45 significa que 45 páginas tuvieron que leerse del disco. Si ves muchos read relativos a hit, tu shared_buffers podría ser demasiado pequeño, o la tabla es demasiado grande para quedarse en caché.

También uso FORMAT JSON cuando necesito pegar el plan en explain.dalibo.com para visualización. La vista de árbol hace que los planes complejos sean mucho más fáciles de leer:

sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

Opciones de EXPLAIN que realmente uso#

Esta es la consulta diagnóstica completa que ejecuto primero para cualquier investigación de consulta lenta:

sql

EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;

TIMING — tiempos por nodo (activado por defecto con ANALYZE, pero soy explícito)
VERBOSE — muestra listas de columnas de salida y nombres de tabla con esquema

Leyendo planes de consulta como un detective#

Cada plan de consulta es un árbol. PostgreSQL lo lee de abajo hacia arriba: los nodos más indentados se ejecutan primero, alimentando resultados hacia arriba. Entender los tipos de escaneo es la base de todo lo demás.

Seq Scan — El escaneo completo de tabla#

Seq Scan on orders  (cost=0.00..25000.00 rows=1000000 width=128)

El escaneo secuencial lee cada fila de la tabla. Para una tabla de un millón de filas, eso es cada página en disco.

Pero aquí está el matiz: Seq Scan no siempre es malo. Si estás seleccionando el 30% o más de la tabla, un escaneo secuencial es en realidad más rápido que un index scan porque la E/S secuencial es mucho más rápida que la E/S aleatoria. PostgreSQL lo sabe. Si está eligiendo un Seq Scan cuando tienes un índice, revisa qué tan selectiva es realmente tu cláusula WHERE.

Cuándo es un problema: cuando estás seleccionando una fracción diminuta de filas de una tabla grande y no hay índice.

Index Scan — La búsqueda dirigida#

Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=5 width=128)
  Index Cond: (customer_id = 42)

Va al índice B-tree, encuentra las entradas que coinciden, luego obtiene las filas reales del heap de la tabla. Cada obtención de fila es una operación de E/S aleatoria al heap. Esto es genial para consultas altamente selectivas pero cada obtención del heap tiene un costo.

Index Only Scan — El santo grial#

Index Only Scan using idx_orders_customer_total on orders  (cost=0.43..4.15 rows=5 width=12)
  Index Cond: (customer_id = 42)
  Heap Fetches: 0

Este es el tipo de escaneo más rápido. PostgreSQL obtiene todo lo que necesita solo del índice, sin tocar el heap de la tabla. Ves esto cuando todas las columnas que usas en SELECT y WHERE están en el índice.

La trampa: Heap Fetches: 0 significa que el visibility map está actualizado. Si tu tabla tiene muchas tuplas muertas (no se ha hecho vacuum recientemente), PostgreSQL igual tiene que verificar el heap para confirmar la visibilidad de las filas. Esta es una razón por la que autovacuum importa para el rendimiento, no solo para el espacio en disco.

Bitmap Scan — El punto medio#

Bitmap Heap Scan on orders  (cost=45.00..1250.00 rows=5000 width=128)
  Recheck Cond: (created_at > '2026-01-01')
  ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..43.75 rows=5000 width=0)
        Index Cond: (created_at > '2026-01-01')

Los bitmap scans son la respuesta de PostgreSQL al problema de "demasiadas filas para un index scan, muy pocas para un seq scan". Construye un bitmap de qué páginas contienen filas que coinciden, las ordena por ubicación física, y luego las obtiene en orden. Esto convierte E/S aleatoria en E/S secuencial.

A menudo verás bitmap scans cuando se combinan dos o más índices:

Bitmap Heap Scan on orders
  Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_orders_status
              Index Cond: (status = 'completed')
        ->  Bitmap Index Scan on idx_orders_created_at
              Index Cond: (created_at > '2026-01-01')

Este BitmapAnd combina dos índices separados. Es PostgreSQL diciendo "no tengo un índice compuesto, pero puedo combinar estos dos índices de una sola columna". Funciona, pero un índice compuesto apropiado sería más rápido.

Detectando estimaciones malas#

Lo número uno que busco en un plan de consulta son estimaciones desiguales:

Nested Loop  (cost=0.43..50.12 rows=1 width=128)
             (actual time=0.025..4521.337 rows=48753 loops=1)

El planificador estimó 1 fila. El resultado real fue 48,753 filas. Eso es un error de estimación de cinco órdenes de magnitud. El planificador eligió un Nested Loop porque pensó que estaba haciendo join contra 1 fila. Con 48,753 filas, un Hash Join habría sido órdenes de magnitud más rápido.

Causas comunes de estimaciones malas:

Estadísticas desactualizadas: Ejecuta ANALYZE en la tabla
Columnas correlacionadas: El planificador asume que los valores de las columnas son independientes. Si status = 'shipped' y created_at > '2026-01-01' están correlacionados (la mayoría de pedidos recientes están enviados), el planificador subestima la selectividad combinada
Funciones personalizadas en WHERE: El planificador usa una estimación de selectividad por defecto (generalmente 0.5% para igualdad, 33% para rango) cuando no puede analizar una función
Consultas parametrizadas con planes genéricos: Después de 5 ejecuciones, PostgreSQL puede cambiar a un plan genérico que no considera el valor real del parámetro

Cuando ves estimaciones malas, la solución generalmente es una de: ejecutar ANALYZE, crear estadísticas extendidas, reescribir la consulta, o usar un CTE como barrera de optimización.

sql

-- Create extended statistics for correlated columns
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;

Estrategia de índices: El índice correcto para el trabajo correcto#

Los índices no son gratis. Cada índice ralentiza las escrituras, consume espacio en disco y necesita mantenimiento. El objetivo no es "indexar todo" — es "indexar exactamente lo que necesitas".

B-tree — El caballo de batalla por defecto#

B-tree es el tipo por defecto y maneja la gran mayoría de casos. Soporta operadores de igualdad y rango (=, <, >, <=, >=, BETWEEN, IN, IS NULL).

sql

CREATE INDEX idx_orders_created_at ON orders (created_at);

El orden de las columnas en un índice B-tree importa enormemente para índices compuestos. La columna más a la izquierda es el ordenamiento primario, luego la siguiente, y así sucesivamente. Un índice en (a, b, c) puede responder eficientemente:

WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND b > 5
WHERE a = 1 ORDER BY b

No puede responder eficientemente:

WHERE b = 2 (salta la primera columna)
WHERE c = 3 (salta las primeras dos columnas)
WHERE a = 1 AND c = 3 (hueco en el medio — la condición de a se usa, la condición de c requiere filtro)

Piénsalo como una guía telefónica ordenada por apellido, luego por nombre. Puedes encontrar rápidamente todas las entradas "Smith", o "Smith, John" específicamente. Pero no puedes encontrar rápidamente todas las entradas "John" sin escanear todo el libro.

La regla: pon las condiciones de igualdad primero, luego las condiciones de rango, luego las columnas de ordenamiento.

sql

-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- Good: equality first, then range, then sort
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- Bad: range before equality
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

Covering Indexes con INCLUDE#

PostgreSQL 11+ te permite agregar columnas no clave a un índice con INCLUDE. Estas columnas se almacenan en las páginas hoja pero no son parte de la estructura B-tree. Habilitan index-only scans sin inflar el árbol:

sql

-- We frequently run:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
 
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);

Ahora PostgreSQL puede satisfacer toda la consulta solo desde el índice. Sin INCLUDE, necesitaría obtener total y status del heap. La diferencia en un caché frío puede ser dramática — he visto consultas pasar de 50ms a 0.2ms solo agregando columnas INCLUDE.

Índices parciales — Indexa solo lo que importa#

Esta es mi funcionalidad favorita de PostgreSQL y la que la mayoría de desarrolladores no conoce.

sql

-- Only 2% of orders are 'pending', but we query them constantly
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

Este índice es diminuto comparado con un índice completo en created_at. Solo contiene filas donde status = 'pending'. Las consultas que coinciden con la cláusula WHERE usan este índice pequeño y rápido. Las consultas que no coinciden lo ignoran.

Ejemplo real de producción: tenía una tabla sessions con 50 millones de filas. Solo unas 200,000 estaban activas (no expiradas). Un índice completo en user_id era de 1.2 GB. Un índice parcial WHERE expires_at > now() era de 8 MB. La consulta pasó de 12ms a 0.1ms porque el índice completo cabía en caché.

sql

-- Another common pattern: soft deletes
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- Unique constraint only on active records
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

Ese último es increíblemente útil. Te permite tener una restricción de email único en usuarios activos mientras permites que el mismo email aparezca en registros borrados.

Índices GIN — Búsqueda de texto completo y JSONB#

GIN (Generalized Inverted Index) es la respuesta cuando necesitas buscar dentro de valores — arrays, documentos JSONB, o vectores de búsqueda de texto completo.

sql

-- JSONB containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- Now this is fast:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

Los índices GIN son grandes y lentos de construir, pero extremadamente rápidos para consultar. Valen la pena para cargas de trabajo intensivas en lectura con consultas de contención complejas.

Para JSONB, también existe jsonb_path_ops que es más pequeño y rápido para consultas de contención @>, pero no soporta operadores de existencia (?, ?|, ?&):

sql

CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);

Índices GiST — Tipos geométricos y de rango#

GiST (Generalized Search Tree) maneja tipos de datos que se superponen: formas geométricas, rangos, búsqueda de texto completo (alternativa a GIN, más pequeño pero más lento).

sql

-- IP range lookups
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- Temporal ranges (scheduling conflicts)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- Find overlapping bookings:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

Índices Hash — De nicho pero útiles#

Los índices hash solo son útiles para comparaciones de igualdad. Desde PostgreSQL 10 están registrados en WAL y son seguros ante crashes. Son más pequeños que B-tree para columnas anchas y ligeramente más rápidos para igualdad pura:

sql

-- Good for equality-only lookups on large text columns
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- This is fast:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- This CANNOT use the hash index:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

En la práctica, rara vez uso índices hash. B-tree maneja la igualdad bien, y la incapacidad de hacer consultas de rango u ordenamiento hace que los índices hash sean demasiado inflexibles para la mayoría de casos de uso reales.

Cuando los índices perjudican#

Cada índice tiene un costo:

Amplificación de escritura: Cada INSERT actualiza cada índice en la tabla. Una tabla con 8 índices significa 8 escrituras adicionales por INSERT
Updates HOT bloqueados: Los updates Heap-Only Tuple (HOT) son una optimización mayor donde PostgreSQL puede actualizar una fila sin actualizar los índices, pero solo si ninguna columna indexada cambió. Más índices = más chances de bloquear updates HOT
Sobrecarga de vacuum: Más índices significa que el vacuum toma más tiempo
Sobrecarga del planificador: Más índices significa que el planificador tiene más opciones que evaluar

Regularmente audito los índices en tablas de producción:

sql

-- Find unused indexes
SELECT
  schemaname, tablename, indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- exclude primary key and unique constraints
  )
ORDER BY pg_relation_size(indexrelid) DESC;

He encontrado índices de varios gigabytes que fueron creados durante una migración única y nunca se usaron de nuevo. Eliminarlos aceleró las escrituras notablemente.

sql

-- Find duplicate indexes (same columns, same order)
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

El problema de consultas N+1#

Todo desarrollador de ORM se topa con esto eventualmente. Es el problema de rendimiento más común que depuro.

Cómo se ve el N+1#

python

# Python / SQLAlchemy — the classic trap
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # This triggers a separate query for EACH order
    print(f"Customer: {order.customer.name}")

Esto genera:

sql

-- Query 1: Get all pending orders
SELECT * FROM orders WHERE status = 'pending';
 
-- Query 2: Get customer for order 1
SELECT * FROM customers WHERE id = 101;
 
-- Query 3: Get customer for order 2
SELECT * FROM customers WHERE id = 102;
 
-- ...Query 502: Get customer for order 500
SELECT * FROM customers WHERE id = 600;

501 consultas en lugar de 1 o 2. Cada consulta es rápida individualmente, quizás 0.5ms. Pero 501 de ellas suman 250ms de solo tiempo de base de datos, más la latencia de round-trip de red por cada una.

Detectando N+1 en los logs#

La forma más rápida de atrapar consultas N+1 es habilitar el logging de sentencias temporalmente:

sql

-- Log all queries that take more than 0ms (i.e., all queries)
SET log_min_duration_statement = 0;
 
-- Better for production: log only slow queries
ALTER SYSTEM SET log_min_duration_statement = 50;  -- 50ms threshold
SELECT pg_reload_conf();

Luego mira los logs. El N+1 es inconfundible — verás cientos de consultas idénticas con diferentes valores de parámetros en rápida sucesión.

Un enfoque más dirigido para desarrollo:

sql

-- Enable auto_explain for slow queries
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;  -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;

Esto registra la salida completa de EXPLAIN ANALYZE para cualquier consulta que supere los 100ms, incluyendo consultas dentro de funciones.

La solución: Eager loading o JOINs#

Enfoque ORM — dile al ORM que cargue los datos relacionados por adelantado:

python

# SQLAlchemy — joinedload fetches customers in the same query
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

Enfoque SQL directo — simplemente usa un JOIN:

sql

SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

Una consulta. Listo.

JOINs vs consultas múltiples#

Hay un debate válido aquí. A veces dos consultas son mejores que un JOIN:

sql

-- Approach 1: Single JOIN (might produce duplicate data if one-to-many)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- Approach 2: Two queries (less data transfer if orders have many columns)
SELECT * FROM orders WHERE status = 'pending';
-- application collects order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);

El enfoque de JOIN duplica los datos del pedido por cada ítem. Si cada pedido tiene 20 ítems y la fila del pedido es ancha, eso es 20x transferencia de datos. El enfoque de dos consultas envía cada pedido exactamente una vez.

Mi regla: usa JOINs para relaciones uno a uno, considera consultas separadas para uno a muchos cuando el lado "uno" es ancho. Pero siempre haz benchmark — el round-trip de red de una segunda consulta frecuentemente cuesta más que los datos duplicados.

Reescrituras comunes de consultas#

Algunas consultas son lentas no por índices faltantes sino por cómo están escritas. El planificador de PostgreSQL es bueno, pero no es magia.

Subquery vs JOIN vs CTE#

Estos tres enfoques pueden producir planes muy diferentes:

sql

-- Subquery in WHERE — often fine, sometimes terrible
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN — usually the best choice
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE — materialized in PG 11 and below, optimized in PG 12+
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

En PostgreSQL 12+, los CTEs generalmente se inlinean (el planificador los trata como subqueries), así que el rendimiento es idéntico. Pero en PostgreSQL 11 y anteriores, los CTEs son barreras de optimización — el planificador los materializa y no puede empujar predicados a través de ellos. Si todavía estás en PG 11, evita los CTEs para consultas críticas de rendimiento.

EXISTS vs IN vs JOIN#

Esto surge constantemente:

sql

-- EXISTS — stops at first match, great for checking existence
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- IN with subquery — builds full result set first
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN — can produce duplicates if returns has multiple rows per order
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

Para tablas grandes, EXISTS a menudo gana porque hace cortocircuito. La versión IN tiene que construir la lista completa de order_ids de devoluciones antes de filtrar. La versión JOIN puede producir duplicados, requiriendo DISTINCT que agrega un paso de sort o hash.

Mi default: usa EXISTS cuando verificas la existencia de filas relacionadas. Es lo más semánticamente claro y generalmente lo más rápido.

Pero hay un contraejemplo. Si el resultado del subquery es pequeño y lo necesitas para múltiples condiciones:

sql

-- If the subquery returns few rows, IN is perfectly fine
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);

Evitando SELECT *#

Esto no es solo una preferencia de estilo de código. Tiene implicaciones reales de rendimiento:

sql

-- Bad: fetches all 25 columns including a TEXT column with kilobytes of data
SELECT * FROM products WHERE category = 'electronics';
 
-- Good: fetches only what you need
SELECT id, name, price FROM products WHERE category = 'electronics';

Con SELECT *:

No puede usar index-only scans (todas las columnas necesitarían estar en el índice)
Transfiere más datos por la red
Usa más memoria para sorting y hashing
Si alguien agrega una columna BYTEA de 10MB después, tus consultas existentes silenciosamente se vuelven más lentas

Window functions vs subqueries#

Las window functions son una de las funcionalidades más poderosas de PostgreSQL y casi siempre superan a los subqueries correlacionados:

sql

-- Slow: correlated subquery runs once per row
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- Fast: window functions compute in a single pass
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

Otro patrón común — obtener la fila más reciente por grupo:

sql

-- Slow: correlated subquery
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- Fast: DISTINCT ON (PostgreSQL-specific, extremely useful)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- Also fast: ROW_NUMBER window function
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

DISTINCT ON es mi opción preferida para este patrón. Es conciso, legible, y PostgreSQL lo optimiza bien con el índice correcto:

sql

CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);

Paginación bien hecha#

OFFSET es una trampa para datasets grandes:

sql

-- Page 1: fast
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- Page 500: slow — PostgreSQL must scan and discard 9,980 rows
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- Page 5000: very slow — scans 99,980 rows to return 20
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

La solución es paginación por keyset (también llamada paginación basada en cursor):

sql

-- First page
SELECT * FROM products ORDER BY id LIMIT 20;
 
-- Next page: use the last id from the previous page
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;

Esto siempre es rápido porque usa el índice para saltar directamente a la posición correcta, sin importar en qué "página" estés. El trade-off es que no puedes saltar a un número de página arbitrario, pero para infinite scroll o UIs de "siguiente página", la paginación por keyset es estrictamente superior.

Para órdenes de clasificación complejos:

sql

-- Keyset pagination with multiple sort columns
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;

Esto usa comparación de row-value, que PostgreSQL maneja eficientemente con un índice compuesto en (price, id).

Estadísticas de tabla y vacuuming#

El planificador de consultas de PostgreSQL toma decisiones basadas en estadísticas sobre tus datos. Estadísticas malas llevan a planes malos. Así de simple.

ANALYZE: Actualiza las estadísticas#

sql

-- Analyze a single table
ANALYZE orders;
 
-- Analyze the entire database
ANALYZE;
 
-- See current statistics for a column
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

El valor de correlation es particularmente interesante. Va de -1 a 1 y mide qué tan bien el orden físico de las filas coincide con el orden lógico de la columna. Una correlación cercana a 1 o -1 significa que los datos están físicamente ordenados en esa columna, haciendo los range scans muy eficientes (E/S secuencial). Una correlación cercana a 0 significa E/S aleatoria para consultas de rango.

sql

-- Increase statistics target for columns with skewed distributions
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

El objetivo de estadísticas por defecto es 100, lo que significa que PostgreSQL muestrea 300 * 100 = 30,000 filas. Para columnas con muchos valores distintos o distribuciones sesgadas, aumentar esto a 500 o 1000 le da al planificador mejores datos a costa de tiempos de ANALYZE ligeramente más largos.

Afinamiento de Autovacuum#

Autovacuum hace dos cosas: reclama espacio de tuplas muertas (filas borradas o actualizadas) y actualiza estadísticas. En tablas ocupadas, la configuración por defecto de autovacuum frecuentemente es demasiado conservadora.

sql

-- Check autovacuum status
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Si ves tablas con millones de tuplas muertas y el último vacuum fue hace horas, tu autovacuum se está quedando atrás.

Para tablas de alta rotación (como sesiones, colas de jobs, o métricas), configuro ajustes de autovacuum por tabla:

sql

-- More aggressive autovacuum for high-write tables
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- vacuum at 1% dead tuples (default 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- analyze at 0.5% changes (default 10%)
  autovacuum_vacuum_cost_delay = 2           -- faster vacuum (default 2ms in newer PG)
);

Table Bloat#

Cuando PostgreSQL actualiza una fila, no la modifica en el mismo lugar — crea una nueva versión y marca la vieja como muerta. Vacuum reclama las filas muertas, pero el espacio solo se reutiliza por esa tabla. El archivo de la tabla en disco no se reduce.

Con el tiempo, una tabla muy actualizada puede tener bloat significativo — la tabla es mucho más grande en disco de lo que los datos vivos requieren. Esto significa más páginas que escanear, más E/S, más presión sobre el caché.

sql

-- Estimate table bloat (simplified version)
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

Para tablas severamente infladas, la opción nuclear es VACUUM FULL — reescribe toda la tabla. Pero toma un lock exclusivo, así que no puedes hacerlo en una tabla de producción en vivo sin downtime. El mejor enfoque es pg_repack, que hace lo mismo sin bloquear:

bash

pg_repack --table orders --no-kill-backend -d mydb

Connection Pooling#

Esto es algo que sorprende a muchos desarrolladores: las conexiones de PostgreSQL son caras. Cada conexión genera un nuevo proceso (no un thread), consume alrededor de 5-10 MB de memoria, y tiene una sobrecarga de fork no trivial.

El max_connections por defecto es 100. Si tienes un servidor de aplicación con 20 workers, cada uno abriendo 5 conexiones, ya estás en tu límite. Agrega un procesador de jobs en segundo plano, una herramienta de monitoreo, y una migración corriendo en algún lado, y estás en problemas.

Por qué necesitas un connection pooler#

Sin un pooler, si tu aplicación necesita manejar 500 solicitudes concurrentes, necesitas 500 conexiones PostgreSQL. Son 5 GB de memoria solo para la sobrecarga de conexiones, y el rendimiento de PostgreSQL se degrada significativamente más allá de unos cientos de conexiones debido a la sobrecarga de gestión de procesos.

Con PgBouncer frente a PostgreSQL, esas 500 conexiones de aplicación se mapean a quizás 20 conexiones PostgreSQL reales. El pooler encola solicitudes cuando todas las conexiones a la base de datos están ocupadas.

Configuración de PgBouncer#

ini

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; Pool mode
pool_mode = transaction
 
; Pool sizing
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

Modo Transaction vs Session#

Modo transaction (pool_mode = transaction): Las conexiones se devuelven al pool después de cada transacción. Esto es lo que quieres el 95% del tiempo. Máxima reutilización de conexiones.
Modo session (pool_mode = session): Las conexiones se mantienen durante toda la sesión del cliente. Usa esto si necesitas sentencias preparadas, comandos SET, LISTEN/NOTIFY, u otras funcionalidades a nivel de sesión.
Modo statement (pool_mode = statement): Las conexiones se devuelven después de cada sentencia. Demasiado restrictivo para la mayoría de aplicaciones — ni siquiera puedes usar transacciones explícitas.

La trampa con el modo transaction: no puedes usar sentencias preparadas (son estado a nivel de sesión), no puedes usar SET para variables de sesión, y LISTEN/NOTIFY no funcionará como esperas. La mayoría de ORMs tienen una forma de deshabilitar las sentencias preparadas.

Para Node.js con el driver pg:

javascript

const pool = new Pool({
  host: '127.0.0.1',
  port: 6432,  // PgBouncer port, not PostgreSQL port
  database: 'mydb',
  max: 20,     // matches PgBouncer default_pool_size
  statement_timeout: 30000,
  // Disable prepared statements for PgBouncer transaction mode
  prepare: false,
});

Fórmula de dimensionamiento del pool#

Hay una fórmula común para el tamaño óptimo del pool de conexiones PostgreSQL:

optimal_connections = (core_count * 2) + effective_spindle_count

Para un servidor moderno con 4 cores y un SSD (1 spindle efectivo):

optimal = (4 * 2) + 1 = 9

Esto parece contraintuitivamente bajo. Pero PostgreSQL está CPU-bound en la mayoría del hardware moderno (los SSDs son lo suficientemente rápidos como para que la E/S rara vez sea cuello de botella). Más conexiones que esto lleva a sobrecarga de cambio de contexto que en realidad ralentiza las cosas.

En la práctica, generalmente configuro default_pool_size a 2-3x este número para manejar ráfagas de tráfico, con el entendimiento de que en pico de carga, algunas consultas esperarán en la cola de PgBouncer en lugar de todas golpear PostgreSQL simultáneamente.

Checklist práctico: Los pasos exactos para cada consulta lenta#

Este es mi proceso real cuando recibo un reporte de "esta consulta es lenta". Sigo estos pasos en este orden, cada vez.

Paso 1: Obtener la consulta real#

No "el endpoint es lento" — el SQL real. Si estás usando un ORM, habilita el logging de consultas:

sql

-- Temporarily log all queries over 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

O revisa pg_stat_statements para las consultas con más tiempo total:

sql

-- Top 10 queries by total execution time
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

pg_stat_statements es la extensión más valiosa para rendimiento de PostgreSQL. Si no la estás usando, instálala ahora:

sql

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Paso 2: Ejecutar EXPLAIN (ANALYZE, BUFFERS)#

sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <the slow query>;

Busca:

Estimaciones de filas malas — filas reales muy diferentes de las filas estimadas
Seq Scans en tablas grandes — posible índice faltante
Nested Loops con muchas filas — debería ser un Hash Join o Merge Join
Lecturas de buffer altas — caché frío o tabla demasiado grande
Operaciones de sort derramando a disco — aumenta work_mem o agrega índice para el sort

Paso 3: Verificar estadísticas de tabla#

sql

-- When were stats last updated?
SELECT
  relname,
  last_analyze,
  last_autoanalyze,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

Si last_analyze es viejo o n_dead_tup es alto relativo a n_live_tup, ejecuta:

sql

ANALYZE orders;

Luego vuelve a ejecutar EXPLAIN ANALYZE. Si el plan cambia, las estadísticas desactualizadas eran el problema.

Paso 4: Verificar índices existentes#

sql

-- What indexes exist on this table?
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

Quizás el índice existe pero no se está usando. Quizás el orden de las columnas es incorrecto para tu consulta.

Paso 5: Crear o modificar índices#

Basándote en el plan de consulta, crea el índice apropiado. Prueba con EXPLAIN ANALYZE antes y después.

sql

-- Create the index concurrently (doesn't lock the table)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- Verify the index is used
EXPLAIN (ANALYZE, BUFFERS) <the slow query>;

Siempre usa CONCURRENTLY en tablas de producción. Un CREATE INDEX regular toma un lock completo de tabla que bloquea todas las escrituras.

Paso 6: Considerar reescrituras de consulta#

Si el índice existe y las estadísticas están frescas pero la consulta sigue lenta, mira la consulta en sí:

¿Se puede reescribir un subquery como JOIN?
¿OFFSET está causando problemas? Cambia a paginación por keyset
¿Estás seleccionando más columnas de las necesarias?
¿Puede un subquery correlacionado convertirse en una window function?
¿Un CTE está impidiendo que el planificador optimice?

Paso 7: Verificar configuración del servidor#

Para patrones de consulta específicos, la configuración del servidor importa:

sql

-- work_mem: memory for sorts and hash joins (per operation, not per connection)
-- Default is 4MB, which is too low for complex queries
SET work_mem = '64MB';  -- try this and re-run EXPLAIN ANALYZE
 
-- effective_cache_size: tells planner how much disk cache to expect
-- Set to ~75% of total RAM
SHOW effective_cache_size;
 
-- random_page_cost: ratio of random I/O to sequential I/O
-- Default is 4.0, set to 1.1-1.5 for SSDs
SHOW random_page_cost;

Si cambiar work_mem arregla un problema de sort-to-disk, considera aumentarlo globalmente. Pero ten cuidado — es por operación, no por conexión. Una consulta compleja con 10 operaciones de sort y work_mem = 256MB podría usar 2.5 GB para una sola consulta.

Paso 8: Monitorear después de la corrección#

No solo arregles y olvides. Verifica que la corrección se mantenga:

sql

-- Reset pg_stat_statements to see fresh data after your changes
SELECT pg_stat_statements_reset();
 
-- Check back in an hour/day
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

Bonus: Quick wins que aplico a cada nueva base de datos#

Estas son configuraciones y prácticas que aplico a cada base de datos PostgreSQL que configuro, antes de que surja cualquier problema de rendimiento:

sql

-- 1. Install pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. Enable slow query logging (50ms is my threshold)
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. SSD-appropriate settings
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. Increase work_mem from the anemic default
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. Set effective_cache_size to 75% of RAM
-- For a 16GB server:
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. Set shared_buffers to 25% of RAM
-- For a 16GB server:
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. Improve autovacuum responsiveness
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- Apply changes
SELECT pg_reload_conf();
-- Note: shared_buffers requires a restart

Y una consulta de monitoreo que ejecuto semanalmente:

sql

-- Tables that need attention
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Reflexiones finales#

La optimización de consultas en PostgreSQL no es un arte oscuro. Es un proceso sistemático:

Mide — no adivines. EXPLAIN (ANALYZE, BUFFERS) es tu mejor amigo.
Entiende el plan — aprende a leer tipos de escaneo, tipos de join y estimaciones de filas.
Indexa estratégicamente — el índice correcto en las columnas correctas, en el orden correcto. Los índices parciales y covering indexes son superpoderes subutilizados.
Escribe mejores consultas — EXISTS sobre IN para verificaciones de existencia, paginación por keyset sobre OFFSET, window functions sobre subqueries correlacionados.
Mantén la base de datos — afinamiento de autovacuum, actualizaciones de estadísticas, connection pooling.
Monitorea continuamente — pg_stat_statements te dice dónde tu base de datos pasa su tiempo. Revísalo regularmente.

La diferencia entre una consulta que toma 4 segundos y una que toma 0.3 milisegundos rara vez es el hardware. Casi siempre es conocimiento — saber dónde buscar y qué cambiar. Y ahora sabes dónde buscar.