Índices PostgreSQL para apps SaaS: btree vs GIN vs GiST

Qué problema solucionan los índices en pantallas SaaS reales

Un índice cambia la forma en que PostgreSQL encuentra filas. Sin un índice, la base de datos a menudo tiene que leer gran parte de la tabla (un escaneo secuencial) y luego descartar la mayor parte. Con el índice adecuado, puede saltar directamente a las filas coincidentes (una búsqueda por índice) y luego recuperar solo lo necesario.

Esto se nota pronto en SaaS porque las pantallas diarias hacen muchas consultas. Un solo clic puede desencadenar varias lecturas: la página de lista, un conteo total, un par de tarjetas del dashboard y un cuadro de búsqueda. Cuando una tabla crece de miles a millones de filas, la misma consulta que antes parecía instantánea empieza a retrasarse.

Un ejemplo típico es una página de Orders filtrada por estado y fecha, ordenada por más recientes primero, con paginación. Si PostgreSQL tiene que escanear toda la tabla de orders para encontrar pagos de los últimos 30 días, cada carga de página hace trabajo extra. Un buen índice convierte eso en un salto rápido a la porción correcta de datos.

Los índices no son gratis. Cada uno acelera lecturas para consultas específicas, pero también ralentiza escrituras (INSERT/UPDATE/DELETE deben actualizar los índices) y ocupa más almacenamiento (además de aumentar la presión de caché). Ese intercambio es la razón por la que deberías partir de patrones de consulta reales, no de tipos de índice.

Una regla simple que evita trabajo inútil: añade un índice solo cuando puedas señalar una consulta concreta y frecuente que vaya a acelerar. Si construyes pantallas con un generador guiado por chat como Koder.ai, ayuda capturar el SQL detrás de tus páginas de lista y dashboards y usarlo como lista de deseos de índices.

B-tree vs GIN vs GiST en términos sencillos

La mayor parte de la confusión sobre índices desaparece cuando dejas de pensar en características (JSON, búsqueda, arrays) y empiezas a pensar en la forma de la consulta: ¿qué hace la cláusula WHERE y cómo esperas que se ordenen los resultados?

B-tree: rápido para orden y comparaciones

Usa B-tree cuando tu consulta parece comparaciones normales y te importa el orden. Es el caballo de batalla para igualdad, rangos y joins.

Ejemplos de forma: filtrar por tenant_id = ?, status = 'active', created_at >= ?, hacer join users.id = orders.user_id, o mostrar “más recientes primero” con ORDER BY created_at DESC.

GIN y GiST: cuando una fila contiene muchos valores buscables

GIN (Generalized Inverted Index) encaja cuando una columna contiene muchos miembros y preguntas del tipo “¿contiene X?”. Es común con claves JSONB, elementos de arrays y vectores de texto completo.

Ejemplos de forma: metadata @> {'plan':'pro'} en JSONB, tags @> ARRAY['urgent'], o to_tsvector(body) @@ plainto_tsquery('reset password').

GiST (Generalized Search Tree) encaja en preguntas sobre distancia u solapamiento, donde los valores se comportan como rangos o formas. Se usa a menudo para tipos range, datos geométricos y algunas búsquedas de “coincidencia más cercana”.

Ejemplos de forma: ventanas de tiempo solapadas con columnas range, búsquedas por similitud (por ejemplo, usando trigram operators), o consultas espaciales (si usas PostGIS).

Una forma práctica de elegir:

• Si filtras u ordenas por columnas normales, empieza con B-tree.
• Si compruebas contención o pertenencia, mira GIN.
• Si preguntas “qué tan cerca” o “si se solapa”, GiST suele ser la opción.
• Si una consulta es rara o la tabla es pequeña, igual no necesitas un índice nuevo.
• Si no puedes describir la forma de la consulta, mide primero (EXPLAIN) antes de añadir nada.

Los índices aceleran lecturas, pero cuestan en tiempo de escritura y disco. En SaaS, ese intercambio importa más en tablas calientes como events, sessions y activity logs.

Patrones B-tree para filtros, orden y paginación

La mayoría de pantallas de listas en SaaS comparten la misma forma: un límite por tenant, un par de filtros y un orden previsible. Los índices B-tree son la elección por defecto aquí y normalmente los más baratos de mantener.

Un patrón común es WHERE tenant_id = ? más filtros como status = ?, user_id = ? y un rango temporal como created_at >= ?. Para índices compuestos B-tree, pon primero filtros de igualdad (columnas que comparas con =), luego añade la columna por la que ordenas.

Reglas que funcionan bien en la mayoría de apps:

