background

Slow Archival, Index Improvements, Node Metrics, Join or Subquery | Scaling Postgres 131

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

In this episode of Scaling Postgres, we discuss slow WAL archival, index improvements coming for Postgres 13, accessing node metrics via SQL and deciding between a join or subquery.

Content Discussed

YouTube Video

Podcast Audio

Transcript



[00:00:00] In this episode of Scaling Postgres we talk about slow archival index improvements,node metrics and joins or sub queries. I'm Kristen Jameson, and this is scaling postgres episode131.

[00:00:18] Morning.

[00:00:23] Alright, I hope you, your friends, family and coworkers continue to do well. Our firstpiece of content is why PostgreSQL wall archival is slow. This is from Procona.com and they'retalking about a problem where your wall backs up and fills up the PG wall directory because itcannot archive those files fast enough. Now, they mention here that they've seen problemssimilar to this, but normally the culprits have been one failing wall archival. So there's some sortof error in the archive command that's configured that it can't write the files to the destination, forexample. Or two, there's an orphan replication slot that is preventing the old wall files from beingarchived. But they've noticed a new problem that's happening more and more where you have alot of wall generation going on and it's basically causing a problem where it can't archive fastenough. Now, they say this is generally due to two issues. One is the rapid generation of wallsegments. So as databases become larger and larger and there's more activity going on, youhave a lot more wall files being generated. And then the second issue, the archive command isactually configured to write to a remote storage location that has additional latency involved. Andbasically those two issues can cause real problems with the archival process being able to keepup. And I myself have witnessed this as well, but I've even seen it when there's so much wallbeing generated, even with a local file copy that it couldn't keep up. So it's kind of shifted up thisto be a bottleneck of the whole system. So this is a very timely post for me in terms of seeingwhat they discuss here. Now, the next thing they cover is the nature of wall archival beingsynchronous. So there are some weights that happen as a part of it. And they have this diagramhere where you have a wall segment that is finished. It writes a ready file in the archival statusdirectory and then signals the archiver that hey, there's a file that you need to pick up. So thisarchival process wakes up and goes to the list and it goes through every single ready file that'sthere looking for the oldest one to be archived and it calls one right after the other. Now, thiscauses a host of problems. So the first problem they mention here is quote, a method of findingout the oldest wall segments one by one and archiving them one by one is not very efficient. Sothe more ready files that you have in the archive status directory, the longer that whole processwill take. So basically the more wall files you need to be archived, the slower it gets. So it'sbasically a downward spiral once you start lagging in this way. The second issue is that thearchive command executes these commands and then waits for them to return before going onto archive the next file. So basically it's a synchronous process and if there's any delays likeyou're trying to copy it to a remote location or even having difficulty writing efficiently to a localdrive, it's going to slow down this process. In other words, it doesn't do this asynchronously, say,pass it off to a command and then look for the next file to archive. It actually waits for the wholeprocess to finish. And then the third issue is that the archiver will wait for a second or morebefore reattempting if there's an issue. So this can further cause delays to be a part of it andthey discuss some more issues with the implementation. But really what it's looking to me likethis is an area ripe for optimization of postgres itself on finding ways to just make this wholearchival process more efficient. But in terms of an immediate solution, what they mention here issince the archive command just passes it off to another tool or utility or program, make that anAsynchronous program that just accepts the input and then starts doing its process and thenreturns immediately. So here they mentioned that PG backrest has just such that capability, so ituses multiple background workers and essentially once it gets called, it returns immediately. Sothe archive command can then do its next piece of work, but then it uses those processes toactually write the files, copy them to different locations, et cetera. So I found this to be a reallygreat post discussing the --problems and even some of these problems that I've seen. And they do mention one solution,but I think long term this would be an area ripe for improvement in postgres to make sure it canhandle more transaction throughput. So if you're interested in that, check out this post fromProcona.com.

