Database Architecture for High-Transaction iGaming Platforms

An iGaming database can fail the business while its uptime dashboard stays green. Two concurrent wagers spend the same funds. A reporting query slows wallet commits. After failover, an acknowledged payment is missing from the promoted database. Each failure concerns a different property: consistency, workload isolation or recovery.

Design the data layer around those properties before choosing a topology. This guide explains where player records, financial movements, game events and bets need different treatment, how to scale reads without weakening money decisions, and which tests make an architecture credible. PostgreSQL and MySQL documentation provides specific examples; the proposed design must be checked against the engine and version you actually operate.

Separate responsibilities before splitting databases

One database is not automatically a bottleneck, and one database per service is not automatically safe. Start with logical ownership: which component may change a player’s status, commit a financial movement, accept a bet or publish a report? Other components should use explicit contracts rather than writing into another domain’s tables.

A relational database with separate schemas or tables can be a sensible starting point when a small team needs local transactions and straightforward operations. Separate deployments become useful when measured workload contention, access boundaries or different recovery needs justify their cost. Each split introduces a boundary across which a local transaction can no longer protect all changes.

• Player domain: identity references, account status and policy decisions.
• Financial domain: ledger entries, balances, reservations and payment state.
• Game and betting domains: provider events, rounds, accepted bets and settlements.
• Read models: support search, dashboards and analytics derived from authoritative records.

If bet acceptance and wallet reservation are in different databases, define how the workflow recovers when only one side succeeds. Keep the case explicit until it is resolved; a timeout must not become an invented success. Explore core domains and boundaries before deciding how many databases to deploy.

Player records: stable identity, limited exposure

Give the player a stable internal identifier. Keep provider-specific account references and brand memberships as explicit relationships, with ownership and uniqueness rules. An email address is a changeable contact field, not a durable substitute for identity.

Separate frequently used operational fields from sensitive identity documents and their storage references. A game-event pipeline usually needs the player reference and relevant account context, not a copy of every identity field. Minimise replication of personal data into logs, analytics and provider payloads, and define access for each use.

Record important eligibility decisions with their effective time, reason and policy version where appropriate. A current account status alone cannot explain why an action was permitted yesterday. Determine which decisions must be checked synchronously before a new action; a delayed reporting copy should not authorise it.

Build indexes around actual lookups, such as brand plus player reference or an external account mapping. Check that tenant or brand scope is enforced on the server. A globally unique identifier helps locate a record; it does not grant access to it.

Financial records: protect the commit boundary

Treat the ledger as the durable record of financial movements. If you maintain a current balance for fast checks, update it consistently with the relevant entries and reconcile it against them. Define separate accounts or balance categories for cash, bonus value and reservations; product rules must explain which funds are spendable.

For a double-entry design, require balanced debit and credit postings within each currency. TigerBeetle’s accounting guide explains the debit and credit model. Enforce completeness when posting; merely storing two rows does not enforce that invariant.

Use exact monetary representation with an explicit currency and scale. Integer minor units may suit some currencies; decimal types may suit other supported assets. MySQL 8.4 documents DECIMAL as an exact-value type. Validate rounding and range at application boundaries rather than relying on silent conversion.

For a conditional example, an account has 100 spendable units and two simultaneous requests each try to reserve 70. Under a no-overdraft policy, at most one may succeed; the other must observe the updated funds and be declined or otherwise handled by the policy. Reading 100 twice and later writing two separate results breaks that rule.

• Identify the operation: scope an idempotency key to the caller and operation. A unique constraint should protect that identity; repeated keys with different amounts or currencies must be rejected.
• Protect the decision: use an engine-appropriate lock, guarded update or isolation strategy to keep the funds check and mutation consistent. Keep transactions short and lock shared resources in a consistent order.
• Commit the evidence: record the movement, reservation or balance change and operation outcome together within the relevant local transaction.
• Handle uncertain responses: if the connection fails around commit, look up the operation by its stable identity. A client timeout does not prove rollback.