• Empieza con tenant_id si cada consulta está acotada por tenant.
• Pon a continuación los filtros con = (a menudo status, user_id).
• Pon la columna de ORDER BY al final (a menudo created_at o id).
• Usa INCLUDE para cubrir páginas de lista sin ensanchar la clave.
• Prefiere paginación por seek en lugar de offset cuando las páginas profundizan.

Un ejemplo realista: una página de Tickets mostrando los ítems más nuevos primero, filtrada por estado.

-- Query
SELECT id, status, created_at, title
FROM tickets
WHERE tenant_id = $1
  AND status = $2
ORDER BY created_at DESC
LIMIT 50;

-- Index
CREATE INDEX tickets_tenant_status_created_at_idx
ON tickets (tenant_id, status, created_at DESC)
INCLUDE (title);

Ese índice soporta tanto el filtro como el orden, así Postgres puede evitar ordenar un conjunto de resultados grande. La parte INCLUDE (title) ayuda a que la página de lista toque menos páginas de tabla, manteniendo las claves del índice centradas en el filtrado y el orden.

Para rangos temporales, la misma idea aplica:

SELECT id, created_at
FROM events
WHERE tenant_id = $1
  AND created_at >= $2
  AND created_at <  $3
ORDER BY created_at DESC
LIMIT 100;

CREATE INDEX events_tenant_created_at_idx
ON events (tenant_id, created_at DESC);

La paginación es donde muchas apps SaaS se ralentizan. La paginación con OFFSET (OFFSET 50000) obliga a la base de datos a pasar por muchas filas. La paginación por seek se mantiene rápida usando la última clave vista:

SELECT id, created_at
FROM tickets
WHERE tenant_id = $1
  AND created_at < $2
ORDER BY created_at DESC
LIMIT 50;

Con el B-tree adecuado, esto permanece rápido incluso cuando la tabla crece.

Indexado pensando en multi-tenant sin sobreindexar

La mayoría de apps SaaS son multi-tenant: cada consulta debe quedarse dentro de un tenant. Si tus índices no incluyen tenant_id, Postgres aún puede encontrar filas rápido, pero a menudo escanea muchas más entradas de índice de las necesarias. Los índices conscientes del tenant mantienen los datos de cada tenant agrupados en el índice para que las pantallas comunes sigan siendo rápidas y predecibles.

Una regla simple: pon tenant_id primero en el índice cuando la consulta siempre filtra por tenant. Luego añade la columna que más filtras u ordenas.

Índices aburridos pero de alto impacto suelen verse así:

• (tenant_id, created_at) para listas de elementos recientes y paginación por cursor
• (tenant_id, status) para filtros por estado (Open, Paid, Failed)
• (tenant_id, user_id) para pantallas de “ítems de este usuario”
• (tenant_id, updated_at) para vistas administrativas de “recientemente cambiados”
• (tenant_id, external_id) para búsquedas desde webhooks o importaciones

El sobreindexado ocurre cuando añades un índice nuevo para cada pantalla ligeramente distinta. Antes de crear otro, verifica si un índice compuesto existente ya cubre las columnas más a la izquierda que necesitas. Por ejemplo, si tienes (tenant_id, created_at), normalmente no necesitas también (tenant_id, created_at, id) a menos que realmente filtres por id después de esas columnas.

Los índices parciales pueden reducir tamaño y coste de escritura cuando la mayoría de filas no son relevantes. Funcionan bien con borrados suaves y datos “solo activos”, por ejemplo: indexar solo donde deleted_at IS NULL, o solo donde status = 'active'.

Cada índice extra hace que las escrituras sean más pesadas. Las inserciones deben actualizar cada índice, y las actualizaciones pueden tocar varios índices incluso si cambias una sola columna. Si tu app ingiere muchos eventos (incluyendo apps creadas rápidamente con Koder.ai), mantén los índices centrados en las pocas formas de consulta que los usuarios usan cada día.

Indexar JSONB: GIN e índices de expresión dirigidos

JSONB es útil cuando tu app necesita campos extra flexibles como feature flags, atributos de usuario o configuración por tenant. El problema es que distintos operadores JSONB se comportan diferente, así que el mejor índice depende de cómo consultas.

Dos formas importan más:

• Contención: “¿Contiene este JSON estos pares clave-valor?” usando @>.
• Extracción de ruta: “¿Cuál es el valor de este campo específico?” usando -> / ->> (a menudo comparado con =).

Cuando un índice GIN es la opción correcta

Si filtras frecuentemente con @>, un índice GIN en la columna JSONB suele compensar.

