¿Índices compuestos: salvación o trampa mortal para el rendimiento de tu BD?

Jewelry

Hola a todos, llevo unos meses administrando una base de datos PostgreSQL que ya tiene cierta carga (unos 50GB y creciendo). El equipo de desarrollo empezó a crear índices compuestos por su cuenta para acelerar consultas de filtrado por varias columnas (ej. WHERE status = 'activo' AND created_at > ...). Al principio notamos una mejora brutal, pero ahora veo que el tamaño de la tabla de índices se ha disparado y algunas escrituras (INSERT/UPDATE) van más lentas. ¿Cuál es la mejor práctica? ¿Debería limitar el número de índices compuestos? ¿O hay alguna estrategia para saber cuándo un índice compuesto es realmente necesario? Agradezco cualquier experiencia o referencia.

Gamer

Muy buen tema. En mi experiencia, los índices compuestos son poderosos pero hay que usarlos con cabeza. La regla de oro es: el orden de las columnas importa (para igualdad, luego rango, luego ordenación). Si tienes WHERE status = ? AND created_at > ?, un índice (status, created_at) funciona bien, pero (created_at, status) no será tan eficiente para esa consulta. Además, revisa si los índices se están usando realmente con pg_stat_user_indexes y elimina los que no se usen. No es tanto el número, sino la redundancia.

Blues

Estoy de acuerdo con lo de los índices redundantes, pero no olvidemos el coste de mantenimiento. Cada índice adicional ralentiza las escrituras (no solo por el espacio, sino por el árbol B+ que hay que actualizar). Yo suelo aplicar la regla del 80/20: si una consulta no es crítica o no se ejecuta con frecuencia, no le doy índice compuesto. Para el caso de filtros con varias columnas, a veces es mejor un índice parcial (si siempre filtras por un estado concreto) o incluso usar particionamiento si la tabla es enorme.

Folk

Ojo, que no todo es PostgreSQL. En MySQL (InnoDB) los índices compuestos también tienen su aquel. Pero mi consejo es que primero midas el impacto real. Utiliza EXPLAIN ANALYZE para ver si el índice se usa de verdad y si el coste de la consulta baja significativamente. A veces el optimizador prefiere un índice de una sola columna y un filtro posterior. No te fíes de las suposiciones.

Jewelry

Gracias por las respuestas. Me ha quedado claro que debo analizar los índices existentes y ver cuáles son redundantes. El tema del orden de columnas me ha hecho pensar: tenemos un índice (created_at, status) que probablemente no se esté usando para las consultas típicas. Voy a revisar con EXPLAIN y también a monitorizar el ratio de escritura. ¿Alguna herramienta recomendada para visualizar el uso de índices en PG? (pg_stat_user_indexes ya lo conozco, pero quizá algo más visual).

Drones

Yo uso pg_stat_statements para ver las consultas más lentas y luego cruzo con los índices. También puedes probar con pg_badger para sacar un informe. Pero cuidado: si borras índices que el optimizador podría usar en el futuro, puedes generar problemas. Te recomiendo que primero marques los índices sospechosos como INVALID (en PG12+) o los dejes desactivados temporalmente y midas el rendimiento en un entorno de staging.

BoardGames

No estoy de acuerdo con desactivar índices a la ligera. Eso puede causar timeouts en producción. Lo correcto es hacer una carga sintética con pgbench o similar, y probar con y sin índices. Además, no te olvides de las estadísticas: si no ejecutas ANALYZE con frecuencia, el optimizador puede ignorar índices por falta de datos actualizados. A veces el problema no es el índice, sino la falta de vacuum/analyze.

Alt

Otro punto: en bases de datos con alta concurrencia, los índices compuestos pueden causar contención de bloqueos en las páginas de índice. Si las escrituras son críticas, considera usar índices con menos columnas o incluso índices cubrientes (con INCLUDE) para que no haya que ir a la tabla. Pero eso solo si es necesario. En resumen: menos es más, pero bien medido.

Jewelry

Perfecto, me llevo un montón de conceptos. Voy a hacer un análisis profundo con pg_stat_user_indexes y EXPLAIN para cada consulta lenta. También probaré la idea de los índices cubrientes para una consulta muy concreta que devuelve pocas columnas. Gracias a todos por el debate, me ha sido muy útil. Seguro que vuelvo con más dudas de infraestructura.

FilmScore

Buen cierre. Solo un matiz: no te cases con los índices cubrientes para todo. En consultas con filtros muy selectivos, un índice compuesto clásico (sin INCLUDE) suele ser más eficiente porque el optimizador puede usar el índice como un 'índice de acceso' y evitar ordenaciones. El INCLUDE es ideal cuando el filtro es poco selectivo y quieres evitar el lookup, pero añade tamaño y coste de escritura no trivial. Antes de decidir, ejecuta un EXPLAIN (ANALYZE, BUFFERS) comparando ambas variantes con datos reales; el plan te dirá cuál gana. Y sobre el ANALYZE: si usas autovacuum con defaults, puede que no se dispare lo suficiente en tablas muy activas. Ajusta autovacuum_analyze_scale_factor y autovacuum_analyze_threshold si ves planes erráticos. Ah, y no olvides revisar pg_stat_user_indexes tras un par de días de carga para ver si algún índice no se usa: esos son candidatos claros a borrar.