PostgreSQL: best practices va indekslash
(Bo’lim: Texnologiyalar va infratuzilma)
Qisqacha xulosa
PostgreSQL - pul, KYC va iGaming’dagi yuridik ahamiyatga ega yozuvlar uchun «haqiqat yadrosi». U ACID kafolatlari, kuchli SQL va kengaytiruvchanlikni beradi. Turnirlar va PSP vebxuklarining eng yuqori cho’qqilariga bardosh berish uchun savodli sxema, indekslar, partiyalashtirish, avtovakuum, WAL tyuningi va kuzatish juda muhim. Quyida - amaliyot va shablon konstruktori.
1) Arxitektura va SLO
PostgreSQL roli: yozish uchun yetakchi + oʻqish uchun nusxalar; issiq ekranlar uchun - kesh/proyeksiya (Redis/materiallashtirilgan tomoshalar).
SLO misollar: p99’INSERT/UPDATE’hamyoni ≤ 25-40 ms; p99 balansni o’qish ≤ 10-15 ms; lag replik ≤ 2-5 s; foydalanish imkoniyati ≥ 99. 9%.
read-after-write siyosati: foydalanuvchi ekranlari tranzaksiyadan so’ng etakchidan o’qiladi yoki replikatsiya kechikishini kutadi.
2) Sxemalarni loyihalashtirish
Pul yadrosini normallashtirish (hamyonlar, ledger) + o’qish uchun denormallashtirish (CQRS/proyeksiyalar).
Qat’iy cheklovlar:’NOT NULL’,’CHECK’,’UNIQUE’, FK maqsadli’ON DELETE/UPDATE’(RESTRICT/SET NULL/NO ACTION).
Sxemani versionlash: up/down migratsiyasi, feature-bayroqlar; issiq vaqtda nomni oʻzgartirishdan qoching.
Identifikatorlar:’BIGINT’+ ketma-ketlik (yoki taqsimlanish uchun ULID/UUIDv7). Issiq qo’shimchalar uchun - alohida tablespace/WAL-jilddagi ketma-ketliklar.
3) Indeksatsiya: nima, qayerda va qanday
3. 1 B-Tree (defolt)
Qachon: aniq muvofiqliklar, diapazonlar, saralash, FK bo’yicha’JOIN’.
Patternlar:’WHERE player_id =?’,’ORDER BY created_at DESC LIMIT 100’.
- Ko’p ustunli indekslar - shartlar tartibiga mos keling.
- Covering: `INCLUDE (...)` для index-only scan.
- Alohida indeks’ORDER BY... DESC’ning «lentalari».
sql
CREATE INDEX idx_tx_player_created_desc
ON tx (player_id, created_at DESC) INCLUDE (amount_cents, status);3. 2 GIN (JSONB, massivlar, to’liq matn)
Qachon: JSONB - yo’l kalitlari, tag massivlari, FTS.
Variantlar:’gin _ trgm _ ops’,’jsonb _ path _ ops ’/’ jsonb _ ops’.
Amaliyot: qidiruv maydonlarini qat’iy cheklang va kardinallik haqida o’ylang.
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 (geo/diapazonlar/imzolar)
Qachon: geolokatsiya (IP-geo, radiuslar), intervallar, nearest-neighbor.
Pul yadrosida kamroq foydalaniladi, geo-cheklovlar/mas’uliyatli o’yin uchun foydalidir.
3. 4 BRIN (vaqt bo’yicha katta appendiks)
Qachon: milliardlab satrlar, vaqt boʻyicha tabiiy korrelyatsiya (stavkalar/voqealar jurnallari).
Afzalliklari: kichik o’lcham, arzon xizmat.
Kamchiliklar: qo’pol selektivlik → partiyalashtirish bilan birlashtirish.
sql
CREATE INDEX idx_bets_brin ON bets USING BRIN (created_at) WITH (pages_per_range = 64);3. 5 Hash
Kamdan-kam hollarda: diapazonlar boʻlmaganda bir ustundan tenglik; koʻpincha B-Tree yetarli.
3. 6. Qisman va iboralar
Partial index: issiq kichik toʻplamlarni tezlashtiradi (aktiv,’status =’pending’).
Expression index: kalit (’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 Indekslarga qarshi
«Hamma narsa uchun indeks»: ortiqcha indekslar yozuvni sekinlashtiradi va VACUUM.
Takrorlanuvchi indekslar (xuddi shu ustunlar to’plami/tartibi).
Kardinalligi juda past bo’lgan ustunga indeks (masalan,’status’2 qiymatli) - qisman qiling.
4) Partiyalashtirish
Nima uchun: bloat qisqartirish, VACUUM/skanerlarni tezlashtirish, retention/arxivni osonlashtirish.
Sxemalar: stavkalar daftarlari uchun sana (kun/hafta) bo’yicha RANGE; katta foydalanuvchi jadvallari uchun’player _ id’uchun HASH; kombinatsiyalangan holda.
Rotatsiya amaliyoti: kelgusi partiyalarni oldindan yaratish, «ATTACH PARTITION», eskilar arxivi - «DETACH» + koʻchirish.
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) Minimal parchalanishga ega bo’lgan qo’shimchalar/yangilanishlar
HOT-yangilanishlar:’fillfactor’ni (masalan, 90) sahifadagi boʻsh joy uchun issiq jadvallarda saqlang.
TOAST: katta JSONB/matnlar - ixchamlashtirishni kuzating; katta maydonlarni alohida jadvalda saqlang.
UPSERT:’ON CONFLICT’dan foydalaning... DO UPDATE’idempotent mantiqqa ega.
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) Tranzaksiyalar, blokirovkalar va raqobat
Izolyatsiya darajalari: aksariyat yo’llar uchun’READ COMMITTED’;’REPEATABLE READ ’/’ SERIALIZABLE’nuqtali (hisobotlar, oflayn-batchi). Ularni uzoq tutmang.
Bloklash turlari: row-level (pessimizm’FOR UPDATE’), table-level (DDL), taqsimlangan myutekslar uchun advisory locks.
Deadlocks: qisqa tranzaksiyalar, mavjudotlarni yangilashning yagona tartibi, taymautlar (’lock _ timeout’,’statement _ timeout’).
Vazifa navbatlari:’SKIP LOCKED’taqsimlangan vork-hovuz uchun.
sql
-- batch processor removes work without racing
SELECT id FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 100;7) Avtovokum, statistika va bloat
VACUUM/ANALYZE: dolzarb statistikani (’default _ statistics _ target’) saqlang, issiq jadvallarda AVni sozlang (ishga tushirish chegarasi quyida).
wraparound (age (txid) <2 mlrd),’vacuum _ freeze _ min _ age’.
bloat-nazorat: og’ir indekslarda cho’qqidan tashqarida muntazam reindex/CLUSTER; partizanlashtirish muammo ko’lamini pasaytiradi.
sql
ALTER TABLE tx SET (
autovacuum_vacuum_scale_factor = 0. 05,
autovacuum_analyze_scale_factor = 0. 02,
autovacuum_vacuum_cost_limit = 4000
);8) Xotira, chekpindlar va WAL
Xotira
’shared _ buffers’: 20-25% RAM (profilga bogʻliq).
’work _ mem’: amalda! Hisobot rollari uchun konservativ (masalan, 8-64MB) va nuqta darajasini oshiring.
’maintenance _ work _ mem’: katta indekslar/tiklash (vazifa boʻyicha 512MB-2GB).
WAL va chekpindlar
WAL uchun NVMe, alohida jild;’wal _ compression = on’.
’checkpoint _ timeout’ 10-15 min.,’max _ wal _ size’oʻzgarishlar hajmi ostida,’checkpoint _ completion _ target ≈ 0. 9`.
Qo’shimchalarning cho’qqilari uchun - batch qo’shimchalar va guruh kommitlari.
9) Replikatsiya va nosozlikka chidamlilik
Yetakchi replikalar: RPO ≈ 0-30s uchun eng yaqin (semi-sync) ga sinxron; o’qish/tahlillar uchun asinxron.
Promoushen va fencing: Patroni/replika-menejer; chiqarib tashlansin.
Hot standby:’hot _ standby _ feedback’ehtiyotkorlik bilan (bloat o’sishi), yaxshiroq - replikalarda uzoq tranzaksiyalarni singdiring.
10) Bekaplar va PITR
To’liq + inkremental bekap, ofsayt nusxalari; WAL uchun’archive _ command’.
PITR: stendlardagi vaqt nuqtasiga qadar tiklanishni tekshiring; RPO/RTOni tartibga soling (hamyonlar - daqiqalar, loglar - o’nlab daqiqalar).
DR (game day) mashqlari: qayta tiklashni muntazam tekshirish.
11) JSONB va «egiluvchan sxema» modeli
JSONB - kamdan-kam o’qiladigan/o’zgaruvchan atributlar uchun juda yaxshi (KYC-meta ma’lumotlar, PSP parametrlari).
Relatsiya ustunlaridagi majburiy maydonlarni qat’iy tasdiqlang; JSONB - nuanslarning «dumi» uchun.
Indeksatsiya: foydalaniladigan yoʻllar boʻyicha nuqtaviy GIN; «hamma narsaga bitta gigant GIN» dan qoching.
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) To’liq matn va fuzzy-qidiruv
Oʻrnatilgan FTS:’tsvector’+ GIN; xatolar uchun -’pg _ trgm’.
Og’ir loglar/o’yinlar bo’yicha qidirish uchun - qidiruv tizimiga (ES/OpenSearch) olib kirish, PG’da esa havolani/meta ma’lumotlarni saqlash.
13) Kuzatish va profillash
pg_stat_statements: top sekin/tez-tez so’rovlar, normallashtirish.
EXPLAIN (ANALYZE, BUFFERS): rejalarni o’qing, issiq yo’llarda seq scan qidiring.
Metriklar: TPS, p95/p99, chekpindlar,’replication _ lag’, deadlocks, bloat, AV-tsikllar, cache-hit ≥ 95%.
Alertlar: laglarning o’sishi, «idle in transaction», kutilmagan seq scan, WAL-bo’ron.
14) Ulanishlar puli va tayyorlangan so’rovlar
Puller (PgBouncer)’transaction’rejimida veb-trafik uchun;’session’- uzoq kursorlar/bek-ofis uchun.
Prepared statements/parametrlash → kamroq parsing, barqaror rejalar.
Bekendlarning maksimal sonini cheklang (’max _ connections’past; ).
15) Xavfsizlik va komplayens
Tranzitdagi TLS, disklarni shifrlash, KMS/tashqi kalitlar.
RBAC: minimal huquqlar, rollarni ajratish o’qish/yozish/admin.
Ko’p tenant ssenariylar uchun RLS (Row-Level Security).
PII: niqoblash/taxalluslashtirish, saqlash muddati.
Audit:’pgaudit ’/tanqidiy jadvallardagi audit triggerlari (hamyon/ledger).
16) iGaming uchun namunaviy namunalar
16. 1 Hamyon va ledger (qat’iy muvofiqlik)
Индексы: `wallet(player_id PK)`, `ledger(player_id, ts DESC)` + `INCLUDE (delta_cents, reason)`.
Tranzaksiya balansni yangilaydi va ledger-ga yozadi; semi-sync replikatsiyasi; kesh faqat proyeksiya sifatida.
16. 2 Stavkalar tarixi (yuqori TPS, o’yinchi/vaqt bo’yicha o’qish)
Kun/hafta bo’yicha partiyalashtirish, vaqt bo’yicha BRIN + B-Tree’(player_id, created_at DESC)’.
’DETACH PARTITION’ orqali Retenshn → OLAP arxivlash.
16. 3 PSP vebxuklari (burstami, retrai)
«Xom» voqealar navbati (append-only), vaqt bo’yicha partiyalar, qisman indeks’status =’pending’bo’yicha.
Idempotentlik’idempotency _ key ’/’ psp _ tx’(UNIQUE).
17) Joriy etish chek-varaqasi
1. SLO va read-after-write siyosatini tuzating.
2. Haqiqiy soʻrovlar uchun asosiy indekslarni loyihalashtiring.
3. Retenshn/append-patternlar mavjud bo’lgan joyda partiyalashtirishni yoqing.
4. Issiq jadvallarda AV/ANALYZE moslamalarini oʻrnating, bloat/wraparound.
5. Xotira, WAL va chekpindlarni eng yuqori oynalar ostida sozlang.
6. Ulanish pulini & pg _ stat _ statements’; alerta qiling.
7. Replikatsiya + DR + PITR - majburiy; mashqlar o’tkazing.
8. JSONB va GINni faqat zarur yo’llar bilan cheklang; partial indexesdan foydalaning.
9. Bitimlarning davomiyligini minimallashtiring va’SKIP LOCKED’dan foydalaning.
10. Audit/PII/shifrlash/rol modeli - to’lovlar boshlangunga qadar.
18) Antipatternlar
Hamma narsaga bitta «universal» indeks va so’rovlar tahlili yo’q.
Uzoq «osilgan» tranzaksiyalar (’idle in transaction’) → blokirovka/bloat o’sishi.
Lag’dan tashqari read-after-write uchun replikalarga tayanish.
JSONB’da hamma narsani saqlash va butun hujjatni bitta GIN bilan indekslash.
VACUUM/ANALYZE nol nazorati va lag/chekpint monitoringi yo’qligi.
Eng yuqori soatlarda ommaviy migratsiya/DDL.
19) Foydali snippetlar
Soʻrov va bufer rejasi
sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT amount_cents
FROM ledger
WHERE player_id = $1
ORDER BY ts DESC
LIMIT 100;«Ogʻir» soʻrovlarni profillash
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;Partiyalarni rotatsiya qilish (g’oya)
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');Yakunlar
PostgreSQL yuklamaning chiziqli o’sishi bilan iGaming platformasida «pul va haqiqat» ni tortishga qodir - agar indeks, partiya, avtovokum, WAL/xotira, replika/DR va kuzatish qobiliyati intizomli bo’lsa. So’rovlar profilidan va SLOdan boshlang, haqiqiy namunalar uchun indekslar tuzing, issiq jadvallarni izolyatsiya qiling, qat’iy foydalanish gigiyenasini yoqing - va baza turnirlar va eng yuqori to’lovlarni oldindan biladi.