Tuning PostgreSQL to work even better

Hi, thanks everybody. So I'm Karen Jex. I'm a senior solutions architect at Crunchy Data and so pleased to I do apologize, I don't like being right behind it. Let me do this. Is that working okay? Right, I'll stay there. Thank you. It's a real pleasure to be here to talk to you about how to tune Postgres to work even better. So I'm going to get the confession out of the way first before I do anything else. I'm not a developer. I've never been a developer. But as you can see from the diagram that depicts my career so far, I do know about databases. I was a DBA for 20-ish years before I became a database consultant and now first time I've got a job title that doesn't say database

in the title, but I still only work with databases. So along the way I've worked with a lot of developers and I've learned a lot from the developers that I've worked with, and I've always tried to share what I know about databases with those developers. I'm also doing a part-time PhD in computer science, so I've had to start to hone my limited development skills, which is interesting for me. So I'm here to talk about PostgreSQL or Postgres because no one's got time for four syllables Postgres is obviously a popular database, not just for Django applications, but with developers in general. And what people tell us time and time again is that Postgres

just works Which is great because it means you can leave Postgres to do its thing and concentrate on what you do best, which is developing your applications. So Postgres when you install it takes up very little space by default. It's designed so that you can install it pretty much anywhere you like without really worrying about the resources that it uses. But what that does mean is that those default parameters that it comes with might not be right for your production database There are lots of Postgres configuration parameters. So there are 345 in version 14 and 380 in version 15. Version 16 came out in beta last week and I haven't looked yet to see how many there are there.

But the good news is that you don't need to know how to tune all of these parameters. You don't even need to know that most of them exist. If you just know about a handful of the Postgres configuration parameters then you should be good to go to set your database up so that it works well. And then you can just leave it to work so that you can get on with developing your application. So the idea today is that I will go through a handful of configuration parameters, the most important ones that I think you should look at. You'll hopefully come away knowing what they are, what they do, why you want to set them and also how to set it to the right setting for your case.

I'm not going to talk through the agenda, I just like to leave it there and I keep going back to it through the presentation. So partly so you can see where we're up to, partly so that you know when I might stop talking Sorry, I do like to make a few poor jokes as we go through. So you can set parameters in Postgres in various different ways and at several different levels. You can set them for the whole Postgres cluster. So that would be either by putting the relevant value in the PostgresQL. conf or by using an alter system. So you set the value that you want for your parameter. You can set them just for one database. So here we're doing an alter

database DB1. You can set them just for a particular user when that user logs in by an alter role. And you can even set it just for a particular user when they connect to a particular database. You can set it for your current session by doing a set parameter or just for the current transaction by doing set local parameter Some parameters can be changed online, others need a restart, some can be changed by any user, others can only be changed by a superuser. If you look in the PG settings view at the context field, then you'll see which are which. So there are seven different contexts, and I'll just

quickly go through those from the order of the most difficult to change through to the easiest to change. So internal parameters. You you pretty much can't change these. Once your cluster's up and running they're set. So They're set when you do your initDB to create your cluster. And some of them you can configure at that point. So things like block size, server version, wall segment size are all set when you create your cluster. Postmaster parameters, that means that you need to restart the server for them to take effect. So that will be things like archive mode or max connections. Siccup doesn't require a restart, but you do need to reload the configuration file.

So you'd do something like a PG CTL reload or a PG reload conf inside your database. A couple of examples of those, archive command and max wall size. We'll get to the actual parameters later, so don't worry about the actual parameter names that I'm giving here. There are just a few super user backend parameters, you probably don't need to know about those. So those are ones that you can either change in the PostgresQL. conf or a superuser can change them. They will only affect new connections to the database, not existing connections. Backend, a similar story, but these will affect existing connections.

Superuser, um so superuser parameters can either be set in PostgreSQL. conf or a superuser can uh set them by doing um a set Uh user, so any user can set these parameters for their own use case So examples of this would be the search path, work mem, and random page cost. So a lot of the ones that are to do with your personal memory usage and query tuning parameters. And you don't need to remember those different contexts, you can obviously look up in the EPG settings view if you need to know that. So different places that you can look for information about parameters

