PostgreSQL: iň oňat amallar we indeksleme

(Bölüm: Tehnologiýalar we infrastruktura)

Gysgaça gysgaça

PostgreSQL - pul, KYC we iGaming-de kanuny taýdan möhüm ýazgylar üçin "hakykatyň özeni". ACID-kepillik, güýçli SQL we giňeltmek ukybyny berýär. Ýaryşlaryň we PSP-webhuklaryň iň ýokary derejesine çydam etmek üçin: başarnykly shema, indeksler, partiýa ýerleşdirmek, awtowakuum, WAL sazlamak we gözegçilik etmek. Aşakda - tejribe we şablon dizaýneri.

1) Arhitektura we SLO

PostgreSQL roly: ýazmak üçin lider + okamak üçin replikalar; gyzgyn ekranlar üçin - keş/proýeksiýa (Redis/materiallaşdyrylan çykyşlar).
SLO mysallar: p99 'INSERT/UPDATE' gapjyk ≤ 25-40 ms; p99 balansy okamak ≤ 10-15 ms; lag replika ≤ 2-5 s; elýeterlilik ≥ 99. 9%.
read-after-write syýasaty: amaldan soň ulanyjy ekranlary liderden okalýar ýa-da köpeltmek gijikdirilmegine garaşýar.

2) Shemalaryň dizaýny

Pul ýadrosynyň kadalaşmagy (gapjyklar, ledger) + okamak üçin denormalizasiýa (CQRS/proýeksiýa).
Berk çäklendirmeler: 'NOT NULL', 'CHECK', 'UNIQUE', FK maksatly 'ON DELETE/UPDATE' (RESTRICT/SET NULL/NO ACTION).
Shemanyň wersiýasy: up/down göçmek, feature-baýdaklar; gyzgyn wagtda adyny üýtgetmekden gaça duruň.
Kesgitleýjiler: 'BIGINT' + yzygiderlilik (ýa-da paýlanyş üçin ULID/UUIDv7). Gyzgyn goşmalar üçin - aýratyn tabespace/WAL-tomda yzygiderlilik.

3) Indeksasiýa: näme, nirede we nädip

3. 1 B-Tree (defolt)

Haçan: Takyk laýyklyklar, diapazonlar, sortlar, FK boýunça 'JOIN'.
Pattern: 'WHERE player_id =?', 'ORDER BY created_at DESC LIMIT 100'.

• Köp sütünli indeksler - şertleriň tertibine laýyk geliň.
• Covering: `INCLUDE (...)` для index-only scan.
• 'ORDER BY... DESC "lentalarda".

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

3. 2 GIN (JSONB, massiwler, doly tekst)

Haçan: Açarlar/ýollar boýunça JSONB-gözlemek, bellik massiwleri, FTS.
Saýlawlar: 'gin _ trgm _ ops', 'jsonb _ path _ ops '/' jsonb _ ops'.
Tejribe: gözleg meýdançalaryny berk çäklendiriň we kardinallyk hakda pikir ediň.

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

Haçan: geolokasiýa (IP-geo, radiuslar), aralyklar, nearest-neighbor.
Az pul özeninde ulanylýar, geo-çäklendirmeler/jogapkär oýun üçin peýdalydyr.

3. 4 BRIN (uly "appendiks" - wagt tablisalary)

Haçan: milliardlarça setirler, tebigy wagt baglanyşygy (jedeller/wakalar magazinesurnallary).
Artykmaçlyklary: kiçi göwrümli, arzan hyzmat.
Minuslar: gödek saýlama → partiýa ýerleşdirmek bilen birleşdirmek.

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

3. 5 Hash

Seýrek zerur: diapazonlar bolmadyk ýagdaýynda bir sütün boýunça deňlik; köplenç B-Tree ýeterlikdir.

3. 6 Bölekleýin we aňlatmalar

Partial index: gyzgyn bölekleri çaltlaşdyrýar (işjeň, 'status =' pending ').
Expression index: Açaryň öňünden sanalmagy ('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 Indekslere garşy

"Hemme zat üçin indeks": indeksleriň artykmaçlygy ýazgyny we VACUUM-y haýalladýar.
Gaýtalanýan indeksler (şol bir sütün toplumy/tertibi).
Gaty pes kardinally sütün üçin indeks (mysal üçin, 2 manyly 'status') - bölekleýin ediň.

4) Partiýa ýerleşdirmek

Näme üçin: bloat azaltmak, VACUUM/skanerleri çaltlaşdyrmak, retention/arhiw ýeňilleşdirmek.
Shemalar: Nyrhlaryň ýazgylary üçin senesi (gün/hepde) boýunça RANGE; Uly ulanyjy tablisalary üçin 'player _ id' HASH; bilelikde.
Rotasiýa tejribesi: geljekki partiýalary öňünden döretmek, "ATTACH PARTITION", köne arhiw - "DETACH" + göçürmek.

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) Iň az bölek bilen goýmalar/täzelenmeler

