background

Postgres 13 RC1, Upgrading at Scale, With Ties, HOT Updates | Scaling Postgres 132

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

In this episode of Scaling Postgres, we discuss the release of Postgres 13 RC1, how to upgrade Postgres at scale, using limit with ties and understanding Heap Only Tuple (HOT) updates.

Content Discussed

YouTube Video

Podcast Audio

Transcript

All right. I hope you, your family, friends, and coworkers continue to do well. Our first piece of content is "PostgreSQL 13 RC 1 Released!". This is from postgresql.org. Release Candidate 1 is released. It looks like they're scheduling September 24, 2020, so later this week. As for the final release, assuming there are no issues with the first release candidate, and there's a convenient link down here for the release notes for Release Candidate 1. What I always find interesting to look at is the migration to a particular version and what things to watch out for. So when you do your upgrade, be sure to check out this area to make sure you don't have any gotchas when you do it. But we're fast approaching the release of version 13.

The next piece of content is "How we upgraded PostgreSQL at GitLab.com". This is from gitlab.com and they're describing their upgrade from Postgres 9.6 to 11 and the process they used. Now this is super detailed and has a lot of information, a lot of links to additional information about the process they went through to do it, as well as their environment. So I highly suggest checking out this post this week. Now, first, they cover why they decided to do the upgrade. Well, the big one is that version 9.6 has end of life in November 2021. But also the additional enhancements and improvements that they've done up to 11. Of course, they continue to do that with 12 and soon 13. But they targeted 11. Then they talk about their environment and their architecture. And they're using 12-96 core instances to run Postgres, each with 614 GB of RAM. 

So their environment is set up like this. They're using Rails with Sidekiq for job processing. Those speak to a PgBouncer internal load balancer. Now, I'm not quite sure what this is. I don't know if it's custom software they developed or if it's just an HAProxy or something, but that, as they say here, distributes connections to the healthy PgBouncers. Then the PgBouncers talk to the primary PostgreSQL instance. That's maintained through Patroni and this consensus console server cluster that they have set up here. And then even these PgBouncers go through a local PgBouncer to actually talk to the primary PostgreSQL server for read and write queries. They say the read-onlys don't look like they go through a PgBouncer set the way they do here. But they do have a PgBouncer on the local PostgreSQL machines that these read-only queries talk to, at least according to this diagram. 

Then they're just using standard streaming replication to keep their replicas in sync and then they send their base backups and their wall archiving to Google Cloud Storage and then they have a, I think they said, an eight-hour delayed instance that's maintained by just reading the WAL files from Google Cloud Storage. Then they have this other disaster recovery archive as well. So that's their environment, their infrastructure that they wanted to upgrade. Then they show how busy things are during a given week and then when they target the upgrade is of course when they're not going to be that busy. Now they had certain upgrade requirements that they wanted to cover. Number one is that they didn't want any regressions on PostgreSQL 11. 

So they did a lot of testing and they said they developed a custom benchmark to perform extensive regression testing to make sure that they didn't have any performance degradations. After they upgraded to Postgres eleven, they wanted to upgrade all of the database servers during the maintenance window. They were going to be using a pg_upgrade, they were going to be keeping a 9.6 cluster available as a fallback and this is something that I do when I upgrade databases as well. They were going to be using the link capability of pg_upgrade, which means you can't really go back on that instance. So having some extra servers around as the old version serves as a way to roll back. They chose to do the upgrade using a fully automated way to do it. So they're using configuration management software to do it, specifically ansible-playbooks, although they also are using Chef for certain purposes. They mentioned Terraform, and they wanted to keep the database upgrades to only 30 minutes. 

They actually recorded the whole procedure and published it, which is available as a video down below. Now they go through the different phases of the project in terms of developing automation in an isolated environment, integrating it into staging, doing multiple tests and staging, and then finally the production upgrade. They talked a little bit about using pg_upgrade and some of the things that they took into account and then also their procedure for doing their regression testing and then a little bit into the automation to ensure that everything was as hands-off as possible. This means you just ran different steps of an ansible-playbook to do the process. There wasn't copying and pasting of code manually. So as you can tell, this is super detailed of everything that they went through as well as the actual final video on YouTube of the upgrade process that they went through. So I highly encourage you to check out this post.

