Middle: питання на співбесіді з теми «Типи даних»

bigint з послідовністю:

• компактний (8 байтів), швидкі індекси й JOIN;
• значення зростають - нові рядки дописуються в кінець індексу;
• але розкриває інформацію: /orders/1042 каже, скільки замовлень у вас було, і дозволяє перебирати чужі ID;
• генерується лише базою - не можна створити ID до вставки чи в іншій системі.

UUID (16 байтів):

• можна генерувати будь-де: у застосунку, на клієнті, в офлайн-режимі, в кількох базах без конфліктів;
• не розкриває кількість записів і не підбирається;
• зручно для злиття даних з різних джерел і публічних посилань.

Проблема UUIDv4 - випадковість. Нові значення потрапляють у випадкові місця B-tree індексу. На великій таблиці це означає постійні вставки в різні сторінки, розщеплення сторінок, роздутий індекс, гірше використання кешу - вставка помітно повільніша, ніж з послідовним ключем.

UUIDv7 (RFC 9562) починається з мітки часу в мілісекундах, а далі - випадкова частина. Значення приблизно впорядковані за часом, тож вставляються в кінець індексу, як bigint, зберігаючи переваги UUID.

-- PostgreSQL 18+
id uuid PRIMARY KEY DEFAULT uuidv7()

У старіших версіях PostgreSQL UUIDv7 генерують у застосунку: у Laravel - Str::uuid7(), а трейт моделі HasUuids у свіжих версіях фреймворку генерує саме UUIDv7.

Нюанс UUIDv7: мітка часу всередині розкриває момент створення запису. Для більшості даних це неважливо, але якщо час створення - чутлива інформація, це варто врахувати.

Поширений компроміс: внутрішній bigint-ключ для зв'язків і швидкості плюс окрема унікальна колонка public_id (UUID чи коротший ідентифікатор) для URL і API.

Докладніше в документації: Тип UUID

PostgreSQL дозволяє створити власний перелічуваний тип:

CREATE TYPE order_status AS ENUM ('new', 'paid', 'shipped', 'cancelled');

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'new'
);

Плюси: компактне зберігання (4 байти), перевірка значень базою, сортування в порядку оголошення.

Мінуси, через які його часто уникають:

• Додати значення можна (ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'paid'), а от видалити - ні. Доведеться створити новий тип, перенести колонку й видалити старий - на великій таблиці це переписування даних.
• ADD VALUE до PostgreSQL 12 не можна було виконати в транзакції - міграції ламалися.
• Тип - окремий об'єкт схеми: його треба створювати до таблиці, переносити між базами, враховувати в міграціях. ORM підтримують його гірше, ніж звичайні колонки.

Альтернатива 1 - text з CHECK:

status text NOT NULL DEFAULT 'new'
    CHECK (status IN ('new', 'paid', 'shipped', 'cancelled'))

Змінити перелік - замінити обмеження (NOT VALID + VALIDATE для великих таблиць), без зміни типу колонки. У Laravel так працює $table->enum() на PostgreSQL.

Альтернатива 2 - таблиця-довідник із зовнішнім ключем: коли значень багато, вони змінюються адміністраторами, або в них є атрибути (назва для показу, колір, порядок, чи активний).

Як обирати:

• Перелік стабільний і короткий, змінюється лише з кодом - CHECK або нативний enum.
• Перелік змінюється без деплою чи має атрибути - довідник.
• Логіка значень живе в застосунку - PHP enum для коду плюс CHECK у базі для цілісності.

Докладніше в документації: Перелічувані типи

Діапазонний тип зберігає проміжок значень як одне значення: int4range, numrange, daterange, tsrange, tstzrange. Межі можуть бути включними [ чи виключними ).

SELECT tstzrange('2026-10-04 10:00', '2026-10-04 12:00', '[)');

-- оператори
period @> '2026-10-04 11:00'::timestamptz     -- містить момент
period && other_period                          -- перетинаються
upper(period) - lower(period)                   -- тривалість

Найцінніше застосування - exclusion constraint. Задача: кімнату не можна забронювати двічі на час, що перетинається. Перевірка в застосунку («SELECT, чи вільно, потім INSERT») має гонку: два запити одночасно побачать «вільно».

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL,
    period tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

Обмеження означає: не може бути двох рядків, у яких room_id рівні і періоди перетинаються. База перевіряє це атомарно, навіть під паралельним навантаженням - друга вставка отримає помилку 23P01 (exclusion_violation).

btree_gist потрібен, щоб звичайну рівність (room_id WITH =) можна було поєднати з GiST-індексом.

Переваги діапазонів над двома колонками starts_at/ends_at:

• Перетин і вкладеність - один оператор замість чотирьох порівнянь з урахуванням меж.
• GiST-індекс прискорює пошук «що перетинається з цим проміжком».
• Exclusion constraints неможливі з двома окремими колонками без діапазону.

Ще застосування: періоди дії цін і тарифів, графіки змін, версії записів у часі («який тариф діяв 15 березня»).

Мультидіапазони (PostgreSQL 14+) - кілька непересічних діапазонів одним значенням: робочі години з перервою на обід.

Докладніше в документації: Діапазонні типи

Згенерована колонка обчислюється базою з інших колонок того ж рядка - як вираз у формулі таблиці. Записати її значення напряму не можна.

