Episode Transcript
[00:00:00] In this episode of Scaling Postgres, we talk about multiranges, missing metrics, newbie postgres and conference videos. I'm Kristen Jamison and this is Scaling Postgres episode 211.
[00:00:22] All right, I hope you, your friends, family and coworkers continue to do well. Our first piece of content is multiranges in PostgreSQL 14. This is from Cybertechn postgresql.com. So you can create a range by just using a function n four range and give it two integers and then it will output it here. And if you notice one side is a bracket, one side is a paren. The bracket means it's inclusive, the perenn means it's exclusive. So essentially there's values ten to 19 within this range. So it does not include the 20 and it does the things to check that things are in range two where they show here where they try to show a high to a low range it gives you an error that's an invalid range. Now once you have a range you can then use this operator, that's kind of like a contains operator to tell you if a value is within that given range. And you could see when you do a check for 17 it gives you a true when it's in the range of ten to 20. And of course you can also use date times. That's probably the more frequent use case for using ranges and then they get into show you the multirange capabilities. So you can use that same int four range and combine two of them together to generate an int four multirange. So you can see the results here where it includes the two ranges that were established here. And you can add as many ranges as you want to this. I'm not quite sure what the limit is, but what is interesting about that is that if you include overlapping ranges, it will actually resolve the overlaps to just include what the ultimate set of ranges are. So here you're concatenating three ranges together but it only ends up storing in the final data type as it were, two ranges because some of them were overlapping. Then of course they mentioned you can of course use these range types within a table and they give that example right here. And they also mentions how it can also handle infinity from either the starting or the ending range just by including null in the range. And you can also add those ranges together in a multirange to get basically an infinity at the low end and the high end as well. You can even do operations on ranges that like you calculate the difference between two ranges. So for example for in the range of one to 20, if you subtract the range of four to six, it's going to be missing those in the multirange that gets returned. Similarly, you can do an intersection of two ranges to get what the results are. And here you're doing an intersection of those same set of ranges to give you the intersection of them. And you can even do things such as aggregating ranges together on the fly. So in this example, it's reading the from and before in separate columns, but it aggregates them into a common range using a range aggregate function. So it operates across multiple rows. And similarly you can also UNNEST a multi range into multiple rows. So this is a great post that shows you all the different manipulations you can do with ranges and multiranges, and I definitely encourage you to check it out.
[00:03:26] The next piece of Content missing metrics required to gain visibility into postgres performance. This is from Coroute.com, and what they're exploring is that when they're doing performance monitoring, they like to be able to show success rate of operations as well as latency of operations. And in terms of postgres, that's usually talking about queries, but it's kind of hard to get both of these metrics with the tools that postgres has by default. And they're showing some examples of this. Here how you have to look into multiple places to kind of get the answer you're looking for. Now the three different places you have to look is PG Stat statements, PG Stat Activity, and what is being logged in the database. So when you look at PG Stat statements, it gives you a historical record of the queries that were run both long and short queries, but it doesn't record any failed queries and it doesn't show you anything that's running currently in the system. In contrast, PG Stat Activity only shows you those queries that are running right now. You don't know if they're going to ultimately be successful or fail, but they show you all the currently running queries. And then lastly, what usually gets logged is queries that are longer than a particular time frame based upon the configuration log men duration statement. So these are the long queries that are getting logged. Short queries don't get logged, but you also get failed queries that get logged. So to get the universe of all the queries running, failed queries, short and long, historical and current queries, you basically have to look at these two system views, PG Stat statements, PGSTAT Activity, as well as what's being logged. So they have developed an open source Prometheus agent to get this information together. That give you a record of success rates and latencies. And they show a little bit how they're able to do that here. And using their tool, they ultimately show you how you can track the errors and query execution times. But the interesting thing is that these are open source agents you can use in the Prometheus agents, so presumably you could use these in your own environment to track these sorts of metrics. So if you're interested in doing that, you can definitely check out this blog post.
[00:05:35] The next piece of Content PostGIS for Newbies this is from Crunchydata.com and they're talking about getting started with PostGIS and basically PostGIS is for geographical information systems and they basically read spatial data primarily. And usually to get started, you're going to have the extension Postgres installed on a Postgres database. You might interact with it with a desktop GIS program. Maybe you're pulling the data to perform some data science functions. And you can also display things out on the web to an end user. So for this example, Post, they're talking about using Postgres and you can use any particular version. They're using their hosted Postgres version. Of course, you need the PostGIS extension that you need to install in the database. They're using PG Admin to be able to get a Gui interface because it actually has a Geometry Viewer feature to show you some maps in the views. They're looking at QGIS for the desktop app for interacting with the GIS data. And they're also talking about the open source tool they developed called PG Featureserve to actually display Postgres data to the web through an API. They go through the process of loading data, showing you how it looks in PG Admin with the Geometry Viewer. Then they talk about different ways you can calculate areas or distance with the data that they loaded, as well as getting started with some QGIS, as well as publishing a view to the web. So if you want to get started very simply with PostGIS, definitely check out this blog post.
[00:07:06] The next piece of content is actually a YouTube playlist, and it's a YouTube playlist of Cituscon that recently happened a number of days ago. So this contains all the different videos that happened at Cituscon. Now a lot of these videos are about the Citus, which is an extension for Postgres implementing scale up capabilities, but a lot of it covers general postgres as well.
[00:07:28] The next piece of content five minutes of Postgres episode 13 new SQL, JSON and JSON table features in Postgres 15. This is from Pganalyze.com and we covered a lot of these posts in last week's episode of Scaling Postgres, talking about the new JSON features coming to Postgres 15. And he goes into more depth of these blog posts and explains the different features coming. So if you want a different perspective on that, you can definitely check out his episode here.
[00:07:56] Next piece of Content pgivm a PostgreSQL extension providing incremental view maintenance feature this is from Yuga Nagata PGSQL blogspot.com, and over the past year he's been talking about a feature that they've been wanting to add to Postgres called Incremental View Maintenance. So this is a materialized view that updates itself normally with a materialized view. It's basically a view that has actually been made into a table. You create materialized view to create it, so the table actually exists. But that data of course comes from base tables. And as that data changes, you need to refresh the view. Refresh the materialized view. Well, the Incremental view maintenance is a way of using triggers to keep the view up to date from the base tables. So basically it keeps the materialized view up to date. Now, there's an overhead with doing that, but there may be use cases that it could be beneficial. So this post is actually talking about an extension, PG IVM, they created. So their intent was to make it a feature, but because people were interested in it, they actually went ahead and released it as an alpha or a beta extension. So you could actually start using it in current versions of Postgres today. And basically you create an incremental view maintenance using these specific functions here once you've installed the extension. So if you're looking for a way to keep your materialized view up to date incrementally, maybe you'd want to check out this new extension.
[00:09:28] The Next Piece of Content update on the trademark actions against the PostgreSQL community this is from Postgresql.org, and this is an update to the trademark issues that have been discussed in the Postgres community between two different postgres organizations. And if you want to learn more about what's going on with this, I definitely encourage you to check out this recent post.
[00:09:51] The next piece of content. PG Backrest multirepositories Tips and Tricks this is from Pstaf GitHub IO, and they're talking about the capabilities of PG Backrest to interact with multiple repositories. This is like saying interacting with Amazon S Three or Google's cloud storage. Well, this particular post talks a lot about the impact of Postgres Archiving of the Wall and how that's handled when working with multiple repositories. So if you're interested in that, you can check out this blog post. The next piece of content. The postgres Girl person of the week is June Gulem de Rote. My apologies for that pronunciation, but if you'd like to learn more about contributions to Postgres, definitely check out this blog post and the Last Piece of content we had another episode of the Rubber Duck Dev Show this past Wednesday evening. This one was on various small projects we're working on. So if you're interested in that type of long form, developer based content, definitely welcome you to check out our show.
[00:10:52] 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.
