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

Дані таблиці лежать у сторінках по 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() до й після.

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

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