MVCC – the part of PostgreSQL we hate the most (2023)

Original Article ↗ Hacker News Discussion ↗

PostgreSQL’s multiversion concurrency control (MVCC) and storage architecture—rooted in 1980s design choices—are criticized for write amplification, heavy reliance on VACUUM, and painful major-version upgrades, especially on large, update-heavy workloads. Commenters contrast PostgreSQL’s “old-school” heap and index strategy with newer approaches (e.g., N2O version ordering, diff-based storage, log-structured systems) and point to projects like OrioleDB and YugabyteDB that try to modernize the backend while keeping the PostgreSQL front end. Despite these flaws, many still favor PostgreSQL for its reliability, features, and ecosystem, arguing that radically changing the storage layer is so disruptive it may effectively require creating a new database.

Backend storage & MVCC criticism

  • Many feel Postgres keeps adding front-end features while neglecting deep storage/MVCC issues (write amplification, vacuum pain, upgrade downtime).
  • Major-version upgrades requiring downtime are seen as outdated; logical replication helps but lacks full DDL and LOB support.
  • Some argue changing the storage architecture is effectively rewriting the database; assumptions around current behavior are part of the “public API.”

O2N vs N2O and version storage

  • Commenters highlight that Postgres’s O2N version chains plus full-row copies amplify writes and bloat.
  • Confusion arises because the theoretical O2N advantage (no immediate index updates) is undercut by Postgres updating indexes on each write anyway.
  • Others note O2N can work well in systems with all pages in memory and cooperative GC, citing other engines.
  • Some emphasize that full-tuple storage is deeply baked into Postgres internals; partial-tuple/delta storage is conceivable but would be a massive change.

Vacuum, pg_repack, and tuning

  • The thread confirms vacuum-related pain, especially with large, update-heavy tables.
  • VACUUM FULL is called “crushing”; pg_repack is seen as a useful but “hackish” workaround that needs double storage and extra complexity.
  • Autovacuum tuning (e.g., lowering autovacuum_vacuum_scale_factor) is mentioned as a practical mitigation so each run does less work.

Alternative engines & architectures

  • OrioleDB is repeatedly mentioned as a new storage engine aiming to fix these MVCC/storage issues; active work and upcoming availability via cloud providers are noted.
  • YugabyteDB is cited as reusing the Postgres query layer with a different storage engine to avoid some MVCC/XID problems.
  • CockroachDB and other distributed systems are discussed as trading performance for horizontal scalability and sync replication guarantees.

Postgres strengths, age of design, and MVCC value

  • Some call Postgres’s MVCC “1980s tech” and outdated; others see long production history and stability as a feature, not a bug.
  • Postgres is praised for safety, ACID, SQL standards adherence, documentation, and a strong community.
  • There is debate on whether MVCC is over-engineering: critics suggest many apps don’t need snapshot semantics; supporters value serializable isolation and not having to reason deeply about anomalies.