Database Scaling Strategies: From Single Instance to Smooth Horizontal Expansion

Introduction

Initial version

If our online service is not critical, a single monolithic database can be used to store data. Advantages: simple, convenient. Disadvantages: concurrency and stability issues.

Advanced

As data volume continuously grows, horizontal sharding can be performed, for example by taking the modulo of user ID or user IP to implement routing. Adding master‑slave replication and KeepAlived can provide high availability.

Scaling

Version 1: Offline scaling (downtime)

A straightforward but brute‑force method that requires stopping the service.

Notify users via the app about the maintenance window.

Create several new high‑availability databases.

Stop the current service and run a data migration program to move all data from the old databases to the new ones.

Update routing rules in the code and restart the service.

Pros: Simple. Cons: Service downtime; cannot guarantee high availability; migration must be error‑free.

Version 2: Online dual‑write

Set up the new database and start writing data to both the old and new databases simultaneously.

Develop a migration program to transfer historical data from the old database to the new one.

During migration, check each insert: if the new table lacks the record, insert it; if it exists, update it, allowing newer data to overwrite older data (middleware such as Canal is recommended).

After a period, verify that data in both old and new databases are identical; once confirmed, switch traffic to the new database.

Pros: High availability. Cons: Not as smooth; large amount of data movement.

Version 3: Smooth scaling

Goal: expand from two databases to four.

Step 1: Modify configuration

Update configuration to map old shards to new shards, ensuring that after expansion data can be correctly routed.

Id % 2 = 0 becomes id % 4 = 0 or id % 4 = 2.

Id % 2 = 1 becomes id % 4 = 1 or id % 4 = 3.

Step 2: Reload configuration

The service layer reloads the configuration; this can be done by restarting the service or by sending a signal through a configuration center such as Cloud.

At this point, the 2 → 4 scaling is complete: the original two database instances are now four.

Step 3: Shrink data

Two pairs of databases (id % 4 = 0 & 2, and id % 4 = 1 & 3) continue synchronizing. Perform final cleanup:

Stop the two synchronization processes.

Create high‑availability setups for the new databases.

Delete redundant data, e.g., remove data of id % 4 = 2 from the machine handling id % 4 = 0, leaving each DB to serve only its designated shard.

Result: multi‑fold expansion achieved without massive data migration.