GYZGYN TÄZELENMELER: 'fillfactor' -y (mysal üçin 90) sahypadaky boş ýer üçin gyzgyn tablisalarda saklaň.
TOAST: uly JSONB/tekst - kompakt; uly meýdanlary aýratyn tablisada saklaň.
UPSERT: 'ON CONFLICT... DO UPDATE 'idempotent logikasy bilen.

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) Geleşikler, petiklemeler we bäsdeşlik

Izolýasiýa derejeleri: Köp ýollar üçin 'READ COMMITTED'; nokat boýunça 'REPEATABLE READ '/' SERIALIZABLE' (hasabatlar, offline-batchi). Olary uzak saklamaň.
Blokirlemeleriň görnüşleri: row-level (pessimizm 'FOR UPDATE'), table-level (DDL), paýlanan sazlar üçin advisory locks.
Deadlocks: gysga amallar, mazmuny täzelemegiň ýeke-täk tertibi, wagtlar ('lock _ timeout', 'statement _ timeout').
Wezipe nobatlary: "SKIP LOCKED" paýlanan iş howuzy üçin.

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

7) Awtowakuum, statistika we bloat

VACUUM/ANALYZE: häzirki statistikany ('default _ statistics _ target') saklaň, gyzgyn tablisalarda AV-ny düzüň (aşakda işleýiş çäkleri).
wraparound (age (txid) <2 mlrd), 'vacuum _ freeze _ min _ age' -ni yzarlaň.
bloat-gözegçilik: iň ýokary derejeden daşary agyr indekslerde yzygiderli reindex/CLUSTER; partiýa ýerleşdirmek meseläniň gerimini peseldýär.

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

8) Ýat, barlag nokatlary we WAL

Ýat

'shared _ buffers': 20-25% RAM (profiline bagly).
'work _ mem': Amal! Konserwatiw (mysal üçin 8-64MB) guruň we hasabat rollary üçin nokady ýokarlandyryň.
'maintenance _ work _ mem': uly indeksler/dikeldiş (512MB-2GB boýunça).

WAL we barlag nokatlary

WAL üçin NVMe, aýratyn jilt; 'wal _ compression = on'.
'checkpoint _ timeout' 10-15 min, 'max _ wal _ size', 'checkpoint _ completion _ target ≈ 0. 9`.
Goşundylaryň iň ýokary nokatlary üçin - batch goşundylary we toparlaýyn komissiýalar.

9) Replikasiýa we şowsuzlyga çydamlylyk

Lider-replika: RPO ≈ 0-30s üçin iň ýakyn (semi-sync) sinhron; okamak/seljermek üçin asinhron.
Mahabat we fencing: Patroni/replika-dolandyryjy; "goşa lider" diýen sözleri aýyrmaly.
Hot standby: 'hot _ standby _ feedback' seresaplylyk bilen (bloat), has gowusy - replikalarda uzyn amallary ýerine ýetiriň.

10) Ekaplar we PITR

WAL üçin 'archive _ command'.
PITR: stendlerdäki wagt nokadyna çenli dikeldişi barlaň; RPO/RTO düzgünleşdiriň (gapjyklar - minutlar, loglar - onlarça minut).
DR maşklary (game day): Yzygiderli dikeldiş barlagy.

11) JSONB we "çeýe shema" modeli

JSONB seýrek okalýan/üýtgeýän atributlar (KYC-meta-maglumatlar, PSP parametrleri) üçin ajaýyp.
Relýasiýa sütünlerinde hökmany meýdanlary berk tassyklaň; JSONB - nuanslaryň "guýrugy" üçin.
Indeksasiýa: ulanylýan ýollar boýunça nokat GIN; "hemme zada bir äpet GIN" -den gaça duruň.

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) Doly söz we fuzzy-gözleg

Gurlan FTS: 'tsvector' + GIN; ýalňyşlyklar üçin - 'pg _ trgm'.
Bloglar/oýunlar boýunça kyn gözleg üçin - gözleg ulgamyna (ES/OpenSearch) getirmek we baglanyşygy/meta-maglumatlary PG-de saklamak.

13) Synlamak we profillemek