-- Query shape: containment
SELECT id
FROM accounts
WHERE tenant_id = $1
  AND metadata @> '{"region":"eu","plan":"pro"}';

-- Index
CREATE INDEX accounts_metadata_gin
ON accounts
USING GIN (metadata);

Si tu estructura JSON es predecible y usas mayormente @> en claves de primer nivel, jsonb_path_ops puede ser más pequeño y rápido, pero soporta menos tipos de operadores.

Cuando un índice de expresión es mejor

Si tu UI filtra repetidamente por un campo (como plan), extraer ese campo e indexarlo suele ser más rápido y barato que un GIN amplio.

SELECT id
FROM accounts
WHERE tenant_id = $1
  AND metadata->>'plan' = 'pro';

CREATE INDEX accounts_plan_expr
ON accounts ((metadata->>'plan'));

Una regla práctica: usa JSONB para atributos flexibles y poco consultados, pero promociona a columnas reales las claves estables y muy usadas (plan, status, created_at). Si iteras rápido en una app generada, a menudo es un ajuste fácil de esquema cuando ves qué filtros aparecen en cada página.

Ejemplo: si guardas {"tags":["beta","finance"],"region":"us"} en JSONB, usa GIN cuando filtras por conjuntos de atributos (@>), y añade índices de expresión para las pocas claves que mueven la mayoría de las vistas de lista (plan, region).

Indexado de arrays: cuando GIN destaca

Los arrays son tentadores porque son fáciles de almacenar y de leer. Una columna users.roles text[] o projects.labels text[] puede funcionar bien cuando la pregunta principal es: ¿contiene esta fila un valor? Ahí es exactamente donde un índice GIN ayuda.

GIN es la elección para consultas de pertenencia en arrays. Descompone el array en elementos individuales y construye una búsqueda rápida de filas que los contienen.

Formas de consulta de arrays que suelen beneficiarse:

• Contiene un valor o conjunto: @> (array contains)
• Solapamiento con un conjunto: && (array comparte elementos)
• A veces: = ANY(...), pero @> es más predecible

Un ejemplo típico para filtrar usuarios por rol:

-- Find users who have the "admin" role
SELECT id, email
FROM users
WHERE roles @> ARRAY['admin'];

CREATE INDEX users_roles_gin ON users USING GIN (roles);

Y filtrar proyectos por un conjunto de etiquetas (debe incluir ambas etiquetas):

SELECT id, name
FROM projects
WHERE labels @> ARRAY['billing', 'urgent'];

CREATE INDEX projects_labels_gin ON projects USING GIN (labels);

Donde la gente se sorprende: algunos patrones no usan el índice como esperas. Si conviertes el array a string (array_to_string(labels, ',')) y luego haces LIKE, el índice GIN no ayudará. Además, si necesitas “empieza por” o coincidencias difusas dentro de las etiquetas, eso es territorio de búsqueda de texto, no pertenencia de arrays.

Los arrays también pueden volverse difíciles de mantener cuando se transforman en una mini-base de datos: actualizaciones frecuentes, necesidad de metadatos por elemento (quién añadió la etiqueta, cuándo, por qué) o analítica por etiqueta. En ese punto, una tabla join como project_labels(project_id, label) suele ser más fácil de validar, consultar y evolucionar.

Indexado de búsqueda: texto completo y coincidencias difusas (GIN y GiST)

Para cuadros de búsqueda aparecen dos patrones una y otra vez: búsqueda de texto completo (encontrar registros sobre un tema) y coincidencia difusa (manejar errores tipográficos, nombres parciales y patrones ILIKE). El índice correcto marca la diferencia entre “instantáneo” y “timeouts a los 10k usuarios”.

Búsqueda de texto completo: tsvector + GIN

Usa búsqueda de texto completo cuando los usuarios escriben palabras reales y quieres resultados ordenados por relevancia, como buscar tickets por asunto y descripción. La configuración habitual es almacenar un tsvector (a menudo en una columna generada) e indexarlo con GIN. Se busca con @@ y un tsquery.

-- Tickets: full-text search on subject + body
ALTER TABLE tickets
ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
  to_tsvector('simple', coalesce(subject,'') || ' ' || coalesce(body,''))
) STORED;

CREATE INDEX tickets_search_vec_gin
ON tickets USING GIN (search_vec);

-- Query
SELECT id, subject
FROM tickets
WHERE search_vec @@ plainto_tsquery('simple', 'invoice failed');

-- Customers: fuzzy name search using trigrams
CREATE INDEX customers_name_trgm
ON customers USING GIN (name gin_trgm_ops);

