Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

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

Питання з реальних співбесід з відповідями: Laravel і PHP, бази даних, JavaScript і фронтенд, Git, Docker, API, безпека й архітектура. Тими самими темами, що й тести.

116 питань

У PostgreSQL немає збереженого лічильника рядків таблиці. Через MVCC різні транзакції одночасно бачать різну кількість рядків, тож SELECT count(*) FROM orders мусить переглянути рядки (або індекс) і перевірити видимість кожного. На сотнях мільйонів рядків це секунди чи хвилини.

Що можна зробити:

1. Приблизна кількість зі статистики - миттєво:

SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE oid = 'public.orders'::regclass;

Значення оновлює ANALYZE/autovacuum. Для лічильника «близько 2,4 млн записів» на сторінці адмінки - цілком достатньо.

2. Оцінка для запиту з умовою - з плану:

EXPLAIN SELECT * FROM orders WHERE status = 'paid';
-- rows=183000 у плані - оцінка планувальника

3. Точна кількість швидше: index-only scan по невеликому індексу, якщо visibility map свіжа (після вакууму), - помітно швидше за читання всієї таблиці.

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

Практичні висновки:

  • Пагінація з «сторінка 1 з 48 213» - дорога на великих таблицях: кожна сторінка - ще й count(*). Laravel simplePaginate() чи cursorPaginate() обходяться без підрахунку.
  • «Чи є хоч один рядок» - не count(*) > 0, а EXISTS (SELECT 1 ... ): зупиняється на першому знайденому.
  • count(column) не швидший за count(*) - він ще й перевіряє NULL.

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

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

ANALYZE orders;                   -- одна таблиця
ANALYZE orders (status, user_id); -- окремі колонки
ANALYZE;                          -- уся база

ANALYZE читає не всю таблицю, а вибірку рядків, тож виконується швидко навіть на великих таблицях.

Чому після імпорту все повільно: статистика стала застарілою. Таблиця, у якій учора було 1000 рядків, сьогодні має 10 мільйонів, а планувальник досі думає, що їх тисяча, - і обирає план, оптимальний для маленької таблиці (наприклад, Nested Loop там, де потрібен Hash Join).

Autovacuum запускає ANALYZE автоматично, коли змінилася помітна частка таблиці, але не миттєво. Тому після масових операцій - імпорту, видалення великої частини даних, відновлення з дампу, pg_upgrade - ANALYZE варто запустити вручну одразу.

Як помітити проблему: у EXPLAIN ANALYZE оцінка rows сильно розходиться з actual rows. Коли статистика застаріла - розходження величезне; ANALYZE усе виправляє.

Коли статистики недостатньо навіть свіжої:

  • Перекошений розподіл значень - збільшити деталізацію: ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000.
  • Корельовані колонки - розширена статистика (CREATE STATISTICS).

Час останнього аналізу видно в pg_stat_user_tables (last_analyze, last_autoanalyze).

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

psql - консольний клієнт PostgreSQL. Окрім SQL, у ньому є метакоманди з \, що швидко показують структуру бази.

Підключення:

psql -h localhost -U app -d app_production
psql "postgresql://app@localhost:5432/app_production"

Структура бази:

\l              список баз
\c app_test     підключитися до іншої бази
\dt             таблиці
\d orders       структура таблиці: колонки, індекси, обмеження, тригери
\di             індекси
\dn             схеми
\du             ролі
\df             функції
\dx             встановлені розширення
\dt+            таблиці з розміром

Робота із запитами:

\x auto         розгорнутий вивід для широких рядків
\timing on      показувати час виконання кожного запиту
\e              редагувати останній запит у $EDITOR
\i file.sql     виконати файл
\copy orders TO 'orders.csv' CSV HEADER   експорт з клієнтського боку
\watch 2        повторювати запит кожні 2 секунди
\q              вийти

Корисні прийоми:

  • \set ON_ERROR_STOP on у скриптах - зупинитися на першій помилці замість виконання решти.
  • BEGIN; перед ризикованими змінами на проді - подивитися результат і вирішити: COMMIT чи ROLLBACK.
  • \set AUTOCOMMIT off - кожна команда автоматично в транзакції, доки не зробити COMMIT.
  • ~/.psqlrc - налаштування за замовчуванням (\timing, \x auto, кольоровий промпт для продакшену).
  • ~/.pgpass - паролі для підключень, щоб не вводити їх щоразу й не передавати в командному рядку.

