Partitioning & Scaling

Partitioning

Partitioning splits one logical table into several physical pieces. Queries that filter on the partitioning key only touch the relevant pieces (partition pruning), and you can drop a whole partition instantly instead of running a slow DELETE.

CREATE TABLE sales (
  id        INT NOT NULL AUTO_INCREMENT,
  sale_date DATE NOT NULL,
  amount    DECIMAL(10,2),
  PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

Type	Splits by
RANGE	Value ranges — ideal for dates
LIST	An explicit list of values (region, status)
HASH	A hash of an expression, for even distribution
KEY	Like HASH but MySQL supplies the function

ALTER TABLE sales DROP PARTITION p2023;                          -- instant archival
ALTER TABLE sales ADD PARTITION (PARTITION p2026 VALUES LESS THAN (2027));
ALTER TABLE sales REORGANIZE PARTITION pmax INTO (…);
EXPLAIN SELECT * FROM sales WHERE sale_date >= '2025-01-01';     -- check "partitions"

Rules and limits: every unique key, including the primary key, must contain every column of the partitioning expression. Partitioned tables cannot have foreign keys. Partitioning is not a general speed-up — if your queries do not filter on the partition key, it makes things slower.

Replication

Replication copies changes from a source server to one or more replicas, using the binary log.

        writes
  App ──────────► Source ──binlog──► Replica 1 (reads)
                            └──────► Replica 2 (reports, backups)

Use it for read scaling, high availability, backups taken off the primary, and analytics that would otherwise disturb production. Modes: asynchronous (default, fastest, can lose recent writes on failover), semi-synchronous (waits for one replica to acknowledge), and Group Replication / InnoDB Cluster for automatic failover.

The key operational risk is replication lag: a replica can be seconds or minutes behind, so a user who just wrote data may not see it when the next read hits a replica. Either route read-after-write to the source or check Seconds_Behind_Source before serving from a replica.

Scaling strategies in order

1. Optimise first. Indexes and query fixes often buy 10–100×; hardware rarely does.
2. Scale up. More RAM so the buffer pool holds the working set, faster NVMe storage.
3. Cache. Redis or Memcached in front of expensive read queries.
4. Read replicas. Send reports and read-heavy traffic off the primary.
5. Archive. Move cold rows to history tables so the hot table stays small.
6. Shard. Split data across servers by customer, region or hash. Do this last — it complicates joins, transactions and every migration.

Health checks

SHOW REPLICA STATUS\G          -- lag and errors (SHOW SLAVE STATUS on older versions)
SHOW GLOBAL STATUS LIKE 'Threads_running';
SELECT * FROM sys.host_summary;
SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY size_mb DESC;