Питання на співбесіді з PostgreSQL

Кожна транзакція, що змінює дані, отримує ідентифікатор (XID) - 32-бітне число. Версія рядка позначена XID транзакції, що її створила, і PostgreSQL порівнює XID, щоб вирішити, які версії видимі якій транзакції.

Проблема: 32 біти - це близько 4 мільярдів значень, і лічильник «закручується» по колу. Порівняння працює в межах ~2 мільярдів транзакцій у минуле. Якби рядок, створений дуже давно, «пережив» цю межу, він раптом став би виглядати як створений у майбутньому - і зник би для всіх запитів.

Як PostgreSQL це запобігає - «заморожування». VACUUM позначає старі версії рядків як заморожені: «видимі всім, XID більше не має значення». Для цього autovacuum періодично запускає агресивний вакуум таблиці, навіть якщо мертвих рядків немає (autovacuum_freeze_max_age, за замовчуванням 200 мільйонів транзакцій).

Якщо заморожування не встигає:

1. Спершу - попередження в логах про наближення межі.
2. Близько до межі PostgreSQL перестає приймати транзакції, що змінюють дані, щоб не пошкодити дані. База доступна лише для читання, доки вакуум не виконано, - це повноцінна аварія.

Чому вакуум може не встигати:

• Довгі транзакції й забуті слоти реплікації блокують заморожування.
• Autovacuum вимкнений чи занадто повільний для навантаження.
• Дуже велика таблиця, агресивний вакуум якої триває годинами й постійно переривається.

Моніторинг:

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

Сповіщення варто ставити задовго до 2 мільярдів - наприклад, на 500 мільйонів-1 мільярд.

Хороша новина: нові версії PostgreSQL значно покращили заморожування (visibility map з позначкою «усе заморожено», агресивніший вакуум), тож на налаштованому сервері з працюючим autovacuum це рідкість. Проблема з'являється саме тоді, коли щось систематично заважає вакууму.

Докладніше в документації: Запобігання переповненню ID транзакцій

Залежить від того, які запити робляться до JSON.

1. GIN на весь документ - для пошуку за довільними ключами й значеннями:

CREATE INDEX products_attrs_gin ON products USING gin (attributes);

-- прискорює
WHERE attributes @> '{"color": "red"}'
WHERE attributes ? 'warranty'

2. GIN з jsonb_path_ops - менший і швидший, але підтримує лише @> (і jsonpath-оператори @?, @@):

CREATE INDEX products_attrs_path_gin ON products USING gin (attributes jsonb_path_ops);

Якщо запити лише «містить такі пари», - це кращий вибір: індекс у рази менший.

3. B-tree за виразом - коли фільтрують чи сортують за одним конкретним полем:

CREATE INDEX products_brand_idx ON products ((attributes ->> 'brand'));

-- прискорює
WHERE attributes ->> 'brand' = 'Bosch'
ORDER BY attributes ->> 'brand'

Зверніть увагу на подвійні дужки й на те, що вираз у запиті має точно збігатися з виразом в індексі. Для числових порівнянь - індекс за виразом з приведенням: ((attributes ->> 'price')::numeric).

Що не прискорює GIN: attributes ->> 'brand' = 'Bosch' - для нього потрібен B-tree за виразом (або переписати умову на @> '{"brand": "Bosch"}').

4. Згенерована колонка + звичайний індекс - коли поле використовується часто: воно стає звичайною колонкою з типом, статистикою й простими індексами, а JSON лишається джерелом.

Ціна GIN: повільніший запис і більший розмір. На таблицях з інтенсивними вставками це помітно; GIN збирає зміни у відкладений список (fastupdate), який вливається пізніше.

Перевірка: EXPLAIN має показувати Bitmap Index Scan по GIN-індексу, а не Seq Scan з фільтром.

Докладніше в документації: Індексування jsonb

SQL/JSON path (PostgreSQL 12+) - мова запитів до вмісту JSON за стандартом SQL, схожа на JSONPath: шляхи, фільтри, арифметика.

