PostgreSQL: best pratices e indenização

(Secção Tecnologia e Infraestrutura)

Resumo curto

PostgreSQL é um «núcleo da verdade» para dinheiro, KYC e registros legalmente significativos no iGaming. Ele oferece garantias ACID, SQL poderoso e extensibilidade. Para suportar os picos de torneios e webhooks PSP, são críticos: padrão de habilitação, índices, particionamento, autódromo, sintonização WAL e observabilidade. Abaixo, o construtor de práticas e modelos.

1) Arquitetura e SLO

Papel PostgreSQL: líder de gravação + réplicas de leitura; para telas quentes - em dinheiro/projeção (Redis/representações materializadas).
SLO exemplos: p99 'INSERT/UPDATE' carteira ≤ 25-40 ms; p99 leitura de balanço ≤ 10-15 ms; réplicas duplas ≤ 2-5 c; disponibilidade ≥ 99. 9%.
Política de read-after-write: telas personalizadas após transação são lidas a partir do líder ou à espera de uma liga de replicação.

2) Design de esquemas

Normalização do núcleo de dinheiro (carteiras, ledger) + desnormalização para leitura (CQRS/projeções).
Restrições rigorosas: 'NOT NULL', 'CHECK', 'UNIQUE', FK com 'ON DELETE/UPDATE' (RESTRICT/SET NULL/NO ACTION).
Versionagem de padrão: migração up/down, função-bandeiras; evite renomeações de quebra de nome em horários quentes.
Identificadores: 'BIGINT' + seqüência (ou ULID/UUIDv7 para distribuição). Para inserções quentes, seqüências em tablespace/volume WAL separado.

3) Indexação: o quê, onde e como

3. 1 B-Tree (default)

Quando: correspondências exatas, faixas, triagens, 'JOIN' por FK.
Pattern: 'WHERE player _ id =', 'ORDER BY created _ at DESC LIMIT 100'.

• Índices multicolonais - cumpra a ordem das condições.
• Covering: `INCLUDE (...)` для index-only scan.
• Índice separado sob 'ORDER BY... DESC 'em fitas.

sql
CREATE INDEX idx_tx_player_created_desc
ON tx (player_id, created_at DESC) INCLUDE (amount_cents, status);

3. 2 GIN (JSONB, matrizes, completos)

Quando: Pesquisa JSONB por chave/caminho, matrizes de marcas de formatação, FTS.
As opções são 'gin _ trgm _ ops' para os trigramas,' jsonb _ path _ ops '/' jsonb _ ops'.
Prática: Limite severamente os campos de busca e pense na radicalidade.

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/faixa/assinatura)

Quando: geolocalização (IP-geo, radios), intervalos, nearest-neighbor.
Menos usado em um núcleo de dinheiro, útil para geo-limitações/jogo responsável.

3. 4 BRIN («apêndice» grande - tempo)

Quando: bilhões de linhas, correlação natural de tempo (revistas de apostas/eventos).
Os benefícios são pequenos, um serviço barato.
Contras: Seletividade bruta → combinar com partidarização.

sql
CREATE INDEX idx_bets_brin ON bets USING BRIN (created_at) WITH (pages_per_range = 64);

3. 5 Hash

Raramente necessário: igualdade de uma coluna quando não há intervalo; Mais frequentemente, o B-Tree é suficiente.

3. 6 Parciais e expressões

Parcial Index: acelera as quentes abaixo-múltiplas (ativas, 'status =' pending ').
Expression index: antecipação da chave ('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 antipatters

Índice para tudo: excesso de índices freia gravação e VACUUM.
Índices duplicados (mesmo conjunto de colunas/ordem).
Índice para coluna muito baixa (por exemplo, 'status' com 2 valores) - Faça uma parcial.

4) Particionamento

Porquê: reduzir o bloat, acelerar o VACUUM/scan, facilitar a retenção/arquivo.
Esquemas: RANGE por data (dia/semana) para os logs de apostas; HASH por 'player _ id' para grandes tabelas personalizadas; combinado.
Prática de rotatividade: criar futuras partituras, 'ATTACH PARTITION', arquivo antigo - 'DETACH' + movimento.

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ções/atualizações com fragmentação mínima

Atualizações HOT: mantenha 'phillfator' (por exemplo, 90) em tabelas quentes para espaço disponível na página.
TOAST: grandes textos JSONB/texto - acompanhe o compacto; guarde os campos pesados em uma tabela separada.
UPSERT: use 'ON CONFLICT... O DO UPDATE 'com uma lógica idumpotente.

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) Transações, bloqueios e concorrência