CREATE TABLE order_items (
    price numeric(10, 2) NOT NULL,
    quantity int NOT NULL,
    total numeric(12, 2) GENERATED ALWAYS AS (price * quantity) STORED
);

ALTER TABLE users
    ADD COLUMN email_normalized text GENERATED ALWAYS AS (lower(trim(email))) VIRTUAL;

Два види:

• STORED - обчислюється при вставці чи оновленні й зберігається на диску, як звичайна колонка. Читання безкоштовне; можна індексувати.
• VIRTUAL (PostgreSQL 18+) - не займає місця, обчислюється при читанні, як подання. У PostgreSQL 18 це вид за замовчуванням.

Навіщо:

• Похідні значення завжди узгоджені: сума позиції, нормалізований email, повне ім'я - неможливо забути оновити в одному з місць коду.
• Індекс для пошуку: tsvector для повнотекстового пошуку, нормалізоване значення для унікальності без урахування регістру - збережену колонку можна проіндексувати звичайним індексом.
• Витягти поле з JSON у звичайну колонку для фільтрів і індексів.

Обмеження:

• Вираз має бути детермінованим (immutable): без now(), випадкових чисел, запитів до інших таблиць.
• Не можна посилатися на інші згенеровані колонки чи колонки інших таблиць.
• Зміна виразу збереженої колонки - переписування таблиці.

Альтернативи: індекс за виразом (якщо значення потрібне лише для пошуку, а не для читання), тригер (для складнішої логіки з іншими таблицями), обчислення в застосунку (якщо значення не потрібне в базі).

Докладніше в документації: Згенеровані колонки

Домен - іменований тип на основі наявного, з обмеженнями. Правило описується один раз і використовується в багатьох колонках.

CREATE DOMAIN email AS text
    CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$');

CREATE DOMAIN positive_money AS numeric(12, 2)
    CHECK (VALUE >= 0);

CREATE DOMAIN country_code AS char(2)
    CHECK (VALUE ~ '^[A-Z]{2}$');

CREATE TABLE customers (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    contact_email email NOT NULL,
    billing_email email,
    credit_limit positive_money NOT NULL DEFAULT 0,
    country country_code NOT NULL
);

Переваги:

• Одне правило - скрізь однаково. Не буде таблиці, де email перевіряється інакше чи не перевіряється взагалі.
• Зміна в одному місці: ALTER DOMAIN email ADD CONSTRAINT ... застосується до всіх колонок.
• Самодокументування: тип колонки positive_money пояснює більше, ніж numeric(12, 2).

Обмеження й нюанси:

• NOT NULL у домені працює неочевидно (наприклад, при LEFT JOIN і в результатах функцій) - документація радить ставити NOT NULL на колонку, а не в домен.
• Зміна обмеження домену перевіряє всі колонки цього типу в усій базі - на великих таблицях довго. Як і з CHECK, є NOT VALID + VALIDATE CONSTRAINT.
• Підтримка ORM: Laravel-міграції не мають методу для доменів - колонку додають сирим SQL, а для застосунку це звичайний text чи numeric.
• Перевірка email регулярним виразом у базі - лише базовий захист від сміття. Справжню валідацію (і повідомлення користувачу) робить застосунок; база гарантує, що навіть код в обхід валідації не запише явно некоректне значення.

Домени корисні в базах, куди пишуть кілька застосунків чи сервісів: правило живе там, де дані, а не в кожному з них окремо.

Докладніше в документації: CREATE DOMAIN

За стандартом SQL NULL означає «невідоме», а невідоме не дорівнює іншому невідомому. Тому унікальне обмеження дозволяє скільки завгодно NULL:

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    phone text UNIQUE
);

INSERT INTO users (phone) VALUES (NULL), (NULL);   -- обидва вставлено

Для необов'язкових унікальних полів (телефон, нікнейм) це якраз потрібна поведінка: багато користувачів без телефону, але два однакові телефони - заборонено.

Де це підводить - складені ключі з необов'язковою частиною:

CREATE TABLE settings (
    user_id bigint NOT NULL,
    team_id bigint,          -- NULL = налаштування на рівні користувача
    key text NOT NULL,
    value text,
    UNIQUE (user_id, team_id, key)
);

INSERT INTO settings VALUES (1, NULL, 'theme', 'dark'), (1, NULL, 'theme', 'light');
-- обидва вставлено: дублікат, бо NULL ≠ NULL

NULLS NOT DISTINCT (PostgreSQL 15+) - вважати NULL однаковими:

UNIQUE NULLS NOT DISTINCT (user_id, team_id, key)

Тепер друга вставка порушить унікальність.

До PostgreSQL 15 обходили так:

• два часткові унікальні індекси - WHERE team_id IS NULL і WHERE team_id IS NOT NULL;
• індекс за виразом COALESCE(team_id, 0) - працює, але «магічне» значення легко пропустити.

Пов'язане - ON CONFLICT: upsert спирається на унікальне обмеження. Без NULLS NOT DISTINCT рядки з NULL у ключі ніколи не конфліктують, і upsert замість оновлення вставлятиме дублікати.

MySQL поводиться так само, як PostgreSQL за замовчуванням (кілька NULL дозволено), а налаштування на зразок NULLS NOT DISTINCT не має.

Докладніше в документації: CREATE TABLE