Índices B-tree vs GIN vs GiST: una guía práctica de PostgreSQL

Lo que realmente eliges al escoger un índice

La mayoría de los problemas con índices en PostgreSQL empiezan igual: una vista de lista es rápida con 1.000 filas y luego se vuelve lenta con 1.000.000. O un cuadro de búsqueda que funcionó en pruebas se convierte en una pausa de segundos en producción. Cuando eso pasa, es tentador preguntar “¿Qué índice es el mejor?” Una mejor pregunta es: “¿Qué le pide esta pantalla a la base de datos?”

La misma tabla puede necesitar distintos tipos de índice porque diferentes pantallas la leen de formas distintas. Una vista filtra por un único estado y ordena por created_at. Otra hace búsqueda de texto completo. Otra comprueba si un campo JSON contiene una clave. Otra encuentra elementos cerca de un punto en un mapa. Esos son patrones de acceso distintos, así que un tipo de índice no ganará en todos los casos.

Eso es lo que eliges al escoger un índice: cómo la app accede a los datos. ¿Principalmente haces coincidencias exactas, rangos y ordenación? ¿Buscas dentro de documentos o arrays? ¿Preguntas “qué está cerca de esta ubicación” o “qué se solapa con este rango”? La respuesta determina si B-tree, GIN o GiST encaja mejor.

B-tree, GIN y GiST en lenguaje llano

Escoger un índice tiene menos que ver con el tipo de columna y más con lo que tus consultas hacen con ella. PostgreSQL decide usar índices según operadores como =, <, @>, o @@, no según si la columna es “text” o “json”. Por eso el mismo campo puede necesitar distintos índices en diferentes pantallas.

B-tree: rápido para búsquedas ordenadas

B-tree es el predeterminado y la elección más común. Brilla cuando filtras por valor exacto, filtras por un rango o necesitas resultados en un orden específico.

Un ejemplo típico es una lista de administración filtrada por estado y ordenada por created_at. Un índice B-tree en (status, created_at) puede ayudar tanto al filtro como al orden. B-tree también es la herramienta habitual para unicidad (constraints UNIQUE).

GIN: rápido cuando cada fila contiene muchas claves buscables

GIN está diseñado para preguntas del tipo “¿esta fila contiene este término/valor?”, donde una fila puede coincidir con muchas claves. Ejemplos comunes son búsqueda de texto completo (un documento contiene palabras) y pertenencia en JSONB/arrays (JSON contiene una clave/valor).

Piensa en un registro de cliente con un objeto JSONB preferences, y una pantalla que filtra usuarios donde preferences contiene {"newsletter": true}. Eso es una consulta al estilo GIN.

GiST: flexible para rangos, geo y similitud

GiST es un marco general usado por tipos de datos que no encajan en un orden simple. Es idóneo para rangos (solapamientos, contención), consultas geométricas y geográficas (cerca, dentro) y algunas búsquedas por similitud.

Al decidir entre B-tree, GIN o GiST, empieza por anotar los operadores que usan tus pantallas más concurridas. El índice adecuado suele quedar claro después.

Tabla de decisión para pantallas comunes (filtros, búsqueda, JSON, geo)

La mayoría de las apps solo necesitan unos pocos patrones de índices. El truco es emparejar el comportamiento de la pantalla con los operadores que usan tus consultas.

Dos índices pueden coexistir cuando los endpoints difieren. Por ejemplo, una lista de administración puede necesitar un B-tree en (tenant_id, created_at) para sorting rápido, mientras una página de búsqueda necesita un GIN para @@. Mantén ambos solo si ambos patrones de consulta son comunes.

Si no estás seguro, mira el operador primero. Los índices ayudan cuando la base de datos puede saltarse gran parte de la tabla.

Filtros y ordenación: donde B-tree suele ganar

Para la mayoría de pantallas cotidianas, B-tree es la elección simple que funciona. Si tu consulta es “elige filas donde una columna es un valor, quizá ordénalas y muestra la página 1”, B-tree suele ser lo primero a probar.

Los filtros por igualdad son el caso clásico. Columnas como status, user_id, account_id, type o tenant_id aparecen constantemente en dashboards y paneles administrativos. Un índice B-tree puede saltar directamente a los valores coincidentes.

Los filtros por rango también encajan bien con B-tree. Cuando filtras por tiempo o rangos numéricos, la estructura ordenada ayuda: created_at >= ..., price BETWEEN ..., id > .... Si tu UI ofrece “Últimos 7 días” o “$50 a $100”, B-tree hace justamente eso.

La ordenación y la paginación son donde B-tree puede ahorrarte más trabajo. Si el orden del índice coincide con tu ORDER BY, PostgreSQL a menudo puede devolver filas ya ordenadas en lugar de ordenar un gran conjunto en memoria.

