Scaling Payment Systems: Sharding, Snowflake IDs, and High‑Availability Databases

1. Sharding (Database Partitioning)

In the era of Redis and Memcached, supporting 100,000 reads per second is easy by scaling cache nodes and web servers, but handling 100,000 orders per second requires tens of thousands of database writes, which a single database cannot sustain. Therefore the order table is split across multiple databases and tables.

We shard by uid using a binary‑tree expansion strategy: databases are added in powers of two (1→2→4→8…), allowing DBA‑level table synchronization without custom row‑level scripts.

Each of the 8 databases (DB1‑DB8) contains 10 tables (order_0‑order_9), giving a total of 80 shards. The database number is calculated as (uid / 10) % 8 + 1 and the table number as uid % 10. For example, uid=9527 maps to DB1, table order_7.

Two types of sharding tools exist: client‑side sharding (direct DB connections) and middleware‑based sharding. The client‑side approach was chosen because the team developed an open‑source data‑access framework called “Mango” that natively supports sharding.

Mango homepage: mango.jfaster.org Mango source: github.com/jfaster/mango

2. Order ID Generation

Generating 100,000 unique order IDs per second cannot rely on auto‑increment columns; instead a Snowflake‑style algorithm is used. The ID consists of a millisecond timestamp, a machine identifier, and a per‑millisecond sequence.

To enable direct lookup without a user ID, the sharding information (database and table numbers) is embedded in the ID. A two‑character shard suffix (e.g., "17" for DB1 table 7) is added, and later expanded to three characters to preserve precision for future scaling up to 64 databases.

A version byte is also reserved at the most significant position for future use.

Snowflake reference: github.com/twitter/snowflake

3. Eventual Consistency

To support queries by merchant ID (bid) as well as user ID, a duplicate order table cluster is maintained per bid. Asynchronous replication via a message queue ensures eventual consistency without the overhead of distributed transactions. Real‑time monitoring detects and reconciles any divergence.

4. Database High Availability

High availability means the system continues to operate as if no failure occurred. Traditional master‑slave setups require manual coordination between DBA and developers, which is slow. The proposed architecture separates DBA responsibilities and automates failover.

Read replicas are accessed through an LVS load balancer that automatically skips failed nodes. The master is accessed via a virtual IP managed by KeepAlive; a backup slave (DB_bak) can be promoted instantly when the primary fails.

Because all core updates are logged before execution, a simple recovery script can quickly patch any remaining inconsistencies.

5. Data Tiering

To reduce load on the primary database, data is divided into three tiers:

Tier 1: Order and transaction data – accessed directly without caching.

Tier 2: User‑related data – cached in Redis.

Tier 3: Configuration data – cached in local memory, with updates pushed via a high‑availability message platform.

6. Coarse‑Fine Pipeline

To protect the system from traffic spikes (e.g., 1 million requests per second versus a capacity of 100 k RPS), a coarse‑fine pipeline is placed in front of the web cluster. The coarse gate caps traffic at 1 M RPS, discarding excess, while the fine gate throttles traffic to the web servers at 100 k RPS, queuing the remainder.

Source: http://www.uml.org.cn/sjjm/201805034.asp?artid=20694