How to Scale SQL Databases for 20,000+ Concurrent Users: Single Instance, Sharding, and Read‑Write Separation

Single‑Instance Database Architecture

The simplest deployment consists of a UI layer, an application server, and a single database server that handles all read and write traffic. This model is adequate for early‑stage projects with a few hundred daily users and tables containing only a few thousand rows. When the user base grows beyond ~1 million, daily visits exceed 200 000 and peak concurrency reaches 20 000, the single database becomes a performance bottleneck.

Multi‑Instance (Sharding) Architecture

When the database layer is the limiting factor, the logical database can be split into several smaller, identical instances (shards). Assuming each shard can comfortably serve about 4 000 concurrent connections, five shards provide capacity for roughly 20 000 concurrent requests.

Routing traffic to the correct shard requires a deterministic algorithm. A common approach is to hash a stable user identifier (e.g., national ID) and map the hash to one of the N shards.

int shardCount = 5;
int shardId = Math.abs(userId.hashCode()) % shardCount; // 0‑4

Further partitioning can be applied at the table level—e.g., creating monthly or yearly sub‑tables and exposing a unified view that UNIONs them for cross‑period queries.

Read‑Write Separation

Read‑write separation adds one or more read‑only replicas of the primary database. All write statements are directed to the primary; read statements are load‑balanced across replicas, reducing CPU, I/O, and network load on the writer.

Implementation details differ by DBMS. Examples:

MySQL/MariaDB – asynchronous binlog replication.

PostgreSQL – streaming replication.

SQL Server – Always On Availability Groups.

Third‑party tools (e.g., Debezium, Maxwell) can capture changes and apply them to replicas in real time.

# MySQL replica configuration example
CHANGE MASTER TO
  MASTER_HOST='primary.host',
  MASTER_USER='repl',
  MASTER_PASSWORD='****',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=107;
START SLAVE;

Application code or middleware (ProxySQL, HAProxy, PgBouncer) should route SELECT queries to the replica pool while keeping INSERT/UPDATE/DELETE on the primary.

Practical Considerations

Capacity planning: Establish a baseline of concurrent connections per shard (e.g., 4 000) and scale the number of shards accordingly.

Shard key selection: Choose a stable, uniformly distributed attribute such as a hashed user ID or geographic region to avoid hotspot shards.

Routing layer: Implement the hash‑modulo logic in the application or use a dedicated proxy/middleware to map requests to the appropriate shard.

Table partitioning: For very large tables, create per‑period tables (e.g., orders_2023_01) and a view that UNIONs them, allowing transparent queries across partitions.

Monitoring & alerts: Continuously track latency, replication lag, connection counts, and I/O metrics; trigger additional shards or replicas when thresholds are exceeded.