Episode Transcript
[00:00:00] So Aural DB is now in public beta.
[00:00:04] Now, I'm excited by this, but I'm also a little saddened by this. But we'll go ahead and talk about that. I hope you, your friends, family and co workers continue to do well.
[00:00:16] Our first piece of content is oreldb public beta, a Postgres storage engine for update heavy tables. This is from supabase.com so the oreldb team did join supabase and now they are moving oreldb into a public beta. Now they say it is an open source storage engine for Postgres and it's available on your Supabase plan. I'm not sure if you can run it yourself yet, but they do say, quote, it's not ready for production yet.
[00:00:47] So they go through and describe what OralDB offers. Primarily, it's moving from storing dead rows dead tuples in the heap into storing them in an undo log.
[00:00:59] And this should have an advantage for update heavy workloads because every update doesn't create a new row. Yes, it enters something in the undo log, but as long as you don't do a rollback it would be much faster just to update in place. But it has more changes than just that. So yes they are using an undo log. Instead of dealing with leftover dead tuples in the heap, they are moving to 64 bit transaction IDs. They do have index organized tables. What that means is that normally you have the heap and then the indexes are a separate structure, whereas in OralDB rows are stored inside the primary key index rather than a separate heap. So a primary key lookup just reads one structure immediately, it doesn't read the index and then consults the heap and then the secondary indexes point to the primary key. Also there's no buffer mapping, so in memory pages link directly to their storage pages and as a result they say page reads are lock free. And they're also using copy on write checkpoints and row level wall, which means they can just write the data that's changed and can reduce disk I O on write heavy workloads. Now this particular beta is on Postgres 17.0 11, so it does not support the 18 series yet.
[00:02:18] least that's my understanding.
[00:02:19] Now I did mention I'm excited about this because these are all very interesting, but I'm a little bit saddened by it. The reason is because for the longest time it would be great to have a separate storage engine in Postgres that handles update heavy workloads or better handles update heavy workloads because I keep talking about articles and scaling Postgres. If someone has a queue system that gets a lot of updates and inevitably it runs into problems. Why? Because of vacuum. Taking a while to vacuum things up. So if we had a storage engine that just did an update in place with an undo log and you could just create a Cubase table using this different storage engine, that would be ideal. So it would just be another storage engine to use as opposed to Postgres default heap with MVCC where the versions are stored in the heap. But whenever I see any project looking to do this, they always add more to it.
[00:03:16] So this project has added 64 bit transaction IDs, they have changed things to index organized tables. So there's all these other different performance improvements they've changed. So it's not as simple as just giving us another storage engine where update heavy workloads aren't as onerous.
[00:03:35] So if you're moving from Postgres onto one of these new solutions, you have to deal with all of this newness. It's not just testing an update heavy workload on a different storage engine, but because trying to get a new storage into Postgres has had a bunch of fits and starts. Maybe this is the only way it can be done where you need to make changes in substantial performance in order to do all the work to be able to support MVCC using an undo log. But they did talk about performance and they used a TPCC benchmark and on a 32 VCPU instance with 128 gigabytes of RAM, it was able to process 1.8 times more transactions per minute compared to Supabase Postgres, which is basically a hosted Postgres and also 46% more throughput per dollar than the heap. Now, when the data fit into memory, the gap was smaller. So they are demonstrating more performance. But it's not something that blows the barn doors off necessarily. But they say here, quote, you know, the performance benefits of Orel DB depend on your workload and it helps the most with tables that are updated or deleted often.
[00:04:46] So updated order status, inventory counts, job queues, which I mentioned, basically the places where they say heap storage builds up the most blue. So again, it would be great just to have a quote unquote simple undo log storage system we could add alongside Postgres as opposed to having to commit to a larger database product. Because with all these changes they say here workloads that depend on non b Tree indexes like PGvector HNSW may not see gains yet since those indexes run through an experimental bridge. So again, with all these changes they're having to build more into the product to be able to keep feature parity with Postgres. But if you want to learn more, you can definitely check out this announcement and even try out the public beta. Next Piece of Content Cybertek PGEE Gets a GPU Execution Path this is from cybertech PostgreSQL and this is a follow on from the very short blog post or announcement I mentioned last week where Cybertek is now offering a way to process queries on GPUs and this was prompted by them asking the question what happens when PostgreSQL gets a GPU execution path and the Planner decides when to use it? So basically the Postgres planner is in control on whether it uses a GPU execution path or a CPU execution path. The Application still speaks PostgreSQL as it always has. All the SQL stays the same. It's just how those queries get executed. Quote the GPU becomes another execution option available to the Planner. Now, I haven't seen any mention of this being open source. I'm sure this is a paid product, but still it's super fascinating if you're the right kind of customer for something like this. So they say basically the Planner costs different paths alongside the CPU alternatives, and when the GPU path has a lower cost and the engine can prove that the operation can produce a correct Postgres tool result, Postgres can choose it. So I'm not sure about that decision matrix how that happens. But another interesting thing is that a single query can use both. So large scans and aggregates can run on the gpu, whereas other parts of the query like index scans can continue on the cpu. Okay, here it says the GPU only gets work it can handle correctly. So basically large parallel data processing. So it accelerates operations like scalar aggregates such as count, sum, average, min, max group by distinct analytics, parallel aggregates, et cetera. They have certain types of defined data types it can work with. And it's interesting, they say they keep the PostgreSQL visibility rules intact. And this is an interesting thing where they say the data stays on the GPU because a lot of this insane speed is getting that data into the GPU memory. So they say here, quote There is a cost to first execution. A cold query has to build a cached representation of the data, so basically it needs to be loaded up. But once it's there they say GPU performance is simply mind blowing. And that data representation does get packed down. For example, he says a 57 gigabyte retail data set occupies roughly 9.5 gigabytes in GPU memory, so about 20% of the size.
[00:08:02] And an IoT data set measuring 158 gigabytes shrank down to about 38 gigabytes. So again, maybe about 20, 25% of the size.
[00:08:13] Now, last week they did mention an example where GPU execution time went from 159 seconds to 2.8 milliseconds, which is 57,000 times faster. And they said, you know, that's kind of slow because the Nvidia L40s chips they're using are old. On the modern H200 cards, they've seen something that's 100,000 times faster. Single GPU core. And you can have multiple cards and get close to a linear scale factor they've seen with four cards, about 3.7. And they say here, you know, multiple GPUs can work on the same query, but if you're working with the right type of data and Postgres, this could blow the doors off of anything seemingly just using raw hardware power.
[00:08:57] Like, for example, Oriel db. Their benchmark result was two times faster, whereas certain query results, once the data is loaded up, they're reporting as being 100,000 times faster, which is insane. But if you want to learn more, definitely check this out. Next piece of content. There was another episode of Postgres FM last week. This one was called Dump Restore. Done.
[00:09:20] Now what? So Nick was joined by Kirk Wolak and they discussed, once you've taken a PG dump and you've PG restored it, what are additional steps you should follow? Now all your data is restored, but is it ready for production level workload? And they specifically mentioned three things that are missing that can tank your performance until those three things are in place. Now, the first thing they mentioned was, of course, statistics. So your statistics need to be rebuilt. Now, on more recent versions of Postgres, those statistics can be carried over, so that's advantageous. But normally you're going to want to run analyze to make sure your statistics are up to date after loading your database with data, the second thing that's missing or isn't updated is the visibility map. And the third thing are hint bits. Now what updates those are, of course, vacuum. So basically, once you have restored your database, you definitely want to run vacuum analyze on your database so you can get the maximum performance out of it. And what's interesting, they also looked at documentation for 28 different platforms and they mentioned only eight of those, 28 actually mentioned the need to analyze after loading data and only three mentioned needing to run vacuum. They also discussed it would be great if Postgres could actually include an vacuum analyze run after doing a PG restore so that people don't have to remember to do this. And they even showed some statistics with regard to performance after you load data compared to running a query before vacuum analyze has been run and then after. But definitely they talked about a lot more subjects. If you're interested, you can definitely listen to the episode here or watch the YouTube video down here.
[00:11:02] And related to that Postgres AI has a blog post related to the show Dump restore done. Now what? And here they have a lot of the graphs of the things they covered. So for example, this is one query after logical restore on Postgres 18.
[00:11:18] If you just run the query immediately, it runs in 32 seconds. After a vacuum analyze, it runs in 23 milliseconds, which is about 1400 times faster. So it's no joke. You need to run vacuum analyze. They show some of the reason for this looking at how the database engine treats things. But if you want all the detail about what they discussed you you can also review it in this blog post here.
[00:11:43] Next piece of content, a lesson in data integrity. This is from mydbanotebook.org and she said they had a colleague come to them and say, hey, we need to add a region column to this table and we're just going to do it as a text column. And she basically said she did not approve that PR now because the code is wrong. But she challenged how are we maintaining integrity?
[00:12:08] And they said, we want to enforce integrity inside the code and not the database. And she said, no my friends, a thousand times no. So that might work if you have one application, but suddenly as your application grows or the use case for this database grows, maybe another application connects to it. Maybe you have a PSQL query that you're trying to load some data into it. You have batch imports. Now suddenly those application rules don't matter and you need that integrity in the database. Then she talks about four ways to handle something like a region. The first is to use a domain where it's basically creating your own reusable type. So you're defining region name as a text and what values are possible in it. So this is very convenient. But I was like looking at the cons. The cons she says, is basically a drop domain cascade because Postgres drops every object that depends on that domain, including the columns that use it. So that's a huge dependency there. Next is an enum and the big con with it is you can append to an enum, but you can't remove a value ever. You can alter them of course, but you can rename them. But still, personally I don't use enums either. Third is a text column with a check constraint. So this had the lowest cons in terms of nothing protecting you from adding the constraint in a way that takes the table offline.
[00:13:31] So basically if you have a big table, you need to be cautious on how you add this. And the fourth is a reference table. So you create a separate regions table and then you create a region ID foreign key in it. So that just has some more moving parts. You maybe need a join, but choosing this option to me kind of depends on your use case. In their particular use case they just went with a text column with a check constraint. But the problem is that check constraint needing to be validated because it will do a full table scan if you just add that column with the check constraint.
[00:14:04] So the best way to do it to avoid holding a lock while that whole table is being scanned is to add the column and the constraint and make it not valid. And ideally go ahead and define a default as well. Then as a separate step, you validate that constraint. Now that still scans the whole table, but you're not holding open the exclusive lock to add that column during that time. And she also covers the situation where you may need to change those values at some point in the future. But it is something you can do. And I like this particular quote here with regard to integrity and whether it should be in the application of the database is that two lines of defense are better than one. But if you want to learn more, definitely check out this blog post.
[00:14:46] Next piece of content How a read replica almost took down its primary database and other database tails. This is from photoroom.com and they've come up with six lessons that they've developed based upon things they've experienced. And I hate to say it, but I keep seeing people experiencing the same lessons, but the first one is batch and throttle backfills because they were loading a lot of data into their database and the replicas started falling behind and as a result their writer was retaining the wall logs on the primary of course, and they grew from around 5 gigabytes to 31.7 terabytes. I hate to say it, but I would lose my mind if this happened. End quote. Replica Lag was increasing by roughly 0.7 days each day. So basically what did they do? Well, they paused the transformation, enlarged the size of the replica I'm assuming made it a more beefy machine, and Wal began draining the database back to normal. But the problem was they were only using 13 terabytes on a 43 terabyte disk. Now that was costing them an extra $6,000 a month, and resolving that essentially resulted in some downtime. So definitely keeping an eye on your replica lag and how far is falling behind is super important.
[00:16:04] And they also discussed setting max slot while keep size, which can be beneficial, but the problem is you don't want your replicas suddenly dropping off in case you need them in a failover scenario.
[00:16:15] The next lesson set short lock timeouts and retries. So clearly something got locked.
[00:16:22] So again this is going back to whenever you do a migration, use a lock timeout because it'll halt that migration if it ends up locking other processes longer than that lock timeout. Next lesson deploy schema changes in compatible stages.
[00:16:36] This is just making sure your database columns exist before the application starts to use them, or even the reverse. If you want to remove a database column, make sure your application is aware of that beforehand. So they discuss some of that here. Next, budget connections for the transition, not the steady state. So this is the case where they were bringing online a pooler and their connections peaked 40% higher than expected.
[00:17:00] But when you're bringing on a pooler it's going to make more new connections to your databases, so you need to take that into account and make sure your max connections can handle that increase.
[00:17:11] Lesson 5 Check query plans against realistic data so this is where they had a query that ran fine on the test system with presumably no data in it, but when they deployed to production it sent the database CPU to 100%.
[00:17:26] So definitely checking on realistic data for those queries. And last turn lessons into automated controls. And for this they built their own zero downtime migrations. Linter so this is presumably a tool or a library that checks your migrations to make sure you're not doing anything that will inadvertently bring your database down. But check this out if you want to learn more. Next piece of content Tuning Postgres for analytics this was a presentation@pgconf eu this is from karenjecks blogspot.com and what I thought was most interesting is further down in the presentation here where she covers things. If you're wanting to run analytics on your same operational database, definitely consider a separate analytics user that has different types of permissions. Maybe they have their own session pool. They have adjusted work memory configuration, adjusted statement timeouts like she's showing here.
[00:18:22] Different indexing strategies, specific indexes for analytics queries, but not too many. Maybe you want to do some index on expressions and what else you can do. You can pre calculate data so you could do store generated columns on a table. To speed things up, you can use materialized views. If that doesn't work, you can consider a separate analytics database so they could read off of a physical standby server. Or. Or you could do logical replication just to pull the data that they need to analyze into its own analytics database through logical replication. And then she of course mentions PG Lake and other extensions for doing analytics work in a different database. And she is an engineer at Snowflake, so of course she mentioned something about that as well. But check this out if you want to learn more. Next Piece of Content what was the turning point for Postgres? This is from planetscale.com and it's talking about why Postgres has suddenly become one of the more popular choices for databases nowadays. So you can check this out if you're interested.
[00:19:24] And the last piece of content, there's a PostgreSQL 18 query optimization book available that appears to be a free download, so you can check it out if you're interested. 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 find an audio version of the show, as well as a full transcript. Thanks. I'll see you next week.