The next post is "PostgreSQL 13: LIMIT ...WITH TIES". This is from 2ndquadrant.com and there's actually a new clause that is supported when essentially using LIMIT. Although it looks like you can't use LIMIT with it, you actually have to use the SQL standard which is "FETCH FIRST n ROWS", which is the same thing as limit. So FETCH FIRST 10 ROWS is the same thing as LIMIT 10. But what this does is it includes any extra rows that are the same as the last row that is being pulled if what you're ordering on the value is the same. So for example, here they're pulling SELECT ALL employees, ORDER BY the salary, and LIMIT to two records. 

So you're only going to get two records. But the question is what would be the third person's salary? Well, that's where WITH TIES comes in. So here it's the same query SELECT ALL from employees and ORDER BY the salary. And then again you have to use the FETCH FIRST so many rows. So it's using FETCH FIRST 2 ROWS WITH TIES. Here you can see that, indeed, the third person, ordering by salary, does have the same salary as the last person. So it's going to give you three rows because you want to include any ties. So this is a pretty interesting addition that helps improve the SQL standard compatibility of Postgres. So if you want to learn more, definitely check out this post.

The next piece of content is " HOT UPDATES IN POSTGRESQL FOR BETTER PERFORMANCE". This is from cybertec-postgresql.com and they're talking about HOT or heap-only tuple updates. So they say how updates happen in Postgres. Basically a new row is inserted while the old row is maintained. Then later that old row is vacuumed up when it's no longer visible. So for example, they show a little example here where you have an index here by ID and it's pointing to each of the records. Then if you update row two, it looks for the first empty spot and that happens to be in the next block, not the current block of the heap because the current block is full. So it's going to put it into the next usable area. So it's going to place that here. Now, doing updates this way, they say, has a lot of advantages. There's no need for an extra storage area where old-row versions are kept. 

These are like rollback segments, say in Oracle. Rollback does not have to undo anything and is very fast. There's not an overflow problem with transactions that modify a ton of rows. But this has some disadvantages. Basically, now you have to vacuum up all of those old rows. You have tables that can become bloated with all of these dead tuples. Every update requires a new index entry to be added even if no indexed attribute is modified. Modifying an index is more expensive than just modifying the heap because you have to maintain order. And this is kind of where heap-only tuple updates come in. So they did the example of the image that was mentioned above. So they have a table with an ID and then a value column. They generate a series of data in it and then they show where the data is in the different blocks using the ctid here. You can see when they do an update of the ID 42, because block zero is full, it places it in block one, so it goes into (1, 10) here. 

So that was not a heap-only tuple update. But here they show an example of how it works. And the reason that this works is because, for each block, an array of line pointers is maintained to point to the actual data within that block or that page. So it separates the external reference from the internal implementation. And they say here "A Heap Only Tuple is a tuple that is not referenced from outside the table block". Instead, a forwarding address is stored in the old row version. So for example, three here, it's pointing here. But if you do an update of three and there's free space in this block, it can place that new tuple here. So it's maintaining the old version here and you have a HOT link, or heap-only tuple link, so that this index entry can find it. You don't have to create an entirely new index entry for this tuple that's been created here when doing an update. 

Now, an important thing to remember, because it's using this internal representation, is that this only works if the new and old versions of the row are in the same block. So basically, you need space in this block for this to work. If it gets put into the next block, it can't do a heap-only tuple update. Now, the two advantages of HOT updates are that Postgres doesn't have to modify indexes because the external address of the tuple remains the same. You can see right here, it's exactly the same. It's just another reference within the block that it gets pointed to to actually get the new row version. Dead tuples can be removed without the need for vacuum. So any backend process can actually adjust this pointer or move the pointer around here. Now, they mentioned there are two conditions for using HOT updates. 

Number one, there must be enough space on the block containing the row. What we mentioned before, you have to have free space in this block. And number two, there's no index defined on any column whose value it modified. So for this latter point, that's pretty easy to tell. However, to determine if there's enough space, how you can do that is exactly what they say here by adjusting the fill factor on the tables. So normally tables are filled at 100%, so the fill factor is 100%. But say if you have a table that gets a lot of updates, maybe you want to drop that down to 90%, 80%, maybe as far as 70% potentially. And then they say you can use the pg_stat_user_tables system view to get a sense of how many tuple updates are happening. Then out of those, how many are HOT updates to give you a sense if you're doing pretty well on using HOT updates or not? 

