PostgreSQL: cele mai bune practici și indexare
(Secțiunea: Tehnologie și infrastructură)
Scurt rezumat
PostgreSQL este „nucleul adevărului” pentru bani, KYC și înregistrări semnificative din punct de vedere legal în iGaming. Acesta oferă garanții ACID, SQL puternic, și extensibilitate. Pentru a rezista la vârfurile turneelor și cârligelor web PSP, acestea sunt esențiale: schemă competentă, indici, partiționare, auto-vid, tuning WAL și observabilitate. Mai jos este constructorul de practici și șabloane.
1) Arhitectură și SLO
PostgreSQL rol: lider pentru scris + replici pentru citit; pentru ecrane fierbinți - cache/proiecții (Redis/vizualizări materializate).
Exemple SLO: p99 „INSERT/UPDATE” portofel ≤ 25-40 ms; p99 citire echilibru ≤ 10-15 ms; lag replica ≤ 2-5 s; disponibilitate ≥ 99. 9%.
Politica de citire după scriere: ecranele utilizatorului după o tranzacție sunt citite de la lider sau așteaptă un decalaj de replicare.
2) Design schematic
Normalizarea nucleului de bani (portofele, registru) + denormalizare pentru citire (CQRS/proiecții).
Restricţii stricte: 'NOT NULL',' CHECK ',' UNIQUE ', FK cu' ON DELETE/UPDATE '(RESTRICT/SET NULL/NO ACTION).
Versionarea schemei: migrații în sus/în jos, steaguri; evitați ruperea redenumirii în timp cald.
Identificatori: secvențe „BIGINT” + (sau ULID/UUIDv7 pentru distribuție). Pentru inserții fierbinți - secvențe într-un volum separat de spațiu/WAL.
3) Indexare: ce, unde și cum
3. 1 B-Tree (implicit)
Când: potriviri exacte, intervale, sortimente, 'JOIN' de FK.
Modele: 'WHERE player_id =?', 'ORDER BY created_at DESC LIMIT 100'.
- Indici multi-coloană - Potriviți ordinea condițiilor.
- Acoperire: „INCLUDE (...)” для scanare numai index.
- Indice individual în conformitate cu "ORDINE DE... DESC' pe „casete”.
sql
CREATE INDEX idx_tx_player_created_desc
ON tx (player_id, created_at DESC) INCLUDE (amount_cents, status);3. 2 GIN (JSONB, matrice, text integral)
Când: Căutare cheie/cale JSONB, matrice de etichete, FTS.
Variante: 'gin _ trgm _ ops' pentru trigrame,' jsonb _ path _ ops'/' jsonb _ ops'.
Practică: Limitați strict câmpurile de căutare și gândiți-vă la cardinalitate.
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/intervale/semnături)
Când: geolocalizare (IP-geo, radii), intervale, cel mai apropiat vecin.
Mai puțin utilizat în miezul banilor, util pentru geo-restricții/joc responsabil.
3. 4 BRIN (tabele mari de „apendice” în timp)
Când: Miliarde de linii, corelație naturală în timp (jurnale de pariuri/evenimente).
Pro: dimensiuni mici, servicii ieftine.
Contra: selectivitatea grosieră → fi combinată cu partiționarea.
sql
CREATE INDEX idx_bets_brin ON bets USING BRIN (created_at) WITH (pages_per_range = 64);3. 5 Hash
Rareori necesar: egalitate cu o coloană în absența intervalelor; mai des B-Tree este suficient.
3. 6 Parțială și expresii
Indice parțial: accelerează sub-seturile fierbinți (active, 'status =' în așteptare ").
Expression index: precompute key ('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 Index Antipatterns
„Index pentru orice”: un exces de indici încetinește scrierea și VACUUM.
Indici duplicați (același set/ordine coloană).
Index la o coloană cu cardinalitate foarte scăzută (de exemplu, „stare” cu 2 valori) - face parțial.
4) Partiționarea
De ce: reduceți balonarea, accelerați VACUUM/scanări, facilitați retenția/arhiva.
Scheme: INTERVAL după dată (zi/săptămână) pentru jurnalele de pariuri; HASH by 'player _ id' pentru mese mari de utilizator; combinate.
Practica de rotație: creați părțile viitoare în avans, „ATTACH PARTITION”, arhiva celor vechi - „DETACH” + mișcare.
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) Inserții/actualizări cu fragmentare minimă
Actualizări fierbinți: Păstrați „fillfactor” (de ex. 90) pe foi de calcul fierbinte pentru spațiu liber pe pagină.
TOAST: mare JSONB/texte - păstrați un ochi pe compactare; Depozitați câmpurile voluminoase într-un tabel separat.
UPSERT: Utilizați "PE CONFLICT... ACTUALIZAȚI "cu logică idempotentă.
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) Tranzacții, încuietori și concurență
Niveluri de izolare: „READ COMMITED” pentru majoritatea căilor; „REPETABIL CITIT ”/„ SERIALIZABIL” punctual (rapoarte, loturi offline). Nu-i ţine prea mult.
Tipuri de încuietori: nivel rând (pesimism „PENTRU ACTUALIZARE”), nivel tabel (DDL), blocări consultative pentru mutexuri distribuite.
Blocaje: tranzacții scurte, ordinea uniformă a entităților de actualizare, timeout ('lock _ timeout', 'statement _ timeout').
Cozi de sarcini: „SKIP LOCKED” pentru piscina de lucru distribuită.
sql
-- batch processor removes work without racing
SELECT id FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 100;7) vid auto, statistici și bloat
VACUUM/ANALYZE: păstrați statisticile actuale ('default _ statistics _ target'), tune AV pe mesele fierbinți (pragul de declanșare de mai jos).
Ferește-te pentru wraparound (vârstă (txid) <2 miliarde), 'vid _ freeze _ min _ age'.
control bloat: reindex regulat/CLUSTER pe indici grei în afara vârfului; partiționarea reduce amploarea problemei.
sql
ALTER TABLE tx SET (
autovacuum_vacuum_scale_factor = 0. 05,
autovacuum_analyze_scale_factor = 0. 02,
autovacuum_vacuum_cost_limit = 4000
);8) Memorie, puncte de control și WAL
Memorie
'shared _ buffers': 20-25% RAM (dependent de profil).
'work _ mem': prin operație! Configurați conservator (de exemplu, 8-64MB) și creșteți punctul de vedere pentru rolurile de raportare.
'mentenanta _ work _ mem': indici mari/recuperare (sarcina 512MB-2GB).
WAL și punctele de control
NVMe pentru WAL, un singur volum; 'wal _ compression = on'.
'checkpoint _ timeout' 10-15 min, 'max _ wal _ size' supus modificării, 'checkpoint _ finalization _ target ≈ 0. 9`.
Pentru inserarea vârfurilor - inserții de lot și angajamente de grup.
9) Replicarea și toleranța la erori
Replica liderului: sincron cu cel mai apropiat (semi-sincronizare) pentru RPO≈0 -30c; asincron pentru citiri/analize.
Promovare și scrimă: Patroni/replica manager; exclude „liderul dual”.
Hot standby: 'hot _ standby _ feedback' cu atenție (creștere bloat), mai bine - îmblânziți tranzacțiile lungi pe replici.
10) Backup-uri și PITR-uri
Copie de rezervă completă + incrementală, copii offsite; 'archive _ command' pentru WAL.
PITR: verificați recuperarea până la punctul de timp de pe standuri; reglează RPO/RTO (portofele - minute, jurnale - zeci de minute).
DR (ziua jocului) exerciții: verificare regulată de recuperare.
11) JSONB și modelul „circuit flexibil”
JSONB - excelent pentru atribute rar citite/variabile (metadate KYC, parametri PSP).
Validați strict câmpurile necesare în coloane relaționale; JSONB - pentru „coada” nuanțelor.
Indexare: punct GIN pe trasee utilizate; evita „un GIN gigant pentru tot”.
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) Text complet și căutare neclară
FTS încorporat: 'tsvector' + GIN; pentru greșeli de ortografie - 'pg _ trgm'.
Pentru o căutare dificilă prin jurnale/jocuri - scoateți la motorul de căutare (ES/OpenSearch) și stocați link-ul/metadatele în PG.
13) Observabilitate și profilare
pg_stat_statements: interogări lente/frecvente, normalizare.
EXPLICA (ANALIZA, BUFFERS): citiți planurile, căutați scanarea seq pe piese fierbinți.
Valori: TPS, p95/p99, puncte de control, 'replication _ lag', blocaje, bloat, bucle AV, cache-hit ≥ 95%.
Alerte: creșterea lag-urilor, „inactiv în tranzacție”, scanare seq neașteptată, furtună WAL.
14) Piscină de conectare și interogări pregătite
Puller (PgBouncer) în modul „tranzacție” pentru traficul web; „sesiune” - pentru cursoare lungi/back office.
Instrucțiuni pregătite/parametrizare → mai puțin parsare, planuri stabile.
Limitați numărul maxim de backend-uri („max _ connections” scăzut; piscina este preluată de supapă).
15) Siguranță și conformitate
TLS în tranzit, criptare disc, KMS/chei străine.
RBAC: drepturi minime, separarea rolurilor de citire/scriere/admin.
RLS (Row-Level Security) pentru scenarii multi-chiriași.
PII: mascare/aliasing, termen de valabilitate.
Audit: „pgaudit ”/declanșatoare de audit pe tabele critice (portofel/registru).
16) Șabloane tipice pentru iGaming
16. 1 Portofel și registru (consistență strictă)
Индексы: 'portofel (player_id PK)', 'registru (player_id, ts DESC)' + 'INCLUDE (delta_cents, motiv)'.
Tranzacția actualizează soldul și scrie în registru; replicarea semi-sincronizată; cache ca proiecție numai.
16. 2 Istoria pariurilor (TPS ridicat, Player/Time Reading)
Partiționarea după zi/săptămână, BRIN după timp + B-Tree '(player_id, created_at DESC)'.
Păstrarea prin „DETACH PARTITION” → arhivarea la OLAP.
16. 3 Webhooks PSP (burstami, retrai)
Coadă de evenimente "raw" (numai pentru adăugare), partiții de timp, index parțial on 'status =' în așteptare "
Idempotența prin „idempotency _ key ”/„ psp _ tx” (UNIC).
17) Lista de verificare a implementării
1. Angajați politica SLO și citiți după scriere.
2. Design indici cheie pentru interogări reale (profil de interogare).
3. Includeți partiționarea în cazul în care există modele de retenție/apendice.
4. Configurați AV/ANALYZE pe mese fierbinți, păstrați un ochi pe bloat/wraparound.
5. Reglați memoria, WAL și punctele de control pentru ferestrele de vârf.
6. Puneți piscina de conexiune și 'pg _ stat _ declarations'; Ia alerte.
7. Replicare + DR + PITR - necesar; exerciții de conduită.
8. Limitați JSONB și GIN la numai căile necesare; utilizați indici parțiali.
9. Minimizați durata tranzacției, utilizați „SKIP LOCKED” pentru cozile de lucru.
10. Audit/PII/criptare/model de rol - înainte de a începe plățile.
18) Antipattern
Un index „universal” pe tot și nici o analiză interogare.
Tranzacții lungi „agățate” („inactiv în tranzacție”) → blocare/creștere bloat.
Bazați-vă pe replici pentru citire după scriere fără întârziere.
Stocați totul în JSONB „doar în caz” și indexați întregul document cu un singur GIN.
Zero control VACUUM/ANALIZA și nici o monitorizare a lag-uri/puncte de control.
Migrații în masă/DDL în timpul orelor de vârf.
19) Fragmente utile
Planul de interogare și tampoane
sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT amount_cents
FROM ledger
WHERE player_id = $1
ORDER BY ts DESC
LIMIT 100;Profilare interogare grea
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;Rotația partidului (idee)
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');Rezumat
PostgreSQL este capabil să tragă „bani și adevăr” pe platforma iGaming cu o creștere liniară a sarcinii - dacă indicii, părțile, auto-vidul, WAL/memoria, replica/DR și observabilitatea sunt disciplinate. Începeți cu un profil de interogări și SLO-uri, construiți indici pentru modele reale, izolați mesele fierbinți, activați igiena operațională strictă - iar baza va păstra în mod previzibil turneele și plățile de vârf.