In this lesson, you will learn why frequent updates are significantly more resource-intensive than new inserts when using copy-on-write or MVCC database architectures.
The Mechanics of an Update
In many modern databases that utilize MVCC (Multi-Version Concurrency Control), a row is immutable. When you issue an UPDATE statement, the database does not simply overwrite the existing data on the disk. Instead, it performs two distinct operations:
- It marks the existing row version as "expired" (or deletes it logically).
- It writes a brand-new version of the row to a new location on the data page.
The Performance Cost
Because the database is essentially treating an UPDATE as an INSERT followed by a DELETE, this architecture creates a "copy-on-write" overhead. If you update a row 100 times, the database doesn't just store one record; it manages 100 versions across your data files.
Consider this sequence:
-- Initial insert (1 write operation)
INSERT INTO inventory (id, stock) VALUES (101, 50);
-- Subsequent update (1 write + 1 logical delete + index overhead)
UPDATE inventory SET stock = 49 WHERE id = 101;
This creates three hidden performance bottlenecks:
- Write Amplification: Each update triggers a new write. If your indexes include the updated column, those indexes must also be updated, pointing to the new row version. This turns a single logical update into multiple physical disk writes.
- Version Bloat: Frequent updates generate "dead tuples" (the old versions of your data). These versions consume space until the background vacuuming process identifies them as invisible to all active transactions.
- Increased I/O Pressure: As the table grows with dead versions, your database must scan through more data pages during queries. Even though the old versions are ignored by your snapshot, they still occupy space on the disk and in the memory cache, reducing the efficiency of your read operations.
While INSERT operations simply append data to the end of a table or index, UPDATE operations create fragmentation and require the database to perform continuous house-keeping (vacuuming) to reclaim space. High-frequency updates can lead to a "bloat spiral" where the database spends more time cleaning up old versions than processing new queries.
Key Takeaway: In MVCC architectures, updates are expensive because they are actually "delete-plus-insert" operations that trigger write amplification, index maintenance, and increased garbage collection overhead.