Episode Transcript
[00:00:00] In this episode of Scaling Postgres, we talk about planner workarounds, grouping sets, lateral join, and not so easy. I'm Kristen Jameson, and this is Scaling postgres episode 177.
[00:00:21] All right, I hope you, your friends, family and coworkers continue to do well. Before we get to our first piece of content, I wanted to let you know about an upcoming live show we're doing this Wednesday, 08:00 P.m. Eastern Standard Time for the Rubber Duck dev show that will be focused on database performance. Now, we're not going to be exclusively talking about postgres, but that will probably be a significant part of it given my experience. So if you're interested in attending this live show, we're going to be doing it this Wednesday. So feel free to stop by the Rubber Duck dev show and click on either one of these links at 08:00 P.m. Eastern Standard Time.
[00:00:59] Our first piece of content is working around a case where the postgres planner is, quote, not very smart. This is from heap IO. So in this post, what they were trying to do is to get an index only scan working. So an index only scan only uses the index to retrieve all the information you need for a query. Typically with an index scan, what you're doing is you're scanning the index, finding the record of interest, and then going to the heap to get the remainder of the data from it. But you can do index only scans where exactly what you're querying is right in the index. So if you have an index on an ID and all you're asking for in the select is the ID, it can just look in the index and not go to the heap to find out what's there. That gives you a speed boost because you don't have to go to the heap. Now, in the example that they're looking at here, they had an events table that had a time column, a user ID column, and a data JSON B column. And typically they would have an index on the time and the user ID of this column to pull back data. But they really wanted to get this specific query to use an index only scan. So as you can see in the where they're looking at a particular data type within the JSON of these different types in a particular time range. And they were also pulling certain columns in the select clause as well. So they created this index to try and get an index only scan. So they defined data in type. So they're indexing on data and type, and by the time right here, then they're including the columns that they're going to be using in the select. So basically, this is an additional payload stored in the index to prevent you from having to go to the heap. So they configure this. The only problem was it wasn't working. And the problem that they discovered, and it's even in the documentation PostgreSQL's planner is currently not very smart about such cases. And that's the case where you have particular operators or functions working with data. So it had a problem interpreting this with this operator as sufficient to not have to go to the heap. So what did they do for a workaround? They actually used a partial index. So instead of doing an index on the JSON B field, for type, they only indexed on time, but then they used the where clause to specifically define the types that they're looking for. So this will only index types with click change touch. But they're doing the index by time and still including these columns and that was sufficient for them to get an index only scan and as a result, they got about double the performance out of it. Now, I typically use these types of partial indexes with general performance improvements because it is a way to eke out a little bit more performance. Although you're going to have to deal with multiple indexes potentially. Like if you typically query certain types, you would need an index per type of query you would use. But this is an interesting workaround when you're trying to get index only scans. So if you want to learn more, go ahead and check out this blog post.
[00:04:09] The next piece of content PostgreSQL grouping sets, roll up and cube. This is from CyberTech Postgresql.com and they're talking about grouping sets. So they have an example table here where they have a country, a product name, the year and then the total amount sold. And in this scenario they are testing analytical queries. So when you select the country and then do a sum of the amount sold from this table and group it by the country, you're going to get output that looks like this just per country and the totals. You can also do it per country by product name for the total amount sold. And you can even do things such as using a case statement to group them differently. So all the USA together and then non USA together using this type of a case statement. But you could also pull this type of data using grouping sets. So in this initial example here, you can break up what you're pulling by country and by product, but it uses just one SQL command that's more efficient than trying to do a union. So for example, you do grouping sets. Your first set is defined as the country, which one defines the first column, two defines the second column. The second grouping set is by product name. So it simply lists one after the other for these grouping sets. Now, he said you could get the equivalent of this by doing a union all, but it's not going to be as efficient as using grouping sets. So the next thing to look at is a roll up. So this is rolling up the totals by a certain area. So here we're doing a group by using a roll up for country and then Product. And what you'll see is there are now totals that are appearing per country. So this is the total for Argentina, this is the total for Germany, total for USA, and then also the ultimate sum of all countries and all products. Now, what they also said is that sometimes you don't want a null to appear here or you want it to say something different. So in this example, they actually put the actual query in the subquery and then they used a case statement to replace the nulls with total. So now you see the Argentina total, the Germany total, and then essentially the grand total for everything. And again, the advantage of these grouping sets or grouping by a roll up is that it does all the calculations in one pass. It doesn't require multiple passes if you were to try to do the same thing using unions. And then they took a look at cubes and this is all the different permutations of calculations. So doing group by cube, Country Product gives you a country and its totals and then also the hats and shoes and its totals. So without respect to the country. And then the last thing they mentioned is that when you do an explain plan of grouping sets, you can see how the actual query plan is different than just using unions. So the advantage of using grouping sets or roll up or cube is that it does all of these calculations in one pass. So if you're interested in that, you can check out this blog post.
[00:07:08] The next piece of content, a simple example of lateral use. This is from F. Luca 1978 GI IO. And they're talking about using lateral joins. And it came from an example where someone was trying to select events from a table where there were no more than ten minutes from one another. So looking at an event, what events were around it within a ten minute interval. So he generated a table that had events listed every two minutes and then did a generate series to generate 100 of them. And then he used a query using a lateral join in order to show the events around it. Now, as a reminder for what a lateral is, for every row that you are pulling, it's going to query other rows, so you can think of it as a nested loop. So this first row, 501, is going to be querying the event table again, or whatever table you designate to look for other rows. So essentially that's what this is doing. For each row of this table selected, it's going to be running this query and it's going to be selecting other IDs, the event name, as well as calculating a time lapse. So a time difference between the events, where the primary key is not the same one that you are on. So you're not going to pull 501 for the 501 event, for example, and where the time difference between them is less than or equal to ten minutes. So for example, in the first event, 501, you're pulling out five events and they are in these two minute intervals up to and equaling ten minutes from that event. So that's essentially how a lateral join could give you an answer to this question. Now, this 1st 20, he wasn't doing the exact lateral join syntax, but he does show you a version of that down here. But if you're interested in learning more about lateral joins, you can check out this blog post.
[00:08:57] The next piece of content. PostgreSQL six not so easy pieces. This is from PG. IO. They're talking about six areas that can become a challenge when you're managing Postgres. The first is connection management, and this goes to Postgres, having its connections be a one to one correlation with processes. A lot of other software solutions use threaded connections, whereas Postgres creates a new process for each new connection. So that's a very heavy thing to do. And because of that a lot of people use connection pooling solution. Now this can just exist within your application framework or you can also implement third party ones such as PG Bouncer and Pgpool. That allows you to really ramp up the number of connections that PostgreSQL can support and still operate very efficiently. The second area they discuss is zero downtime upgrades because the upgrade process essentially always requires downtime. The only one that doesn't is logical replication, but that has its caveats you need to take into account basically when you're doing a minor version upgrade, you basically still need to reboot the system to load those new binaries in and it brings the system down. But when you're talking about major version upgrades, that can take a while. If you're a smaller database, you can do a PG dump and a PG restore to restore the database in the new version. If you're at a larger database, you would probably want to use PG upgrade. Now, they say in either case the databases would be down for a considerable amount of time. That's not my experience. If you use the linked mode of PG upgrade, again, depending on the size of the database, you could do it in a matter of seconds to moves to minutes if you're doing that type of an upgrade. But then they also say that logical replication is a way to do it with zero downtime. But there are risks inherent of that and you have to check a lot of things to make sure that it's good before you do a transition.
[00:10:56] The next area they discuss is high availability. And this is basically there's nothing out of the box with Postgres that supports high availability. It has a lot of features, but there's not an all encompassing solution. So there's a lot of third party solutions that do that for you. And the main ones, they call out here are PG Auto, Failover and Petrone, so you could definitely check those out if you're looking for a high availability solution. The next area they mentioned is Bloat Management. And basically this is the tendency for postgres to bloat its data files and that's because whenever a delete happens or an update happens, it actually doesn't remove the row or do an update in place, it marks it for deletion and then deletes it later because it needs to maintain those old versions to handle postgres's concurrency control. But as a consequence you need to go in and vacuum those up. So there's a tendency for bloat to develop in tables and indexes. Now, using reindex concurrently is a great way to rebuild your indexes and to keep them efficient, but for tables that's a little bit more of an inconvenient solution. There's not a great way to handle it, but PG Repack is a way to recompact these tables live and online, but you need to have sufficient disk space to do it. The next area they talk about is Query Plan Management. That's basically a way to monitor what's going on in terms of queries in the system. So PG Stat activity gives you immediate insight into what queries are actively running. But PG Stat statements is a great way to look at statements running over time and how many database resources queries are using for you to then optimize. And this is an extension that you can install with postgres. There's also Auto Explain that explains queries that take too long so you can look at what the parameters are of them. And with both of these together you should be able to help optimize your queries for postgres. And the last area they talk about is Tuning. So basically configuring postgres to make it as efficient as possible for your workload. Now there are hundreds of configuration options to adjust, but there's really a minimal set that can get you started and then you can tweak from there. But it is a bit overwhelming when you get started if you really want to get into the weeds with tuning. But this blog post lists some of the six things you need to be aware of and gives you some clues as to where to look to resolve some of these potential issues.
[00:13:25] The next piece of content logical decoding of two phase commits in PostgreSQL 14. This is from PostgreSQL fastware.com. They're talking about a new feature that's going to be coming in 14 that supports logical decoding of two phase commits. So two phase commits I would say are more a rarity and relatively few people would use them because they're a use case where you have two different database systems, not direct replicas, but basically it's trying to do more of a multimaster scenario. So it's not like a physical replica you would set up with streaming replication, nor even just logical replication. There are two database systems that are trying to both be masters and in order to coordinate transactions between one another, you have these specific features that support two phase commits to be able to ensure that each database doesn't lose transactions. And they talk about that here and go into depth about it. But this two phase commit did not support Logical Replication, but they've put the decoding side into postgres 14, and they're planning for the actual output plugin called PG Output, to be upgraded by postgres 15. So basically some features will be ready in 14, but they hope to have the whole thing complete in postgres 15. So if you're interested in two phase commits and logical replication, maybe you want to check out this blog post.
[00:14:53] The next piece of content using Cert Manager to deploy TLS for postgres on Kubernetes this is from Crunchydata.com, and they're talking about where you want to run Postgres and Kubernetes. In their example here, they're using the Postgres operator from Crunchy Data, and you want to use your own certificates and run your own certificate manager. And specifically they're looking at Cert Manager, which is an open source certificate management solution, and how you can use that to generate certificates for the postgres operator that crunchydata operates. So if you're interested in that, you can check out this blog. Post next piece of content is the Postgres goal. Person of the week is Bryn Llewellyn. So if you're interested in learning more about Brynn and his contributions to Postgres, you can check out this blog post and the last piece of content. We had another episode of The Rubber Duck Dev Show, so if you're a software developer and want to learn more about writing secure code, you can check out our episode on that in the link provided. And like I mentioned, if you're interested in learning more about database performance, our upcoming episode this Wednesday, 08:00 P.m. Eastern Standard Time, is on database performance.
[00:16:04] 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 week notifications of each episode, or you can subscribe via YouTube. RyTunes thanks.
