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 (дефолт)
Қашан: нақты сәйкестіктер, диапазондар, сұрыптаулар, FK бойынша 'JOIN'.
Паттерндер: '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 жазбасын тежейді.
Қайталанатын индекстер (сол бағандар жиынтығы/тәртібі).
Түбегейлілігі өте төмен бағанға индекс (мысалы, 2 мәнді 'status') - ішінара жасаңыз.
4) Партияландыру
Не үшін: bloat азайту, VACUUM/сканерлеуді жылдамдату, retention/мұрағатты жеңілдету.
Сызбалар: ставкалар логтары үшін күні (күні/аптасы) бойынша RANGE; Үлкен пайдаланушы кестелері үшін 'player _ id' бойынша HASH; аралас.
Ротация практикасы: болашақ партияларды алдын ала құру, «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: өзектi статистиканы ('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 және чек пункттері
WAL үшін NVMe, жеке том; 'wal _ compression = on'.
'checkpoint _ timeout' 10-15 мин, 'max _ wal _ size' өзгерістердің көлеміне, 'checkpoint _ completion _ target ≈ 0. 9`.
Ендірме шыңдары үшін - батч-ендірме және топтық коммиттер.
9) Репликация және істен шығу тұрақтылығы
Реплика көшбасшысы: RPO ≈ 0-30с үшін ең жақын (semi-sync) синхронды; оқылымдар/талдаулар үшін асинхронды.
Промоушен және fencing: Patroni/реплика-менеджер; «қосарланған көшбасшы» деген сөздер алып тасталсын.
Hot standby: 'hot _ standby _ feedback' абайлаңыз (өсу bloat), жақсы - репликалардағы ұзақ транзакцияларды қанағаттандырыңыз.
10) Бэкаптар және PITR
Толық + инкременталды бэкап, оффсайт-көшірмелер; WAL үшін 'archive _ command'.
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-дан бастаңыз, индекстерді нақты үлгілерге салыңыз, ыстық кестелерді оқшаулаңыз, қатаң пайдалану гигиенасын қосыңыз - және база турнирлер мен ең жоғары төлемдерді ұстайды.