background

Vacuum Speed Up, Faster Foreign Tables, Fast Queries With Union, Query Optimizer | Scaling Postgres 158

Join Over 1,000 Engineers & Get New Episodes Weekly!

In this episode of Scaling Postgres, we discuss a vacuum speed up and faster foreign tables in Postgres 14, running faster queries with union and learning about the query optimizer.

Content Discussed

YouTube Video

Podcast Audio

Transcript



[00:00:00] In this episode of Scaling Postgres, we talk about a vacuum speedup, faster foreigntables, fast queries with union, and a query optimizer. I'm Kristen Jameson, and this is scalingpostgres episode 158.

[00:00:22] All right, I hope you, your friends, family and coworkers continue to do well. Our firstpiece of content is speeding up recovery and vacuum in postgres 14. This is from Citusdata.comand they're talking about an enhancement that has been made recently to the future postgres 14to be released in the fall. And what they changed was the compactify tuples function. Now that'sused in three different areas. One, when PostgreSQL starts up after a nonclean shutdown,basically a crash recovery. Also during the recovery process that physical standby serversconstantly run to replay wall changes it's used there as well as by vacuum. Now, when they didsome profiling, specifically the area that had issues was a high CPU load coming from heap toclean wall records and basically they tracked it down to this function. And what this doesbasically this is an example of a Heap page in postgres. So you have the page header and anarray of items that have pointers to the actual tuple of data within the page. And during updatedelete operations, certain space becomes unused so it's a no longer needed tuple and it needsto be vacuumed up eventually. But those three different processes I mentioned actually do thingsto compact the space within a page. So basically after compaction it looks something like thistoday and you can have the tuples essentially out of order from the array of items at thebeginning of the page. Now, in terms of the rewrite compaction process, the area of compactifytuples that was taking the longest was actually the QSort function within it to make sure you'renot overriding what you need during the compaction phase. And basically they reengineered itso that you don't have to do a sort operation at all. Basically, it keeps the items in order to ensureit can always write without damaging compaction. If things do come out of order, they actuallycopy some items to memory to be able to do the compaction of the page, but basically they areavoiding that sort. And of course the blog post goes into a lot more detail about that, but they dohave some performance changes that they show here. So number one, they check the crashrecovery because that seemed to give the most bang for the buck with changing this and doing aPG bench run with 10 million rows in the database, doing a bunch of updates and then forcing acrash of the system, therefore requiring a recovery when it comes back up. After the change, itcame up 2.4 times faster, so more than twice as fast. But a crash recovery doesn't happen allthat often. What does happen a lot of course, is vacuum. And actually the vacuum performancechanged as well. So now vacuum with this change runs 25% faster. So anything that can makevacuum run faster is going to be better. So to me, even though it's not over twice as fast duringrecovery, vacuum is going to happen so much more frequently that this is the more importantspeed up in my opinion.

[00:03:32] Now of course, this also impacts the recovery process of replicas as well, so thatminimizes the chance that they're going to fall behind the primary. So, definitely some greatenhancements coming in postgres 14. And if you want to find out more about the details,definitely check out this blog post.

[00:03:49] The next piece of content is Faster bulk insertion to foreign Tables introduction toPostgreSQL 14 Committed Features and this is from Postgresroad Blogspot.com and this istalking about an enhancement that's been worked on for Postgres in terms of inserting datathrough a foreign data wrapper. Now, this post goes into a lot of the overview about why they'reworking on this in particularly. And they're mainly focusing on the scale out potential of postgreseventually. And getting the foreign data wrapper working efficiently is going to make Postgresscale out faster. So they had someone who was testing this and they were seeing some poorperformance. So they took a look at it and basically it looked like the insertions were happeningrow by row at a time. So very inefficient, particularly if you have a network round trip happeningto an external foreign server. So basically they developed some batch methods to do theinsertions and they have some of the timing changes they saw here. So for --example, with a local table without using a foreign data wrapper, the insertions that they testedhappened in 6.1 second before their improvement using a foreign data wrapper, it was 125seconds after they made their improvement. The insertion into the foreign data wrapper was11.1 second. So it's about twice as slow doing it to a local table, but eleven times faster than itcan do it in postgres 13 and below. So definitely a huge improvement, but it's still going to havesome sort of an impact compared to local table insertion. Now, they did mention that the foreignserver that they're using is actually on the same machine, so there's no network latency, so youhave to take that into account. And then they also split the target table into eight hash partitions,so presumably multiple tables that they're pushing out the data to. And with that it was just a littlebit slower at 12.5 seconds compared to the 11 seconds before, but even the local table wasslower. So definitely some great performance improvement improvements when doing inserts inparticular to foreign data wrappers in this post. Also mentioned that they're also working on theability to use multiple CPUs as well. So maybe you can paralyze that across multiple differentservers to do the inserts. So, some more interesting changes coming in Postgres 14 and if youwant to find out more, definitely check out this blog post.

