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

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

• PostgreSQL 13 RC 1 Released!
• How we upgraded PostgreSQL at GitLab.com
• PostgreSQL 13: LIMIT … WITH TIES
• HOT updates in PostgreSQL for better performance
• 7 Best Practice Tips for PostgreSQL Bulk Data Loading
• Hidden Gems of PostgreSQL 13
• Exploring PL/Python: Turn Postgres Table Data Into a NumPy Array
• Postgres and the Artificial Intelligence Landscape
• Talking about Citus & Postgres at any scale
• Anthony Nowocien

YouTube Video

Podcast Audio

Transcript

[00:00:00] Hello. In this episode of Scaling Postgres, we talk about Postgres 13, ReleaseCandidate One, upgrading at scale with ties and hot updates. I'm creston. Jameson. And this isscaling postgres episode 132. You all right? I hope you, your family, friends and coworkerscontinue to do well. Our first piece of content is Postgres QL 13. Release. Candidate one isreleased. This is from the Postgres news area. And release. Candidate one is released. And itlooks like they're scheduling September 24 so later this week. As for the final release, assumingthere's no issues with the first release candidate, and there's a convenient link down here for therelease notes for Release Candidate One. And what I always find interesting to look at is themigration to a particular version, things to watch out for. So when you do your upgrade, be sureto check out this area to make sure you don't have any gotchas when you do it. But we're fastapproaching the release of version 13.

[00:01:12] The next piece of content is how we upgraded postgresql@gitlab.com. And of course,this is from GitLab.com and they're describing their upgrade from postgres 9.6 to eleven and theprocess they used. Now this was super detailed, has a lot of information, a lot of links toadditional 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 todo the upgrade. Well, the big one is that for version 9.6, it's end of life on November 2021. Sothat's going to be here in a little over a year, but also the additional enhancements andimprovements that they've done up to eleven. And of course they continue to do that with Twelveand soon 13. But they targeted eleven. And then they talk about their environment and theirarchitecture. And they're using 1296 core instances to run postgres, each with 614gb of Ram.So their environment is set up like this. They're using Rails with Sidekick for job processing.Those speak to a PG Bouncer internal load balancer. Now, I'm not quite sure what this is. I don'tknow if it's custom software they developed or it's just an Ha proxy or something, but that, asthey say here, distributes connections to the healthy PG bouncers. And then the PG bouncerstalk to the primary PostgreSQL instance. And that's maintained through Petrone and thisconsensus console server cluster that they have set up here. And then even these PG bouncersgo through a local PG bouncer to actually talk to the primary PostgreSQL server for read andwrite queries. They say the read onlys don't look like they go through a PG bouncer set the waythey do here. But they do have a PG bouncer on the local PostgreSQL machines that these readonly queries talk to, at least according to this diagram. And they're just using standard streamingreplication to keep their replicas in sync and then they send their base backups and their wallarchiving to Google Cloud Storage and then they have a, I think they said an eight hour delayedinstance that's maintained by just reading the wall files from Google Cloud Storage. And thenthey have this other disaster recovery archive as well. So that's their environment, theirinfrastructure that they wanted to upgrade and then they show how busy things are during agiven week and then when they targeted the upgrade is of course when they're not going to bethat busy. Now they had certain upgrade requirements that they wanted to cover. Number onebeing they didn't want any regressions on PostgreSQL eleven. So they did a lot of testing andthey said they developed a custom benchmark to perform extensive regression testing to makesure that they didn't have any performance degradations. After they upgraded to postgreseleven, 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 clusteravailable as a fallback and this is something that I do when I upgrade databases as well. Theywere going to be using the link capability of PG upgrade, which means you can't really go backon that instance. So having some extra servers around as the old version serves as a way to rollback. They chose to do the upgrade using a fully automated way to do it. So they're usingconfiguration management software to do it, specifically ansible Playbooks, although they alsoare using Chef for certain purposes. They menti --oned as well as TerraForm, and they wanted to keep the database upgrades to only 30minutes. And they actually recorded the whole procedure and published it, which is available asa video down below. Now they go through the different phases of the project in terms ofdeveloping automation in isolated environment, integrating it into staging, doing multiple testsand staging, and then finally the production upgrade. They talk a little bit about using PGupgrade and some of the things that they took into account and then also their procedure fordoing their regression testing and then a little bit into the automation to ensure that everythingwas as hands off as possible. Meaning you just ran different steps of an ansible playbook to dothe process. There wasn't copying and pasting of code manually. So as you can tell, this is superdetailed of everything that they went through as well as the actual final video on YouTube of theupgrade process that they went through. So I highly encourage you to check out this post fromGitLab.com.