[00:04:54] The next piece of content is another post from Procona.com. It's index improvementsin PostgreSQL 13. And this is just basically a list of all the index improvements they'vediscovered in postgres 13, I believe beta three. So the first thing they mentioned, the big one isthe deduplication of the Btree indexes and they show how some of the improvements can getyou space savings looks like up to a third in some cases. They discuss gist in SP gist being ableto handle box point distance lookups, allowing Gin indexes to more efficiently handle notrestrictions. And then they cover a few other additions as well. So if you are looking for someindex improvements coming in postgres 13, check out this post from Pacona.com.

[00:05:39] The next post is PostgreSQL 13, beta three B tree index deduplication. This is fromRustproof Labs and this continues on the index improvements talking about deduplication. Andthey looked at some examples from Twelve and 13 and what they found is that of course whenyou have an index with a lot of unique values such as a serial, you're not going to get that muchreduction. But other types of index, depending upon your data, you can get significantly highreductions like this one's, greater than 70%, and they show some of the improvements in indexsizes between twelve and 13 as well here. So if you want to learn more about this upcomingcapability in Postgres 13, check out this post.

[00:06:22] The next piece of content is PostgreSQL node metrics. Don't fly blind. This is fromCrunchydata.com and they're talking about a new extension they created called Pgnodemx. I'massuming that means postgres node metrics extension because this is an extension and what itdoes is allows you to query system metrics from your operating system through SQL. It showssome of the different functionality related to it. And they've made this a part of the Crunchy datamonitoring system that they have set up with a PG monitor. And it works of course, with theircontainer suite and the postgres operator. So if you want to learn more about this extension andits capabilities for retrieving system information, you can check out this post fromCrunchydata.com.

[00:07:12] The next post, also from Crunchydata.com is joins our subquery in PostgreSQLLessons Learned. So they were trying to do an exclusion query. So example, find all the recordsthat didn't exist in another table or say a parent table. His first attempt not using a subquery or ajoin basically doesn't work. But then the proper join query listing here where you do join onetable to another and then you check the ID of the left outer join to table is null, like check the IDis null. That's the way that I tend to pull out these types of records. Now we also did it as a subquery using where the ID is not n all the IDs from the other table, the performance wasn't quiteas great, but still pretty fast. And then the next thing they tried is actually using an accept clauseand they even set it up with the better syntax using a CTE and it was actually able to be fasterthan the left outer join, not looking for the ID. So down to less than six milliseconds. So, someinteresting insights on different ways that you can pull rows that don't exist in a secondary table.So if you're interested in that, check out this post from Crunchydata.com.

[00:08:24] The next piece of content is generating a normal distribution in SQL. This is fromCyberTech Postgresql.com and he's basically generating a normal distribution of data foranalysis purposes. And he's actually using the table funk extension and he shows you how torun a command using the normal rand function to be able to generate your distribution. So ifyou're interested in generating a distribution of data, definitely check out this post.

[00:08:53] The next piece of content is which partition contains a specific row in my PostgreSQLdatabase. So this is a very quick post, but it does exactly what it says and it uses table OIDs orTable Object IDs. And with this simple query you can determine where particular record is in apartition. Now you may be able to do this easily, as he says, with Lister range partitionin --g, but with hash partitioning this is probably the easiest way to find certain data and find outwhich partition is being targeted for it. So if you're interested in that, you can check out this postfrom secondquader.com.

[00:09:29] The next piece of content is FDWs curl and limit. So this was an interesting postwhere he's using a file foreign data wrapper to basically pull in a set of data from the Internet,looks like a CSV file and format it in terms of a table and then query against it. Now we actuallywanted to limit the number of rows returned, but that actually caused an error. But he found away around it using some shell manipulations to be able to avoid the error and still pull it and beable to do a limit on the query. Now he says this is a very inefficient way to set up a foreign datawrapper with a file on the internet, because you're constantly going to be downloading the datawith every query. So his solution for that is creating a materialized view. So basically wheneveryou refresh the materialized view, it redownloads the data and repopulates this materialized viewwith all the data. That way you can add the index to have fresh data and not refresh it with everyquery. So it's an interesting use case, but if you're interested in that, you can check it out on theblog@hagandar.net.

