El B-tree cubre la mayoría de casos de indexación en PostgreSQL, pero cuando los datos o el patrón de consulta se salen de lo estándar, hay herramientas más específicas —índices parciales, de expresión, BRIN, GIN, GiST— que pueden marcar una diferencia enorme en tamaño y velocidad. Esto es cuándo usar cada una.

Índices parciales: para cuando solo te importa un subconjunto de filas

Caso habitual: una tabla users con millones de filas, donde el 90% de las queries filtra por usuarios activos y solo un 10% consulta los archivados:

-- La mayoría de queries
SELECT * FROM users WHERE is_active = true AND created_at > NOW() - INTERVAL '30 days';

-- Una minoría de queries
SELECT * FROM users WHERE is_active = false;

Un índice normal indexaría todas las filas, activas y archivadas por igual:

CREATE INDEX idx_users ON users(is_active, created_at);

Un índice parcial solo indexa el subconjunto que de verdad se consulta con frecuencia:

CREATE INDEX idx_users_active ON users(created_at)
WHERE is_active = true;

El resultado es un índice notablemente más pequeño (solo cubre usuarios activos), más rápido de construir y con menor impacto en el rendimiento de escritura, porque los inserts de usuarios archivados ni siquiera lo tocan. Como regla general: si una condición WHERE cubre una fracción pequeña de la tabla y se repite en muchas queries, es buena candidata para un índice parcial.

Índices de expresión: computar sin penalizar cada lectura

Problema típico: búsquedas case-insensitive.

SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

Sin índice, esto es un Seq Scan. Un índice normal en email tampoco ayuda, porque LOWER() transforma el valor antes de comparar. La solución es indexar directamente la expresión:

CREATE INDEX idx_email_lower ON users(LOWER(email));

Con eso, la misma query pasa a usar Index Scan. El mismo principio aplica a búsqueda de texto completo:

CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector('english', content));

SELECT * FROM posts
WHERE to_tsvector('english', content) @@ plainto_tsquery('english', 'hello world');

El coste es que escribir se vuelve algo más lento, porque hay que calcular la expresión en cada insert. Vale la pena si la mayoría de tus queries usan esa expresión.

BRIN: el índice que nadie usa y debería usarse más para series temporales

Imagina una tabla events con miles de millones de filas, ordenadas de forma natural por fecha de inserción. Una query típica:

SELECT * FROM events WHERE timestamp > NOW() - INTERVAL '7 days';

Un índice B-tree sobre esa columna puede llegar a ocupar decenas de gigabytes en tablas de ese tamaño. Un índice BRIN sobre la misma columna puede ocupar una fracción minúscula de eso:

CREATE INDEX idx_events_brin ON events USING BRIN(timestamp);

BRIN ("Block Range Index") no indexa fila por fila, sino rangos de bloques de disco. Para datos ordenados temporalmente, esto funciona casi tan rápido como un B-tree en las queries típicas, con una fracción de su tamaño en disco. La contrapartida: si la tabla no está realmente ordenada por esa columna (por ejemplo, si se insertan filas fuera de orden), BRIN deja de ser eficiente. Para series temporales bien ordenadas, es una de las herramientas más infrautilizadas de PostgreSQL.

GIN vs GiST: JSON, arrays y datos espaciales

GIN (Generalized Inverted Index) es la mejor opción para consultas sobre JSON, búsquedas en arrays y texto completo:

CREATE TABLE documents (id INT, data JSONB);
CREATE INDEX idx_docs_gin ON documents USING GIN(data);

SELECT * FROM documents WHERE data @> '{"author": "John"}';

GiST (Generalized Search Tree) está pensado para datos geométricos y búsquedas espaciales, como las que usa PostGIS:

CREATE TABLE locations (id INT, point POINT);
CREATE INDEX idx_locations_gist ON locations USING GiST(point);

SELECT * FROM locations WHERE point <-> POINT(0,0) < 1;

Índices multicolumna: el orden de las columnas importa

CREATE INDEX idx_users_status_date ON users(status, created_at);

Este índice es óptimo para:

SELECT * FROM users WHERE status = 'active' AND created_at > '2026-01-01'; -- Index Scan

Pero no ayuda a esta otra, porque un B-tree se evalúa de izquierda a derecha y aquí falta la primera columna:

SELECT * FROM users WHERE created_at > '2026-01-01'; -- Seq Scan

Si de verdad necesitas cubrir ambos patrones, la solución es crear los dos índices por separado —el coste de almacenamiento extra suele ser pequeño comparado con el beneficio.

Mantenimiento: bloat y monitorización

Los índices crecen con las actualizaciones: las entradas viejas no se borran de inmediato, solo se marcan como inutilizables. Si el bloat se acumula demasiado, conviene reconstruir:

REINDEX INDEX CONCURRENTLY idx_users;

Para detectar índices que nunca se usan y candidatos a eliminar:

SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

Un índice con idx_scan = 0 nunca se ha usado desde el último reinicio de estadísticas, y normalmente es seguro eliminarlo —cada índice de más ralentiza los inserts y updates sin aportar nada a cambio.

Medir el impacto real

pgbench permite comparar el rendimiento antes y después de añadir un índice de forma objetiva, en vez de fiarte de la sensación:

pgbench -i mydb
pgbench -c 10 -j 4 -t 1000 mydb

Resumen de estrategia

1. Empieza simple: B-tree en claves foráneas y columnas filtradas con frecuencia
2. Perfila con EXPLAIN ANALYZE para encontrar las queries lentas de verdad
3. Optimiza con la herramienta que corresponda: índice parcial, de expresión, o BRIN según el caso
4. Monitoriza el uso mensualmente, elimina lo que no se usa, reconstruye lo que tiene bloat
5. Mide con pgbench antes y después para confirmar la mejora

Fuentes:

• PostgreSQL — Tipos de índices
• Depesz Blog