- Los índices de PostgreSQL son una estructura clave para acelerar el acceso a los datos, ya que reducen la cantidad de datos que deben leerse desde disco y mejoran el rendimiento de las consultas
- Los índices se ofrecen en varias formas, como Btree, Hash, BRIN, GIN, GiST y SP-GiST, y cada una está optimizada para distintas características de datos y patrones de consulta
- Los índices implican varios costos, como espacio en disco, rendimiento de escritura, complejidad del planificador de consultas y uso de memoria
- Con funciones avanzadas como índices parciales, índices multicolumna, índices de cobertura e índices de expresión, es posible maximizar la eficiencia en situaciones específicas
- Se destaca que la elección y administración adecuadas de los índices son un factor clave para optimizar el rendimiento de PostgreSQL
Conceptos básicos de los índices
- Un índice es una estructura que acelera las consultas al reducir la cantidad de datos que la base de datos lee desde disco
- Las claves primarias, claves únicas y restricciones de exclusión también se implementan mediante índices
- Los índices son efectivos cuando el resultado de una consulta representa menos del 15~20% de la tabla completa; por encima de eso, un escaneo secuencial puede ser más eficiente
- PostgreSQL ofrece por defecto 6 tipos de índices, y mediante extensiones se pueden usar más tipos
- Cada índice conecta los valores clave con la ubicación de los datos correspondiente (TID)
Estructura de los datos almacenados en disco
- Las tablas de PostgreSQL se almacenan como archivos heap, compuestos por páginas de 8KB
- Cada fila (tuple) se almacena sin un orden específico, y su dirección interna se identifica con ctid (current tuple id)
- Por ejemplo:
(0,1) significa la primera tupla de la página 0
- Los índices enlazan estas ubicaciones del heap (
ctid) en una estructura de árbol para permitir búsquedas rápidas
Cómo los índices aceleran el acceso a los datos
- Sin un índice, PostgreSQL realiza un escaneo secuencial leyendo todas las páginas
- En la consulta de ejemplo, para encontrar
name='Ronaldo', se leen 6272 páginas y toma 265ms
- Al agregar un índice, cambia a un Index Scan y solo lee 4 páginas, completándose en 0.077ms
- El índice mapea el valor con el
ctid para encontrar rápidamente solo las filas necesarias
- El tamaño del archivo de índice puede ser similar al tamaño de la tabla (por ejemplo: una tabla de 30MB → un índice de 30MB)
Factores de costo de los índices
- Además de mejorar el rendimiento, los índices también traen varias cargas adicionales
Espacio en disco
- Los índices ocupan espacio de almacenamiento adicional y pueden ser más grandes que la tabla
- Esto genera costos extra en respaldos, replicación y recuperación ante fallas
- Se puede mejorar la eficiencia del espacio mediante índices parciales, índices multicolumna y BRIN, entre otros
Operaciones de escritura
- En
UPDATE, INSERT y DELETE, si cambian columnas incluidas en un índice, se produce una sobrecarga de actualización del índice
Planificador de consultas
- Cuantos más índices haya, más opciones debe considerar el planificador, lo que aumenta el tiempo para construir el plan de consulta
Uso de memoria
- Las páginas de índice se cargan y almacenan en caché en el shared buffer, por lo que más índices implican mayor uso de memoria
- Debido al límite de tamaño de los nodos btree, cuanto más grandes sean las columnas, mayor será la profundidad del árbol
- También se usa memoria adicional de work memory en ordenamientos, escaneos multicolumna,
vacuum, reindex y otros procesos
Tipos principales de índices
Btree
- Es la estructura de índice predeterminada de PostgreSQL, un índice de propósito general usado en la mayoría de los DBMS
- Permite búsquedas rápidas con complejidad temporal O(log n)
- Tiene una estructura de árbol balanceado donde todos los nodos hoja tienen la misma profundidad
- Es ventajoso para operaciones ORDER BY y JOIN, y se usa para restricciones de clave primaria y clave única
- Los nodos internos almacenan punteros a nodos hijos, y los nodos hoja almacenan claves y punteros al heap
- Gracias a los punteros a nodos izquierdo y derecho, es posible la exploración bidireccional
Uso de múltiples índices
- PostgreSQL puede combinar varios índices mediante operaciones de bitmap AND/OR para procesar condiciones compuestas
- Ejemplo: con la condición
age=30 AND login_count=100, combina los bitmaps de ambos índices
Índices multicolumna
- Agrupar varias columnas en un solo índice permite ahorrar espacio y mejorar la velocidad
- Sin embargo, el orden de las columnas es importante, y el índice solo puede usarse con condiciones que coincidan desde la izquierda
Índices parciales
- Indexan solo filas específicas mediante una expresión condicional
- Reducen el tamaño del índice, mejoran el ajuste en RAM y aceleran las consultas
- Ejemplo:
create index on rules(status) where status='enabled';
- Son útiles cuando la distribución de valores es desigual (
status <> 'TODO', etc.)
Índices de cobertura
- Si todas las columnas requeridas por la consulta están incluidas en el índice, es posible devolver resultados sin acceder al heap (index-only scan)
create index abc_cov_idx on bar(a, b) including c;- Son más eficientes en espacio que los índices multicolumna
Índices de expresión
- Indexan el resultado de una función o expresión, no el valor directo de la columna
- Ejemplo:
CREATE INDEX idx_lower_name ON customers (lower(name));
- Son útiles al buscar con valores transformados como
LOWER(name)
- Solo pueden usarse funciones inmutables (immutable)
Hash
- Es un índice basado en una estructura de hash map, eficiente en espacio para cadenas largas o UUID
- Almacena códigos hash de 32 bits para reducir tamaño
- Solo admite comparaciones de igualdad (
=), y no permite ordenamiento ni índices multicolumna
- Con una distribución hash uniforme, puede ofrecer mejor rendimiento de lectura que Btree
- Según la documentación oficial, los índices hash reducen el I/O en tablas grandes mediante acceso directo a páginas bucket
BRIN (Block Range Index)
- Es un índice que almacena solo los valores mínimos y máximos de cada rango de bloques
- Es muy compacto y amigable con la caché
- Es adecuado para datos a gran escala, append-only y series temporales
- Si las filas se actualizan con frecuencia, la eficiencia disminuye por el almacenamiento duplicado causado por MVCC
- Con la configuración
pages_per_range se puede ajustar el equilibrio entre precisión y tamaño
GIN (Generalized Inverted Index)
- Es un índice adecuado para la búsqueda en datos compuestos
- Permite buscar elementos específicos en texto, arreglos, JSONB y más
- Usa estrategias dedicadas (opclass) según el tipo de dato
- Para JSON se recomienda usar columnas JSONB, y para texto, tsvector o la extensión pg_trgm
GiST y SP-GiST
- El árbol de búsqueda generalizado (GiST) y el árbol de partición espacial (SP-GiST) son frameworks para implementar índices de tipos de datos específicos
- GiST admite árboles balanceados, mientras que SP-GiST admite estructuras no balanceadas
- Se usan en información geográfica, inet, rangos y vectores de texto
- GIN ofrece consultas rápidas, mientras que GiST tiene menores costos de construcción y mantenimiento
- En búsqueda de texto completo, se elige entre ambos según los requisitos
Conclusión
- Los índices son clave para optimizar el rendimiento de PostgreSQL, y es importante equilibrar la mejora en lectura con los costos de escritura y almacenamiento
- Elegir el tipo de índice adecuado según las características de los datos y los patrones de consulta permite una operación de base de datos rápida y eficiente
- Un diseño apropiado de índices es un elemento esencial para garantizar la escalabilidad y estabilidad de sistemas a gran escala
1 comentarios
Comentarios en Hacker News
La documentación oficial de PostgreSQL está realmente muy bien escrita y hasta es entretenida de leer, así que la comparto.
Documento introductorio sobre índices en PostgreSQL
La parte sobre índices multicolumna es casi igual a la forma en que yo lo había aprendido.
Pero me preguntaba si eso seguía siendo cierto incluso en las versiones más recientes de PostgreSQL.
Hace tiempo vi que se usaba un bitmap index scan en una consulta parecida al tercer ejemplo, y desde entonces empecé a replantearme la “sabiduría convencional”.
Como referencia sobre índices, creo que el sitio y el libro Use The Index, Luke son un clásico que vale la pena que lea todo el equipo.
En versiones anteriores también se podía, pero requería un escaneo completo del índice y era ineficiente.
Video relacionado: enlace de YouTube
Creo que sería bueno que PostgreSQL ofreciera soporte nativo para incremental view maintenance.
Es un concepto que, como un índice, se actualiza automáticamente cuando cambian los datos base, pero no se limita a una vista específica y puede aplicarse a vistas arbitrarias.
Hay muchos proyectos relacionados, como Noria, Materialize, Apache Flink, GCP Continuous Queries, Spark Streaming Tables, Delta Tables, ClickHouse streaming tables, TimescaleDB, ksqlDB, StreamSQL, etc.
En PostgreSQL, recientemente una extensión llamada pg_ivm empezó a abordar este problema.
La discusión sobre B-tree vs índices hash es interesante.
Mucha gente piensa que para una columna ID conviene más hash, pero en realidad el B-tree predeterminado suele ser más eficiente.
Sobre todo cuando se insertan valores casi secuenciales, la estructura de árbol resulta más conveniente.
Aun así, en la entrada de blog mencionada esta vez se dice lo contrario: que hash ganó en el benchmark.
El momento en que salió este artículo fue muy oportuno.
La regla de la columna líder en índices multicolumna siempre me había confundido, pero gracias a bitmap index scan ya no es tan crítica como antes.
La función skip scan de PostgreSQL 18 cambia bastante lo que se daba por sentado, así que si aprendiste basándote en versiones anteriores, hace falta actualizar el modelo mental.
Me parece un artículo realmente excelente como material sobre PostgreSQL.
Para todo lo relacionado con índices B-tree, desde hace tiempo consulto mucho Use The Index, Luke.
Creo que es lectura obligatoria.
Va mucho más allá de una simple introducción: tiene profundidad y, mientras no entre en estructuras internas, sigue siendo bastante fácil de leer.
Me gusta este estilo de escritura simple y humilde.
Se siente bien esa forma directa de transmitir conocimiento.