PostgreSQL: best practices və indeksasiya

(Bölmə: Texnologiya və Infrastruktur)

Qısa xülasə

PostgreSQL - pul, KYC və iGaming-də hüquqi əhəmiyyətli qeydlər üçün «həqiqət nüvəsidir». Bu ACID zəmanət, güclü SQL və genişləndirilebilirlik verir. Turnirlərin və PSP vebhuklarının zirvələrinə tab gətirmək üçün: səriştəli sxem, indekslər, partiyalaşdırma, avtovakuum, WAL sazlama və müşahidə. Aşağıda - praktiklər və şablonlar dizayneri.

1) Memarlıq və SLO

PostgreSQL rolu: record lideri + oxu replikaları; isti ekranlar üçün - cache/proyeksiya (Redis/materiallaşdırılmış performans).
SLO nümunələri: p99 'INSERT/UPDATE' cüzdan ≤ 25-40ms; p99 balans oxu ≤ 10-15ms; lag replikaları ≤ 2-5 s; mövcudluğu ≥ 99. 9%.
read-after-write siyasəti: əməliyyatdan sonra istifadəçi ekranları liderdən oxunur və ya replikasiya gecikməsini gözləyir.

2) Sxemlərin dizaynı

Pul nüvəsinin normallaşdırılması (cüzdan, ledger) + oxumaq üçün denormallaşma (CQRS/proyeksiyalar).
Ciddi məhdudiyyətlər: 'NOT NULL', 'CHECK', 'UNIQUE', FK məqsədyönlü 'ON DELETE/UPDATE' (RESTRICT/SET NULL/NO ACTION).
Sxem versiyası: up/down miqrasiya, feature bayraqlar; isti vaxtlarda adını dəyişdirməkdən çəkinin.
Identifikatorlar: 'BIGINT' + ardıcıllığı (və ya paylanması üçün ULID/UUIDv7). İsti əlavələr üçün - ayrı bir tablespace/WAL cildindəki ardıcıllıqlar.

3) Indeksasiya: nə, harada və necə

3. 1 B-Tree (defolt)

Nə zaman: dəqiq uyğunluq, diapazonlar, sıralama, 'JOIN' FK.
Nümunələr: 'WHERE player_id =?', 'ORDER BY created_at DESC LIMIT 100'.

• Çox sütunlu indekslər - şərtlər qaydasına uyğun olun.
• Covering: `INCLUDE (...)` для index-only scan.
• Ayrı-ayrı indeks altında 'ORDER BY... DESC 'lent'.

sql
CREATE INDEX idx_tx_player_created_desc
ON tx (player_id, created_at DESC) INCLUDE (amount_cents, status);

3. 2 GIN (JSONB, massivlər, tam mətn)

Nə zaman: JSONB-axtarış açarları/yolları, tag massivləri, FTS.
Variantlar: triqramlar üçün 'gin _ trgm _ ops', 'jsonb _ path _ ops '/' jsonb _ ops'.
Təcrübə: axtarış sahələrini ciddi şəkildə məhdudlaşdırın və kardinallıq haqqında düşünün.

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/imzalar)

Nə zaman: geolokasiya (IP-geo, radiuslar), intervallar, nearest-neighbor.
Pul nüvəsində daha az istifadə olunur, geo məhdudiyyətlər/məsuliyyətli oyun üçün faydalıdır.

3. 4 BRIN (böyük «apendiks» -tables vaxt)

Nə zaman: milyardlarla sətir, təbii vaxt korrelyasiyası (bahis/hadisə jurnalları).
Üstünlüklər: kiçik ölçülü, ucuz xidmət.
Mənfi cəhətləri: kobud seçicilik → partiyalaşdırma ilə birləşir.

sql
CREATE INDEX idx_bets_brin ON bets USING BRIN (created_at) WITH (pages_per_range = 64);

3. 5 Hash

Nadir hallarda lazımdır: diapazonlar olmadıqda bir sütun bərabərliyi; Daha çox B-Tree kifayətdir.

3. 6 Qismən və ifadələr

Partial index: isti alt çoxluqları sürətləndirir (aktiv, 'status =' pending ').
Expression index: açar ('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 Antipattern indeksləri

«Hər şey üçün indeks»: həddindən artıq indekslər yazmağa və VACUUM-a mane olur.
Təkrarlanan indekslər (eyni sütun/sıra).
Çox aşağı kardinallığı olan sütun üçün indeks (məsələn, 2 qiymətli 'status') - qismən edin.

4) Partiyalaşdırma

Niyə: bloat azaltmaq, VACUUM/scan sürətləndirmək, retention/arxiv asanlaşdırmaq.
Sxemlər: RANGE tarixinə görə (gün/həftə); Böyük istifadəçi cədvəlləri üçün 'player _ id' HASH; birləşmiş.
Rotasiya təcrübəsi: əvvəlcədən gələcək partiyalar yaratmaq, 'ATTACH PARTITION', köhnə arxiv - 'DETACH' + hərəkət.

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) Minimum parçalanma ilə əlavələr/yeniləmələr

