PostgreSQL : meilleures pratiques et indexation
(Section : Technologie et infrastructure)
Résumé succinct
PostgreSQL est le « noyau de la vérité » pour l'argent, KYC et les enregistrements juridiquement significatifs dans iGaming. Il donne des garanties ACID, puissant SQL et extensible. Pour résister aux pics des tournois et des webhooks PSP, il est essentiel : un schéma alphabétisé, des indices, un partitionnement, un autotakuum, un tuning WAL et une observabilité. Ci-dessous, un concepteur de pratiques et de modèles.
1) Architecture et SLO
Rôle PostgreSQL : leader en écriture + répliques en lecture ; pour les écrans chauds - cache/projections (Redis/représentations matérialisées).
Exemples de SLO : Porte-monnaie p99 'INSERT/UPDATE' ≤ 25-40 ms ; p99 lectures du bilan ≤ 10-15 ms ; lag réplique ≤ 2-5 s ; disponibilité ≥ 99. 9%.
Politique read-after-write : les écrans utilisateur après la transaction lisent du leader ou attendent une réplication.
2) Conception de schémas
Normaliser le noyau d'argent (portefeuille, ledger) + dénormaliser pour lire (CQRS/projections).
Restrictions strictes : 'NOT NULL',' CHECK ',' UNIQUE ', FK avec la cible' ON DELETE/UPDATE '(RESTRICT/SET NULL/NO ACTION).
Versioner le schéma : migration up/down, flags de fonction ; éviter les rebaptisations cassantes en temps chaud.
Identifiants : 'BIGINT' + séquence (ou ULID/UUIDv7 pour la distribution). Pour les inserts chauds, une séquence dans un volume tablespace/WAL distinct.
3) Indexation : Quoi, où et comment
3. 1 B-Tree (défaut)
Quand : correspondances précises, gammes, triage, 'JOIN' par FK.
Patterns : 'WHERE player_id = ?', 'ORDER BY created_at DESC LIMIT 100'.
- Index multi-colonnes - Correspondez à l'ordre des conditions.
- Covering: `INCLUDE (...)` для index-only scan.
- Index séparé sous 'ORDER BY... DESC'sur les « bandes ».
sql
CREATE INDEX idx_tx_player_created_desc
ON tx (player_id, created_at DESC) INCLUDE (amount_cents, status);3. 2 GIN (JSONB, matrices, texte intégral)
Quand : Recherche JSONB par clé/chemin, tableaux de balises, FTS.
Variantes : 'gin _ trgm _ ops' pour trigrammes, 'jsonb _ path _ ops '/' jsonb _ ops'.
Pratique : limiter strictement les champs de recherche et penser à la cardinalité.
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 (géo/gammes/signatures)
Quand : géolocalisation (géo IP, rayons), intervalles, nearest-neighbor.
Moins utilisé dans le noyau d'argent, utile pour les géo-restrictions/jeu responsable.
3. 4 BRIN (gros « appendice » -tablis dans le temps)
Quand : des milliards de lignes, corrélation naturelle dans le temps (journaux de paris/événements).
Avantages : petite taille, service bon marché.
Inconvénients : sélectivité grossière → combiner avec le lot.
sql
CREATE INDEX idx_bets_brin ON bets USING BRIN (created_at) WITH (pages_per_range = 64);3. 5 Hash
Rarement nécessaire : égalité d'une colonne en l'absence de fourchettes ; plus souvent, le B-Tree suffit.
3. 6 Partielles et expressions
Indice partial : accélère les sous-ensembles chauds (actif, 'status =' pending ').
Indice d'expression : anticipation de la clé ('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-modèles d'index
« Indice pour tout » : l'excès d'indices freine l'enregistrement et VACUUM.
Index en double (même jeu de colonnes/ordre).
Index par colonne avec une cardinalité très faible (par exemple, 'status' avec 2 valeurs) - faire partiel.
4) Lot
Pourquoi : réduire le bloat, accélérer le VACUUM/scans, faciliter la rétention/archive.
Schémas : RANGE par date (jour/semaine) pour les logs de paris ; HASH par 'player _ id'pour les grandes tables personnalisées ; combiné.
Pratique de rotation : créer à l'avance les futurs lots, 'ATTACH PARTITION', archive des anciens - 'DETACH' + se déplacer.
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) Inserts/mises à jour avec une fragmentation minimale
Mises à jour HOT : gardez 'fillfactor' (par exemple, 90) sur les tables chaudes pour l'espace libre sur la page.
TOAST : gros JSONB/textes - suivez la compacte ; stocker les champs encombrants dans une table distincte.
UPSERT : utiliser 'ON CONFLICT... DO UPDAT'avec une logique 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) Transactions, blocages et concurrence
Niveaux d'isolation : 'READ COMMITTED'pour la plupart des chemins ;' REPETABLE READ '/' SERIALIZABLE 'est ponctuel (rapports, batchi hors ligne). Ne les gardez pas longtemps.
Types de verrous : row-level (pessimisme 'FOR UPDATE'), table-level (DDL), advisory locks pour les mutex distribués.
Deadlocks : transactions courtes, ordre unique de mise à jour des entités, temporisation ('lock _ timeout', 'statement _ timeout').
Files d'attente de tâches : 'SKIP LOCKED' pour le pool de workflow distribué.
sql
-- batch processor removes work without racing
SELECT id FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 100;7) Avtovakuum, statistiques et blog
VACUUM/ANALYZE : Gardez les statistiques à jour ('default _ statistics _ target'), tuner l'AV sur les tables chaudes (seuil de déclenchement inférieur).
Regardez wraparound (age (txid) <2 milliards), 'vacuum _ freeze _ min _ age'.
bloat control : reindex/CLUSTER réguliers sur les indices lourds en dehors des pics ; le lot réduit l'ampleur du problème.
sql
ALTER TABLE tx SET (
autovacuum_vacuum_scale_factor = 0. 05,
autovacuum_analyze_scale_factor = 0. 02,
autovacuum_vacuum_cost_limit = 4000
);8) Mémoire, chekpoints et WAL
Mémoire
'Shared _ buffers ': 20-25 % RAM (dépend du profil).
'Work _ mem ': par opération ! Configurez de manière conservatrice (par exemple, 8-64MB) et augmentez ponctuellement les rôles de rapport.
'Maintenance _ work _ mem ': index/restauration volumineux (512MB-2GB par tâche).
WAL et chekpoints
NVMe pour WAL, volume séparé ; 'wal _ compression = on'.
'checkpoint _ timeout' 10-15 min, 'max _ wal _ size' sous le volume des modifications, 'checkpoint _ completion _ target ≈ 0. 9`.
Pour les pics d'inserts, les inserts batch et les commits de groupe.
9) Réplication et tolérance aux pannes
Répliques leader : Synchrone à la plus proche (semi-sync) pour les RPO≈0 -30c ; asynchrone pour la lecture/analyse.
Promotion et fencing : Patroni/réplique-manager ; supprimer le « double leader ».
Hot standby : 'hot _ standby _ feedback' attention (croissance du bloat), mieux vaut dompter les longues transactions sur les répliques.
10) Backups et PITR
Backup complet + incrémental, copie offsite ; 'archive _ command' pour WAL.
PITR : vérifier le rétablissement à un point dans le temps sur les bancs ; Réglez RPO/RTO (portefeuilles - minutes, logs - dizaines de minutes).
Exercice DR (game day) : vérification régulière de la récupération.
11) JSONB et le modèle « circuit flexible »
JSONB - excellent pour les attributs rarement lus/variatifs (métadonnées KYC, paramètres PSP).
Valider strictement les champs obligatoires dans les colonnes relationnelles ; JSONB - pour la « queue » des nuances.
Indexation : point GIN par voie utilisée ; évitez « un GIN géant pour tout ».
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) Plein texte et recherche fuzzy
FTS intégré : 'tsvector' + GIN ; pour les erreurs typographiques - 'pg _ trgm'.
Pour une recherche lourde par logs/jeux - sortir dans le moteur de recherche (ES/OpenSearch), et dans PG stocker le lien/métadonnées.
13) Observabilité et profilage
pg_stat_statements : top des demandes lentes/fréquentes, normalisation.
EXPLAIN (ANALYZE, BUFFERS) : lire les plans, chercher seq scan sur les voies chaudes.
Métriques : TPS, p95/p99, checkpoints, 'replication _ lag', deadlocks, bloat, cycles AV, cache-hit ≥ 95 %.
Alert : croissance des lagunes, « idle in transaction », seq scan inattendu, tempête WAL.
14) Pool de connexions et demandes préparées
Puller (PgBouncer) en mode 'transaction' pour le trafic Web ; 'session' pour les curseurs/back-office longs.
Paramètres prédéfinis/paramétrage → moins de parsing, plans stables.
Limitez le nombre maximum de beckands ('max _ connections' bas ; le pool reprend le robinet).
15) Sécurité et conformité
TLS en transit, chiffrement des disques, KMS/clés externes.
RBAC : droits minimums, partage des rôles lecture/écriture/admin.
RLS (Row-Level Security) pour les scénarios multi-tenants.
PII : masquage/pseudonymisation, durée de conservation.
Audit : 'pgaudit '/déclencheurs d'audit sur les tables critiques (portefeuille/ledger).
16) Modèles types pour iGaming
16. 1 Portefeuille et ledger (cohérence stricte)
Индексы: `wallet(player_id PK)`, `ledger(player_id, ts DESC)` + `INCLUDE (delta_cents, reason)`.
La transaction met à jour le solde et écrit à ledger ; réplication semi-sync ; cache uniquement comme projection.
16. 2 Historique des paris (TPS élevé, lecture par joueur/temps)
Lot par jour/semaine, BRIN par heure + B-Tree « (player_id, created_at DESC) ».
Retensh via 'DETACH PARTITION' → archivage dans OLAP.
16. 3 Webhooks PSP (bursts, retraits)
File d'attente d'événements "bruts" (append-only), lots temporels, index partiel pour 'status =' pending ".
Idempotence par 'idempotency _ key '/' psp _ tx' (UNIQUE).
17) Chèque de mise en œuvre
1. Fixez la politique SLO et read-after-write.
2. Concevoir des index clés pour les requêtes réelles (profil de requête).
3. Activer le lot là où il y a des motifs de rétention/Append.
4. Configurez AV/ANALYZE sur les tables chaudes, suivez le blog/wraparound.
5. Mettez la mémoire, le WAL et les checkpoints sous les fenêtres de pointe.
6. Mettez le pool de connexions et 'pg _ stat _ statements' ; Faites des alertes.
7. Réplication + DR + PITR - obligatoire ; Faites des exercices.
8. Limiter le JSONB et le GIN aux seules voies nécessaires ; utilisez des indices partiels.
9. Réduisez la durée des transactions, utilisez 'SKIP LOCKED' pour les files d'attente.
10. Audit/PII/cryptage/modèle de rôle - avant le lancement des paiements.
18) Anti-modèles
Un index « universel » pour tout et aucune analyse des requêtes.
Longues transactions « pendantes » ('idle in transaction') → verrouillage/croissance de blog.
Compter sur les répliques pour read-after-write sans tenir compte du retard.
Stocker tout dans JSONB « au cas où » et indexer tout le document avec un seul GIN.
Zéro contrôle VACUUM/ANALYZE et pas de contrôle des lagunes/chekpoints.
Migrations massives/DDL aux heures de pointe.
19) Extraits utiles
Plan de requête et tampons
sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT amount_cents
FROM ledger
WHERE player_id = $1
ORDER BY ts DESC
LIMIT 100;Profilage des demandes « lourdes »
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 des lots (idée)
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');Résultats
PostgreSQL est capable de tirer « de l'argent et de la vérité » sur la plate-forme iGaming avec une augmentation linéaire de la charge - si les indices, les lots, l'autotakuum, WAL/mémoire, réplique/DR et l'observabilité sont disciplinés. Commencez par le profil des demandes et le SLO, construisez des indices pour les modèles réels, isolez les tables chaudes, incluez une hygiène opérationnelle stricte - et la base gardera les tournois et les paiements de pointe prévisibles.