PostgreSQL: best practices та індексація
(Розділ: Технології та Інфраструктура)
Коротке резюме
PostgreSQL - «ядро правди» для грошей, KYC і юридично значущих записів в iGaming. Він дає ACID-гарантії, потужний SQL і розширюваність. Щоб витримувати піки турнірів і PSP-вебхуків, критичні: грамотна схема, індекси, партіонування, автовакуум, тюнінг WAL і спостережуваність. Нижче - конструктор практик і шаблонів.
1) Архітектура і SLO
Роль PostgreSQL: лідер на запис + репліки на читання; для гарячих екранів - кеш/проекції (Redis/матеріалізовані уявлення).
SLO приклади: p99'INSERT/UPDATE'гаманця ≤ 25-40 мс; p99 читання балансу ≤ 10-15 мс; лаг реплік ≤ 2-5 с; доступність ≥ 99. 9%.
Політика read-after-write: користувальницькі екрани після транзакції читають з лідера або чекають реплікаційний лаг.
2) Проектування схем
Нормалізація ядра грошей (гаманці, ledger) + денормалізація для читання (CQRS/проекції).
Суворі обмеження: 'NOT NULL','CHECK','UNIQUE', FK з цілеспрямованим'ON DELETE/UPDATE'( RESTRICT/SET NULL/NO ACTION).
Версіонування схеми: міграції up/down, feature-прапори; уникайте ламаючих перейменувань в гарячий час.
Ідентифікатори: 'BIGINT'+ послідовності (або ULID/UUIDv7 для розподіленості). Для гарячих вставок - послідовності в окремому tablespace/WAL-томі.
3) Індексація: що, де і як
3. 1 B-Tree (дефолт)
Коли: точні відповідності, діапазони, сортування,'JOIN'по FK.
Патерни: `WHERE player_id =?`, `ORDER BY created_at DESC LIMIT 100`.
- Багатоколонкові індекси - відповідайте порядку умов.
- Covering: `INCLUDE (...)` для index-only scan.
- Окремий індекс під'ORDER BY... DESC'на «стрічках».
sql
CREATE INDEX idx_tx_player_created_desc
ON tx (player_id, created_at DESC) INCLUDE (amount_cents, status);3. 2 GIN (JSONB, масиви, повнотекст)
Коли: JSONB-пошук по ключах/шляху, масиви тегів, FTS.
Варіанти: 'gin _ trgm _ ops'для триграм,'jsonb _ path _ ops '/' jsonb _ ops'.
Практика: суворо обмежуйте поля пошуку і думайте про кардинальність.
sql
CREATE INDEX idx_profile_jsonb_gin
ON player_profile USING GIN (data jsonb_path_ops);
-- Search by email/name with typos
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_player_trgm ON player USING GIN (email gin_trgm_ops);3. 3 GiST (гео/діапазони/підписи)
Коли: геолокація (IP-гео, радіуси), інтервали, nearest-neighbor.
Менше використовується в ядрі грошей, корисно для гео-обмежень/відповідальної гри.
3. 4 BRIN (великі «апендикс» -таблиці за часом)
Коли: мільярди рядків, природна кореляція за часом (журнали ставок/подій).
Плюси: маленький розмір, дешеве обслуговування.
Мінуси: груба селективність → комбінувати з партіонуванням.
sql
CREATE INDEX idx_bets_brin ON bets USING BRIN (created_at) WITH (pages_per_range = 64);3. 5 Hash
Рідко потрібен: рівність по одному стовпцю при відсутності діапазонів; частіше достатньо B-Tree.
3. 6 Часткові та вирази
Partial index: прискорює гарячі під-множини (активні,'status ='pending ").
Expression index: передвирахування ключа ('lower (email)','( data->>'psp _ tx')').
sql
CREATE INDEX idx_withdraw_pending ON withdrawals (player_id)
WHERE status = 'pending';
CREATE INDEX idx_tx_psp_tx ON tx ((data->>'psp_tx'));3. 7 Антипатерни індексів
«Індекс на все»: надлишок індексів гальмує запис і VACUUM.
Дублюючі індекси (той же набір колонок/порядок).
Індекс на колонку з дуже низькою кардинальністю (наприклад, «status» з 2 значеннями) - робіть частковий.
4) Партіонування
Навіщо: зменшити bloat, прискорити VACUUM/скани, полегшити retention/архів.
Схеми: RANGE за датою (день/тиждень) для логів ставок; HASH по'player _ id'для великих призначених для користувача таблиць; комбіновано.
Практика ротації: заздалегідь створювати майбутні партії,'ATTACH PARTITION', архів старих -'DETACH'+ переміщення.
sql
CREATE TABLE bets (
bet_id BIGINT PRIMARY KEY,
player_id BIGINT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
amount_cents BIGINT NOT NULL
) PARTITION BY RANGE (created_at);
CREATE TABLE bets_2025_11_05 PARTITION OF bets
FOR VALUES FROM ('2025-11-05') TO ('2025-11-06');5) Вставки/оновлення з мінімальною фрагментацією
HOT-оновлення: тримайте'fillfactor'( наприклад, 90) на гарячих таблицях для вільного місця на сторінці.
TOAST: великі JSONB/тексти - стежте за компактацією; зберігайте громіздкі поля в окремій таблиці.
UPSERT: використовуйте'ON CONFLICT... DO UPDATE'з ідемпотентною логікою.
sql
INSERT INTO wallet (player_id, balance_cents, updated_at)
VALUES ($1, $2, now())
ON CONFLICT (player_id) DO UPDATE
SET balance_cents = wallet. balance_cents + EXCLUDED. balance_cents,
updated_at = now();6) Транзакції, блокування та конкуренція
Рівні ізоляції: 'READ COMMITTED'для більшості шляхів;'REPEATABLE READ '/' SERIALIZABLE'точково (звіти, офлайн-батчі). Не тримайте їх довго.
Види блокувань: row-level (песимізм'FOR UPDATE'), table-level (DDL), advisory locks для розподілених м'ютексів.
Deadlocks: короткі транзакції, єдиний порядок оновлення сутностей, таймаути ('lock _ timeout','statement _ timeout').
Черги завдань: 'SKIP LOCKED'для розподіленого ворк-пулу.
sql
-- batch processor removes work without racing
SELECT id FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 100;7) Автовакуум, статистика і bloat
VACUUM/ANALYZE: тримайте актуальну статистику ('default _ statistics _ target'), тюньте AV на гарячих таблицях (поріг спрацьовування нижче).
Слідкуйте за wraparound (age (txid) <2 млрд),'vacuum _ freeze _ min _ age'.
bloat-контроль: регулярні reindex/CLUSTER на важких індексах поза піками; партіонування знижує масштаб проблеми.
sql
ALTER TABLE tx SET (
autovacuum_vacuum_scale_factor = 0. 05,
autovacuum_analyze_scale_factor = 0. 02,
autovacuum_vacuum_cost_limit = 4000
);8) Пам'ять, чекпоінти і WAL
Пам'ять
`shared_buffers`: 20-25% RAM (залежить від профілю).
`work_mem`: за операцією! Налаштовуйте консервативно (наприклад, 8-64MB) і точково підвищуйте для звітних ролей.
`maintenance_work_mem`: великі індекси/відновлення (512MB-2GB за завданням).
WAL і чекпоінти
NVMe для WAL, окремий том;'wal _ compression = on'.
'checkpoint _ timeout'10-15 хв,'max _ wal _ size'під обсяг змін,'checkpoint _ completion _ target ≈ 0. 9`.
Для піків вставок - батч-вставки і групові коміти.
9) Реплікація і відмовостійкість
Лідер-репліки: синхронна до найближчої (semi-sync) для RPO≈0 -30с; асинхронні для читань/аналітики.
Промоушен і fencing: Patroni/репліка-менеджер; виключити «подвійного лідера».
Hot standby: 'hot _ standby _ feedback'обережно (зростання bloat), краще - приборкуйте довгі транзакції на репліках.
10) Бекапи і PITR
Повний + інкрементальний бекап, офсайт-копії;'archive _ command'для WAL.
PITR: перевіряйте відновлення до точки часу на стендах; регламентуйте RPO/RTO (гаманці - хвилини, логи - десятки хвилин).
Навчання DR (game day): регулярна перевірка відновлення.
11) JSONB і модель «гнучкої схеми»
JSONB - відмінно для рідко читаються/варіативних атрибутів (KYC-метадані, параметри PSP).
Строго валідуйте обов'язкові поля в реляційних колонках; JSONB - для «хвоста» нюансів.
Індексація: точково GIN по використовуваних шляхах; уникайте «одного гігантського GIN на все».
sql
-- partial GIN index only for documents with the desired key
CREATE INDEX idx_kyc_docnum ON kyc USING GIN ((data->'doc'->'number'))
WHERE data? 'doc';12) Повнотекст і fuzzy-пошук
Вбудований FTS: `tsvector` + GIN; для друкарських помилок -'pg _ trgm'.
Для важкого пошуку за логами/іграми - винесення в пошукову систему (ES/OpenSearch), а в PG зберігати посилання/метадані.
13) Спостережуваність і профілювання
pg_stat_statements: топ повільних/частих запитів, нормалізація.
EXPLAIN (ANALYZE, BUFFERS): читайте плани, шукайте seq scan на гарячих шляхах.
Метрики: TPS, p95/p99, чекпоінти,'replication _ lag', deadlocks, bloat, AV-цикли, cache-hit ≥ 95%.
Алерти: зростання лагів, «idle in transaction», несподівані seq scan, WAL-шторм.
14) Пул з'єднань і підготовлені запити
Пуллер (PgBouncer) в режимі'transaction'для веб-трафіку;'session'- для довгих курсорів/бек-офісу.
Prepared statements/параметризація → менше парсингу, стабільні плани.
Обмежуйте максимальне число бекендів ('max _ connections'низько; пул на себе бере вентиль).
15) Безпека та комплаєнс
TLS в транзиті, шифрування дисків, KMS/зовнішні ключі.
RBAC: мінімальні права, поділ ролей читання/запис/адмін.
RLS (Row-Level Security) для мульти-тенантних сценаріїв.
PII: маскування/псевдонімізація, термін зберігання.
Аудит: 'pgaudit '/тригери аудиту на критичних таблицях (гаманець/ledger).
16) Типові шаблони для iGaming
16. 1 Гаманець і ledger (сувора узгодженість)
Індекси: `wallet(player_id PK)`, `ledger(player_id, ts DESC)` + `INCLUDE (delta_cents, reason)`.
Транзакція оновлює баланс і пише в ledger; реплікація semi-sync; кеш тільки як проекція.
16. 2 Історія ставок (високий TPS, читання по гравцеві/часу)
Партіонування за днем/тижнем, BRIN за часом + B-Tree'( player_id, created_at DESC)'.
Ретеншн через'DETACH PARTITION'→ архівація в OLAP.
16. 3 Вебхукі PSP (бурстами, ретраї)
Черга "сирих" подій (append-only), партії за часом, частковий індекс за'status ='pending ".
Ідемпотентність по'idempotency _ key '/' psp _ tx'( UNIQUE).
17) Чек-лист впровадження
1. Зафіксуйте SLO і read-after-write політику.
2. Спроектуйте ключові індекси під реальні запити (профіль запитів).
3. Увімкніть партіонування там, де є ретеншн/апенд-патерни.
4. Налаштуйте AV/ANALYZE на гарячих таблицях, стежте за bloat/wraparound.
5. Тюньте пам'ять, WAL і чекпоінти під пікові вікна.
6. Поставте пул з'єднань і'pg _ stat _ statements'; Заведіть алерти.
7. Реплікація + DR + PITR - обов'язкові; проводьте навчання.
8. Обмежте JSONB і GIN лише необхідними шляхами; використовуйте partial indexes.
9. Мінімізуйте тривалість транзакцій, використовуйте'SKIP LOCKED'для ворк-черг.
10. Аудит/PII/шифрування/рольова модель - до запуску платежів.
18) Антипатерни
Один «універсальний» індекс на все і відсутність аналізу запитів.
Довгі «висячі» транзакції ('idle in transaction') → блокування/зростання bloat.
Покладатися на репліки для read-after-write без урахування лага.
Зберігати все в JSONB «про всяк випадок» і індексувати весь документ одним GIN.
Нульовий контроль VACUUM/ANALYZE та відсутність моніторингу лагів/чекпоінтів.
Масові міграції/DDL в годинник піків.
19) Корисні сніпети
План запиту та буфери
sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT amount_cents
FROM ledger
WHERE player_id = $1
ORDER BY ts DESC
LIMIT 100;Профілювання «важких» запитів
sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;Ротація партій (ідея)
sql
-- create a party for tomorrow
CREATE TABLE bets_2025_11_06 PARTITION OF bets
FOR VALUES FROM ('2025-11-06') TO ('2025-11-07');Підсумки
PostgreSQL здатний тягнути «гроші і правду» на iGaming-платформі при лінійному зростанні навантаження - якщо дисципліновані індекси, партії, автовакуум, WAL/пам'ять, репліка/DR і спостережуваність. Почніть з профілю запитів і SLO, побудуйте індекси під реальні патерни, ізолюйте гарячі таблиці, включіть сувору експлуатаційну гігієну - і база буде передбачувано тримати турніри і пікові виплати.