Planner Workarounds, Grouping Sets, Lateral Join, Not-So-Easy | Scaling Postgres 177

In this episode of Scaling Postgres, we discuss ways to work around the planner, working with grouping sets, using a lateral join and not-so-easy Postgres issues.

Content Discussed

• Working Around a Case Where the Postgres Planner Is "Not Very Smart"
• PostgreSQL grouping sets: ROLLUP & CUBE
• A simple example of LATERAL use
• PostgreSQL: Six Not-So-Easy Pieces
• Logical decoding of two-phase commits in PostgreSQL 14
• Using Cert Manager to Deploy TLS for Postgres on Kubernetes
• Bryn Llewellyn
• Rubber Duck Dev Show Episode 7 | Writing Secure Code

YouTube Video

Podcast Audio

Transcript

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., 2022, Eastern Standard Time for The Rubber Duck Dev Show. It 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.

Our first piece of content is "Working Around a Case Where the Postgres Planner Is '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 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 JSONB column.

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. 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. 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. The problem that they discovered, and it's even in the documentation, PostgreSQL's planner is currently not very smart about such cases. 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 JSONB 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, and 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 get a little bit more performance. Although you're going to have to deal with multiple indexes potentially. 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.

The next piece of content- "POSTGRESQL GROUPING SETS: ROLLUP & CUBE". This is from cybertec-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. 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: per country and the totals. You can also do it per country by product name for the total amount sold. 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 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, in which one defines the first column, and 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, but it's not going to be as efficient as using grouping sets. So the next thing to look at is a ROLLUP. So this is rolling up the totals by a certain area. So here we're doing a group by using a ROLLUP for country and then product. 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, the total for the 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 the total. So now you see the Argentina total, the Germany total, and then essentially the grand total for everything. Again, the advantage of these grouping sets or grouping by a ROLLUP 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. Then they took a look at CUBES and this is all the different permutations of calculations. So doing a group by CUBE, country and product gives you a country and its totals and then also the hats and shoes and their totals. So without respect to the country. 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 ROLLUP 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.

The next piece of content- "A simple example of LATERAL use". This is from fluca1978.github.io. They're talking about using lateral joins. 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. Then he used a query using a LATERAL join in order to show the events around it. Now, as a reminder of 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 first one, 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.

The next piece of content- "PostgreSQL: Six Not-So-Easy Pieces". This is from pgdash.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. Because of that, a lot of people use connection pooling solutions. Now this can just exist within your application framework or you can also implement third-party ones such as PgBouncer 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 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 have 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 move to minutes if you're doing that type of upgrade. But then they also say that logical replication is a way to do it with zero downtime.

But there are risks inherent in that and you have to check a lot of things to make sure that it's good before you do a transition. The next area they discuss is High Availability. 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 are a lot of third-party solutions that do that for you. The main ones they call out here are pg_auto_failover and Patroni, so you could definitely check those out if you're looking for a high-availability solution. The next area they mentioned is Bloat Management. 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.

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. With both of these together, you should be able to help optimize your queries for Postgres. 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.

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 are what I would say are more of 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. 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 pgoutput, 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.

The next piece of content- "Using Cert Manager to Deploy TLS for Postgres on Kubernetes". This is from blog.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 Crunchy Data operates. So if you're interested in that, you can check out this blog post.

Next piece of content, the PostgreSQL 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.

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.