SELECT id, name
FROM customers
WHERE name ILIKE '%jon smth%';

Qué almacenar en el vector: solo los campos que realmente buscas. Si incluyes todo (notes, logs internos), pagas en tamaño de índice y coste de escritura.

Coincidencias difusas: trigramas con GIN o GiST

Usa similitud por trigramas cuando los usuarios buscan nombres, emails o frases cortas y necesitas coincidencias parciales o tolerancia a errores. Los trigramas ayudan con ILIKE '%term%' y operadores de similitud. GIN suele ser más rápido para búsquedas de “coincide o no”; GiST puede encajar mejor cuando también te importa ordenar por similitud.

Reglas prácticas:

• Usa GIN + tsvector para búsqueda de texto basada en relevancia.
• Usa trigramas para ILIKE y búsquedas de nombres tolerantes a errores.

Peligros a vigilar:

• Wildcards iniciales sin trigramas (ILIKE '%abc') fuerzan escaneos.
• Términos de búsqueda muy cortos (1-2 caracteres) a menudo no aprovechan bien los trigramas.
• Stop words y stemming pueden sorprender a los usuarios en resultados de texto completo; elige una configuración que encaje con el idioma de tu producto.

Si vas a lanzar pantallas de búsqueda rápido, trata el índice como parte de la funcionalidad: la UX de búsqueda y la elección de índice deben diseñarse juntas.

Paso a paso: de consulta lenta al índice correcto

Empieza con la consulta exacta que ejecuta tu app, no con una suposición. Una “pantalla lenta” suele ser una sentencia SQL con un WHERE y ORDER BY muy específicos. Cópiala de logs, del output de debug de tu ORM o de la captura de consultas que ya uses.

Un flujo de trabajo que funciona en apps reales:

• Captura el SQL completo, incluyendo WHERE, ORDER BY y LIMIT.
• Ejecuta EXPLAIN (ANALYZE, BUFFERS) sobre la misma consulta.
• Fíjate en los operadores que hacen el trabajo (=, >=, LIKE, @>, @@), no solo en los nombres de columnas.
• Añade el índice más pequeño que coincida con esos operadores.
• Vuelve a ejecutar EXPLAIN (ANALYZE, BUFFERS) con volumen de datos realista.

Aquí tienes un ejemplo concreto. Una página de Customers filtra por tenant y status, ordena por lo más nuevo y pagina:

SELECT id, created_at, email
FROM customers
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT 50;

Si EXPLAIN muestra un escaneo secuencial y una ordenación, un índice B-tree que coincida con el filtro y el orden suele arreglarlo:

CREATE INDEX ON customers (tenant_id, status, created_at DESC);

Si la parte lenta es un filtrado JSONB como metadata @> '{"plan":"pro"}', eso apunta a GIN. Si es búsqueda de texto completo como to_tsvector(...) @@ plainto_tsquery(...), eso también apunta a un índice de búsqueda respaldado por GIN. Si son operadores de “coincidencia más cercana” u overlap, ahí GiST suele encajar.

Después de añadir el índice, mide el intercambio. Comprueba el tamaño del índice, el tiempo de inserción y actualización, y si ayuda a las consultas lentas principales o solo a un caso extremo. En proyectos que avanzan rápido (incluidos los generados con Koder.ai), esta comprobación te ayuda a evitar acumular índices sin uso.

Errores comunes de indexado que gastan tiempo y dinero

La mayoría de problemas con índices no son elegir B-tree vs GIN vs GiST. Son construir un índice que parece correcto pero no coincide con cómo la app consulta la tabla.

Errores que suelen perjudicar más:

• Índices que nunca se usan. La consulta usa un operador distinto del que soporta el índice, o un índice compuesto tiene el orden de columnas equivocado. Si tu WHERE empieza con tenant_id y created_at, pero el índice empieza con created_at, el planner puede descartarlo.
• Indexar columnas de baja cardinalidad solas. Un índice aislado en status, is_active o un booleano a menudo ayuda poco porque coincide con demasiadas filas. Combínalo con una columna selectiva (como tenant_id o created_at) o sáltatelo.
• Índices solapados que inflan y ralentizan escrituras. Índices similares en la misma tabla pueden duplicar almacenamiento y hacer inserciones y actualizaciones más lentas.
• Puntos ciegos en paginación. Indexar solo para soportar OFFSET es una trampa común. Si usas paginación por keyset, necesitas un índice que coincida con el orden y el filtro del último-seen.
• Estadísticas obsoletas y tabla desordenada. Si autovacuum no está al día, o ANALYZE no se ha ejecutado recientemente, el planner puede elegir planes malos incluso cuando existe el índice correcto.