-- order.data = {"items": [{"sku": "A1", "qty": 2, "price": 100}, {"sku": "B2", "qty": 1, "price": 900}]}

SELECT jsonb_path_query_array(data, '$.items[*] ? (@.price > 500).sku') FROM orders;
-- ["B2"]

SELECT * FROM orders WHERE data @? '$.items[*] ? (@.qty > 1)';   -- є позиція з кількістю > 1
SELECT * FROM orders WHERE data @@ '$.items.size() > 3';         -- предикат

• $ - корінь документа, @ - поточний елемент у фільтрі ? (...).
• @? - чи повертає шлях хоч щось; @@ - результат предиката. Обидва прискорює GIN-індекс.
• Режими lax (за замовчуванням, прощає відсутні ключі) і strict (помилка на відсутніх).

JSON_TABLE (PostgreSQL 17+) перетворює JSON на звичайні рядки й колонки - без ручного jsonb_array_elements і приведень:

SELECT o.id, items.*
FROM orders o,
     JSON_TABLE(o.data, '$.items[*]'
         COLUMNS (
             sku   text    PATH '$.sku',
             qty   int     PATH '$.qty',
             price numeric PATH '$.price'
         )
     ) AS items
WHERE items.qty > 1;

Результат можна з'єднувати, групувати й фільтрувати, як будь-яку таблицю.

Де корисно: розбір відповідей зовнішніх API, збережених у JSON; імпорт вкладених документів у реляційні таблиці; звіти по даних, що зберігаються як JSON.

PostgreSQL 17 також додав функції стандарту JSON_EXISTS, JSON_QUERY, JSON_VALUE - їх зручно використовувати, якщо запити мають бути переносимими між СУБД.

Застереження: якщо такі запити - щоденна робота, а не виняток, дані, ймовірно, варто зберігати в таблицях, а не в JSON.

Докладніше в документації: Мова SQL/JSON path

PostgreSQL може зібрати вкладену JSON-структуру одним запитом - без N+1 і без складання масивів у застосунку.

SELECT jsonb_build_object(
    'id', u.id,
    'name', u.name,
    'orders', coalesce(
        (SELECT jsonb_agg(
                    jsonb_build_object('id', o.id, 'total', o.total, 'created_at', o.created_at)
                    ORDER BY o.created_at DESC
                )
         FROM orders o
         WHERE o.user_id = u.id),
        '[]'::jsonb
    )
) AS payload
FROM users u
WHERE u.id = 42;

Основні функції:

• jsonb_build_object(k1, v1, ...) - об'єкт з пар ключ-значення;
• jsonb_build_array(...) - масив;
• jsonb_agg(expr ORDER BY ...) - агрегат: рядки групи → JSON-масив;
• jsonb_object_agg(key, value) - агрегат: рядки → JSON-об'єкт;
• row_to_json(t) / to_jsonb(t) - увесь рядок у JSON.

Пастки:

• jsonb_agg по порожній групі повертає NULL, а не [] - звідси coalesce(..., '[]').
• jsonb не зберігає порядок ключів (впорядковує їх), а json - зберігає. Якщо порядок полів у відповіді важливий, - json_build_object / json_agg.
• Числа numeric потрапляють у JSON як числа з повною точністю; клієнт на JavaScript може їх округлити - гроші краще віддавати рядками чи в копійках.

Коли це доречно:

• Ендпоінти з глибоко вкладеними даними, де збирання в застосунку дає багато запитів.
• Експорти й звіти, де формат стабільний.
• Сервіси на кшталт PostgREST і Hasura будують усі відповіді саме так.

Коли - ні: у звичайному Laravel-застосунку форматування відповіді належить API Resources: там логіка видимості полів, перетворень і версій. SQL-збирання JSON - інструмент для гарячих місць, де виміряно, що звичайний підхід надто повільний.

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

