Питання на співбесіді: Типи даних

У PostgreSQL text, varchar і varchar(n) зберігаються однаково й працюють з однаковою швидкістю. Різниця лише в тому, що varchar(n) додає перевірку довжини.

• text - рядок довільної довжини.
• varchar(n) - рядок не довший за n символів (не байтів). Довший - помилка.
• varchar без довжини - те саме, що text.
• char(n) - доповнюється пробілами до n. Майже ніколи не потрібен: займає не менше місця і має дивну семантику порівнянь.

Звідки звичка до varchar(255): у MySQL та інших СУБД довжина впливає на зберігання, індекси й тимчасові таблиці. У PostgreSQL такої причини немає.

Як обирати:

• text - для більшості рядків. Обмеження довжини - якщо воно справді є правилом домену.
• Бізнес-обмеження - через CHECK, його легше змінити:

title text NOT NULL CHECK (char_length(title) <= 200)

Збільшити ліміт varchar(n) у сучасних версіях можна без переписування таблиці, але зменшення вимагає повної перевірки даних.

Що варто знати:

• Довгі значення (понад ~2 КБ) автоматично стискаються й виносяться в TOAST - таблиця не роздувається.
• Laravel $table->string('name') створює varchar(255) - на PostgreSQL це лише обмеження, а не оптимізація. Для довгих текстів - $table->text().
• Валідація довжини в застосунку все одно потрібна, щоб користувач отримав зрозуміле повідомлення, а не помилку бази.

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

Обидва способи створюють автоінкрементний цілочисельний ключ на основі послідовності (sequence).

serial / bigserial - старий спосіб, по суті скорочення:

id bigserial PRIMARY KEY
-- розгортається в:
-- id bigint NOT NULL DEFAULT nextval('orders_id_seq')
-- + окрема послідовність, «прив'язана» до колонки

Identity (PostgreSQL 10+, стандарт SQL):

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- або
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

Чому identity краще:

• Стандарт SQL - переноситься між СУБД.
• Послідовність - частина колонки: права, видалення таблиці, копіювання структури працюють коректно. З serial треба окремо давати права на послідовність, і CREATE TABLE ... (LIKE ...) може поділити послідовність між таблицями.
• GENERATED ALWAYS забороняє вставляти власні значення (можна лише явно з OVERRIDING SYSTEM VALUE). Захищає від ручних ID, що потім конфліктують з послідовністю.
• Керування через ALTER TABLE ... ALTER COLUMN id RESTART WITH 1000.

Спільні нюанси:

• Дірки в номерах - норма. Значення з послідовності береться до коміту й не повертається при відкаті. ID 5, 6, 8 - не баг. Для «безперервних» номерів рахунків послідовність не підходить.
• bigint, а не int: 2,1 мільярда закінчуються несподівано швидко в таблицях подій і логів, а зміна типу первинного ключа на великій таблиці - болюча операція.
• Після ручного імпорту даних з явними ID послідовність треба зсунути: SELECT setval('orders_id_seq', (SELECT max(id) FROM orders));.

Laravel $table->id() на PostgreSQL створює bigserial-ключ - для більшості застосунків різниця непомітна.

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

PostgreSQL дозволяє зберігати в колонці масив значень будь-якого типу: text[], int[], uuid[].

CREATE TABLE posts (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    tags text[] NOT NULL DEFAULT '{}'
);

INSERT INTO posts (title, tags) VALUES ('Черги в Laravel', ARRAY['laravel', 'queues']);

SELECT * FROM posts WHERE 'queues' = ANY(tags);       -- містить елемент
SELECT * FROM posts WHERE tags @> ARRAY['laravel'];   -- містить усі
SELECT * FROM posts WHERE tags && ARRAY['vue', 'react'];  -- є хоча б один спільний
SELECT unnest(tags) AS tag, count(*) FROM posts GROUP BY tag;   -- розгорнути в рядки

Для швидкого пошуку - GIN-індекс: CREATE INDEX posts_tags_idx ON posts USING gin (tags); (працює з @>, &&, але не з = ANY).

Індексація з одиниці: tags[1] - перший елемент.

Коли масив доречний:

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

Коли краще окрема таблиця:

• Елементи посилаються на інші сутності - масив ID не має зовнішніх ключів, і видалення пов'язаного запису не прибере його з масиву.
• Потрібні атрибути зв'язку (хто й коли додав тег).
• Потрібно часто змінювати окремі елементи чи масив великий.
• Потрібні звичайні JOIN і агрегати - на масивах вони можливі, але громіздкіші.

Пастки: NULL і порожній масив '{}' - різні речі; = ANY(NULL) дає NULL; багатовимірні масиви мусять бути «прямокутними». Laravel не має типу масиву в схемі - колонку додають через $table->addColumn(...) чи сирий SQL, а читають з кастом.

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

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

Дані таблиці лежать у сторінках по 8 КБ, і рядок має вміщатися в одну сторінку. TOAST (The Oversized-Attribute Storage Technique) - механізм для значень, що не вміщаються: довгих текстів, великих jsonb, масивів, bytea.