Then they give another implementation example where they have a fill factor of 70 that they set here and they use the same procedure to do an update of the table update ID 42, and now you can see it is kept in the same block, therefore it was a HOT update versus the previous implementation. Now they did cover the fill factor in a previous article that they linked to here and we discussed in a previous episode of Scaling Postgres and what they found was that an 80-90 fill factor was probably the best balance for performance. But if you have a lot of heap-only tuple updates, you may want to adjust this down a little bit further potentially to get more heap-only tuple updates. So it's kind of based upon your use case and what you're seeing. You can use the system table here to give you a sense of what's your balance of heap-only tuple updates to non-heap-only tuple updates. But another great post I encourage you to check out.

The next piece of content is "7 Best Practice Tips for PostgreSQL Bulk Data Loading". This is from 2ndquarter.com. All of these tips are essentially don't do things that could potentially happen during an INSERT, so basically just do less work. So tip number one is to change your target table to an unlogged mode. So basically, nothing gets saved in the WAL files, so that allows you to get great speed with inserting it. However, that can cause a problem if ultimately you do want to log these tables, particularly when a table is being replicated or you have a replica set up. This can potentially cause a lot of replication traffic once you try to re-enable logging mode again. So something to be cautious of, but this is definitely an option. The second tip is to drop and then recreate the indexes. 

So drop all the indexes except for say, maybe the primary key, and then recreate all of those indexes once all of the data is loaded. The third is to drop and recreate foreign keys. So again, do less work for each INSERT. Generally, the foreign key has to be consulted before the INSERT can succeed. So if you remove those foreign key constraints, it can happen faster. Fourth, disable any triggers that you have there, as long as you can safely do that. Again, less work for the INSERT will make it faster. Fifth, us COPY. This is much faster than trying to insert data using standard SQL statements. If you must insert data using SQL statements, use a multivalued INSERT. So insert multiple rows at a time with your data and they advise keeping it to say 1,000 rows or less generally. The last tip is to run ANALYZE once all of the data has been loaded. So definitely a great set of tips.

Next piece of content is "Hidden Gems of PostgreSQL 13". This is from blog.crunchydata.com and it's highlighting some of the features that they call little gems. Number one is to guard against the rogue replication slot, and I tend to call these orphan replication slots. So they're replication slots that have hung around and now they're potentially causing WAL bloat in your primary and potentially causing it to run out of disk space. But there's now this new configuration setting max_slot_keep_wal_size that enables you to define the max amount of WAL size a slot can use before it gets deactivated, essentially. So it protects your primary database system from running out of disk space. So this is a great addition. 

I definitely agree with this. The second one is functions calculating the greatest common divisor and the least common multiple. The third is certificate authentication for the PostgreSQL Foreign Data Wrapper. Fourth is encrypted certificates to connect from your applications. So basically your certificates can now include an SSL password as a part of it to be able to do the authentication when there's a password on the key. Next is support for Unicode normalization. The next one is using UUIDs without having to install an extension for it. Then finally the PostgreSQL glossary which they mentioned here. So definitely great additions. That sounds like we'll be coming to Postgres 13 as early as this week. 

The next piece of content is "Postgres and the Artificial Intelligence Landscape". This is from Bruce Momjian at momjian.us. This was an interesting post that talks about artificial intelligence and some things that you can do within postgres. Now what's great is he has links throughout this presentation to almost every slide where it is discussed in more detail. So this was a vibrant presentation from that perspective of diving in and getting exposed to machine learning. Then he actually uses PL/Perl to generate machine learning within PostgreSQL functions. So this is a very interesting blog post if you want to get introduced to machine learning and do a little bit of it within PostgreSQL.

The next piece of content is "Exploring PL/Python: Turn Postgres Table Data Into a NumPy Array". This is from blog.crunchydata.com. So apparently, a NumPy is for scientific analysis and one of its data structures is an array. You can convert a Postgres table data into that. That's exactly what this post does so that you can do scientific analysis of data that resides within Postgres. So if you're interested in that, check out this post.

The next post is "Talking about Citus & Postgres at any scale".  This is from citusdata.com. So this is discussing a presentation that was given at a conference and the YouTube video is right here for it and talking about the scale-out solution which is Citus. So if you're interested in that, check out that blog post.

Then the last piece of content, the PostgreSQL person of the week is Anthony Nowocien. So if you're interested in learning more about Anthony and his contributions to PostgreSQL, definitely check out this blog post.

episode_image