PostgreSQL 18’s isolation documentation explains that Serializable transactions can fail and require a retry. Retry the complete database transaction safely; do not repeat an external payout as though it were part of that rollback.

Correct a posted financial movement with a linked compensating entry rather than silently overwriting its history. For a fuller treatment, see wallet and transaction-ledger design.

Game events: preserve identity through retries and corrections

Separate the provider’s game event from its financial effect. An event may be received twice, arrive late or describe a correction to an earlier result. Store enough context to establish provider, integration or tenant scope, event identity, round identity, currency, amount and the linked financial operation.

Do not assume a round identifier uniquely identifies every debit, credit or rollback within that round. The deduplication key must match the provider contract. Preserve occurrence time and receipt time separately; timestamps alone are a weak substitute for an explicit sequence or version rule.

Use a durable processing status to distinguish received, financially applied and unresolved events. Quarantine conflicting payloads for the same identity and route them to a review path. Retrying an unresolved event should not make a second financial movement.

Publish committed changes through an outbox: an event table written in the same transaction as the authoritative change. AWS’s transactional outbox guidance describes this pattern and duplicate-tolerant consumers. Writing the outbox independently in another database recreates the gap. Monitor backlog and replay by stable identity.

Bet records: retain accepted terms and settlement history

A bet is a long-lived agreement, not just a row waiting for an outcome. Retain the terms actually accepted: selections, price, stake, currency, relevant rule version and acceptance time, together with the funding reference. Later updates to a market or displayed price must not rewrite those accepted terms.

Represent acceptance, rejection, settlement, voiding, cash-out and any supported resettlement as controlled transitions. The exact model depends on the product. Store linked revisions or events so an operator can explain a corrected result without losing the previous decision.

For a settlement batch, use a stable settlement identity and a version rule. If processing stops after some bets are settled, the restart should identify completed work and continue without creating duplicate credits. Preserve exceptions rather than treating the entire batch as either untouched or complete.

Separate operational records from analytical copies of odds and market history. Read-heavy trading or reporting queries should not hold up the financial commit path. When money and betting state span services, test reconciliation of partially completed transitions as deliberately as the normal flow.

Read workloads: keep reporting off the critical path

Route reads according to the decision they support. A funds check or withdrawal decision needs an authoritative view with the required consistency. A historical dashboard may tolerate a stated delay. Support search may use a derived index, provided the user can inspect the underlying authoritative transaction before taking a sensitive action.

Build replicas, search indexes or analytical stores for their workload instead of letting every consumer scan transactional tables. Publish data through a controlled pipeline, define freshness expectations and expose the last applied position or update time. A stale view should be recognisable.

After a write, a player may immediately request their balance. Provide read-after-write behaviour deliberately: return the confirmed result, route the follow-up to the authority, or use a verified replication-position mechanism. Do not assume an arbitrary replica has caught up.

Measure query plans, lock waits, connection-pool pressure, commit latency and replica or projection lag together. Check tail latency during reporting and export jobs, not only average response time under an idle database. Additional indexes can improve reads while adding work to writes, so validate them against the mixed workload.

Replication: decide what a successful write guarantees

Specify what the application promises when it reports a successful operation. Has the transaction reached durable storage only on the primary, or also the required standby? Which failure is that promise intended to survive?

PostgreSQL 18’s replication documentation describes possible loss of committed transactions after asynchronous failover. Synchronous acknowledgement offers different durability guarantees and adds waiting. Choose the policy against your loss tolerance, latency target and failure scenarios; do not equate replication with zero data loss.

Define a recovery point objective, the maximum acceptable data-loss window, and a recovery time objective, the acceptable restoration time. Include promotion rules, prevention of two active writers, application reconnection and reconciliation with provider records. When the commit outcome is uncertain, recover by operation identity rather than issuing a fresh debit.

Rehearse failover with active transactions, delayed replication and an unavailable standby. Check which acknowledged operations survive and what happens to transactions still in flight. A topology diagram and a successful empty-database failover leave those questions unanswered.