The Postgres docs actually are quite extensive. They have a lot of information. There is a default PostgresQL. conf that gets created when you create your cluster. And then we just saw the PG settings view. I'll just go through each of those. You don't need to be able to read this, it's just to show you the page. So the server configuration section is actually chapter 20 of the PostgreSQL docs. So you've got sections for different types of parameters. You've got information about memory-related ones, checkpoints, and different connections and session type information. And you'll get information like this if you go to one of those sections. It will tell you things like the name of the parameter, what type it is, how you can change it.

minimum, maximum, default values, etc. And then it will also give you some information telling you what that parameter does, what kind of settings you might want and and how to how to tune it. So as I said, when you create your Postgres cluster, you actually get a default PostgresQL. com file which lists all of the possible parameters. So it has the initial part of the file, gives you information about how to read the file and how to use the file. And then you have all of the available parameters. They're grouped in the same way as they're grouped in the PostgreSQL documentation. So here I've got the connections and authentication section. information about some connection settings.

You'll see that all of the default values are included but they're commented out And it gives you comments about each of them, gives you some information about the parameter, and also tells you whether or not changing that parameter requires a restart of your server. Most people just keep a copy of this default file because it's very verbose. So normally you just keep a copy of this file for reference if you want to look at the different parameters and then just keep a separate Postgresql. com file with just the actual parameters that you change in it. We already looked at just the context column of the PG settings view.

And you can see a lot more information about the different settings in here. So you can see things like a description, you can see the context that we already saw, you can see the minimum values, maximums, and the defaults. So that's all the different ways that you can get information about settings. If you want to actually see the values That you have for your configuration parameters, you've got a couple of options. You can use the show command and that will give you the current value. Or again, you can use the PG settings view. So the show command would just give you the actual current value for the session that you're in at this particular moment. You don't know from that whether that's the default value.

whether it's set just for your session or for the whole server, and you can't see anything about how to change it. In the PG settings view, you've got so here I've got an example for the max connections, max connections parameter. For legibility, I've uh missed out a few of the columns. I've just given you the most useful ones. But you can see here that this particular value It's coming from my PostgreSQL. conf. If you change that value via an alter system then that would show you PostgresQL. auto. conf so you can see if that's been changed. You can see that Postgres has written that to the auto.

conf This is another example from PG Settings, this time for WorkMem, and this time you can see that the source is default. Okay. So that means it hasn't been changed. If you set that via, for example, a set work mem, then that default will change to session so that you know that it's relevant just in this particular session. So we've had a look at the different categories of parameters, how we can change them and how we can look at the values. So I'll get into the most important bit, which is, as I promised, the handful of parameters that I think are the most important that you'll want to set.

That will generally do the trick for making sure your database runs as you want it to. So I've grouped them into categories. So we've got parameters to do with connections and sessions, parameters to do with memory use. Parameters to do with logging, so that's not going to impact the performance of your database, but it does help to speed things up a lot if you need to do debugging. uh queries to do with wall or write a head log and then some uh parameters to do with query tuning So, first of all, parameters that affect connections to the database. Listen addresses. This isn't a performance tuning parameter. It's not going to impact performance in any way, but it's one that does sometimes trip people up.

because most packages when you install Postgres by default, sorry, when you initialize a cluster, by default listen addresses is set to localhost Which means that you can only connect over a loop back connection on your on your database server. So you'll get an error message if you try and connect from anywhere else Which is this message here asking you, is the server running on that host? Is it accepting TCP/IP connections? So So most people want to allow some kind of external connections to the database. You've probably got one or more application servers that you want to connect to that So, most people will set that to an asterisk, which means that you're accepting connection requests from all available IP interfaces.

There are various different options for that. You can set it to all zeros if you just want to listen for all IPv4 addresses, to two colons if you just want to listen to all for all IPv6 addresses And then you need to remember that once you've set this, this means that Postgres is going to be listening for those external connections. But it doesn't mean that people can actually connect to your database. There's also the pghba. conf, the host-based authentication file. You need to add lines to that file to allow different users to connect to different databases. Okay, so that's the first parameter.

