SQL vs NoSQL: comparing approaches

(Section: Technology and Infrastructure)

Brief Summary

SQL (relational databases) - strong consistency, ACID transactions, rich query language and joynes. Ideal for money transactions and reference books.
NoSQL (document/column/key-value/graph) - flexible scheme, horizontal scale out of the box, high throughput and low latency for highly specialized patterns (logs, behavior, cache, analytical scans, leadboards).

The practice of iGaming almost always comes to polyglot persistence: SQL for balances and orders, NoSQL for events/logs/caches/search/online analytics.

Basic principles: ACID, BASE, CAP and PACELC

ACID (SQL): atomicity, consistency, isolation, durability - transactions with strict guarantees.
BASE (often NoSQL): "Basically Available, Soft state, Eventual consistency" - emphasis on availability and horizontal scale, but the final consistency is achieved over time.
CAP: with a network split, select C (consistency) or A (availability).
PACELC: In the absence of failures, the Latency vs Consistency compromise. Cash flows are more often C-oriented; telemetry/logs - L-oriented.

Data models

• Strict scheme, normalization, foreign keys, joynes, representations.
• Rich SQL (window functions, CTE, transactions, triggers).

• Document (MongoDB): JSON documents, flexible schema, indexes on nested fields.
• Column/wide lines (Cassandra/ScyllaDB): partitioning by key, quick entries and scans by partitions.
• Key-value/cache (Redis): millisecond latency, data structures in memory.
• Search (Elasticsearch/OpenSearch): inverted indexes, full text, aggregations.
• Graph (Neo4j): relations and paths, recommendations/anti-fraud-connections.

Transactions and Consistency

SQL: fully functional transactions (before Serializable), triggers, FK constraints - reliable money invariance.
Document NoSQL: transactions are often limited to collection/lot; inter-document - more expensive and less common.
NoSQL columns: tunable consistency.
iGaming practice: "money and legally significant records" → SQL/CP solutions; "events/metrics/logs/caches" → NoSQL with idempotency and asynchronous correction.

Scale and performance

SQL: vertical scale + replicas for reading, sharding manually/through frameworks; excellent complex sampling and ad hoc analytics on "hot" sets.
NoSQL: horizontal "first class" scale (shard-by-key, auto-rebalance), high TPS per write/simple reads; limited joynes/transactions, design for requests in advance.

Scheme and evolution

SQL: strict scheme, migrations (DDL), type control - less garbage, reliable invariants.
NoSQL: "schema-on-read," flexible changes, but requires field version discipline, validators, and data sanitization.

Query language and indexing

SQL: universal language, complex aggregations and joynes, rich optimization, secondary indexes.
NoSQL: language/DSL is different from SQL (aggregation pipeline, map/reduce, CQL), indexing is engine specific; often there is no "common" joyne - use denormalization and materialization.

Typical iGaming domains: where

• Wallets/balances, payments, accounting (strict consistency, transactions).
• ACC/compliance records, directories, authentication/ACL.
• Back-office reports with guaranteed correctness.

• Stream of events/logs/clicks/webhooks PSP (high recording, time/key parties).
• Leaderboards/ratings/real-time counters (Redis/Cassandra).
• Personalization and features of online ML (key-value + TTL).
• Search, recommendations, anti-fraud signals (ES/graph).
• Materialized projections from the stream (documents for specific screens).

Polyglot persistence (recommended)

• Postgres/MySQL is a "record system" for money and contracts.
• Kafka → ClickHouse/Pinot/Druid - online analytics and metrics.
• Redis - cache of balances, limits, tokens; rate-limits.
• Cassandra/Scylla - telemetry/betting stories with huge TPS.
• Elasticsearch - full-text search by games/providers/tiket-log.
• MongoDB - flexible profiles/settings/CRM cards of the player.

Design examples

1) Player balance (SQL, transactions)

sql
BEGIN;
UPDATE wallet SET balance_cents = balance_cents - 5000
WHERE player_id = 123 AND balance_cents >= 5000;
INSERT INTO ledger (player_id, delta_cents, reason, ts)
VALUES (123, -5000, 'bet_stake', now());
COMMIT;

The guarantee of the invariant "balance does not go into minus," a holistic entry in the journal.

2) Log of rate events (NoSQL, column)

Partitioning scheme: 'partition _ key = player_id',' clustering = event_time DESC '.

Queries: "last N player events," "all events per day by player."

3) Leadboard (Redis, ordered sets)

Ключ: `leaderboard:tournament:2025-11-05`

Team: 'ZINCRBY' with every bet/win → reading the top 100 'ZREVRANGE'.

Integration with Event Streaming

Outbox from SQL → Kafka → materialization to NoSQL/caches/search.
CDC (Debezium) for real-time directory/balance sheet updates.
CQRS: commands change state in SQL; read models live in NoSQL for fast screens.

Operational Perspective

SQL: mature backup tools, PITR, strict rights, understandable query plans; sharding requires discipline.
NoSQL: easy horizontal growth, but more responsibility for the design of keys and query patterns; backups/restores are engine specific.

Security and compliance

SQL is easier to use as a "source of truth" for audit/compliance (ACID, FK, strict logs).
NoSQL obliges: encryption, TTL/retention, PII control, audit of changes, validation of schemes.

Cost and TCO

SQL vertically can become expensive on large records; however, saves development time for complex features.
NoSQL is horizontally cheaper on terabytes of events and logs, but requires a competent design and more DevOps procedures for a specific engine.

Migrations and Evolution

From SQL to NoSQL: Start by duplicating events (outbox→strim→NoSQL), gradually switching reads to projections.
From NoSQL to SQL: highlight the "kernel of truth" (monetary/legal data), transfer with invariant validation and deduplication.

Selection checklist

1. Money/invariants/legal significance? → SQL/CP, ACID.
2. TPS for writing and linear growth? → NoSQL with sharding.
3. Complex joyns/ad-hoc analytics? → SQL or OLAP-DBMS.
4. Leaderboards/caches/counters? → Redis/quality KV.
5. Search/recommendations/log analysis? → Elasticsearch/column.
6. Need real time-to-insight? → streaming + materialized views.
7. GDPR/localization compliance? → geo-sharding and strict PII policy regardless of engine.

Anti-patterns

Trying to "shove everything" into one database (both SQL and NoSQL) is a loss of strengths.
Use NoSQL as a "relational without joynes" - uncontrolled denormalization and complex updates.
Make monetary transactions in eventual repositories without strict idempotence.
Ignore the key of shardiness and hot parties.
Lack of governance schemes in document databases → "zoo" documents.

Summary

SQL and NoSQL are not competitors, but complementary tools. For iGaming, a reliable strategy is SQL as a source of truth for critical data and NoSQL loops for high-speed events, caches, search and projections. Add streaming (outbox + CDC), CQRS, the discipline of schemes and sharding keys, and you get a platform that both reliably counts money and instantly responds to player behavior.