На проді - обережно: psql на продакшн-базі - повний доступ. Безпечніше підключатися до репліки для читання і мати окрему роль лише з правами читання для розслідувань.

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

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

Типова схема ролей:

-- Власник схеми: від його імені ганяють міграції
CREATE ROLE app_owner LOGIN PASSWORD '...';
CREATE SCHEMA app AUTHORIZATION app_owner;

-- Застосунок: лише робота з даними
CREATE ROLE app_user LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_user;

-- Права на таблиці, які створять пізніше міграції
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT USAGE ON SEQUENCES TO app_user;

-- Аналітика й розслідування: лише читання
CREATE ROLE readonly LOGIN PASSWORD '...';
GRANT pg_read_all_data TO readonly;   -- PostgreSQL 14+

Що це дає:

  • Застосунок не може DROP TABLE, ALTER, створювати ролі чи читати системні налаштування.
  • Міграції виконуються окремою роллю - її пароль не живе в змінних середовища вебсерверів.
  • Аналітики й розробники на проді працюють від ролі лише для читання.

Деталі, про які забувають:

  • Права за замовчуванням (ALTER DEFAULT PRIVILEGES) - без них нова таблиця з міграції буде недоступна застосунку.
  • Схема public: з PostgreSQL 15 звичайні ролі за замовчуванням не можуть створювати в ній об'єкти - на старіших версіях це право варто забрати (REVOKE CREATE ON SCHEMA public FROM PUBLIC).
  • Послідовності потребують окремого USAGE, інакше вставка з автоінкрементом впаде.
  • Підключення обмежують ще й на рівні pg_hba.conf: з яких адрес, до яких баз, яким методом автентифікації (scram-sha-256).

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

Мінорні оновлення (17.4 → 17.6) - лише виправлення помилок: досить оновити пакет і перезапустити сервер. Формат даних не змінюється.

Мажорні (16 → 17 → 18) змінюють внутрішній формат зберігання, тож файли даних старої версії нова прочитати не може. Є три способи:

1. pg_dump + відновлення - найпростіше й найнадійніше:

pg_dump -Fc -d app > app.dump
# новий сервер
pg_restore -d app app.dump

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

2. pg_upgrade - перетворює каталог даних «на місці»:

pg_upgrade --old-datadir ... --new-datadir ... --old-bindir ... --new-bindir ... --link

З --link файли даних не копіюються, а використовуються жорсткі посилання - оновлення займає хвилини навіть для великих баз. Обов'язково спершу --check (сухий прогін). Після оновлення потрібна статистика для планувальника (vacuumdb --analyze-in-stages); з PostgreSQL 18 pg_upgrade уміє переносити її сам.

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

Як підготуватися:

  • Прочитати release notes усіх мажорних версій між поточною й цільовою - розділи про несумісності.
  • Перевірити, що розширення (PostGIS, pgvector тощо) доступні для нової версії.
  • Прогнати тести застосунку на новій версії, відрепетирувати оновлення на копії продакшн-даних, виміряти час.
  • Мати свіжий бекап і план відкату.

На керованих сервісах (RDS, Cloud SQL) мажорне оновлення - кнопка, але простій і перевірки сумісності ті самі - про них варто подбати заздалегідь.

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

-- settings = '{"theme": "dark", "notify": {"email": true}, "tags": ["php", "sql"]}'

settings -> 'notify'                 -- {"email": true}   (результат - jsonb)
settings ->> 'theme'                 -- dark              (результат - text)
settings -> 'notify' ->> 'email'     -- true
settings #>> '{notify,email}'        -- true (шлях масивом)
settings -> 'tags' -> 0              -- "php" (елемент масиву за індексом з нуля)

settings @> '{"theme": "dark"}'      -- містить цю пару
settings ? 'theme'                   -- є ключ верхнього рівня
settings ?| array['theme', 'lang']   -- є хоча б один з ключів
settings ?& array['theme', 'lang']   -- є всі ключі

