Overview
Archiving is a lifecycle decision, not simply moving old rows to another table. Determine which records must remain online, which must be retained for legal or business reasons, and which can be deleted after an approved retention period. Confirm requirements before changing data.
Start with the workload and the invariant
Define an archival boundary using a stable timestamp and a clear rule. Process records in bounded primary-key batches, and ensure related child rows are handled consistently. A parent row should not be removed while active records still depend on it. Use an archive marker or staging table when the move must be reviewed before deletion.
Practical example
SELECT id
FROM audit_events
WHERE created_at < ?
ORDER BY id
LIMIT 500;
Make the process restartable. Record the last processed key or select eligible rows each run with a deterministic condition. Use transactions for each batch, not one transaction for the entire archive. Prevent concurrent application writes from changing records halfway through the move, for example by archiving only immutable or closed records.
Trade-offs and failure modes
Decide how reports query both active and archived data. Options include a reporting view, a union-based reporting query, or a separate analytics store. Avoid silently changing report totals when older rows disappear from the primary table. Document whether historical exports include archived records.
Verification and rollout
Back up and verify archived data before deleting the source. Compare row counts and checksums over stable columns, validate foreign-key relationships, and test restoration of a sample. Protect archives with access controls and encryption because old records can still contain sensitive information.
Engineering checklist
Monitor batch duration, replication lag, disk growth, and application query latency. Archiving should be reversible enough to recover from a mistake and predictable enough to run as routine maintenance.
Before shipping, review the database version, storage engine, SQL mode, character set, collation, and deployment process used by the target environment. Features and DDL behavior can differ between MySQL and MariaDB releases. Validate the exact statements on a representative staging database, capture a baseline, and keep a tested recovery path for changes that affect production data.