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

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

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

116 питань

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

  • statement_timeout - максимальний час виконання однієї команди. Після нього запит скасовується з помилкою.
  • lock_timeout - максимальний час очікування блокування. Сам запит може йти довго, але чекати на чужі блокування - не довше вказаного.
  • idle_in_transaction_session_timeout - розірвати сесію, що простоює всередині відкритої транзакції.
  • transaction_timeout (PostgreSQL 17+) - ліміт на всю транзакцію.

Де налаштовувати - на різних рівнях:

-- для ролі застосунку
ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

-- для окремої сесії чи транзакції
SET lock_timeout = '3s';                    -- для сесії
SET LOCAL statement_timeout = '5min';       -- лише до кінця поточної транзакції

Типові значення:

  • Веб-запити: statement_timeout кілька секунд - веб-запит, що чекає базу хвилину, однаково нікому не потрібен, а тримає з'єднання з пулу.
  • Міграції: lock_timeout 2-5 секунд, щоб ALTER TABLE не вишикував чергу з усіх запитів; statement_timeout - побільше або вимкнений для довгих операцій на зразок CREATE INDEX CONCURRENTLY.
  • Звіти й фонові задачі: окрема роль чи SET LOCAL з більшими лімітами.

Застереження:

  • Глобальний statement_timeout у postgresql.conf зачепить і адміністративні операції - pg_dump, вакуум вручну. Краще налаштовувати на роль застосунку.
  • З PgBouncer у режимі transaction pooling SET без LOCAL «протікає» в інші сесії - використовувати SET LOCAL або налаштування ролі.
  • Помилку таймауту застосунок має обробляти: логувати з текстом запиту й показувати користувачу зрозуміле повідомлення, а не 500.

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

PostgreSQL можна використати як надійну чергу без окремого брокера - так працюють драйвер database у Laravel, good_job у Rails, pg-boss, River.

Основа - FOR UPDATE SKIP LOCKED:

BEGIN;

SELECT id, payload
FROM jobs
WHERE queue = 'default' AND available_at <= now()
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- обробили завдання
DELETE FROM jobs WHERE id = $1;
COMMIT;

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

LISTEN / NOTIFY - щоб воркери не опитували таблицю постійно:

-- воркер
LISTEN new_job;

-- при додаванні завдання (можна тригером)
NOTIFY new_job;

Повідомлення доставляються після коміту транзакції-відправника, тож воркер не прокинеться раніше, ніж завдання стане видимим.

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

Межі:

  • Навантаження: тисячі завдань на секунду - уже відчутно для бази: кожне завдання - вставка, оновлення, видалення, мертві версії й вакуум. Таблиця черги роздувається й потребує агресивного autovacuum.
  • NOTIFY не надійний як черга: повідомлення не зберігаються, якщо слухача немає; розмір payload обмежений (8000 байтів). Це лише «будильник», джерело правди - таблиця.
  • Конкуренція з основним навантаженням бази за ресурси.

Коли брати Redis/RabbitMQ/SQS: високий потік завдань, потреба в pub/sub, затримках і пріоритетах без навантаження на основну базу. Для типового застосунку з десятками-сотнями завдань на хвилину черга в PostgreSQL - цілком розумний вибір.

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

Двофазний коміт (2PC) - протокол, щоб кілька незалежних баз (чи ресурсів) закомітили спільну транзакцію атомарно: або всі, або жодна.

  1. Фаза підготовки: координатор просить кожного учасника підготуватися. Учасник виконує все, крім фіксації, гарантує, що зможе закомітити навіть після перезапуску, і відповідає «готовий».
  2. Фаза фіксації: якщо готові всі - координатор надсилає COMMIT кожному; якщо хтось відмовив - ROLLBACK усім.

У PostgreSQL це команди:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
PREPARE TRANSACTION 'transfer-42';     -- фаза 1

-- пізніше, за рішенням координатора
COMMIT PREPARED 'transfer-42';         -- фаза 2
-- або ROLLBACK PREPARED 'transfer-42';