Níveis de isolamento: 'READ COMMITTED' para a maioria dos caminhos; 'REPEATABLE READ '/' SERIALIZÁVEL' (relatórios, batches off-line). Não os mantenha por muito tempo.
Tipos de bloqueio: row-level (pessimismo 'FOR UPDATE'), tablet-level (DDL), advisory locks para mutex distribuídos.
Deadlocks: transações curtas, ordem única de atualização de entidades, temporizações ('lock _ timeout', 'statement _ timeout').
As filas de tarefas são 'SKIP LOCKED' para o pool de work distribuído.

sql
-- batch processor removes work without racing
SELECT id FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 100;

7) Carro automático, estatísticas e bloat

VACUUM/ANALYZE: Mantenha as estatísticas atualizadas ('default _ estatísticas _ target') e aponte AV em tabelas quentes (limite de ativação abaixo).
Siga o wraparound (age (txid) <2 bilhões), 'vacuum _ freeze _ min _ age'.
controle bloat: regularmente reindex/CLUSTER em índices pesados fora dos picos; a partitização reduz a escala do problema.

sql
ALTER TABLE tx SET (
autovacuum_vacuum_scale_factor = 0. 05,
autovacuum_analyze_scale_factor = 0. 02,
autovacuum_vacuum_cost_limit = 4000
);

8) Memória, Checkpoint e WAL

Memória

'shared _ buffers': 20-25% RAM (dependente de perfil).
'work _ mem': por operação! Configure de forma conservadora (por exemplo, 8-64MB) e aumente pontualmente para os papéis de relatório.
'maintenance _ work _ mem': grandes índices/restauração (512MB-2GB por tarefa).

WAL e checkpoint

NVMe para WAL, volume separado; 'wal _ compressão = on'.
'checkpoint _ timeout' 10-15 min, 'max _ wal _ size' sob o volume de alterações, 'checkpoint _ complition _ target ≈ 0. 9`.
Para picos de inserção, colagens de batch e grupos.

9) Replicação e resistência a falhas

Líder de réplica: sincronizada ao mais próximo (semi-sync) para RPO≈0 -30s; asincrões para leitura/analistas.
Promoção e fencing: Patroni/réplica-gerente; excluir «líder duplo».
Hot standby: 'hot _ standby _ feedback' com cuidado (crescimento bloat), melhor: dome transações longas em réplicas.

10) Bacapes e PITR

Backup completo + incorporativo, cópias off-site; 'archive _ command' para WAL.
PITR: verifique a recuperação até o ponto de tempo dos estandes; regula RPO/RTO (carteiras - minutos, logs - dezenas de minutos).
Exercício DR. (game day): Verificação regular de recuperação.

11) JSONB e modelo de «esquema flexível»

JSONB é ótimo para atributos raramente lidos/variáveis (metadados KYC, parâmetros PSP).
Valide rigorosamente os campos obrigatórios nas colunas relacionais; JSONB - para «cauda» de nuances.
Indexação: GIN por pontos nos caminhos usados; evite «um GIN gigante para tudo».

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) Pesquisa completa e fuzzy

FTS incorporado: 'tsvector' + GIN; para os arquivos - 'pg _ trgm'.
Para a busca pesada por logs/jogos, remova para o motor de busca (ES/OpenSearch) e armazene o link/metadados no PG.

13) Observabilidade e perfilagem