Головна різниця, на якій помиляються: -> повертає jsonb, ->> - text. Для порівняння з рядком чи числом потрібен ->> і приведення типу:

WHERE (data ->> 'price')::numeric > 100
WHERE data ->> 'status' = 'active'

@> (містить) - найкорисніший оператор для фільтрів: його прискорює GIN-індекс, на відміну від порівняння через ->>.

Пастка в PHP: оператори ?, ?|, ?& конфліктують із заповнювачами параметрів PDO - драйвер прийме ? за параметр запиту. Варіанти:

  • функції-аналоги: jsonb_exists(settings, 'theme'), jsonb_exists_any(), jsonb_exists_all();
  • подвоєний знак ?? (PDO з PHP 7.4 розуміє його як буквальний ?);
  • оператор @> там, де він підходить за змістом.

У Laravel запити до JSON-полів пишуть через стрілки в імені колонки - Query Builder перетворює їх на оператори PostgreSQL:

User::where('settings->theme', 'dark')->get();
User::whereJsonContains('settings->tags', 'php')->get();

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

Значення jsonb - одне ціле: «змінити поле на місці» неможливо, будь-яка зміна створює новий документ. Але PostgreSQL має функції й оператори, які збирають новий документ за вас:

-- встановити чи замінити значення за шляхом
UPDATE users SET settings = jsonb_set(settings, '{notify,email}', 'false')
WHERE id = 1;

-- jsonb_set створює лише останній ключ шляху: якщо об'єкта ui ще немає,
-- документ повернеться без змін. Проміжний об'єкт збирають явно:
UPDATE users SET settings = jsonb_set(
    settings, '{ui}', coalesce(settings -> 'ui', '{}') || '{"sidebar": "collapsed"}'
);

-- злити об'єкти: ключі правого перезапишуть лівий
UPDATE users SET settings = settings || '{"theme": "light", "lang": "uk"}';

-- видалити ключ / шлях / елемент масиву
UPDATE users SET settings = settings - 'beta_features';
UPDATE users SET settings = settings #- '{notify,sms}';

-- вставити в масив
UPDATE users SET settings = jsonb_insert(settings, '{tags,0}', '"laravel"');

Нюанси:

  • Значення в jsonb_set - теж JSON: рядок пишуть з подвійними лапками всередині ('"collapsed"'), число й true/false - без.
  • jsonb_set з NULL як новим значенням (не JSON-null, а SQL NULL) поверне NULL для всього документа - і стерте налаштування. Для безпечної роботи з NULL є jsonb_set_lax() (PostgreSQL 13+): за замовчуванням він запише JSON-null.
  • || зливає лише верхній рівень: вкладений об'єкт праворуч повністю замінить вкладений об'єкт ліворуч, а не зіллється з ним.
  • Конкурентні оновлення: два запити, що одночасно змінюють різні ключі одного документа через jsonb_set, не конфліктують - кожен перечитує актуальне значення в UPDATE. А от «прочитати весь JSON у застосунок → змінити → записати назад» дає загублене оновлення.

У Laravel:

$user->update(['settings->notify->email' => false]);   // перетвориться на jsonb_set

Сигнал до зміни схеми: якщо якесь поле JSON постійно оновлюється й фільтрується окремо, - йому, ймовірно, місце в окремій колонці.

Докладніше в документації: Функції обробки JSON

Нормалізація - розкладання даних по таблицях так, щоб кожен факт зберігався в одному місці. Мета - уникнути аномалій: коли зміну доводиться вносити в кількох місцях і одне з них забувають.

Приклад проблеми:

orders: id | customer_name | customer_email | product | price
        1  | Оля           | olia@x.com     | Книга   | 300
        2  | Оля           | olia@x.com     | Ручка   | 50

Оля змінила email - треба оновити всі її замовлення. Пропустили одне - дані суперечать одне одному.

Нормальні форми (основні три):

  • 1НФ - у кожній клітинці одне атомарне значення; немає повторюваних груп («product1, product2, product3» чи список через кому в одній колонці).
  • 2НФ - 1НФ, і кожна неключова колонка залежить від усього складеного ключа, а не від частини. У таблиці (order_id, product_id, product_name, qty) назва товару залежить лише від product_id - її місце в таблиці товарів.
  • 3НФ - 2НФ, і неключові колонки не залежать одна від одної (немає транзитивних залежностей). orders (id, customer_id, customer_email) - email залежить від клієнта, а не від замовлення.

