PostgreSQL: best practices and indexing

(Section: Technology and Infrastructure)

Brief Summary

PostgreSQL is the "kernel of truth" for money, KYC and legally significant records in iGaming. It provides ACID guarantees, powerful SQL, and extensibility. To withstand the peaks of tournaments and PSP webhooks, they are critical: competent scheme, indices, partitioning, auto-vacuum, tuning WAL and observability. Below is the constructor of practices and templates.

1) Architecture and SLO

PostgreSQL role: leader for writing + replicas for reading; for hot screens - cache/projections (Redis/materialized views).
SLO examples: p99 'INSERT/UPDATE' wallet ≤ 25-40 ms; p99 balance reading ≤ 10-15 ms; replica lag ≤ 2-5 s; availability ≥ 99. 9%.
Read-after-write policy: user screens after a transaction are read from the leader or waiting for a replication lag.

2) Schematic design

Normalization of the core of money (wallets, ledger) + denormalization for reading (CQRS/projections).
Strict restrictions: 'NOT NULL', 'CHECK', 'UNIQUE', FK with targeted 'ON DELETE/UPDATE' (RESTRICT/SET NULL/NO ACTION).
Schema versioning: up/down migrations, feature flags; avoid breaking hot-time renaming.
Identifiers: 'BIGINT' + sequences (or ULID/UUIDv7 for distribution). For hot inserts - sequences in a separate tablespace/WAL volume.

3) Indexing: what, where and how

3. 1 B-Tree (default)

When: exact matches, ranges, sorts, 'JOIN' by FK.
Patterns: 'WHERE player_id =?', 'ORDER BY created_at DESC LIMIT 100'.

• Multi-column indexes - Match the order of the conditions.
• Covering: `INCLUDE (...)` для index-only scan.
• Individual index under 'ORDER BY... DESC 'on "tapes."

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

3. 2 GIN (JSONB, arrays, full text)

When: JSONB key/path search, tag arrays, FTS.
Variants: 'gin _ trgm _ ops' for trigrams, 'jsonb _ path _ ops '/' jsonb _ ops'.
Practice: Strictly limit search fields and think about cardinality.

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/ranges/signatures)

When: geolocation (IP-geo, radii), intervals, nearest-neighbor.
Less used in the core of money, useful for geo-restrictions/responsible play.

3. 4 BRIN (large "appendix" tables in time)

When: Billions of lines, natural correlation over time (betting/event logs).
Pros: small size, cheap service.
Cons: coarse selectivity → be combined with partitioning.

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

3. 5 Hash

Rarely needed: equality by one column in the absence of ranges; more often B-Tree is enough.

3. 6 Partial and expressions

Partial index: accelerates hot sub-sets (active, 'status =' pending ").
Expression index: precompute key ('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 Index Antipatterns

"Index for everything": an excess of indices slows down writing and VACUUM.
Duplicate indexes (same column set/order).
Index to a column with very low cardinality (for example, 'status' with 2 values) - do partial.

4) Partitioning

Why: reduce bloat, speed up VACUUM/scans, facilitate retention/archive.
Schemes: RANGE by date (day/week) for betting logs; HASH by 'player _ id' for large user tables; combined.
Rotation practice: create future parties in advance, 'ATTACH PARTITION', archive of old ones - 'DETACH' + movement.

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) Insertions/updates with minimal fragmentation

HOT updates: Keep 'fillfactor' (e.g. 90) on hot spreadsheets for free space on the page.
TOAST: large JSONB/texts - keep an eye on compaction; Store bulky fields in a separate table.
UPSERT: Use 'ON CONFLICT... DO UPDATE 'with idempotent logic.

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, locks and competition

Isolation levels: 'READ COMMITTED' for most paths; 'REPEATABLE READ '/' SERIALIZABLE' pointwise (reports, offline batches). Don't hold them for long.
Types of locks: row-level (pessimism 'FOR UPDATE'), table-level (DDL), advisory locks for distributed mutexes.
Deadlocks: short transactions, uniform order of updating entities, timeouts ('lock _ timeout', 'statement _ timeout').
Task queues: 'SKIP LOCKED' for distributed work pool.

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

7) Auto vacuum, statistics and bloat

VACUUM/ANALYZE: keep current statistics ('default _ statistics _ target'), tune AV on hot tables (trigger threshold below).
Watch out for wraparound (age (txid) <2 billion), 'vacuum _ freeze _ min _ age'.
bloat control: regular reindex/CLUSTER on heavy off-peak indices; partitioning reduces the scale of the problem.

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

8) Memory, checkpoints and WAL

Memory

'shared _ buffers': 20-25% RAM (profile dependent).
'work _ mem ': by operation! Configure conservatively (for example, 8-64MB) and increase pointwise for reporting roles.
'maintenance _ work _ mem ': large indexes/recovery (task 512MB-2GB).

