Database Sharding and Replication

(Section: Technology and Infrastructure)

Brief summary

For iGaming platforms, traffic growth (bets, deposits, PSP webhooks, game events) and availability requirements (≈99. 9–99. 99%) quickly hit the limit of one DB. Replication provides horizontal read scaling and fault tolerance; sharding - horizontal scaling of record and data. The key is the conscious compromises of PACELC (after failure: CA/P, otherwise: Latency vs Consistency), clear SLOs and scheme/key discipline.

Terms and models

Replication - Copies data between sites.

Leader-Follower (Primary-Replica): one entry → many reads.
Multi-Leader (Active-Active): entries in several regions, conflicts/merge.
Consensus-replication (Raft/Paxos, NewSQL): quorum records (Cassandra/Scylla - AP quorums, CockroachDB/Yugabyte - CP quorums).
Sync/Semi-sync/Async: delay balance vs RPO.
Sharding - horizontal division of tables/keys by shards.

Hash-sharding (uniformity, harder ranges).
Range-sharding (key ranges, hot end risk).
Consistent hashing.
Geo-sharding (by region/jurisdiction).
Functional sharding (by domain: payments/rates/CRM).

When and what to choose in iGaming

Replication only (no sharding) - when the main problem is reading: event feeds, reports, public directories. Records are placed in one leader, reads from replicas.
Sharding - when the write/hold bottleneck: rate flow, balance sheet transactions, trigger events.

Multi-region:

Player/PSP latency → local reads from replicas.
Regulation (data localization) → geo-sharding.
Interregional DR → asynchronous replica + switchover plan.

PACELC and warranty properties

CAP: with a split network, select C (consistency) or A (availability).
PACELC: if there are no failures, choose between Latency (L) and Consistency (C).
Cash ways: usually C-oriented (CP/strict serializable or Serializable + business idempotency).
Less critical subsystems (log clicks, directories): L-oriented (AP/EC, eventual).

Replication Practices

Leader–Follower

Writes → leader, reads, → read scaling.
Read-after-write: for user operations, read from the leader or wait for lag (check 'last _ committed _ lsn '/' wait _ for _ replay _ lag').
Semi-sync on critical paths (RPO reduction at the cost of latency).
Failover: automatic (patroni/raft coordinator) + fencing (so that there is no double leader).

Multi-Leader

Suitable for split domains and low conflict (e.g. content/settings), but not for a single player account without special measures.
Merge policies: last-write-wins, CRDT, domain consolidation rules.

Consensus/Quorum Databases

Quorum write (e.g. 'WRITE QUORUM'), quorum read ('READ QUORUM') → strong/configurable consistency.
Consider inter-AZ/region latency and quorum cost.

Sharding: Strategies and Key Choices

How to choose a key

Stable distribution by player_id/ account_id/ bet_id.
Avoid monotonous keys (auto-increment) in range-sharding - "hot" tail.
For payments - often 'player _ id' or 'account _ id'; for logs - 'event _ time' + bucketing; for content - 'tenant _ id'.

Strategy

Hash-sharding by player_id: balance on the flow of rates/balances.
Range-based time-based sharding for analytics/archives.
Geo-sharding: EU players → EU-shard (compliance with local laws).
Hybrid: hash within region + geo by jurisdiction.

Fighting hot keys

Key-salting (add salt/bucket to the key).
Write-throttling is essentially a command queue (serial executor).
Materialize "aggregates" (balance) in a separate row with a sequence queue.

Cross-shard operations

Money transfer/compensation: avoid 2PC on hot tracks.
Saga pattern: split into local transactions + compensatory actions, hard idempotence and outbox.
2PC/commit protocols: permissible point (back-office batches), but expensive in latency and fault tolerance.
Projections: read models for cross-domain screens, updated from the stream.

Schemes, indices and evolution

Schema versioning: migrations from back-compat, feature-flags on code.
Indexes on sharding keys and frequent queries; avoid cross-shard join (do pre-join/denormalization).
For JSON/docking storages - validate schemes (JSON-Schema/Protobuf) and TTL for "noisy" collections.

Online scaling and re-harding

Plan to N≫tekushcheye the number of virtual shards (slots) → flexible rebalance.
Consistent hashing or "virtual nodes" for soft node addition.

Online Resharding:

double entry (old + new shard), consistency validation;
background copies of chunks (logical dump/table move/streaming clone);
switch by "marker" + observation window, then double-record.
Moving the leader without downtime: switching roles, draining connections.

SLO, observability and alerting

