Scaling a database is typically performed when data volume is manageable, as large-scale operations introduce complexity. A common challenge with sharding is its negaitve impact on pagination performance.

Data Partitioning Concept Modern databases like MySQL support automatic table partitioning, such as by date ranges. While this enables partition-specific queries, it can lead to "tail hotspot" issues where recent data receives disproportionate query volume, making QPS (Queries Per Second) optimization difficult.

QPS = Concurrent Connections / Average Response Time Concurrent Connections = QPS × Average Response Time

1. Service Outage Approach

This method involves scheduled downtime for data migration.

1. Announce service maintenance window to users.
2. Stop all application services.
3. Migrate and redistribute data from old to new database schema.
4. Validate data consistency between old and new systems.
5. Update application configuration (e.g., change hash modulus from 3 to 4).
6. Restart services.
7. Prepare rollback procedures for each step.

While ensuring migration integrity, this approach impacts user experience and creates time pressure to complete within the maintenance window.

2. Read-Only Mode Approach

Maintain read availability while disabling write operations.

1. Ensure database supports read/write separation.
2. Announce upgrade schedule.
3. Intercept and block write requests at service layer.
4. Replicate and redistribute data to new schema.
5. Validate data consistency.
6. Update application configuration.
7. Re-enable write operations.
8. Clean up redundant data.
9. Prepare rollback procedures.

Disadvantages include extended write downtime and the need for post-migration cleanup operations.

3. Transaction Log Approach

Old database continues serving requests while logging all write operations. New database syncs initial data, then applies logged changes until systems converge.

Log format example:
Database | Table | Primary Key | Operation
---------|-------|-------------|----------
db1      | users | id123       | UPDATE
db1      | users | id456       | INSERT
db1      | users | id789       | DELETE

This enables blue-green or rolling deployments with eventual consistency.

4. Dual-Write Approach (Suitable for Medium Databases)

MySQL employs four primary log types:

• Redo Log: Write-ahead logging for crash recovery
• Undo Log: Reverse operations for rollback support
• Bin Log: Replication log for master-slave synchronization
• Relay Log: Entermediate log for slave replication

Implementation steps:

1. Disable master-slave replication
2. Sync historical data from slave to new database
3. Deploy change data capture (CDC) to monitor source binlog
4. Stream incremental changes until near real-time sync
5. Switch traffic when systems achieve near-identical state

5. Smooth 2N Scaling (Suitable for Large Datasets)

This method eliminates bulk data synchronization:

1. Establish bidirectional replication between two databases
2. Break replication during scaling event
3. Redistribute data according to new sharding rules
4. Optionally clean up residual data post-migration