[00:06:14] The next piece of content is speeding up SQL queries by orders of magnitude usingUnion. This is from Foxtown Systems and basically they had to develop a pretty comprehensivequery to return some data and they have what they called a Diamond Schema. So basicallythere's multiple paths to query it because everything is kind of interrelated here. You'll see,essentially it's a circular way you could do joins and when they pulled out one segment of thedata that they needed, it ran in 1.5 milliseconds. So that's super fast. But then they joined to theother set of data they needed to bring in and they were using a fair amount of ores. So anytimeyou have ors you can think of, okay, this is probably not going to result in some greatperformance and with doing that, adding in that extra data with the join, it finished in 3.2seconds, so 3000 times slower. Now some of that was due to there's more data in some of thetables that they joined to, but what their idea was is hey, maybe we can query both of theseseparately and then join them together as opposed to doing using the Ors. So basically theytook the original query that's up here that's not using any Ors, it's just pulling the exact dataneeded for one set of data and they're calling the employee meal items and then they didanother query to pull the customer meal items. So they query it separately, not using any ors.Then they took those results and they merged them together using a Union all. Now the originalquery ran in one millisecond, the second one doing the customer meal items ran in 102milliseconds. So taking them both and then doing a Union awe to these queries ran in 112milliseconds. So pretty fast and much faster than the more than three second query. And ofcourse, they have some conclusions here, but basically what I look at this as when you have orsyou're essentially going to slow down your queries and this is one technique you can useaddress each of those queries individually as smaller, shorter queries and then union them up ormerge them together at the end. Might yield better performance, as this example demonstrates.So if you want to learn more about this technique, definitely check out this blog post.

[00:08:39] The next piece of content, how the PostgreSQL query optimizer works. This is fromCyberTech Postgresql.com and here they're just going through all the different techniques thatthe optimizer uses to determine the best path for queries in postgres. Now this is a verycomprehensive blog post and they talk about a variety of techniques used by the planner andhow he's demonstrating this is he uses queries and then doesn't explain to see, okay, what is theoptimizer actually doing. So here he demonstrates things like constant folding, a function inliningdetermining whether to run a function once per row or once that can be applied to multiple rowsby determining whether it's volatile, stable or immutable, looking at inlining and subselectflattening, and covering things like the different types of joins. So this is a very comprehensiveblog post --that I encourage you to read if you want to learn more about how the optimizer works totranslate your SQL into the actual plan that's executed against Postgres, because it can helpyou get in a sense of why Postgres is doing what it's doing. So if you want to learn more aboutthat, highly encourage you to check out this blog post.

[00:09:57] The next piece of content is how to check and resolve bloat in PostgreSQL. This isfrom Haigo CA and the first thing they cover about why bloat happens. And basically withPostgres's MultiVersion Currency control, every update actually happens as an insert, and thenthe old row is eventually not used anymore and can be vacuumed up. Same thing with thedeletes. The row isn't actually deleted, it's just marked that is no longer there, and theneventually it can be reclaimed. But that process of vacuum doesn't free up disk space. Sobasically you can have bloat if space cannot be utilized. Now, how to detect it? There's a fewtechniques you can use, but they're not very accurate just using base postgres. But there's anextension, PG Stat Tuple, that probably does the best job of determining how bloated your tablesare. So here they did a test and they found for a fresh table the bloat would be 0%, whereas theydid a I believe it was a delete process, and then they showed that the bloat was at 30%. Andsimilarly, indexes can also be bloated and they show the bloat here by checking an index. Nowthen the question comes, once you've identified bloat, how do you deal with it? The first optionthey discuss here is vacuum full. So basically this essentially rewrites the whole table. Thedisadvantage is you cannot read or write to it while this vacuum full is going on. So that's reallyhard to do. Cluster is similar in that it's rewriting the table, but it's organizing the data on disk tothe equivalent of how an index is set up. So you cluster to a particular index and it orders thedata that way. But much like vacuumful, you cannot read or write to the table. Pgripak is aseparate extension that does online rewriting of the tables and indexes. So you can leave thetable online and use Pgripak. The downside to it is that it does take a fairly long time to rundepending upon your table size, and you're going to use two to three times the amount of spaceof your table to do it. So if you're short on disk space, this unfortunately isn't really an option.Then the other option is reindexing. So this just addresses indexes. You can reindex them quiteeasily doing reindex concurrently on the most recent versions of Postgres. So that essentiallyrewrites the index. But of course it doesn't do anything to assist with table Bloat. But if you wantto learn more, you can check out this post.

[00:12:20] Next piece of content is waiting for PostgreSQL. SQL 14 allow configurable LZ Fourtoast compression. So by default Postgres does compress toast. So toast is where Postgresstores data that exceeds the space within a tuple, so it's a separate storage area. What it storesin that toast area is compressed. By default, it uses Postgres's implementation of the LZalgorithm. But in this patch in postgres 14, they've added the option to use LZ Four. Now youneed to do a number of things to enable it, especially building it using the with LZ four. So youkind of need to know ahead of time you want to use this LZ Four compression, but theadvantages was about twice as fast as the native compression. So if you're looking for morespeed with using compression with your toe stables, you may want to check out this newcompression coming in postgres 14. So if you want to learn more, definitely check out this blogpost.