Як це працює: коли рядок перевищує поріг (~2 КБ), PostgreSQL:

1. спершу стискає великі значення (алгоритм pglz або lz4 з PostgreSQL 14);
2. якщо рядок досі завеликий - виносить значення в окрему TOAST-таблицю, розрізавши на шматки, а в основному рядку лишає вказівник.

Усе це прозоро: запити працюють як зазвичай.

Наслідки для продуктивності:

• SELECT * дорожчий, ніж здається: для кожного рядка, де є «тостоване» значення, база читає й розпаковує його з окремої таблиці. Запит списку статей, який вибирає й body, читає всі тексти. Вибирайте лише потрібні колонки.
• Таблиця лишається компактною: основні сторінки містять короткі рядки, тож сканування за іншими колонками швидке, навіть якщо в таблиці гігабайти тексту.
• Оновлення інших колонок не переписує великі значення: якщо body не змінювався, нова версія рядка посилається на ті самі TOAST-дані.
• Великий jsonb, з якого читають одне поле, все одно розпаковується цілком. Тому часті запити до поля великого JSON краще перевести на згенеровану колонку чи окреме поле.

Налаштування:

ALTER TABLE documents ALTER COLUMN body SET COMPRESSION lz4;    -- швидше стискання й розпаковування
ALTER TABLE files ALTER COLUMN content SET STORAGE EXTERNAL;    -- не стискати (вже стиснені дані)

Ліміт: одне значення може бути до 1 ГБ. Але зберігати великі файли в базі зазвичай погана ідея - для них об'єктне сховище (S3), а в базі - шлях і метадані.

Як подивитися розмір: pg_total_relation_size() включає TOAST і індекси, pg_relation_size() - лише основну таблицю.

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

Складений тип (composite type) - структура з кількох іменованих полів, як рядок таблиці. Кожна таблиця автоматично має однойменний складений тип.

CREATE TYPE address AS (
    street text,
    city text,
    postal_code text
);

CREATE TABLE companies (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    hq address
);

INSERT INTO companies (name, hq) VALUES ('Acme', ROW('Хрещатик, 1', 'Київ', '01001'));
SELECT name, (hq).city FROM companies;     -- дужки обов'язкові

Де складені типи справді корисні - функції, що повертають кілька значень:

CREATE FUNCTION order_stats(p_user_id bigint)
RETURNS TABLE (orders_count bigint, total_spent numeric, last_order_at timestamptz)
LANGUAGE sql STABLE AS $$
    SELECT count(*), coalesce(sum(total), 0), max(created_at)
    FROM orders
    WHERE user_id = p_user_id;
$$;

SELECT * FROM order_stats(42);

Рядок таблиці як значення: SELECT u FROM users u повертає весь рядок одним значенням; row_to_json(u) перетворює його на JSON - зручно для API та аудиту.

Чому складені типи в колонках рідкісні:

• Звернення до полів незручне (дужки), ORM майже не підтримують такі колонки.
• Зміна типу (ALTER TYPE ... ADD ATTRIBUTE) зачіпає всі таблиці, що його використовують.
• Індексувати поле можна лише індексом за виразом.
• Для «вкладеної структури» сьогодні частіше беруть jsonb (гнучкий, добре підтримуваний) або окремі колонки чи таблицю (з обмеженнями й індексами).

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

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

PostgreSQL вирівнює значення колонок у рядку за межами їхнього типу (alignment): bigint, timestamptz, float8 - за 8 байтами, int - за 4, smallint - за 2, boolean і text - за 1. Між колонками вставляються «порожні» байти (padding), щоб наступне значення почалося з правильної адреси.

Приклад:

-- Невдалий порядок
CREATE TABLE events_bad (
    is_processed boolean,   -- 1 байт + 7 байтів вирівнювання
    created_at timestamptz, -- 8
    priority smallint,      -- 2 + 6 байтів вирівнювання
    user_id bigint,         -- 8
    attempts int            -- 4
);

-- Той самий набір колонок, від більших до менших
CREATE TABLE events_good (
    created_at timestamptz, -- 8
    user_id bigint,         -- 8
    attempts int,           -- 4
    priority smallint,      -- 2
    is_processed boolean    -- 1
);

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

Правило: колонки фіксованої довжини - від найбільшого вирівнювання до найменшого (8 → 4 → 2 → 1 байт), а змінної довжини (text, numeric, jsonb) - у кінці.

Що ще впливає на розмір рядка:

• Заголовок рядка - ~23 байти на кожен рядок незалежно від даних. Тому мільярд вузьких рядків - це вже десятки гігабайтів лише заголовків.
• NULL не займають місця для значення - лише біт у bitmap.
• Короткі рядки змінної довжини (до 126 байтів) мають 1-байтовий заголовок замість 4.

Як перевірити: pg_column_size(row(...)) і pg_relation_size() до й після.

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

Докладніше в документації: Розміщення сторінок бази