[00:05:35] The next post is PostgreSQL 13 Limit with Ties. This is from secondquader.com andthere's actually a new clause that is supported when essentially using Limit. Although it lookslike you can't use Limit with it, you actually have to use the SQL standard which is fetch first inrows, which is the same thing as limit. So fetch first ten rows is the same thing as limit ten. Butwhat this does is it includes any extra rows that are the same as the last row that is being pulledif what you're ordering on the value is the same. So for example, here they're pulling select allemployees, order it by the salary and limit it to two records. So you're only going to get tworecords. But the question is what would be the third person's salary? Well, that's where with tiescomes in. So here it's the same query select all from employees order by the salary. And thenagain you have to use the fetch first so many rows. So it's using fetch first two rows with ties.And here you can see that indeed the third person ordering by salary does have the same salaryas the last person. So it's going to give you three rows because you want to include any ties. Sothis 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.

[00:06:56] The next piece of content is Hot updates in PostgreSQL for better performance. Thisis from CyberTech Postgresql.com and they're talking about hot or heap only Tuple updates. Sothey say how updates happen in postgres is basically a new row is inserted while the old row ismaintained. And then later that old row is vacuumed up when it's no longer visible. So forexample, they show a little example here where you have an index here by ID and it's pointing toeach of the records. And then if you update row two, it looks for the first empty spot and thathappens to be in the next block, not the current block of the heap because the current block isfull. So it's going to put it into the next usable area. So it's going to place that here. Now, doingupdates this way they say, have a lot of advantages. There's no need for an extra storage areawhere old row versions are kept. These are like rollback segments. Say in Oracle, rollback doesnot have to undo anything and is very fast. And there's not an overflow problem withtransactions that modify a ton of rows. But this has some disadvantages. Basically now youhave to vacuum up all of those old rows. You have tables that can become bloated with all ofthese dead Tuples. And every update requires a new index entry to be added even if no indexedattribute is modified. And modifying an index is more expensive than just modifying the heapbecause you have to maintain order. And this is kind of where heaponly Tuple updates come in.So they did the example of the image that was mentioned above. So they have a table with anID and then a value column. They generate a series of data in it and then they show where thedata is in the different blocks using the Ctid here. And you can see when they do an update of ID42, because block zero is full, it places it in block one, so it goes into 110 here. So that was not aheap only Tuple updates. But here they show an example of how it works. And the reason thatthis works is because for each block, an array of line pointers is maintained to point to the actualdata within that block or that pag --e. So it separates the external reference from the internal implementation. And they say here aheap only tuple is a tuple that is not referenced from outside the table block. And instead aforwarding 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 tuplehere. So it's maintaining the old version here and you have a hot link or heap only tuple link sothat this index entry can find it. And you don't have to create an entirely new index entry for thistuple that's been created here when doing an update. Now, important thing to remember is,because it's using this internal representation, is that this only works if the new and old versionof the row are in the same block. So basically, you need space in this block for this to work. If itgets put into the next block, it can't do a heap only tuple update. Now, the two advantages of hotupdates are that postgres doesn't have to modify indexes because the external address of thetuple remains the same. You can see right here, it's exactly the same. It's just another referencewithin the block that it gets pointed to to actually get the new row version. And dead tuples canbe removed without the need for vacuum. So any backend process can actually adjust thispointer or move the pointer around here. Now, they mentioned there are two conditions for usingHot updates. Number one, there must be enough space on the block containing the row. Whatwe mentioned before, you have to have free space in this block. And number two, there's noindex defined on any column whose value it modified. So for this latter point, that's pretty easy totell. However, determining if there's enough space, how you can do that is exactly what they sayhere by adjusting the fill factor on the tables. So normally tables are filled at 100%, so the fillfactor is 100%. But say if you have a table that gets a lot of updates, maybe you want to dropthat down to 90, 80, maybe as far as 70% potentially. And then they say you can use thePGSTAT user tables system view to get a sense of how many Tuple updates are happening. Andthen out of those, how many are hot updates to give you a sense if you're doing pretty well onusing hot updates or not. And then they give another implementation example where they havea fill factor of 70 that they set here and they use the same procedure to do an update of the tableupdate ID 42, and now you can see it is kept in the same block, therefore it was a hot updateversus the previous implementation. Now they did cover fill factor in a previous article that theylinked to here and we discussed in a previous episode of Scaling Postgres and what they foundwas an 80 to 90 fill factor was probably the best balance for performance. But if you have a lot ofheap only Tuple updates, you may want to adjust this down a little bit further potentially to getmore heap only Tuple updates. So it's kind of based upon your use case and what you're seeing.And you can use the system table here to give you a sense of what's your balance of heap onlyTuple updates to non heap only Tuple updates. But another great post I encourage you to checkout from Cyprtechyphen Postgresql.com.