-- Una pantalla común: "Mis tickets abiertos, más recientes primero"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

Los índices compuestos siguen una regla simple: PostgreSQL solo puede usar eficientemente la parte líder del índice. Piensa “de izquierda a derecha”. Con (user_id, status, created_at), las consultas que filtran por user_id (y opcionalmente status) se benefician. Una consulta que filtra solo por status normalmente no lo hará.

Los índices parciales son una gran mejora cuando tu pantalla solo se preocupa por una porción de datos. Cortes comunes son “solo filas activas”, “no soft-deleted” o “actividad reciente”. Mantienen el índice más pequeño y rápido.

Columnas de alta cardinalidad y el coste de índices extras

Las columnas de alta cardinalidad tienen muchos valores únicos, como user_id, order_id, email o created_at con resolución de segundos. Los índices suelen brillar aquí porque un filtro puede reducir rápidamente los resultados a una porción pequeña de la tabla.

Las columnas de baja cardinalidad son lo contrario: booleanos y enums pequeños como is_active, status IN ('open','closed') o plan IN ('free','pro'). Un índice sobre estas suele decepcionar porque cada valor coincide con una gran porción de filas. PostgreSQL puede elegir correctamente un escaneo secuencial ya que saltar por el índice aún implica leer muchas páginas de la tabla.

Otro coste sutil es obtener las filas. Incluso si un índice encuentra IDs coincidentes rápido, la base de datos puede tener que visitar la tabla por las demás columnas. Si tu consulta solo necesita unos pocos campos, un índice covering puede ayudar, pero también hace el índice más grande y más caro de mantener.

Cada índice extra tiene un precio en escrituras. Las inserciones deben escribir en cada índice. Las actualizaciones que cambian columnas indexadas deben actualizar esas entradas también. Añadir índices “por si acaso” puede ralentizar toda la app, no solo una pantalla.

Guía práctica:

• Empieza con 1-2 índices de trabajo por tabla ocupada, basados en filtros y ordenaciones reales.
• Favorece columnas de alta cardinalidad usadas en WHERE y ORDER BY.
• Ten precaución al indexar booleanos y enums pequeños a menos que se combinen con otra columna selectiva.
• Añade un índice nuevo solo después de poder nombrar la consulta exacta que acelerará.

Ejemplo: una lista de tickets de soporte filtrada por assignee_id (alta cardinalidad) se beneficia de un índice, mientras que is_archived = false sola a menudo no.

Pantallas de búsqueda: texto completo, prefijos y “contiene”

Los cuadros de búsqueda parecen simples, pero los usuarios esperan mucho: varias palabras, distintas formas de una palabra y un ranking razonable. En PostgreSQL, eso suele ser búsqueda de texto completo: guardas un tsvector (texto preparado) y consultas con un tsquery (lo que el usuario escribió, parseado en términos).

Para búsqueda de texto completo, GIN es el predeterminado común porque es rápido respondiendo “¿contiene este documento estos términos?” en muchas filas. El intercambio es escrituras más pesadas: insertar y actualizar filas suele costar más.

GiST también puede funcionar para texto completo. A menudo es más pequeño y más barato de actualizar, pero generalmente más lento en lecturas que GIN. Si tus datos cambian constantemente (por ejemplo, tablas tipo evento), ese balance lectura-escritura puede importar.

La búsqueda por prefijo no es texto completo

Búsqueda por prefijo significa “empieza con”, como buscar clientes por prefijo de email. Eso no es para lo que se creó la búsqueda de texto completo. Para patrones por prefijo, un índice B-tree puede ayudar (a menudo con la clase de operador adecuada) porque coincide con el orden de las cadenas.

Para búsquedas “contiene” como ILIKE '%error%', B-tree normalmente no ayuda. Ahí es donde entra el índice trigram o un enfoque de búsqueda distinto.

Cuando los usuarios quieren filtros más búsqueda de texto

La mayoría de pantallas reales combinan búsqueda con filtros: status, assignee, rango de fechas, tenant, etc. Una configuración práctica es:

• Un índice GIN (o a veces GiST) para la columna tsvector.
• Índices B-tree para los filtros más selectivos (por ejemplo, account_id, status, created_at).
• Una regla estricta de “mantenerlo mínimo”, porque demasiados índices hacen las escrituras más lentas.

Ejemplo: una pantalla de tickets que busca “refund delayed” y filtra status = 'open' y un account_id específico. El texto completo te da filas relevantes, mientras que B-tree ayuda a PostgreSQL a reducir rápidamente a la cuenta y estado correctos.

Campos JSONB: elegir entre GIN y B-tree dirigidos

