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, постройте индексы под реальные паттерны, изолируйте горячие таблицы, включите строгую эксплуатационную гигиену — и база будет предсказуемо держать турниры и пиковые выплаты.