I just attended my fifth in-person conference of 2023, four of them PostgreSQL focused. I look forward to attending more in the coming months to share and learn about the platform, meet more community members, and continue to invest however I can.
Unfortunately, between these conferences, several recent blogs, and the general attitude towards PostgreSQL from some other platforms, I have to admit that I’m getting a bit worn out by the seemingly constant complaining about (or dare I say shaming of) the PostgreSQL MVCC implementation and the issues that it can cause some workloads.
When things go south and you realize that part of the problem was a core architectural design feature, I get that it’s frustrating. I’ve been on that side of learning PostgreSQL, too.
But sitting on the plane with a few hours on my hands, I wanted to talk this out (maybe for myself) and try to encourage us to think about the conversation differently, at least as a PostgreSQL community. What others choose to say and do is their business. But within the community, including databases that are built on the foundation of the PostgreSQL codebase, I think we owe it to everyone to make sure we’re staying focused on the right things.
If you don’t agree, no worries! Feel free to skip this one. Like I said, maybe this is just a dialog I need to have for myself to gain a better perspective.
Level-Set
Let me start by saying that I am not a contributor to the PostgreSQL codebase. I have hopes that someday I’ll be able to help at a code level, but until then I’m working to contribute to the community and dialog however I can.
I only have about five and a half years invested in this project after spending more than 15 in the Microsoft SQL Server/Data platform ecosystem. Although, to be fair, I did run a small PostgreSQL cluster for a project at Penn State University back in 2002-2004, so in some sense I was an early adopter. 😉
I’ve run PostgreSQL in many environments with workloads big and small, but I know there are many people with much more experience than me, managing multi-terabyte or petabyte scale environments.
Instead, my primary focus with PostgreSQL right now is to build the community through teaching and being available to help others in their day-to-day work, however I am able. I still believe that this platform is revolutionary in ways that other databases are not and I’m excited to share that at every opportunity.
It’s that last part, focusing on helping the community, that got me a bit fired up after these last few interactions. Recently it feels like the loudest voices only want to discuss how awful the MVCC implementation is in their opinion. I hear a lot less discussion about the value that the PostgreSQL MVCC implementation provides and the fact that most projects will never experience an issue. I’m probably just missing those talks and blog posts right now, but when discussions are lopsided like this, I’m concerned we’re sending the wrong message to a growing community that doesn’t have the same level of context that many of us do.
Let me try to explain (and seriously, this is a somewhat reactionary blog post, so I’m bound to get a few things wrong here).
Why MVCC Is an Easy Target
I don’t want this blog to become an epic 10k-word article. Therefore, I’m not going to write a treatise on the inner workings of MVCC and how it differs from other databases. Break out your Google/ChatGPT-Foo for that one.
Suffice it to say, the PostgreSQL implementation of MVCC was a design choice to accomplish concurrent isolation levels as outlined by the ANSI standard. The core team could have made different design choices similar to other relational databases but they didn’t. They had a specific goal in mind and (as far as I can tell) believed the benefits outweighed the potential issues.
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.
For today’s high-throughput workloads and ORM-first applications, there are very justifiable reasons why these three issues are probably more noticeable now than in previous application generations. Indeed, many developers are caught off guard when they discover that their tables are significantly larger than they should be, or that queries are getting slower over time because incorrectly configured servers are preventing auto vacuum from working efficiently.
But again, here’s the thing. Most PostgreSQL users won’t be impacted by these issues as long as they learn how to correctly configure the cluster for their workloads. In a few recent talks I attended I’m pretty certain folks came to learn how to do just that, but instead they heard a bunch of stuff about how silly and outdated MVCC and vacuuming is. Maybe, but this kind of messaging just seems determined to convince everyone that the sky is falling, when in fact, it is not for most users.
To quote Jan Karremans in a recent Twitter exchange around this topic:
Now, is “good enough” what we’re all striving for? I hope not! And I don’t think that’s what Jan is saying at all. In its current form – MVCC, vacuuming, and all – PostgreSQL handles workloads big and small with features that exceed other databases.
In every way? No. Is PostgreSQL the only database that should exist? Again, no.
But you should probably start with PostgreSQL. 😃
Nobody Gets Blocked
Let’s recall that this MVCC implementation was chosen and developed for its primary advantage (at least). Quoting from the PostgreSQL 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.
Yes, your PostgreSQL database/cluster runs the risk of being inefficient over time if the background processes aren’t working as well as they should.
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.
But MVCC and vacuuming get so much attention that it must be a huge problem for everyone that ever touches PostgreSQL, right?
Probably not.
Workload Perspective
When I worked for Timescale, they did a lot of benchmarking and were good at generating Hacker News dialog. You might see my name on a few of them. 😉
Before we performed any benchmarks, we always discussed what the right kind of workload was based on the features we were trying to understand about each platform. Inevitably, this would end up in a discussion about what realistic, representative workloads looked like. When Timescale, Clickhouse, QuestDB, and others talk about inserting hundreds of thousands or millions of rows per second, it’s always in relatively short batches of time with a fixed amount of data (tens to hundreds of GB). That scale, however, rarely matched that of a typical, ongoing user load.
Put another way, inserting 100,000 rows/second for an hour is 360 million rows an hour. While there are applications that absolutely need that kind of ingest and throughput, they are rare… no matter how much of a unicorn your startup is.
I think the same holds true for MVCC and the problems that most real-world users face day-to-day. Additionally, the leading hosting providers (AWS, Microsoft, Google, EDB, Crunchy) have invested a lot of energy to mitigate potential MVCC and vacuuming issues before they reach that point.
Currently, PostgreSQL uses a 32-bit unsigned integer for the transaction ID which provides ~4 billion transaction IDs in total. At any one time, half of the transactions in a database could be older than mine, and half could be newer than mine. When PostgreSQL has used all the available IDs it “wraps around” and starts using them over… after doing fancy things in the database to ensure that older data from a previous use of that transaction ID is still available to queries. These fancy things are part of the vacuum process that should typically be running.
Let’s continue down the transaction ID wraparound path, even with my woefully inadequate explanation above. (Remember, this isn’t an MVCC internals post, so Google or start with one of these suggestions: docs, Cybertec blog 1 & blog 2, and Concurrency Control (Part 4) from “The Internals of PostgreSQL”)
If your application does 100 individual transactions a second (6,000/minute), it will theoretically take ~230+/- days to experience a wraparound issue if PostgreSQL was completely unable to run the vacuum process during that entire time, whatever the cause. Do you have 1,000 or 10,000 transactions per second? You’d still have days or weeks to detect and mitigate a developing problem. In fact, even if you aren’t monitoring for wraparound and your server hasn’t run out of space from dead tuples, modern versions of PostgreSQL will go into a failsafe mode when you get below 3 million reusable transaction IDs prevent the appearance of data loss.
Also remember that we’re talking transactions here, not individual inserts or updates. You do insert your data in batches, correct?
If your workload is larger than that, you’ve hopefully done enough preparation or have a good consultant on hand to ensure the database is correctly configured for your situation… even if you aren’t using PostgreSQL. All relational databases will will have some kind of issue as your scale approaches millions of transactions per minute if they aren’t configured appropriately.
Until There Are MVCC Alternatives
As with most things PostgreSQL, these issues will eventually be resolved for the workloads that do struggle with daily operations within the core codebase. I feel pretty certain about that. Also, many companies are working to solve it within their own ecosystems right now, and the approaches are all different.
Yugabyte has added a time component to their transaction ID so that it never wraps around and they’ve modified the storage layer completely to avoid typical vacuum issues, at least for some workloads.
OrioleDB is working towards a pluggable storage architecture which provides an alternative to traditional MVCC issues at the database or individual table level. I also appreciate how the team talks about PostgreSQL and MVCC, not as a total villain, but also as something to build upon and provide an alternative when it makes sense.
On the other end there are solutions like Citus that distribute your data across multiple nodes which often helps mitigate vacuuming issues as a byproduct.
Regardless, we don’t truly “win” as a community until at least one solution is available with a PostgreSQL license, included in core. As we look forward to that day (which will free folks to find a new thing to be the focus of all PostgreSQL ire), I’d love to hear more honest discussion from everyone talking about potential problems and how to mitigate them.
Sensationalize the Good, Not the Bad
So how do we encourage a different dialog within the community when discussing MVCC and vacuuming? I think we provide more context than we think we need to and we change the focus of what we sensationalize in talks and posts. Let me explain.
This whole topic hit a nerve for me mostly because I’ve recently noticed more speakers or authors sensationalizing MVCC and vacuuming problems to make things interesting. Unfortunately, they often skim over the context of the issue and leave people puzzled and confused about what the original problem is. To be honest, I’m sure I’ve done this too and it’s something I need to be more mindful of!
As a speaker or author, remember that many of the folks that came to hear your talk, hoping it will solve their problem or help them manage PostgreSQL better, are earlier in their PostgreSQL journey than you realize. They still don’t fully grasp why PostgreSQL is such a big player in the global vacuum supply chain, let alone why their database is bigger than it should be.
If you change your perspective just a little bit, then you can sensationalize your talk or blog around how awesome things can be if you understand a few key concepts and work on a the right configuration changes.
Talk about vacuum settings. Help people understand how to tune individual table thresholds. Discuss maintenance_work_mem, effective partitioning, and how to validate your settings with a given workload.
One recent example I saw that did this well, in my opinion, was a talk by Chelsa Dole at Scale 20x and CitusCon 2023. Chelsea tackles vacuuming head on and teaches from the beginning. There could have been lots of jabs using the “graveyard” analogy, but she helped lead folks forward to better understanding instead.
I also appreciate when PostgreSQL companies that do this kind of education well. Personally, I’ve always been impressed by the team at Cybertec and how they approach these tough topics on their blog. It’s obvious to me that they really love PostgreSQL, love getting in the weeds, and want to bring people along with them, not talk at them for the sake of talking.
Use these examples to build your own skills and repertoire of topics to share in a helpful way.
Is There Really a Problem?
I don’t want to add to the noise. Maybe I’m too sensitive about some of these topics or the way some of the conversation is headed in the dataverse (databaseverse? data sphere? anyway…)
I simply wanted to say that I don’t think everything is a total mess and the benefits of the current MVCC implementation, vacuum and all, have served a lot of folks faithfully for years, and will for many years to come. If we’re not careful, we give the impression that things are broken when they really aren’t, at least for the vast majority of users. (and I really mean vast majority!)
I love PostgreSQL. I love that it easily solves many database problems with ease. And I love the community as it continues to grow.
Here’s my parting encouragement to everyone when it comes to MVCC and vacuuming. Share the complete, true story. Absolutely share the warts too, that’s how the ecosystem improves. But the last time I looked out my window, the sky was still… well… up there!
Thanks for listening.
1 thought on “In Defense of PostgreSQL MVCC and Vacuuming”