LIKE '%черги%' шукає підрядок: не знаходить інших форм слова, не ранжує результати й не використовує звичайні індекси. Повнотекстовий пошук працює зі словами.

Два типи:

• tsvector - документ, розібраний на нормалізовані слова (лексеми) з позиціями;
• tsquery - запит з операторами & (і), | (або), ! (не), <-> (наступне слово).

SELECT to_tsvector('english', 'Running background jobs with queues');
-- 'background':2 'job':3 'queue':5 'run':1

SELECT to_tsvector('english', 'Running background jobs with queues')
    @@ to_tsquery('english', 'queue & job');   -- true

Конфігурація (english) визначає словники: стемінг (jobs → job, running → run), стоп-слова (with викинуто), нижній регістр.

Зручні функції для запиту від користувача:

websearch_to_tsquery('english', 'laravel queues -redis "failed jobs"')   -- синтаксис як у пошуковиках
plainto_tsquery('english', 'laravel queues')                            -- просто слова через «і»

Індекс - GIN за tsvector, найкраще збереженим у згенерованій колонці:

ALTER TABLE posts ADD COLUMN search tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body, '')), 'B')
    ) STORED;

CREATE INDEX posts_search_idx ON posts USING gin (search);

SELECT * FROM posts WHERE search @@ websearch_to_tsquery('english', 'queue retries');

Чого повнотекстовий пошук PostgreSQL не вміє з коробки: виправляти опечатки (для цього pg_trgm), шукати синоніми без налаштування словників, «пошук під час набору» за частиною слова (лише префікси: 'que:*'), складне ранжування з урахуванням популярності.

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

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

Ранжування - ts_rank (за частотою слів запиту) або ts_rank_cd (ще й за близькістю слів одне до одного):

SELECT id, title,
       ts_rank(search, query) AS rank
FROM posts, websearch_to_tsquery('english', 'queue retries') AS query
WHERE search @@ query
ORDER BY rank DESC
LIMIT 20;

Ваги частин документа. setweight позначає частини літерами A-D (за спаданням важливості). Збіг у заголовку (A) важить більше, ніж у тексті (D):

setweight(to_tsvector('english', title), 'A') ||
setweight(to_tsvector('english', summary), 'B') ||
setweight(to_tsvector('english', body), 'D')

Ваги можна змінити в самому ts_rank: ts_rank('{0.1, 0.2, 0.4, 1.0}', search, query) - масив для D, C, B, A.

Нормалізація за довжиною: без неї довгий документ має перевагу просто тому, що містить більше слів. Третій аргумент ts_rank задає нормалізацію: ts_rank(search, query, 32) - ділити на (1 + rank) - чи 1 - на логарифм довжини документа.

Підсвічування збігів - ts_headline повертає фрагмент тексту з позначеними словами:

SELECT ts_headline('english', body, query,
       'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15, MaxFragments=2')
FROM posts, websearch_to_tsquery('english', 'queue retries') AS query
WHERE search @@ query;

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

• ts_rank обчислюється для кожного збігу - на запиті з сотнями тисяч збігів сортування за рангом повільне. Індекс прискорює лише пошук, не ранжування.
• ts_headline повільна: вона розбирає сирий текст заново. Викликайте її лише для рядків поточної сторінки - у зовнішньому запиті після LIMIT.
• Екранування: ts_headline повертає текст разом з вашими мітками; якщо текст містить HTML від користувачів, його треба екранувати до підсвічування, інакше вийде XSS.

Коли ранжування PostgreSQL замало - урахування популярності, свіжості, персоналізації, «можливо, ви мали на увазі» - це вже задача пошукового рушія (Meilisearch, Elasticsearch, Typesense).

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

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

Типові випадки:

• Лічильники: posts.comments_count замість count(*) по коментарях на кожній сторінці списку.
• Агрегати: orders.total замість суми по позиціях; рейтинг товару.
• Копії для історії - насправді не денормалізація, а окремий факт: order_items.price в момент покупки, адреса доставки в замовленні.
• Дані для пошуку й списків: ім'я автора в рядку статті, щоб показати список без JOIN.
• Звітні таблиці й матеріалізовані подання.