JSONB es genial para datos flexibles, pero puede convertirse en consultas lentas si lo tratas como una columna normal. La decisión central es simple: ¿buscas “en cualquier parte de este JSON” o filtras por unas pocas rutas específicas una y otra vez?

Para consultas de contención como metadata @> '{"plan":"pro"}', un índice GIN suele ser la primera opción. Está diseñado para “¿contiene este documento esta forma?” y también soporta cheques de existencia de clave como ?, ?| y ?&.

Si tu app filtra mayormente por una o dos claves JSON, un índice de expresión B-tree dirigido suele ser más rápido y más pequeño. También ayuda cuando necesitas ordenar o comparar numéricamente valores extraídos.

-- Soporte amplio para contención y cheques de clave
CREATE INDEX ON customers USING GIN (metadata);

-- Filtros dirigidos y ordenación en una ruta JSON
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

Regla práctica:

• Usa GIN cuando los usuarios buscan múltiples claves, tags o estructuras anidadas.
• Usa índices de expresión B-tree cuando filtras por rutas específicas repetidamente.
• Indexa lo que aparece en pantallas reales, no todo.
• Si el rendimiento depende de unas pocas claves JSON que siempre usas, considera promoverlas a columnas reales.

Ejemplo: una pantalla de soporte podría filtrar tickets por metadata->>'priority' y ordenar por created_at. Indexa la ruta JSON de prioridad y la columna created_at. Omite un GIN amplio a menos que los usuarios también busquen tags o atributos anidados.

Geo y consultas de rango: donde GiST encaja mejor

Las pantallas geo y de rango son donde GiST suele volverse la elección obvia. GiST está pensado para “¿esto se solapa, contiene o está cerca de eso?” más que “¿este valor es igual a ese valor?”

Los datos geo suelen ser puntos (ubicación de una tienda), líneas (una ruta) o polígonos (zona de entrega). Pantallas comunes incluyen “tiendas cerca de mí”, “trabajos dentro de 10 km”, “mostrar elementos dentro de esta caja del mapa” o “¿esta dirección está dentro de nuestra área de servicio?” Un índice GiST (normalmente vía PostGIS geometry o geography) acelera estos operadores espaciales para que la base de datos pueda saltarse la mayoría de filas en lugar de comprobar cada forma.

Los rangos son similares. PostgreSQL tiene tipos range como daterange e int4range, y la pregunta típica es solapamiento: “¿esta reserva colisiona con otra existente?” o “muestra suscripciones activas durante esta semana.” GiST soporta operadores de solapamiento y contención eficientemente, por eso es habitual en calendarios, planificación y comprobaciones de disponibilidad.

B-tree aún puede importar en pantallas geo. Muchas páginas primero filtran por tenant, status o tiempo, luego aplican una condición espacial, luego ordenan. Por ejemplo: “solo entregas de mi empresa, de los últimos 7 días, más cercanas primero.” GiST maneja la parte espacial, pero B-tree ayuda con filtros selectivos y ordenación.

Cómo elegir un índice paso a paso

La elección de índice trata principalmente sobre el operador, no del nombre de la columna. La misma columna puede necesitar distintos índices según si usas =, >, LIKE 'prefix%', búsqueda de texto completo, contención JSON o distancia geo.

Lee la consulta como una lista de verificación: WHERE decide qué filas califican, JOIN decide cómo se conectan las tablas, ORDER BY decide el orden de salida y LIMIT decide cuántas filas necesitas realmente. El mejor índice suele ser el que te ayuda a encontrar las primeras 20 filas rápido.

Un proceso simple que funciona para la mayoría de pantallas:

1. Anota los operadores exactos que usa tu pantalla (ejemplo: status =, created_at >=, name ILIKE, meta @>, ST_DWithin).
2. Comienza con un índice que coincida con el filtro más selectivo o con el orden por defecto. Si la pantalla ordena por created_at DESC, empieza por ahí.
3. Añade un índice compuesto solo cuando veas los mismos filtros juntos todo el tiempo. Pon columnas de igualdad primero, luego las de rango y luego la clave de orden.
4. Usa un índice parcial cuando siempre filtras a un subconjunto (ejemplo: solo status = 'open'). Usa un índice de expresión cuando consultas un valor calculado (ejemplo: lower(email) para búsquedas case-insensitive).
5. Valida con EXPLAIN ANALYZE. Mantenlo si reduce mucho el tiempo de ejecución y las filas leídas.

Ejemplo concreto: un dashboard de soporte puede filtrar tickets por status y ordenar por más recientes. Un B-tree en (status, created_at DESC) es un buen primer intento. Si la misma pantalla también filtra por una flag JSONB como meta @> '{"vip": true}', ese es un operador distinto y suele necesitar un índice enfocado en JSON.

Errores comunes que hacen perder tiempo (y ralentizan las escrituras)