And the next one is max connections. As it implies, Max Connections says how many concurrent connections we can have in total to our database. The default value for that on almost all systems is 100. If max connections is 100 and you're the 101st user to try to connect, you're going to get an error. If you consistently get this error then you might be tempted to set max connections to a really high value just to avoid the problem and not see the error anymore. But you just need to remember that Postgres actually forks a new Postmaster process every time you connect a client. So the architecture of Postgres means that you really don't want more than

probably a few hundred connections um on a small system possibly not even a hundred connections So if you've got hundreds or thousands of connections, then you're going to want to start thinking about connection pooling, perhaps putting something like PG Bouncer in place. More than once as a DBA, I was called in to try and resolve a problem where um Queries were just hanging or they seem to just be hanging, people were queuing up, waiting to do things, nothing was happening on the database. So this is my amazing artistic skills here and I apologize for I apologize for that. So this is unhappy users waiting for a lock on the database

Investigation revealed that what had happened was somebody had logged into the application, they'd started um performing some actions and then they'd left for lunch or for the weekend or even to go on holiday. Their transaction was still open and still holding on to all of the locks that it had taken Not only can this block other users and stop them performing queries, but it can also prevent vacuum from cleaning up dead rows and therefore it contributes to table bloat. If you want to avoid this, then it's worth setting the idle in transaction session timeout parameter. I love how long some of the Postgres configuration parameter names are, but at least you know they do what they say on the tin. You can often figure out what it's doing.

It's disabled by default. Oh sorry, so there it's I've just explained what that is It's disabled by default with the value zero. But you can think about setting it to something like maybe 30 minutes. So if someone just gets distracted at the water cooler for a few minutes, you're not going to kill their session But if they go off for lunch or away for the weekend, then you're going to kill that session and allow other people access to that resource. Okay, so that was uh just I think three sessions to do with uh connections and sessions. Now we'll look at parameters that affect how much memory is used and how much avail uh memory we make available to the database.

Shared buffers. So this says how much memory we're making available to Postgres for caching data. The default is typically 128 megabytes. The default units 8 kilobyte blocks. It's generally good for performance to set it much higher. So lots of different rules of thumb, but in general looking at something like 25 to 40% of the available memory on your system is often a good place to start. If you've got a system with less than one gigabyte of RAM, then you might need to look even lower than that or maybe start at 25% and see how you go. The entire amount of memory is allocated to Postgres

when you start the Postgres server. The amount you need is very much going to be use case dependent. So it's something where, you know, you can take this as a rule of thumb, but you're going to need to test on your system to make sure that you get that right. So you'll see performance will, you know, you'll have a bell curve type thing. So you can you can usually see if you've got that about right WorkMem. So workMem is the maximum amount of memory that we can use by a query operation. So not an entire query, but one part of a query potentially. Before it spills to disk and writes a temporary file. So the default for this is four megabytes. Larger values can be useful, especially if you've got queries that are performing large sort operations

Um so you could uh try something like ten megabytes for that. Um If you increase this, or even if you don't, sorry, you can set log temp files. So if you set log temp files to zero, that means every time you write a temporary file to disk which is almost always for this reason, you'll see that happening and then you can decide whether or not you need to increase work mem What's worth considering though is that, as I said, it's not an amount for a whole query. an amount per sort or per operation. So if you've got, for example, 50 users who are all performing four sorts

And using 10 m megabytes of work mem for each of those, that's about 2 gigabytes of memory that you're using So what can sometimes be useful is setting a conservative value for work mem for the whole server, and then if you've got sessions that have that um intensive sort type operation going on, you could increase it just for that particular session. Maintenance work mem is similar, but it's memory used for maintenance operations, so things like vacuum, create index, alt table add foreign key. So you're going to have fewer of these in general going on at the same time than you would work mem. So it's usually safe to set this to a higher value.

