background

Unattended Upgrade, ARM Benchmarks, Exploration, PostGIS Performance | Scaling Postgres 141

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

In this episode of Scaling Postgres, we discuss an unattended upgrade, ARM Postgres benchmarks, how to explore new databases and PostGIS performance.

Content Discussed

YouTube Video

Podcast Audio

Transcript



[00:00:00] In this episode of Scaling Postgres, we talk about unattended upgrade, armbenchmarks exploration and postgres performance. I'm Kristen Jameson, and this is ScalingPostgres episode 141.

[00:00:22] All right, I hope you, your friends, family and coworkers continue to do well. Our firstpiece 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 databasedecided to upgrade itself one evening. Now, I've actually seen this occur, and in this post hedescribes exactly how it happens and why. So if you use Ubuntu, I highly suggest you check outthis post. Now, normally when I do an installation of Postgres, I'm using the package repositoriesat app Postgresql.org, but depending upon the version of Ubuntu, it includes certain versions ofPostgres. So for example, 18 four included Postgres Ten in their package repository, and inUbuntu 24, they included Postgres twelve. So what this means is that if you are on Ubuntu 18Four, you could be automatically upgraded to a dot release of a version ten, or if you're onUbuntu 24, you could be automatically upgraded to a dot version of Postgres twelve. The reasonis that Unattended Upgrades trusts the Ubuntu package repository and they contain thoseversions based upon your versions of Ubuntu. So if you look in this file, you can see that theUbuntu package repositories are trusted for undetended upgrades and they happenautomatically, but the Postgres repositories are not. So you're relatively safe if you're using oneof these repositories as long as Ubuntu does not have the same version package of the oneyou're using. So for example, if you're using version eleven of Postgres, it doesn't matter ifyou'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 releaseduring an unattended upgrade. Or if you're on 24 and you're on Postgres Twelve, it couldautomatically upgrade you to the next point release. So how he advocates getting around it is toadd the Postgres packages as a blacklist, so they are not going to install them using anundetended upgrade method from the Ubuntu package repository. And basically you blacklist allthe different packages that you're using that are Postgres related. So definitely if you usePostgres on Ubuntu, I would check out this blog post and follow the practice that he highlightshere 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 MacBookPro 2020 this is from Crunchydata.com. Now, in a previous post, they did a PG bench toolsanalysis of different MacBooks across the years, and it looks like they included the data herefrom 2011 to 2019 and then used one of the new M One MacBooks containing these new Armprocessors to look at the performance. As you can tell, it's dramatically higher and it looks to bedouble some of the 2019 numbers in terms of performance with regard to PG bench. So that'spretty 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 Ryzen2700 x and a Ryzen 39 50 x, one from 2018, one from 2019. Now you can tell this processoractually has a lot of CPU cores. It may be a 16 core processor, I'm not sure on that, or maybeit'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 atAWS? And I haven't seen any benchmarks on those particular servers. But if these results arepotentially indicative of performance you can expect I might check out postgres performance onsome of these Arm processors. So definitely an interesting blog post about Arm performancewith PostgreSQL.

[00:04:32] The next piece of content is exploring a new postgres database. This is fromCraigrsteins.com, and this post describes when he steps into a new database. What does hetypically use to check it out and analyze it? And basically he uses psql, the postgres clientinterface. He goes over some of the settings he likes to make to his psqlrc file, such as aut --omatically formatting the output with xAUTO, defining some prettier nulls, saving the historyfile based upon the database name, turning on query timing of course. And then in terms ofanalyzing what objects exist, using the backslash D command or DT to only look at the tablerelations in the database, as well as examining particular objects by just doing a backslash D todescribe, say, the user's table. And you can get a sense of the table as well as the indexes, andthen just selecting one record from the particular table to analyze kind of what the data lookslike. 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 fromCrunchydata.com. He's talking about some different performance improvements that have beenmade to the upcoming PostGIS 3.1. 1st, talking about large geometry Caching header. Onlygeometry reads faster text generation, and then he says how much faster? So he did an analysisusing 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, butthat's a pretty large performance improvement over 20 times. So it looks like with 3.1 there'sgoing to be more performance coming to postgres now from a related post from CleverElephant. CA talks about waiting for PostGIS three one vector tile improvements. So it talksabout again more performance improvements and that some of these vector tile improvementshave resulted in a 30% to 40% faster performance with three one, as well as reducing memoryto approximately a third of what was used before. So definitely some improvements coming inthree one.

[00:06:42] The next post solving the third argument isn't within one problem in PG routing. Thisis from Elephanttamer Net and it's a brief post describing how to get around this issue hedescribed. 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 talkingabout the ISOv operator which helps you determine the type. So for example you can do selectsome number is of type text and it will say False or select two is of type integer and it will sayTrue for example. And they have a bunch of examples including the comments of how to usethis. 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 withPostgreSQL and PG bouncer by Andrew Dustin. This is on the second quadrant YouTubechannel. Now, this is a bit of an older presentation, I believe from November of 2019, but it's stillrelatively relevant. Some of the software has been upgraded of course with Postgres and newversions of PG Bouncer. But if you're interested in setting up SSL authentication with these, youcan check out this webinar.

[00:07:56] The next piece of content is writing a postgres foreign data wrapper for ClickHouse inGo. This is from Arransori Me and basically he describes setting up a data wrapper and writing itin Go as opposed to So and some of the issues he had to get around with that and writing aforeign data wrapper to an OLAP database called ClickHouse in order to pull data from it. So ifyou'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 releasedas well as 3.1. Alpha three has been released as well. So if you're looking for those performanceimprovements, 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. Sothis is the Citus extension that enables you to do scale out of your postgres instances. And thisis the open source version and it lists a number of bullet points of changes in terms of postgres13 support adaptive connection management for copies, so you don't utilize too manyconnections across your cluster farm function to change a distributed table to a local table and anumber of other enhancements. So if you're interested in the Citus extension, definitely checkout this blog post from Citusdata.com.. And they've recently added a new feature being able to determine the Query ID to help youfurther optimize your code. So if you're interested in that, you can check out this blog post fromArjuju 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 scheduledtasks within Postgres.

[00:09:56] And the last piece of content is the PostgreSQL Person of the Week is MarcusWanner. 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 getlinks to all the content mentioned in the show notes. Be sure to head over toScalingposgres.com, where you can sign up to receive weekly notifications of each episode, oryou can subscribe via YouTube or itunes. Thanks, Sam. --

episode_image