Índices BRIN en PostgreSQL: una guía para las gemas de rendimiento ocultas

Cuando hablamos de PostgreSQL y optimización de consultas, la mayoría de la gente piensa inmediatamente en los índices B-Tree, GIN, GiST, and so on. Pero los índices BRIN a menudo permanecen en la sombra, a pesar de ser increíblemente útiles en ciertos escenarios. Pueden aumentar drásticamente el rendimiento, especialmente cuando se trata de tablas masivas y espacio en disco limitado. Hoy te explicaré cómo funciona BRIN y cuándo brilla.

¿Qué es BRIN?

BRIN significa Índice de rango de bloques. Mientras que los índices regulares tienen como objetivo almacenar información detallada sobre cada fila, BRIN adopta un enfoque diferente. Almacena información resumida para grupos de páginas, llamadas “zonas”, en lugar de indexar cada fila individualmente.

Para simplificar, BRIN divide una tabla en bloques (cada zona normalmente cubre 32 páginas de forma predeterminada) y almacena los valores mínimo y máximo para una columna (u otras características generalizadas según la clase de operador) dentro de esa zona. Cuando consulta los datos, BRIN identifica las zonas relevantes y omite el resto.

¿Es 100% preciso? No del todo: aún necesitarás escanear filas dentro de las zonas seleccionadas. Sin embargo, este filtrado es significativamente más rápido que escanear toda la tabla.

¿Cuándo es BRIN una buena opción?

Aquí hay algunos escenarios en los que BRIN sobresale:

1. Mesas enormes
   Cuando se trata de tablas que contienen millones o miles de millones de filas, los índices tradicionales del árbol B pueden inflarse y ocupar un espacio appreciable. BRIN, por otro lado, sigue siendo ligero y compacto.

2. Correlación de datos naturales
   Si sus datos tienen un orden pure, BRIN puede resultar particularmente eficaz. Por ejemplo, si tienes un order_date columna en un orders tabla, es possible que los pedidos más antiguos se almacenen al principio de la tabla y los más nuevos al closing. Para consultas dirigidas a un mes específico, BRIN cut back rápidamente las zonas relevantes.

3. Ahorro de espacio en disco
   Los índices BRIN son notablemente compactos. En lugar de almacenar todos los valores individuales, utilizan metadatos agregados por zona, lo que cut back los requisitos de almacenamiento.

Limitaciones de BRIN

Como cualquier herramienta, BRIN tiene sus ventajas y desventajas:

• Inexactitud: BRIN no identifica las ubicaciones de los datos. Sólo indica zonas donde los datos podría ser, lo que requiere un mayor filtrado dentro de esas zonas.
• Datos dispersos: Si los valores de las columnas están dispersos al azar, BRIN no podrá filtrar zonas irrelevantes de manera efectiva, lo que lo hará menos beneficioso.

Crear un índice BRIN

Aquí hay un ejemplo rápido de cómo crear un índice BRIN:

CREATE INDEX idx_orders_date_brin ON orders
USING BRIN(order_date)
WITH (pages_per_range = 32);

El pages_per_range El parámetro determina el número de páginas en cada zona. El valor predeterminado es 128 páginas, pero puedes ajustarlo. Un valor más pequeño aumenta la precisión del índice pero también aumenta el tamaño del índice. Experimentar con este parámetro puede ayudar a encontrar el mejor equilibrio para sus datos.

Automatización de resúmenes

Puedes usar extensiones como autosummarize para automatizar la actualización de datos resumidos, eliminando la necesidad de llamar manualmente brin_summarize_new_values.

Clases de operadores BRIN: Minmax e inclusión

BRIN utiliza clases de operador para definir cómo resume los datos. Los dos enfoques principales son:

• Clases Minmax: Almacene los valores mínimo y máximo en cada zona. Ideally suited para tipos ordenados como números, fechas o cadenas.
• Clases de inclusión: Maneje tipos más complejos, como geometrías, redes IP o rangos. Estas clases almacenan representaciones “masivas”, como cuadros delimitadores o uniones de rangos, lo que permite operaciones en estructuras de datos complejas.

Por qué son importantes las clases de inclusión

Las clases de inclusión pueden procesar datos complejos de manera eficiente. Por ejemplo, podrían identificar zonas que contienen objetos que intersectan un área específica, lo que las hace útiles para casos de uso especializados como datos espaciales.

Los índices BRIN son una característica poderosa pero infrautilizada en PostgreSQL. Si bien no son adecuados para todas las situaciones, pueden mejorar significativamente el rendimiento cuando se trabaja con grandes conjuntos de datos y datos ordenados de forma pure. Experimente con BRIN para desbloquear su potencial y optimizar las consultas de su base de datos.

Hay operaciones minmax para la mayoría de los tipos y operadores de inclusión para estructuras más exóticas. Es decir, puede indexar no sólo números y cadenas simples, sino también tipos complejos. Por ejemplo, box_inclusion_ops le permitirá buscar objetos en un área geográfica determinada, cortando rápidamente las zonas donde no hay exactamente objetos adecuados.

Ejemplo de optimización de muestra

Supongamos que hay una tabla de pedidos con varias filas:

CREATE TABLE orders (
    id            BIGSERIAL PRIMARY KEY,
    order_date    DATE NOT NULL,
    customer_id   BIGINT NOT NULL,
    total_amount  NUMERIC(10, 2) NOT NULL
);

Insertando un montón de datos:

INSERT INTO orders (order_date, customer_id, total_amount)
SELECT
    (DATE '2023-01-01' + (RANDOM()*365)::INT),
    (RANDOM()*1000000)::BIGINT,
    (RANDOM()*1000)::NUMERIC(10,2)
FROM generate_series(1,10000000) g;

Ahora creamos el índice BRIN:

CREATE INDEX idx_orders_date_brin ON orders
USING BRIN(order_date date_minmax_ops)
WITH (pages_per_range = 64);

date_minmax_ops cube: almacenaremos la fecha mínima y máxima para cada zona de 64 páginas.

Al consultar:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date BETWEEN '2023-06-01' AND '2023-06-15';

El plan mostrará que PostgreSQL consultará primero el índice BRIN, determinará qué zonas pueden contener las fechas deseadas y omitirá el resto. Si los datos se ordenan aproximadamente por fecha (por ejemplo, según se insertan), se minimizará el número de zonas adicionales.

Sutilezas de parámetros

pages_per_range

• Valores más pequeños: Proporciona un filtrado más preciso (cada zona es más pequeña, por lo que hay menos basura) pero da como resultado un tamaño de índice mayor.
• Valores más grandes: Cut back el tamaño del índice pero empeora la precisión.
• Recomendación: Pruebe con datos reales para encontrar el equilibrio óptimo.

autovacuum y brin_summarize_new_values

Clases de operador personalizadas

Si ninguna clase de operador estándar se adapta a sus necesidades, puede escribir la suya propia. Este es un enfoque más avanzado que requiere lo siguiente:

• Implemente funciones C para describir cómo:
  • Valores agregados,
  • Fusionar zonas y
  • Verifique las intersecciones con consultas.
• Esto le permite indexar estructuras de datos únicas o exóticas, pero es una tarea compleja y altamente técnica.

Conclusión

Los índices BRIN no reemplazarán a los índices B-Tree en todas partes. Sin embargo, en los casos en que los datos están organizados o tienen una correlación pure con su ubicación física, BRIN puede ofrecer un rendimiento sorprendente con un tamaño de índice mínimo.