HOT-Updates: Səhifədə boş yer üçün isti cədvəllərdə 'fillfactor' (məsələn, 90) saxlayın.
TOAST: böyük JSONB/mətnlər - kompakt nəzarət; böyük sahələri ayrı bir cədvəldə saxlayın.
UPSERT: 'ON CONFLICT istifadə... DO UPDATE 'idempotent məntiqi ilə.

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) Əməliyyatlar, bloklama və rəqabət

İzolyasiya səviyyələri: əksər yollar üçün 'READ COMMITTED'; 'REPEATABLE READ '/' SERIALIZABLE' nöqtəli (hesabatlar, offline batches). Onları uzun müddət saxlamayın.
Bloklama növləri: row-level (pessimizm 'FOR UPDATE'), table-level (DDL), paylanmış myutekslər üçün advisory locks.
Deadlocks: qısa əməliyyatlar, varlıqların yenilənməsi üçün vahid prosedur, taymaut ('lock _ timeout', 'statement _ timeout').
Tapşırıq növbələri: paylanmış work hovuzu üçün 'SKIP LOCKED'.

sql
-- batch processor removes work without racing
SELECT id FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 100;

7) Avtovağlama, statistika və bloat

VACUUM/ANALYZE: Cari statistikanı ('default _ statistics _ target') saxlayın, isti cədvəllərdə AV-nı sürükləyin (aşağıda işlənmə həddi).
wraparound (age (txid) <2 milyard), 'vacuum _ freeze _ min _ age'.
bloat-control: ağır indekslərdə müntəzəm reindex/CLUSTER; partizanlaşdırma problemin miqyasını azaldır.

sql
ALTER TABLE tx SET (
autovacuum_vacuum_scale_factor = 0. 05,
autovacuum_analyze_scale_factor = 0. 02,
autovacuum_vacuum_cost_limit = 4000
);

8) Yaddaş, yoxlama nöqtələri və WAL

Yaddaş

'shared _ buffers': 20-25% RAM (profildən asılıdır).
'work _ mem': əməliyyat üçün! Mühafizəkar şəkildə (məsələn, 8-64MB) qurun və hesabat rolları üçün nöqtəli şəkildə artırın.
'maintenance _ work _ mem': böyük indekslər/bərpa (tapşırıq 512MB-2GB).

WAL və çek nöqtələri

WAL üçün NVMe, ayrı bir cild; 'wal _ compression = on'.
'checkpoint _ timeout' 10-15 min, 'max _ wal _ size' dəyişiklik həcmi altında, 'checkpoint _ completion _ target ≈ 0. 9`.
Əlavələrin zirvələri üçün - batch əlavələri və qrup kommitləri.

9) Replikasiya və uğursuzluğa davamlılıq

Lider replikalar: RPO ≈ 0-30s üçün ən yaxın (yarı-sync) üçün sinxron; oxu/analitika üçün asenxron.
Promosyon və fencing: Patroni/replika-menecer; «ikili lider» istisna.
Hot standby: 'hot _ standby _ feedback' diqqətlə (böyümə bloat), daha yaxşı - replikalarda uzun əməliyyatları təmin edin.

10) Backup və PITR

Tam + artım arxası, ofsayt surətləri; WAL üçün 'archive _ command'.
PITR: stendlərdə vaxt nöqtəsinə qədər bərpa edin; RPO/RTO (cüzdan - dəqiqə, log - on dəqiqə) tənzimləmək.
DR təlimləri (game day): müntəzəm bərpa yoxlaması.

11) JSONB və «çevik sxem» modeli

JSONB nadir oxunan/dəyişən atributlar (KYC metadata, PSP parametrləri) üçün əladır.
Relyasiya sütunlarında məcburi sahələri ciddi şəkildə təsdiqləyin; JSONB - nüansların «quyruğu» üçün.
Indeksasiya: istifadə olunan yollar üzrə GIN nöqtəsi; «hər şeyə bir nəhəng GIN» çəkinin.

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) Tam mətn və fuzzy-axtarış

Daxili FTS: 'tsvector' + GIN; 'pg _ trgm'.
Ağır axtarış/oyun üçün - axtarış sisteminə (ES/OpenSearch) çıxarılması və PG-də link/metadata saxlamaq.

13) Müşahidə və profil

