Designing Scalable Database Architecture for High‑Concurrency Systems

When a system grows from a few hundred thousand users to tens of millions with peak traffic reaching 10,000 requests per second, a single‑instance database quickly becomes a bottleneck due to disk I/O, CPU, memory, and slow SQL queries.

To support such load, the article recommends deploying multiple database servers (e.g., five) each hosting an identical database (e.g., db_order_01 , db_order_02 , …) and using a middleware like Sharding‑JDBC or MyCAT to route data based on a hash of the order ID. This basic sharding reduces the data volume per table by a factor of the number of shards.

Because a single table can still become too large, further partitioning into many tables (e.g., 1,024 tables per logical order table) is suggested, bringing the row count per table down to a manageable size (around 100 k rows) even after years of growth.

After sharding, generating globally unique identifiers is essential. The article reviews four approaches:

Dedicated auto‑increment table (simple but a bottleneck under high concurrency).

UUID (globally unique but large and inefficient as a primary key).

Timestamp combined with business fields (risk of collisions under high load).

Snowflake algorithm (64‑bit ID composed of timestamp, datacenter ID, machine ID, and sequence number).

The Snowflake implementation is provided in Java; the full source code is wrapped below:

public class IdWorker {
  private long workerId; // machine id
  private long datacenterId; // datacenter id
  private long sequence; // sequence within the same millisecond
  private long twepoch = 1288834974657L;
  private long workerIdBits = 5L;
  private long datacenterIdBits = 5L;
  private long maxWorkerId = -1L ^ (-1L << workerIdBits);
  private long maxDatacenterId = -1L ^ (-1L << datacenterIdBits);
  private long sequenceBits = 12L;
  private long workerIdShift = sequenceBits;
  private long datacenterIdShift = sequenceBits + workerIdBits;
  private long timestampLeftShift = sequenceBits + workerIdBits + datacenterIdBits;
  private long sequenceMask = -1L ^ (-1L << sequenceBits);
  private long lastTimestamp = -1L;

  public IdWorker(long workerId, long datacenterId, long sequence) {
    if (workerId > maxWorkerId || workerId < 0) {
      throw new IllegalArgumentException(String.format("worker Id can't be greater than %d or less than 0", maxWorkerId));
    }
    if (datacenterId > maxDatacenterId || datacenterId < 0) {
      throw new IllegalArgumentException(String.format("datacenter Id can't be greater than %d or less than 0", maxDatacenterId));
    }
    this.workerId = workerId;
    this.datacenterId = datacenterId;
    this.sequence = sequence;
  }

  public long getWorkerId() { return workerId; }
  public long getDatacenterId() { return datacenterId; }
  public long getTimestamp() { return System.currentTimeMillis(); }

  public synchronized long nextId() {
    long timestamp = timeGen();
    if (timestamp < lastTimestamp) {
      throw new RuntimeException(String.format("Clock moved backwards. Refusing to generate id for %d milliseconds", lastTimestamp - timestamp));
    }
    if (lastTimestamp == timestamp) {
      sequence = (sequence + 1) & sequenceMask;
      if (sequence == 0) {
        timestamp = tilNextMillis(lastTimestamp);
      }
    } else {
      sequence = 0;
    }
    lastTimestamp = timestamp;
    return ((timestamp - twepoch) << timestampLeftShift) |
           (datacenterId << datacenterIdShift) |
           (workerId << workerIdShift) | sequence;
  }

  private long tilNextMillis(long lastTimestamp) {
    long timestamp = timeGen();
    while (timestamp <= lastTimestamp) {
      timestamp = timeGen();
    }
    return timestamp;
  }

  private long timeGen() { return System.currentTimeMillis(); }

  public static void main(String[] args) {
    IdWorker worker = new IdWorker(1, 1, 1);
    for (int i = 0; i < 30; i++) {
      System.out.println(worker.nextId());
    }
  }
}

Beyond ID generation, the article advocates read‑write separation using a primary‑replica (master‑slave) setup: writes go to the master, reads are served by one or more replicas, allowing independent scaling of read capacity without affecting write performance.

In summary, a high‑concurrency database design should combine multi‑server sharding, extensive table partitioning, a robust distributed ID scheme (preferably Snowflake), and master‑replica read‑write separation to achieve both scalability and reliability.