Коли виправдано: читань набагато більше, ніж записів; обчислення чи JOIN справді дорогі (виміряно, а не здається); допустима невелика затримка актуальності.

Ціна - узгодженість. Тепер одне значення живе в двох місцях, і їх треба синхронізувати. Способи:

• Тригер у базі - надійно, бо спрацює для будь-якого коду, що змінює дані:

UPDATE posts SET comments_count = comments_count + 1 WHERE id = NEW.post_id;

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

Конкурентність: comments_count = comments_count + 1 атомарний; а «прочитати лічильник, додати один, записати» - втрачає оновлення при паралельних запитах.

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

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

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

1. Таблиця аудиту з тригером у базі:

CREATE TABLE audit_log (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name text NOT NULL,
    record_id bigint NOT NULL,
    action text NOT NULL,                    -- INSERT / UPDATE / DELETE
    old_data jsonb,
    new_data jsonb,
    changed_by text DEFAULT current_setting('app.user_id', true),
    changed_at timestamptz NOT NULL DEFAULT now()
);

CREATE FUNCTION audit_trigger() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
    VALUES (TG_TABLE_NAME, coalesce(NEW.id, OLD.id), TG_OP,
            CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
            CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END);
    RETURN NULL;
END $$;

CREATE TRIGGER orders_audit AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit_trigger();

• Плюс: ловить усі зміни - з застосунку, міграцій, ручних SQL-запитів.
• Мінус: база не знає користувача застосунку - його передають змінною сесії (SET LOCAL app.user_id), що потребує дисципліни.

2. Аудит у застосунку - події моделей (у Laravel: spatie/laravel-activitylog, owen-it/laravel-auditing):

• Плюс: природно знає користувача, запит, IP, контекст бізнес-дії («скасування замовлення», а не просто «змінено status»).
• Мінус: пропускає масові оновлення в обхід моделей (Model::where(...)->update()) і зміни поза застосунком.

3. Історичні таблиці / темпоральні дані - кожна версія запису з періодом дії (valid_from, valid_to). Дає відповідь на «яким був запис на 1 березня» простим запитом.

Що врахувати:

• Обсяг: таблиця аудиту росте швидше за основні. Партиціонування за часом і політика зберігання.
• Чутливі дані: у знімках змін опиняться паролі, токени, персональні дані - їх треба виключати чи маскувати.
• Незмінність: права лише на INSERT в таблицю аудиту, без UPDATE/DELETE для ролі застосунку.
• Зберігати лише змінені поля (diff), якщо повні знімки занадто великі.

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

Soft delete - замість видалення рядка позначати його як видалений: deleted_at timestamptz. Застосунок фільтрує такі рядки в запитах (у Laravel - трейт SoftDeletes з глобальним скоупом).

Плюси:

• Відновлення помилково видаленого без бекапу.
• Історія й аудит: видно, що існувало й коли зникло.
• Посилання не ламаються: старі замовлення досі посилаються на «видалений» товар.

Мінуси й підводні камені:

• Унікальність ламається. Користувач видалив обліковий запис, а зареєструватися знову з тим самим email не може: рядок досі є, і UNIQUE (email) спрацьовує. Рішення - частковий унікальний індекс:

CREATE UNIQUE INDEX users_email_active ON users (email) WHERE deleted_at IS NULL;

• Зовнішні ключі не знають про «видалення»: база не завадить створити замовлення на «видаленого» клієнта, а ON DELETE CASCADE не спрацює. Каскад доводиться реалізовувати в коді.
• Забуті фільтри: будь-який запит в обхід ORM (сирий SQL, звіти, аналітика, інший сервіс) бачить видалені рядки. Звідси витоки даних і неправильні цифри.
• Ростуть таблиці й індекси, і кожен запит несе додаткову умову. Індекси варто робити частковими (WHERE deleted_at IS NULL).
• Право на видалення даних (GDPR): «видалене» має бути видалене насправді. Soft delete не скасовує обов'язку стерти персональні дані.

