Problemas típicos al trabajar con índices

Hasta el ordenador más moderno se queda pillado si le echas limonada en vez de gasolina. Lo mismo pasa con los índices en PostgreSQL. Son una herramienta muy potente, pero hay que usarlos con cabeza. Vamos a ver algunos problemas típicos relacionados con los índices.

Problema 1: indexación excesiva

Para empezar, vamos a repasar el tema de la antepenúltima clase. Cuando tienes demasiados índices en una tabla, PostgreSQL tiene que trabajar con cada uno de ellos para mantenerlos actualizados. Esto afecta directamente a las operaciones de inserción, actualización y borrado. ¡Porque cada índice hay que actualizarlo y sincronizarlo!

Supón que tienes una tabla students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    age INTEGER,
    grade INTEGER
);

Y decides crear índices en cada columna “por si acaso”:

CREATE INDEX idx_students_name ON students(name);
CREATE INDEX idx_students_age ON students(age);
CREATE INDEX idx_students_grade ON students(grade);

Ahora imagina que insertas 10 mil registros nuevos. PostgreSQL no solo tiene que guardar los datos en la tabla, sino también actualizar los tres índices. Si hay muchos datos, la velocidad de escritura baja y el rendimiento del sistema sufre.

¿Cómo evitar este problema? Antes de crear un índice, hazte dos preguntas:

1. ¿Con qué frecuencia esta columna se usa en filtrado (WHERE), ordenación (ORDER BY) o agrupación (GROUP BY)?
2. ¿La consulta va a usar este índice o igual hará un escaneo completo de la tabla?

Si la respuesta a ambas es “rara vez” o “nunca”, no necesitas ese índice.

Problema 2: elegir mal las columnas para el índice

Crear un índice en datos con poca variedad es como intentar echar té en una taza con la tapa pegada: el resultado es casi inútil. Si una columna solo tiene 2-3 valores únicos, PostgreSQL seguramente hará un escaneo completo de la tabla en vez de usar el índice.

Supón que tienes una tabla courses:

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    level VARCHAR(10) -- Puede ser solo 'Beginner', 'Intermediate' o 'Advanced'
);

Y decides crear un índice en la columna level:

CREATE INDEX idx_courses_level ON courses(level);

Pero la consulta:

SELECT * FROM courses WHERE level = 'Beginner';

puede no usar el índice, porque PostgreSQL calcula que es más fácil escanear toda la tabla que mirar el índice. Esto pasa sobre todo en tablas pequeñas y datos con poca variedad.

Por eso los índices tienen sentido en columnas con alta cardinalidad (o sea, muchas valores únicos). Para datos con poca variedad, mejor usa otros mecanismos de optimización, como particionar la tabla.

Problema 3: índices obsoletos

A veces se crean índices y luego se olvidan, aunque ya no se usen. Es como los archivos en el escritorio: al principio hay dos, tres, cinco. Y de repente te ves perdiendo tiempo buscando el que necesitas entre un montón de iconos... ¿Te suena?

Supón que creamos un índice para una funcionalidad antigua, luego cambiamos la lógica de las consultas y añadimos un índice nuevo. El índice viejo ya no lo usa nadie, pero sigue ocupando espacio y ralentizando las escrituras.

Para evitarlo, revisa y analiza los índices de vez en cuando. PostgreSQL tiene una métrica muy útil:

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS total_scans
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0;

Aquí idx_scan muestra cuántas consultas han usado ese índice. Si el valor es 0, el índice no se usa y puedes borrarlo:

DROP INDEX idx_courses_level;

Problema 4: índices en columnas que se actualizan mucho

Si tienes un índice en una columna que actualizas a menudo, PostgreSQL tiene que reconstruir ese índice cada vez que cambias algo. Esto puede bajar mucho el rendimiento.

Imagina una tabla con datos de pedidos:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20), -- Puede cambiar varias veces (por ejemplo, "nuevo", "en proceso", "finalizado")
    total NUMERIC(10, 2)
);

Creas un índice en la columna status para filtrar más rápido por estado:

CREATE INDEX idx_orders_status ON orders(status);

Pero si el estado se actualiza decenas de veces por registro, el índice empieza a fastidiar el rendimiento.

Para evitar esto, no crees índices en columnas que cambian mucho. Si de verdad necesitas el índice, piensa en usar índices parciales:

CREATE INDEX idx_orders_status_partial
ON orders(status) 
WHERE status = 'en proceso';

Así, el índice solo se actualiza para los registros con ese valor.

Problema 5: restricciones UNIQUE en columnas innecesarias

Los índices únicos (UNIQUE) se crean automáticamente para mantener la unicidad de los datos. Pero si la unicidad no es realmente necesaria, estos índices meten carga extra.

Supón que creamos una tabla de logs:

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP UNIQUE
);

Si cada segundo se añaden miles de registros, mantener la unicidad en created_at mete mucha carga.

Para que todo vaya bien, deja las restricciones UNIQUE solo donde de verdad hagan falta. En nuestro ejemplo, si la unicidad en created_at no es obligatoria, cambia el índice por uno normal:

CREATE INDEX idx_logs_created_at ON logs(created_at);

Problema 6: mal uso de índices combinados

Los índices combinados (multi-column indexes) son útiles si las consultas filtran u ordenan por varias columnas a la vez. Pero hay que crearlos bien, si no, no se usan.

Supón que tienes este índice:

CREATE INDEX idx_students_name_grade ON students(name, grade);

Este índice se usa si la consulta filtra u ordena por ambas columnas:

SELECT * FROM students WHERE name = 'Alice' AND grade = 90;

Pero la consulta:

SELECT * FROM students WHERE grade = 90;

no usa este índice, porque el campo name va primero.

Para evitar el problema, crea índices combinados solo en el orden en que más se usan en las consultas. Si necesitas filtrar solo por una columna, crea índices separados.

Consejos útiles

Monitorea el uso de los índices. PostgreSQL tiene la vista del sistema pg_stat_user_indexes, donde puedes ver qué índices se usan y cuáles no.

Optimiza las consultas junto con los índices. Las consultas malas seguirán siendo malas aunque pongas índices.

No te olvides de borrar. Los índices viejos solo ocupan espacio y ralentizan las escrituras.

¡Y eso es todo, colegas! Los índices son una herramienta potente, pero acuérdate siempre de que con gran poder viene gran responsabilidad. Usa los índices con cabeza y tu base de datos irá como un cohete de SpaceX!