Copy-on-write isn’t just a filesystem or memory management technique. It’s also a foundational mechanism that modern databases use to implement snapshot isolation, allowing readers and writers to work concurrently without blocking each other.

The Reader-Writer Conflict

Traditional locking approaches create a fundamental tension: either writers block readers, readers block writers, or both. A long-running analytical query could prevent updates from committing, or a steady stream of writes could starve read-heavy workloads. This problem intensifies as database workloads become more mixed, combining transactional updates with analytics on the same dataset.

Snapshot isolation resolves this by giving each transaction a consistent view of the database as it existed at transaction start time. Readers see a stable snapshot while writers continue modifying data. Copy-on-write makes this possible without duplicating the entire database.

How COW Enables Snapshots

When a transaction begins, the database records a timestamp or transaction ID marking that point in time. As the transaction reads data, it sees the version that was current at that timestamp. If another transaction has since modified that data, the database maintains both the old and new versions.

The copy-on-write mechanism kicks in during writes. Instead of modifying data in place and destroying the old version, the database creates a new version while preserving the original. Multiple versions of the same logical row coexist in storage, each tagged with the transaction ID or timestamp that created it.

When a reader encounters a row, the database checks which versions are visible to that transaction’s snapshot. Versions created after the snapshot began are invisible. Versions created by transactions that hadn’t yet committed at snapshot time are also invisible. The database walks backward through versions until it finds one that matches the visibility rules.

Implementation Patterns

Postgres implements this through its MVCC system with tuple versioning. Each row update creates a new tuple version. The old version remains in place, marked with metadata indicating which transactions can see it. Vacuum processes eventually reclaim space from versions that are no longer visible to any active transaction.

MySQL’s InnoDB takes a different approach with its undo log. Updates modify rows in place but write the previous version to a separate undo log segment. When a transaction needs an older version, InnoDB reconstructs it by applying undo records backward from the current version.

Oracle’s architecture is similar, using rollback segments to store old versions. The key difference from pure copy-on-write filesystems is selectivity: databases only version modified data, not the entire dataset on every change.

The Version Explosion Problem

The tradeoff is version accumulation. Long-running transactions prevent old versions from being reclaimed because they might still need those snapshots. A single analytical query that runs for hours can force the database to retain every version created during that window.

This manifests as bloat in Postgres, where tables grow as dead tuples accumulate faster than vacuum can remove them. In MySQL, it appears as undo log growth, which can consume significant disk space and degrade performance as the system scans through long version chains.

Some databases add mechanisms to limit the damage. Postgres allows administrators to cancel queries that prevent vacuum from making progress. SnowflakeDB and similar cloud data warehouses separate storage and compute, using object storage’s native versioning capabilities while adding time-travel features that automatically expire old snapshots.

Why It Matters

Copy-on-write transaction isolation is a classic systems tradeoff: lower latency and higher concurrency in exchange for increased storage overhead and garbage collection complexity. It’s the mechanism that allows modern databases to serve mixed workloads without forcing users to choose between consistency and performance.

Understanding this pattern explains seemingly unrelated database behavior: why vacuum exists, why long transactions cause performance degradation, why “hot update” optimization matters in Postgres, and why some queries see different data than others even when run simultaneously. It’s copy-on-write doing what it does best—creating isolated views of mutable state.