Episode Transcript
[00:00:00] Speaker A: Hello. In this episode of Scaling Postgres, we talk about reduce replication lag, explain parameterized queries postgres day, and multiple PG bouncers. I'm Kristen Jameson, and this is scaling postgres episode 242.
[00:00:20] Speaker B: One.
[00:00:24] Speaker A: All all right, I hope you, your friends, family and coworkers continue to do well. Our first piece of content is five minutes of postgres episode 44, reducing replication lag with Maintenance I O concurrency in postgres 15. This is from Pganalyze.com. And if this topic is familiar, he's actually covering the post that I covered last week on Scaling postgres that I said was probably the most important post of the bunch. Talking about the changes that have been made to postgres 15 to allow Replicas to more easily keep up with the primary at the stage where it's trying to write the wall to the database files on the Replica and given some I O enhancements. That were added to postgres version 15. You can actually change the maintenance I o concurrency to help improve the apply performance of the replica. So I felt this post was so interesting, it would be good to reiterate it again and listen to Lucas's perspective on this post as well. So definitely check this piece of content out if you want to learn more about that.
The next piece of content, explain that parameterized statement in PostgreSQL. This is from CyberTech Postgresql.com, and they're talking about a situation when you're using parameterized queries or essentially prepared statements is what you typically hear it termed as where. The parameters you send to the SQL statement is done in two stages. One is a prepare statement where you define the statement you're going to use, and then you put a parameter in generally for the where clause or the order by clause. And that helps avoid some SQL injection attacks. But it also helps with performance because postgres can reuse plans for subsequent execution. So for example, you prepare it once and then you can actually execute a statement multiple times. And postgres does custom plans and generic plans. And basically, I believe it starts with a custom, but it may switch to a generic. But that's based upon heuristic measurements that it makes and also how many times a particular query is executed. But this post examines okay, you want to understand why a query was slow, but if you look in PG stat statements, all it's going to show you is the parameterized queries. It doesn't show you all the details. So how do you actually explain to find out what the query plan is? And this post talks about a way to get a generic plan for any parameterized query. Now, you can't just explain the statement putting in the parameter because it's not going to understand it. But what you can do is prepare the statement using the parameter as a part of it. Then you can set the plan cache mode to force a generic plan, and then you can actually send null as the parameter in and it will give you the generic query plan. You can also use Unknown for the parameters and that'll also let you be able to see what the generic query plan is. Now, we had talked about a previous post that showed how to do this technique as well, but what they've done here is they actually made an extension that does it that they're calling a generic plan. If you're interested in this, you can create an extension generic plan and then you can run this function generic plan and give it the statement you want to get the explain plan from and it does all of these different steps for you. So if you want to learn how to find a generic plan for parameterized queries when you're trying to understand why a particular query is so slow, definitely.
[00:03:56] Speaker B: Check out this blog post next piece.
[00:04:00] Speaker A: Of content route the Interesting things, not just roads with OpenStreetMap. This is from Rustprooflabs.com, and they're talking about a presentation that Ryan Lambert will be doing, or has already done at PostGIS day 2022, which took place a couple of days ago, november 17. So this is information with regard to his presentation in terms of downloads and links, but he also has links to the Crunchy Data's website where they're the ones that are actually hosting Postgres Day in a way that you can register. Now, the event has already passed, but I'm assuming they're going to have recordings of this posted at some point later. But if you're interested in that, definitely.
[00:04:40] Speaker B: Check out this blog post next piece.
[00:04:43] Speaker A: Of content postgres at scale running multiple PG Bouncers this is from Crunchydata.com. They're talking about a situation where you're running PG Bouncer for your system and it's just not keeping up. Now, the main reason I've seen that is basically the CPU usage for the core that PG Bouncer runs on is hitting 100%. So PG Bouncer is not multithreaded, it's single threaded, so it can only use one CPU core at a time. So if you put it on a dual core system or a quad core system, you're going to have a lot of resources that are not used. Now, one technique to get around that is to actually create separate PG Bouncer services and run them on different ports. So that way you can send traffic to individual PG bouncer processes. Essentially that can then talk to the database. But there's also another technique where you can actually use a common port and then that gets sent to multiple PG Bouncer backends. Now, they don't explore how to do that here because there is already existing write up for a few years ago about how to set up this process by Peter Eisenhower. So if you're realizing that your PG Bouncer is not keeping up or that the CPU core it's running on is the bottleneck for your database system, you'll probably need to add additional PG Bouncers. And reviewing this post as well as the write up on how to do it using a single port you'll probably find very beneficial.
Next Piece of Content Postgres Query Boost using Any instead of N this is from Crunchydata.com and he's talking about the N operator typically operates with a list of values, whereas the Any keyword typically looks for arrays. And this has some Ramifications depending upon your interface to postgres. So he has an example here where he's using Java where he has an array of numbers he's looking for and he actually cannot use N, but it works just fine using Any. And if you actually use N in your syntax of your statement and you actually explain, look at the Explain plan. Most of the times I've seen the actual Explain plan uses Any as opposed to N. So this is another option to use if you run into particular issues when you're trying to run certain statements, at least through a programming language. But check this blog post out if you want to learn more about that.
Next piece of content. Emacs client as editor in psql. This is from Fluco, 1978. GitHub IO. He's talking about psql. You have the ability to open up an editor by doing backslash e, and that'll open your defined editor and it will load in the last executed SQL statement that you can edit it, save it, close it, and then you can run it again. And apparently HEU likes to use emacs for this, but it takes a long time to load. But apparently you can run emacs as a daemon. So what you actually start is the emacs client that talks to that demon and it starts up much faster. So he walks through the process of doing that. And he also had some issues with his Zsh as opposed to Bash, but just using psql and the backslash e is a great way to edit queries you're working on in psql. And if you want to learn more.
[00:07:59] Speaker B: Definitely check out this blog post.
[00:08:01] Speaker A: Next piece of content improved Arch 64. Arm 64 support is available in the PostgreSQL rpm repository. This is from Planetpusgresql.org, and basically they've had a number of updates to the Rpm packages available for like Red Hat and Sentos and Rocky Linux distributions. So if you're using those on Arm, definitely seek out these new packages to use for postgres.
Next piece of Content PostgreSQL 16 Part Two or Commit Fest 2022 Nine this is from Postgrespro.com and they're doing a review of all the different code changes worked on in the last Commit Fest for the upcoming Postgres 16 end of next year.
[00:08:46] Speaker B: So definitely check out this blog post.
[00:08:48] Speaker A: If you want to learn more about those. Next piece of content. Multicloud Strategies with Crunchy Postgres for Kubernetes this is from Crunchydata.com and they're talking about their postgres operator and how they've set it up so it can actually communicate and do replication between two different cloud providers or even on premises. So basically they work out a process going from Amazon EKS as the primary and streaming to a standby on Google Kubernetes engine cluster. So if you want to learn how to do that with our operator, definitely.
[00:09:22] Speaker B: Check out this blog post.
[00:09:24] Speaker A: Next piece of content is Dynamic Spare Process Management in Pgpool Two. This is from Pgsqlpgpool blogspot.com. They're talking about an enhancement to PG Pool Two. That dynamically defines how many processes to keep around for incoming connections, and it did result in some cases with a five times speed up improvement. So definitely check out this blog post if you're interested in learning more about that next piece of content. How to use an external database for PG Admin user settings. This is from Enterprisedb.com and PG Admin out of the box uses a SQLite database to store user accounts, auto discovered servers, preferences, etc. Well, you can actually choose Postgres to store these, and basically you just define a config database Uri and it will point and start using Postgres. So check this post if you want to learn more about that next piece of content. There was another episode of Postgres FM this week. This one was on Hut Updates or basically heap only Tuble updates and they talk about what they are, the benefits, and how you can optimize for them. So if you want to learn more about that, definitely listen to this episode. Or you can check it out on YouTube using the link here.
The next piece of content the Postgres Person of the Week is onai Kaliche. If you're interested in learning more about Onai and his contributions to Postgres, definitely.
[00:10:46] Speaker B: Check out this blog post.
[00:10:48] Speaker A: And for our last piece of content, we did have another episode of the Rubber Duck Dev Show this past Wednesday evening. This one was on top down or Bottom up testing, so we talked through different considerations. When you're testing your application, whether you tend to like to do it from the UI side down, or start with unit tests and then build your application up from there. So if you're interested in that type of content, we welcome you to check out our show.
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.
