In a database that uses multi-version concurrency control (MVCC), deleting a million rows usually frees no disk space immediately. Why does the engine keep the old row versions, and what eventually removes them?
answer
- MVCC = readers see a snapshot, so old versions must survive
- DELETE marks expired, does not free
- Undo-based purge vs heap vacuum — same idea
- Space reused inside the object, not returned to OS
- Rollback also leaves garbage
basics
~20 sUnder MVCC an UPDATE or DELETE does not overwrite data; it marks the old version dead so transactions with older snapshots can still read it. The space is only reclaimable once no running transaction can see that version, and a background cleanup process (vacuum or undo purge) reclaims it later.
solid answer
~50 sMVCC gives readers a consistent snapshot without blocking writers, and it does that by keeping **multiple versions** of a row. An `UPDATE` writes a new version and marks the old one as expired; a `DELETE` only marks the row expired. Neither frees anything at once. The old version must survive while any transaction whose snapshot predates the change might still read it — that is the whole point. Only when no live transaction can see it does it become garbage. Cleanup is asynchronous and comes in two flavours. In **in-place-update engines with an undo log** (InnoDB, Oracle), the current version lives in the table and old versions live in undo/rollback segments; a purge thread truncates undo once it is no longer needed. In **append-style engines** (PostgreSQL-style heaps), all versions live in the table itself and `VACUUM` marks dead tuples' space reusable, plus cleans the corresponding index entries. In both cases the space is normally returned to the table for reuse, not to the operating system.
go deeper
State that MVCC keeps old versions so concurrent readers see a consistent snapshot, that DELETE only marks rows dead, and that background cleanup reclaims the space later for reuse.
Distinguish undo-based purge from heap vacuum, explain that updates produce versions too, and that space is reused rather than returned.
Add the operational angle: cleanup as a background service that competes for I/O and can be stalled by old snapshots; prefer partition drops over bulk deletes.
Frame lifecycle management as a schema decision — partitioning and retention design so reclamation is a metadata operation rather than a maintenance burden.
## Why versions exist at all The classic problem: a long report reads a table while other sessions modify it. Locking readers against writers is correct but kills concurrency. **MVCC** solves it by never destroying data in place. Each transaction gets a **snapshot** — a definition of which versions are visible to it — and reads the version that was current as of that snapshot. Readers do not block writers and writers do not block readers. The price is that a row is not one thing. Each version carries bookkeeping (creating transaction id, expiring transaction id, or a pointer to the previous version) so visibility can be decided per version, per transaction. ## What UPDATE and DELETE actually do - **UPDATE**: a new version of the row becomes current, and the previous version is marked expired by this transaction. Whether the new version is written into the table (append-style) or the old one is moved to undo (in-place style) is an engine design choice. - **DELETE**: no new version. The existing one is stamped with the deleting transaction id. It is invisible to new transactions after commit, but still physically present and still readable by older snapshots. - **ROLLBACK**: the new version is the one that becomes garbage instead. Note that rolled-back work also leaves debris — an aborted bulk load still consumed space. This is why `DELETE FROM t` leaves the file exactly as large as before, and why the row count dropping to zero does not shrink the database. ## Two engine families **Undo-based / in-place update** (InnoDB, Oracle). The table holds the newest version. Older versions are reconstructed by walking undo records in rollback segments. Consequences: the table stays compact, but the undo log grows with the amount of change that old snapshots might still need. A **purge** process truncates undo once no transaction needs it, and also removes index entries for delete-marked rows. **Append-style / heap versions** (PostgreSQL-style). Every version is a tuple in the table's pages, with expired ones sitting there as **dead tuples**. `VACUUM` scans, confirms which tuples are invisible to everyone, marks their space free for reuse within the page, and removes matching index pointers. Consequences: no separate undo store, but the table itself grows and must be cleaned. The vocabulary differs; the concept — asynchronous reclamation of versions nobody can see — is identical. ## Space is reused, not returned After cleanup, freed space is normally recorded as **reusable inside the object**. New rows fill it. The file does not shrink and the operating system sees no change. This is deliberate: shrinking a file requires moving live rows and an exclusive lock, which is far too disruptive for routine maintenance. So a table that held 100 GB, then had 90% of its rows deleted, will still occupy roughly 100 GB but will absorb the next 90 GB of inserts without growing. Actually returning space needs an explicit rewrite operation (a full vacuum, a table rebuild, `OPTIMIZE TABLE`, dropping a partition), all of which are expensive and locking. The practical takeaway is that **`DELETE` is not how you reclaim disk**. Deleting a partition or truncating a table drops whole files and returns space immediately; row-by-row deletion does not. ## Why this matters to an application developer Three everyday consequences: 1. **Deletes are expensive writes.** A `DELETE` writes to the row, writes to the write-ahead log, and creates future cleanup work in the table and in every index on it. Bulk deletion is often slower and heavier than the insert that created the rows. 2. **Updates behave like insert + delete for space purposes.** A table updated constantly grows and needs cleanup even if its row count never changes. A row of counters updated once a second produces 86,400 dead versions a day. 3. **Cleanup is a background service you can starve.** It competes for I/O, and — critically — it can be blocked entirely by a single long-running or idle-in-transaction session, because that session's snapshot may still need the old versions. That is the most common cause of runaway table growth in MVCC systems. ## Quick model to remember Write paths create versions; snapshots keep versions alive; a background collector frees them once no snapshot can see them; freed space is reused in place. Every operational problem in this area is one of those four steps going wrong.
- You deleted 90% of a 100 GB table but disk usage did not drop. Why, and how do you actually reclaim the space?Cleanup marks the space reusable inside the table rather than returning it to the filesystem, because shrinking a file requires relocating live rows under an exclusive lock. The table will absorb future inserts without growing. To truly return space you need a rewrite — a full vacuum, table rebuild or OPTIMIZE-style operation — or a design where you drop a partition instead of deleting rows.
- Does an UPDATE that changes one column of a wide row create a whole new version?In append-style engines, yes: the whole tuple is rewritten as a new version, which is why updating a single counter column on a wide row is far more expensive than it looks, and why every index entry may need updating too unless the engine can apply an in-page optimization. In undo-based engines the row is modified in place and only the before-image of the changed part goes to undo, so the cost profile differs, but old versions are still produced.
Crossing out a line in a ledger instead of erasing it: everyone reading an earlier photocopy still needs the original text, and the page only gets reused after every such reader has finished.
saying these in an interview costs you the question
- Believing DELETE frees disk space immediately
- Thinking MVCC means there is only ever one copy of a row
- Assuming a rollback leaves no trace on disk
- Saying updates are cheaper than inserts because the row count does not change
- Expecting routine cleanup to shrink files back to the operating system