Episode Transcript
[00:00:00] In this episode of Scaling Postgres, we talk about unattended upgrade, arm benchmarks exploration and postgres performance. I'm Kristen Jameson, and this is Scaling Postgres episode 141.
[00:00:22] All right, I hope you, your friends, family and coworkers continue to do well. Our first piece of content is unattended upgrades ubuntu 18 four and PostgreSQL ten the Perfect Storm. So this is from Clar US, and he's talking about an issue where his production postgres database decided to upgrade itself one evening. Now, I've actually seen this occur, and in this post he describes exactly how it happens and why. So if you use Ubuntu, I highly suggest you check out this post. Now, normally when I do an installation of Postgres, I'm using the package repositories at app Postgresql.org, but depending upon the version of Ubuntu, it includes certain versions of Postgres. So for example, 18 four included Postgres Ten in their package repository, and in Ubuntu 24, they included Postgres twelve. So what this means is that if you are on Ubuntu 18 Four, you could be automatically upgraded to a dot release of a version ten, or if you're on Ubuntu 24, you could be automatically upgraded to a dot version of Postgres twelve. The reason is that Unattended Upgrades trusts the Ubuntu package repository and they contain those versions based upon your versions of Ubuntu. So if you look in this file, you can see that the Ubuntu package repositories are trusted for undetended upgrades and they happen automatically, but the Postgres repositories are not. So you're relatively safe if you're using one of these repositories as long as Ubuntu does not have the same version package of the one you're using. So for example, if you're using version eleven of Postgres, it doesn't matter if you're using 18 four or 24, it's not going to automatically upgrade that to the next point release. However, if you're on 18 four and version ten, you could be upgraded to the next point release during an unattended upgrade. Or if you're on 24 and you're on Postgres Twelve, it could automatically upgrade you to the next point release. So how he advocates getting around it is to add the Postgres packages as a blacklist, so they are not going to install them using an undetended upgrade method from the Ubuntu package repository. And basically you blacklist all the different packages that you're using that are Postgres related. So definitely if you use Postgres on Ubuntu, I would check out this blog post and follow the practice that he highlights here to make sure that your Postgres versions don't suddenly upgrade themselves one evening.
[00:02:56] The next piece of content is PostgreSQL Benchmarks apple Arm M One MacBook Pro 2020 this is from Crunchydata.com. Now, in a previous post, they did a PG bench tools analysis of different MacBooks across the years, and it looks like they included the data here from 2011 to 2019 and then used one of the new M One MacBooks containing these new Arm processors to look at the performance. As you can tell, it's dramatically higher and it looks to be double some of the 2019 numbers in terms of performance with regard to PG bench. So that's pretty great performance. And then he has the raw numbers here, and then he wanted to say, okay, how does this compare against some desktop processors? So he also included a Ryzen 2700 x and a Ryzen 39 50 x, one from 2018, one from 2019. Now you can tell this processor actually has a lot of CPU cores. It may be a 16 core processor, I'm not sure on that, or maybe it's a twelve, but you can take a look at the performance of the M one is pretty significant. Now, having this on a laptop is not too much of an interest to me, but this makes me wonder, gee, what kind of performance could you get on Arm servers such as the Graviton server, say at AWS? And I haven't seen any benchmarks on those particular servers. But if these results are potentially indicative of performance you can expect I might check out postgres performance on some of these Arm processors. So definitely an interesting blog post about Arm performance with PostgreSQL.
[00:04:32] The next piece of content is exploring a new postgres database. This is from Craigrsteins.com, and this post describes when he steps into a new database. What does he typically use to check it out and analyze it? And basically he uses psql, the postgres client interface. He goes over some of the settings he likes to make to his psqlrc file, such as automatically formatting the output with xAUTO, defining some prettier nulls, saving the history file based upon the database name, turning on query timing of course. And then in terms of analyzing what objects exist, using the backslash D command or DT to only look at the table relations in the database, as well as examining particular objects by just doing a backslash D to describe, say, the user's table. And you can get a sense of the table as well as the indexes, and then just selecting one record from the particular table to analyze kind of what the data looks like. So this is a quick way to explore any new databases that you are introduced to.
[00:05:34] The next piece of content is waiting for PostGIS three one performance. This is from Crunchydata.com. He's talking about some different performance improvements that have been made to the upcoming PostGIS 3.1. 1st, talking about large geometry Caching header. Only geometry reads faster text generation, and then he says how much faster? So he did an analysis using this example here, and as he says, over five runs, PostGIS three ran in 23 seconds, whereas PostGIS 3.1 ran in less than a second. Now he says this may be a bit of an outlier, but that's a pretty large performance improvement over 20 times. So it looks like with 3.1 there's going to be more performance coming to postgres now from a related post from Clever Elephant. CA talks about waiting for PostGIS three one vector tile improvements. So it talks about again more performance improvements and that some of these vector tile improvements have resulted in a 30% to 40% faster performance with three one, as well as reducing memory to approximately a third of what was used before. So definitely some improvements coming in three one.
[00:06:42] The next post solving the third argument isn't within one problem in PG routing. This is from Elephanttamer Net and it's a brief post describing how to get around this issue he described. So if you're interested in that you can check out this blog post.
[00:06:59] The next piece of content is ISOv. This is from Commandprompt.com. They're talking about the ISOv operator which helps you determine the type. So for example you can do select some number is of type text and it will say False or select two is of type integer and it will say True for example. And they have a bunch of examples including the comments of how to use this. So just a brief post about using ISOv to determine data types within Postgres.
[00:07:27] The next piece of content is actually YouTube video and it's webinar using SSL with PostgreSQL and PG bouncer by Andrew Dustin. This is on the second quadrant YouTube channel. Now, this is a bit of an older presentation, I believe from November of 2019, but it's still relatively relevant. Some of the software has been upgraded of course with Postgres and new versions of PG Bouncer. But if you're interested in setting up SSL authentication with these, you can check out this webinar.
[00:07:56] The next piece of content is writing a postgres foreign data wrapper for ClickHouse in Go. This is from Arransori Me and basically he describes setting up a data wrapper and writing it in Go as opposed to So and some of the issues he had to get around with that and writing a foreign data wrapper to an OLAP database called ClickHouse in order to pull data from it. So if you're interested in content such as this, you can check out this blog post.
[00:08:26] The next piece of content is an announcement that 3.0.3 PostGIS has been released as well as 3.1. Alpha three has been released as well. So if you're looking for those performance improvements, you may want to check this out.
[00:08:42] The next piece of content is what's new in the Cytus 9.5 extension to Postgres. So this is the Citus extension that enables you to do scale out of your postgres instances. And this is the open source version and it lists a number of bullet points of changes in terms of postgres 13 support adaptive connection management for copies, so you don't utilize too many connections across your cluster farm function to change a distributed table to a local table and a number of other enhancements. So if you're interested in the Citus extension, definitely check out this blog post from Citusdata.com.
[00:09:17] The next piece of content is query ID. Reporting in plpgsql. Check. And plpgsql Check is actually a static code analysis tool for analyzing essentially your procedures or your functions. And they've recently added a new feature being able to determine the Query ID to help you further optimize your code. So if you're interested in that, you can check out this blog post from Arjuju GitHub IO.
[00:09:43] The next piece of content is that PG timetable version three is out. This is from Cyber. Hyphen postgresql.com. This gives you essentially like a cron like interface to do scheduled tasks within Postgres.
[00:09:56] And the last piece of content is the PostgreSQL Person of the Week is Marcus Wanner. So if you're interested in learning more about Marcus and his contributions to postgres, definitely check out this blog post 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, Sam.
