Episode Transcript
[00:00:00] For the longest time I've wanted someone or some group to develop a new storage engine for Postgres that could better handle update heavy workloads. So imagine you have a table that gets a lot of updates.
[00:00:15] Well, with how Postgres MVCC works, you're going to be creating a new row for each row updated, and if a single row gets updated multiple times, a thousand times, you're creating essentially a thousand rows. Now eventually once all those row versions are no longer used, they get vacuumed up, but of course that causes tons of bloat until they're cleaned up. And you have a lot of write amplification that can occur particularly with wall generation.
[00:00:44] So I thought it would be good to have a alternative storage engine that uses say an undo log to store these different versions as opposed to putting them in the heap with all the other data.
[00:00:55] But I really like the perspective of the first article we're going to talk about this week where he says, yes, MVCC is bad, but so is everything else.
[00:01:05] But I hope you, your friends, family and co workers continue to do well. Our first piece of content, PostgreSQL MVCC is bad. So is everyone else's. This is from boringsql.com and he says if you follow people who don't like Postgres, they basically they say MVC is bad and call it a 40 year old design mistake and it can cover all the issues I mentioned in the intro. You know, the write amplification running out of transaction IDs, bloated tables. But he breaks it down and said, you know, essentially we're asking four questions. Where do the old versions of the data live? Is it in the table itself or in a separate structure? Which way do version chains point? Is it from old to new or new to old?
[00:01:51] What do indexes point at? Do they point at the physical row location or a logical key? And then who cleans up what and when? Is it a background process later or is it the transaction itself? Because each of these is a trade off you have to make. And that's essentially what this blog post talks about, because in terms of Postgres, the old versions live in the table. The version chain points from old to new, the indexes point at a physical location and and then the cleanup happens with a background process later. Now next he discusses the charges against PostgreSQL, basically the write amplification. So when you update say 100,000 rows, catching a non indexed row, you can see the wall generation, but essentially almost double the size when you use an index table, because each of those indexes need to now reference the new physical row. And the more wall you generate, the more checkpoints that have to happen, which leads to more full page image rights and more bytes shipped to a replica. Now, Postgres has a mitigation for this. There are heap only tuple updates or a hot update. But sometimes to get the most benefit out of this, you do have to adjust the fill factor, which again increases the size of the database tables themselves because you're essentially reserving additional space.
[00:03:10] The next charge is your table is an accident scene, which I guess to him means every update is an insert plus a deferred delete by vacuum. And if you don't have vacuum configured, well, basically your dead versions accumulate and it results in a polluted table charge number three. One transaction poisons the well.
[00:03:31] So basically you have issues with long running transactions that can prevent rows from being cleaned up. And if you try to run a vacuum, it has dead but not yet removable. And the fourth charge is the 32 bit debt. Basically the transaction IDs are only 32 bits. So basically these are the four ways that Postgres MVCC is bad.
[00:03:54] But then he goes and looks at other database systems. So he looks at those that use an undo log, for example, such as Oracle and the NODB engine and MySQL. So this is a separate storage area. So essentially updates happen in place. So the table only holds the newest version and the history hangs off in a change of deltas in that undo log. So basically the consequence of this is that a reader is now slowed down if it needs to look at an older version because it notices that the current version is too new and it needs to walk the chain backwards to to get to the version it needs.
[00:04:32] Now, the benefit of this is that the table stays compact, you're not bloating it with additional row versions. You don't need to worry about rewriting secondary indexes. There's no separate vacuum process. Basically, he says the cleanup is woven into the normal operation and no wraparound rituals. Basically because these engines have 48 bit or 64 bit transaction IDs. Now the benefit of Postgres design is rollbacks are super fast because the data is already in the database. So for Postgres to roll back, a 1 million row update takes 0.1 milliseconds. But as he says, with an undo based engine, rollback means walking the undo chain and reversing every change one row at a time, which is roughly as much work as it took to update it to begin with. And he says frequently MySQL operators end up kill long running updates and then watch it as it takes exactly as long to actually do the undo. The other difference if you remember back, is that if a reader is looking for the most recent version, it's pretty quick, but if it's not the most recent version they need to walk that chain and it slows down reads, at least compared to Postgres. So reading a row in Postgres, no matter the version basically costs about the same as anything else, whereas with the undo every version except the newest version must be reconstructed, so you're taking a hit on some reads. So he says an example is a long running report against a hot table gets progressively slower as the gap between the snapshot and the current state widens. And he says it's not like the idle snapshot problem went away, the failure mode changed. So if a long running query needs a version whose undo has been recycled, Oracle just says well, that snapshot's too old now, whereas NodB just basically makes you wait essentially it looks like. So he says it's the same fundamental tension. Choose your poison. Do you want to cancel the reader or keep the garbage? Because he says NoDB's purge threads are essentially a form of vacuum. And he says the undo camp didn't eliminate MVCC's costs, it moved them probably away from the future where you have a background cleanup job like vacuum or you deal with table bloat and onto the writers critical path if you need to do rollbacks or read older data. But he says there are certain workloads where this is optimal. So if you're committing nearly always and you have to update heavily indexed rows and you rarely have long reads against hot tables, an undo log does better. But this kind of goes towards it would be awesome to have the choice in Postgres to be able to choose a particular undo storage system like this for certain tables or certain workloads. Next to cover is SQL Server and it looked like it mostly just dealt with locking and it's only very recently where they've started truly having MVCC as an option. Well I should say they have had a version store since 2005, but most recently since 2019 they've added a persistent version store and it lives inside the user database, not in tempdb. So it's very similar to Postgres design it seems. And he talks about a few other systems like MongoDB, LSMTree versions like CockroachDB and YugabyteDB he looked at ETCD and he goes and talks about fixing Postgres itself and here's where he talks about, you know, Z Heap, the project that was worked a lot@Enterprise DB and that was abandoned and then it started happening at Cybertech, but then it's stalled but we still have Oriole DB but unsure as to the state of that and when we might see that. But he says, keep in mind all the while this was going on, Postgres has spent, you know, 15 years shrinking the restrictions that the design imposes. So it implemented hot updates visibility map to skip vacuuming, clean pages, introducing B tree duplication, bottom up index deletion, a rewritten vacuum tid store, removing the memory limit that used to force multi pass index vacuums, and a way to deal with bloat in version 19 with repack concurrently. Now he does show a scorecard of sorts that shows how these questions are answered and dealt with. But he says, you know, the cost paid for multi versioning, keeping history, reading history, discarding history are conserved essentially across and each engine chooses a different way to address that issue. And there are positives and negatives for both directions. So he says quote PostgreSQL charges the future and fails by bloating the undo camp charges the writer and the reader of history and fails by canceling queries or ballooning undo. And the benefits of Postgres is that never blocks a reader, never cancels a query with snapshot too old, never makes you wait for a rollback, and never hides its garbage where you can't inspect it.
[00:09:35] So I thought this was a great blog post and it gives you insight into Postgres design and how other systems work. And each of these decisions has a trade off. So definitely encourage you to check this out if you're interested. Next piece of content the Startups Postgres Survival Guide. This is from Hatchet Run and I thought this was a pretty great summary of things to watch out for when you start using Postgres. So his first recommendation is write a good schema and he has guidance for using identity columns or uuids for primary keys, although use version 7 if you're going to be doing that.
[00:10:12] Always use timestamp TZ always use primary keys, use foreign keys with cascading deletes for low volume tables. Be sure to write good read queries as well as performant joins creating compound indexes that are aligned by your order by be sure to create good write queries how to handle some migration and connection management working with a query planner in bulk updates and some autovacuum configuration of course talking about four updates, skipped lock for queue like workloads, partitioning and just a comment about large table migration. So if you're interested in that, definitely check this out. Next Piece of Content There was another episode of Postgres FM last week. This one was on DBaus and Nick and Michael were joined by Kwan and Peter to talk about their open source platform for durable workflows. So I have talked about this before and the site is here dbas.dev and it has a pretty interesting way to keep track of workloads that I encourage you to check out if you haven't seen it already. They also cover in their use of listen notify they were running into performance problems. It's mostly because of the notify global lockdown and how they tried to address that is basically do notifies in batches. So send one notify that has a batch of work to do and just send that every 10 milliseconds. Because they don't need essentially immediate real time notifications, they found that they can process 20 to 30,000 actions per second. Pushing it beyond that you start running into bloat problems and autovacuum usage, et cetera. So they do definitely recommend keeping this dbaus system on a separate database. It doesn't need to be on a separate instance, but at least on a separate database from your actual main application database. But if you want to learn more, definitely encourage you to listen to the episode here or watch the YouTube video down here. Next piece of content Hybrid search patterns with Postgres and PGvector. This is from CrunchyData.com and he's talking about situations where you don't just want to do a basic similarity or nearest neighbor search, but you actually want to also sort in the where clause particular records you're looking at. So essentially you would normally do this in a B tree index, whereas this requires a specialized vector index like hnsw. Now he does talk how to deal with the limit reduction that can happen when you're using a vector based index, but he has a recommendations with regard to that. But going back to try to adjust your query to pull both of this information one where the category is legal and it's similar to a particular embedding. One way is to do a vector search first and then filter on the actual category, but this might hurt your recall. Or do a scalar first and then rank by distance. So this is just using a typical where clause and then the order by Andy has various workarounds and tricks to retrieving the data, including creating a partial HNSW index. So for example, creating a dedicated index was where the category is legal. So I personally like this recommendation. The other option is to try to over sample first. So first do a embedding search limiting it to 200 and then you apply the category legal and re rank only looking for the 10 records. He discusses ways you can cache the response and even looking how to diagnose the plan choice that you're given.
[00:13:38] So I thought this was a pretty good set of solutions for doing essentially hybrid searches containing scalar and nearest neighbor or distance searching. But check it out if you want to learn more. Next piece of content hybrid search in PostgreSQL BM25 sparse vectors and reciprocal rank fusion this is from pgedge.com and that's what I actually thought the other blog post was going to be talking about is Reciprocal Rank fusion. And this is a different type of hybrid search where they're combining vector searching and text based searching using Elasticsearch like indexes like BM25 and then combining those results together using reciprocal rank fusion. So if you want to learn how to do that you can definitely check out this blog post. They do use some of their own extensions like the PGEdge vectorizer here, but you can check it out. Next piece of content looking forward to Postgres 19 autovacuum tweaks and in this blog post he's talking about the new prioritization system that vacuum is getting in Postgres 19 that helps it choose which table to vacuum next. So it assesses a table's transaction ID age, the multi exact ID age, the count of dead tuples waiting to be reclaimed, the count of freshly inserted tuples, and the volume of changes since the last analyze. And this can be seen with a new view. PGSat auto vacuum scores and it looks something similar to this. So this is great that vacuum is actually using known information to choose what to vacuum next and it gives you a new set of configuration options to tweak these if you feel necessary. So these are different weights to assign to the scoring system, but if you want to learn more, definitely encourage you to check out this blog post. Next piece of content the hidden cost of Postgres constraints at scale this is from tigerdata.com and they're talking about a situation where you're looking to really increase your bulk insert performance. And you want to be very careful of of course your foreign keys and potentially unique constraints because they can really slow down your ingest performance. So the recommendation is to defer the foreign key checks to commit time. So basically insert a bunch of data and then just commit commit it once. Second recommendation is to scope the unique check to your live data window. So basically have a constrained unique index that only covers an example here. So most recent seven days. So that unique index only gets applied in the most recent seven days and you would just need to re index that concurrently periodically to keep it up to date. Third recommendation is to validate the foreign key references in the application layer, so don't do it in the database. And the fourth is to just drop the foreign key constraint. But check this out if you want to learn more.
[00:16:29] Next Piece of content what's new in Postgres 19? This is from Planetscale.com and they actually highlighted three areas repack and the ability to repack concurrently. JIT being disabled by default as well as the query planner being smarter sometimes.
[00:16:48] So if you're interested in that, definitely check out this blog post and the last piece of content SQL improvements in PostgreSQL 11 through 18 a personal selection. So here tupoau.org where Dimitri Fontaine discusses his personal best features across these different versions. So check this out if you want to learn more.
[00:17:10] I hope you enjoyed this episode. Be sure to check out scalingpostgrows.com where you can find links to all the content discussed, as well as sign up to receive weekly notifications of each episode. There you can also find an audio version of the show as well as a full transcript. Thanks. I'll see you next week.
