Nivel 2 · 25 min

Tipos de Índice

PostgreSQL ofrece múltiples tipos de índice optimizados para diferentes patrones de acceso: B-tree para comparaciones ordenadas, GIN para datos compuestos (arrays, JSONB, full-text), GiST para tipos geométricos y rangos, BRIN para datos con correlación física, y Hash para igualdad exacta.

B-tree, GIN, GiST y BRIN

B-tree es el default y cubre igualdad, rangos y ordenamiento. GIN (Generalized Inverted Index) indexa los elementos dentro de valores compuestos —ideal para arrays, JSONB y full-text search. GiST soporta tipos geométricos, rangos y exclusión. BRIN (Block Range Index) es extremadamente pequeño y funciona bien cuando los datos tienen correlación natural con el orden físico de la tabla (ej. timestamps de logs).

Índices Parciales y Cubrientes

Los índices parciales tienen una cláusula WHERE y solo indexan un subconjunto de filas: CREATE INDEX idx_active ON users(email) WHERE active = true. Mucho más pequeños y rápidos para consultas que siempre filtran ese subconjunto. Los índices cubrientes incluyen columnas adicionales con INCLUDE: CREATE INDEX ON orders(user_id) INCLUDE (status, total) —permite index-only scan sin tocar la tabla.

Mantenimiento de Índices

Los índices se degradan con el tiempo por bloat (páginas vacías de filas eliminadas). VACUUM reclaim espacio pero no lo devuelve al OS; VACUUM FULL sí pero requiere lock. REINDEX reconstruye el índice; REINDEX CONCURRENTLY lo hace sin bloquear. Monitoreá pg_stat_user_indexes para detectar índices no usados (idx_scan = 0 después de mucho tiempo).

Puntos clave

GIN para arrays y JSONB, GiST para geometría y rangos, BRIN para datos con correlación física como timestamps.
Los índices parciales pueden ser 10x más pequeños que índices completos cuando el filtro es selectivo.
Monitoreá pg_stat_user_indexes para detectar índices sin uso —tienen costo de escritura sin beneficio de lectura.

Code example

-- Índice parcial: solo usuarios activos
CREATE INDEX idx_users_active_email ON users(email) WHERE deleted_at IS NULL;

-- Índice cubriente: evita hit a la tabla
CREATE INDEX idx_orders_user ON orders(user_id)
INCLUDE (status, total, created_at);

-- GIN para JSONB
CREATE INDEX idx_events_payload ON events USING GIN(payload jsonb_path_ops);

-- BRIN para logs con timestamp
CREATE INDEX idx_logs_ts ON server_logs USING BRIN(created_at);