In a database engine that keeps multiple physical versions of each row so concurrent transactions read consistent snapshots, what is table and index bloat, and why does it accumulate?
answer
- update = new version + old marked dead
- delete marks, never erases
- dead is not free until cleanup runs
- space reused in-file, file rarely shrinks
- one index entry per version
basics
~20 sUpdates and deletes leave the old row version behind so older snapshots can still read it. Until a background cleanup process reclaims those dead versions, tables and indexes hold space no query needs. That waste is bloat.
solid answer
~50 sIn a multi-version engine an UPDATE writes a new version of the row and marks the old one superseded; a DELETE only marks the row dead. Neither frees space immediately, because transactions that started earlier may still legitimately need the old image. A background cleanup mechanism (a vacuum pass, an undo purge thread, an in-page pruning step) later reclaims versions no live snapshot can see. **Bloat** is the gap between the live data in a relation and the space it physically occupies. It has two sources: dead versions not yet reclaimed, and space that has been reclaimed but only marked reusable inside the file rather than returned to the filesystem. Indexes bloat too, because each new row version normally needs its own index entry. It costs real performance: scans read pages that are mostly dead rows, the buffer cache holds fewer useful rows per page, and backups copy the waste. Some dead space is normal and healthy; unbounded growth is the defect.
go deeper
Recall the mechanism: updates and deletes leave old versions, a background process removes them later, the leftover space is bloat.
Add why cleanup is deferred rather than done at commit, and that indexes bloat alongside the heap; mention that reclaimed space is reused, not returned.
Frame it as steady state versus runaway growth, name the runtime costs (scan volume, cache hit ratio, backup size) and say how you would measure the ratio and alert on the trend.
Talk about it as a capacity and design constraint: churn rate versus cleanup throughput, partitioning so old data leaves by DROP, and the space-for-concurrency trade MVCC is making on your behalf.
## What multi-versioning buys, and what it costs A multi-version concurrency control (MVCC) engine never destroys the copy of a row that another in-flight transaction may still need. An UPDATE logically produces a **new version** of the row and marks the previous one as superseded as of the updating transaction's commit. A DELETE erases nothing; it stamps the existing version as dead from that commit onward. Readers then pick whichever version matches their snapshot, which is exactly what lets a report that started ten minutes ago see a stable picture while writers keep working. The cost is arithmetic. If nothing ever removed superseded versions, storage would grow without limit. So every MVCC engine has a **cleanup** mechanism whose job is to find versions that no current or future reader can see and reclaim their space. ## Bloat is the gap between live data and occupied space Bloat is the space a table or index occupies that no live row needs. Two things feed it: 1. **Dead versions not yet reclaimed.** Cleanup runs behind the workload, not in lockstep with it. Between the moment a version dies and the moment cleanup gets to it, its bytes sit there. 2. **Reclaimed but not returned.** Most engines recycle freed space *within* the relation, by recording it in a free-space map for future inserts. The file on disk stays the size it grew to. That is deliberate: giving pages back to the filesystem generally requires rewriting or truncating the relation, which needs heavy locking. ## Indexes bloat as well, often worse Every version of a row that is visible to somebody must be findable by index, so a new version usually means a new index entry in every index on the table. When the old version finally dies, its entries must be removed too. B-tree pages that empty out are not always merged back into their neighbours, so an index can stay large and sparse long after its rows are gone. It is common to find index bloat exceeding heap bloat on a heavily updated table. ## Why deferred cleanup, rather than cleaning on commit? The committing transaction cannot know whether some other snapshot still needs the version it just superseded, and checking would put coordination work on the hot write path. Deferring keeps commits cheap and lets cleanup batch its work, at the price of transient space. This is the classic space-for-concurrency trade that MVCC makes everywhere. ## What bloat costs at runtime - **Sequential scans get slower in direct proportion.** A table that is 80% dead reads five times the pages to return the same rows. - **Cache efficiency drops.** Each cached page carries fewer live rows, so the working set no longer fits in memory and reads start hitting disk. - **Index traversals lengthen.** Sparse, oversized indexes mean more levels and more page visits, and scans may land on entries pointing at dead versions. - **Everything that copies the relation pays.** Backups, replica rebuilds, checksum verification and maintenance operations all move the dead bytes. - **Planner estimates drift** if statistics and row-count estimates no longer match reality, which can flip a plan to a worse access path. ## Steady state versus runaway A busy, frequently updated table always carries some dead space, and that is fine: the free space is reused by the next inserts, so the relation reaches a stable ceiling somewhat larger than its live data. The failure mode is not "dead rows exist", it is "the ceiling keeps rising". That happens when cleanup cannot reclaim (something is holding old snapshots alive) or cannot keep up (churn exceeds cleanup throughput). Both show as the same symptom, so diagnosis has to distinguish them. ## Measuring it The honest measure is a ratio, not an absolute: compare live row count multiplied by average row width against the physical size of the relation, and track index sizes separately. Watch the trend. A table sitting at 2x its live size for months is healthy; one that has gone 1.2x, then 3x, then 8x over a week is telling you cleanup has stopped working. Alerting on the trend and on the age of the oldest open transaction catches the problem well before disk fills.
- If cleanup reclaims the space, why does the table file on disk usually not shrink?Routine cleanup marks freed space as reusable inside the relation and records it in a free-space map, so future inserts and new row versions land there instead of extending the file. Returning pages to the filesystem requires truncating or rewriting the relation, which needs an exclusive lock or an online copy-and-swap tool. Engines default to reuse because it is cheap and lock-free, and because a churning table would just re-grow anyway.
- Does deleting a million rows free disk space immediately?No. The delete marks those row versions dead as of its commit; the bytes remain until cleanup confirms no snapshot can see them and reclaims them, and even then the space is normally recycled inside the relation rather than released to the OS. Index entries linger the same way. If you actually need the disk back, you need a rewrite or an online reorganisation, or better, partitioning so that old data leaves by dropping a partition.
A filing cabinet where superseded pages are stamped 'void' rather than shredded, because a colleague reading with an older index may still need them. The cabinet keeps growing until someone walks through and shreds the pages no reader can still reference.
saying these in an interview costs you the question
- Saying a DELETE frees disk space right away
- Believing an UPDATE modifies the row in place so no extra version exists
- Thinking only the heap bloats and indexes are unaffected
- Treating any amount of dead space as a bug rather than the normal cost of MVCC
- Assuming reclaimed space is returned to the operating system by default