Альтернативи:

• Архівна таблиця: при видаленні рядок переноситься в deleted_users тригером чи кодом. Основна таблиця лишається чистою, унікальність і ключі працюють нормально.
• Статус замість «видалення» (archived, closed), якщо це насправді бізнес-стан, а не видалення.
• Аудит-лог зі знімком видаленого запису - для відновлення й історії.

Правило: soft delete - не дефолт для кожної таблиці. Він доречний там, де відновлення й історія справді потрібні, і з продуманими частковими індексами й очищенням.

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

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

comments: id | commentable_type | commentable_id | body
          1  | App\Models\Post  | 42             | ...
          2  | App\Models\Video | 42             | ...

У Laravel це morphTo / morphMany - зручно: один механізм коментарів, тегів, вкладень для будь-яких моделей.

Проблеми з погляду бази:

• Немає зовнішнього ключа. База не може гарантувати, що commentable_id = 42 справді існує в таблиці, зазначеній у commentable_type. Видалили пост - коментарі лишилися «сиротами»; каскадне видалення - лише кодом.
• Тип - рядок з іменем класу застосунку. Перейменували модель чи простір імен - дані в базі зламалися (у Laravel рятує Relation::enforceMorphMap() з короткими псевдонімами).
• З'єднання незручні: не можна зробити один JOIN - лише окремі для кожного типу, UNION чи CASE.
• Індекс потрібен складений (commentable_type, commentable_id), і він більший через рядок типу.

Альтернативи:

1. Окремі колонки на кожен тип з перевіркою, що заповнена рівно одна:

CREATE TABLE comments (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    post_id  bigint REFERENCES posts (id) ON DELETE CASCADE,
    video_id bigint REFERENCES videos (id) ON DELETE CASCADE,
    body text NOT NULL,
    CHECK (num_nonnulls(post_id, video_id) = 1)
);

Справжні зовнішні ключі й каскади. Підходить, коли типів кілька й їх перелік стабільний.

2. Окремі проміжні таблиці: post_comments, video_comments - сам коментар без зв'язку, а зв'язок - у таблиці для кожного типу.

3. Спільний «батьківський» запис: таблиця commentable_entities, на яку посилаються і пости, і відео, а коментарі посилаються на неї. Елегантно, але складніше.

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

Докладніше в документації: Обмеження

EAV (Entity-Attribute-Value) - зберігати довільні атрибути не колонками, а рядками «сутність - атрибут - значення»:

product_attributes: product_id | attribute | value
                    1          | color     | red
                    1          | weight    | 1.2
                    2          | voltage   | 220

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

Чому EAV уникають:

• Немає типів: value - текст для всього. Числа порівнюються як рядки ('10' < '9'), дати - теж, і будь-яке значення можна записати в будь-який атрибут.
• Немає обмежень: NOT NULL, діапазони, зовнішні ключі для значень - неможливо описати базою.
• Запити жахливі: «червоні товари вагою до 2 кг» - кілька самоз'єднань тієї самої таблиці чи GROUP BY з умовами в HAVING. Кожен новий фільтр - ще один JOIN.
• Продуктивність падає з ростом таблиці атрибутів, а індекси допомагають слабо.
• Звіти й аналітика вимагають «розвертати» рядки в колонки.

Альтернативи:

• jsonb для змінних атрибутів - найчастіша сучасна заміна:

CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    price numeric(10, 2) NOT NULL,      -- спільні поля - звичайні колонки
    attributes jsonb NOT NULL DEFAULT '{}'
);

SELECT * FROM products WHERE attributes @> '{"color": "red"}';

Зберігаються типи JSON (числа - числами), є GIN-індекси, документ читається одним рядком.

• Окремі таблиці для типів сутностей (наслідування таблиць: спільна products + product_clothing, product_appliances), коли типів небагато і їхні атрибути стабільні.
• Звичайні колонки - якщо атрибутів насправді десяток і вони відомі заздалегідь.