WAL and checkpoints

NVMe for WAL, single volume; 'wal _ compression = on'.
'checkpoint _ timeout '10-15 min, 'max _ wal _ size' subject to change, 'checkpoint _ completion _ target ≈ 0. 9`.
For insert peaks - batch inserts and group commits.

9) Replication and fault tolerance

Leader replica: synchronous to the nearest (semi-sync) for RPO≈0 -30c; asynchronous for reads/analytics.

Promotion and fencing: Patroni/replica manager; exclude "dual leader."

Hot standby: 'hot _ standby _ feedback' carefully (bloat growth), better - tame long transactions on replicas.

10) Backups and PITRs

Full + incremental backup, offsite copies; 'archive _ command' for WAL.
PITR: check recovery to the time point on the stands; regulate RPO/RTO (wallets - minutes, logs - tens of minutes).
DR (game day) exercises: regular recovery check.

11) JSONB and the "flexible circuit" model

JSONB - excellent for rarely read/variable attributes (KYC metadata, PSP parameters).
Strictly validate required fields in relational columns; JSONB - for the "tail" of nuances.

Indexing: point GIN by paths used; avoid "one giant GIN for everything."

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) Full text and fuzzy search

Built-in FTS: 'tsvector' + GIN; for typos - 'pg _ trgm'.
For a difficult search by logs/games - take out to the search engine (ES/OpenSearch), and store the link/metadata in PG.

13) Observability and profiling

pg_stat_statements: top slow/frequent queries, normalization.
EXPLAIN (ANALYZE, BUFFERS): read plans, look for seq scan on hot tracks.
Metrics: TPS, p95/p99, checkpoints, 'replication _ lag', deadlocks, bloat, AV loops, cache-hit ≥ 95%.
Alerts: growth of lags, "idle in transaction," unexpected seq scan, WAL storm.

14) Connection pool and prepared queries

Puller (PgBouncer) in 'transaction' mode for web traffic; 'session' - for long cursors/back office.
Prepared statements/parameterization → less parsing, stable plans.
Limit the maximum number of backends ('max _ connections' low; the pool is taken over by the valve).

15) Safety and compliance

TLS in transit, disk encryption, KMS/foreign keys.
RBAC: minimal rights, separation of read/write/admin roles.
RLS (Row-Level Security) for multi-tenant scenarios.
PII: masking/aliasing, shelf life.
Audit: 'pgaudit '/audit triggers on critical tables (wallet/ledger).

16) Typical templates for iGaming

16. 1 Wallet and ledger (strict consistency)

Индексы: `wallet(player_id PK)`, `ledger(player_id, ts DESC)` + `INCLUDE (delta_cents, reason)`.
The transaction updates the balance and writes to the ledger; semi-sync replication; cache as projection only.

16. 2 Betting History (High TPS, Player/Time Reading)

Partitioning by day/week, BRIN by time + B-Tree '(player_id, created_at DESC)'.
Retention via'DETACH PARTITION '→ archiving to OLAP.

16. 3 Webhooks PSP (burstami, retrai)

Queue of "raw" events (append-only), time partitions, partial index on'status = 'pending. "

Idempotency by 'idempotency _ key '/' psp _ tx' (UNIQUE).

17) Implementation checklist

1. Commit the SLO and read-after-write policy.
2. Design key indexes for real queries (query profile).
3. Include partitioning where there are retention/appendix patterns.
4. Set up AV/ANALYZE on hot tables, keep an eye on bloat/wraparound.
5. Tune memory, WAL and checkpoints for peak windows.
6. Put the connection pool and 'pg _ stat _ statements'; get alerts.
7. Replication + DR + PITR - required; conduct exercises.
8. Limit JSONB and GIN to only the required paths; use partial indexes.
9. Minimize transaction duration, use 'SKIP LOCKED' for work queues.
10. Audit/PII/encryption/role model - before starting payments.

18) Antipatterns

One "universal" index on everything and no query analysis.
Long "hanging" transactions ('idle in transaction') → blocking/bloat growth.
Rely on replicas for read-after-write without lag.
Store everything in JSONB "just in case" and index the entire document with one GIN.
Zero control VACUUM/ANALYZE and no monitoring of lags/checkpoints.
Mass migrations/DDL during peak hours.

19) Useful snippets

Query plan and buffers

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

Heavy query profiling

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;

Party rotation (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');

Summary

PostgreSQL is able to pull "money and truth" on the iGaming platform with a linear increase in load - if indices, parties, auto-vacuum, WAL/memory, replica/DR and observability are disciplined. Start with a profile of queries and SLOs, build indexes for real patterns, isolate hot tables, turn on strict operational hygiene - and the base will predictably keep tournaments and peak payments.