Indexación de datos JSONB: usando índices GIN y BTREE

La indexación en PostgreSQL es una forma de buscar datos rápido en la base de datos. Si los datos de tu tabla fueran libros, la indexación sería como el catálogo de una biblioteca, que te deja encontrar el libro que quieres por el título o el autor. Con JSONB esto puede ser un poco más tricky, porque los datos están en un formato estructurado, no en filas y columnas separadas.

Cuando los datos JSONB empiezan a crecer hasta el tamaño de "un libro de Harry Potter, pero sin ilustraciones", buscar dentro de esa estructura puede volverse lento. Por ejemplo, si quieres encontrar todos los pedidos donde la clave "status" tiene el valor "delivered", PostgreSQL tiene que revisar todos los registros para hacer la búsqueda. Suena como un curro que no querrías hacer a mano, ¿verdad?

Pues los índices GIN y BTREE son nuestros héroes que vienen al rescate, ¡salvándonos de largas esperas!

Tipos de índices para JSONB

GIN (Generalized Inverted Index)

El índice GIN está hecho especialmente para trabajar con datos estructurados, como arrays y objetos, lo que lo hace perfecto para JSONB. Permite indexar no el objeto entero, sino las claves y valores dentro de él. Esto significa que con GIN puedes encontrar rápido los registros que contienen ciertas claves, valores o combinaciones.

Imagina una columna JSONB con estos datos:

{"name": "Alice", "age": 25, "city": "Berlin"}

El índice GIN crea una estructura interna donde las claves "name", "age" y "city" están conectadas a sus valores. Así que cuando buscamos "name": "Alice", PostgreSQL ya sabe dónde buscar — no tiene que recorrer toda la tabla.

BTREE

El índice BTREE es más tradicional. Crea una estructura ordenada que permite encontrar datos rápido por valores concretos. En el caso de JSONB, el índice BTREE se puede usar si buscas una coincidencia exacta de los datos o si tienes una clave fija (por ejemplo, quieres comparar el valor del JSONB entero).

Si tu columna tiene objetos JSONB como estos:

{"name": "Bob", "age": 30}

El índice BTREE puede ser útil si buscas registros donde el objeto entero sea igual.

{"name": "Bob", "age": 30}

Creando un índice para JSONB

Primero veamos cómo crear un índice GIN. Todo lo que necesitas es el comando mágico CREATE INDEX. Así es como se hace:

-- Creamos un índice GIN para la columna JSONB
CREATE INDEX idx_jsonb_data ON orders USING GIN (data);

Dónde:

• idx_jsonb_data — nombre del índice.
• orders — nombre de la tabla.
• data — columna con datos JSONB.

Después de crear este índice, las consultas que buscan claves o valores dentro del JSONB irán mucho más rápido.

Supón que tenemos una tabla orders con una columna data que contiene JSONB:

Consulta sin índice:

-- Buscar todos los pedidos con status "delivered"
SELECT * FROM orders WHERE data @> '{"status": "delivered"}';

Si la tabla es grande, esta consulta puede tardar mucho. Pero con el índice GIN irá mucho más rápido.

Cómo crear un índice BTREE

Para crear un índice BTREE tienes que cambiar un poco el enfoque. En la mayoría de los casos, para usar BTREE con JSONB, tienes que decir qué parte del objeto quieres indexar, no el objeto entero. Aquí tienes un ejemplo:

-- Creamos un índice BTREE para una clave concreta
CREATE INDEX idx_jsonb_total ON orders ((data->>'total'));

Fíjate en (data->>'total'). Esto saca el valor de la clave total del objeto JSONB, y eso es lo que se indexa. Ahora, si buscas pedidos donde total = 100, PostgreSQL usará este índice.

Un ejemplo usando los mismos datos:

Consulta:

-- Buscar todos los pedidos donde total = 100
SELECT * FROM orders WHERE data->>'total' = '100';

Con el índice BTREE para data->>'total', esta consulta será mucho más rápida.

Comparando GIN y BTREE

Si tienes estructuras JSONB complejas y usas mucho operadores como @> o ?, elige GIN. Si buscas valores o claves concretas y fijas, BTREE puede ser mejor opción.

Trampas y errores típicos al indexar JSONB

Trabajar con índices en JSONB puede ser muy potente, pero hay algunas trampas que tienes que tener en cuenta.

1. Falta de índice donde hace falta. Si usas mucho datos JSONB en filtros (WHERE), pero no creas un índice, las consultas serán lentas.
2. Indexar en exceso. Si creas índices para cada clave posible de JSONB, puedes hacer que las inserciones y actualizaciones vayan más lentas.
3. Elegir mal el tipo de índice. Si tus consultas son complejas y usan operadores como @> o ?, pero creas un índice BTREE, no vas a ganar rendimiento.
4. No saber sobre rutas. Si siempre accedes a valores anidados, pero no creas un índice para esa ruta concreta (por ejemplo, data->>'some_key'), tu consulta seguirá siendo lenta.

Resumen: cuándo usar cada índice

• Usa GIN si tienes arrays u objetos complejos donde buscas mucho por claves y valores.
• Usa BTREE si buscas coincidencias exactas o accedes mucho a claves concretas.