[00:13:20] The next piece of content sharding postgres on a single citus node. How, why andwhen. So this is talking about the new citus ten that is open source. You can now do distributedtables within a single node. And then of course the reason is well, why would you want to dothat? Now, this post lists four reasons you would potentially want to do that. One is queryparallelization for multi shard queries. Basically, you can get query parallelization by setting up adistributed table within a single node, although Postgres already has some parallel features, so Idon't know how advantageous this is compared to those features. They didn't discuss that here.Second reason is smaller indexes to create and maintain. So you can definitely do that with this,but you can also do that with partiti --on tables if you happen to want to partition that data.

[00:14:11] Smaller tables to auto vacuum in parallel. Again, partitions can give you that. So Idon't know how big of advantage this is compared to partition tables. And then faster bulk dataloads, which you could also get with partition tables. So a lot of the reasons they give here, youcan get them with native postgres otherwise. So that only leaves the reason that you eventuallywant to scale out. Like if you envision your database growing super fast and needing to scaleout, then at that point it makes sense to go with citus, it seems like. But they talk about how youdo it, how you would set it up on a single node, and then once you're ready, how to actually bringup different worker nodes to be able to send those sharded tables to when you want to scaleout. So if you want to learn more you can check out this blog post from Citedata.com.

[00:15:02] The next piece of content is actually a YouTube channel. This is EDB and in the lastweek they've had a number of postgres webinars that have been posted, each about an hour inlength. So if you want some postgres video content, definitely check out this YouTube channel.

[00:15:20] Next piece of content is explaining your postgres query performance. This is fromCrunchydata.com and they're talking about Explain and Explain Analyze for analyzing yourqueries to make them faster. Now this is a secondary post where they're talking about using PGStats statements to identify the statements that are taking the most time in your database. Wellthen you want to look at each statement using Explain Analyze to determine how to optimizethose queries so they're running faster, taking less resources, so they go through the process ofexplaining being able to interpret the output of Explain Analyze to help you optimize yourqueries. So you can check out this blog post.

[00:15:58] The next piece of content is ansible collection for PostgreSQL and EDB components.This is from Enterprisedb.com and they've set up an ansible collection called they're calling EDBPostgres with a link to it right here on GitHub. And basically they've defined a whole bunch ofansible roles for setting up different components of a postgres infrastructure and I believe they'reusing Red Hat Enterprise Linux for doing this because they also intend in the future to add aDebian and Ubuntu support offering more backup recovery options such as PG backrest andsome more. So if you want to try to use these playbooks and roles or even get a sense of howthey've chosen to set them up, you can definitely check out this open source project.

[00:16:47] The next piece of content is no space left on device. This is from Mydbainobook.organd this post talks about what happens when you run out of space on your database server.Basically the first thing is you need to add more disks or remove any unnecessary files. Nowwith regard to this, the very important point they make here is that whatever you do, don't touchanything inside of the data directory of postgres, especially one called Pgxlog in versions prior toten or PG wall in versions ten and higher. But once you're doing that, some other things to keepin mind is that with that disks running out of space, did you have any walls corrupted? And theysaid you could check the log for some messages to see if that's potentially the case and waysyou can resolve the wall corruption. Ideally you restore things from backup, but failing that youcan examine the PG reset wall tool but you need to be very careful with this. So definitely whatthey say here quote I strongly encourage you to read the whole documentation page of it, sodefinitely, definitely agree with that. And you can also check for data corruption as well in yourdata using the PG checksums, and they have a little quick example of what it shows in the logsonce they've relieved the space issue and the database coming back online. So you can checkout this blog post if you're interested.

[00:18:06] The next piece of content is performance improvements in GEOS. This is fromCrunchydata.com. They're talking about GEOS, which is a library that PostGIS uses for doingthe quote hard computational geometry functionality such as intersections, unions, differences,buffers of geometry. And they're showing here from version 3.6 onto the mainline, all thedifferent performance improvements they've made to different capabilities of the GEOS library.So this --is a great example of how they're really driving forward the performance of this library in orderto help PostGIS in general. So if you're interested in that, you can check out this post.

[00:18:47] Next piece of content is how to get the status of a cloud native PostgreSQL cluster.This is from Enterprisedb.com, and they're talking about how you can use the Kubectl, which ispart of the I believe the Kubernetes controller, to be able to get a status of your postgres cluster.So if you're using Kubernetes with Postgres, maybe you want to check out this blog post.

[00:19:12] And the last piece of content is the PostgreSQL Person of the Week is Julian Ryu. Soif you're interested in learning more about Julian and his contributions to Postgres, definitelycheck out this blog post.

[00:19:24] That does it. For this episode of Scaling Postgres, you can get links to all the contentmentioned in the show notes. Be sure to head over to Scalingpostgres.com, where you can signup to receive notifications of each episode, or you can subscribe via YouTube ride tunes.Thanks. --

episode_image