Creación de índices ( CREATE INDEX) y parámetros de indexación ( UNIQUE, CONCURRENTLY)

Ya hemos hablado varias veces de cómo los índices aceleran las búsquedas y ayudan a la base a no tener que recorrer todo. Ahora toca ver cómo se crean exactamente, qué parámetros tiene el comando CREATE INDEX y cuándo conviene usar opciones como UNIQUE o CONCURRENTLY. Todo esto es importante si quieres no solo usar índices, sino gestionarlos de forma inteligente.

Sintaxis de CREATE INDEX

Puedes crear un índice usando el comando CREATE INDEX. Aquí tienes la sintaxis básica:

CREATE INDEX index_name
ON table_name (column_name);

• index_name — El nombre del índice. Lo ideal es que refleje para qué sirve el índice, por ejemplo, idx_users_email para un índice sobre la columna email en la tabla users.
• table_name — El nombre de la tabla para la que creas el índice.
• column_name — La columna que vas a indexar.

Vamos con un ejemplo sencillo. Supón que tienes una tabla users:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    age INT
);

Queremos acelerar la búsqueda de usuarios por el campo email. Creamos el índice así:

CREATE INDEX idx_users_email
ON users (email);

Ahora, cuando hagas consultas como:

SELECT * FROM users WHERE email = 'example@example.com';

PostgreSQL usará el índice idx_users_email para encontrar la fila que buscas mucho más rápido.

Índices únicos (UNIQUE)

Un índice único es una garantía de que los valores en la columna (o columnas) indicada serán únicos. Si intentas meter un valor duplicado, PostgreSQL no te va a dejar hacerlo.

Los índices únicos se usan mucho para claves como email, username u otros identificadores que no deberían repetirse.

Sintaxis para crear un índice único

La sintaxis para crear un índice único es casi igual que la de un índice normal, solo que añades la palabra clave UNIQUE:

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

Supón que en nuestra tabla users el campo email debe ser único, para que no haya dos usuarios con el mismo correo. Así lo haríamos:

CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);

Ahora, si intentas hacer algo como esto:

INSERT INTO users (name, email, age) VALUES ('John', 'john@example.com', 30);
INSERT INTO users (name, email, age) VALUES ('Jane', 'john@example.com', 25);

PostgreSQL lanzará un error, porque email tiene que ser único.

Creando índices con el parámetro CONCURRENTLY

Imagina que trabajas con una tabla enorme en producción, donde siempre se están haciendo operaciones (por ejemplo, insertando nuevos datos). Crear un índice en modo estándar (CREATE INDEX) bloquea esa tabla, y no deja que otros queries inserten, actualicen o borren datos. Esto puede ser un desastre para un sistema en marcha. Para evitarlo, puedes crear el índice "en paralelo" usando el parámetro CONCURRENTLY.

Sintaxis

CREATE INDEX CONCURRENTLY index_name
ON table_name (column_name);

La palabra clave CONCURRENTLY le dice a PostgreSQL que cree el índice en paralelo, sin bloquear la tabla.

Supón que tienes una tabla orders con millones de registros y que siempre está recibiendo nuevos pedidos:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL
);

Quieres crear un índice para acelerar la búsqueda por order_date, pero sin bloquear la tabla:

CREATE INDEX CONCURRENTLY idx_orders_order_date
ON orders (order_date);

Ahora la base de datos creará el índice sin bloquear la tabla, y tus usuarios ni se enterarán de que está pasando.

Entre las particularidades de CONCURRENTLY están:

1. El índice se crea más lento que en modo normal, porque PostgreSQL hace la operación en varias fases.
2. Si el índice se crea con errores (por ejemplo, por datos duplicados), tendrás que borrarlo a mano y crearlo de nuevo.

Parámetros extra de indexación

PostgreSQL te deja añadir parámetros extra al crear índices. Por ejemplo, puedes indexar varias columnas a la vez. Esto es útil si sueles hacer consultas filtrando por varios campos.

CREATE INDEX idx_users_name_email
ON users (name, email);

Ahora las consultas con condiciones WHERE name = 'John' AND email = 'john@example.com' irán mucho más rápido.

¡Ojo! Dos índices por una columna no es lo mismo que un índice por dos columnas. El índice multicolumna acelera justo las búsquedas donde en el WHERE aparecen todas esas columnas.

Ejemplos de errores y cómo resolverlos

Al crear índices puedes encontrarte con varios errores. Aquí van los más típicos:

Error de inserción de duplicados al crear un índice único. Si ya tienes filas duplicadas en la tabla, PostgreSQL no podrá crear el índice único. En ese caso, primero tienes que borrar o arreglar los duplicados.

DELETE FROM users
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(email) > 1
);

Error de bloqueo al crear índices. Si usas la creación normal de índices en una base en producción, los clientes pueden notar retrasos o fallos. Usa el parámetro CONCURRENTLY para evitar este problema.

Ahora imagina que curras en una empresa y te encargan optimizar una base de datos con millones de registros. Puedes usar índices para encontrar cuellos de botella y mejorar la experiencia de usuario. Por ejemplo, añadiendo el índice correcto, puedes bajar el tiempo de una consulta de 10 segundos a unos pocos milisegundos. ¿A que mola?