So the default is 64 megabytes You can uh improve the performance of your maintenance tasks often if you increase this There have been various different tests that have shown that if you set it really high actually you don't necessarily get massive performance gains. So again something to test. A good starting point, potentially 5% of your available RAM Auto vacuum is a bit of a special case, so by default it will use up to three times maintenance work mem. Just put the caveat there if you've got those default parameters. Okay, uh logging. As I said, the logging parameters aren't going to do anything to the performance of your database, but they do help things to run smoothly because it means that you can

investigate much more easily what's going on. Logmin duration statement was mentioned this morning by Rudy and I completely agree that it is a very good idea. Was it logmin duration statement? I think it was a I think it was a different one. Um sorry. Um so this means if you set this it will write into the Postgres logs all of the statements that have taken longer than that particular time. So this can help you to track down unoptimized queries in your session. It's disabled by default I've written a suggested value of one second, but it is very, very much dependent on what's normal for your system. So

really, whatever counts as too long in your system, you probably don't want to be writing all of your queries into the logs, but what you do want to know is when am I having queries that are running for longer than I want them to? And you might not want to have this in place all the time on production systems. You might just want to consider putting it in place when you need to do some debugging. Log line prefix. So in the Postgres logs by default we just have um %M % P which is a timestamp with milliseconds and a process ID. So each line in the log will contain that information and then the actual message

I've given an example of suggested value because I find it useful to have much more information in my logs than that. So I would often do percent T so that I've got the timestamp but without the milliseconds, obviously up to you which you prefer Percent R to say these aren't intuitive but they are listed in the documentation. Percent R to say which host uh I'm connecting from. percent U to say which user I'm connecting to the database as and then I've put in the literal character at %d to say which database I'm connecting to, and then%P for my process ID. So you can use any mixture of these special characters and literals to get the information that you want in your log.

So, as I've said, if you add more information, generally it makes it much easier to go in and debug what's going on. So things like who, what, where, when Um just gives you much more information to work with Wall parameters. So tuning your write-a-head log and checkpoint parameters can actually have quite a significant impact on your performance. Wall buffers. So uh this is the number of disk page buffers in the shared memory for your writer head log The default is minus one. That means Postgres is going to calculate it automatically.

And it calculates it automatically as approximately 3% of your shared buffers, up to a maximum of 16 megabytes, which is the size of one wall file. If you've got a large number of concurrent connections, then it is often useful to increase this value. So I've suggested here potentially increasing to 32 megabytes. But again, you would want to test and see what's the right value in your situation. Checkpoint timeouts. You want ideally Postgres to perform checkpoints based on a timeout so that you know when they're going to happen.

So a checkpoint is when Postgres is going to flush all of the dirty data pages to disk. They're already written to the wall files, it's already safe, it's already there. But during a checkpoint it will flush the actual data pages to disk. So we're going to trigger a checkpoint if one hasn't happened within checkpoint timeout. So the default here There we go. I've uh gone ahead of my gone ahead of my uh notes there. So you've got to get the balance. Checkpoints are expensive and I. O. intensive. So you do um you don't want to be doing them too often But you do want them to be happening fairly frequently because you don't want crash recovery to take too long.

So, the default value is five minutes. Most people find that that's too low and prefer somewhere between 10 and 30 minutes. Again, it's going to depend very much on how much activity you have on your database. So it's going to be a case of testing that. But generally five minutes people find is too low. We've also got max wall size. So I said you want your checkpoints to be triggered by a timeout, ideally, so that they're regular, they you they happen at a time that you you know and you expect it But on the other hand, if you have sudden unexpectedly high activity , you might want a checkpoint to be triggered before that. So the default value is one

gigabyte, which is the equivalent of 64 wall files. The suggested value is always quoted as, or often quoted as a half to two-thirds of the disk space available in your wall directory. But that doesn't tell you how big your wall directory should be. So it's a bit of a chicken and egg situation there. The main thing is to monitor your logs. Checkpoints are logged by default. So you can see if they're happening because of a timeout, which is what you want. or if they're happening because um you've hit the max wall size. If you're often finding them triggered by max wall size then you probably want to increase this and make sure you've got more space available for your wall