SLO write/read: p99 ≤ X ms on hot tables, valid lag replicas ≤ Y seconds, availability ≥ Z.
Metrics: TPS, p95/p99, replication lag, multi-leader, retry rate, deadlocks, lock wait, cache hit ratio, IOPS/latency disk.
Trace: 'trace _ id' in database requests, associate with broker/event bus.
Canary queries and synthetic transactions for early detection of degradation.

Security and Compliance

Encryption at rest and in transit (TLS), key rotation.
RBAC/ACL, segmentation by domain/tenant, separate clusters for payments/LCC.
Data localization (EU/TR/LATAM) - combine geo-sharding and retention policies.
Audit: who and what read/rules; PII masking; Export Audit.

Backups, PITR, DR

Full + incremental backups, offsite storage.
PITR (point-in-time recovery) for leader clusters.

RPO/RTO:

Critical domains (balance/payment) - RPO≈0 -30s (semi-sync or frequent WAL-shipping), RTO ≤ minutes with automatic failover.
Less critical - RPO up to minutes/hours.
DR exercises (game day) and a documented switch runbook.

Performance and tuning (brief)

Memory/cache: increase buffers (shared buffers/innodb buffer pool), monitor cache-hit ≥ 95%.
Magazine/engine: fast NVMe, separate volume under WAL/redo.
Connection pool (PgBouncer/Hikari).
Planner/statistics: auto-analysis/auto-vacuum (Postgres), GC compression/tuning (LSM engines).
Quorum/replica factor: balance between p99 and fault tolerance.

Typical topologies for iGaming

1) Balances and payments (CP-loop)

Leader-Follower in the player's region, semi-sync to a close replica.
Hash-sharding by 'account _ id'.
Readings "after writing" - from the leader; projections to Redis for API-latency.
Outbox → event bus for calculations/analytics.

2) Betting history/gaming events (AP-oriented log)

Range-sharding by time or hash by 'player _ id' in column/LSM storage.
Asynchronous replicas for reporting/OLAP.
Eventual consistency is acceptable, bandwidth is more important.

3) Profiles/CRM (Multi-region read, localization)

Geo-sharding by jurisdiction, local replicas for readings.
Entries through the nearest leader; cross-region - asynchronously + conflict resolution for non-critical fields only.

Examples (conceptual)

Postgres: declarative sharding by 'player _ id'

sql
CREATE TABLE player_wallet (
player_id BIGINT NOT NULL,
balance_cents BIGINT NOT NULL,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (player_id)
) PARTITION BY HASH (player_id);

CREATE TABLE player_wallet_p0 PARTITION OF player_wallet FOR VALUES WITH (MODULUS 32, REMAINDER 0);
--... p1..p31

-- Репликация: публикация WAL на реплики, синхронность для «горячего» региона.
ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (replica_eu1, replica_eu2)';

Quorum recording (pseudo)


WRITE CL=QUORUM  -- запись подтверждена большинством реплик
READ CL=LOCAL_QUORUM -- локальный кворум для низкой задержки

Saga instead of 2PC (simplified)

1. Write off the deposit to shard-A (idempotent).
2. Send event "removed" → payment service (shard-B).
3. If step 2 fails, compensate step 1 with a "return" event.

Implementation checklist

1. Define data domains and SLOs (p99, RPO/RTO, replica log).
2. Select the replication model (leader/follower, quorum) and the sharding strategy.
3. Fix the sharding keys and the scheme (immutable!).
4. Type read-after-write policy and read routing.
5. Design online resharding (virtual shards, double entry).
6. Ensure idempotency and outbox for events/commands.
7. Set up backups, PITR, DR and regular exercises.
8. Include observability: lag, quorums, hot keys, conflicts.
9. Document runbook: failover, split-brain, degradation.
10. Perform load/chaos tests under match peaks.

Anti-patterns

One giant shard "for everything" and "then cut."

Cross-shard join's in the hot way of the request.
No read-after-write policy (floating bugs).
Migrations of schemes "breaking" sharding keys.
Multi-leader for cash accounts without strict conflict resolution.
No PITR/DR - Unable to recover from logical error.

Results

Replication solves reads and resiliency, sharding solves writes and volume. The successful architecture in iGaming is clear SLO and PACELC compromises, stable sharding keys, a minimum of cross-shard coordination (saga instead of 2PC), read-after-write discipline, well-functioning online resharding and regular DR exercises. This approach scales to tournament peaks, withstands regulatory restrictions on data localization and remains predictable in operation.