Una forma común de decepcionarse es elegir el tipo de índice “correcto” para el operador equivocado. PostgreSQL solo puede usar un índice cuando la consulta coincide con lo que el índice fue construido para responder. Si tu app usa ILIKE '%term%', un B-tree simple sobre esa columna de texto permanecerá sin usarse y seguirás escaneando la tabla.

Otra trampa es construir índices multicolumna gigantes “por si acaso”. Parecen seguros, pero son caros de mantener y a menudo no coinciden con patrones reales de consulta. Si las columnas más a la izquierda no se usan en el filtro, el resto del índice puede no ayudar.

Las columnas de baja selectividad también son fáciles de sobre-indexar. Un B-tree en un booleano como is_active o un estado con pocos valores puede ser casi inútil a menos que lo hagas parcial para coincidir con lo que consultas realmente.

JSONB añade sus propias trampas. Un GIN amplio puede ser excelente para filtros flexibles, pero muchas comprobaciones de rutas JSON son más rápidas con un índice de expresión que indexa el valor extraído. Si tu pantalla siempre filtra por payload->>'customer_id', indexar esa expresión suele ser más pequeño y rápido que indexar todo el documento.

Finalmente, cada índice extra grava las escrituras. En tablas con muchas actualizaciones (tickets u órdenes), cada inserción y actualización tiene que tocar todos los índices.

Antes de añadir un índice, párate y revisa:

• ¿El índice coincide con el operador exacto que usa tu consulta?
• ¿Puedes reemplazar un índice multicolumna ancho con uno o dos enfocados?
• ¿Debería ser parcial para evitar ruido de baja selectividad?
• Para JSONB, ¿un índice de expresión encajaría mejor?
• ¿Es la tabla lo suficientemente write-heavy como para que el coste del índice supere la ganancia en lectura?

Comprobaciones rápidas antes de añadir (o mantener) un índice

Antes de crear un índice nuevo, sé específico sobre lo que la app realmente hace. Un índice “agradable de tener” a menudo se convierte en escrituras más lentas y más almacenamiento con poco beneficio.

Empieza con tus tres pantallas principales (o endpoints API) y anota la forma exacta de sus consultas: filtros, orden y qué escribe el usuario. Muchos “problemas de índice” son en realidad “problemas de consulta poco claros”, especialmente cuando se debate B-tree vs GIN vs GiST sin nombrar el operador.

Una lista de comprobación simple:

• Elige 3 pantallas reales y lista sus patrones exactos de WHERE y ORDER BY (incluyendo dirección y manejo de NULL).
• Confirma el tipo de operador: igualdad (=), rango (>, BETWEEN), prefijo, contención, solapamiento o distancia.
• Elige un índice por patrón de pantalla común, pruébalo y conserva solo los que reduzcan significativamente tiempo o lecturas.
• Si la tabla es write-heavy, sé estricto: índices extra multiplican el coste de escritura y pueden aumentar la presión de vacuum.
• Revisa después de cambios de funcionalidad. Un nuevo filtro, un nuevo orden por defecto o cambiar de “empieza con” a “contiene” puede volver irrelevante el índice antiguo.

Ejemplo: un dashboard agrega un nuevo orden por defecto last_activity DESC. Si solo indexaste status, el filtro puede seguir siendo rápido, pero la ordenación ahora obliga a trabajo extra.

Ejemplo: mapear pantallas reales a índices adecuados

Una tabla de decisión solo ayuda si la puedes mapear a pantallas reales que entregas. Aquí hay tres pantallas comunes y cómo encajan con elecciones de índice.

Una forma práctica de aplicar esto es partir de la UI:

• Lista cada control que reduce resultados (filtros).
• Anota el orden por defecto.
• Sé específico sobre el comportamiento de búsqueda (texto completo vs empieza-con vs contiene).
• Señala campos “especiales” (JSONB, geo, rangos).

Próximos pasos: hacer de la indexación parte de tu proceso de construcción

Los buenos índices siguen a tus pantallas: los filtros que la gente pulsa, el orden que esperan y el cuadro de búsqueda que usan. Trata la indexación como un hábito durante el desarrollo y evitarás la mayoría de sorpresas de rendimiento más adelante.

Hazlo repetible: identifica las 1-3 consultas que ejecuta una pantalla, añade el índice más pequeño que coincida con ellas, prueba con datos realistas y luego elimina lo que no aporte valor.

Si estás construyendo una herramienta interna o un portal de clientes, planea las necesidades de índices temprano porque estas apps suelen crecer añadiendo más filtros y más pantallas de lista. Si construyes con AppMaster (appmaster.io), ayuda tratar la configuración de filtros y orden de cada pantalla como un contrato de consulta concreto, y luego añadir solo los índices que apoyen esos clics reales.