Создание индексов (`CREATE INDEX`) и параметры индексации (`UNIQUE`, `CONCURRENTLY`)

Мы уже не раз говорили о том, как индексы ускоряют выборки и помогают базе не перебирать всё подряд. Теперь пришло время разобраться, как именно они создаются, какие бывают параметры у команды CREATE INDEX и в каких случаях стоит использовать опции вроде UNIQUE или CONCURRENTLY. Всё это важно, если вы хотите не просто пользоваться индексами, а управлять ими грамотно.

Синтаксис CREATE INDEX

Создать индекс можно с помощью команды CREATE INDEX. Вот её базовый синтаксис:

CREATE INDEX index_name
ON table_name (column_name);

• index_name — Имя индекса. Желательно, чтобы оно отражало назначение индекса, например, idx_users_email для индекса на столбец email в таблице users.
• table_name — Имя таблицы, для которой создается индекс.
• column_name — Столбец, который будет индексирован.

Приведём простой пример. Допустим, у нас есть таблица users:

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

Мы хотим ускорить поиск пользователей по полю email. Создаем индекс:

CREATE INDEX idx_users_email
ON users (email);

Теперь, когда вы будете выполнять запросы вроде:

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

PostgreSQL будет использовать индекс idx_users_email, чтобы быстро найти нужную строку.

Уникальные индексы (UNIQUE)

Уникальный индекс — это гарантия, что значения в указанном столбце или столбцах будут уникальными. Если вы попробуете вставить дублирующее значение, PostgreSQL не позволит вам это сделать.

Уникальные индексы часто используются для ключей, таких как email, username или другие идентификаторы, которые не должны дублироваться.

Синтаксис создания уникального индекса

Синтаксис для создания уникального индекса очень похож на обычный индекс, за исключением ключевого слова UNIQUE:

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

Допустим, в нашей таблице users поле email должно быть уникальным, чтобы не допустить двух пользователей с одинаковым адресом. Вот как мы это сделаем:

CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);

Теперь, если вы попробуете выполнить, например:

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 выбросит ошибку, так как email должен быть уникальным.

Создание индексов с параметром CONCURRENTLY

Представьте, что вы работаете с огромной таблицей в продакшене, на которой постоянно выполняются операции (например, вставки новых данных). Создание индекса в стандартном режиме (CREATE INDEX) блокирует эту таблицу, не позволяя другим запросам вставлять, обновлять или удалять данные. Это может быть катастрофой для работающей системы. Чтобы этого избежать, можно создать индекс "асинхронно" с помощью параметра CONCURRENTLY.

Синтаксис

CREATE INDEX CONCURRENTLY index_name
ON table_name (column_name);

Ключевое слово CONCURRENTLY указывает PostgreSQL, что индекс должен создаваться параллельно, без блокировки таблицы.

Предположим, у нас есть таблица orders, которая содержит миллионы записей и постоянно пополняется новыми заказами:

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

Вы хотите создать индекс для ускорения поиска по order_date, но без блокировки таблицы:

CREATE INDEX CONCURRENTLY idx_orders_order_date
ON orders (order_date);

Теперь база данных создаст индекс без блокировки таблицы, и ваши пользователи даже не заметят этого процесса.

Среди особенностейCONCURRENTLY можно отметить такое:

1. Индекс создается медленнее, чем в обычном режиме, так как PostgreSQL выполняет эту операцию в несколько этапов.
2. Если индекс создается с ошибками (например, из-за дублирующихся данных), его придется удалить вручную и создать заново.

Дополнительные параметры индексации

PostgreSQL позволяет добавлять дополнительные параметры при создании индексов. Например, можно организровать одновременную индексацию нескольких столбцов. Это полезно, когда вы часто выполняете запросы с фильтрацией по нескольким полям.

CREATE INDEX idx_users_name_email
ON users (name, email);

Теперь запросы с условиями WHERE name = 'John' AND email = 'john@example.com' будут работать быстрее.

Два индекса по одному столбцу это не тоже самое, что индекс по двум столбцам! Индекс по нескольким столбцам ускоряет именно поиск, где в WHERE фигурируют все эти столбцы.

Примеры ошибок и их решение

При создании индексов можно столкнуться с рядом ошибок. Вот наиболее распространенные из них:

Ошибка вставки дубликатов при создании уникального индекса. Если в таблице уже есть дублирующиеся строки, PostgreSQL не сможет создать уникальный индекс. В таком случае нужно сначала удалить или исправить дубликаты.

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

Ошибка блокировки при создании индексов. Если вы используете обычное создание индекса в рабочей базе, клиенты могут заметить задержки или сбои. Используйте параметр CONCURRENTLY, чтобы избежать этой проблемы.

Теперь представьте, вы работаете в компании и вам доверили оптимизацию базы данных с миллионами записей. Вы можете использовать индексы, чтобы найти узкие места и ускорить пользовательский опыт. Например, добавив правильный индекс, вы сократите время выполнения запроса с 10 секунд до нескольких миллисекунд. Разве это не круто?