pg_stat_statements: iň ýokary haýal/ýygy-ýygydan haýyşlar, kadalaşma.
EXPLAIN (ANALYZE, BUFFERS): meýilnamalary okaň, gyzgyn ýollarda seq scan gözläň.
Metrikler: TPS, p95/p99, çek nokatlary, 'replication _ lag', deadlocks, bloat, AV siklleri, cache-hit ≥ 95%.
Alertler: laglaryň beýikligi, "idle in transaction", garaşylmadyk seq scan, WAL-tupan.

14) Birikmeleriň howuzy we taýýarlanan haýyşlar

Web-trafik üçin 'transaction' re modunda puller (PgBouncer); 'session' - uzak kursorlar/arka ofis üçin.
Prepared statements/parametrlemek → az parsing, durnukly meýilnamalar.
Iň köp bekend sanyny çäklendiriň ('max _ connections'; çeňňegi öz üstüne alýar).

15) Howpsuzlyk we gabat gelmek

Tranzitde TLS, diskleri şifrlemek, KMS/daşarky açarlar.
RBAC: iň az hukuklar, rollary bölmek okamak/ýazmak/admin.
Multi-tenant ssenariler üçin RLS (Row-Level Security).
PII: gizlemek/lakamlaşdyrmak, saklanyş möhleti.
Audit: 'pgaudit '/kritiki tablisalarda audit triggerleri (gapjyk/ledger).

16) iGaming üçin adaty şablonlar

16. 1 Gapjyk we ledger (berk sazlaşyk)

Индексы: `wallet(player_id PK)`, `ledger(player_id, ts DESC)` + `INCLUDE (delta_cents, reason)`.
Geleşik balansy täzeleýär we ledger-e ýazýar; semi-sync göçürmesi; keş diňe proýeksiýa hökmünde.

16. 2 Jedelleriň taryhy (ýokary TPS, oýunçy/wagt okamak)

Gün/hepde boýunça partizasiýa, BRIN wagt boýunça + B-Tree '(player_id, created_at DESC)'.
'DETACH PARTITION' arkaly gaýtadan işlemek → OLAP-da arhiwlemek.

16. 3 PSP webhuklar (burstlar, retralar)

"Çig" wakalaryň nobaty (append-only), wagt boýunça partiýa, bölekleýin indeks 'status =' pending '.
Idempotentlik 'idempotency _ key '/' psp _ tx' (UNIQUE).

17) Girizmegiň çek-sanawy

1. SLO we read-after-write syýasatyny düzüň.
2. Hakyky soraglara (soraglaryň profiline) esasy indeksleri düzüň.
3. Retenşn/append-patternleriň bar ýerinde partizasiýany açyň.
4. AV/ANALYZE-ni gyzgyn tablisalarda sazlaň, bloat/wraparound-a gözegçilik ediň.
5. Iň ýokary penjireleriň aşagyndaky ýady, WAL we barlag nokatlaryny sazlaň.
6. Baglanyşyk howuzyny goýuň we 'pg _ stat _ statements'; aladalary dörediň.
7. DR + PITR göçürmesi - hökmany; türgenleşik geçiriň.
8. JSONB we GIN-i diňe zerur ýollar bilen çäklendiriň; partial indexes.
9. Amallaryň dowamlylygyny azaldyň, work-nobatlar üçin 'SKIP LOCKED' ulanyň.
10. Audit/PII/şifrlemek/rol modeli - tölegler başlanýança.

18) Antipatternler

Hemme zat üçin bir "ähliumumy" indeks we haýyşlary seljermegiň ýoklugy.
Uzak "asylan" amallar ('idle in transaction') → blokirleme/bloat.
Lagy hasaba almazdan read-after-write üçin replikalara bil baglaň.
JSONB-de hemme zady saklamak we ähli resminamany bir GIN bilen indekslemek.
VACUUM/ANALYZE nol gözegçiligi we laglaryň/barlag nokatlarynyň gözegçiliginiň ýoklugy.
Iň ýokary sagatlarda köpçülikleýin göçmek/DDL.

19) Peýdaly snippetler

Soragyň we buferleriň meýilnamasy

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

"Agyr" soraglary profillemek

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;

Partiýalaryň aýlanmagy (ideýa)

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

Netijeler

PostgreSQL, ýüküň çyzykly ýokarlanmagy bilen iGaming platformasynda "pul we hakykaty" çekmäge ukyply - indeksler, partiýalar, awtowakuwum, WAL/ýat, replika/DR we syn ediliş tertipli bolsa. Soraglaryň profilinden we SLO-dan başlaň, hakyky nusgalar üçin indeksleri guruň, gyzgyn tablisalary izolýasiýa ediň, berk iş arassaçylygyny açyň we baza ýaryşlary we iň ýokary tölegleri saklar.