一篇对多个数据库系统的深度分析指出，PostgreSQL的多版本并发控制（MVCC）存在的问题并非其独有，而是反映了整个行业在版本管理设计上面临的普遍困境1。该分析对比了PostgreSQL、Oracle/InnoDB、SQL Server、MongoDB和LSM引擎等多个系统，梳理出MVCC的四个核心设计权衡——版本存储位置、版本链方向、索引指向方式、清理机制——指出每种技术路线都存在其失败模式，不存在完美解决方案1。
PostgreSQL采用的设计方案具有鲜明特点：版本数据存储在表内、版本链从旧指向新、索引直接指向物理位置、由后台进程执行清理1。这套方案的代价是写放大严重——单行默认产生7.1条预写日志（WAL）记录，即便通过HOT更新优化也仅能降至3.0条1。更为棘手的是，一个未提交的事务可阻止整个数据库的垃圾回收，这被称为xmin horizon问题1。此外，PostgreSQL采用32位事务ID需要定期冻结元组以防溢出，这是其独有的维护负担1。Uber在2016年对PostgreSQL的测量正是基于这些写放大问题，最终促使该公司转向MySQL1。
相比之下，其他系统采取了不同的折衷方案，各自面临独特的困境1。InnoDB和Oracle使用undo日志设计，使回滚成本等同于事务成本1。Oracle、InnoDB和MongoDB中都普遍存在"快照过旧"类错误（如ORA-01555）1。SQL Server将版本存储在tempdb中，长事务会导致整个实例故障1。MongoDB则将版本存储在内存，缓存压力下会直接回滚事务，其失败模式表现为延迟而非错误1。CockroachDB和YugabyteDB采用LSM树架构，垃圾回收窗口历史上的默认设置为25小时1。
业界也在尝试改进方案1。etcd采用类似PostgreSQL的设计，同样面临bloat和快照过旧问题1。PostgreSQL社区在2018年启动了zheap项目，试图为该系统添加undo引擎，但多年后仍未被主分支采纳1。另一个方向是OrioleDB，它结合了undo式版本控制与索引组织表，由Alexander Korotkov在Supabase维护1。
A technical analysis comparing multi-version concurrency control (MVCC) implementations across major database engines reveals that PostgreSQL's approach, while distinct, shares fundamental challenges with all alternative implementations.1 The article examines four core design decisions—version storage location, version chain direction, index pointer targets, and garbage collection mechanisms—and concludes that each choice involves unavoidable trade-offs with distinct failure modes across the industry.1
PostgreSQL stores versions within table rows, with chains pointing from old to new versions, indexes pointing to physical locations, and background processes handling cleanup.1 This design contributes to write amplification: a single row update generates 7.1 Write-Ahead Log (WAL) records by default, though heap-only table (HOT) optimization can reduce this to 3.0 records.1 A more severe limitation emerges when a single uncommitted transaction blocks garbage collection for the entire database—the xmin horizon problem—preventing tuple freezing and forcing periodic table maintenance to prevent 32-bit transaction ID overflow, a challenge unique to PostgreSQL.1 These issues prompted Uber to migrate from PostgreSQL to MySQL after measuring write amplification in 2016.1
Alternative architectures present different compromises. InnoDB and Oracle use undo logs where rollback costs equal transaction costs.1 SQL Server stores versions in tempdb, where long-running transactions can cause instance-wide failures.1 MongoDB maintains versions in memory, causing transaction rollbacks under cache pressure rather than explicit errors.1 CockroachDB and YugabyteDB employ log-structured merge (LSM) trees with historical garbage collection windows that have historically defaulted to 25 hours.1 Snapshot exhaustion errors—including ORA-01555 and "snapshot too old" messages—occur across Oracle, InnoDB, and MongoDB implementations.1 etcd adopts PostgreSQL's design and faces identical bloat and snapshot expiration problems.1
Proposed solutions have had limited adoption. The zheap project, initiated in 2018 to add undo capabilities to PostgreSQL, remains unmerged after years of development.1 OrioleDB, maintained by Alexander Korotkov at Supabase, implements undo-style versioning combined with index-organized tables as an alternative approach.1
评论
还没有评论，欢迎留下第一条。