PostgreSQL: Cómo hacer que consultas en tablas de millones de filas dejen de ser un dolor de cabeza

Si ya has peleado con tablas grandes en MySQL o SQL Server, sabes perfectamente que el rendimiento no es un “nice to have”: es supervivencia. En PostgreSQL, cuando una tabla de logs, auditoría o transacciones cruza cómodamente los 20–30 millones de filas, una consulta mal pensada no solo se pone lenta… puede poner a gemir el CPU, saturar el disco y dejar bloat por todos lados que afecta incluso a otras bases del mismo clúster.

Esta guía va dirigida a quienes ya trabajan en producción y quieren mejoras concretas, rápidas de aplicar y que realmente se noten, sin teoría pesada ni academicismos.

Empecemos:

1. SELECT: sé radicalmente selectivo con las columnas

PostgreSQL usa TOAST para sacar fuera de la página principal de la tabla esos valores gordos (TEXT largos, JSONB, BYTEA, etc.). Es genial… hasta que tú los pides todos.

Error muy típico que vemos todos los días:

SELECT *
FROM facturas
WHERE cliente_id = 500;

Si facturas tiene descripcion TEXT, xml_factura XML, metadata JSONB o similar, estás forzando al motor a:

Leer páginas extras del heap
Descomprimir TOASTs que quizás ni ibas a usar
Mandar por red kilobytes (o megas) que tu aplicación va a ignorar

La versión adulta y responsable:

SELECT id, numero_factura, fecha_emision, total, estado
FROM facturas
WHERE cliente_id = 500;

¿Qué ganas de verdad?

Menos presión en memoria del backend
Mucho menos tráfico de red (importante si estás en RDS o similar con costo por GB transferido)
Más chances de que el índice cubra toda la consulta (index-only scan)
Bastante menos I/O aleatorio en discos

Cuando la tabla ya tiene millones de filas, esto deja de ser micro-optimización y pasa a ser diseño sensato.

2. SARGable conditions: deja que el índice haga su trabajo

El query planner de Postgres solo puede aprovechar un índice si la condición del WHERE es “amigable” con él (SARGable = Search ARGument able).

Clásico anti-patrón que mata rendimiento:

SELECT COUNT(*)
FROM logs
WHERE DATE(created_at) = '2026-02-23';

Cada fila tiene que pasar por la función DATE(). Adiós índice → hola Sequential Scan casi garantizado.

Versión que sí usa índice (y vuela):

SELECT COUNT(*)
FROM logs
WHERE created_at >= '2026-02-23 00:00:00'
  AND created_at  < '2026-02-24 00:00:00';

El índice B-tree normal sobre created_at ahora sí puede hacer su magia.

¿Y si de verdad necesitas la función?

PostgreSQL te da una salida muy potente: índices de expresión.

CREATE INDEX idx_logs_dia ON logs (DATE(created_at));

O incluso con truncado más agresivo si solo te interesa mes/año:

CREATE INDEX idx_logs_mes ON logs (DATE_TRUNC('month', created_at));

Es una de las cosas que más me gustan de Postgres: te deja indexar casi cualquier expresión que uses en el WHERE.

3. Sequential Scan no es automáticamente el diablo

Ver en el plan

Seq Scan on logs  (cost=...)

no significa que estés haciendo todo mal.

Está bien cuando:

La consulta devuelve un porcentaje alto de la tabla (digamos > 5–15 %)
La selectividad es bajísima
El planner calcula (correctamente) que saltar páginas del índice sería más caro que leer todo secuencialmente

Se vuelve problemático cuando:

Esperas pocas filas
La tabla es gigante
Y aun así te hace Seq Scan

Primer paso rápido: refrescar estadísticas

ANALYZE logs;
-- o mejor aún, si puedes permitirte un ratito:
VACUUM ANALYZE logs;

Muchas veces el planner estaba tomando decisiones tontas solo porque las estadísticas estaban desactualizadas.

4. Olvídate del OFFSET para paginación profunda: usa keyset (o seek method)

El clásico que mata bases:

SELECT id, evento, created_at
FROM logs
ORDER BY id DESC
OFFSET 1000000
LIMIT 50;

Para saltar un millón de filas, Postgres tiene que:

Encontrarlas
Ordenarlas
Descartarlas una por una

Eso escala pésimo. A partir de la página 10.000 ya duele.

Solución pragmática y escalable: paginación por ancla (keyset)

Página 1:

SELECT id, evento, created_at
FROM logs
ORDER BY id DESC
LIMIT 50;

Digamos que el último id que te devolvió es 105432.

Página 2:

SELECT id, evento, created_at
FROM logs
WHERE id < 105432
ORDER BY id DESC
LIMIT 50;

Ventajas reales:

Tiempo casi constante independientemente de la página
Usa el índice de forma directa (sin tener que contar filas previas)
Mucho más resistente a inserts concurrentes (menos riesgo de saltos/duplicados)

Requisito indispensable: la columna de orden debe tener índice y ser única/determinista (id autoincremental, created_at + id, etc.).

5. Index-Only Scans + INCLUDE: menos I/O es felicidad

Un Index-Only Scan es cuando Postgres encuentra todo lo que necesita directamente en el índice y ni toca la tabla (heap).

Ejemplo práctico:

CREATE INDEX idx_ventas_cliente_info
ON ventas (cliente_id)
INCLUDE (monto_total, fecha_emision, estado);

Consulta:

SELECT cliente_id, monto_total, fecha_emision
FROM ventas
WHERE cliente_id = 98765;

Si el Visibility Map está al día (gracias a un VACUUM decente), Postgres lee solo el índice → I/O brutalmente reducido.

6. BRIN: el índice pequeño y sorprendentemente útil para tablas enormes

Los índices B-tree son geniales… hasta que la tabla tiene cientos de millones de filas y el índice mismo se vuelve gigante y caro de mantener.

BRIN (Block Range INdex) guarda resúmenes por rangos de páginas físicas, no por fila individual.

CREATE INDEX idx_logs_created_brin
ON logs USING BRIN (created_at);

Cuándo BRIN brilla:

Tablas append-only o casi (logs, mediciones, transacciones por fecha)
Datos físicamente ordenados (o casi) por el campo indexado
Filtrado por rangos grandes (días, meses, años)

Cuándo NO usarlo:

Búsquedas puntuales por UUID o email
Tablas con updates/deletes frecuentes y aleatorios

BRIN ocupa muy poco espacio, se crea rapidísimo y en logs masivos puede darte un speedup sorprendente con casi cero costo.

7. MVCC y el drama del bloat: no ignores autovacuum

PostgreSQL es MVCC puro. Cada UPDATE o DELETE genera una nueva versión de la fila; la antigua queda “muerta” hasta que autovacuum la reclama.

Si autovacuum no sigue el ritmo:

La tabla y los índices se inflan (bloat)
Lecturas tienen que revisar más versiones obsoletas (“ Heap Fetches” innecesarios)
Rendimiento general se va al suelo

Optimizar consultas sin monitorear y tunear VACUUM es ponerle una curita a una hemorragia.

Revisa al menos:

SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_all_tables
WHERE relname = 'logs'
ORDER BY n_dead_tup DESC;

Y considera tunear autovacuum_vacuum_scale_factor, autovacuum_vacuum_cost_limit, etc. en tablas muy activas.

8. La herramienta que nunca debes saltarte: EXPLAIN ANALYZE

Nunca optimices guiándote por corazonadas.

Ejecuta siempre:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT ...

Fíjate en:

Diferencia grande entre Rows Removed by Filter / Estimated Rows
Actual time vs cost (¿el planner se equivocó mucho?)
Loops altos –> nested loops caros
Shared hit/read –> cuánto I/O real estás haciendo

Decisiones basadas en números, no en «a mí me parece que…»

Caso real: de segundos a milisegundos (sucede más de lo que crees)

Consulta problemática que encontramos hace poco:

SELECT *
FROM transacciones
WHERE EXTRACT(MONTH FROM fecha) = 1;

Problemas:

Función sobre columna –> adiós índice
Trae enero de todos los años
Casi seguro Sequential Scan

Versión corregida:

SELECT id, monto, referencia
FROM transacciones
WHERE fecha >= '2026-01-01'
  AND fecha  < '2026-02-01';

Resultado típico (tabla ~80M filas):

Index Scan o Bitmap Index Scan
I/O reducido drásticamente
Tiempo: de 4–12 segundos –> 15–80 ms

Cierre: el mindset que realmente cambia el juego

Optimizar PostgreSQL en tablas grandes no se trata de coleccionar truquitos de Reddit. Se trata de entender cómo razona el query planner y diseñar con eso en mente:

Escribe condiciones WHERE que el índice pueda usar sin sudar
Pide solo las columnas que de verdad necesitas
Aprovecha INCLUDE para index-only scans cuando el patrón lo justifique
Considera BRIN en datasets masivos y ordenados naturalmente
Trata el VACUUM y las estadísticas como parte del desarrollo, no como «mantenimiento opcional»

Cuando interiorizas estos conceptos, pasar de cientos de miles a decenas de millones de filas deja de ser una pesadilla y se convierte en una cuestión de aplicar buenas decisiones de diseño desde el principio.

¿Tienes una tabla que ya te está haciendo sufrir? Prueba una de estas cosas esta semana y cuéntame en los comentarios cómo te fue. Muchas veces el 80 % de la ganancia viene del primer 20 % de esfuerzo. Sigamos codificando.