Un ejemplo concreto: tu pantalla de Invoices filtra por tenant_id y status, luego ordena por created_at DESC. Un índice solo en status ayudará muy poco. Un ajuste mejor es un índice compuesto que empiece con tenant_id, luego status, luego created_at (filtrado primero, orden al final). Ese cambio suele vencer a añadir tres índices separados.

Trata cada índice como un coste. Tiene que ganarse su puesto con consultas reales, no solo en teoría.

Lista rápida de comprobaciones antes de desplegar cambios de índice

Los cambios de índice son fáciles de enviar y molestos de deshacer si aumentan el coste de escritura o bloquean una tabla ocupada. Antes de hacer merge, trátalo como una pequeña release.

Empieza decidiendo qué optimizas. Extrae dos rankings cortos de tus logs o monitorización: las consultas que se ejecutan con más frecuencia, y las consultas con mayor latencia. Para cada una, escribe la forma exacta: columnas de filtrado, orden, joins y operadores usados (equals, range, IN, ILIKE, operadores JSONB, array contains). Esto evita adivinar y te ayuda a elegir el tipo de índice correcto.

Checklist previo al despliegue:

• Confirma que la consulta está acotada por tenant cuando debe.
• Haz que el índice coincida con el operador: B-tree para igualdad/rango/orden, GIN para pertenencia (arrays, JSONB, texto completo), GiST para casos de solapamiento o distancia.
• Prefiere un índice compuesto que coincida con filtro + orden comunes, en lugar de varios índices de columna única.
• Mantén el índice estrecho: incluye solo las columnas que la consulta realmente usa.
• Planifica el despliegue: ¿la creación del índice bloqueará escrituras y necesitas programarla en horas de baja actividad?

Tras añadir el índice, verifica que ayudó en el plan real. Ejecuta EXPLAIN (ANALYZE, BUFFERS) sobre la consulta exacta y compara antes y después. Luego vigila el comportamiento en producción durante un día:

• ¿Bajó la latencia de lectura para las pantallas objetivo?
• ¿Aumentó la latencia de escritura (inserts/updates)?
• ¿Subieron CPU o almacenamiento por el nuevo índice?
• ¿Se está usando realmente el índice o es peso muerto?

Si construyes con Koder.ai, vale la pena mantener el SQL generado para una o dos pantallas lentas junto al cambio, de modo que el índice coincida con lo que la app realmente ejecuta.

Ejemplo: indexando un flujo de trabajo típico de SaaS + siguientes pasos

Imagina una pantalla administrativa común: una lista de Users con scope por tenant, unos pocos filtros, orden por última actividad y un cuadro de búsqueda. Aquí los índices dejan de ser teoría y empiezan a ahorrar tiempo real.

Tres formas de consulta que verás normalmente:

-- 1) List page with tenant + status filter + sort
SELECT id, email, last_active_at
FROM users
WHERE tenant_id = $1 AND status = $2
ORDER BY last_active_at DESC
LIMIT 50;

-- 2) Search box (full-text)
SELECT id, email
FROM users
WHERE tenant_id = $1
  AND to_tsvector('simple', coalesce(name,'') || ' ' || coalesce(email,'')) @@ plainto_tsquery($2)
ORDER BY last_active_at DESC
LIMIT 50;

-- 3) Filter on JSON metadata (plan, flags)
SELECT id
FROM users
WHERE tenant_id = $1
  AND metadata @> '{"plan":"pro"}'::jsonb;

Un conjunto de índices pequeño pero intencional para esta pantalla:

• B-tree compuesto para la vista de lista: (tenant_id, status, last_active_at DESC).
• GIN para búsqueda: una columna tsvector generada con un índice GIN.
• Indexado JSONB según uso: GIN (metadata) cuando usas mucho @>, o un índice de expresión B-tree como ((metadata->>'plan')) cuando filtras mayormente por una clave.

Las necesidades mixtas son normales. Si una página hace filtros + búsqueda + JSON, evita meterlo todo en un mega-índice. Mantén el B-tree para orden/paginación y añade un índice especializado (a menudo GIN) para la parte más cara.

Siguientes pasos: elige una pantalla lenta, anota sus 2-3 formas de consulta principales y revisa cada índice por propósito (filtro, orden, búsqueda, JSON). Si un índice no coincide claramente con una consulta real, elimínalo del plan. Si iteras rápido con Koder.ai, hacer esta revisión a medida que añades pantallas puede prevenir la proliferación de índices mientras tu esquema cambia.