За замовчуванням вимкнено: max_prepared_transactions = 0.

Чому в застосунках його уникають:

  • Блокування на час координації. Підготовлена транзакція тримає блокування й горизонт вакууму, доки координатор не прийме рішення. Якщо координатор упав між фазами, транзакція «зависає» - її доводиться розв'язувати вручну, а тим часом таблиці роздуваються.
  • Координатор - єдина точка відмови й складна частина системи, яку треба писати й підтримувати.
  • Не всі учасники підтримують 2PC: зовнішні API, черги, пошта не вміють «підготуватися».
  • Знижує доступність: транзакція можлива, лише коли доступні всі учасники.

Що використовують натомість:

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

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

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

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

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

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

Finalize Aggregate
  -> Gather  (Workers Planned: 2, Workers Launched: 2)
       -> Partial Aggregate
            -> Parallel Seq Scan on orders

Що вміє працювати паралельно: послідовне й індексне сканування, Hash Join і Nested Loop, агрегати, сортування з об'єднанням (Gather Merge), створення B-tree індексів.

Ключові налаштування:

  • max_parallel_workers_per_gather (за замовчуванням 2) - воркерів на один вузол запиту;
  • max_parallel_workers і max_worker_processes - загальні ліміти на сервер;
  • min_parallel_table_scan_size - менші таблиці не розпаралелюються;
  • parallel_setup_cost, parallel_tuple_cost - вартість запуску й передачі рядків, з якою планувальник порівнює виграш.

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

Коли не допомагає чи шкодить:

  • OLTP-запити (знайти користувача за ID) - запуск воркерів коштує більше, ніж сам запит; планувальник їх і не розпаралелить.
  • Високе паралельне навантаження: якщо сервер і так зайнятий сотнями запитів, воркери конкурують з ними за ядра. Сумарна пропускна здатність може впасти.
  • Запити, що змінюють дані (UPDATE, DELETE), майже не розпаралелюються, а INSERT ... SELECT - лише частково.
  • Функції, позначені PARALLEL UNSAFE (за замовчуванням для власних функцій), забороняють паралельний план.

Як діагностувати: Workers Planned проти Workers Launched у EXPLAIN ANALYZE - якщо запущено менше запланованого, на сервері забракло вільних воркерів.

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

Підготовлений запит (prepared statement) розбирається один раз, а виконується багато разів з різними параметрами. PDO і Laravel використовують їх для прив'язки параметрів.

PREPARE find_orders (text) AS SELECT * FROM orders WHERE status = $1;
EXECUTE find_orders('pending');

Для виконання PostgreSQL може використати два види плану:

  • Custom plan - будується для конкретних значень параметрів. Точний, але планування на кожне виконання коштує час.
  • Generic plan - один план для будь-яких значень, без урахування конкретних. Будується раз і перевикористовується.

Як PostgreSQL обирає (plan_cache_mode = auto): перші п'ять виконань - custom-плани. Потім він порівнює їхню середню вартість з вартістю generic-плану і, якщо generic не набагато гірший, переходить на нього.

Де це ламається - перекошені дані:

-- status = 'pending'   - 0.1% рядків → ідеальний Index Scan
-- status = 'completed' - 95% рядків → потрібен Seq Scan

Generic-план не знає, яке значення прийде, і обирає «середній» варіант. Запит, що спершу виконувався за мілісекунди, після п'ятого разу раптом стає повільним для частини значень. Класична загадка «запит повільний лише в застосунку, а в psql - швидкий» (у psql ви виконуєте його з конкретним значенням - custom план).

Що робити:

SET plan_cache_mode = force_custom_plan;   -- для сесії чи ролі

Або точково - для конкретних запитів з перекошеними даними.

Як діагностувати: EXPLAIN EXECUTE find_orders('completed') після шостого виконання показує, чи план уже generic (параметри в ньому виглядають як $1); auto_explain у логах продакшену.

