An archive table beats a soft delete on a hot table

A deleted_at column means every query filters on it forever, and the rows stay in the index, in the buffer pool and in the backup.

-- every index now needs deleted_at, or every query scans
WHERE deleted_at IS NULL AND customer_id = ?

-- a partial index would fix it, and MySQL has none.
-- so the alternative:
INSERT INTO orders_archive SELECT * FROM orders WHERE ...;
DELETE FROM orders WHERE ...;

-- the hot table stays small; the archive is queried
-- rarely and can be on slower storage.

PostgreSQL’s partial index makes soft deletes affordable and MySQL has no equivalent, which changes the calculation entirely — the filter is in every query and every composite index needs the column as its leading edge. An archive table is more work up front and keeps the working set proportional to the working data rather than to the history.