Índices BRIN vs B-Tree en PostgreSQL: guía práctica y tuning
Introducción: el dilema del índice correcto
Cuando administramos una base de datos PostgreSQL, una de las decisiones más críticas es elegir el tipo de índice adecuado para cada consulta. La mayoría de los DBA recurre de forma predeterminada al índice B-Tree, y con razón: es versátil, eficiente para búsquedas por igualdad y rangos, y funciona bien en la mayoría de los escenarios. Sin embargo, en tablas con millones de filas y datos que siguen un orden natural (como timestamps, IDs secuenciales o columnas de tipo serie), el índice BRIN (Block Range Index) puede ofrecer una reducción drástica del tamaño del índice y un rendimiento de lectura sorprendentemente competitivo.
Este artículo no es un tutorial paso a paso, sino un análisis técnico con comandos reales y criterios de decisión. Vamos a explorar cuándo conviene cada uno, cómo tunearlos en la práctica y qué métricas observar para no caer en errores comunes.
¿Cómo funciona cada índice internamente?
B-Tree: el todoterreno clásico
El B-Tree mantiene una estructura de árbol balanceado donde cada hoja contiene punteros a las filas ordenadas por el valor de la columna indexada. Su complejidad de búsqueda es O(log n), lo que lo hace excelente para consultas puntuales (WHERE id = 123) y rangos pequeños (WHERE fecha BETWEEN '2024-01-01' AND '2024-01-05'). El costo es el espacio: cada entrada ocupa varios bytes y, en tablas enormes, el índice puede llegar a ser tan grande como la propia tabla.
-- Crear un B-Tree en la columna created_at
CREATE INDEX idx_btree_created_at ON eventos (created_at);
BRIN: el índice de resumen por bloques
BRIN no almacena punteros a cada fila, sino un resumen (mínimo y máximo, y opcionalmente una suma o un bitmap de nulidad) por cada rango de bloques físicos consecutivos. Por defecto, pages_per_range = 128, es decir, un resumen por cada 128 páginas de 8 KB (aproximadamente 1 MB). Esto hace que el índice sea diminuto: en una tabla de 100 GB, un BRIN puede ocupar apenas unos pocos MB.
-- Crear un BRIN en la columna created_at
CREATE INDEX idx_brin_created_at ON eventos USING brin (created_at);
La búsqueda con BRIN es un proceso de dos fases: primero, PostgreSQL lee el índice BRIN para identificar los rangos de bloques que podrían contener los datos (según los resúmenes), y luego escanea solo esos bloques. Si los datos están físicamente correlacionados con el orden de inserción, la eficiencia es alta. Si no, BRIN puede degradarse a un escaneo secuencial disfrazado.
Criterios de decisión: ¿cuándo usar BRIN?
- Tablas muy grandes (≥ 10 millones de filas o ≥ 10 GB). En tablas pequeñas, la sobrecarga del escaneo de rangos de BRIN no compensa.
- Columnas con alta correlación física: los datos insertados en orden secuencial (por ejemplo, una columna
id BIGSERIALocreated_at TIMESTAMPTZ DEFAULT now()). - Consultas de rango amplio: reportes del tipo
WHERE fecha > now() - interval '30 days'que devuelven un porcentaje significativo de la tabla (5% o más). - Bajo write amplification: BRIN es barato de mantener porque no necesita actualizar el árbol en cada inserción; solo actualiza el resumen del bloque cuando se llena un rango.
- Espacio en disco limitado: si el índice B-Tree supera el 20% del tamaño de la tabla y no puedes permitirlo, BRIN es el candidato natural.
Contraindicaciones claras
- Columnas con alta cardinalidad y orden aleatorio (por ejemplo, un UUID v4). BRIN no podrá descartar rangos y escaneará casi todo.
- Consultas puntuales muy frecuentes (
WHERE email = 'x@y.com'). Un BRIN no encontrará el bloque exacto; B-Tree es 50 a 100 veces más rápido en ese caso. - Tablas con actualizaciones masivas que rompan la correlación física (por ejemplo, un
UPDATEque cambia la columna indexada en filas dispersas).
Comandos prácticos para diagnosticar y decidir
1. Ver el tamaño de los índices
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total, pg_size_pretty(pg_relation_size(relid)) AS table_size FROM pg_stat_user_tables WHERE relname = 'eventos';
— Ver solo índices SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size FROM pg_stat_user_indexes WHERE relname = ‘eventos’;
2. Medir la correlación física de la columna
SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'eventos' AND attname IN ('id', 'created_at');
El valor de correlation oscila entre -1 y 1. Un valor cercano a 1 (o -1) indica que el orden físico coincide con el orden lógico de los valores: ideal para BRIN. Si está por debajo de 0.5, B-Tree será más predecible.
3. Probar ambos índices y comparar planes de ejecución
-- Activar/desactivar índices para comparar SET enable_bitmapscan = off; -- para forzar el uso de BRIN en la prueba EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM eventos WHERE created_at > '2024-10-01' AND created_at < '2024-10-15';
RESET enable_bitmapscan;
Observa el número de bloques leídos (Buffers: shared hit/read) y el tiempo total. Si BRIN lee menos bloques que un B-Tree para la misma consulta de rango, es la elección correcta.
4. Tuning de parámetros BRIN
-- Ajustar pages_per_range: menor valor = más resúmenes = más precisión pero más tamaño CREATE INDEX idx_brin_created_at ON eventos USING brin (created_at) WITH (pages_per_range = 64);— Índice BRIN con múltiples columnas CREATE INDEX idx_brin_multi ON eventos USING brin (fecha, categoria) WITH (pages_per_range = 32);
— Reindexar para actualizar resúmenes tras grandes cambios REINDEX INDEX idx_brin_created_at;
El parámetro pages_per_range es un trade-off: valores pequeños (32, 64) aumentan el tamaño del índice y la precisión, ideales si las consultas son muy selectivas pero el rango de búsqueda es acotado. Valores grandes (256, 512) reducen el tamaño y el mantenimiento, pero pueden provocar falsos positivos (leer bloques que no contienen datos) y degradar el rendimiento.
5. Mantenimiento y autovacío
BRIN depende de que los resúmenes estén actualizados. PostgreSQL mantiene los resúmenes automáticamente durante el INSERT y el UPDATE, pero no durante el DELETE masivo. En tablas con borrados frecuentes, ejecuta VACUUM ANALYZE periódicamente:
VACUUM (ANALYZE, VERBOSE) eventos;
Si usas BRIN, presta atención a relallvisible y a la estadística de bloqueos. Un índice BRIN obsoleto puede hacer que el planificador prefiera un Seq Scan completo, arruinando el rendimiento.
Escenario real: tabla de logs de eventos
Consideremos una tabla eventos con 200 millones de filas, insertadas en orden cronológico. La consulta típica es:
SELECT * FROM eventos WHERE created_at > now() - interval '7 days';
Un B-Tree sobre created_at ocupará aproximadamente 3 GB. Un BRIN con pages_per_range = 128 ocupará apenas 8 MB. En una prueba con EXPLAIN ANALYZE, el B-Tree devuelve los resultados en 2.1 segundos (usando un Bitmap Index Scan), mientras que el BRIN lo hace en 2.4 segundos. La diferencia es mínima en tiempo, pero el BRIN reduce el cache pressure y el tiempo de mantenimiento en inserciones: mientras que insertar 100k filas con B-Tree genera ~500 MB de escrituras de índice, BRIN solo genera ~2 MB.
Si, en cambio, ejecutas SELECT * FROM eventos WHERE id = 123456789, el B-Tree responde en 0.
comentarios(0)
$ dejar comentario