[00:10:44] The next piece of content is PG. Friday Ten Things Postgres Could Improve PartThree now, this is following up some of their posts, talking about things that Postgres couldimprove on, and they're talking about some of the disadvantages with the way that postgresdoes MVCC, and that is that records that are updated or deleted aren't updated in place ordeleted right now. They're just marked for future removal and a new record is added. So if you'regoing to update something, new record is added, the old one is marked as not there anymore.Now that's great because depending upon the visibility of that row, you can maintain that otherdatabase systems manage rollback segments or something similar where they do update inplace, but then they record the fact that that older version still exists. So they talk about some ofthe benefit and the disadvantages of it mainly is that you have to go in and vacuum up all thoserows and they go into a little bit of the freezing as well. With the XID, they also talk about theindex as the heap. So what they say is that postgres keeps the indexes and the heaps separateand the previous post that was talking about things that Postgres can improve upon is that itwould be advantageous to have the heap or the actual data ordered in some way. But right nowPostgres doesn't really have any order. You can cluster it, but it doesn't maintain that order forlong and you'd have to constantly recluster it again, which is a problem because this locks theentire table. So it's definitely not ideal to use unless you have some sort of data warehousingscenario where the system can be offline while tables are reclustered. So he talks aboutpotential benefits of doing that and whether it's advantageous just to leave Postgres as it is,having indexes and heaps completely separate. And the next thing they discuss is inlinecompression and that Postgres doesn't do that currently. But if you check out the previousepisode of Scaling Postgres, we talked about a patch that actually enables you to definecompression for certain tables and lets you choose the compression level and get benefits interms of you want greater write efficiency or greater read efficiency. So it looks like this particularpain point that was mentioned might be looking to be resolved in a future version of Postgres, sothat would be super. So if you're interested in learning more about some of the discussions onWay, Postgres could improve, you can definitely check out this post from second quadrant.com.

[00:13:11] The next piece of content is actually a presentation that was done by Bruce Momgumand it's postgres in the cloud the hard way. He basically set up a postgres instance only usingthe command line and setting it up on AWS. So you can see the outline of the steps he tookhere. He set up the AWS client interface, chose an AMI, set up the instance, set up logging andconfiguring. And in terms of setting up the instance, it looks like he also created security groupsand roles and things of that nature, installed Postgres and then was able to connect to it. So ifyou want to see a command line only way to set up post --gres on an AWS infrastructure, be sure to check out this presentation.

[00:13:57] The next piece of content is actually a YouTube video and it's best practices andsecurity with PostgreSQL. This is from the Enterprise DB YouTube channel and they're talkingabout how to set up postgres covering all sorts of different best practices with security in termsof authentication and access. So if you're interested in that, you can check out this webinar.

[00:14:19] The next piece of content is waiting for PostgreSQL 14. Add support for partitiontables and indexes in reindex. Now, what's interesting about this is that it also supportsconcurrent reindexing, which is fabulous. So for example, you can see here where you're able toindex the parent table concurrently and it automatically goes in and reindexes all of the childtables. So definitely a great improvement coming to 14. And you can check out this post fromDep.

[00:14:50] If you want to learn more, next piece of content is tuning PostgreSQL on ZFS. This isfrom PG Uptrace Dev discussing the advantages of how to really shrink the size on disk withthis. And they say a quote here you can achieve a three to five compression ratio using LZ four,and it compresses a 1 data down to around 300gb. So if you're thinking about using analternative storage solution for Postgres, maybe you want to check out this blog post.

[00:15:22] And the last piece of content is the PostgreSQL Person of the Week is Regina Obey.So if you're interested in learning more about Regina and her contributions to PostgreSQL,definitely check out this blog post.

[00:15:34] 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 Scalingposgres.com, where you can signup to receive weekly notifications of each episode, or you can subscribe via YouTube or itunes.Thanks. --

episode_image