PostgreSQL: best practices e indicizzazione
(Sezione Tecnologia e infrastruttura)
Breve riepilogo
Il è il nucleo della verità per i soldi, la KYC e le registrazioni giuridicamente rilevanti. Fornisce garanzie ACID, SQL potenti ed espandibile. Per resistere ai picchi dei tornei e dei siti PSP, sono critici: schema, indici, partitura, autoscatto, tuning WAL e osservazione. Di seguito è riportato il progetto di procedure e modelli.
1) Architettura e SLO
Ruolo PostgreSQL: leader di scrittura + repliche di lettura per le schermate hot - cache/proiezione (Redis/rappresentazioni materializzate).
SLO esempi: p99 'INSERT/UPDAT'portafoglio 25-40 ms; p99 lettura bilanciamento 10-15 ms Due repliche 2-5 c; Disponibilità ≥ 99. 9%.
Criterio read-after-write - Schermate personalizzate dopo la transazione vengono lette dal leader o in attesa di una serie di repliche.
2) Progettazione di diagrammi
Normalizzazione del nucleo di denaro (portafogli, ledger) + denormalizzazione per la lettura (CQRS/proiezioni).
Vincoli rigorosi: «NOT NULL», «CHECK», «UNIQUE», FK con «ON DELETE/UPDATE» mirato (RESTRITT/SET NULL/NO ACTION).
Versioning dello schema: migrazione up/down, feature-flag; Evitate di rompere il nome in tempi caldi.
Identificatori: 'BIGINT' + sequenze (o ULID/UUIDv7 per la distribuzione). Per le inserzioni hot - sequenze in un singolo volume tablespace/WAL.
3) Indicizzazione: cosa, dove e come
3. 1 B-Tree (default)
Quando: corrispondenze precise, intervalli, ordinamenti, 'JOIN'per FK.
Pattern: 'WHERE player _ id =?', 'ORDER BY created _ at DESC LIMIT 100'.
- Indici multipli - Adatta l'ordine delle condizioni.
- Covering: `INCLUDE (...)` для index-only scan.
- Indice separato sotto "ORDER BY... DESC'su nastri.
sql
CREATE INDEX idx_tx_player_created_desc
ON tx (player_id, created_at DESC) INCLUDE (amount_cents, status);3. 2 GIN (JSONB, array, full text)
Quando: Ricerca JSONB su chiavi/percorsi, array di tag, FTS.
Le varianti sono "gin _ trgm _ ops'per i trigrammi," jsonb _ path _ ops "/" jsonb _ ops ".
Pratica: limitare rigorosamente i campi di ricerca e pensare alla radicalità.
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/intervalli/firme)
Quando: geolocalizzazione (geo IP, raggio), intervalli, nearest-neighbor.
Meno utilizzato in un nucleo di denaro, utile per geo-vincoli/gioco responsabile.
3. 4 BRIN (grandi appendici -tablici nel tempo)
Quando: miliardi di righe, correlazione naturale in termini di tempo (registri scommesse/eventi).
I vantaggi sono piccoli, un servizio economico.
Contro, la selettività ruvida può essere combinata con la partitura.
sql
CREATE INDEX idx_bets_brin ON bets USING BRIN (created_at) WITH (pages_per_range = 64);3. 5 Hash
Raramente è necessario: uguaglianza di una colonna in assenza di intervalli; più spesso basta B-Tree.
3. 6 Parziali e espressioni
Partial index: accelera le calde sotto-molteplici (attive, 'status =' pending ").
Espresso index: anticipa la chiave ('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 Indici antipattern
Indice su tutto - L'eccesso di indici frena la voce e VACUUM.
Indici duplicati (stesso insieme di colonne/ordine).
Indice per colonna molto bassa (ad esempio, status con 2 valori) - Fai un parziale.
4) Partizionamento
Perché ridurre il bloat, accelerare il VACUUM/scan, facilitare la retrazione/archivio.
Schemi: RANGE per data (giorno/settimana) per i fogli di puntata; HASH per «player _ id» per grandi tabelle personalizzate; combinato.
La pratica di rotazione è di predisporre le partenze future, «ATTACH PARTITION», l'archivio dei vecchi - «DETACH» + lo spostamento.
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) Inserzioni/aggiornamenti con frammentazione minima
Aggiornamenti HOT: tenete il'illfactor '(ad esempio 90) sulle tabelle hot per lo spazio libero sulla pagina.
TOAST: grandi JSONB/testi - Controlla la compattazione; memorizzare i campi pesanti in una tabella separata.
UPSERT - Usa «ON CONFLICT»... DO UPDATE "con logica idempotente.
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) Transazioni, blocchi e concorrenza
Livelli di isolamento: 'READ COMMITTED' per la maggior parte dei percorsi; 'REPEATABLE READ'/' SERIALIZZABILE' (report, batch off-line). Non teneteli a lungo.
Tipi di blocco: row-level (pessimismo «FOR UPDATE»), table-level (DDL), advisory locks per i mutex distribuiti.
Deadlocks: transazioni brevi, un unico ordine di aggiornamento delle entità, timeout ('lock _ timeout', 'statement _ timeout').
Code di task: «SKIP LOCKED» per un pool di work distribuito.
sql
-- batch processor removes work without racing
SELECT id FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 100;7) Autotrasportatore, statistiche e bloat
VACUUM/ANALYZE - Mantieni le statistiche aggiornate ('default _ statistics _ target'), timbrate AV sulle tabelle calde (la soglia di attivazione è inferiore).
Tieni d'occhio wraparound (age (txid) <2 miliardi), 'vacuum _ freeze _ min _ age'.
bloat control: reindex/CLUSTER regolari su indici pesanti fuori picchi; La partizione riduce la portata del problema.
sql
ALTER TABLE tx SET (
autovacuum_vacuum_scale_factor = 0. 05,
autovacuum_analyze_scale_factor = 0. 02,
autovacuum_vacuum_cost_limit = 4000
);8) Memoria, checkpoint e WAL
Memoria
«shared _ buffers»: 20-25% RAM (dipende dal profilo).
«work _ mem» per operazione! Personalizzare in modo conservativo (ad esempio 8-64MB) e aumentare i livelli per i ruoli di report.
'maintenance _ work _ mem ': grandi indici/ripristino (512MB-2GB per attività).
WAL e checkpoint
NVMe per WAL, volume separato; 'wal _ compressione = on'.
'checkpoint _ timeout' 10-15 min, 'max _ wal _ size', 'checkpoint _ completon _ target ≈ 0. 9`.
Per i picchi di inserimento sono inserimenti di batch e commit di gruppo.
9) Replica e disponibilità
Il leader di replica è sincronizzato con il più vicino (semi-sync) per il -30s; asincrona per letture/analisi.
Promozione e fencing: Patroni/Replica Manager; escludere il «doppio leader».
Hot standby: 'hot _ standby _ feedback', attenta alla crescita del bloat, meglio è domare le lunghe transazioni nelle repliche.
10) Bacapi e PITR
Backup completo + incrementale, copie offsite; 'archive _ commande'per WAL.
PITR: convalida il ripristino al punto temporale degli stand regolamentare RPO/RTO (portafogli - minuti, loghi - decine di minuti).
Esercitazione DR (game day) - Verifica regolare del ripristino.
11) JSONB e modello «schema flessibile»
JSONB - Ottimo per gli attributi raramente letti/variabili (metadati KYC, parametri PSP).
Valuti rigorosamente i campi obbligatori nelle colonne relazionali; JSONB - per la «coda» delle sfumature.
Indicizzazione: GIN puntuale per i percorsi utilizzati; Evitate «un GIN gigante per tutto».
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) Ricerca full-text e fuzzy
FTS incorporato: 'tsvector' + GIN; per le schede - 'pg _ trgm'.
Per la ricerca pesante di logi/giochi, l'estrazione nel motore di ricerca (ES/OpenSearch) e il PG conserva il collegamento/metadati.
13) Osservazione e profilassi
pg _ stat _ statents: il massimo delle query lente/frequenti, normalizzazione.
EXPLORER (ANALYZE, BUFFERS) - Leggere i piani, cercare seq scan sulle vie calde.
Metriche: TPS, p95/p99, checkpoint, 'replication _ lag', deadlocks, bloat, cicli AV, cache-hit al 95%.
Alert: crescita di lame, idle in transizione, seq scan inaspettati, tempesta WAL.
14) Pool di connessioni e query preparate
Puller (PgBouncer) in modalità transaction per il traffico Web; «sessione» per i lunghi cursori/back office.
Predared statents/parametrazione meno parsing, piani stabili.
Limitare il numero massimo di beckend ('max _ connessions'in basso; il pool prende il velo).
15) Sicurezza e compliance
TLS in transito, crittografia dei dischi, KMS/chiavi esterne.
RBAC: diritti minimi, separazione dei ruoli lettura/scrittura/admine.
RLS (Row-Level Security) per gli script multi-tenanti.
PII: occultamento/alias, conservazione.
Controllo: 'pgaudit '/trigger di controllo su tabelle critiche (portafoglio/ledger).
16) Modelli tipici per il iGaming
16. 1 Portafoglio e ledger (rigorosa coerenza)
Индексы: `wallet(player_id PK)`, `ledger(player_id, ts DESC)` + `INCLUDE (delta_cents, reason)`.
La transazione aggiorna il saldo e scrive in ledger; Replica semi-sync cache solo come proiezione.
16. 2 Storia delle scommesse (TPS alto, lettura giocatore/tempo)
Partizionamento giorno/settimana, BRIN ora + B-Tree '(player _ id, created _ at DESC)'.
L'archiviazione di DETACH PARTITION è stata eseguita in OLAP.
16. 3 Webhook PSP (bursti, retrai)
Coda di eventi crudi (append-only), partizioni di tempo, indice parziale di status = 'pending'.
Idimpotenza per «idempotency _ key »/« psp _ tx» (UNIQUE).
17) Assegno-foglio di implementazione
1. Fissare i criteri SLO e read-after-write.
2. Progettare gli indici chiave in base alle richieste reali (profilo query).
3. Attivare la partitura dove ci sono retensioni/append-pattern.
4. Configura AV/ANALYZE sulle tabelle hot, controlla bloat/wraparound.
5. Sintonizzate memoria, WAL e checkpoint sotto le finestre di punta.
6. Inserisci il pool di connessioni e 'pg _ stat _ statements'; Prendete gli alert.
7. La replica + DR + PITR è obbligatoria; Fate le esercitazioni.
8. Limitare JSONB e GIN ai percorsi necessari; Utilizzare partial indexes.
9. Ridurre al minimo la durata delle transazioni, utilizzare SKIP LOCKED per le code work.
10. Controllo/PII/crittografia/modello di ruolo - prima dell'avvio dei pagamenti.
18) Antipattern
Un indice «universale» per tutto e nessuna analisi delle richieste.
Transazioni «appese» lunghe («idle in communication») bloccano/crescono bloat.
Affidarsi alle repliche per il read-after-write, senza contare il raggio.
Memorizza tutto in JSONB per sicurezza e indicizza l'intero documento con un singolo GIN.
Controllo zero di VACUUM/ANALYZE e mancanza di monitoraggio di lame/checkpoint.
Migrazioni di massa/DDL nelle ore di picco.
19) Snippet utili
Piano di query e buffer
sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT amount_cents
FROM ledger
WHERE player_id = $1
ORDER BY ts DESC
LIMIT 100;Profilazione delle query «pesanti»
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;Rotazione partenze (idea)
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');Riepilogo
PostgreSQL è in grado di trarre «denaro e verità» sulla piattaforma iGaming con un aumento lineare del carico di lavoro - se disciplinato da indici, partenze, autoscuola, WAL/memoria, replica/DR e osservabilità. Iniziate con il profilo di query e SLO, costruite indici sotto pattern reali, isolate le tabelle calde, includete l'igiene operativa rigorosa - e la base terrà prevedibilmente i tornei e i picchi di pagamento.