Нюанс PHP: PDO з PDO::ATTR_EMULATE_PREPARES = true підставляє параметри сам і надсилає готовий текст - тоді на сервері підготовлених запитів немає взагалі, і проблеми generic-планів теж. Laravel за замовчуванням для PostgreSQL використовує справжні підготовлені запити.

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

JIT (з PostgreSQL 11, увімкнено за замовчуванням з 12) компілює частини виконання запиту - обчислення виразів у WHERE, агрегатів, розбір рядків - у машинний код через LLVM. Замість інтерпретації виразу для кожного рядка виконується скомпільований код.

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

Як вирішується, чи вмикати: за оцінною вартістю запиту.

  • jit_above_cost (100 000) - вмикати JIT для запитів, дорожчих за це значення;
  • jit_inline_above_cost, jit_optimize_above_cost (500 000) - вмикати вбудовування й агресивну оптимізацію.

Чому JIT часто вимикають на OLTP-серверах:

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

Як побачити в плані:

JIT:
  Functions: 18
  Timing: Generation 2.1 ms, Inlining 45.3 ms, Optimization 98.7 ms, Emission 60.2 ms, Total 206.3 ms
Execution Time: 248.9 ms

Тут на JIT пішло понад 80% часу запиту.

Що роблять:

ALTER SYSTEM SET jit = off;                -- для типового веб-застосунку
ALTER ROLE analytics SET jit = on;         -- лишити для аналітики

або піднімають пороги jit_above_cost, щоб JIT вмикався лише для справді важких запитів.

Висновок: як і JIT у PHP, у PostgreSQL він корисний для обчислювально важкої аналітики й мало допомагає типовому вебу з короткими запитами.

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

Failover - підвищення репліки до нового primary, коли старий недоступний. Сам PostgreSQL уміє лише виконати підвищення за командою (pg_promote()); вирішувати, коли це робити, має зовнішній інструмент.

Інструменти: Patroni (найпоширеніший), pg_auto_failover, repmgr, Stolon; у хмарі - керовані сервіси (RDS Multi-AZ, Cloud SQL HA), які роблять це самі.

Як працює Patroni:

  • Кожен вузол PostgreSQL має агента Patroni.
  • Хто зараз лідер, записано в розподіленому сховищі консенсусу (etcd, Consul, ZooKeeper) з обмеженим терміном оренди.
  • Лідер постійно поновлює оренду. Якщо він зник і оренда спливла, агенти реплік обирають нового лідера - зазвичай найменш відсталу репліку, - і та підвищується.
  • Застосунок підключається через точку доступу, що завжди веде до поточного лідера: HAProxy з перевіркою стану, віртуальний IP чи DNS.

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

Як захищаються:

  • Консенсус і кворум: лідером може бути лише той, хто тримає оренду в сховищі консенсусу; вузол, що втратив зв'язок з більшістю, сам знижується до репліки.
  • Fencing («огорожа»): гарантовано зупинити старий primary (вимкнути, відрізати від мережі, watchdog), перш ніж підвищувати новий.
  • Синхронна реплікація - щоб при перемиканні не втратити підтверджені транзакції.

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

  • Асинхронна реплікація = можлива втрата останніх транзакцій при failover. Скільки - визначає затримка реплікації.
  • Застосунок має переживати перемикання: повторні підключення, повтори транзакцій, короткі таймаути з'єднань.
  • Регулярні навчання: failover, який ніколи не перевіряли, у критичний момент зазвичай не спрацьовує.

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

Row-Level Security (RLS) - політики на рівні бази, що визначають, які рядки таблиці бачить і може змінювати роль. Фільтр додається до кожного запиту автоматично - навіть якщо в коді його забули.

Ізоляція тенантів:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;   -- діє й на власника таблиці

CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.tenant_id')::bigint)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);
  • USING - які рядки видно (SELECT, UPDATE, DELETE).
  • WITH CHECK - які рядки дозволено записати (INSERT, UPDATE): не можна вставити рядок з чужим tenant_id.

Застосунок на початку кожного запиту чи транзакції встановлює тенанта:

