Episode Transcript
[00:00:00] One of the things you want to avoid doing in postgres is doing a lot of updates of a single row. Why? Because that basically multiplies the number of rows that are generated behind the scenes. Because of how postgres MVCC works, basically whenever you update a row, it creates a new row and then that is the most visible row to you. But if you've updated a row 10,000 times, there are 10,000 rows in that table. Now, a different way to handle this is to use an undo log. And this is something similar to what Oracle does in that it stores rollback information in the undo log as opposed to creating new rows every time something needs to be updated. And ever since postgres has offered the ability to have different storage engines, I assumed one would come around that would support this type of undo log. Well, years ago there was Z heap, but that seems to have lost traction. And more recently I've heard about aureoledb, but things have been quiet for many months. But thankfully we have an announcement to make today. But I hope you, your friends, family and co workers continue to do well. Our first piece of content is AureolDB Beta 7 benchmarks. This is from Orioledb.com and AureolDB is an open source storage extension for PostgreSQL. So it's leveraging Postgres storage engine architecture to define a new storage engine for postgres other than the heap storage, which is the default storage. So some of the things that makes it different is one, it eliminates buffer mapping and has lockless page reading. So basically orioledb maps memory pages directly to storage pages, so you don't need to do buffer mapping and you can do in memory page reading with lockless access. So that can improve performance. Another thing it does is it uses MVCC based on the undo log concept that I was mentioning earlier. So basically rows get updated in place, but there's a separate undo log maintained if you need to do a rollback. They also offer a page level undo and basically they're trying to make it so that you don't have to run vacuum. In the third point they use copy on write checkpoints and row level wall as opposed to block level. And they say because the row level wall makes it easier to parallelize things. So that's what kind of makes the storage system different. And now they have some benchmarks. So they used a transactional benchmark here, TPCC and they said the measurement of warehouses, one warehouse is 100 megabytes. So they tested it looks like basically 10 gigabytes, 50 gigabytes and 100 gigabytes if I'm doing the math right and running this benchmark. They say on average it was about 2.3 times faster than the heap storage. So basically you could get better performance simply from choosing this different storage engine at the 500 warehouse level. Or I would assume around 50 gigabytes it tested 5.5 times faster and they said this is because of the shared memory cache bottleneck that orioledb is able to avoid. Now interestingly, when they went up to about 100 gigabytes, the difference wasn't as large. It was about 2.7 times faster than postgres. But they said another reason that helps edge out performance here is they have index organized tables so tables presumably are aligned according to an index. Some other optimizations they have is they have built in compression to reduce storage up to 5x in some cases. We mentioned the undo log so you don't have to do vacuuming and then also removal of the sub transaction hazard. So this is still a beta. It's not ready for primetime yet, but you can go ahead and install it from GitHub and when you create your tables you just say using aureolidb as the storage engine you want to create those tables on. And personally I think it would be great if you have a use case where you have an update heavy workload. Typically I would say don't run that on postgres res or redesign it differently to just do inserts only as opposed to just updates. But once this new storage system is ready for primetime I could imagine putting certain update heavy workloads on the storage engine versus the heap, at least in a transition period. And then the blog post follows up talking about their vision for orioledb and basically they want to make it the go to postgres storage engine and basically take over or supplant the heap eventually. They're also pushing to decouple storage from Compute and this is something I've been super interested about as well that I've only seen hosted providers do so far. But I remember they did do a test where the Oriole EDB storage engine was against S3 so that was interesting. They want to support hybrid workloads with KAL indexes and also work to support multimaster replication which that'll be a trick. I'll see how they do that. Now there are some current limitations.
[00:05:12] Right now you can only use B tree indexes with the storage engine prepared. Transactions are not supported either and you cannot re index concurrently. But they say their plan is to have these particular features included with the general availability release sometime in 2025. So I found this super interesting and definitely check out this blog post if you want to learn more. But also there was an announcement by Supabase because I believe they acquired OriolDB and on the Supabase platform they are offering an OriolDB Public Alpha. So definitely not ready for production workloads, but you can start testing it out and they just show you how to go ahead and get started on the Supabase platform if you want to do that. Now I will add that Supabase had their launch week last week, so they had a number of announcements and all of these articles are included on the website for links to the Supabase launch week. I'm not covering them individually during the show, but you can definitely check those out because they're talking about an AI assistant built in, cron, jobs, etc.
[00:06:18] Next piece of content Performance Archaeology OLAP or Online Analytical Processing. This is from Vonder Me and this is a follow up to the blog post that happened last week on OLTP, or Online Transaction Processing. This is where he went back approximately 20 years from version 8.0 up, and he's tested how each version of Postgres has improved its performance this time relative to analytical workloads. So he's actually using a smaller piece of hardware for this and he's using the TPCH benchmark. He has his configuration he set up here and these are the results. And as opposed to the charts going up and to the right, they kind of go down and to the right because as opposed to measuring tps, he's measuring durations in seconds. So things are getting faster over time. So again, on the Y axis you have duration number of seconds, on the X axis you have the version. Now the first chart we look at is data load, so how fast it is to load the data. And again things have gotten faster as new versions have come out. And you'll see the parentheses P that basically means parallelism was utilized in doing the load. So things definitely get faster in terms of building. And then he breaks out what the responsibility of different areas were and how you can see the improvement, like creating indexes has gotten a lot faster over time, as you can see here because that's the dark blue. Then he took a look at the queries. First he looked at an uncached case and you can see things have been pretty stable since I guess around 11 or maybe even the version 10 series. Then he breaks it out by individual query, which is kind of hard to see. But then he looked at the cached results again. Ever since about 11 it seems to have been pretty consistent. Then he talks about some of the main improvements that happen through different versions. So if you want to learn more, definitely check out this blog post.
[00:08:19] Next piece of Content SQL best practices don't compare count with zero so basically he's taken an example if someone does a query and he wants to find where the count from another table is zero, so basically make sure no addresses exist for this particular user. So basically find users that don't have an address. The count of addresses for a particular user is zero. And he said this is awful painful to me, but there exists an exists expression so you can say where not exists and basically use the same query. And it's much more efficient. You don't have to count everything, you just need to know. Give me the first one. Now personally I actually don't use exists a lot. I know there's certain cases where it's faster, but I generally tend to use joins like do a left outer join and look for the null case in the secondary table, the addresses table in this case. But I found the comments of this post interesting as well. Like you can see here, he's using the left join technique checking for null and Dabesh says it actually depends on data distribution which method is faster, either the join or the exists. And we've seen some blog posts that measured that. And Lorentz says the not exists subquery should be executed as an anti join by the postgres optimizer anyway. But if you want to learn more, definitely check out this blog post.
[00:09:42] Next piece of content There was another episode of Postgres FM this one was on PGStatStatements track planning. Basically Nikolai Michael discussed should you enable this or not? Because. Because by default when you install the PGSTATStatement extension, it is not enabled. So this kind of leaves you a blind spot as to what is happening with regard to planning. So you'll get execution time but not the planning time. Now I'm guilty of that myself by fixating on the execution time and I don't focus a lot on planning time, but the more indexes you have on a table, the more joins you're doing or the more partitions that are included as a part of the query, that planning time is going to creep up and sometimes exceed the execution time. So it is important to go ahead and turn on track planning to know about those things, and they had a whole discussion on whether track planning should be turned on by default. And ultimately they came to the agreement that yes, it should be because the reason it was actually turned off is because someone noticed poor performance. But Nikolai mentioned he thinks that's mostly from synthetic benchmarks, because it is easy to see pgestat statements and even track planning impact performance when you're using synthetic benchmarks, like sending the same query to the system many, many different times. So he says you'll probably rarely see this in a production scenario. Basically, you have to be living on the edge and pushing Postgres to its limits to be able to see some of these observer effects with PGSTAT statements and track planning. So all in all, they felt it was more beneficial to have it on than have it off. But if you want to learn more, you can listen to the episode here or watch the YouTube video down here.
[00:11:30] Next piece of content Unique constraint violations during inserts cause bloat in PostgreSQL. This is from Creditive DE and we have talked about blog posts that mentioned this before. Basically whenever an insert happens into postgres, it starts doing the insert process and and then it checks the indexes. Well, if you have a unique index and there's a violation of that, it now has to cancel that insert essentially. But you still use a transaction id. You're still creating a row that then needs to be cleaned up and that increases bloat. But a way to get around it is to do your insert with onconflict. Do nothing because then it checks indexes first before it actually does an insert. And this way if there is a conflict, it doesn't create a new row, it doesn't use up a transaction id. It should be easier on the system and it doesn't generate more bloat. Well, they went through a set of tests here showing this with their experiment. So if you want to learn more, definitely check this out. Next piece of content what I wish someone told me about postgres this is from chalascript.com and this was a pretty interesting read of what he wish he knew when he started using postgres and I'll just run over very quickly some of the main topics here. He's got a convenient index so he first says normalize your data unless you have a good reason not to. Next is follow all advice from folks that make postgres and some of the ones he mentions is use the text type for all text storage. Just use the timestamp z or the time with time zone and name all your tables in snakecase. Some of the other areas are Note some general SQL eccentricities.
[00:13:14] Next is you can make PSQL more useful. So the more you know about it, the more you can do it's possible that adding an index will do nothing, particularly if it's misconfigured. So basically this goes under the heading why won't it use my index? So he talks about different reasons why an index wouldn't be used. Long held locks can break your app, even access share. So he covers that and follows up with JSON B is a sharp knife. So basically my opinion is kind of use it sparingly. But if you want to learn more, definitely encourage you to check out this blog post.
[00:13:50] Next Piece of Content Cloud Neutral Postgres Databases with Kubernetes and cloud native PG this is from enterprisedb.com and this was an interesting post. I didn't know what cloud neutral meant and basically he means being able to set up a cloud that you're not beholden to any particular cloud provider and Kubernetes kind of offers that it's an open source way to put your infrastructure up on any cloud, even on your own premises or under a particular vendor or even multiple vendors. So he talks all about running Postgres and Kubernetes and particularly with an emphasis on cloud native PG because of course that's the postgres operator that he is developing and he says it is an open source project, it is run by a vendor neutral community and he says it's the only postgres operator run by a vendor neutral community. He says that while it did come out of EDB they donated the intellectual property to the Cloud Native PG community in May of 2022. Andy does make a call out to PostgreSQL DBAs is that you know, you should probably start learning this stuff or at least getting your feet wet into kubernetes and running postgres on it. Because at least to his perspective it looks like you know, it's going to be the future. But if you want to learn more, definitely check out this blog post. Next Piece of Content Amazon Aurora DSQL Features so this is an announcement from AWS and I don't see where DSQL is supposed to mean. My assumption is it means something like distributed SQL but it's probably supposed to be like a planet scale SQL database and offer 99.999% availability across multi regions. This is in preview and they say it's postgres compatible so it's definitely not postgres. There are certain things that it can't support. If you look in their documentation here you can see the different unsupported objects such as views, temporary tables, triggers, your own types, sequences aren't supported. There's particular constraints that aren't supported, like foreign keys or exclusion constraints, and of course different operations. But if you want the trade off of not these things but essentially planet scale database systems, definitely feel free to check this out. The last piece of content postGIS Day 2024 happened. I believe it was last week or a week before last. Well, all of the videos have been posted on this YouTube channel here, so you can feel free to check that out. Or there is also a PostGIS Day 2024 summary that was done on the CrunchyData.com blog. So if you're interested in learning more about that, definitely check this out.
[00:16:36] I hope you enjoyed this episode. Be sure to check out scalingpostgres.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 and I'll see you next week.
