Blogmark

For all the FUD that gets spread about PostgreSQL’s MVCC architecture, its downsides are not impacting most users and the upsides are great.

Most of the complaints about the PostgreSQL implementation of MVCC revolve around vacuuming issues which can cause table bloat, transaction ID wraparound, and wasted overhead introduced by the visibility map.

From the Postgres docs:

The main advantage of using the MVCC model of concurrency control rather than locking is that in MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading. PostgreSQL maintains this guarantee even when providing the strictest level of transaction isolation…

…proper use of MVCC will generally provide better performance than locks.

There are always tradeoffs. Someone being over-critical about the downsides of MVCC may not be representing what you lose when you switch away from Postgres.

The solution isn’t necessarily to jump ship, either. In other databases a large select statement can block your writes. You also won’t have a rich extension ecosystem or a complete vendor agnostic database.

Here are the kinds of things to learn about if you want to better understand how to tune your database for impacted workloads:

Talk about vacuum settings. Help people understand how to tune individual table thresholds. Discuss maintenanceworkmem, effective partitioning, and how to validate your settings with a given workload.