pg_stat_statements: top yavaş/tez-tez sorğular, normallaşdırma.
EXPLAIN (ANALYZE, BUFFERS): planları oxuyun, isti yollarda seq scan axtarın.
Metriklər: TPS, p95/p99, kontrol nöqtələri, 'replication _ lag', deadlocks, bloat, AV dövrləri, cache-hit ≥ 95%.
Alertlər: laqların böyüməsi, «idle in transaction», gözlənilməz seq scan, WAL-fırtına.

14) Bağlantı hovuzu və hazırlanmış sorğular

Puller (PgBouncer) 'transaction' rejimində veb trafik üçün; 'session' - uzun kursorlar/arxa ofis üçün.
Prepared statements/parametrləşdirilməsi → daha az parsing, sabit planlar.
Maksimum bekend sayını məhdudlaşdırın ('max _ connections' aşağı; hovuz klapan götürür).

15) Təhlükəsizlik və uyğunluq

Tranzit TLS, disk şifrələmə, KMS/xarici açarlar.
RBAC: minimum hüquqlar, rolların bölünməsi oxu/yazma/admin.
Multi-tenant ssenarilər üçün RLS (Row-Level Security).
PII: maskalama/təxəllüs, saxlama müddəti.
Audit: 'pgaudit '/kritik cədvəllərdə audit tetikleyiciləri (cüzdan/ledger).

16) iGaming üçün standart şablonlar

16. 1 Cüzdan və ledger (ciddi uyğunluq)

Индексы: `wallet(player_id PK)`, `ledger(player_id, ts DESC)` + `INCLUDE (delta_cents, reason)`.
Əməliyyat balansı yeniləyir və ledger-də yazır; semi-sync replikasiyası; yalnız proyeksiya kimi cache.

16. 2 Bahis tarixi (yüksək TPS, oyunçu/vaxt oxu)

Gün/həftə partizanlaşdırma, BRIN vaxt + B-Tree '(player_id, created_at DESC)'.
'DETACH PARTITION' vasitəsilə Retenshn → OLAP arxivləşdirilməsi.

16. 3 PSP vebhukları (burstlar, retralar)

«Xam» hadisələrin növbəsi (append-only), vaxtına görə partiyalar, qismən indeks 'status =' pending '.
İdempotentlik 'idempotency _ key '/' psp _ tx' (UNIQUE).

17) Giriş çek siyahısı

1. SLO və read-after-write siyasətini düzəldin.
2. Real sorğular üçün əsas indeksləri dizayn edin (sorğu profili).
3. Retenshn/append nümunələri olan yerdə partizanlaşdırmanı daxil edin.
4. AV/ANALYZE-ni qaynar cədvəllərdə qurun, bloat/wraparound-u izləyin.
5. Pik pəncərələrin altında yaddaş, WAL və check-point.
6. Bağlantı hovuzunu və 'pg _ stat _ statements'; alertlər qurmaq.
7. Replikasiya + DR + PITR - məcburi; təlimlər keçirin.
8. JSONB və GIN-i yalnız lazımi yollarla məhdudlaşdırın; partial indexes istifadə edin.
9. Əməliyyatların müddətini minimuma endirin, work-növbələr üçün 'SKIP LOCKED' istifadə edin.
10. Audit/PII/şifrələmə/rol modeli - ödənişlər başlamazdan əvvəl.

18) Antipattern

Hər şey üçün bir «universal» indeks və sorğu analizi yoxdur.
Uzun «asılı» əməliyyatlar ('idle in transaction') → bloklama/böyümə bloat.
Laq nəzərə alınmadan read-after-write üçün replikalara güvənmək.
Hər şeyi JSONB-da saxlayın və bütün sənədi bir GIN ilə indeksləyin.
Sıfır VACUUM/ANALYZE nəzarəti və lag/kontrol nöqtələrinin monitorinqinin olmaması.
Kütləvi miqrasiyalar/DDL pik saatlarda.

19) Faydalı snippet

Sorğu və bufer planı

sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT amount_cents
FROM ledger
WHERE player_id = $1
ORDER BY ts DESC
LIMIT 100;

«Ağır» sorğuların profilləşdirilməsi

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;

Partiyaların rotasiyası (ideya)

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');

Nəticələr

PostgreSQL yükün xətti artımı ilə iGaming platformasında «pul və həqiqəti» çəkməyə qadirdir - indekslər, partiyalar, avtovağlama, WAL/yaddaş, replika/DR və müşahidə olunursa. Sorğu profili və SLO ilə başlayın, real nümunələr üçün indekslər qurun, isti cədvəlləri təcrid edin, ciddi əməliyyat gigiyenasını yandırın və baza turnirlər və pik ödənişləri saxlayacaq.