Partitioning: match the key to access and retention

Partitioning divides a table into smaller parts within a database; sharding distributes data across database instances. They address different constraints. Partitioning alone does not add independent write capacity or remove contention on a heavily used account.

Time-based partitions can suit large event histories and retention work. Player or account keys can help locality for account-centric access. Test how your actual queries reach partitions, how late events are routed and whether one brand, account or batch concentrates too much work.

Check uniqueness before partitioning an idempotency table. PostgreSQL 18’s partitioning rules require a partitioned unique constraint to include every partition-key column. Adding an event date can therefore permit the same provider identity on different dates. Use a design that preserves the intended deduplication scope.

Consider a dedicated operation registry whose uniqueness is not weakened by time partitioning, but also define its retention. Deleting the registry too early can turn an old replay into a new operation. When sharding, additionally resolve routing, account moves, cross-shard operations and global reporting before calling the design ready.

Archiving and backups solve different problems

An archive preserves historical records for later use. A backup supports recovery from loss or corruption. A replica follows changes and may follow an accidental deletion too. Plan all three according to their separate purposes.

Before moving old data, define retention with the operator’s legal, compliance and operational owners. Different record categories may need different policies. Do not assume that closing an account permits deleting its financial evidence, or that retaining evidence justifies keeping every identity document indefinitely.

Preserve identifiers, relationships, schema versions and retrieval instructions. An archive should reconstruct a player’s transaction history and supporting decisions without a forgotten vendor screen. Validate completeness before removing source data, and keep unresolved rounds, bets and disputes available to their operational owners.

For PostgreSQL, point-in-time recovery documentation requires a suitable base backup and the necessary continuous WAL archive; a logical dump alone is not a WAL-replay base backup. Test the recovery method you actually use, including keys, credentials and access to the backup location.

Measure restoration of a realistic dataset and verify its business state before reopening writes. Restoring the database does not automatically reconcile actions already performed by payment or game providers after the chosen recovery point.

Auditability: reconstruct the outcome, then prove recovery

An audit trail should answer a concrete question: why did this player’s funds change, what input authorised the change, and what happened afterwards? Connect the provider event, internal operation, ledger entries, business decision and any manual correction with stable references.

Record the actor or service, reason, relevant version and effective and recorded times. Protect financial and audit records with restricted write paths, monitored privileged access and independent retention where appropriate. An append-only application API does not prevent a database administrator from altering storage.

Use reconciliation to compare financial records with provider outcomes and business states. Investigate unmatched amounts, duplicated identities and ageing unresolved cases. Route discrepancies to an owner with a permitted correction path, not to an unattended dashboard.

Before approval, agree acceptance conditions for the checks below. Retain the measured workload and the exact deployment and settings used; results from another topology may not apply. Connect database signals to platform observability so an incident can be traced to the affected operation.

To review the wider system, start with iGaming platform architecture. The useful deliverable is a documented consistency and recovery model supported by evidence, not a claim that a particular database brand makes money operations safe.

• Concurrent spending: race two requests against insufficient combined funds; verify the permitted outcome and matching ledger and reservation records, including balanced postings where double-entry is used.
• Duplicate identity: repeat a provider event and a client request; verify one financial effect, and reject a conflicting payload for the same key.
• Commit uncertainty: interrupt the response around commit; recover the outcome by identity without creating a new operation.
• Mixed workload: run wallet traffic with representative reporting, exports and background maintenance; meet agreed commit-latency and error limits.
• Delayed reads: slow a replica or projection; verify the chosen read-after-write policy and visible freshness for delayed views.
• Failover: promote under active load and measured lag; verify acknowledged operations, writer fencing and recovery of in-flight work.
• Retention and replay: test late events across partition boundaries and deduplication retention; retrieve an archived case with its linked evidence.
• Restore: recover a realistic backup to an isolated environment, reconcile business state and record restoration time before approving the procedure.