1. Why versions instead of locks
Traditional two-phase locking blocks readers behind writers and vice versa. MVCC sidesteps that by keeping multiple physical versions of every row, each tagged with the transaction ID that produced it. A reader picks the version visible to its snapshot and never has to wait for a writer that committed after it started.
The result is non-blocking reads at the cost of more storage and a background process that prunes dead versions. Meridian calls that process the vacuum loop.
2. Snapshot isolation in practice
When a transaction begins, Meridian records the highest committed transaction ID it can see. Every row read during that transaction is filtered against this snapshot so the world appears frozen at the start time. Writes by other transactions are invisible until they commit and the next snapshot is taken.
BEGIN TRANSACTION ISOLATION LEVEL SNAPSHOT; SELECT balance FROM accounts WHERE id = 42; -- Sees the version committed before BEGIN. UPDATE accounts SET balance = balance - 100 WHERE id = 42; -- Creates a new version tagged with this txn id. COMMIT; -- New version becomes visible to future snapshots.
3. Trade-offs and tuning
MVCC trades disk and vacuum overhead for concurrency. Long-running transactions hold back the vacuum horizon, which causes version bloat and slower scans. Monitor the oldest_xmin metric and kill or split queries that hold it for more than a few minutes.
For write-heavy workloads, batch updates into smaller transactions and let the vacuum loop catch up between bursts. The default Meridian configuration vacuums every 30 seconds and targets a 10% dead-tuple ceiling.