[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 episode 131.
[00:00:18] Morning.
[00:00:23] Alright, I hope you, your friends, family and coworkers continue to do well. Our first piece of content is why PostgreSQL wall archival is slow. This is from Procona.com and they're talking about a problem where your wall backs up and fills up the PG wall directory because it cannot archive those files fast enough. Now, they mention here that they've seen problems similar to this, but normally the culprits have been one failing wall archival. So there's some sort of error in the archive command that's configured that it can't write the files to the destination, for example. Or two, there's an orphan replication slot that is preventing the old wall files from being archived. But they've noticed a new problem that's happening more and more where you have a lot of wall generation going on and it's basically causing a problem where it can't archive fast enough. Now, they say this is generally due to two issues. One is the rapid generation of wall segments. So as databases become larger and larger and there's more activity going on, you have a lot more wall files being generated. And then the second issue, the archive command is actually configured to write to a remote storage location that has additional latency involved. And basically those two issues can cause real problems with the archival process being able to keep up. And I myself have witnessed this as well, but I've even seen it when there's so much wall being generated, even with a local file copy that it couldn't keep up. So it's kind of shifted up this to be a bottleneck of the whole system. So this is a very timely post for me in terms of seeing what they discuss here. Now, the next thing they cover is the nature of wall archival being synchronous. So there are some weights that happen as a part of it. And they have this diagram here where you have a wall segment that is finished. It writes a ready file in the archival status directory and then signals the archiver that hey, there's a file that you need to pick up. So this archival process wakes up and goes to the list and it goes through every single ready file that's there looking for the oldest one to be archived and it calls one right after the other. Now, this causes a host of problems. So the first problem they mention here is quote, a method of finding out the oldest wall segments one by one and archiving them one by one is not very efficient. So the more ready files that you have in the archive status directory, the longer that whole process will take. So basically the more wall files you need to be archived, the slower it gets. So it's basically a downward spiral once you start lagging in this way. The second issue is that the archive command executes these commands and then waits for them to return before going on to archive the next file. So basically it's a synchronous process and if there's any delays like you're trying to copy it to a remote location or even having difficulty writing efficiently to a local drive, 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 whole process to finish. And then the third issue is that the archiver will wait for a second or more before reattempting if there's an issue. So this can further cause delays to be a part of it and they discuss some more issues with the implementation. But really what it's looking to me like this is an area ripe for optimization of postgres itself on finding ways to just make this whole archival process more efficient. But in terms of an immediate solution, what they mention here is since the archive command just passes it off to another tool or utility or program, make that an Asynchronous program that just accepts the input and then starts doing its process and then returns immediately. So here they mentioned that PG backrest has just such that capability, so it uses multiple background workers and essentially once it gets called, it returns immediately. So the archive command can then do its next piece of work, but then it uses those processes to actually write the files, copy them to different locations, et cetera. So I found this to be a really great 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 can handle more transaction throughput. So if you're interested in that, check out this post from Procona.com.
[00:04:54] The next piece of content is another post from Procona.com. It's index improvements in PostgreSQL 13. And this is just basically a list of all the index improvements they've discovered in postgres 13, I believe beta three. So the first thing they mentioned, the big one is the deduplication of the Btree indexes and they show how some of the improvements can get you space savings looks like up to a third in some cases. They discuss gist in SP gist being able to handle box point distance lookups, allowing Gin indexes to more efficiently handle not restrictions. And then they cover a few other additions as well. So if you are looking for some index 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 from Rustproof Labs and this continues on the index improvements talking about deduplication. And they looked at some examples from Twelve and 13 and what they found is that of course when you have an index with a lot of unique values such as a serial, you're not going to get that much reduction. But other types of index, depending upon your data, you can get significantly high reductions like this one's, greater than 70%, and they show some of the improvements in index sizes between twelve and 13 as well here. So if you want to learn more about this upcoming capability 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 from Crunchydata.com and they're talking about a new extension they created called Pgnodemx. I'm assuming that means postgres node metrics extension because this is an extension and what it does is allows you to query system metrics from your operating system through SQL. It shows some of the different functionality related to it. And they've made this a part of the Crunchy data monitoring system that they have set up with a PG monitor. And it works of course, with their container suite and the postgres operator. So if you want to learn more about this extension and its capabilities for retrieving system information, you can check out this post from Crunchydata.com.
[00:07:12] The next post, also from Crunchydata.com is joins our subquery in PostgreSQL Lessons Learned. So they were trying to do an exclusion query. So example, find all the records that didn't exist in another table or say a parent table. His first attempt not using a subquery or a join basically doesn't work. But then the proper join query listing here where you do join one table to another and then you check the ID of the left outer join to table is null, like check the ID is null. That's the way that I tend to pull out these types of records. Now we also did it as a sub query using where the ID is not n all the IDs from the other table, the performance wasn't quite as great, but still pretty fast. And then the next thing they tried is actually using an accept clause and they even set it up with the better syntax using a CTE and it was actually able to be faster than the left outer join, not looking for the ID. So down to less than six milliseconds. So, some interesting 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 from CyberTech Postgresql.com and he's basically generating a normal distribution of data for analysis purposes. And he's actually using the table funk extension and he shows you how to run a command using the normal rand function to be able to generate your distribution. So if you'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 PostgreSQL database. So this is a very quick post, but it does exactly what it says and it uses table OIDs or Table Object IDs. And with this simple query you can determine where particular record is in a partition. Now you may be able to do this easily, as he says, with Lister range partitioning, but with hash partitioning this is probably the easiest way to find certain data and find out which partition is being targeted for it. So if you're interested in that, you can check out this post from secondquader.com.
[00:09:29] The next piece of content is FDWs curl and limit. So this was an interesting post where 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 actually wanted to limit the number of rows returned, but that actually caused an error. But he found a way around it using some shell manipulations to be able to avoid the error and still pull it and be able to do a limit on the query. Now he says this is a very inefficient way to set up a foreign data wrapper with a file on the internet, because you're constantly going to be downloading the data with every query. So his solution for that is creating a materialized view. So basically whenever you refresh the materialized view, it redownloads the data and repopulates this materialized view with all the data. That way you can add the index to have fresh data and not refresh it with every query. So it's an interesting use case, but if you're interested in that, you can check it out on the
[email protected].
[00:10:44] The next piece of content is PG. Friday Ten Things Postgres Could Improve Part Three now, this is following up some of their posts, talking about things that Postgres could improve on, and they're talking about some of the disadvantages with the way that postgres does MVCC, and that is that records that are updated or deleted aren't updated in place or deleted right now. They're just marked for future removal and a new record is added. So if you're going 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 other database systems manage rollback segments or something similar where they do update in place, but then they record the fact that that older version still exists. So they talk about some of the benefit and the disadvantages of it mainly is that you have to go in and vacuum up all those rows and they go into a little bit of the freezing as well. With the XID, they also talk about the index as the heap. So what they say is that postgres keeps the indexes and the heaps separate and the previous post that was talking about things that Postgres can improve upon is that it would be advantageous to have the heap or the actual data ordered in some way. But right now Postgres doesn't really have any order. You can cluster it, but it doesn't maintain that order for long and you'd have to constantly recluster it again, which is a problem because this locks the entire table. So it's definitely not ideal to use unless you have some sort of data warehousing scenario where the system can be offline while tables are reclustered. So he talks about potential 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 inline compression and that Postgres doesn't do that currently. But if you check out the previous episode of Scaling Postgres, we talked about a patch that actually enables you to define compression for certain tables and lets you choose the compression level and get benefits in terms of you want greater write efficiency or greater read efficiency. So it looks like this particular pain point that was mentioned might be looking to be resolved in a future version of Postgres, so that would be super. So if you're interested in learning more about some of the discussions on Way, 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 Momgum and it's postgres in the cloud the hard way. He basically set up a postgres instance only using the command line and setting it up on AWS. So you can see the outline of the steps he took here. He set up the AWS client interface, chose an AMI, set up the instance, set up logging and configuring. And in terms of setting up the instance, it looks like he also created security groups and roles and things of that nature, installed Postgres and then was able to connect to it. So if you want to see a command line only way to set up postgres 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 and security with PostgreSQL. This is from the Enterprise DB YouTube channel and they're talking about how to set up postgres covering all sorts of different best practices with security in terms of 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 partition tables and indexes in reindex. Now, what's interesting about this is that it also supports concurrent reindexing, which is fabulous. So for example, you can see here where you're able to index the parent table concurrently and it automatically goes in and reindexes all of the child tables. So definitely a great improvement coming to 14. And you can check out this post from Dep.
[00:14:50] If you want to learn more, next piece of content is tuning PostgreSQL on ZFS. This is from PG Uptrace Dev discussing the advantages of how to really shrink the size on disk with this. 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 an alternative 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 content mentioned in the show notes be sure to head over to Scalingposgres.com, where you can sign up to receive weekly notifications of each episode, or you can subscribe via YouTube or itunes. Thanks.
