PostgreSQL: mejores prácticas e indexación
(Sección: Tecnologías e Infraestructura)
Resumen breve
PostgreSQL es el «núcleo de la verdad» para el dinero, KYC y registros legalmente significativos en iGaming. Proporciona garantía ACID, SQL potente y extensibilidad. Para soportar los picos de los torneos y los PSP-webhooks, son críticos: esquema competente, índices, partituras, autovacuum, afinación WAL y observabilidad. A continuación, un diseñador de prácticas y plantillas.
1) Arquitectura y SLO
Función PostgreSQL: líder en escritura + réplicas por lectura; para las pantallas calientes - caché/proyecciones (Redis/vistas materializadas).
Ejemplos de SLO: p99 'INSERT/UPDATE' monedero ≤ 25-40 ms; p99 lectura de balance ≤ 10-15 ms; lag réplicas ≤ 2-5 s; disponibilidad ≥ 99. 9%.
Política read-after-write: Las pantallas personalizadas después de una transacción leen con el líder o esperan una réplica.
2) Diseño de circuitos
Normalización del núcleo del dinero (carteras, ledger) + desnormalización para lectura (CQRS/proyección).
Restricciones estrictas: 'NOT NULL', 'CHECK', 'UNIQUE', FK con objetivo 'ON DELETE/UPDATE' (REFRICT/SET NULL/NO AO ACTION).
Versionar esquema: migraciones up/down, flags feature; evite los cambios de nombre en las horas calientes.
Identificadores: 'BIGINT' + secuencias (o ULID/UUIDv7 para la distribución). Para inserciones en caliente: secuencias en un volumen de tablespace/WAL separado.
3) Indización: qué, dónde y cómo
3. 1 B-Tree (default)
Cuándo: coincidencias exactas, rangos, ordenaciones, 'JOIN' por FK.
Patrones: 'WHERE player_id =?', 'ORDER BY created_at DESC LIMIT 100'.
- Índices de múltiples columnas: cumpla con el orden de las condiciones.
- Covering: `INCLUDE (...)` для index-only scan.
- Un índice separado bajo 'ORDER BY... DESC 'en «cintas».
sql
CREATE INDEX idx_tx_player_created_desc
ON tx (player_id, created_at DESC) INCLUDE (amount_cents, status);3. 2 GIN (JSONB, matrices, texto completo)
Cuándo: Búsqueda JSONB por claves/ruta, arreglos de etiquetas, FTS.
Las opciones son: 'gin _ trgm _ ops' para trigramas,' jsonb _ path _ ops '/' jsonb _ ops'.
Práctica: limite estrictamente los campos de búsqueda y piense en la cardinalidad.
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/rangos/firmas)
Cuándo: geolocalización (geo IP, radios), intervalos, nearest-neighbor.
Menos utilizado en el núcleo del dinero, útil para las restricciones geográficas/juego responsable.
3. 4 BRIN (grandes «apéndice» -tablis en el tiempo)
Cuándo: miles de millones de líneas, correlación natural en el tiempo (registros de apuestas/eventos).
Pros: tamaño pequeño, servicio barato.
Contras: la selectividad burda → combinarse con el partido.
sql
CREATE INDEX idx_bets_brin ON bets USING BRIN (created_at) WITH (pages_per_range = 64);3. 5 Hash
Rara vez se necesita: igualdad de una columna a la vez en ausencia de rangos; Más a menudo, B-Tree es suficiente.
3. 6 Parciales y expresiones
Índice parcial: acelera los debajo-conjuntos calientes (activos, 'status =' pending ").
Índice de expresión: anticipación de clave ('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 Índices antipattern
«Índice para todo»: el exceso de índices frena el registro y VACUUM.
Índices duplicados (el mismo conjunto de columnas/orden).
Índice por columna con cardinalidad muy baja (por ejemplo, 'status' con 2 valores) - hacer parcial.
4) Partición
Por qué: reducir bloat, acelerar VACUUM/escáneres, facilitar retention/archive.
Esquemas: RANGE por fecha (día/semana) para los registros de apuestas; HASH por 'player _ id' para tablas personalizadas de gran tamaño; combinado.
Práctica de rotación: crear por adelantado futuros lotes, 'ATTACH PARTITION', archivo de los antiguos - 'DETACH' + movimiento.
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) Inserciones/actualizaciones con una fragmentación mínima
Actualizaciones HOT: mantenga 'fillfactor' (por ejemplo, 90) en tablas calientes para espacio libre en la página.
TOAST: grandes JSONB/textos - seguir la compactación; almacene los campos voluminosos en una tabla separada.
UPSERT: usa 'ON CONFLICT... DO UPDATE 'con lógica 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) Transacciones, bloqueos y competencia
Niveles de aislamiento: 'READ COMMITTED' para la mayoría de las rutas; 'REPEATABLE READ '/' SERIALIZABLE' puntualmente (informes, batches offline). No los mantengas por mucho tiempo.
Tipos de bloqueo: row-level (pesimismo 'FOR UPDATE'), table-level (DDL), locks de asesoramiento para mutex distribuidos.
Deadlocks: transacciones cortas, orden único de actualización de entidades, temporizadores ('lock _ timeout', 'statement _ timeout').
Colas de tareas: 'SKIP LOCKED' para el grupo de tareas distribuido.
sql
-- batch processor removes work without racing
SELECT id FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 100;7) Autovacuum, estadísticas y bloat
VACUUM/ANALYZE: Mantenga las estadísticas actualizadas ('default _ statistics _ target'), afine AV en tablas calientes (el umbral de activación es inferior).
Siga wraparound (age (txid) <2.000 millones), 'vacuum _ freeze _ min _ age'.
bloat-control: reindex/CLUSTER regulares en índices pesados fuera de los picos; la partición reduce la escala 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, cheques y WAL
Memoria
'shared _ bufers': 20-25% RAM (depende del perfil).
'work _ amb': por operación! Configure de forma conservadora (por ejemplo, 8-64MB) y aumente puntualmente las funciones de informes.
'maintenance _ work _ amb': grandes índices/recuperación (512MB-2GB por tarea).
WAL y cheques
NVMe para WAL, volumen separado; 'wal _ compression = on'.
'checkpoint _ timeout' 10-15 min, 'max _ wal _ size' por volumen de cambios, 'checkpoint _ completion _ target ≈ 0. 9`.
Para picos de inserción, inserciones de batch y commits de grupo.
9) Replicación y tolerancia a fallas
Replica líder: sincronizado con el más cercano (semi-sync) para RPO≈0 -30s; asíncrono para lecturas/análisis.
Promoción y fencing: Patroni/Replica-manager; eliminar el «doble líder».
Hot standby: 'hot _ standby _ feedback' cuidadosamente (crecimiento de bloat), mejor - doma las transacciones largas en las réplicas.
10) Backups y PITR
Backup completo + incremental, copias offsite; 'archive _ command' para WAL.
PITR: compruebe la recuperación a un punto en el tiempo en los stands; regular RPO/RTO (monederos - minutos, registros - decenas de minutos).
Ejercicios de DR (día del juego): verificación regular de la recuperación.
11) JSONB y modelo de «esquema flexible»
JSONB es excelente para atributos raramente legibles/variables (metadatos KYC, parámetros PSP).
Valide estrictamente los campos obligatorios en las columnas relacionales; JSONB - para matices de «cola».
Indización: GIN puntual sobre las rutas utilizadas; evite «un GIN gigante para todo».
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) Texto completo y búsqueda fuzzy
FTS incorporado: 'tsvector' + GIN; para los errores tipográficos - 'pg _ trgm'.
Para una búsqueda pesada por logs/juegos, lleve al motor de búsqueda (ES/OpenSearch), y en PG almacene el enlace/metadatos.
13) Observabilidad y elaboración de perfiles
pg_stat_statements: superior de consultas lentas/frecuentes, normalización.
EXPLAIN (ANALYZE, BUFFERS): lee los planes, busca el seq scan en las vías calientes.
Métricas: TPS, p95/p99, checkpoints, 'replication _ lag', deadlocks, bloat, ciclos AV, cache-hit ≥ 95%.
Alertas: crecimiento de las lagunas, «idle in transaction», inesperado seq scan, tormenta WAL.
14) Grupo de conexiones y solicitudes preparadas
Puller (PgBouncer) en modo 'transaction' para tráfico web; 'session' es para cursores largos/back office.
Estados preparados/parametrización → menos parsing, planes estables.
Limite el número máximo de buckends ('max _ connections' bajo; la piscina toma la válvula).
15) Seguridad y cumplimiento
TLS en tránsito, cifrado de disco, claves KMS/externas.
RBAC: derechos mínimos, separación de roles lectura/escritura/administración.
RLS (Row-Level Security) para scripts multi-tenant.
PII: enmascaramiento/seudonimización, vida útil.
Auditoría: 'pgaudit '/desencadenantes de auditoría en tablas críticas (monedero/ledger).
16) Plantillas típicas para iGaming
16. 1 Monedero y ledger (coherencia estricta)
Индексы: `wallet(player_id PK)`, `ledger(player_id, ts DESC)` + `INCLUDE (delta_cents, reason)`.
La transacción actualiza el balance y escribe en ledger; replicación semi-sync; caché sólo como proyección.
16. 2 Historial de apuestas (TPS alto, lectura por jugador/tiempo)
Partido por día/semana, BRIN por hora + B-Tree '(player_id, created_at DESC)'.
El retiro a través de 'DETACH PARTITION' → archivado en OLAP.
16. 3 Webhooks PSP (burstami, retrai)
Cola de eventos "crudos" (append-only), lotes según el tiempo, índice parcial por 'status =' pending ".
Idempotencia por 'idempotency _ key '/' psp _ tx' (UNIQUE).
17) Lista de verificación de implementación
1. Fije la política SLO y read-after-write.
2. Diseñar índices clave para consultas reales (perfil de consulta).
3. Habilite el lote donde haya patrones de retransmisión/append.
4. Configure AV/ANALYZE en las tablas calientes, siga bloat/wraparound.
5. Afine la memoria, WAL y los puntos de comprobación debajo de las ventanas de pico.
6. Suministre la agrupación de conexiones y 'pg _ stat _ statements'; montar alertas.
7. Replicación + DR + PITR: obligatorio; lleven a cabo ejercicios.
8. Limite JSONB y GIN sólo a los caminos necesarios; use índices parciales.
9. Minimice la duración de las transacciones, utilice 'SKIP LOCKED' para las colas de texto.
10. Auditoría/PII/cifrado/modelo de rol - antes de iniciar los pagos.
18) Antipattern
Un índice «universal» para todo y la falta de análisis de consultas.
Transacciones largas «colgantes» ('idle in transaction') → bloqueos/crecimiento de bloat.
Confíe en las réplicas para leer-después-escribir sin tener en cuenta la laguna.
Almacenar todo en JSONB «por si acaso» e indexar todo el documento con una sola GIN.
Control cero de VACUUM/ANALYZE y falta de monitoreo de lags/checkpoints.
Migraciones masivas/DDL en horas de pico.
19) Snippets útiles
Plan de consulta y búfer
sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT amount_cents
FROM ledger
WHERE player_id = $1
ORDER BY ts DESC
LIMIT 100;Perfiles de consultas «pesadas»
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;Rotación de partidos (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');Resultados
PostgreSQL es capaz de tirar de «dinero y verdad» en una plataforma iGaming con un crecimiento de carga lineal - si los índices, lotes, autovacuum, WAL/memoria, réplica/DR y observabilidad están disciplinados. Comience con un perfil de consultas y SLO, construya índices bajo patrones reales, aísle las mesas calientes, incluya una estricta higiene operativa, y la base será predecible para mantener los torneos y los pagos máximos.