Створення індексів (`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 секунд до кількох мілісекунд. Хіба це не круто?