Коли EAV ще має сенс: атрибути визначають самі користувачі в адмінці, їх тисячі, і потрібна метаінформація про кожен атрибут (тип, одиниці, порядок показу). Тоді EAV роблять з типізованими колонками значень (value_int, value_text, value_date) і таблицею-описом атрибутів.

Докладніше в документації: Entity-attribute-value model

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

Задача: три останні замовлення кожного користувача.

SELECT u.id, u.email, o.id AS order_id, o.total, o.created_at
FROM users u
CROSS JOIN LATERAL (
    SELECT id, total, created_at
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) o;

LIMIT 3 усередині застосовується окремо для кожного користувача - звичайним JOIN чи GROUP BY такого не виразити.

LEFT JOIN LATERAL ... ON true - зберегти користувачів без замовлень:

SELECT u.id, last_order.total
FROM users u
LEFT JOIN LATERAL (
    SELECT total FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1
) last_order ON true;

Коли LATERAL доречний:

• топ-N у групі, особливо з індексом (user_id, created_at DESC): для кожного користувача - коротке сканування індексу, а не сортування всієї таблиці;
• функції, що повертають набори рядків з аргументами з іншої таблиці:

SELECT p.id, tag
FROM posts p, LATERAL unnest(p.tags) AS tag;

• кілька обчислених значень без повторення виразів у SELECT;
• розгортання JSON (jsonb_array_elements, jsonb_to_recordset) для кожного рядка.

Порівняння для «топ-N у групі»:

Ризик: LATERAL - це фактично вкладений цикл. На мільйоні зовнішніх рядків без індексу для внутрішнього запиту він виконає мільйон повних сканувань. Перевіряйте план через EXPLAIN ANALYZE.

У Laravel: joinLateral() і leftJoinLateral() у конструкторі запитів:

$latest = DB::table('orders')
    ->whereColumn('user_id', 'users.id')
    ->orderByDesc('created_at')
    ->limit(3);

User::query()->joinLateral($latest, 'recent_orders')->get();

Докладніше в документації: Табличні вирази: LATERAL

FILTER - умова для окремого агрегату. Кілька різних підрахунків за один прохід таблиці:

SELECT
    count(*)                                      AS total,
    count(*) FILTER (WHERE status = 'paid')       AS paid,
    count(*) FILTER (WHERE status = 'refunded')   AS refunded,
    sum(total) FILTER (WHERE status = 'paid')     AS revenue
FROM orders
WHERE created_at >= date_trunc('month', now());

Те саме в стандартному SQL пишуть через sum(CASE WHEN ... THEN 1 ELSE 0 END) - FILTER коротший і читабельніший. Він працює з будь-якими агрегатами: sum, avg, array_agg, jsonb_agg.

generate_series - згенерувати послідовність чисел чи дат:

SELECT generate_series(1, 5);
SELECT generate_series('2026-10-01'::date, '2026-10-07', interval '1 day');

Звіт по днях без пропусків. Якщо згрупувати замовлення за днем, дні без замовлень просто зникнуть з результату - а на графіку потрібен нуль:

SELECT d::date AS day,
       count(o.id) AS orders,
       coalesce(sum(o.total), 0) AS revenue
FROM generate_series(date_trunc('day', now()) - interval '29 days',
                     date_trunc('day', now()),
                     interval '1 day') AS d
LEFT JOIN orders o
       ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY d
ORDER BY d;

• серія дає всі дні періоду;
• LEFT JOIN зберігає дні без замовлень;
• count(o.id), а не count(*) - інакше порожній день порахується як 1.

Зведена таблиця (pivot) через FILTER:

SELECT date_trunc('week', created_at) AS week,
       count(*) FILTER (WHERE channel = 'web')    AS web,
       count(*) FILTER (WHERE channel = 'mobile') AS mobile
FROM orders
GROUP BY 1
ORDER BY 1;

