Episode Transcript
[00:00:00] In this episode of Scaling Postgres we talk about picturing vacuum recovery changes, certificate authentication and function replication. I'm Kristen Jameson, and this is scaling postgres episode 74.
[00:00:21] Alright, I hope you're having a great week. Our first piece of content is a Postgres SQL award and this was listed on the second quadrant, Postgresql.com blog. But basically, PostgreSQL was presented the 2019 O'Reilly Open Source Award for lifetime achievement. And it looks like Mark WAM, Bruce momgean and Hoffpettis accepted the reward on PostgreSQL. So definitely quite a recognition.
[00:00:50] The next post is actually a webinar that you can sign up for called Webinar Postgres Vacuuming through Pictures follow up and you can just click the link here. To get access to the webinar you need to submit some contact information, but it gives a good overview of Vacuum talking about its purpose, given that Postgres uses MVCC and therefore must be able to see multiple versions of rows. Once some of these old versions are done, vacuum is used to clean out those old rows. That's why they call it a vacuum. He talks also about the importance of configuring auto vacuum for being able to handle bloat and some numerous settings that you can make to configure auto vacuum or Vacuum for your environment. Now there weren't quite as many pictures as I thought there might be, but it does give a pretty good overview of Vacuum and why and how it works. So definitely a piece of content I encourage you to check out.
[00:01:45] The next post is Postgres Twelve highlight recovery changes. And this is from Pakier XYZ and he's talking about the changes coming to how you handle recovery or standby databases with Postgres twelve. And basically there will no longer be a recovery.com file, but all those configuration changes are now taking place within the Postgresql.com file. And some of the benefits, we actually mentioned some of these, but what he mentions here is the ability to reload some of these parameters, to be able to change them, to monitor the values which show and to apply changes with Alter system. But when you move to twelve you definitely need to be ready for these changes because again, if the recovery.com file is present, PostgreSQL will not start. So you definitely need to review these additional changes and make changes to your environment when you're planning the move to Postgres twelve. So definitely another piece of content to make sure you're prepared.
[00:02:41] The next post is certificate authentication recipe for PostgreSQL docker containers. And this is from Crunchydata.com. Now actually this isn't only for docker containers. Now they're describing how to do it within a docker container, but a lot of the information is beneficial even if you're not using docker. Basically, how do you set up certificate authentication for PostgreSQL? Now they have a lot of code here that you can directly use, but here's pretty much the process. So the step one is first you create a root certificate authority. So you need some sort of common certificate authority to sign the certificates that are being used by the server side and the client side. So usually you'll want to create your own certificate authority to do this, but you can choose to purchase the certificates if you want. Instead, the next step is to generate the PostgreSQL server key and its certificate, then configure PostgreSQL to enable TLS and certificate based authentication. Again, this is within the configuration area as well as changing the Pghba.com file. And they also mentioned some few other docker commands here, but you can apply those to any system environment. Step four is generating the client key and the certificate. So these would be used on the clients to be able to connect and then they show you how to connect. Basically they're exporting a couple of different parameters that your client uses to be able to connect up to a certificate authentication secured server. So if you're interested in using certificate authentication, even if you're not using Doctor, this is definitely a great post to check out. Now, there was also a video linked here called how to set up an enterprise certificate based authentication system. Now this link goes to a YouTube video talking about advanced replication. So they talk about using all sorts of other authentication methods, but during the last part they do describe setting up certificate authentication. So this video is another piece of content you can check out to help you set up certificate authentication.
[00:04:45] The next post is PostgreSQL. How are functions and stored procedures replicated? And this is from CyberTech postgresql.com. And the first thing they cover is well, how are functions and procedures stored? Basically in system tables. And there's a System Viewpg Proc that you can take a look at. So he created just a basic function here and you can see it actually stores in the source when using this procedure. Language 14 is actually SQL. It just stores the code right in the source field. But if you have procedures stored in other languages, it actually represents it a bit differently. So if your code relies on libraries, it stores it a bit differently. So for example, he created the PG Trigram extension and then he looked that up in the PG Proc view or System table and you see the source is actually a name, but then the library is referenced here. So now in terms of replication, the System tables of course get replicated over to Replicas. So you don't need to do anything about that. But if you have a library dependency, those need to be placed on every Replica you're going to use. And he says here, quote, if you create a function on the master written in C, for example, and your slave does not have the desired library, the changes in the System tables will be present. There's just no way to call these functions on the slave or the Replica. So basically the System tables get replicated, but any libraries you need to make sure are on each instance that you're replicating to. So definitely some great information to be aware of.
[00:06:19] The next post is Yum upgrade PostgreSQL eleven, panic. So this is from Luca Ferrari at F Lucan 1978 GitHub IO. He was wanting to upgrade a database system and he just used Sudo Yum upgrade PostgreSQL. But unfortunately this database server did not start and he couldn't connect to it. And looking at it when trying to start, it said basically the data directory is missing or empty. So he was like, what? So he said systemd was trying to launch PostgreSQL with an empty PG data directory. So he says apparently quote yum upgraded my system D configuration for PostgreSQL to the CentOS default. So basically the service file was overwritten without any advice. So basically what he eventually did is used the command system CTL Edit PostgreSQL eleven and added an environment PG Data to state where the data directory is, and that helped it find it. So, I haven't run into this when doing upgrades, like I tend to use Ubuntu, but this is definitely something to be aware of. So definitely a blog post to check out if you ever run into this particular type of situation.
[00:07:32] The next post is automatic. Index recommendations in PostgreSQL using PG, qualstats and hypopg. So this is from the Procona.com Blog, and they had done a previous post that we covered on Scaling Postgres, talking about hypopg, which sets up hypothetical indexes. Basically this post walks through a process of using hypopg and PG Qual Stats in order to predict or make a recommendation for what indexes should be used based upon queries that are running against the system. So ideally you would look at some queries here and it would say, okay, we recommend creating this index and it should give this percentage improvement. They say the PG Qualstats is an extension developed by the Powa team to uncover the need for storing predicates or Quals. And this is important because PG Stats statements doesn't store the predicates, you only get the variables. So they basically walked through how to set up PG Qualstats. And so for setting up then to do these types of index predictions, they needed to install these three extensions hypopg, PG Stat statements and PG Qualstats, and add to the shared preload libraries the PG Stat statements as well as PG Qualstats. And then they created this quite complex function that they called Find Usable Indexes to be able to consult the different tables to make the recommendations. And at the end they get some output like this, which basically gives them for particular queries what the index recommendation is. So if you're wanting to use this potentially automated way of determining what some additional indexes could help your application or system, definitely a blog post to check out. The next post is jsquery versus SQL JSON. This is from Tombrown blogspot.com. Also, the name of the blog is Tom's Memory Leak and I wasn't necessarily aware of this one but their JS query is actually an extension that you can install and he says, quote here it was introduced as a non core extension in PostgreSQL 9.4 and higher now with PostgreSQL twelve coming and provides a native way to query JSON data. He compares and contrasts both of these so with the changes that are coming with SQL, JSON and postgres twelve. The reason why I'm highlighting this post is because it gives you another opportunity to kind of learn the JSON path syntax. Now he compares it to Jquery but this is basically another opportunity to learn the JSON path syntax. So if you're interested in doing that, definitely a blog post to check out.
[00:10:06] The next two posts are related to PostGIS and as I've said I'm not an expert on that but I wanted to make them aware. The first post and both of these are from Crunchydata.com. The first post is Waiting for PostGIS three Stasgo JSON record. So apparently this is a new function that is available. So if you're potentially interested in that, definitely blog post to check out as well as a waiting for PostGIS three Hilbert geometry sorting. And they say order geometry columns will return rows using a Hilbert curve ordering and do so about twice as fast. So if you're interested in that, definitely another blog post to check out.
[00:10:47] 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 Scalingpostgres.com where you can sign up to receive weekly notifications of each episode, or you can subscribe via YouTube or itunes. Thanks.