BEGIN;
SET LOCAL app.tenant_id = '42';
SELECT * FROM projects;   -- лише проєкти тенанта 42, без WHERE у коді
COMMIT;

Чому це цінно: у мультитенантному застосунку найнебезпечніший баг - запит без фільтра за тенантом. З RLS такий запит поверне не чужі дані, а лише дані поточного тенанта (або нічого, якщо тенант не встановлений).

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

  • Суперкористувачі й ролі з BYPASSRLS політики ігнорують, а власник таблиці - теж, без FORCE ROW LEVEL SECURITY. Застосунок має працювати від звичайної ролі.
  • Пул з'єднань: з PgBouncer у режимі transaction pooling - лише SET LOCAL усередині транзакції. SET без LOCAL залишить тенанта в з'єднанні, яке потім отримає інший запит - найгірший можливий сценарій.
  • Продуктивність: умова політики додається до кожного запиту - потрібен індекс на tenant_id, а функції в політиці мають бути простими.
  • Фонові задачі й адмінка мають явно працювати або в контексті тенанта, або від окремої ролі з обґрунтованим обходом.
  • Налагодження складніше: «чому запит нічого не повертає» часто означає, що не встановлено змінну тенанта.

RLS - сильний додатковий рівень захисту, але не заміна перевіркам у застосунку.

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

DELETE FROM events WHERE created_at < '2025-01-01' на 50 мільйонах рядків в одній транзакції:

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

Правильно - порціями:

-- повторювати, доки видаляється хоч щось
DELETE FROM events
WHERE id IN (
    SELECT id FROM events
    WHERE created_at < '2025-01-01'
    ORDER BY id
    LIMIT 10000
);

Кожна порція - коротка транзакція. Між порціями - невелика пауза, щоб вакуум і репліки встигали. У Laravel - команда з циклом і ->limit(10000)->delete() чи chunkById з видаленням.

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

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

ALTER TABLE events DETACH PARTITION events_2024_12;
DROP TABLE events_2024_12;

Миттєво, без мертвих рядків, без навантаження на вакуум і без величезного WAL.

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

Після масового видалення: VACUUM (звичайний) звільнить місце для повторного використання, але файл таблиці не зменшиться. Повернути місце ОС без довгого ексклюзивного блокування допоможе pg_repack; VACUUM FULL блокує таблицю на весь час роботи.

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

Вбудованої української конфігурації в PostgreSQL немає - серед стандартних (english, russian, german, ...) української мови немає й у PostgreSQL 18. Перевірити: SELECT cfgname FROM pg_ts_config;.

Варіанти:

1. Конфігурація simple - без стемінгу й стоп-слів, лише нижній регістр:

to_tsvector('simple', 'Черги в Laravel')   -- 'черги':1 'в':2 'laravel':3

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

2. Hunspell-словник для української - справжня нормалізація форм. Файли словника (.dict, .affix, стоп-слова) кладуть у каталог tsearch_data сервера, а потім:

CREATE TEXT SEARCH DICTIONARY ukrainian_hunspell (
    TEMPLATE = ispell, DictFile = uk_ua, AffFile = uk_ua, StopWords = ukrainian
);

