When a table becomes unwieldy.
WHAT CHANGES AT SCALE
Full scans become impossible Schema changes take hours Deletes generate enormous undo Backups take longer than the window Indexes no longer fit in memory
WHAT TO DO FIRST
Confirm the indexes are right.
WHY
Most large-table problems are still index problems.
WHAT ARCHIVING PROVIDES
Moving old rows out, keeping the active table small.
WHAT TO ARCHIVE BY
Date, usually.
WHAT PARTITIONING PROVIDES
One logical table stored as several parts.
WHAT IT ENABLES
Dropping an entire period instantly, rather than deleting rows Queries reading only relevant parts
WHAT IT REQUIRES
The partitioning column in every unique key.
WHY THAT CONSTRAINT MATTERS
It frequently forces a schema change.
WHAT TO BE REALISTIC ABOUT
Partitioning helps specific patterns, not everything.
WHAT SUMMARY TABLES PROVIDE
Precomputed aggregates for reporting.
WHY THEY HELP
Reports read a small table rather than scanning a huge one.
WHAT THEY REQUIRE
Maintaining them, and accepting they lag.
WHAT TO DO ABOUT DELETES ON LARGE TABLES
Batch them, with a pause between.
WHY THE PAUSE
It lets replication and other work catch up.
