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

За замовчуванням 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