The Part of PostgreSQL We Hate the Most

[nelsonic]

https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postgresql-we-hate-the-most.html The-Part-of-PostgreSQL-We-Hate-the-Most.pdf via: https://news.ycombinator.com/item?id=41895951

Where we learned about: https://www.orioledb.com -> https://github.com/orioledb/orioledb

[ndrean]

Thanks for sharing. Learning is a never ending story.

If I understand correctly, MVCC is about how to allow non blocking reads (eg snapshot) while allowing writes.

• with Oracle, MySQL: create an undo log with the original data and write the new data in place of the original.
• Postgres: write new in new location, keep the original in place, and change metadata, the pointer from the old data to the new data.

Now the fun part. I asked my new best friend to summarise how OrioleDB challenges Postgres MVCC approach.

OrioleDB offers a fresh approach to PostgreSQL's MVCC system,
addressing key limitations, particularly around maintenance and performance. 
While PostgreSQL's MVCC ensures data consistency by maintaining multiple versions of data, 
it comes with challenges like table bloat and the need for regular VACUUM processes, 
which are resource-intensive and sometimes inefficient at large scales.

OrioleDB improves upon this by using row-level and block-level undo logs, 
eliminating the need for VACUUM and significantly reducing table bloat. 
This makes it much more efficient at handling updates and deletes compared to PostgreSQL's 
traditional heap storage. 
For example, where PostgreSQL requires the VACUUM process to clean up space after updates,
OrioleDB can do in-place updates thanks to these undo logs. 
Additionally, OrioleDB has an automatic page merging feature, 
further optimizing storage and reducing fragmentation over time​

Performance benchmarks show OrioleDB achieving higher transaction throughput (up to 5x), 
using fewer system resources (less CPU and I/O), 
and preventing issues like index bloat that affect PostgreSQL under heavy load​

OrioleDB’s architecture also scales better with modern hardware, 
bypassing some of PostgreSQL's legacy limitations with locking and memory management​

Overall, OrioleDB presents a compelling alternative to PostgreSQL’s MVCC, 
especially for workloads with high update/delete rates or large-scale data, 
as it simplifies maintenance while improving performance.