Пошук уроків, статей та іншого контенту
Дізнаєтеся, як унікальні індекси забезпечують цілісність даних і впливають на виконання запитів.
Унікальний індекс — це індекс, який одночасно:
прискорює пошук за індексованими колонками;
не дозволяє зберегти два рядки з однаковими значеннями індексованих колонок.
Наприклад, адреса електронної пошти користувача зазвичай має бути унікальною:
CREATE UNIQUE INDEX users_email_idx
ON users (email);Після створення такого індексу PostgreSQL перевірятиме кожну операцію INSERT і
Унікальність перевіряється не лише під час виконання запитів, а й під час запису даних. Це важливо: цілісність забезпечується самою базою даних, а не тільки перевірками в коді застосунку.
Розглянемо приклад із таблицею користувачів:
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id integer GENERATED ALWAYS AS IDENTITY,
email text NOT NULL,
name text NOT NULL
);
CREATE UNIQUE INDEX users_email_idx
ON users (email);
INSERT INTO users (email, name)
VALUES ('olena@example.com', 'Олена');
-- Цей запис буде додано
INSERT INTO users (email, name)
VALUES ('taras@example.com', 'Тарас');
-- Цей запит завершиться помилкою через дубль email
INSERT INTO users (email, name)
VALUES ('olena@example.com', 'Інша Олена');Останній INSERT спричинить помилку на кшталт:
ERROR: duplicate key value violates unique constraint "users_email_idx"Назва індексу потрапляє в повідомлення про помилку, тому варто давати індексам зрозумілі назви.
У PostgreSQL унікальність можна оголосити двома основними способами.
CREATE TABLE accounts (
id integer GENERATED ALWAYS AS IDENTITY,
email text NOT NULL UNIQUE
);Або окремо:
ALTER TABLE accounts
ADD CONSTRAINT accounts_email_key UNIQUE (email);CREATE UNIQUE INDEX accounts_email_idx
ON accounts (email);Унікальне обмеження описує правило предметної області: значення колонки мають бути унікальними. PostgreSQL реалізує таке обмеження за допомогою унікального індексу.
Унікальний індекс створюється безпосередньо як індекс. Це корисно, коли потрібні можливості індексів, яких немає у звичайному унікальному обмеженні, наприклад:
часткова унікальність через WHERE;
унікальність обчисленого виразу;
специфічні параметри індексу.
Якщо потрібно лише зафіксувати бізнес-правило «значення має бути унікальним», зазвичай краще використовувати UNIQUE-обмеження. Якщо правило безпосередньо пов’язане з індексом або має умову, можна створити унікальний індекс.
Унікальний індекс може містити кілька колонок:
DROP TABLE IF EXISTS memberships;
CREATE TABLE memberships (
organization_id integer NOT NULL,
user_id integer NOT NULL,
role text NOT NULL
);
CREATE UNIQUE INDEX memberships_organization_user_idx
ON memberships (organization_id, user_id);
INSERT INTO memberships (organization_id, user_id, role)
VALUES (10, 5, 'member');
-- Дозволено: інша організація
INSERT INTO memberships (organization_id, user_id, role)
VALUES (20, 5, 'admin');
-- Дозволено: інший користувач
INSERT INTO memberships (organization_id, user_id, role)
VALUES (10, 8, 'member');
-- Помилка: така пара вже існує
INSERT INTO memberships (organization_id, user_id, role)
VALUES (10, 5, 'admin');Тут унікальною має бути не кожна колонка окремо, а комбінація:
(organization_id, user_id)Це типовий випадок для зв’язків між сутностями: один користувач може належати до багатьох організацій, але лише один раз до кожної конкретної організації.
Для індексу:
CREATE UNIQUE INDEX memberships_organization_user_idx
ON memberships (organization_id, user_id);важливий порядок колонок.
Такий індекс добре підходить для запитів, які фільтрують за organization_id:
SELECT *
FROM memberships
WHERE organization_id = 10;Також він підходить для запитів, які фільтрують за обома колонками:
SELECT *
FROM memberships
WHERE organization_id = 10
AND user_id = 5;Але запит лише за user_id не використовує перевагу першої колонки індексу так само ефективно:
SELECT *
FROM memberships
WHERE user_id = 5;Унікальність при цьому все одно перевіряється для повної комбінації колонок.
NULLЗа замовчуванням PostgreSQL вважає NULL відмінним від іншого NULL під час перевірки унікального індексу. Тому в колонці без NOT NULL може бути кілька рядків із NULL.
DROP TABLE IF EXISTS invitations;
CREATE TABLE invitations (
id integer GENERATED ALWAYS AS IDENTITY,
email text
);
CREATE UNIQUE INDEX invitations_email_idx
ON invitations (email);
-- Дозволено
INSERT INTO invitations (email)
VALUES (NULL);
-- Також дозволено за замовчуванням
INSERT INTO invitations (email)
VALUES (NULL);
-- Будь-які два однакові не-NULL значення заборонені
INSERT INTO invitations (email)
VALUES ('guest@example.com');
-- Помилка
INSERT INTO invitations (email)
VALUES ('guest@example.com');Якщо NULL не повинен повторюватися, є два поширені варіанти:
додати NOT NULL, якщо значення обов’язкове;
у PostgreSQL 15 і новіших версіях використати NULLS NOT DISTINCT.
CREATE UNIQUE INDEX invitations_email_strict_idx
ON invitations (email) NULLS NOT DISTINCT;У цьому випадку два NULL вважаються однаковим значенням і другий такий рядок буде відхилено.
Іноді унікальність потрібна лише для рядків, які відповідають певній умові.
Наприклад, у системі може бути лише один активний сеанс для конкретного користувача:
DROP TABLE IF EXISTS sessions;
CREATE TABLE sessions (
id integer GENERATED ALWAYS AS IDENTITY,
user_id integer NOT NULL,
token text NOT NULL,
revoked_at timestamptz
);
CREATE UNIQUE INDEX sessions_one_active_per_user_idx
ON sessions (user_id)
WHERE revoked_at IS NULL;Тепер база даних дозволяє кілька відкликаних сеансів, але лише один активний:
INSERT INTO sessions (user_id, token)
VALUES (42, 'token-1');
-- Помилка: для user_id = 42 вже є активний сеанс
INSERT INTO sessions (user_id, token)
VALUES (42, 'token-2');
-- Відкликаємо перший сеанс
UPDATE sessions
SET revoked_at = CURRENT_TIMESTAMP
WHERE token = 'token-1';
-- Тепер новий активний сеанс дозволений
INSERT INTO sessions (user_id, token)
VALUES (42, 'token-2');Умова індексу має бути узгоджена із запитами. Наприклад, запит пошуку активного сеансу містить ту саму умову:
SELECT *
FROM sessions
WHERE user_id = 42
AND revoked_at IS NULL;Частковий унікальний індекс не робить унікальними всі рядки таблиці. Він контролює лише рядки, які потрапляють до індексу.
Унікальний індекс може будуватися не безпосередньо за колонкою, а за виразом.
Наприклад, потрібно заборонити реєстрацію однакових адрес електронної пошти незалежно від регістру:
DROP TABLE IF EXISTS subscribers;
CREATE TABLE subscribers (
id integer GENERATED ALWAYS AS IDENTITY,
email text NOT NULL
);
CREATE UNIQUE INDEX subscribers_lower_email_idx
ON subscribers (lower(email));
INSERT INTO subscribers (email)
VALUES ('User@example.com');
-- Помилка: lower(email) має таке саме значення
INSERT INTO subscribers (email)
VALUES ('user@example.com');У цьому випадку PostgreSQL порівнює значення lower(email), а не початковий текст.
Під час пошуку потрібно використовувати такий самий вираз:
SELECT *
FROM subscribers
WHERE lower(email) = lower('USER@example.com');Звичайний запит із порівнянням лише email може не використовувати цей індекс так, як очікується:
SELECT *
FROM subscribers
WHERE email = 'USER@example.com';Якщо логіка нормалізації значення важлива для всієї системи, інколи зручніше зберігати вже нормалізоване значення в окремій колонці та зробити її унікальною.
Унікальний індекс може прискорити:
пошук рядка за унікальним значенням;
перевірку існування рядка;
з’єднання таблиць за індексованими колонками;
сортування за колонками індексу в ситуаціях, де планувальник вважає це вигідним.
Приклад перевірки плану:
DROP TABLE IF EXISTS products;
CREATE TABLE products (
id integer GENERATED ALWAYS AS IDENTITY,
sku text NOT NULL,
name text NOT NULL
);
CREATE UNIQUE INDEX products_sku_idx
ON products (sku);
INSERT INTO products (sku, name)
SELECT
'SKU-' || number,
'Product ' || number
FROM generate_series(1, 10000) AS numbers(number);
ANALYZE products;
EXPLAIN (ANALYZE, COSTS OFF)
SELECT *
FROM products
WHERE sku = 'SKU-5000';Планувальник може використати products_sku_idx для пошуку за sku. Однак конкретний план не гарантований: PostgreSQL враховує розмір таблиці, статистику, вибірковість умови та вартість різних способів доступу.
Для дуже маленької таблиці планувальник може вибрати послідовне сканування, навіть якщо індекс існує. Це нормальна поведінка: прочитати всю маленьку таблицю іноді дешевше, ніж звертатися до індексу.
Унікальність також дає планувальнику додаткову інформацію. Він знає, що пошук за повним ключем унікального індексу може повернути не більше одного рядка.
Індекс прискорює читання, але має ціну:
займає місце на диску;
уповільнює INSERT;
може уповільнювати UPDATE, якщо змінюються індексовані колонки;
потребує обслуговування під час змін таблиці.
Під час вставки PostgreSQL має не лише додати ключ до індексу, а й перевірити, що такого ключа ще немає. Тому унікальні індекси потрібно створювати для реальних вимог до даних, а не для кожної колонки без аналізу запитів.
Окремо створювати звичайний індекс на тих самих колонках зазвичай не потрібно:
CREATE UNIQUE INDEX users_email_idx
ON users (email);
-- Такий індекс дублюватиме роботу першого
CREATE INDEX users_email_regular_idx
ON users (email);Унікальний індекс уже виконує функції індексу для пошуку за email.
Звичайне створення індексу може блокувати операції, які змінюють таблицю, на час побудови індексу. Для великих таблиць PostgreSQL підтримує створення індексу одночасно з роботою користувачів:
CREATE UNIQUE INDEX CONCURRENTLY users_email_idx
ON users (email);CONCURRENTLY зменшує блокування операцій запису, але має обмеження:
команду не можна виконувати всередині транзакції;
побудова зазвичай триває довше;
під час побудови індекс усе одно може завершитися помилкою, якщо в таблиці вже є дублікати.
Перед створенням унікального індексу потрібно перевірити та виправити наявні дублікати.
NULL буде унікальнимCREATE UNIQUE INDEX example_idx
ON example_table (optional_code);Такий індекс за замовчуванням дозволяє кілька NULL. Якщо значення обов’язкове, додайте NOT NULL. Якщо потрібно вважати всі NULL однаковими, використайте NULLS NOT DISTINCT у PostgreSQL 15+.
Перевірка на кшталт «спочатку знайти email, а потім виконати INSERT» не захищає від конкурентних запитів. Два процеси можуть одночасно не знайти запис і обидва спробувати його додати.
Унікальний індекс або унікальне обмеження має бути остаточним захистом на рівні бази даних.
Індекс:
CREATE UNIQUE INDEX example_idx
ON example_table (tenant_id, username);робить унікальною пару (tenant_id, username), а не username у всій таблиці.
Якщо ім’я користувача має бути унікальним глобально, індекс потрібно створити лише за username.
Унікальний індекс уже можна використовувати для пошуку. Створення додаткового звичайного індексу з тим самим ключем збільшує розмір бази та вартість запису без користі.
Порушення унікальності — це помилка операції. Застосунок має обробляти її відповідно до сценарію: повідомити користувача, повторити операцію з іншими даними або використати правильну логіку оновлення.
Унікальний індекс одночасно прискорює пошук і забороняє дублікати.
Унікальність складеного індексу поширюється на комбінацію колонок.
За замовчуванням кілька NULL не порушують унікальність.
Частковий унікальний індекс контролює лише рядки, що відповідають його умові.
Унікальний індекс можна створити за виразом, наприклад lower(email).
Для простого бізнес-правила зазвичай використовують UNIQUE-обмеження.
PostgreSQL може використовувати унікальний індекс для швидкого пошуку, але остаточний план залежить від статистики та розміру таблиці.
Унікальність має забезпечувати база даних, а не лише код застосунку.