PostgreSQL: Best Practices und Indexierung
(Abschnitt: Technologie und Infrastruktur)
Kurze Zusammenfassung
PostgreSQL ist der „Kern der Wahrheit“ für Geld, KYC und rechtlich relevante Einträge in iGaming. Es gibt ACID-Garantien, leistungsstarke SQL und Erweiterbarkeit. Um den Spitzen von Turnieren und PSP-Webhooks standzuhalten, sind kritisch: ein kompetentes Schema, Indizes, Partitionierung, Auto-Vakuum, WAL-Tuning und Beobachtbarkeit. Im Folgenden finden Sie den Builder für Praktiken und Vorlagen.
1) Architektur und SLO
Die Rolle von PostgreSQL: Leader to Write + Replikate to Read; für Hot Screens - Cache/Projektionen (Redis/materialisierte Ansichten).
SLO Beispiele: p99 'INSERT/UPDATE' wallet ≤ 25-40 ms; p99 Lesen der Balance ≤ 10-15 ms; Lag Repliken ≤ 2-5 s; Verfügbarkeit ≥ 99. 9%.
Read-After-Write-Richtlinie: Benutzerdefinierte Bildschirme nach einer Transaktion, die vom Leader gelesen werden oder auf einen Replikationsfehler warten.
2) Schaltungsdesign
Normalisierung des Geldkerns (Geldbörsen, Ledger) + Denormalisierung zum Lesen (CQRS/Projektionen).
Strenge Einschränkungen: 'NOT NULL', 'CHECK', 'UNIQUE', FK mit gezieltem 'ON DELETE/UPDATE' (RESTRICT/SET NULL/NO ACTION).
Schema-Versionierung: Auf/Ab-Migrationen, Feature-Flags; Vermeiden Sie brechende Umbenennungen in heißen Zeiten.
Kennungen: 'BIGINT' + Sequenzen (oder ULID/UUIDv7 für die Verteilung). Für Hot Inserts - Sequenzen in einem separaten Tablespace/WAL-Volume.
3) Indexierung: Was, wo und wie
3. 1 B-Baum (Ausfall)
Wann: genaue Entsprechungen, Bereiche, Sortierung, 'JOIN' nach FK.
Die Muster lauten: „WHERE player_id =?“, „ORDER BY created_at DESC LIMIT 100“.
- Mehrspaltige Indizes - Erfüllen Sie die Reihenfolge der Bedingungen.
- Covering: `INCLUDE (...)` для index-only scan.
- Separater Index unter 'ORDER BY... DESC 'auf „Bänder“.
sql
CREATE INDEX idx_tx_player_created_desc
ON tx (player_id, created_at DESC) INCLUDE (amount_cents, status);3. 2 GIN (JSONB, Arrays, Volltext)
Wann: JSONB-Suche nach Schlüssel/Pfad, Tag-Arrays, FTS.
Optionen: 'gin _ trgm _ ops' für Trigramme,' jsonb _ path _ ops'/' jsonb _ ops'.
Praxis: Schränken Sie die Suchfelder streng ein und denken Sie an die Kardinalität.
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/Bereiche/Signaturen)
Wann: Geolokalisierung (IP-Geo, Radien), Intervalle, nearest-neighbor.
Weniger verwendet im Geldkern, nützlich für Geobeschränkungen/verantwortungsvolles Spielen.
3. 4 BRIN (große „Appendix“ -Tabellen in der Zeit)
Wann: Milliarden von Zeilen, natürliche zeitliche Korrelation (Wett-/Ereignisprotokolle).
Vorteile: geringe Größe, billiger Service.
Nachteile: Grobe Selektivität → mit Partitionierung kombiniert werden.
sql
CREATE INDEX idx_bets_brin ON bets USING BRIN (created_at) WITH (pages_per_range = 64);3. 5 Hash
Selten benötigt: Gleichheit einer Spalte in Abwesenheit von Bereichen; häufiger reicht B-Tree.
3. 6 Partielle und Ausdrücke
Partieller Index: beschleunigt heiße Untermengen (aktiv, 'status =' pending').
Ausdruck Index: Vorwegnahme des Schlüssels ('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 Anti-Pattern-Indizes
„Index für alles“: Ein Überschuss an Indizes hemmt die Erfassung und VACUUM.
Doppelte Indizes (gleiche Menge von Spalten/Reihenfolge).
Index pro Spalte mit sehr geringer Kardinalität (z.B. 'status' mit 2 Werten) - partiell machen.
4) Partitionierung
Warum: Reduzieren Sie Bloat, beschleunigen Sie VACUUM/Scans, erleichtern Sie Retention/Archiv.
Schemata: RANGE nach Datum (Tag/Woche) für Wettprotokolle; HASH durch 'player _ id' für große benutzerdefinierte Tabellen; kombiniert.
Rotationspraxis: Erstellen Sie im Voraus zukünftige Parteien, 'ATTACH PARTITION', Archiv der alten - 'DETACH' + Umzug.
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) Einfügungen/Updates mit minimaler Fragmentierung
HOT-Updates: Halten Sie' fillfactor'(z. B. 90) auf heißen Tabellen für freien Speicherplatz auf der Seite.
TOAST: große JSONB/Texte - achten Sie auf die Kompaktheit; Speichern Sie sperrige Felder in einer separaten Tabelle.
UPSERT: Verwenden Sie' ON CONFLICT... DO UPDATE ™ mit idempotenter Logik.
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) Transaktionen, Sperren und Wettbewerb
Isolationsstufen: 'READ COMMITTED' für die meisten Pfade; 'REPEATABLE READ '/' SERIALIZABLE' Punkt (Berichte, Offline-Batches). Halten Sie sie nicht lange.
Arten von Sperren: Row-Level (Pessimismus' FOR UPDATE'), Table-Level (DDL), Advisory-Sperren für verteilte Mutexe.
Deadlocks: kurze Transaktionen, einheitliche Reihenfolge der Aktualisierung von Entitäten, Timeouts ('lock _ timeout', 'statement _ timeout').
Aufgabenwarteschlangen: 'SKIP LOCKED' für einen verteilten Workpool.
sql
-- batch processor removes work without racing
SELECT id FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 100;7) Auto-Vakuum, Statistik und Bloat
VACUUM/ANALYZE: Halten Sie aktuelle Statistiken ('default _ statistics _ target'), tunen Sie AV auf heißen Tabellen (Auslöseschwelle unten).
Achten Sie auf wraparound (Alter (txid) <2 Milliarden), 'vacuum _ freeze _ min _ age'.
bloat-control: regelmäßige reindex/CLUSTER auf schweren Indizes außerhalb der Peaks; Partitionierung verringert das Ausmaß des Problems.
sql
ALTER TABLE tx SET (
autovacuum_vacuum_scale_factor = 0. 05,
autovacuum_analyze_scale_factor = 0. 02,
autovacuum_vacuum_cost_limit = 4000
);8) Gedächtnis, Checkpoints und WAL
Gedächtnis
'shared _ buffers': 20-25% RAM (profilabhängig).
'work _ mem': nach Operation! Passen Sie sich konservativ an (z. B. 8-64MB) und erhöhen Sie punktgenau für Berichtsrollen.
'maintenance _ work _ mem': große Indizes/Wiederherstellung (512MB-2GB nach Aufgabe).
WAL und Checkpoints
NVMe für WAL, separates Volume; 'wal _ compression = on'.
'checkpoint _ timeout' 10-15 min, 'max _ wal _ size' unter dem Umfang der Änderungen, 'checkpoint _ completion _ target ≈ 0. 9`.
Für Einfügungsspitzen gibt es Batch-Einfügungen und Gruppen-Commits.
9) Replikation und Fehlertoleranz
Lead-Repliken: synchron zum nächsten (Semi-Sync) für RPO≈0 -30c; asynchron für Lesungen/Analysen.
Promotion und Fencing: Patroni/Replik-Manager; „Doppelspitze“ abzuschaffen.
Hot standby: 'hot _ standby _ feedback' vorsichtig (Wachstum bloat), besser - zähmen lange Transaktionen auf Repliken.
10) Backups und PITRs
Vollständige + inkrementelle Sicherung, Offsite-Kopien; 'archive _ command' für WAL.
PITR: Überprüfen Sie die Wiederherstellung bis zum Zeitpunkt an den Ständen; Regeln Sie RPO/RTO (Geldbörsen - Minuten, Protokolle - Dutzende von Minuten).
DR (Spieltag) Übungen: regelmäßige Überprüfung der Erholung.
11) JSONB und das Modell der „flexiblen Schaltung“
JSONB - Ideal für selten lesbare/variable Attribute (KYC-Metadaten, PSP-Parameter).
Die Pflichtfelder in den relationalen Spalten streng validieren; JSONB ist für den „Schwanz“ der Nuancen.
Indexierung: Punkt-zu-Punkt-GIN nach verwendeten Pfaden; Vermeiden Sie „einen riesigen GIN für alles“.
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) Volltext und fuzzy-Suche
Eingebauter FTS: 'tsvector' + GIN; Tippfehler: „pg _ trgm“.
Für eine schwere Suche nach Protokollen/Spielen - in die Suchmaschine (ES/OpenSearch) mitnehmen und in PG einen Link/Metadaten speichern.
13) Beobachtbarkeit und Profilierung
pg_stat_statements: Top langsame/häufige Anfragen, Normalisierung.
EXPLAIN (ANALYZE, BUFFERS): Lesen Sie die Pläne, suchen Sie nach seq scan auf den heißen Pfaden.
Metriken: TPS, p95/p99, Checkpoints, 'replication _ lag', Deadlocks, Bloat, AV-Loops, Cache-Hit ≥ 95%.
Alertas: Anstieg der Verzögerungen, „idle in transaction“, unerwartete seq scan, WAL-Sturm.
14) Verbindungspool und vorbereitete Anfragen
Puller (PgBouncer) im 'Transaction' -Modus für Web-Verkehr; 'Session' - für lange Cursor/Backoffice.
Vorbereitete Aussagen/Parametrisierung → weniger Parsing, stabile Pläne.
Begrenzen Sie die maximale Anzahl von Beckends ('max _ connections' niedrig; Pool übernimmt das Ventil).
15) Sicherheit und Compliance
TLS im Transit, Festplattenverschlüsselung, KMS/Fremdschlüssel.
RBAC: Mindestrechte, Rollenaufteilung Lesen/Schreiben/Admin.
RLS (Row-Level Security) für Multi-Tenant-Szenarien.
PII: Maskierung/Pseudonymisierung, Speicherdauer.
Audit: 'pgaudit '/Audit-Trigger auf kritischen Tabellen (Wallet/Ledger).
16) Typische Vorlagen für iGaming
16. 1 Wallet und Ledger (strikte Konsistenz)
Индексы: `wallet(player_id PK)`, `ledger(player_id, ts DESC)` + `INCLUDE (delta_cents, reason)`.
Die Transaktion aktualisiert den Saldo und schreibt an ledger; Semi-Sync-Replikation; Cache nur als Projektion.
16. 2 Wettverlauf (hoher TPS, Lesen nach Spieler/Zeit)
Partitionierung nach Tag/Woche, BRIN nach Zeit + B-Tree'(player_id, created_at DESC)'.
Retention über „DETACH PARTITION“ → Archivierung in OLAP.
16. 3 PSP Webhooks (Burstami, Retrays)
Warteschlange der „rohen“ Ereignisse (append-only), Partitionen nach Zeit, Teilindex nach 'status =' pending'.
Idempotenz durch 'idempotency _ key '/' psp _ tx' (UNIQUE).
17) Checkliste Umsetzung
1. Fix SLO und Read-After-Write-Richtlinie.
2. Entwerfen Sie Schlüsselindizes für reale Abfragen (Anforderungsprofil).
3. Aktivieren Sie die Partitionierung, wenn Retention/Append-Muster vorhanden sind.
4. Konfigurieren Sie AV/ANALYZE auf heißen Tabellen, achten Sie auf bloat/wraparound.
5. Tune Speicher, WAL und Checkpoints für Spitzenfenster.
6. Stellen Sie den Verbindungspool und 'pg _ stat _ statements'; Übernehmen Sie die Alerts.
7. Replikation + DR + PITR - erforderlich; Führen Sie Übungen durch.
8. Beschränken Sie JSONB und GIN nur auf die erforderlichen Pfade; Verwenden Sie partielle Indizes.
9. Minimieren Sie die Transaktionsdauer, verwenden Sie' SKIP LOCKED 'für Workqueues.
10. Audit/PII/Verschlüsselung/Rollenmodell - vor dem Start der Zahlungen.
18) Antipatterns
Ein „universeller“ Index für alles und keine Anforderungsanalyse.
Lange „hängende“ Transaktionen ('idle in transaction') → bloat Blockaden/Wachstum.
Verlassen Sie sich auf Repliken für Read-After-Write ohne Berücksichtigung der Verzögerung.
Speichern Sie alles in JSONB „nur für den Fall“ und indizieren Sie das gesamte Dokument mit einer GIN.
Null VACUUM/ANALYZE-Kontrolle und keine Überwachung von Verzögerungen/Checkpoints.
Massenmigrationen/DDLs in Spitzenzeiten.
19) Nützliche Snippets
Anforderungsplan und Puffer
sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT amount_cents
FROM ledger
WHERE player_id = $1
ORDER BY ts DESC
LIMIT 100;Profilierung von „schweren“ Anfragen
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;Rotation von Parteien (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');Ergebnisse
PostgreSQL ist in der Lage, „Geld und Wahrheit“ auf der iGaming-Plattform zu ziehen, wenn die Last linear steigt - wenn Indizes, Partitionen, Auto-Vakuum, WAL/Speicher, Replik/DR und Beobachtbarkeit diszipliniert sind. Beginnen Sie mit einem Anforderungsprofil und SLOs, erstellen Sie Indizes für reale Muster, isolieren Sie heiße Tabellen, aktivieren Sie strenge Betriebshygiene - und die Basis wird Turniere und Spitzenzahlungen vorhersehbar halten.