files. And then finally query tuning parameters. So you've got effective cache size, which actually Isn't um it's not a memory allocation. It's just a hint to the Postgres planner to say this is approximately how much memory you have available to you for performing certain operations. So this will tell it what kind of operations it should perform. If it thinks it's got lots of memory available, it might go for um index lookups rather than doing a full table scan. So suggested value here is 50 to 70%

of your total memory available. You need to leave some space for shared buffers, which you already set earlier, and maybe 5% for your operating system. Obviously, that's going to depend on your system. Random page cost uh is slightly less important than the other ones, but it can be significant. So it's giving the planner an estimate of how expensive it is to fetch a disk page. And it was based really on old-fashioned spinning disks. So the default value is four. If the value's lower, then Postgres is going to prefer index scans over

full table scans. And it can be set much lower for fast disks, and especially if you've got solid state disks, then usually it's recommended to set it as 1. 1 or potentially 2 for fast-spinning disks. Again There's a bit of testing involved to see what's right, but in general the default value of four most people, unless you're on old spinning discs, you'll find that it's uh much too high. Oh, I slipped in an extra um agenda item which is parameters to leave alone. So F sync and auto vacuum. You can improve performance of your database by doing away with the pesky

fsync calls. You know, it it takes quite a lot of resource for Postgres to make sure that all of the updates are physically written to disk. You know, the only benefit after all is to make sure that your your cluster can recover to a consistent state after an operating system or hardware crash. This actually used to be um fairly standard performance tuning advice to switch F Sync off, so much so that they had To put this particular message into the docs and the PostgreSQL. conf. Turning this off can cause unrecoverable data corruption. you are likely to get a database that will not start, that is corrupt, that you cannot use. I do apologize, sorry

Something you can do if you, I mean if you really do not care about the data in your database, and and there are some cases where that's That's true because you might be able to build it completely from somewhere else, and actually it doesn't take you long to do that, and you need it to be fast, that might be a good option. But most people actually care about the data in their database, so Uh we'll leave it alone. Synchronous commit is something that potentially you could switch off to get similar performance benefits. You don't risk corruption, but you do risk losing data. So it's still not something that's particularly a good idea but it's slightly less scary. And auto vacuum. So the documentation describes autovacuum as optional but highly recommended. So autovacuum will keep an eye on your tables

and if you've got um too many if you've had a large number of changes then it will either vacuum or analyse the tables or both to make sure that you're getting rid of the bloat and that you've got up-to-date statistics. Sometimes people look at their database and they see these auto-vacuum processes that are using resources and they don't like that. So they just go in and switch autovacuum off. So, you know, they they think, aha, well that's what's uh that's what's slowing the database down, so I'll switch it off and everything will be fine. They usually regret their life choices sometime later. Unless you've got really clever processes in place. That are going to make sure that you're vacuuming your tables, all of them, on a regular basis.

Just leave auto-vacuum to do its thing. There are parameters you can use. To make sure that autovacuum is nicely tuned. So if you're finding that it's causing performance issues, there are things you can do, but in general, just leave it alone to do its thing Okay, and you can set uh log autovacuum in duration to zero, and then you'll see in your logs when auto vacuum is happening, what it's doing, and how long it's taking. So this I won't talk through that, that's just so that you can see a summary of the different parameters that I talked about. So three to do with connections and sessions, three to do with memory. A couple to do with logging, some to do with wall and checkpoints, a couple to do with query tuning, and a couple just to leave alone.

Just waiting for photos. Okay. And then this is the lazy. I mean sorry, the if you don't have time to go through all of those 13 parameters, these are the top ones that I would say just start with these: shared buffers, work memory, maintenance work memory. Wall buffers and effective cache size. So mainly the things that are to do with memory and a little bit to do with your write-ahead log and checkpointing. And then I think I'm out of time, is that yep, so I will just leave the conclusions up there. The takeaway is Postgres does, it really does just work, but you might want to look at just a few of those parameters. Thank you very much.