Postgre .SQL: таҷрибаи пешқадам ва индексатсия
(Қисм: Технология ва инфрасохтор)
Хулосаи мухтасар
Postgre-SQL "ядрои ҳақиқат" барои пул, KYC ва сабтҳои аз ҷиҳати ҳуқуқӣ муҳим дар IGaming мебошад. Он кафолатҳои ACID, SQL-и пурқувват ва васеъшавиро таъмин мекунад. Барои тоб овардан ба қуллаҳои мусобиқаҳо ва вебҳукҳои PSP, онҳо муҳиманд: нақшаи салоҳиятдор, нишондиҳандаҳо, тақсимот, худкори вакуум, танзими WAL ва мушоҳидаҳо. Дар зер созандаи амалия ва қолабҳо оварда шудааст.
1) Меъморӣ ва SLO
Нақши Postgre-SQL: пешво барои навиштан + нусхаҳо барои хондан; барои экранҳои гарм - кэш/пешгӯӣ (Редис/назари моддӣ).
Намунаҳои SLO: ҳамёни p99 'INSERT/UPDATE' ≤ 25-40 мс; p99 тавозуни хониш ≤ 10-15 мс; реплика ақибмонӣ ≤ 2-5 с; мавҷудияти ≥ 99. 9%.
Сиёсати пас аз навиштан: экранҳои корбар пас аз муомилот аз роҳбар хонда мешаванд ё интизори ақибмонии такрорӣ мебошанд.
2) Тарҳи схемавӣ
Нормализатсияи аслии пул (ҳамёнҳо, дафтарча) + ғайримуқаррарӣ барои хондан (CQRS/пешгӯиҳо).
Маҳдудиятҳои қатъӣ: 'NOT NULL', 'CHECK', 'UNIQUE', FK бо ҳадафи 'ON DELETE/UPDATE' (Маҳдудият/SET NULL/NO ACTION).
Версияи схема: муҳоҷирати боло/поён, парчамҳои махсус; нагузоред, ки тағйири ному насаби гарм.
Идентификаторҳо: 'BIGINT' + пайдарпаӣ (ё ULID/UUIDv7 барои тақсимот). Барои замимаҳои гарм - пайдарпаӣ дар фазои алоҳида/ҳаҷми WAL.
3) Индексатсия: чӣ, дар куҷо ва чӣ тавр
3. 1 B-дарахт (пешфарз)
Вақте ки: бозии дақиқ, диапазонҳо, навъҳо, 'ҲАМРОҲ' аз ҷониби FK.
Намунаҳо: 'Дар куҷо player_id =?', 'Фармоиш аз ҷониби created_at DESC LIMIT 100'.
- Индексҳои бисёрсатрӣ - Ба тартиби шароит мувофиқат кунед.
- Пӯшиш: 'INBER (...)' танҳо сканкунии индекси для.
- Индекси инфиродӣ аз рӯи 'Фармоиш 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 ГСТ (гео/диапазон/имзо)
Кай: геолокатсия (IP-geo, radii), фосилаҳо, ҳамсояи наздиктарин.
Камтар дар ядрои пул истифода мешавад, ки барои маҳдудиятҳои гео/бозии масъул муфид аст.
3. 4 BRIN (ҷадвалҳои калони "замимаҳо" дар вақташ)
Кай: Миллиардҳо хатҳо, таносуби табиӣ бо мурури замон (гузоришҳои букмекерӣ/рӯйдодҳо).
Тарафдор: андозаи хурд, хидмати арзон.
Омӯз: Интихоби дағалона → бо тақсимот якҷоя карда шавад.
sql
CREATE INDEX idx_bets_brin ON bets USING BRIN (created_at) WITH (pages_per_range = 64);3. 5 Ҳаш
Хеле кам лозим аст: баробарӣ аз ҷониби як сутун дар сурати набудани диапазон; бештар B-Tree кофӣ аст.
3. 6 Қисман ва ифодаҳо
Индекси қисман: зербахшҳои гармро суръат мебахшад (фаъол, 'status =' интизорӣ ').
Индекси ифода: калиди пешакӣ ('поёнтар (почтаи электронӣ)', '(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 арзиш) - қисман иҷро кунед.
4) Тақсимот
Чаро: варамро кам кунед, VACUUM/сканро суръат бахшед, нигоҳдорӣ/бойгониро осон кунед.
Схемаҳо: 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: Истифода баред 'ДАР НИЗОЪ... Бо мантиқи бемаънӣ навсозӣ кунед.
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) Амалиётҳо, қуфлҳо ва рақобат
Сатҳи изолятсия: 'ХОНЕД' барои аксари роҳҳо; 'Хониши такрорӣ '/' SERIALIZABLE' нуқта (гузоришҳо, партияҳои офлайнӣ). Онҳоро муддати дароз нигоҳ надоред.
Намудҳои қуфлҳо: сатҳи сатр (ноумедӣ 'FOR UPDATE'), сатҳи ҷадвал (DDL), қуфлҳои машваратӣ барои мутексҳои тақсимшуда.
Маҳдудиятҳо: транзаксияҳои кӯтоҳ, тартиби ягонаи навсозии субъектҳо, таъхирҳо ('lock _ timeout', 'article _ timeout').
Навбати вазифа: 'SKIP LOCKED' барои ҳавзи тақсимшудаи корӣ.
sql
-- batch processor removes work without racing
SELECT id FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 100;7) Вакуумии худкор, омор ва гулӯ
ВАКУУМ/АНАЛИЗЕ: омори ҷориро нигоҳ доред ('пешфарз _ омор _ ҳадаф'), AV-ро дар ҷадвалҳои гарм танзим кунед (ҳадди триггер дар зер).
Эҳтиёт бошед, ки (синну сол (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 ва нуқтаҳои гузаргоҳ
NVM je барои WAL, ҳаҷми ягона; 'wal _ фишурдасозӣ = дар'.
'checkpoint _ timeout' 10-15 дақиқа, 'max _ wal _ size' бояд тағир дода шавад, 'нуқтаи гузариш _ анҷом _ ҳадаф ≈ 0. 9`.
Барои гузоштани қуллаҳо - замимаҳо ва супоришҳои гурӯҳӣ.
9) Такрори ва таҳаммулпазирии гуноҳ
Нусхаи пешво: синхронӣ ба наздиктарин (нимсохт) барои RPO ≈ 0 -30c; асинхронӣ барои хондан/таҳлил.
Пешбурд ва тавораҳо: Менеҷери Patroni/реплика; истисно "пешвои дугона".
Интизории гарм: 'hot _ standby _ fidedback' бодиққат (афзоиши варам), беҳтар - муомилоти дарозро дар реплика.
10) Нусхабардорӣ ва PITR
Нусхаи нусхабардории пурра + афзоишёбанда, нусхаҳои берунӣ; 'archive _ фармон' барои WAL.
PITR: барқароркуниро то нуқтаи вақт дар деворҳо санҷед; RPO/RTO-ро танзим кунед (ҳамёнҳо - дақиқаҳо, гузоришҳо - даҳҳо дақиқа).
Машқҳои DR (рӯзи бозӣ): санҷиши мунтазами барқароршавӣ.
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) Матни пурра ва ҷустуҷӯи номуайян
FTS-и сохташуда: 'tsvector' + GIN; барои чопҳо - 'pg _ trgm'.
Барои ҷустуҷӯи душвор аз рӯи гузоришҳо/бозиҳо - ба системаи ҷустуҷӯӣ бароед (ES/OpEN Search) ва пайванд/метамаълумотро дар PG нигоҳ доред.
13) Мушоҳида ва профил
pg_stat_statements: дархостҳои оҳиста/зуд-зуд, нормализатсия.
Шарҳ (ANALYZE, BUFFERS): нақшаҳоро хонед, дар роҳҳои гарм сканро ҷустуҷӯ кунед.
Нишондиҳандаҳо: TPS, p95/p99, нуқтаҳои гузаргоҳ, 'replication _ lag', монеаҳо, гулӯ, ҳалқаҳои AV, кэш-хит ≥ 95%.
Огоҳиҳо: афзоиши ақибмонӣ, "бекорӣ дар муомилот", сканкунии ғайричашмдошт, тӯфони WAL.
14) Ҳавзи пайвастшавӣ ва дархостҳои омодашуда
Puller (PGBouncer) дар ҳолати 'транзаксия' барои трафики веб; 'session' - барои курсорҳои дароз/дафтари бозгашт.
Изҳороти омодашуда/параметризатсия → таҳлили камтар, нақшаҳои устувор.
Маҳдудияти шумораи максималии пуштҳо ('max _ connections' паст; ҳавз бо клапан гирифта мешавад).
15) Бехатарӣ ва риояи
TLS дар транзит, рамзгузории диск, KMS/калидҳои хориҷӣ.
RBAC: ҳуқуқҳои ҳадди аққал, ҷудо кардани нақшҳои хондан/навиштан/маъмурӣ.
RLS (Row-Level Security) барои сенарияҳои бисёр иҷорагир.
PII: ниқоб/бегона, мӯҳлати нигоҳдорӣ.
Аудит: триггерҳои 'pgaudit '/аудит дар ҷадвалҳои интиқодӣ (ҳамён/дафтар).
16) Қолабҳои маъмулӣ барои IGaming
16. 1 Ҳамён ва дафтар (мувофиқати қатъӣ)
Индексы: 'ҳамён (player_id ПК)', 'китобча (player_id, ts DESC)' + 'INBERD (delta_cents, сабаб)'.
Муомилот тавозунро нав мекунад ва ба дафтар менависад; такрори нимҳарбӣ; кэш ҳамчун дурнамо танҳо.
16. 2 Таърихи гарав (TPS баланд, плеер/хониши вақт)
Тақсимот аз рӯи рӯз/ҳафта, BRIN аз рӯи вақт + B-Tree '(player_id, created_at DESC)'.
Нигоҳдорӣ тавассути 'DETACH PARTITION' → бойгонӣ ба OLAP.
16. 3 Webhooks PSP (бурстами, ретрай)
Навбати рӯйдодҳои "хом" (танҳо замима), қисмҳои вақт, индекси қисман дар 'status =' интизорӣ. "
Idempotency аз ҷониби 'idempotency _ key '/' psp _ tx' (UNIQUE).
17) Рӯйхати назорати амалисозӣ
1. SLO-ро содир кунед ва сиёсати пас аз хондан нависед.
2. Индексҳои калидӣ барои дархостҳои воқеӣ (профили дархост).
3. Қисмбандиро дар ҷое, ки шакли нигоҳдорӣ/замимаҳо мавҷуданд, дохил кунед.
4. Дар мизҳои гарм AV/ANALIZE насб кунед, ба гулӯ/парпеч нигоҳ кунед.
5. Хотира, WAL ва нуқтаҳои гузаргоҳро барои тирезаҳои қуллаҳо танзим кунед.
6. Ҳавзи пайвастшавӣ ва 'pg _ stat _ comments' -ро гузоред; огоҳӣ гиред.
7. Такрор + DR + PITR - лозим аст; машқҳоро мегузаронад.
8. Маҳдуд кардани JSONB ва GIN танҳо ба роҳҳои зарурӣ; индексҳои қисман истифода мебаранд.
9. Давомнокии транзаксияро кам кунед, 'SKIP LOCKED' -ро барои навбати корӣ истифода баред.
10. Модели аудит/PII/рамзгузорӣ/нақш - пеш аз оғози пардохт.
18) Антипаттернҳо
Як шохиси "универсалӣ" дар ҳама чиз ва таҳлили дархостҳо нест.
Амалиётҳои дарозмуддати "овезон" ('бекорӣ дар муомилот') → афзоиши блок/варам.
Ба нусхаҳои пас аз хондан бидуни ақиб такя кунед.
Ҳама чизро дар JSONB "танҳо дар ҳолати" нигоҳ доред ва тамоми ҳуҷҷатро бо як GIN индексатсия кунед.
Назорати сифр VACUUM/ANALIZE ва ҳеҷ гуна мониторинги қафо/гузаргоҳҳо.
Муҳоҷирати оммавӣ/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');Хулоса
Postgre-SQL қодир аст, ки "пул ва ҳақиқат" -ро дар платформаи IGaming бо афзоиши хатти сарборӣ кашад - агар индексатсияҳо, ҳизбҳо, худкори вакуум, WAL/хотира, реплика/DR ва мушоҳидаҳо интизом бошанд. Аз профили дархостҳо ва SLO оғоз кунед, индексатсияҳоро барои намунаҳои воқеӣ эҷод кунед, ҷадвалҳои гармро ҷудо кунед, гигиенаи қатъии амалиётиро фаъол созед - ва пойгоҳ эҳтимолан мусобиқаҳо ва пардохтҳои баландтаринро нигоҳ медорад.