Un índice en PostgreSQL acelera las lecturas pero ralentiza las escrituras. Saber postgresql índices cuándo usar es la diferencia entre una query que termina en milisegundos y un report que bloquea la aplicación por minutos. Creas el índice en la columna correcta, el planner lo usa, el rendimiento mejora. Lo creas en la columna incorrecta, malgastas disco y CPU en cada INSERT.
| Tipo Índice | Cuándo usarlo | Ejemplo típico |
|————-|—————|—————-|
| B-tree | Búsquedas exactas, rangos, ordenamiento | ID, fechas, estados |
| Hash | Igualdad pura (raro) | Lookup clave-valor |
| GIN | Arrays, JSONB, full-text | Tags, documentos |
| GiST | Datos geoespaciales, rangos | Coordenadas GPS |
Cuando migré y optimicé la base de datos de una organización sanitaria pública con tablas de más de 50 millones de filas, el problema no era la potencia del servidor. Era la ausencia de índices en las claves foráneas y el abuso de índices multicolumn en el orden incorrecto. El 73% de los breach parte de credenciales robadas (Verizon DBIR 2025), pero el 100% de las quejas de usuarios parte de una aplicación lenta. Una query sin índice en producción es un riesgo para la continuidad del servicio, igual que una vulnerabilidad.
Requisitos Previos para probar PostgreSQL Índices
Necesitas una instancia PostgreSQL 14 o superior. Puedes usar un container Docker para aislar las pruebas. Crea una base de datos de test con algunos millones de filas usando generate_series para notar la diferencia real entre Seq Scan e Index Scan. No hagas pruebas de rendimiento sobre 100 filas. El planner se comportaría de forma distinta.
Cómo funciona un índice B-tree
Un B-tree es una estructura de datos equilibrada. Imagina el índice de un libro. En lugar de recorrer las 500 páginas para encontrar la palabra «PostgreSQL«, vas a la letra P, luego a las páginas indicadas. PostgreSQL hace lo mismo. Divide los datos en bloques y crea un árbol de referencia. La búsqueda desciende de arriba abajo, reduciendo los bloques leídos de millones a muy pocos. Esto reduce las operaciones de I/O en disco, el recurso más lento del sistema.
Cuándo PostgreSQL usa el índice (Seq Scan vs Index Scan)
PostgreSQL no usa siempre el índice. Si una query devuelve un gran porcentaje de la tabla (a menudo más del 5-10%), el planner elige un Sequential Scan. Leer toda la tabla de forma secuencial es más rápido que hacer miles de saltos lógicos entre índice y tabla. Esto asusta a quien espera siempre un Index Scan. El planner hace una estimación basada en estadísticas. Si las estadísticas son antiguas, decide mal. Por eso el ANALYZE automático es vital.
Crear un índice en producción
La sintaxis básica es directa:
CREATE INDEX idx_nombre ON tabla(columna);Cuidado. Este comando bloquea las escrituras en la tabla. En un entorno con 2.000 endpoints conectados, un lock de 30 segundos en una tabla central significa timeout en cascada. Para producción, usa siempre la variante concurrente:
CREATE INDEX CONCURRENTLY idx_nombre ON tabla(columna);Tarda más, pero no bloquea INSERT, UPDATE o DELETE.
Índices parciales y multicolumn
No indiques todo. Si buscas solo los registros activos, indiza solo esos. Ahorras espacio en disco y reduces la carga de mantenimiento durante las escrituras.
CREATE INDEX CONCURRENTLY idx_parcial ON tabla(columna) WHERE activo = true;He visto tablas de 10 millones de filas donde el 80% de los registros tenía estado «archivado». Un índice parcial en las filas activas redujo las lecturas un 80% y las escrituras un 70%. PostgreSQL no actualiza el índice cuando modificas un registro archivado.
Si tu query filtra por dos columnas, un índice multicolumn puede servir. El orden de las columnas es importante. Pon primero la columna más selectiva o la que filtras sola con más frecuencia.
CREATE INDEX idx_multi ON tabla(col1, col2);Un índice en (apellido, nombre) ayuda a buscar por apellido y nombre, o por solo apellido. No ayuda a buscar por solo nombre. Es la regla del prefijo más a la izquierda.
Tipos de índice y su impacto en la performance
B-tree es el default. Cubre el 95% de los casos: igualdad, rango, ordenamiento. Hash gestiona solo igualdad pura, pero rara vez ofrece ventajas sobre el B-tree moderno. GIN es perfecto para arrays y JSONB. Si buscas dentro de un campo JSONB o un array de tags, GIN es la elección correcta. GiST se usa para datos geoespaciales con PostGIS o rangos de fechas superpuestos. Elegir el tipo incorrecto significa tener un índice que el planner ignora. La postgresql index performance depende de esta elección inicial.
EXPLAIN ANALYZE para verificar el uso
No adivines. Mide. Ejecuta siempre EXPLAIN ANALYZE antes y después de crear un índice.
EXPLAIN ANALYZE SELECT * FROM tabla WHERE columna = 'valor';Si ves «Seq Scan», el índice no se usa. Si ves «Index Scan», funciona. Observa el costo estimado y el tiempo real de ejecución. Si el planner elige Seq Scan incluso con el índice, podría tener razón. O las estadísticas están obsoletas. Fuerza el índice para probar con SET enable_seqscan = off;, mide, luego reactiva el seq scan. Si con el índice es más lento, el planner tenía razón.
Cuándo NO usar un índice
No crees índices en columnas con baja selectividad, como un campo booleano en una tabla donde el 90% de los registros es verdadero. No indiques columnas que solo se leen en bloque, como los logs insertados continuamente y leídos rara vez con filtros específicos. Cada índice añade overhead a cada INSERT, UPDATE y DELETE. En tablas con escrituras intensas, demasiados índices matan el rendimiento de escritura e inflan el storage. Un report de Gartner de 2024 indica que el 40% de las operaciones DBA en entornos enterprise se dedica a resolver problemas de rendimiento causados por configuraciones subóptimas, índices incluidos.
Mantenimiento y detección de índices inútiles
Los índices se fragmentan. Tras millones de UPDATE y DELETE, se crea el bloat: espacio vacío dentro de las páginas del índice que ocupa disco y ralentiza las lecturas. Ejecuta REINDEX para reconstruir el índice. En producción sobre PostgreSQL 12+, usa REINDEX CONCURRENTLY para evitar lock.
REINDEX INDEX CONCURRENTLY idx_nombre;Monitoriza el bloat con la extensión pgstattuple. Si el bloat supera el 20-30%, es momento de mantenimiento.
Los índices inutilizados cuestan recursos. Cuestan espacio en disco y ralentizan cada escritura sin dar nada a cambio. PostgreSQL registra cuántas veces se usa cada índice.
SELECT indexname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;Si idx_scan es 0 desde hace meses, elimina el índice. Haz un backup de la definición antes de dropearlo. En una auditoría sobre un database con 300+ tablas, encontré 45 índices nunca usados. Al eliminarlos, las escrituras mejoraron un 15% y recuperamos 20 GB de espacio.
Errores Comunes y Resolución de Problemas
- Índice creado pero Seq Scan persiste: las estadísticas son antiguas. Ejecuta ANALYZE tabla;
- Índice en tipo incorrecto: comparaciones entre tipos distintos (ej. varchar vs text) impiden el uso del índice.
- Uso de funciones en la columna: WHERE LOWER(columna) = ‘valor’ ignora un índice estándar en columna. Crea un índice funcional: CREATE INDEX idx_lower ON tabla(LOWER(columna));
Conclusiones con Puntos Clave Operativos
Los índices no son un botón para pulsar al azar. Debes saber postgresql índices cuándo usar. Crea índices en las columnas usadas en las WHERE y las JOIN. Usa EXPLAIN ANALYZE para verificar que el planner los usa. Elige el índice parcial cuando sea posible. Usa CONCURRENTLY en producción. Monitoriza y elimina los índices inutilizados. Haz mantenimiento periódico para controlar el bloat. Un database bien indexado reduce los costes de infraestructura y mejora la experiencia de usuario. Un database mal indexado te obliga a añadir RAM y CPU para enmascarar problemas de diseño.
Lee también: Lynis Audit Linux: Guía Práctica de Hardening 2026
Referencia oficial: https://www.postgresql.org/docs/current/indexes.html