[00:12:14] The next piece of content is Seven best practice tips for PostgreSQL bulk dataloading. And this is from secondquarter.com. All of these tips are essentially don't do things thatcould potentially happen during an insert, so basically just do less work. So tip number one ischange your target table to an unlogged mode, so basically nothing gets saved in the wall files,so that allows you to get great speed with inserting it. However, that can cause a problem ifultimately you do want to log these tables, and particularly when a table is being replicated oryou have a replica set up. This can potentially cause a lot of replication traffic once you try to reenable logging mode again. So something to be cautious of, but this is definitely an option.Second tip is drop and then recreate the indexes. So drop all the indexes except for say, maybethe primary key, and then recreate all of those indexes once all of the data is loaded. Third isdrop and recreate foreign keys. So again, do less work for each insert. Generally the foreign keyhas to be consulted before the insert can succeed. So if you remove those foreign keyconstraints, it can happen faster. A four disable any --triggers that you have there, as long as you can safely do that. Again, less work for the insertwill make it faster. A five use copy. This is much faster than trying to insert data using standardSQL statements. If you must insert data using SQL statements, use a multivalued insert. Soinsert multiple rows at a time with your data and they advise keeping it to say 1000 rows or lessgenerally. And the last tip is to run analyze once all of the data has been loaded. So definitely agreat set of tips from secondquarter.com.

[00:13:59] Next piece of content is Hidden gems of PostgreSQL 13. This is fromCrunchydata.com and it's highlighting some of the features that they call little gems. Number oneis guard against the rogue replication slot, and I tend to call these orphan replication slots. Sothey're replication slots that have hung around and now they're potentially causing wall bloat inyour primary and potentially causing it to run out of disk space. But there's now this newconfiguration setting max slot keep wall size that enables you to define the max amount of wallsize a slot can use before it gets deactivated, essentially. So it protects your primary databasesystem from running out of disk space. So this is a great addition. I definitely agree with this. Thesecond 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 isencrypted certificates to connect from your applications. So basically your certificates you cannow include an SSL password as a part of it to be able to do the authentication when there's apassword on the key support for Unicode Normalization. The next one is using UUIDs withouthaving to install an extension for it. And then finally the PostgreSQL glossary which theymentioned here. So definitely great additions. That sounds like we'll be coming to Postgres 13 asearly as this week.

[00:15:28] The next piece of content is Postgres and the Artificial intelligence landscape. This isfrom Bruce Mongium at Mongium US. And this was an interesting post that talks about artificialintelligence and some things that you can do within postgres. Now what's great is he has linksthroughout this presentation to almost each slide where it discusses in more detail. So this was avery rich presentation from that perspective of diving in and getting exposed to machinelearning. And then he actually uses PL Perl to generate machine learning within PostgreSQLfunctions. So this is a very interesting blog post if you want to get introduced to machine learningand doing a little bit of it within PostgreSQL.

[00:16:13] The next piece of content is exploring PL Python turn Postgres Table data into aNumPy array. So apparently a NumPy is for scientific analysis and one of its data structures isan array. And you can convert a Postgres Table data into that. And that's exactly what this postdoes so that you can do scientific analysis of data that resides within postgres. So if you'reinterested in that, check out this post from Crunchydata.com.

[00:16:42] The next post is talking about Citus and postgres at any scale. So this is discussing apresentation that was given at a conference and the YouTube video is right here for it and talkingabout the scale out solution which is Citus. So if you're interested in that, check out that blogpost. This is from citrusdata.com. And then the last piece of content is the PostgreSQL person ofthe Week is Anthony novochain. So if you're interested in learning more about Anthony and hiscontributions to PostgreSQL, definitely check out this blog post that does it. For this episode ofScaling Postgres, you can get links to all the content mentioned in the show. Notes be sure tohead over to Scalingposgres.com, where you can sign up to receive weekly notifications of eachepisode, or you can subscribe via YouTube. Ride tunes. Thanks. --