pg _ stat _ statents: top de consultas lentas/frequentes, normalização.
EXPLAIN (ANALYZE, BUFFERS): leia os planos, procure o seq scan em vias quentes.
Métricas: TPS, p95/p99, checkpoint, 'reprodução _ lag', deadlocks, bloat, ciclos AV, cachê-hit ≥ 95%.
Alerts, crescimento das lajes, idle in transation, seq scan inesperado, tempestade WAL.

14) Pool de conexões e consultas preparadas

Poller (PgBouncer) em modo de 'transmissão' para tráfego na Web; 'sessions' para longos cursores/back-office.
Prepared statents/configuração → menos parsing, planos estáveis.
Limite o número máximo de becendes ('max _ connections' baixo; o pool toma conta da ventela).

15) Segurança e Complacência

TLS em trânsito, criptografia de disco, KMS/chaves externas.
RBAC: direitos mínimos, divisão de papéis leitura/gravação/admin.
RLS (Row-Level Security) para cenários multi-tenentes.
PII: camuflagem/pseudonimização, prazo de armazenamento.
Auditoria: 'pgaudit '/desencadeadores de auditoria em tabelas críticas (carteira/ledger).

16) Modelos de iGaming

16. 1 Carteira e ledger (rigorosa coerência)

Индексы: `wallet(player_id PK)`, `ledger(player_id, ts DESC)` + `INCLUDE (delta_cents, reason)`.
A transação atualiza o balanço e escreve para ledger; replicação semi-sync; O dinheiro é apenas uma projeção.

16. 2 Histórico de apostas (TPS alto, leitura por jogador/tempo)

Particionamento dia/semana, BRIN hora + B-Tree '(player _ id, created _ at DESC)'.
Retenshn através de 'DETACH PARTITION' → arquivamento em OLAP.

16. 3 Webhooks PSP (buracos, retais)

Fila de eventos crus (append-only), partituras de tempo, índice parcial por 'status =' pending '.
Idempotidade por 'idempotency _ key '/' psp _ tx' (UNIQUE).

17) Folha de cheque de implementação

1. Fixe a política SLO e read-after-write.
2. Projete os índices-chave para os pedidos reais (perfil de solicitação).
3. Ative a partitização onde houver retensas/apend-pattern.
4. Configure o AV/ANALYZE em tabelas quentes, acompanhe o bloat/wraparound.
5. Use memória, WAL e checkpoint para as janelas de pico.
6. Coloque um pool de conexões e 'pg _ stat _ statents'; Comecem as alertas.
7. Replicação + DR. + PITR - obrigatório; Faça os ensinamentos.
8. Limite o JSONB e o GIN apenas aos caminhos necessários; use partial indexes.
9. Minimize a duração das transações e use 'SKIP LOCKED' para as filas work.
10. Auditoria/PII/criptografia/rol modelo - antes de iniciar pagamentos.

18) Antipattern

Um índice «universal» para todos e nenhuma análise de solicitação.
Transações «penduradas» longas («idle in direction») → bloqueio/crescimento do bloat.
Basear-se em réplicas para read-after-write sem incluir a laje.
Armazenar tudo no JSONB «por precaução» e indexar todo o documento com um GIN.
Controle zero VACUUM/ANALYZE e falta de monitoramento de lajes/checkpoint.
Migração em massa/DDL no relógio de pico.

19) Snippets úteis

Plano de consulta e tampão

sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT amount_cents
FROM ledger
WHERE player_id = $1
ORDER BY ts DESC
LIMIT 100;

Perfilar 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;

Rotação de partições (ideia)

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');

Resumo

PostgreSQL é capaz de puxar «dinheiro e verdade» em uma plataforma iGaming com aumento linear de carga - se os índices, partituras, autossuficiência, WAL/memória, réplica/DR. e observabilidade são disciplinados. Comece com o perfil de solicitação e SLO, construa índices para pattern reais, isole as tabelas quentes, inclua a higiene operacional rigorosa - e a base vai manter previsivelmente os torneios e pagamentos de pico.