CREATE TEXT SEARCH CONFIGURATION ukrainian (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION ukrainian
    ALTER MAPPING FOR word, hword, hword_part
    WITH ukrainian_hunspell, simple;

Потрібен доступ до файлової системи сервера - на керованих базах (RDS, Cloud SQL) це зазвичай неможливо.

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

Пастка, на якій ламається пошук кирилицею, - локаль бази. Приведення до нижнього регістру в повнотекстовому пошуку й у lower() залежить від LC_CTYPE бази. З локаллю C (часто так створюють базу в Docker і для тестів) кирилиця не перетворюється на малі літери:

SELECT lower('Черги');                                      -- 'Черги' при LC_CTYPE = C
SELECT to_tsvector('simple', 'Черги') @@ to_tsquery('simple', 'черги');   -- false

Пошук «тихо» не знаходить слів з великої літери. Рішення - створювати базу з UTF-8 локаллю (uk_UA.UTF-8, ICU чи вбудований провайдер C.UTF-8 з PostgreSQL 17).

Реалістичний вибір: для серйозного пошуку українською часто беруть окремий рушій (Meilisearch, Typesense, Elasticsearch з українським аналізатором) - там морфологія, опечатки й ранжування вже налаштовані. PostgreSQL лишається для фільтрів і простого пошуку.

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

Є три способи зберігати пошуковий вектор, і в кожного свої компроміси.

1. Індекс за виразом - нічого не зберігати:

CREATE INDEX posts_fts_idx ON posts USING gin (to_tsvector('english', title || ' ' || body));

SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'queue');
  • Не займає місця в таблиці.
  • Вираз у запиті має дослівно збігатися з виразом в індексі (разом з конфігурацією - функція з одним аргументом нестабільна й в індексі не допускається).
  • ts_rank і перевірки збігу обчислюють вектор заново для кожного рядка - повільно.

2. Згенерована колонка - найпростіший надійний варіант:

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);
  • Завжди актуальна, без коду в застосунку.
  • Вектор обчислено заздалегідь - ранжування швидке.
  • Обмеження: лише колонки того самого рядка.

3. Тригер - коли вектор має включати дані з інших таблиць: назви тегів, ім'я автора, назву категорії.

CREATE FUNCTION posts_search_update() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.search := setweight(to_tsvector('english', NEW.title), 'A')
               || setweight(to_tsvector('english',
                      coalesce((SELECT string_agg(name, ' ') FROM tags t
                                JOIN post_tag pt ON pt.tag_id = t.id
                                WHERE pt.post_id = NEW.id), '')), 'B');
    RETURN NEW;
END $$;

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

coalesce обов'язковий: to_tsvector(NULL) дає NULL, а NULL || вектор - теж NULL. Один порожній опис - і весь документ випадає з пошуку.

Рекомендація: згенерована колонка за замовчуванням; тригер чи перерахування в черзі - коли потрібні дані з інших таблиць; індекс за виразом - для простих випадків без ранжування.

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

pg_trgm розбиває рядок на триграми - послідовності з трьох символів: «laravel» → l, la, lar, ara, rav, ave, vel, el . Схожість двох рядків - частка спільних триграм.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT similarity('laravel', 'laravle');     -- ~0.45
SELECT 'laravle' % 'laravel';                -- true, якщо схожість вища за поріг (0.3)
SELECT word_similarity('ларав', 'Laravel Україна');

Що вміє:

  • Пошук з опечатками: знайти «Хмельницький» за запитом «Хмельницкий».
  • LIKE '%підрядок%' та ILIKE з індексом - головна практична причина ставити розширення.
  • Найближчі за схожістю: ORDER BY name <-> 'запит' LIMIT 10 з GiST-індексом.
CREATE INDEX companies_name_trgm ON companies USING gin (name gin_trgm_ops);

SELECT * FROM companies WHERE name ILIKE '%софт%';       -- тепер з індексом
SELECT * FROM companies WHERE name % 'Епам' ORDER BY similarity(name, 'Епам') DESC;

GIN проти GiST для триграм: GIN швидший на пошук і LIKE, GiST підтримує сортування за відстанню (<->) для «найближчих».

Обмеження:

  • Короткі запити (1-2 символи) майже не мають триграм - індекс не допомагає, а результатів забагато.
  • Розмір індексу великий - кілька триграм на кожен символ тексту. Для довгих текстів (статті) підходить погано; для назв, імен, адрес - добре.
  • Поріг схожості (pg_trgm.similarity_threshold) треба підбирати під дані: занизький дає сміття, зависокий пропускає опечатки.
  • Регістр і локаль: порівняння без урахування регістру залежить від LC_CTYPE бази - з локаллю C кирилиця не зводиться до малих літер.

Типове поєднання: повнотекстовий пошук для документів + pg_trgm для назв, автодоповнення й підказок «можливо, ви мали на увазі».

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

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

Рівні
Junior 30 Middle 47 Senior 39

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