Нормалізована схема:

customers: id | name | email
products:  id | name | price
orders:    id | customer_id | created_at
order_items: order_id | product_id | quantity | price_at_purchase

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

Практичне правило: проєктувати в 3НФ за замовчуванням і свідомо денормалізувати там, де виміряно, що це потрібно для швидкодії. Вищі форми (BCNF, 4НФ, 5НФ) у прикладних застосунках потрібні рідко.

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

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

CREATE TABLE posts (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL);
CREATE TABLE tags  (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL UNIQUE);

CREATE TABLE post_tag (
    post_id bigint NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
    tag_id  bigint NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX post_tag_tag_id_idx ON post_tag (tag_id);

Ключові деталі:

  • Складений первинний ключ (post_id, tag_id) - не дає прив'язати той самий тег двічі і водночас є індексом для «теги статті».
  • Окремий індекс на tag_id - для зворотного напрямку «статті з тегом». Складений ключ починається з post_id, тож для пошуку за tag_id він не допоможе.
  • ON DELETE CASCADE - видалили статтю, зник і зв'язок. Без цього видалення статті падатиме на зовнішньому ключі.

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

CREATE TABLE team_user (
    team_id bigint REFERENCES teams (id),
    user_id bigint REFERENCES users (id),
    role text NOT NULL DEFAULT 'member',
    joined_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (team_id, user_id)
);

У Laravel: belongsToMany з таблицею post_tag (назви моделей в однині, за абеткою), withPivot('role') і withTimestamps() для атрибутів, attach/detach/sync для зміни зв'язків.

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

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

  • Природний ключ - значення, що вже унікальне в реальному світі: email, ІПН, код валюти UAH, ISBN, номер телефону.
  • Сурогатний ключ - штучний ідентифікатор без бізнес-змісту: id з послідовності чи UUID.

Проблеми природних ключів:

  • Вони змінюються. Email змінюють, телефони переносять, номери документів виправляють. Зміна первинного ключа - каскадне оновлення всіх таблиць, що на нього посилаються.
  • Унікальність, яка виявляється не зовсім унікальною: один номер телефону в сім'ї, повторно використаний email після видалення облікового запису, дублі ІПН через помилки введення.
  • Розмір і швидкість: довгий рядок як ключ і в кожному зовнішньому ключі займає більше місця, ніж bigint.
  • Розкриття даних: email в URL і логах.

Тому типова практика:

  • Сурогатний первинний ключ для зв'язків між таблицями;
  • Унікальне обмеження на природний ключ, щоб правило бізнесу все одно перевірялося базою:
CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL,
    CONSTRAINT users_email_unique UNIQUE (email)
);

Коли природний ключ доречний:

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

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

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

Upsert - «вставити, а якщо такий запис уже є, - оновити». У PostgreSQL це один атомарний запит:

INSERT INTO product_prices (product_id, currency, amount)
VALUES (42, 'UAH', 1999)
ON CONFLICT (product_id, currency)
DO UPDATE SET amount = EXCLUDED.amount, updated_at = now();
  • ON CONFLICT (колонки) - на якому унікальному обмеженні чи індексі визначати конфлікт;
  • EXCLUDED - рядок, який намагалися вставити: з нього беруть нові значення;
  • DO NOTHING - просто пропустити дублікат.
INSERT INTO subscriptions (user_id, list_id)
VALUES (7, 3)
ON CONFLICT DO NOTHING;

Обов'язкова умова: на колонках з ON CONFLICT має бути унікальний індекс чи обмеження. Інакше:

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Умовне оновлення - не перезаписувати свіжіші дані старішими:

INSERT INTO stock (sku, qty, synced_at) VALUES ('A-1', 10, '2026-10-04 09:00')
ON CONFLICT (sku) DO UPDATE
SET qty = EXCLUDED.qty, synced_at = EXCLUDED.synced_at
WHERE stock.synced_at < EXCLUDED.synced_at;

Лічильник одним запитом:

INSERT INTO page_views (page_id, day, views) VALUES (5, current_date, 1)
ON CONFLICT (page_id, day) DO UPDATE SET views = page_views.views + 1;

Чому не «SELECT, потім INSERT або UPDATE»: між перевіркою й записом інший запит може вставити той самий рядок - і один з двох отримає помилку унікальності чи перезапише чужі дані. ON CONFLICT розв'язує конфлікт атомарно всередині бази.

У Laravel Model::upsert($rows, uniqueBy: [...], update: [...]) генерує саме INSERT ... ON CONFLICT ... DO UPDATE. Події моделі при цьому не спрацьовують.

Нюанси:

  • одним запитом не можна оновити той самий рядок двічі: якщо в пакеті вставки два рядки з однаковим ключем - помилка ON CONFLICT DO UPDATE command cannot affect row a second time. Дублікати треба прибрати до запиту;
  • послідовність identity/serial витрачає значення навіть при конфлікті - у нумерації з'являються пропуски, і це нормально.

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

RETURNING повертає дані рядків, які щойно вставлено, оновлено чи видалено, - тим самим запитом, без додаткового SELECT.

INSERT INTO orders (user_id, total) VALUES (7, 1999)
RETURNING id, created_at;

Типова потреба: дізнатися згенерований id, значення за замовчуванням (created_at, uuid), результат тригера.

З UPDATE і DELETE:

UPDATE accounts SET balance = balance - 500
WHERE id = 7 AND balance >= 500
RETURNING balance;
-- 0 рядків у відповіді - коштів не вистачило

DELETE FROM sessions WHERE last_activity < now() - interval '30 days'
RETURNING user_id;

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

UPDATE products SET price = price * 1.1 WHERE category_id = 3
RETURNING id, old.price AS old_price, new.price AS new_price;

Раніше для «було - стало» потрібен був окремий запит до оновлення чи тригер.

З ON CONFLICT - дізнатися, чи рядок вставлено, чи оновлено:

INSERT INTO prices (sku, amount) VALUES ('A-1', 100)
ON CONFLICT (sku) DO UPDATE SET amount = EXCLUDED.amount
RETURNING id, (xmax = 0) AS inserted;

(xmax = 0 - поширений прийом, що спирається на внутрішню деталь MVCC, а не на гарантований інтерфейс.)

Чому це важливо:

  • на один запит менше - менше затримки, особливо коли база на іншому сервері;
  • атомарність: окремий SELECT після UPDATE може побачити вже змінений іншим запитом рядок, а RETURNING повертає саме те, що записав ваш запит;
  • черга задач: UPDATE ... WHERE id = (SELECT ... FOR UPDATE SKIP LOCKED) RETURNING * - взяти задачу й позначити її зайнятою одним запитом.

У Laravel: при Model::create() на PostgreSQL Eloquent отримує id саме через INSERT ... RETURNING "id". Для решти сценаріїв - сирий запит:

$rows = DB::select('UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ? RETURNING balance', [500, 7, 500]);

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

DISTINCT ON (вирази) - розширення PostgreSQL: з кожної групи рядків з однаковими значеннями виразів лишається перший рядок за порядком ORDER BY.

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

SELECT DISTINCT ON (user_id) user_id, id, total, created_at
FROM orders
ORDER BY user_id, created_at DESC;
  • групи визначає user_id;
  • усередині групи рядки впорядковані за created_at DESC;
  • лишається перший - тобто найновіший.

Правило: вирази з DISTINCT ON мають бути на початку ORDER BY. Інакше:

ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Без додаткового сортування всередині групи «перший» рядок випадковий - завжди вказуйте, який саме потрібен.

Порівняння з альтернативами:

Спосіб Особливості
DISTINCT ON найкоротший запис, лише PostgreSQL
віконна функція row_number() стандартний SQL, працює й у MySQL 8, можна взяти N рядків на групу
LATERAL з LIMIT 1 швидкий, коли груп мало, а рядків у групі багато, і є індекс
-- те саме через віконну функцію
SELECT * FROM (
  SELECT o.*, row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t WHERE rn = 1;

Продуктивність: індекс (user_id, created_at DESC) дозволяє виконати DISTINCT ON без окремого сортування всієї таблиці. Без індексу PostgreSQL сортує всі рядки - на великій таблиці це повільно.

У Laravel є прямий метод:

Order::query()
    ->distinct('user_id')
    ->orderBy('user_id')
    ->latest()
    ->get();

distinct('user_id') на PostgreSQL генерує DISTINCT ON ("user_id"). Для зв'язку «останнє замовлення» в Eloquent є latestOfMany() - він будує підзапит, що працює в усіх базах.

Типова помилка - очікувати від DISTINCT ON агрегацію: він не рахує суми й кількості, лише вибирає один рядок з групи. Для підсумків потрібен GROUP BY.

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

Представлення (view) - збережений запит з іменем. Його можна читати як таблицю, але дані не зберігаються: при кожному зверненні PostgreSQL виконує запит, що лежить в основі.

CREATE VIEW active_customers AS
SELECT u.id, u.email, count(o.id) AS orders_count, max(o.created_at) AS last_order_at
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.email;

SELECT * FROM active_customers WHERE orders_count > 5;

Планувальник підставляє запит представлення в зовнішній запит і оптимізує їх разом - умова orders_count > 5 не означає, що спершу рахуються всі клієнти.

Навіщо представлення:

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

Звичайне проти матеріалізованого:

VIEW MATERIALIZED VIEW
зберігає дані ні так, знімок на момент оновлення
актуальність завжди до REFRESH
швидкість читання як у запиту як у таблиці, можна індексувати

Оновлювані представлення: просте представлення з однієї таблиці без агрегатів PostgreSQL дозволяє оновлювати через INSERT/UPDATE/DELETE. WITH CHECK OPTION забороняє вставку рядків, які не пройдуть умову WHERE представлення.

У Laravel представлення створюють у міграції через DB::statement('CREATE VIEW ...'), а читають звичайною моделлю:

class ActiveCustomer extends Model
{
    protected $table = 'active_customers';
}

Підводні камені:

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

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

Розширення додають у базу нові типи, функції, оператори й індекси без зміни самого PostgreSQL. Встановлюються командою в конкретній базі:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT extname, extversion FROM pg_extension;    -- що вже встановлено
SELECT name, default_version FROM pg_available_extensions;   -- що доступно на сервері

Корисні розширення:

Розширення Для чого
pg_stat_statements статистика всіх запитів: які найповільніші й найчастіші - основний інструмент пошуку проблем
pg_trgm пошук за схожістю й LIKE '%текст%' з індексом
unaccent пошук без урахування діакритичних знаків
citext рядки без урахування регістру (email)
pgcrypto хешування й шифрування в SQL
btree_gist обмеження на кшталт «бронювання не перетинаються» з рівністю й діапазоном
pgvector вектори для семантичного пошуку й RAG
postgis географічні дані: відстані, полігони, «найближчі точки»
pg_partman автоматичне створення партицій

pg_stat_statements - особливий: його потрібно завантажити при старті сервера:

shared_preload_libraries = 'pg_stat_statements'
SELECT calls, round(mean_exec_time::numeric, 1) AS avg_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

У Laravel розширення вмикають у міграції:

public function up(): void
{
    DB::statement('CREATE EXTENSION IF NOT EXISTS pg_trgm');
}

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

  • права: створювати більшість розширень може лише суперкористувач чи власник бази; користувач застосунку з мінімальними правами міграцію не виконає - розширення встановлює адміністратор;
  • керовані бази (RDS, Cloud SQL, Neon, Supabase) дозволяють лише розширення зі свого списку - перевірте до того, як будувати на ньому архітектуру;
  • тестова база теж має мати розширення, інакше міграції в CI впадуть;
  • оновлення PostgreSQL: розширення мають бути доступні й у новій версії, а ALTER EXTENSION ... UPDATE - частина плану оновлення.

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

Питання з реальних технічних співбесід - 116 питань у 9 темах, розібраних із відповідями. Нижче - розбивка за рівнями та темами, якщо хочете звузити підготовку.

Рівні
Junior 30 Middle 47 Senior 39

Готуєтесь до співбесіди не просто так: зараз на сайті 90 відкритих вакансій Laravel і PHP. Переглянути вакансії