MVCC exists to make readers and writers coexist. Instead of locking readers out while a writer holds a row, the database keeps multiple versions of each row and lets every transaction read the version that was current in its own snapshot. Writers block only other writers — through row locks on the current version — and readers never block anyone, ever.
The mechanism is concrete. In Postgres each row version carries xmin (the transaction that created it) and xmax (the transaction that deleted or superseded it). An UPDATE doesn't modify the tuple — it inserts a new tuple and sets the old one's xmax. A transaction computes its snapshot (which committed transactions are visible to it) and walks the version chain until it finds a version whose xmin is visible and whose xmax isn't. That's the "M" in MVCC: the multiversion part. InnoDB does the same with undo log records instead of in-place tuple headers, but the model is identical.
Now the problem the question is really about: every version has to stay around until every transaction that could see it has finished. A tuple superseded by 500 updates doesn't vanish — there are 500 dead tuples on the page, and each new transaction's snapshot determines whether it can skip them. Old versions accumulate, so index scans read pages full of dead rows, updates split pages prematurely, and table bloat grows. That's why vacuum exists — it's not maintenance cosmetics, it's the garbage collector for the version chain.
Vacuum walks the heap, marks tuples that no snapshot can see as reusable space, and updates visibility maps so index-only scans work. It runs automatically via autovacuum, triggered by dead-tuple thresholds. The existential part: transaction IDs are 32-bit (~4 billion), and Postgres freezes tuples older than the wraparound horizon — a frozen tuple is visible to everyone forever, so its xid no longer needs checking. If vacuum can't keep up (typically because one long-running transaction pins an ancient snapshot and blocks cleanup of everything after it), the database approaches wraparound and eventually refuses to accept writes to protect itself. VACUUM FULL is the heavier tool: it rewrites the table, reclaims space to the OS, and takes a full table lock — the version-chains are the same, but the file is compacted.
The tradeoff framing to close with: MVCC trades storage for concurrency. The bloat is the tax; vacuum is how the tax gets collected. Systems that skip the cost — like InnoDB's purge thread or read-only replicas — just move the collection somewhere else.