Часові пояси: date_trunc('day', created_at) обрізає за поясом сесії бази. Для звіту за київським часом - date_trunc('day', created_at, 'Europe/Kyiv') (для timestamptz), інакше «день» починатиметься о 02:00 чи 03:00 за Києвом.

Продуктивність: умова за діапазоном created_at >= ... AND created_at < ... використовує індекс, а WHERE date(created_at) = ... - ні (функція над колонкою).

У Laravel такі запити зручно писати через selectRaw і DB::select, а для дашбордів - кешувати: звіт за місяць не має перераховуватися на кожне відкриття сторінки.

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

MERGE (PostgreSQL 15+, стандартний SQL) порівнює цільову таблицю з джерелом і для кожного рядка виконує дію залежно від того, знайдено збіг чи ні:

MERGE INTO products AS p
USING staging_products AS s
ON p.sku = s.sku
WHEN MATCHED AND s.discontinued THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET price = s.price, updated_at = now()
WHEN NOT MATCHED THEN
    INSERT (sku, name, price) VALUES (s.sku, s.name, s.price);

PostgreSQL 17+: WHEN NOT MATCHED BY SOURCE - рядки цільової таблиці, яких немає в джерелі (наприклад, видалити товари, що зникли з фіда), і RETURNING з функцією merge_action(), що показує, яку дію виконано для рядка.

Порівняння:

Головна відмінність - поведінка при конкуренції. ON CONFLICT гарантовано обробляє ситуацію, коли інший запит одночасно вставляє той самий ключ: він чекає й оновлює. MERGE спершу визначає, чи є збіг, а потім виконує дію - якщо паралельний запит встиг вставити рядок, MERGE спробує вставити його вдруге й отримає помилку унікальності.

Коли що обирати:

• ON CONFLICT - upsert з високою конкуренцією: лічильники, кеш-таблиці, синхронізація окремих записів з багатьох процесів;
• MERGE - пакетна синхронізація з таблицею-джерелом: імпорт фіда, ETL з проміжної таблиці, де потрібні і вставки, і оновлення, і видалення, а паралельних записувачів немає (чи їх виключено блокуванням).

Типовий сценарій імпорту:

1. COPY файлу в тимчасову таблицю;
2. MERGE з неї в робочу таблицю;
3. одна транзакція - або все застосовано, або нічого.

У Laravel конструктор запитів не має методу для MERGE - використовують DB::statement() з параметрами.

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

PL/pgSQL - процедурна мова PostgreSQL: змінні, умови, цикли, обробка винятків поверх SQL.

CREATE FUNCTION transfer(from_id bigint, to_id bigint, amount numeric)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount
    WHERE id = from_id AND balance >= amount;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Insufficient funds on account %', from_id
            USING ERRCODE = 'check_violation';
    END IF;

    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$;

SELECT transfer(1, 2, 500);

Функція чи процедура:

Прості функції - на SQL, а не PL/pgSQL: LANGUAGE sql функцію планувальник може вбудувати в запит, а тіло PL/pgSQL для нього - «чорна скринька».

Коли логіка в базі виправдана:

• інваріанти даних, які мають виконуватися незалежно від того, хто пише в базу (застосунок, скрипт, адмін через psql): тригери, обмеження;
• обробка великих обсягів даних без передачі їх у застосунок і назад;
• атомарні операції, які інакше потребували б кількох запитів з блокуваннями.

Коли - ні (і це більшість бізнес-логіки):

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

У Laravel: функції створюють у міграціях (DB::unprepared() для тіла з $$), викликають через DB::select('SELECT transfer(?, ?, ?)', [...]). Винятки з RAISE EXCEPTION приходять як QueryException з кодом SQLSTATE, який можна перевірити.

Безпека: динамічний SQL усередині функції (EXECUTE) - лише з format('%I', ...) для ідентифікаторів і USING для значень, інакше SQL-ін'єкція переїжджає з PHP у базу.

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