Optimización de PostgreSQL: De consultas lentas a búsquedas instantáneas

Cuando una tabla crece, las búsquedas que antes respondían al instante empiezan a tardar. Dos casos son especialmente comunes en sistemas de gestión: buscar clientes o productos escribiendo solo una parte del nombre, y consultar tablas enormes de registros por fecha, como facturas históricas o auditoría. PostgreSQL tiene un índice pensado para cada caso: pg_trgm con GIN para texto y BRIN para datos ordenados por tiempo.

Primero mide, después indexa

Antes de crear un índice, comprueba que de verdad hace falta. EXPLAIN ANALYZE muestra cómo ejecuta PostgreSQL la consulta y cuánto tarda:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, nombre FROM clientes WHERE nombre ILIKE '%perez%';

Si ves Seq Scan sobre una tabla grande y un tiempo alto, PostgreSQL está leyendo todas las filas. Ese es el momento de pensar en un índice. Si la tabla es pequeña, el índice no ayudará y solo añadirá costo.

Búsqueda por texto parcial con pg_trgm y GIN

Un índice normal (B-tree) no sirve para ILIKE '%perez%', porque el texto buscado puede estar en cualquier parte. La extensión pg_trgm divide el texto en trozos de tres letras (trigramas) y permite indexarlos:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY idx_clientes_nombre_trgm
    ON clientes USING gin (nombre gin_trgm_ops);

Con ese índice, las consultas LIKE e ILIKE con comodines al inicio pueden usar el índice. También habilita la búsqueda por parecido, útil para tolerar errores de tipeo:

-- Umbral de parecido (0 a 1); el valor por defecto es 0.3
SET pg_trgm.similarity_threshold = 0.4;

SELECT nombre, similarity(nombre, 'Hernandes') AS parecido
FROM clientes
WHERE nombre % 'Hernandes'
ORDER BY parecido DESC
LIMIT 10;

Cosas a tener en cuenta

• El índice GIN ocupa espacio y hace más lentas las escrituras. Úsalo en columnas que se buscan mucho, no en todas.
• Con textos de una o dos letras el índice ayuda poco. Conviene exigir un mínimo de caracteres en el buscador.
• CREATE INDEX CONCURRENTLY evita bloquear la tabla mientras se crea, pero tarda más y no se puede ejecutar dentro de una transacción.

Tablas de registros con BRIN

Las tablas de auditoría, bitácoras y facturas históricas crecen siempre hacia adelante: las filas nuevas llevan fechas más recientes. Para ese patrón, un índice BRIN guarda solo el rango de valores de cada bloque de páginas, no cada fila, por lo que es muy pequeño:

CREATE INDEX idx_auditoria_creado_en_brin
    ON auditoria USING brin (creado_en)
    WITH (pages_per_range = 64);

SELECT count(*)
FROM auditoria
WHERE creado_en >= date '2026-01-01' AND creado_en < date '2026-02-01';

Cuándo funciona bien

BRIN solo es efectivo cuando el orden físico de las filas se parece al orden de la columna. Una columna de fecha de inserción cumple eso casi siempre. Una columna de valores sin relación con el orden de inserción, como un nombre, no sirve.

Puedes comprobarlo con la correlación estadística:

SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'auditoria' AND attname = 'creado_en';

Un valor cercano a 1 (o a -1) indica que BRIN funcionará bien.

Compara tamaños

La diferencia de espacio es lo que hace atractivo a BRIN en tablas muy grandes. Puedes medirla en tu propia base:

SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS tamano
FROM pg_class
WHERE relname IN ('idx_auditoria_creado_en_brin', 'auditoria');

Mantén los índices bajo control

Cada índice cuesta espacio y ralentiza las escrituras. Revisa cada cierto tiempo cuáles no se usan:

SELECT relname AS tabla, indexrelname AS indice, idx_scan AS usos
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;

Un índice con cero usos durante un periodo largo es candidato a eliminarse, siempre después de confirmar que ninguna consulta ocasional (un reporte mensual, por ejemplo) depende de él.

Resumen

Si tu sistema se está volviendo lento y no sabes por dónde empezar, en RFJ Software ofrecemos revisión y optimización de PostgreSQL. Con un par de consultas lentas reales ya se puede decir mucho.