 one last time before we get started, which is we are going through all of Postgres' many, many scores of configuration settings, which means there will be a lot of text on screen, which means that you want to follow along one of two ways. One is you can move to the front of the room. The second is all of this material is in a Git repo, which you will want anyway, so you can take it home with you, and you can download that. My annotations and comments and recommendations are available as a Postgres database dump in either PgDump or for people using earlier versions of Postgres' N10 SQL format. It's available as a massive CSV file that you can load up in your favorite spreadsheet, and then I have my recommended configuration files, which we will be going over in this tutorial in that repo. So if you download the repo, then you will be safe from the blurry projector. So that is again, github.jbergisanitated.com, which we are about to move on to the next screen, but that will bring up the URL again. So welcome everybody. Welcome to GUX, a three-hour tour. I'm your skipper. We're going for a three-hour tour around the bay, and we'll hopefully not end up stranded on a tropical island for 11 years. But we'll see. My name is Josh Berkus. Actually, I work for Red Hat, and I work on Kubernetes these days, but for many years, depending on how you count it somewhere between 6 and 14, I tuned Postgres' QL for a living, which is a pretty good living, and I'm actually very happy to say it is actually less of a good living now, because a lot more of the settings and a lot more of Postgres' performance out of the box is becoming what you would want out of the box, which was a very happy thing to have happen, even if it meant, because it meant I got to move on to a different job, so the, which is awesome, but we're going to go over here, which is just because a lot of the defaults are better, doesn't mean that you can leave the configuration alone entirely, although a lot of people do just that, and in case you are interested in tinkering the Postgres' QL configuration, or at least knowing what's involved, you're in the right place. So we're going to talk about docs, understanding them, I talk about a little bit of what changed recently, go over how you manage Postgres' QL configurations, then show you sort of a simple basic configuration, and then we will go through as many of the configuration settings as we have time for, starting with the 20 or so that are the most frequently touched in my experience, or should be the most frequently touched, following with sort of the next group of 20 and then the whole rest of them. There is a whole bunch of related stuff in terms of Postgres' QL configuration to the postgresql.com and it's related settings, such as hostspaceaccess.conf, recovery.conf, pgident.conf, settings for the libpq client, psql setting commands, and system functions, and we are not doing any of that, because we have plenty to do just with the general configuration settings by themselves. What's a gox? First of all, who knew before they walked in the room that it was supposed to be pronounced gox? Because first of all, one of the things that a lot of these people do is an acronym GUC, lowercase s. The s is actually part of the acronym. It stands for Grand Unified Configuration Settings. Back in Postgres 6.5 days, Peter Eisenkraut took the five different configuration files we had scattered around the place, consolidated them all into a single text file and a single C file, which was there after called the GUC, the grand, the gox file. This is where you set all of the settings that are going to control sort of whole database, whole database server behavior. Again, accepting some of the things that we're not going to cover like access control, which is handled through HBA, and part of your replication configuration, which is still in a separate file, although really hopefully in Postgres 11 that will stop being a separate file. So I will say two things that might surprise you. Number one, tuning the configuration file is seldom your best way to improve performance in the database. You will get far more performance bang for the troubleshooting hour by fixing your queries and fixing your application request interaction with the database, because the most speeding up you can get of a particular query is to eliminate it entirely. So this is actually not your first round. This is honestly what you do next after you have already been through your application and done a reasonable job of tuning your application behavior. If you're looking at this from performance perspective, not all of the settings we're going to be going over here are for performance. There are some there for security, some are for reliability, some that are for logging, and those things you're probably going to need to change anyway. But really what we have is 269 as of Postgres 10 switches and levers for controlling database behavior. That's a few. A little alarming to most people, but it doesn't need to be alarming. We make it more alarming than it needs to be, and here's the second thing that might be a violation of conventional wisdom, which is when you install Postgres QL, depending on how you install it, you are generally given a massive Postgres.conf file that is, I don't know, 40, 50 pages long. First thing to do, delete that file. That contains every configuration setting there is. You are highly unlikely to ever touch like 200 of those settings. And as a result, having a file that has 200 settings commented out in order to get the 10 or 20 or 30 settings that you actually do anything with is just adding a mental burden you don't need. So you actually do need to save a handful of lines from that file, which is the things that get set at a NITDB time based on calculations or based on, say, file locations. So save anything that's not commented out in that file. Delete the rest. And start over with a fresh file. And I'm going to give you an example of a fresh file based on the things that most people need to adjust. And don't worry about writing this down now. We will go over it later in detail. And if you downloaded the GitHub repo, if you do it in the future, you will actually have this in text file format. But first, let's talk about what GUX are and how they work and how you set them. 6, 7, 8. 6, 7, 8. Who do we appreciate? GUX, GUX, GUX. Let's work better in Chicago. We have the hockey team. So 6, 7, 8. What do we have here? Well, there's six what are known as context. This is the unified configuration setting. So it's not configuration setting for one particular thing or one particular set of behaviors, but really for everything. And what that means is that you can't necessarily change these configuration settings at the same time for all settings. And so these, what we call contacts, indicate when you are allowed to change that setting. The vast majority of the settings we run here through GUX are what is known as session level context, which could also be called user context. And these are settings that can be changed at any time, including in a user's individual session. So a user can individually say, good example of that is obviously time zone, right? Is the user can say, hey, I want to change my time zone to Malawi, because I want to see things in that time zone. And they can do that at any time in the middle of the session. So most settings are actually session settings. A second sort of step up from there in terms of restrictiveness are what are known as super user settings. So super user settings can be changed at runtime in a PSQL session, but only by the super user. Only by somebody with super user that is sort of root permissions on the database. And super user is a specific thing where it says, older role, you know, Josh, with super user. The Postgres user in most cases is the original super user for the database installation. The next one is one that you see as reload context, which is actually abbreviated in the PG settings file as SIGUP. And that goes to Linux process management or Unix process management where you have to send a signal to the back end processes to tell them to reread the configuration file. And so what this says is you need to change the setting in a file, in a configuration file, and then you need to signal all the Postgres back ends to reread that file. Now, in the old days the way we used to do that is we would go on and we would use a PS command to send the HUP signal to the back end. Now a much better way to do it is log into the database as the Postgres user and send the command PG Reload Conf. Easier to remember, a lot easier to do via remote connection, and tell it to reload its configuration file. It is important to know that we are signaling back ends, so all of the various existing Postgres connections will not see that configuration change simultaneously. They are waiting to receive a message. And if some of them are super busy with a workload, they won't see it for a while. The next one up in terms of restrictiveness is restart. These are settings that can only be changed with a full restart of the database server. An example is static memory allocations. You're telling Postgres how much dedicated memory it has, how much memory it has for the auto vacuum daemon, other things that are allocated statically. Those require restarting the Postgres server, which obviously means a downtime for your application. So hopefully you want to limit changes to those as much as possible for that reason. So those are our six contexts. So here's our sort of reloading things, right? Like I said, the old way is use kill to send a signal on the command line. It still works, fine, we accept that. But much better ways are from a super user session you do pgreloadconf, or if you need to do something on the command line because it is part of a script on your server, then you send pgcontrol reload, which will also do the same thing. And those are better methods because among other things, those two methods reload and pgreloadconf will actually fail if they don't manage to signal the database server. Whereas the kill command will just happily return and you won't necessarily know what happened. So to reload it. So the two remaining contexts. One is backend. So these are generally for developer slash debugging settings. A lot of them require certain things to have been compiled into Postgres in advance that you don't necessarily, that aren't necessarily part of default Postgres packages, like log monitoring or something else. Some of them have to be set before you start Postgres. Some of them only work in single user mode, which is a mode for troubleshooting a Postgres server that won't start correctly. And I'll be showing you some of those, but hopefully you will never actually use these developer. Hopefully you will either use these developer settings or you won't use them. Hopefully you will use them because you will start contributing to Postgres QL and you will use these for troubleshooting new patches. Hopefully you won't is the other reason to use these developer settings is because you have a corrupt database and you're trying to recover it. And then finally internal. So the internal context is stuff that is not changeable. It is actually a setting that is supplying lead only information about something about either how Postgres was compiled or how it was installed. So a good example of that is the log file, the transaction log incremental size. Default to 16 megabytes, but you can actually change it. And that has to be compiled into Postgres to change it. So that's information only, but because some people are actually looking at changing that it's information that might be different from installation to installation and for that reason it's worth checking. Now we are to our 7. And this is the 7 different places that you can change configuration settings. Yes, actually 7. Because in the world of Postgres we don't ever want to... We never eliminate anywhere where you could tweak a knob. We just add new places. So, obvious one, PostgresHill.conf file. What almost everybody starts with. What a lot of people stick with pretty much forever. By the way, remember myself for just a moment. I forgot to mention on the table contest. We will be taking a 15 minute break at 11 o'clock in case you were wondering when you can get a bio break. So we'll take a 15 minute break at 11 o'clock then resume at 11.15 and finish the rest of the tutorial. So this is what everybody starts with. This is what people may work with for their entire time managing Postgres. It's a single configuration file that controls all of Postgres. It's just a file. You can do anything with it. You can do it with other files. It can be relocated. It can be sim linked. There's actually a couple of ways to relocate it in the configuration settings itself. And you can do other things with it. It's the primary way that a lot of people change these configuration settings. Particularly, all of the settings that you want to be built into Postgres at startup time, like login configuration, like memory allocation, like sensible defaults for a whole bunch of things. Now, like I said, the default one you get is this giant list of every setting there is. My opinion is that's not helpful because in order to change the handful of settings you actually want to change, you have to wade through like literally dozens of screens of stuff that you're not going to touch. So again, I'm going to show you starting with a fresh file. Now, here's a couple of important things to know about loading these configuration files. Number one, you know, use bash commenting to comment out things. Anything that's commented out doesn't take effect. And it operates on last-in links. So if you have the same configuration settings at multiple times, whichever one is at the bottom of the file is going to be what your actual setting is. And to make this even more complicated, you can add include files. So you can actually have Postgres.conf include another file. And then it matters which order you are including those files in because, again, we're still last-in-links. So you do want to keep track of that. And if you end up troubleshooting a complicated Postgres configuration, it will probably be because of that last-in-links problem. Now, a step up from that and something I recommend if you are going to use config files to manage your Postgres configuration is to actually not have a single file, but take advantage of the feature that allows you to have a configuration directory. So you do include, if exists, the directory. And then you drop multiple configuration files into the directory. So you have one that configures, say, performance, another one that configures logging, and another one configures some reliability stuff, and another one configures replication to separate files. One of the reasons to do this is, for example, one of the things you may frequently change with Postgres is you'll want to swap out logging configurations because, say, you want to do a 12-hour intensive performance monitoring of Postgres. So you want to swap in a more intensive logging configuration and then swap it back out at the end of 12 hours. That is easier to do, particularly through a configuration management system, if that logging configuration is a separate file. Now, these files get loaded in alphanumeric ASCII order within that directory. So do keep that in mind if you're going to have files that deliberately clobber each other's configuration settings, that you still need to do last and wins. What it really is, if you're going to be managing your Postgres configuration using Puppet or Chef or Ansible or whatever, try using the configuration directory with the separate files. It will actually make your life easier. We're not limited to changing configuration stuff using configuration files, though. So the first way that we can actually do this on the SQL command line is set and show. So show tells us what a configuration is. So show tells us what a configuration setting is. And set, there's no worm memory, because worms notoriously have no memory at all. VM is, we can set it in a session. Now, this obviously only works for session-level parameters, or for super-users-level parameters if you are the super-user. But for a lot of things, this makes sense whether you're doing it interactively or whether you're doing it in a script with that you can actually go ahead and set those session-level parameters for things like time zone, for example. Because one of the things I strongly recommend is everybody's Postgres server should be running in the UTC time zone and that if people need individual different time zones that should be set by the application. And you should be using time zone TZ for all of your variable storage, for all of your column storage of timestamps so that all of this works. Now, one of the problems with set and show is that these are actually SQL primitives and the parameters that they take are not strings. These are identifiers. Which means that if you want to actually set them dynamically as part of the script you have to do the annoying string concatenation rather than being able to pass them around those proper database parameters. So in order to enable that, we have an alternate version of set and show which are a set of functions, set, config, and current setting. And those actually take a string. And these are the best things to incorporate in a script where you're going to be supplying that value dynamically because then you can do it as a proper Postgres parameter. So here's another thing that ships with PostgresQL. And this is the PG Settings table. Mostly the work of Joe Conway who was actually in here earlier talking to me before the session started. And PG Settings is a giant administrative what we call a pseudo table as in it looks like a table but it's not actually a table. So you can see we've got a whole bunch of information there about every setting. And if you actually look at PG Settings we've got the information, the configuration name, what the current setting is, what its unit is, if it's relevant, the category, a short description of it which is generally the first line of whatever is about that setting in the documentation. The extra description is supposed to be populated for the documentation but it isn't for most settings. It's just kind of a to-do context for when you can reset it, what the variable type is, what the source is, what the range for that setting is, etc. So you can get all that from PG Settings. Now what a lot of people don't know is that if you can actually, if you have permissions which generally requires you to be the super user unfortunately, you can actually change settings by updating PG Settings as a table and that calls Set Config under the hood. There's a whole other sort of level to set settings on but I feel like people really don't make enough use of which is for session level settings you can actually set those as the property of certain database objects. Most frequently as properties of either databases within your Postgres installation or most frequently, most usefully roles as in users. So like the biggest reason to actually set this is for example, imagining a database where you have a consolidated database where you're doing both supporting web application and running analytics reports against it. You might want to have a separate web user that has a very low memory allocation because you're expecting to have a whole lot of connections for the web user and an analytics user that will only have one connection but a much larger memory allocation so they can run big analytics reports. The best way to handle this in terms of maintenance is to actually have two separate login roles for those and set that memory allocation as a property of the login role. The other thing particularly that you often do this is for a variable called search path which governs how you find objects in the list of Postgres schema. As a matter of fact, managing search path for people who are using multiple Postgres schema became such a thing that a few releases ago we added a feature to specifically allow you to set a setting on a role in a particular database because obviously if you have multiple databases in your Postgres installation the same search path is not going to make sense in all of those different databases because your list of schema folders will be different. And this is really something for certain and I'll be talking about certain settings later on that really should be set only this way because they don't make a lot of sense to set them globally. The other reason why you would set things on a particular role is obviously for localization. If you're geographically distributed one of the ways you can manage is hey, what time zone, what collation are people going to see things in linguistically, et cetera is to actually manage these properties of a role. So for example, Brazilian user actually has a separate login role in the same group as British user. The other place where you want to set parameters, very importantly, how many people are using Postgres stored procedures? Okay, how many people have some security definer stored procedures as in stored procedures that deliberately escalate privileges? If you are doing that, you need to set a bunch of configuration parameters attached to that stored procedure because otherwise someone can use that security definer feature as a ready-made security hole by overriding things it depends on. Particularly you absolutely need to set search path because that's the main way to do that because for example, if the user is able to override the meaning of the equal sign they can have that stored procedure do pretty much anything they want. And this is one of those ones that I used to say is something you shouldn't really be doing at runtime but a change in how people deploy applications has changed that which is you can set your configuration settings by setting them in the command line with Postgres. You start up Postgres and you set a bunch of settings in the command line using there. A little bit better to set the PG options environment variable which will have all of those things in there with the PG options and those get supplied to Postgres in the command line. Now this used to be something that would pretty much only made sense for automated testing. I mean, otherwise this facility dates back to when we didn't have a configuration file. Except that now people are running Postgres in containers and it is really easy to supply a bunch of environment variables to a container within a container orchestration environment and so this starts becoming a much more attractive way to set your settings. Then the actual management of your settings takes place on the container orchestration master which then supplies a bunch of environment variables to the individual Postgres server as it starts up through the PG options variable and as a result you can manage settings on that without needing to reach into the container and try to change the file. Particularly if you're using an orchestration manager like Swarm which doesn't really have an analogous concept to config maps with Kubernetes apps. So this is now a thing again and a good option for that kind of environment. And then the final one, this is my favorite one because it really sort of changed the game for how you manage Postgres settings which is the ability to manage persistent settings on the command line, alter system set. What alter system set says is first of all, require super user permissions and you log in through the SQL thing and you say, hey, I want you to change the setting but I also want you to write it to a file so it will persist across Postgres restarts. So you can actually change things overall that way. So for example, so we can go ahead and change, I can change the default time zone for the database through alter system set. And then this actually gets written to a file in the PG data directory regardless of where your other config files are located called PG.Postgres.auto.conf thread before some things and after some others and I actually strongly recommend this for, well, it depends. It depends on your infrastructure setup. Again, if you are in a new cloud stack environment, for example, containerized environment or open stack environment or somewhere else where Postgres is being regarded with immutable infrastructure principles, then this is a much better way to manage your settings but this does mean that you have to have the settings under management somewhere that your central orchestrator has access to because the settings have to be under management somewhere. And if you just change them dynamically in the digital Postgres instance then you have difficulty recreating that if you have to recreate the server. But this provides you with a more programmatic way to manage a lot of settings. Now, that's seven different places that you can manage your settings and you can imagine what happens if you have a large distributed development team and everybody has changed their setting a different way. A couple of Postgres versions ago there was really no choice other than sleuthing for a long time and changing settings in various places until you actually saw what stuck. But now, there is a view called PG file settings and this will show you every setting that has been changed from the default along with where it was changed and if the place it was changed is a file what line of the file which can be really important because of last in-links. You can say, hey, I keep changing auto vacuum workmen and nothing happens. It turns out it was a much lower line on the same file. We can see here, we've got this and let's see if I can get some of the ones that are not out of Postgres.conf. So for example, the one that I just changed time zone is that's now in Postgres.auto.conf. See, it's coming from a different place. So this is your way to avoid having these multiple locations become a total nightmare. So we have eight basic types of values that settings can be set to in terms of the variable types. These will not exactly match up with what PG Settings tells you because PG Settings increments some values in different ways. So the obvious and the simplest one is Boolean which is settings that can be on or off. Then there are settings that only take whole numbers because these represent something outside the database. For example, account of connections will take whole numbers, which are integers. There are floats and these take real decimal numbers and these are often for performance tuning settings which will take real numbers, etc. Now, there's a whole set that are memory and disk size. These will appear built in PG Settings as if they were integers usually. But the important thing to know is that you can actually attach a unit size to these to make them much easier to understand and remember for setting them. So for example, you can set work mem in terms of megabytes which is easier to remember than to remember that work mem is in terms of 8KB pages and that you're actually setting it to a number of 8KB pages and work that calculation out on paper. Unfortunately, if you are an RDS or some other managed systems, they only give you access to the raw values and you still have to actually look up that calculation. But if you're setting it on bare metal postcards, it's not an issue. Time. There are a number of different variables that are set in terms of time. These time values, again, are usually show up in PG Settings as an integer, but I've called them out and annotated as actual time settings. They take reasonable values as for seconds, min for minutes, HR for hours. They don't all have the same minimum-maximum ranges because some of them have a base unit of milliseconds and others are a base unit of hours or minutes, but you can set them using those and again, better for self-documentation. Strings are obvious. These are actually some strings, often a file path or a file name or an identifier. There are a number of string values that are limited to a specific list of values. These are enums, as it were, and actually do show up in PG Settings that way. There's a specific list of values, like wall level, which can be one of three specific things. Which, actually, a hot standby is not one. I forgot to update the slide. We changed that set of values in Postgres 9.5. Then there are lists. Now, lists, again, are saved as a string, comma-separated. For example, search path, which has a list of schemas. Then those get parsed inside Postgres into a list of things. We've gone through the different places and how to set things. Does anybody have any quick questions before we move into other things that have changed recently? Yes. The question was, postgresauto.com, is that persistent between restarts or is it only active during user session? The answer is persistent between restarts. If you wanted something that was only persistent during user session, then you just do the regular set or set config. That only exists in that individual user set. Not only is it not persistent, it does not affect other user sessions. But alter system set and creating postgres.auto.conf are persistent. You can, in fact, even edit postgres.auto.conf by hand, but make sure you get the formatting right. Because you get the formatting wrong, then postgres will refuse to start. Any other questions? Okay. A few things have changed. One of the things is I actually have not done this particular tutorial since I think Postgres 9.2. I was updating it last week. A lot of things have changed. For anybody who is on somewhere in the 9 series and is looking at Postgres 10, how many people are running a Postgres 9.3? 9.4? 9.5? 9.2? 9.0. Anybody running 9.0? It's out of support, but I think it's still supported by Red Hat. 9.1. You kept moving your hand. What version are you running? 9.6. I forgot we did it in 9.6, didn't we? Yeah, so 9.6, yeah. But if you look across the 9 series, we actually eliminated 23 configuration settings, which is awesome, right? Because every configuration setting is just a deferred design decision. And so limiting 23 configuration settings makes things easier for the user. And it would be wonderful to submit when we had 23 less configuration settings, but unfortunately we eliminated 23 configuration settings and then we added 69 new ones. Now, to be fair, an awful lot of these 69 new ones specifically have to do with new features, such as configuring replication and configuring in the three different forms of replication, configuring parallel query, configuring some of the improved maintenance stuff and a bunch of other things that were added to Postgres' features. But nevertheless means here is another group of settings you might potentially need to know. Now, these break down into a few specific areas. One of them is this whole, and this is a very new set of settings to configure memory flushing behavior on sort of a fine-tuned basis in terms of when things get flushed from memory to storage. I honestly don't have any recommendations on how to set these. These are new. I haven't seen anything in the way of, you know, documented tested performance papers published on them. So if you're interested in them, then follow the Postgres performance mailing list. Identification. We've added more places for you to stick an arbitrary ID for that individual Postgres instance, which is really frigging useful if you are running multiple Postgres' on a server. And people are like, who's going to run multiple Postgres' on a server? Well, a lot of us, including me, are doing container orchestration now, which means you are running multiple Postgres' on that server because each one's in a container. And so it's really useful to be able to identify those. One of the practices I have is in my container I set these things to the name of the container. And then I know that Postgres is associated with that container. We have overhauled SSL config because quite bluntly, the original SSL config in Postgres was pretty bad. For one thing, it made a lot of assumptions. Your SSL key files had to be present in the Postgres directory and you couldn't move them, which was kind of a problem, so now you can actually stick them wherever you want to. But it does mean we have a bunch of new performance and a bunch of new configuration things for SSL. We added a bunch of timeouts and resource limits, and I'll go over these more later on because these are actually awesome, and I recommend that you make use of them. And then, of course, all of the settings for the different kinds of replication, including logical replication and synchronous replication and all of the other things that have been added in the last seven versions. One of the things most recently in Postgres 10 is wall compression, the ability to compress the right-hand log in order to improve IO performance. And that comes with its own couple of configuration variables. Parallel query comes with a bunch of configuration variables and will have more in the future as the team working on that paralyzes more things. Every time they paralyze a new query operation, they'll give you the opportunity to turn that off. Now, not all of this is adding to your mental burden. One of my favorite changes, and this was from 9-5, either 9-4 or 9-5, where we actually made things a lot simpler. One of the things that used to be on my list of things everyone has to set is the setting called checkpoint segments, which define the size of your transaction logs on disk. Well, the maximum size. Well, the maximum size according to a really complicated calculation that required at least two different sets of parentheses in order to work correctly, which meant nobody really understood how to set it. I mean, experts did, but regular users didn't. But you had to set it because the defaults were way too small. So, got rid of that. Hikey added a change where you simply give Postgres parameters where you tell the biggest the transaction log can be and the smallest that it can be, and then Postgres dynamically resizes the transaction log according to the level of activity on the database. And that maximum size is in megabytes or gigabytes. It's really simple. Plus, importantly, the defaults work for 95% of users. So, something now that you're not going to touch, that you always had to touch before and you don't have to touch now. So, that's awesome. And I wish we had more changes like that. So, any questions about new things before we start getting into the configuration settings themselves? Again, I showed you this before. Like I said, 269 settings, of which I will go over a whole bunch. But most of them you will never touch. These are the 19 or so settings, and I say or so because some of them are actually groups of related settings, that you are the most likely to need to touch or that I recommend that you touch in terms of changing them to get a reasonable Postgres configuration. Fortunately, I have recommendations on how to set all of these, which we'll be going over. So, if for anybody who checked out the Git repo, you can actually follow along with this by opening the file postgresql.10.simple.conf. And this is an example simple configuration file that has my recommendations for settings you need to touch. When your installer generates that default postgresql.conf, you will need to drop in those file location settings that it generates that tells it where the Postgres files are to add it to the file to actually have this be a working configuration file because those settings are required for Postgres to know where its files are. But these are all the settings that you would actually manually change, are most likely to manually change. So I will go over this. Now, there is a whole bunch of settings in Postgres that cannot be set automatically because we need to set them according to how much RAM you have available on your server for Postgres. Now, anybody hazard a guess as to why we can't do that automatically? Why can't we just say, hey, you've got 32 gigabytes of RAM on your server. I'm just going to calculate all those. Any guesses? Yes. The server may not be dedicated to Postgres. Postgres may be only one of several things running on the server. There's a second reason as well. Any guesses? The second reason is actually determining cross-platform and cross-environment when you include things like VMs and container environments. How much memory you have available turns out not to be a trivial thing. Postgres is available currently on eight different platforms. A lot more than that if you include the various versions of Windows. And saying how much RAM does the system have turns out to actually not be an easy question to answer in a simple piece of code. That's two reasons. As a result, we require you, the user, to tinker with those settings to tell Postgres how much memory it has. If you look at this recommendations file, it uses an abbreviation called AvRAM, which means available RAM, which means the amount of RAM that Postgres should have available for it to use. If it's a dedicated bare metal Postgres server, then that's really simple. It's all the RAM on the machine. But if Postgres is running alongside of the things, if it's in a VM, if it's on a container, something like that, this needs to be the RAM that's actually available for Postgres. Now, before we get into those individual settings, let's get into connection settings, because obviously you're not going anywhere with Postgres if you can't connect to it. And the first thing, the thing that almost everybody ends up touching almost immediately, even if they never change a single other Postgres setting, is listen addresses. If you're installing Postgres in bare metal, it installs in a secure configuration, which means that Postgres only answers local connections in the local machine, which is almost certainly not what you want it to do. So in addition to altering pghba.conf for the access control list, you also need to go in and take this listen addresses setting and set it to something else. Most frequently, star, which just says listen on all interfaces. However, if you have a machine that has multiple networks available to it and those networks are not all equally secure, then you want to give it specific networks it should be listening on. For example, an environment where that is always the case is Amazon AWS, right? Every AWS instance has a public IP address and a VPC IP address. And you generally want to set Postgres to listen only on the VPC address, not on the public address, because if your Postgres port is on the public internet, someone will find a way to attack it. The second thing that you often need to set is max connections. The max connections is how many connections will Postgres take before it stops answering them? And unfortunately, setting this is fairly application workload dependent. As in, you have to have an idea of what's reasonable for your application workload because a public web application that's using Rails or another framework where each application back-end tends to spin up a database connection whether it's going to use it or not is going to require a lot more Postgres connections and be able to make use of a lot more Postgres connections than say a data warehousing analytics application where each connection represents a data pipeline that's pumping through gigabytes of depth. So now, that being said, each Postgres connection, Postgres is a multi-process database like Oracle is. And as a result, each connection represents a process on the host server or a thread, a full thread on Windows. And that means that there is a fair amount of memory and other resources devoted to each connection. So if you find to satisfy your application requirements, that you are raising max connections up above a few hundred, then you should probably look at some kind of intermediate connection pooling. Because you're going to find that your Postgres server or VM or whatever is spending all of its resources dealing with managing those connection processes. SSL, turn it on. Not turned out by default in a lot of installations because it requires you to install a separate SSL package. But you should do that and you should install SSL. None of us was at crunchy outside soft interior as a poor security approach. And as a result, you should really be planning on securing connections. Particularly anything as important as a database connection, which is possibly containing information that you really don't want to be out in the public. So you want to turn SSL on, so we use SSL connections with Postgres. Pretty easy except that obviously you need SSL configuration in the server. And depending on what level of auto configuration your installer does, you may need to tell it where to find those SSL certificates. And you may need to change permissions and on Linux change SE Linux settings for those files so that Postgres can access them. So now we're getting into some of the performance configuration. Where we're talking about this memory configuration I talked about earlier. You know, as in you need to set some of these things. So shared buffers. Shared buffers is the amount of memory that Postgres statically allocates via M-MAP as Postgres' dedicated cache and sort of work area. That cache is managed using a frequency algorithm. And as a result, you don't actually want this to be the majority of RAM. And this is one of the places where configuring Postgres is very different from configuring Oracle. Oracle has a multi-level cache that you actually want it to. Like you're setting up an Oracle server, you set this to like 90% of RAM. For Postgres, this needs to be a minority of RAM. And minority I mean about a quarter with a limit of about eight gigabytes on high RAM machines. Just because there's a lot of, and if you're going to get more into should that limit really be eight gigabytes. Again, I say subscribe to PostgreSQL performance mailing list. There's a lot of sort of argument back and forth about this is how much RAM can Postgres really make use of in this dedicated cache. But it can reliably make use of two or four eight gigabytes above that maybe counterproductive. So set this to a minority. Default is I believe 128 megabytes, which is actually kind of on the small side for even machines you might use on even Amazon instances, which tend to come more in the one gig, one, two, four gigabyte flavor these days. So you want to raise this actually a little bit for the sort of most efficient operation of caching and working with data in Postgres. The way that you can actually tell interactively whether or not you should change this is by looking at Postgres' cache hit ratios which you can get from some of the Postgres system tables where you can actually see how often are we hitting cache for commonly accessed tables and commonly accessed indexes versus how often are we having to go out to the file system cache for them. But you have to use your understanding of the database when you do that because if the database itself is 25 times the size of RAM, your cache hit ratio is going to be poor no matter what you do. And increasing the size of shared buffers won't actually help you there. That's just if your database is a fraction of the size of RAM that cache hit ratio should be really high and if not consider raising shared buffers. Next setting work memory. So work memory is how much RAM do I have for an individual operation in this session? It is not an allocation, it's a limit. And it is non-shared. Not only is it not shared between sessions, but it is in fact not shared between operations. So if you have a complex query that does three simultaneous sorts, it could use three times whatever this work memory limit is. Which is particularly important to keep track of when you are doing parallel query. So this is a limit and the reason why it's there is to prevent you from severely from having, hey I'm going to have a hundred simultaneous connections, each of which is going to use 128 gigabytes of RAM on my four gigabyte Postgres AWS instance. Doesn't work out well. We would rather start cutting things off than to over allocate the hack out of the server. So in order to calculate this you need to know actually figure out what's a rational setting for this before you start responding interactively to things is you need to have an idea not just how much RAM you have but what kind of workload you're running in this database server. Because if you have a sort of interactive application workload like a web application workload then that has hundreds of connections then you can be setting this really low. Because first of all most of its queries should be primary key lookups which require no work memory at all. And second you have a whole bunch of current connections so allocating a bunch of work memory is kind of a problem. By the way one of the other ways of allocating a lot of work memory is a problem. If you are using persistent connections in your connection pooling that work memory allocation never goes away because the process on the Postgres side never goes away. So you will see over time the actual RAM consumed by each of your Postgres backends will go up to roughly double whatever the work mem allocation is you know for that and that can be a reason to actually rotate your persistent connections. So for a web application a simple way to do this is to actually take your total amount of RAM and divide it by max connections assuming that that max connections is a maximum that you expect to actually be enforced. In other words if you set max connections to 200 because you expect that sometimes you might actually have 200 connections then yes divide it out. If you set max connections to a thousand because your boss never wanted to get a connection refuse message but you know 99% of the time you only have 40 connections then you might actually divide this out by something lower. The other thing is work memory is actually allocated by doubling so any remainder you have left over from a power of 2 is not used so I like to remind myself of that just by always setting this allocation in powers of 2. It doesn't hurt anything if you don't it's just that if you set it to like 16 megabytes nothing over 256 is going to be used. Now on the other side of things if you are running an analytics application you're going to want to actually allocate a bunch of work memory so you can do big sorts and merges and aggregations and other operations require a lot of work memory but in that case you're actually expecting to have a lot fewer connections. Now where this could complicate is if you have a mixed use application remember I said about setting this as a property of the individual login role and this is when you want to do that you want to say hey my analytics application is going to have work memory set to 256 megabytes but my user facing application is going to have it set to 2 megabytes and you would do that by doing an alter role. Maintenance work memory. So talking about maintenance work memory this is the memory that is used for two things one is when you manually run maintenance commands such as vacuum or analyze and also when you do bulk loads for honestly largely historical reason bulk loads particularly bulk loads of indexes use maintenance work memory and not work memory. Now since this is for the interactive manual one I generally set it to roughly about one-eighth of RAM because I'm unlikely to be doing multiple manual maintenance activities at the same time. Even bulk loads tend to be serialized so they're not using multiple maintenance memory in the same you know session at the same time. Keeping in mind that there's been a number of performance tests that show that setting maintenance work memory above 2 gigabytes doesn't really help you any and may in fact be counterproductive. I don't know if that's been updated recently the last time I saw somebody actually test that was for Postgres 9.5 I think or debating raising the limit on maximum maintenance work memory but at least at that time setting it more than 2 gigabytes and certainly if you're doing it out of the box don't set it more than 2 gigabytes most of the time lower values than that but you know you want that vacuum to complete quickly and you don't want to complete more a manual vacuum particularly generally if you are doing something like a manual vacuum or a bulk load it's really important for you to complete quickly and so you want to give it all the memory it needs to do that. Now one of the reasons why I said hey that's wrong one of the reasons why I said manual maintenance is that a couple versions of Postgres ago we added a separate variable auto vacuum work memory where you can actually give auto vacuum a different memory allocation than you have for manual maintenance procedures now it still defaults to using maintenance work memory if you see that this value is set to negative 1 the default then it is the same as maintenance work memory but you don't want it to be the same because auto vacuum now uses multiple background workers default of up to 3 if you have a super busy database with a thousand tables you're actually going to want to raise the number of auto vacuum workers and as a result you want this allocation of memory for the auto vacuum to be lower than you would have it for manual maintenance procedures because you really want auto vacuum to be a background activity and you don't want it to be pushing a whole bunch of stuff out of the file system cache every time it runs so on a default system we're using 3 auto vacuum workers and most of the time only one is busy at any given time I set this to a 16th of RAM believe it or not you're actually going to want to set it and if you raise the number of auto vacuum workers you're going to need to set this lower effective cache size now this is different because everything else I've talked about from memory up until now has actually been either an allocation like shared buffers or a hard limit like workmen the effective cache size is just an advisory to the query planner it tells the query planner how much memory you have available for file system caching on the system and Postgres wants to know this because I mentioned that Postgres' dedicated cache is only a frequency cache what we rely on for our second level cache is the file system cache you know from our Linux file system or Windows file system or whatever so we rely on that as our sort of bulk larger cache but Postgres needs to know in a very rough estimate how big that is so it can guess whether or not the database object you're trying to access is likely to already be in the file system cache or whether Postgres is going to have to go out to long term storage so the easy thing for this is though if you are setting shared buffers to about a quarter of RAM available RAM then you set this to three quarters of available RAM and again it's kind of as you know query planner advisors this is kind of an order of magnitude thing so you don't have to be precise about it you know as long as you're you know within the reasonable sort of factor of three of it it will advise the query planner in the correct way the default for this is four gigabytes by the way which is why a lot of people don't have to adjust it because most people running on RDS instances or smaller servers or shared servers or whatever the default works fine for them now random page cost as other advisory this is the one disk setting that I put in our top list of things to adjust this used to be one that we told you not to touch all the time because people would touch it as a way of clobbering the query planner which would result in as much undesirable behavior as desirable behavior that's not true anymore and the reason why it's not true is random page cost represents the ratio between the amount of time required to seek for an individual database page on disk versus scanning a larger file and finding that page now on spinning disk that ratio has stayed at a relatively consistent four to one through multiple generations of spinning disk technology increasingly people are not using spinning disk for their databases they're using SSDs they're using solid state arrays they're using high end sands with enormous RAM caches and for all of these seeks are much less expensive than they were for spinning disk and given that you actually in any of those environments which includes things like Amazon, EBS, solid state and other cloud storage you actually want to lower that random page cost like a lot to 1.5 compared to 4.0 it's still more expensive to do a seek but it's not much more expensive to do a seek and you will get the query planner when you do this the query planner will make better decisions about whether to use an index or a direct table scan whether to do an index operation in general or a full table operation when running queries so I'm going to pause just a moment and before I go on to some of the other top settings do we have any questions about the connections between disk settings I'm covering everything the question is can the SSL settings reference a pen bundle instead of broken out key and certificate files the answer is yes but I would actually have to look up exactly how you do that because you do have to do a weird thing when you're using pen bundles but there is a way to make it work and I just have to look it up you should lower it yes you're using the number of auto vacuum workers which is a setting you can do and again the case where you'd raise the number of auto vacuum workers I've seen this a lot a lot of people who are doing multi-tenant applications may have literally 100,000 tables in that case 3 auto vacuum workers is not going to cut it to keep up with maintaining cleaning all of your tables so in that case you might have 5 or 12 or 15 but the thing is you need to lower the amount of memory each has available or you will find those auto vacuum workers consuming the majority of RAM which will crowd your file system cache and anything else before I go on to some safety settings so safety settings so these are all about database reliability and data safety the first one is a hard one because it can only be set at a NITDB time that is when you initialize your database directory which means if you're already up and running in production it's going to be very hard to turn this one on unfortunately and this is called data checksums and the idea of data checksums is we add a checksum value to every database page so that we can find out if it has been corrupted in memory or on disk as soon as we read it back and the reason for this is obvious the reason you want to do this is hey memory corruption and disk corruption happens even if you had phenomenally perfect hardware the most expensive hardware in the world there's still things like cosmic rays cosmic ray bit flipping is actually a thing if you have enough machines and so stuff does get corrupted on disk it will happen to you and when it happens it's really useful to find out as soon as possible that something has been corrupted because one of the ways the corruption can express itself is it can be stealth corruption in which a query simply returns the wrong results and that is much worse than getting an error message in most circumstances so you want to use data checksums if you can now the obvious reason is not to use this number one is you're already in production and re-initializing the database server which would require a downtime based backup and restore to make it happen is just something you can't schedule doing so in that case just think of this for the next time you do a Postgres version upgrade and maybe when you do the version upgrade also turn data checksums on in the new version the second reason not to use it is it does add extra IO there is a significant amount of extra IO in terms of rights to the transaction log in order to support checksums because there is information that now has to be transaction log that would not have been if we weren't checksumming it plus some CPU for generating the checksums itself so there is a performance overhead and if you are running really close to the wire on performance then you might be dubious about turning checksums on but even that I'd ask you to reconsider because here is one of the problems the other corruption has happened database corruption can follow replication so saying hey I have lots of replicas so I am safe from discorruption of the database you are not safe because that corruption can get replicated depending on how it is expressed and then all of your databases are corrupted in which case you don't actually have a backup that helps you so turn data checksums on if there is any way for you to do so next one is that I honestly waited for my whole Postgres consulting career to get installed and of course it became a Postgres feature after I stopped working on Postgres I don't want transaction time out so open transactions in Postgres consume resources particularly they consume resources in two ways they pin things in versions and sometimes in memory in a way that prevents concurrent sessions from evicting them or in the case of auto vacuum cleaning them like if you have an old row version a deleted row that if you have a deleted row that is no longer visible to most users because it was deleted 15 minutes ago if you have a transaction that is more than 15 minutes old that row can still not be scrubbed if you have a long running database and that doesn't sound like a really bad thing until you consider something like queuing tables that might be deleting hundreds of thousands of rows a minute and none of those can be scrubbed so and it's one thing if the reason that we cannot manage this concurrent activity is because you have long running reports and other you know necessary work that takes a long time it's really stupid if the reason that you're pinning all of these resources is because some application some buggy application open to transaction and then went to sleep and it happens a lot like when I was a Postgres performance tuning consultant we had a bunch of scripts we would run that would look for idle and transactions and this was you know hour one of setting up our instrumentation at the customer was to set up this idle monitor because 75% of the time the client would have an idle and transaction problem they didn't know about and those depending on the rest of the workload on the database that can be a minor problem or it can be a devastating problem so you want this to be turned on that simply looks for abandoned transactions transactions where there haven't been any commands sent so this is the time from the last time that a command was sent in that transaction now you obviously don't want this to be zero because applications need some kind of think time but I would start with this at like half an hour and then look at cranking it down because really there aren't a lot of legitimate reasons for an application to need to think about something for a full minute but don't start there because I think you'll discover if you've never done this before, if you've said it's 30 minutes you'll discover there's a bunch of broken connections and one of the other things I discover is that buggy applications that abandoned transactions also seem to handle having their connection terminated poorly so you're going to have to do some cleanup on the refactoring on the application side once you turn this on but it'll be good refactoring it'll help you statement time out so here's the other thing and this is a little bit more complicated to set because you have to actually know your application workload well but here's the other thing sometimes queries get stuck for a variety of reasons they get stuck behind IO queuing they get stuck because a really terrible query plan where we're re-scanning a really big table 100 times they get stuck because of process management problems or something else that hangs up the query it happens some of the time not a lot of the time, well depending performance bad query plan problems when they do happen tend to happen on 100 connections but other problems it doesn't happen all of the time but the problem is that if it only happens 0.1% of the time depending on your query load that could still be a lot of queries and those long running statements consume a lot of resources because usually that's the reason why they're long running in the first place is they're consuming a lot of resources so if they are long running for bad reasons you actually want to kill them off and preferably you want to kill them off automatically and that's where statement time out comes in now there's two things about statement time out one is it requires you to figure out what's the longest running legitimate request I might have and then second you want to set this on a per application connection application basis because what is the longest running thing is again going to be a question of which application am I talking about because on the one hand your node.js web frontend because 98% primary key lookups a reasonable statement time out for that might in fact be two seconds whereas PGdump is going to need a statement time out measured in like a couple of hours because it's dumping the whole database so you're probably going to want to set this by login role in order to get a reasonable setting for each application but you might be surprised that actually setting this on a per because it nails that less than 1% of operations that are getting stuck and it's better for the application to retry those than to keep consuming resources temp file limit this is another safety one that distressingly few people know about I personally think we should have it on by default but I lost that argument so temp file limit says if any database operation needs more than this amount of space on disk terminated now why do we use things on disk well imagine we want to sort 12 gigabytes of depth and we've set work mem to be only 128 megabytes we're not allowed to do that sort in memory because we're trying to reasonably resource allocate our memory and so where do we do that sort, we do the sort on disk we do a tape sort on disk or a modified tape sort and we actually have the second kind of sort we now do but anyway but we do a disk sort of the data so that we can actually expand it but that disk sort is using up a significant amount of disk space in IO and the thing is that if a really bad query comes in somebody manages to mess with your search interface so they pass a search that is actually if executed sorting all 100% of the information in your database and I have seen it this can result in post-course attempting to use more temp file space than you actually have disk which then results in down time that's really irritating to recover from so you want to actually set here this can be a high limit but something you want to have some kind of limit here and the first thing to do the limit is look at your available disk space right, if you have 30 gigabytes of disk space available you should probably set this to some small fraction of that set this to like 5 gigabytes so a single operation is not going to use more than one sixth of your available disk space beyond that you have to sort of once again consider your workload analytics applications are going to require a higher temp file limit then interactive web applications or mobile applications that often are not doing any distorts at all and one of the ways that you can interactively monitor this is there is a setting called log temp files in the logging section that will tell you how much you're actually using so you can get a good idea of what's reasonable for a limit on this so questions about limits before we move on to replication no questions on limits on safety, okay okay, replication or archiving so for everybody who's using Postgres in production here are you using Postgres binary replication yes, no nobody's using Postgres binary replication the standard hot standby master-slave replication would be a little surprising wow, okay maybe I should have given a replication tutorial instead the um are people using continuous backup Postgres archiving at least, I hope okay, so all the rest of you all are okay with losing like a whole day worth of data apparently however often you take PG dumps well actually well nobody here using the new logical replication for anything I mean that's new, it's new in 10 it's awesome, but it is new so I'm not surprised nobody's using it the um so, I'm going to say if you have important business that in that database you should certainly have a replica or in our continuous backup archive or preferably both because if you cannot easily and quickly recover all of your database data from its source material then it's not something you want to do that's what we call the that's what we call a resume outage which is when the database crashes and you send out your resume because you know you can't recover it you had a question? I'm suggesting that the master have a continuous backup um, and yes I am suggesting that I'm in addition to the replicas um, and the reason why is um replicas are great for fast recovery and they're going to be a primary method of recovery but sometimes the event you have to recover from happened on the database level like somebody said drop table customers it's amazing like you can run in infrastructure where your replication lag due to bad network problems averages 15 minutes and the minute somebody sends drop table customers replication lag is 0 so your replicas won't help you recover from that kind of a mistake and that's why you want to have continuous backup ideally and obviously if you have a really small database and you can do PG dumps once an hour then that is an option for you and so the reason is that continuous backup or PG dumps is your only way to go back in time if you have to if there's a problem that happens not at the hardware and server level but at the inside the database level and that's why I'm suggesting you do both so there's some settings here um, and the um, and I actually give you and again like I said these replication settings like the logging settings kind of come in groups it only makes sense to set them with a combination of them together now one of the really nice things that somebody I think Hikey did in 9.6 was to adjust the defaults for a bunch of the replication settings um, two reasonable values so you no longer need to change things in order to turn replication on at all it's automatically on the moment that you allow replication connections in pghba.conf so that's nice however you actually probably do want to tweak a couple other things if you're using replication production so um, here is an example of just your basic binary asynchronous replication you know a master to an asynchronous replica which is 95% of all Postgres replication so one of the things that you might want to tweak there's a setting called well keep segments which says keep this much extra transaction log in case the replica needs it to catch up because what will happen is if the replica loses its connection to the master because of a network outage or something then it needs to catch up on transactions it might have missed and the way that it catches up is from the master's transaction log but if the master has recycled that portion of the transaction log then you can't catch up at all now by default Postgres doesn't tend to recycle the whole transaction log particularly quickly but I found just to avoid bad timing based incidents where you have like a 5 minute network outage that happens at exactly the wrong time and you lose those transactions set well keep segments which just gives you a little padding to a small value like 4 segments which is a total of 64 megabytes worth of luck now if you have a very fast transaction load where you're going through 16 megabytes of transaction log every 30 seconds then you might want to set this to a higher value like 128 um the um um but not necessarily because what I'll suggest in that case is you really want your replication to be using replication slots which is a better method for this um what well keep segments end up covering you for more is actually starting replication to begin with where when you spin up a new replica when the new replica first comes up it needs a certain amount of catch up time um and it doesn't have a replication slot yet to hold the bookmark for that and so in that case you want to have that well keep segment so but a small value one of the things that's not on by default on the standby um for reasons I disagree with is hot standby feedback is it most of the time hot standby feedback says the replica should tell the master about its workload so that the master doesn't delete data the replica was looking at and then send that deletion over via the replication stream because that causes something we call query cancel where after a certain timeout limit whatever is connected to the replica gets a termination error message saying the data you were looking at has been removed by replication and so you have to start over and most applications are not prepared to handle that kind of termination and so you don't really want it to happen and one of the ways to prevent it from happening is the replica tells the master about its workload now this is a tradeoff because that means that there is less stuff that the master can clean up and vacuum and that sort of stuff on its own because effectively it's carrying the workload of the entire replication cluster so if you have a master that has 30 or 40 replicas you might want to refact your application to deal with the query cancel rather than having hot standby turned on but most of the time for most users they do want hot standby feedback track commit time stamp not on by default, don't understand why this simply says hey every time I do a commit I'm going to have a time stamp with that I'm going to have that available by certain post quiz functions so that you can keep track of how did this commit happen the overhead for this is not substantial and it's useful information to have particularly if you're going to say particularly if you're going to monitor replication lag it's a better way of monitoring replication lag than saying hey I'm going to flip a boolean every 30 seconds and see when it changes on the replica the max wall cinders, max replication slots the defaults for this are pretty good for most people it's 10 and 10 and most people are not running with 40 replicas and most people are running with one or two in which case you don't need to touch the defaults but if you are the person who is running with 40 replicas then you need max wall cinders, max replication slots to be higher I generally set it to double the maximum number of replicas you ever expect to have there's two reasons for that number one is right when replication starts up depending on how you are starting replication it can use two replication connections instead of one the second reason to double is every time you say the maximum axe I might want to use you are going to estimate too low and changing max wall cinders requires restarting the master which means downtime which means most people don't want to do it replication slots are bookmarks that the replicas hold on the master for when they last left off syncing data it's required for logical replication it's a good idea for binary replication so again you want this to match up now new logical replication new feature in postgres 10 allows you to replicate individual tables allows you to filter replication allows you to replicate across different postgres versions but it does mean make replication more complicated because you are replicating individual tables not all database which is a much simpler concept but lots of you are going to use logical replication in the future for these and other reasons and in that case here's an example sort of configuration with some of the stuff you have to raise what's called the wall level so wall level is how much stuff information am I writing to the transaction log because the replica doing regular replication requires a little bit of extra information to transaction log logical replication requires even more extra information to the transaction log so and by the way this means that if you are not doing any replication at all for whatever reason in that case you actually want to have wall level set to minimal so that you can minimize your IO with the understanding that if you ever want to do replication in the future you are going to have to change the configuration and restart but so if you want to do logical replication you have to raise that level from the default and you want to do that also if you have logical replicas that are not doing binary replication you actually want to turn hot standby feedback off on those because they are still making replication connection to the master you don't want them publishing their visibility information because that is not useful and it is just a burden on the master and you definitely most of the logical there are different logical replication plugins but most of them require you to turn track commit time stamp on then finally archiving we are talking about continuous backup and this actually only has a couple of settings associated with it one is archive mode which turns archiving on and the other is an archive command which tells it what to do now this archive command should be a command supplied to you by the documentation of whatever piece of software using to manage archives and I am hoping that piece of software is something better than an ad hoc bash script yes you can do an ad hoc bash script but really you should be using something like Wally in an Amazon environment or PG Backrest in a bare model environment or something else that does long rotation snapshot rotation scheduled regular full snapshots and provides you metadata so you can browse your own backups because doing this ad hoc first of all is likely to fail in unexpected ways when things you didn't expect to have happen and second makes it really a management nightmare but you will supply that archive command now here is one hint on this turning archiving on requires restarting the server changing the archive command does not so my recommendation is even if you are not doing archiving now turn archive mode on and set archive command to something like bin true on linux and bin true will always return success so the archiving will always happen successfully and then someday when you actually install PG Backrest you don't have to restart the master in order to make it work because you can just change the archiving command also that is the way out of a bottleneck if your archiving starts failing and you fill up your disk because postgres if archiving is failing postgres will keep the transaction along forever so your way out of that is change the archive command to something like bin true that returns success automatically and postgres will start deleting those archive segments because otherwise it is very hard to get out of the situation so we are at 11 o'clock now and my last bit of this set of core recommendations is going to be logging settings so we will take that up at 11.15 when we all come back from the break so see you in 15 minutes thanks and I'm going to get more water sure it's over hey folks we're going to start back up we'll get started here and we will get into some logging settings which is another area we're going to touch and actually in terms of settings that you are going to touch frequently and repeatedly it's going to be these logging settings because postgres really allows you to log a truly amazing number of things so much so that turning them all on can mean that you are writing more information to the activity log than you are writing to the database I've seen it so the answer is not to turn all the logging things on all the time the answer is to turn them on specifically when you are going to actually make use of them so in here and again like with the replication settings the logging settings only make sense when you are combining several of them to do different to produce a particular result that you want in terms of logging so for example most people installing on bare metal are actually going to be logging postgres to syslog and this means that you actually need to set a couple of things number one is so postgres is a setting called log destination and log destination is where is the log going to be output to this is actually a list you can actually tell postgres I want to write the log to syslog and I want to write it to a custom file and I want to write it to a csv file so postgres will do that the bio effect is probably not desirable but it will do that but most of the time people are logging to one source or another so under most circumstances people are going to be logging to syslog because they have some kind of syslog management in place because they have some sort of unified cluster wide syslog because they have something and they have got syslog feeding off to prometheus but in any way it is going to do syslog so one is you have to tell you are logging to syslog you have to tell it which syslog facility you are using if your system has multiple ones by default in a new fresh Linux system you have I think three of which local zero is for applications but obviously if you are logging to something like our syslog this is going to change the syslog facility name is going to change and then the important thing that a lot of people forget to set is the syslog which tells it how you are identifying this particular postgres and I am saying you actually should have some kind of unique name there a lot of people just do this my syslog is postgres which is great up until you have three different postgres backed applications which has three different replicas and you are using our syslog or logly or something to unify all of your syslogs across your whole enterprise and now you have a million entries that all say postgres and you don't know which postgres it is so give each one a unique name according to whatever naming schema makes sense to you if you are running pure bare metal hosts can work, if you are running containers can work, if you are running VMs VM names can work something that is going to identify which postgres this is that is producing the logs the other thing you want to add for syslog because syslog does not allow us to reformat the entry is you want to add a logline prefix which supplies some extra information about what got generated so this suggested logline prefix gives you the database name the login role name the user name if application name is set which I suggest setting in the client if application name is set the application name and the IP address of the client which is useful or not useful depending on whether you are using connection pulling the other reason for suggesting this particular logline prefix is this is the one PG Badger uses if PG Badger has to get this information from syslog syslog and I will explain that in just a moment windows uses the log destination event log otherwise you can set a lot of the other things the same up above here is an example for having a private postgres log and not a heavily used postgres server where you just want to have a log to their log postgres and have it rotate weekly the and this is an example of that the managed postgres rotation is with the two settings log file name which uses strftime escapes and look up that's actually the escapes are listed in the documentation strftime escapes to name this that's the that's the day of the week and then whether or not you actually truncate the log segments on rotation the alternative truncating the rotation is to append this monday's log appended to last monday's log under most circumstances the and then here we're actually logging every query that takes more than 100 milliseconds which is log min duration statement most of the time if you are monitoring query performance you're going to want to use log min duration statement there are two other settings log statement and log duration that you use under other special circumstances but most of the tools that we know of that will analyze your query performance want log min duration statement the difference is log min duration statement waits until the query completes and then on one line logs the duration of the query and the query itself log duration to log statement logs the statement when the query starts and then logs the duration when the query completes the problem is if you're doing that to something like syslog it can be very hard to reunite the duration line with the original query line in whatever your log analysis tool is and for that reason log min duration statement tends to be all more useful there is one circumstance under which your log statement is much more useful though guesses anyone oh come on if the query is not completing then you need log statement to log the query that's not going to complete oh and log statement versus log min duration statement they actually interfere with each other if they both turned on at the same time so don't turn them on at the same time you end up with a really weird log output log management tool I know of can deal with here's an example here so one of the things that I used to use a lot with Postgres is a performance analysis tool with Postgres called PG Badger PG Badger takes a log all the things log and then digests it by importing it into importing it and then running a whole bunch of routines over it to analyze things like how many connections did you have in each minute and how long did your checkpoints take and how many temp files did you use and everything else and that requires you turning on all of your log things now PG Badger does not require you to log all queries which is what log min duration statement 0 says is log all queries no matter how short they were however if you are doing actual comprehensive performance analysis of your application you need to log all queries because a lot of people make the assumption that hey what's consuming all my resources is a few long running queries which is sometimes even often the case but sometimes what's consuming all your queries is thousands or millions of very short queries I've been in situations actually where set time zone was in fact consuming 10% of server resources because the front end application had been programmed to set the time zone before every single statement or before every entrance into a function so it was sending so every connection was sending set time zone several times often several times per second and it turns out in the aggregate that was consuming a lot of resources so at some stage if you're going to do full performance analysis you actually want to log all the queries in that case you're probably going to want to set log directory and you're going to want to send the logs that's going to have the storage to handle this because if you're logging all queries on a server that does 1,000 queries a second you'll actually discover that that may generate a gigabyte of logs per minute so you're going to want to monitor that and you're actually going to want to be actively ready to switch back to default log settings if it starts to fill the disk up now a few more of these settings in terms of things that you can log log rotation size is how big should our activity log chunks be if you do not need to go through syslog you want to use CSV log that's comma separated value formatted logs that contains all of the registers that Postgres has to log to so you don't need to set up a special log line prefix in order to capture things like user and application name information that's just in there as a column in the CSV so it's the easiest thing for external sources to digest for that matter if you have something like Grafana that wants JSON it's trivially easy to convert a CSV file into a JSON file so if you're going to be dealing with the regular logs you set it that way and then we can log lots of things we can log checkpoints to find out how long flushing memory took and how much IO bandwidth it created you want to log connections and disconnections to see how many concurrent connections you have to Postgres and what your sort of connection activity is logging lock weights is whenever a query or statement or function or something has to wait on another one in order to do anything like it wants to modify a row that another query has opened for modification or it requires a full table lock for something because I'm going to change an index or something else then you're going to have a lock weight and those lock weights can be very destructive to performance because they can pin resources and under really bad situations they can result in pile ups where you have a whole chain of 15 or 20 or 25 different sessions all waiting on locks to be released so you want to look for that activity the reason why you really want to at some point analyze lock weight activity is that it is the one common chronic performance problem that cannot be solved by adding more system resources as a matter of fact if you add more cores and more memory lock weight problems will get worse because that just means you have more concurrent connections so that they can conflict with each other more so the problem with lock weights is we can't actually currently log the lock weights when they happen when something first starts waiting the only central resource we have that knows what things are waiting on locks because this lock management is distributed for very good performance reasons is the deadlock time out monitor now the main purpose of the deadlock time out demon is to go through and see two sessions that are deadlocked that is query one wants to modify row one and then row three and query two wants to modify row three and then row one and the two of these start simultaneously they are never going to complete they are each going to be waiting on each other and so the deadlock monitor looks for cycles like that and resolves them by terminating sessions until the remaining sessions are able to proceed normally the deadlock time out runs once a second which is actually a pretty good interval by default but if you are specifically doing lock analysis you want to crank down that time out in order to what this set of settings means is I am going to be reporting on every lock weight that is more than 100 milliseconds waiting unfortunately cranking down your deadlock time out to something like 5 milliseconds would actually be really expensive in terms of database performance because that deadlock demon is actually kind of expensive in terms of the amount of memory it touches so you can't really do that but in a few milliseconds I find is a good balance between affecting performance and getting useful reporting data log temp files again like I said temp file limit we want to know how much temp files we are actually using so we can set that rationally so log your temp files and then we put this one in a long while ago because sometimes people change the error verbosity which you can do you can tell Postgres to be super explicit in the things that it is logging and you can tell it to be very terse our three options here are terse, default and verbose and log digestion tools generally expect default so those are sort of your log combinations about some of these logging things any questions about logging settings or for that matter the simple config file which I have supplied to you for your tearing apart and using in your own infrastructure options any questions about the simple config file before we move on to some other settings ok awesome so if you downloaded the github repo I have a second file that has settings that are going to be touched less frequently but can often be useful under circumstances that are not in the majority of users and often enough for you to want to know about them so this is sort of our second tier that we just went over is our eighth tier of settings that if you are administering Postgres long enough you probably will touch and now this is settings that you might want to know about but you can get away with ignoring and I've got a little bit more terse format here and a lot of these settings honestly have to do with performance optimization so I'll save it as one thing is if you are not in any way rate limited to Postgres like if you've got a Postgres instance and it's running at about 10% of system capacity ignore most of us but if you're interested in getting into Postgres performance tuning this is a bunch of settings that you will want to know or if that 10% system capacity is crept up to 30% in the last three months this is a group of settings you'll probably want to know but first let's start with the not so performance oriented ones some of this is authentication timeout and I went over some of the other timeouts this is a different timeout this says if somebody starts an authentication handshake in order to log into Postgres and they don't finish it when do we give up and reallocate the socket and the important there's an important rule for this one which is whatever is set in your driver or your application on the client side this should be the same as so for example I believe the default for JDBC is 20 seconds that is default PG JDBC settings the Java will give up on connecting to Postgres after 20 seconds if it can't complete the connection well Postgres default is 60 seconds so what happens if Java is given up after 20 seconds and Postgres doesn't give up for 50 seconds 40 seconds of Postgres holding a socket open for no good reason and if you are having chronic network problems this can show up actually is really destructive performance on the Postgres side of things including running out of connections like I had a situation once where we were hitting the connection limit even though there were only 25 live connections and the connection level was 300, what was going on well it turns out we had a lot of aborted connections and they were not aborting so we had to fix the situation but the other thing you can have to make that not as destructive in the future is make authentication timeout match the application timeout now it's obviously a lot harder to do if you have a diverse array of applications that have all the different timeouts my argument there is you should refactor those applications in order to synchronize those but whatever the other thing is there are two other places you can give Postgres a unique name and again for identification purposes this is useful which is you can give Postgres a cluster name and and then when you are looking at the Linux process list or you are looking at the windows application list that cluster name that you've set will show up in that list so if you're running more than one Postgres instance on a particular machine that is super useful and it's actually useful even if you're not running more than one on a particular machine because if you have different databases that have particular purposes like this is the reporting database and this is the transaction database and this is the dev backup if you're looking at something on a server and you're about to start killing stuff it's useful to have that reminder which database you're looking at that yes in fact you are looking at the dev database and not the production one a similar thing to this event source does the same thing for the windows event log that syslog does for Linux syslog so we can actually have an identification of the windows event log once again in case you're using generalized event log management and you're consolidating somewhere you want to be able to identify which Postgres did this come from ok so now we're into more interesting performance tuning so controversial recommendation for you synchronous commit so here's the reason why you do this sometimes people are in environments where storage latency is very high and their performance constrained and they can't really improve the storage latency and losing half a half a second of data in the event of a hard crash is ok and a good example of this, a very common example is I am running an interactive web application and Postgres is being hosted on AWS EBS write latency because it is virtual storage on top of virtual storage is actually kind of high compared to bare metal latency and that latency for can dominate a database application where you're doing small writes there's also true with other forms of cloud storage like Gluster and Seth is the latency for writing an individual row for writing a single you know 140 byte row of Postgres can really be destructively high in those environments because you're actually writing a whole 8k page the whole 8k page is actually writing a whole megabyte block the whole megabyte block has to go through whatever the cluster starting system is in the cloud storage and that can be extremely slow so if you're saying hey I could lose half a second of data and I'd survive and I would really like to see the weight queue when I'll stop dominating all of my performance profiles then turning synchronous commit off by default can be an option for you particularly because in Postgres unique among transactional databases no there's only 2 databases to do this Postgres and Cassandra you can actually set your level of synchronicity in an individual transaction so you can say hey normally I'm okay with losing up to half a second of data but I'm actually saving the financial transaction financial data so I'm going to turn synchronous commit back on for that individual transaction so you can actually do that and as long as you're doing that for less than 10% of transactions you can actually get a boost in latency performance by turning synchronous commit off wall buffers wall buffers is a small dedicated area of memory where we marshal data to be written to the right of head log the defaults are pretty good the defaults go up to 8 megabytes or up to 16 megabytes depending on what you've set shared buffers at it's a calculation against the size of shared buffers and for any sort of realistic new system it's going to use the max it actually honestly works okay for most people but if you have very high concurrency if you have 200 busy connections on a system with 24 cores then you would actually benefit from raising wall buffers just in order to deal with concurrent write problems from the last time I've seen any performance testing on it the max that's actually useful to increase this to is about 128 megabytes so if you're in that high concurrency situation consider manually raising this to that wall compression a new feature in Postgres 9.6 I believe this allows you to compress depending on whether you have it turned on is full page writes to the log and if you are doing replication Postgres does not allow you to turn full page writes off because we need those complete 8k data pages in order to replicate them but the drawback to that is hey we're writing a lot of 8k pages when all we did was modify a single 100 byte row so it's like there should be somebody who can make that more efficient wall compression does make that more efficient like any form of compression we are trading CPU time for IO time most of the time in most situations that's a good trade right because most of the time you are more likely to be waiting on IO wait latency than you are to be waiting on CPU queuing but look at your system if you've got a bunch of CPUs that are maxed out and your IO waits are low then turning on wall compression is not going to be a win for you most situations actually turning on wall compression will be a win another controversial recommendation turn the auto vacuum off when? well if you have a database that is just for analytics and so all new data comes into that database as bulk loads as programmatic bulk loads and you are going to put a bunch of manual vacuum and analyze statements into those bulk loads at the appropriate points then you are better off turning off auto vacuum has this annoying tendency of kicking in and wanting to do this clean up activity in the middle of a bulk load which then makes the bulk load slower but you only want to do that after you've instrumented your bulk load code so it is doing those manual vacuum and analyze statements there's a couple of other so now one of the things is that people weren't into all the time is hey why isn't Postgres using this index that I have and usually people try to clobber that over the head by setting random page cost to some ridiculous level like 0.1 which may improve that particular query you are looking at but will tend to break a whole lot of other queries or to do other clobbering people don't realize that there is actually some more subtle is a bunch of Postgres so Postgres uses what is known as a cost based optimizer and the cost based optimizer does a whole bunch of mathematical calculations it basically tries to plan out all the different query plans by which it could possibly execute a given query and then calculate mathematically how much each of those will cost and then execute the lowest cost one well there is a whole bunch of constants that go into that cost and two they are useful in subtly influencing Postgres to favor indexes and to favor functional indexes and multi column indexes over single column indexes are CPU index tuple cost and CPU operator cost so if you have been analyzing a lot of slow queries on your Postgres and you are saying hey Postgres is just not using this index even though I think even though I have done the back of the envelope calculations where hey you know it only needs to access you know 2% of the table and the index should be relevant and that sort of thing before you go clobbering Postgres over the head with some of the less subtle settings try lowering CPU index tuple cost and CPU operator cost slightly and you may discover that it fixes the queries that are borderline cases without breaking everything else and of course you can do those interactively before you set them as part of the defaults default statistics target again default of a hundred is good for things like interactive applications however if you have big data you know 10 million billion rows then this is a static sample size it's not percentage and it's going to be a little small so you are actually going to want to first up the default and then second on specific tables or on specific columns you may want to actually up the sample size a lot and you can set this with an alter table statement alter table set default set statistics target alter table set statistics target to some value up to 10,000 samples so again interactive application that sort of thing something under you know 50 gigabytes in size probably don't need to touch this larger data you may want to increase the default like I said a good thing to increase the default would be about 500 or 1,000 you definitely don't want to increase the blanket default more than that beyond that you really want to be looking at setting this on tables or even on individual columns such as index columns or numerical columns effective IO concurrency easy one to set you have high end storage that can handle a lot of concurrency tell the query planner default for this is either one or two I forget which and that one or two tells Postgres that you have negligible concurrency in terms of being able to access say multiple indexes at the same time which really plays into things like merge sorts so if you are on a bunch of SSD storage or high-end sand or cloud storage or whatever your concurrency is generally pretty good and in that case up this I'd say the useful maximum for this is four because without resorting to parallel query Postgres is limited in stability to parallelize things so bump that to four here's one that is just a just do it and we'll go away as a problem in Postgres 11 in 9.5 or 9.6 we added this option called replacement sort tuples which was an alternate sort path for on disk sorts etc that was faster for small sorts with general improvements that were made to sorting in Postgres 10 this is actually no longer an improvement the default in Postgres 10 unfortunately is 150,000 so 150,000 rows or less it's going to attempt to use this you don't actually want it doing that the number of situations where it's really a problem are fairly small which is why I didn't put this in my core settings but if you're going to get into performance tuning just set this down to zero in Postgres 11 this will go away because the setting will go away so this is another one for so one of your chronic problems in the world of Postgres what are our common chronic bad use cases you have a mix use database that has long running analytical queries on it and it also has a queue a Postgres table back queue it's a bad situation because what queues do is they constantly create and delete records which means you want that queue to be pretty much being continuously vacuumed by the auto vacuum demon because it will always have more dead rows than it has live ones but a long running analytical query or any other long running operation will pin the row versions in that queue table so that they can't be vacuumed and so it becomes a real performance problem because even though the queue only has three live rows in it it has 300,000 dead rows and so it behaves like a table with 303,000 rows in it not like the small table that should actually be so our workaround for this in added in version 9.6 is the ability and this is actually something that people who are familiar with Oracle Oracle has the same problem, they have the same feature it's called old snapshot threshold which this says hey I'm allowed to stop pinning row versions after this amount of time and this actually solves the queue and analytical problem as long as that analytical query is not ever touching the queue table the minute the analytical query touches the queue table it will get a snapshot to old error and terminate but it has to be set cluster wide as a time interval and I recommend it setting it to something reasonable like an hour or if you're in a particularly pathological queuing situation you might set it down to like three or five minutes in order to actually cycle that and then you just have to be careful in that application that you are not going to have long running applications that are touching data that cycles really fast because those may get canceled out by a snapshot to old error but it will you know for that queuing issue it really does help a lot questions about that performance tuning before we move on to a few other miscellaneous settings anything you know all the performance stuff okay some file security stuff if you're running on bare metal or in a container and your Postgres packaging doesn't do this automatically I know the packaging for red hat actually already relocates this stuff but like say you're compiling Postgres from source if you're compiling Postgres from source with the defaults it drops the Postgres Unix socket in the temp directory as world-rightable there are a number of hacks that can take advantage of this even if Postgres is running in a Linux container because a lot of Linux containers treat the temp directory especially in a way that is less secure so so if you are installing Postgres in a way that does drop it in the default temp directory location you actually want to relocate it and lock down its Unix permissions a little bit not a whole lot just make it non-trivial to actually exploit it obviously if you have applications that are connecting over the Unix socket other than backup or something that runs as the Postgres user they're going to have to for example be in the Postgres group but it's a really good thing to do so one of the things we're going to talk about is freezing now if you really want to read up on freezing I have some blog posts from 2012 that I've linked in there that are still largely relevant some tremendous improvements on freezing particularly the freeze map that makes it much less costly to freeze your tables but I still feel like the default settings for a few of these settings are way the heck too conservative in terms of when to freeze stuff and they result in kind of a time bomb problem where large tables that are infrequently updated end up waiting until you hit this do or die deadline called auto vacuum max freeze age and then you get an uninterruptible auto vacuum that generates a whole ton of IO and that you can't terminate and that if you even restart Postgres it just comes back so what I recommend doing to try to avoid having that happen is two things number one is you can raise that slightly not a lot because our hard limit is 2 billion because transaction IDs are in force so our hard limit is 2 billion at least until Postgres 11 Postgres 12 we may move to transaction IDs that are de facto in date in which case this whole thing changes but it doesn't change in Postgres 10 so default is 200 million I recommend raising it to you can raise it to 500 million gives you a little bit more time before that becomes a do or die deadline it's not really safe to raise it further than that except under an emergency situation because you really don't want to have it be more than a quarter of what is the Postgres cannot start because if we genuinely run out of the very last transaction ID you literally cannot ever start that database because anything you want to do requires a transaction and we have no IDs to allocate anymore so you never want to get into that situation so don't set this to 2 billion even if you can and then the other thing that you actually want to get into and then we've got Vacuum Freeze Table Age Vacuum Freeze Table Age says if I'm vacuuming a table anyway when do you want me to do a full scan so I can freeze a bunch of things always set that to 75%, 80% of whatever auto vacuum freeze max age is because again that's an expensive IO operation you really only want to kick it in if you were looking at possibly doing that anyway unless you're setting an individual session if you have regular slow periods in your database activity then that's a good time to schedule a manual vacuum and set that with a really low vacuum freeze table age so that it cleans things up proactively and my old company Postgres School Experts has in fact a script up under the PG Experts GitHub repo I don't remember what it's exactly called that actually helps program doing this proactively anyway then the other thing you should do is really lower the minimum age and the minimum age is hey if I'm writing this data page anyway how old does the transaction ID have to be before I freeze it and this thing you actually want to have as low as is reasonable like the actual level should be hey 15 minutes to an hour of transaction ID spent like if you're actually willing to sit there at sort of like medium application time and check how many transactions what's my transaction ID now what's my transaction ID in 15 minutes to get an idea of what your transaction ID burn rate is that's actually what we should set it to try setting to something like 50,000 to start out with that will actually cause a lot of things to get frozen before they're ever flushed to disk and if you want to know why we don't do this by default you might want to find a long argument between me and Tom Lane on BG SQL Hackers from about four years ago and again if you want to know more about freezing read my blog post et cetera and then hopefully in Postgres 11 or 12 a lot of this will be going away honestly but right now it is something that you might want to tweak so one other set of settings I forgot to put in this file and by the way does somebody have my GitHub repo open in the browser anyone there's somebody to log into that I want you to file to you right now saying but what about parallel query settings because I meant to include those in the extra and I forgot about them so this is looking at that database I distributed to everybody there is a view called annotated report that joins PG settings to the table annotated which is my additional notes on every setting and then presents it in a sort of spreadsheet friendly format for you to actually look at stuff so I'm going to pick some columns out of that and then particularly actually want to look at the parallel query settings so max workers doesn't really need to do the parallel this is the auto back in max workers that I talked about earlier so there's a few things that are setting for parallel force parallel mode is a query troubleshooting thing you would only do that interactively that's a hey if I were using parallel query what would this query execution be like in cases where the query planner is choosing not to parallelize usually you find out why parallelization was really bad ideal you can do that but sometimes you need to numerically know why it was a bad idea so the way that parallel query works is parallel is a multi process database and so in order to parallelize a query we actually have to spin off extra processes these processes are known as workers and for resource management on the system CPU resource management primarily the number of ways for the administrator to limit the number of workers there are and the default limits are quite low logical replication workers that test through the replication but here we go so one of those settings there is max parallel workers and max parallel workers says across the whole system what is the maximum number of concurrent parallel query background workers I can be executing at one time if you are actually going to be actively using parallel query because you are doing analytics on the database this is real low 8 is going to be not high enough except if you only have 8 cores in your system to begin with that is often not all that helpful to have more parallel workers than you have cores now in a lot of cases when you are doing parallel query you are waiting on IO wait so it can be helpful to have double the number of workers you have cores because the system will cycle things but not a lot more than cores and usually a default is you want less than cores because you are assuming you are going to have a bunch of other activity in the system that has nothing to do with parallel query but in the case of where this would get high is this is an analytics database that is only used for analytics and reporting I am managing the connections to it so I have never got more than one or two concurrent connections at a time current jobs at a time and so in that case I want to up parallel workers to double the number of cores I have max system wide then your second limit is called max parallel workers per gather now currently there is only one parallel core operation that happens in close queries which is called a gather and the gather is either we are going to take this many fragments of a sort and collate them together we are going to take this many fragments of a merge and collate them together but it gives many fragments of an aggregation to this underlying operation called the gather and so right now that is the only thing that has a limit if we add other types of parallel operation in the future you may see a second limit appearing here but this is basically what is the maximum number of parallel workers I can have doing a single gather and the default here is two so I can only have two parallel threads working on the same sort not all that useful if you actually need parallel query so this is one of the things we compromise by making it two where the sensible limit is either zero or something higher than two because if we need parallel query you are going to need just to be like 4-8 or something and if you don't need parallel query you probably don't actually want it happening by accident because it is extra CPU load right so again doing an analytics database you might raise this up to half of what you raise max parallel workers up to okay again that actually has to do with replication if you are using max singular for subscription this is for logical replication on the standby side it allows you to parallel receiving the replication information in order to keep up so then max worker processes so this is the super set of max parallel workers so the super set of max parallel workers where it says how many workers of any kind am I allowed in the system why is the different parallel workers well Postgres actually is a feature called custom background workers where I can for example have a backup tool running as a process under the Postgres daemon or in interface like for example there is an interesting tool that makes Postgres protocol compatible with MongoDB that runs as a background worker so you can connect to it on the Postgres server so for any of these background workers those are extra background workers that have nothing to do with parallel query so if you have no custom background workers this is going to be the same number you have max parallel workers set to but if you might load Postgres extensions that involve other background workers then you may want to set this to something higher than that so that you always have sort of headroom for the other workers even if parallel query is maxed out now there is a bunch of tuning parameters which is things like this parallel setup cost for example which is how much do I add to the cost of a query if we are going to kick off parallel query at all because we have to instantiate a bunch of connection processes we need to set up a lot of sort of scaffolding for sharing data etc that is non-free and the default here is a thousand parallel tuple cost how much is it to gather an individual row that sort of thing I will say absolutely no one has done exhausted performance testing across a variety of different workloads to discover what are reasonable settings for these cost parameters these are still in the point one sounds good to me territory so if you are working in analytics and you are using parallel query in Postgres heavily you may find yourself monkeying with these cost parameters and playing with them if you are going to do that I would say please share your experiments on the virtual performance mailing list because other people are at the experimental stage too and you will both get good information and provide good information to other people in the community and then eventually that information will make it back into the Postgres default so they will be set better in the future we are at 12 o'clock we have 30 minutes left in the session during which I will cover a random selection of other settings however before I do that any questions on this other group of settings parallel query and other things questions on how to manage Postgres QL settings no ok settings to where it is you don't have anything left in the slides I don't think I have anything left in the slides the grand tour so if you look at so again how do they get repo there is a number of files if you look at the annotated table in the database or you look at that annotated.csv file in a spreadsheet program you will notice that I have added a bunch of categories and sub-categories for the GUCs that do not match the category column in PG settings and that's because I filled the category column in PG settings is out of date and does not have enough different categories the and for one thing one of the things you will notice in the official categories in PG settings an awful lot of random settings are thrown in client connection defaults which is basically the miscellaneous category I hate miscellaneous categories it was a database geek for many years and if there is anything a database geek hates it's a miscellaneous category so I have come up with a different sort of schema of categories and sub-categories that I feel more completely divides up the setting space into the different kinds of settings that you can do obviously no categorization system is perfect but I think it actually helps you a little bit so we are going to actually look at those by category so one of the things I mentioned file locations so in postgres.conf you can actually define a number of file connections for where things are one of those is super deceptive the config file contains its own location which means you can't actually change this um but everything else so one of those is data directory how many people here are using Debian or Ubuntu Debian or Ubuntu do a thing that I actually really like which is that they have the configuration file in a separate directory from the postgres data this is the way I set up systems on my own on Red Hat which puts them in the same directory because the data in the config really should be under separate management for obvious reasons right that is managed by replication and backup etc config is managed by configuration management systems they should not be in the same directory so um so what you can do is you put the configuration directory in somewhere and then you set this data directory parameter it's not already set by your installer to a different location other things are a little more special purpose um external PID file which was the one that we just had above um was um a way of actually setting an extra process ID for the postgres master because you the default one goes in PG data for postgres is own monitoring of is the master still work is the master process within that individual postgres is still running um but sometimes your system like system d also wants to keep track of the PID and it wants it in a different location so if you look at a red head install that variable is actually set because we need that second copy of the PID for system d db username space never use it it was a hack we added to like postgres 8.4 and can't seem to get rid of um the um uh anybody using Kerberos authentication or gss api which is the successor in Kerberos bunch of settings associated with that if you were we told you about the SSL settings where you have to actually set a bunch of these things um tada um I really hope to the gods nobody is still using banjur don't understand why we still have parameters for this I guess we have a bunch of people who are still running postgres on mac 10.2 and for that reason they need banjur but uh max connections port you can change the postgres port sometimes useful regularly you need to do this if you are running multiple postgres on the same machine although the port is one of those things that I often recommend managing by command line parameter instead because you may want it to run in a different port depend dynamically depending on how many postgres you are starting super user reserve connections this is one of your save your but things which is if you have run out of connections on the server because you are having an application problem where there is lots of idle connections and so all 200 connections are used how do you log into the database to find out what those 200 connections are using so you can selectively kick them off by default 3 of those connections are reserved for only super user connections backup rings to the super user you may have a partition management tool that is running as a super user you may have a monitoring tool that is running as a super user if you have all of those things then you may need to up super user reserve connections to 5 so that you don't end up in a situation where you are locked out 1 over unit sockets already authentication timeout if you are a network geek you can actually play with tcp keep alive so that you can have the lowest possible interval between an individual postgres server going down and the other postgres servers replicas and masters and stuff knowing about it but this requires you to actually know enough to play with this because you will also need to play with the tcp settings on your host system to make this make any sense parallel i went through the max worker processes dynamic shared memory type postgres installer should deal with this automatically huge pages one of those goofy things generally you can trust the defaults here this is a linux specific thing usually you are going to be using huge pages because you are going to be running postgres on a system that has more than 2 gigabytes of ram but if you are actually running postgres on a system that has less than 2 gigabytes of ram because you are running on an amazon instance for example there might be some tiny performance gain in turning huge pages off again only do this if you are willing to check for performance improvement on those circumstances but we went over all the memory settings max prepared transactions i have not updated this for postgres the default for this is now zero as it should have been this is if you are doing 3 phase commit across multiple postgres systems if you need 3 phase commit you will know it because you will be using some tool like a cluster management tool or distributed transaction tool that requires it otherwise this needs to stay at zero the max files for process one of the things you adjust when you get a certain error in the logs as in exceeded max files for process quitting although again and the reason why you touch this is imagine that you actually have an 11 terabyte database where every table is broken up into 100 partitions you can actually pretty easily exceed touching 100 files in an individual transaction if that happens shared preload libraries this is primarily for preloading postgres extensions some of the extensions require you to use shared preload libraries for example if I own install the site extension postgres allows you to use started postgres that requires loading site as a shared preload library and then you have to restart postgres that's a list temp file limit we already mentioned wall and checkpoints min wall size max wall size this is our new easier way to govern the transaction log usually the minimum of 128 megabytes and the maximum of a gigabyte and then postgres dynamically allocating between those works great for most people most people don't need to touch that however one is if you are literally writing more than a gigabyte of transaction logs per minute then you actually might want to increase that maximum and sometimes that happens because you have a whole ton of transactions you're doing 10,000 transactions per second right? sometimes it happens because you're doing massive bulk loads and during the bulk load period you can write a whole ton of data right? but in either case then you might want to up the maximum size the reason why you want to up the minimum size is if your workload is super bursty because then if you have like several minutes of zero stuff being written and then all of a sudden we go up to hey I'm going to write 160 gigabytes of data and then I'm going to have zero stuff being written for half an hour if that's your workload then you actually want to increase the minimum because otherwise what's going to happen is the transaction log manager during your idle periods is going to shrink the transaction log down to the minimum to 80 megabytes and then it increases it by running out of space but while it's doing that running out of space it's cycling the transaction log a whole bunch which slows down your write rate well compression we went over archive commit archive mode we went over those flash settings I mentioned those are new I don't have any tuning information about them if you want to mess with those which you might if you're having all kinds of IO bottlenecks then hop on Postgres performance full page writes has to be on for replication if you're not doing replication you can conceivably turn it off to lower IO but then you're increasing your danger of database corruption so just never turn it off synchronous commit we went over wall sync method gets set automatically to install time pretty much never have to touch that the wall writer delay is a weird one for people who actually deliberately want to have replicas lag behind which is lots of fun the otherwise well no one I'm sorry this is the wrong delay that's wall receiver delay wall writer delay is to actually help the wall buffer work better for replication and I believe that 200 is 200 microseconds which means you shouldn't really have to touch that I think it's microseconds actually but I would need to double check that it's a new setting the we used to have so Postgres as other IO constraints with actual databases spends a lot of time tinkering with what's known as group commit various methods of what's known as group commit and group commit says hey if I've got these six writes in different sessions that I was going to do at the same time anyway within like 100 microseconds of each other it would be really nice to send them to the operating system as one big batch write so that if some of them were hitting the same disk pages it would be one write instead of two and plus we can minimize seeking behavior that way right because if you send them as a batch then the IO schedule on the operating system can do a better job of collating them for efficient use of your IO resources so our first attempt to do this was something called commit delay and commit siblings I feel that commit delay and commit siblings have been completely superseded by the flush settings by the checkpoint management settings by a synchronous commit and thus they're absolutely not useful now but a few people are really addicted to the settings every time anybody brings up or moving them they say no I still use those so I am not recommending to use these I recommend that you set them at the default which is to not use them checkpoint completion target says hey from a scale of you know 0.0 how much of my checkpoint timing interval am I allowed to use for a flush and the default is one is 100% of it to say gradually sink over the entire 5 minutes or 10 minutes of the checkpoint time which is really what you want to have happen to minimize IO impact and default checkpoint time out is set in seconds and that is 5 minutes works for most people sometimes for example if you're doing a lot of bulk loading it can be helpful to actually increase this to if you're doing a lot of bulk loading and you have a lot of memory because you actually want Postgres doing its own checkpoints before the OS runs out of file system cache and starts forcing a flush because the OS tends to be a lot less efficient about flushing stuff out of memory and tends to do things like stall your entire IO queue while it's doing the flushing to be doing the flushing and not the OS to be doing the flushing so if you're going to increase this time out you have to be reasonably sure that you have enough memory to hold all those dirty pages so you're not triggering an OS flush the background writer is something that actually goes through and flushes Postgres's dedicated cache, the shared buffers cache and flushes data from the shared buffers cache in order to avoid running out of space in the shared buffers cache there are a number of settings to this these settings have been tuned over a number of Postgres releases to the point where I actually don't have good general recommendations for how you would tinker with these if you're tinkering with them today you're tinkering with them to address a specific problem and that specific problem is going to be based on loading the Postgres buffer cache extension and looking at your actual buffer cache behavior in order to figure out where your problem is otherwise you're not going to touch this so I don't have general advice for when you would actually tinker with the BG writer settings except under one circumstance which is if you're running in ephemeral Postgres node then you'll actually set BG writer LLU max pages to something really super high in order to avoid the BG writer from running at all but it's a fable setting. So replication thoughts went over, all the replication settings went over so there are a bunch of things for tuning the network activity of replication this is again very fine great stuff the settings I didn't go over are mostly hey how often should the standby pole, the master, etc again if you're going to be messing with these are going to be addressing a kind of specific network and timeout situation so there aren't general sort of setting recommendations for these, the settings are there if you do need to tune those I did mention that there are other cost contents you can set one of the ones that people mess with sometimes is this one sequential page cost and that sequential page cost is what is the cost of a single table scan tuple and that is the base number that all of the numbers are multipliers of and the main reason for people changing this is they are trying to get that cost number that the query planner reports when you do an explain to match up with time in milliseconds I think this is a fool's errand because it never actually works and in the meantime you're making your costs look all weird but if you do find, if you inherit a Postgres database and somebody has changed that sequential page cost that was probably what they were doing now there are a whole bunch of settings all of these enable settings these enable settings are for interactive query tuning so you do an explain it's really slow you see it's doing a huge merge join you say hey I wonder if that we're not doing a merge join if that was doing like a hash join or a nest loop instead would it have been faster? well I'm going to turn, I'm going to hit enable merge join equals off if you are setting these enable settings in the postgres.conf file you have done something wrong and that's probably an indication that something else is really out of whack in your database like effective cache size is set to one megabyte or something else and a really gross statistic is bad and people are trying to fix that by doing these enable settings GEQO is the genetic query optimizer for queries that are too complicated to plan exhaustively I'm just not going to get into that because we only have 10 minutes left I have gone over the parallel settings and then we've got a whole bunch of other cost settings again you need an education on the cost planner and looking at a lot of the query settings to actually look at how to set these one that is actually kind of interesting is constraint exclusion this actually enables something so constraint exclusion says if I am scanning a whole bunch of tables that are going to be appended to each other can I drop individual tables from that if I don't think that they have any rows that comply with the query and the main time that you do that is if the table is partitioned because one of the big query optimizations for optimizing query is to say hey this table is partitioned I want to drop partitions out of the scan if I don't think those partitions have any compliant rows usually based on table constraints and that's why it's called constraint exclusion on defined set of constraints on each partition segment so for that reason this is set to partition by default which is what most people want if you set this to on then this also applies to union and union all queries which if you do a lot of union and union all and intersect and accept queries can actually be a useful performance optimization otherwise leave this to the default oh and if you think that there's some sort of weird error in constraint exclusion obviously you can try turning this off interactively and see what happens from join collapse limit are the opposite of genetic query optimization on which they say hey part of the exhaustively planning queries is to say hey I'm joining 7 tables and I can actually join these in any order but the problem is that that's not unlimited this is a joining table as a traveling salesman problem so if you said hey I have 130 tables I can join these in any order well in that case you better be available to check out that quantum computer that IBM has because otherwise that query plan is never going to finish I actually kind of feel that the default limit is kind of low as in based on current processor speeds and that sort of thing this could be 12 but not a lot higher than that and frankly most people are not doing more than 8 table joins anyway but if you do a lot of analytics queries consider raising something like 12 20 is going to be too high let's see anything else that's interesting because we only have about 5 minutes left stats temp directory this is actually a useful performance optimization and I may throw this into extra.com for the future it has external dependencies which is Postgres maintains a buffer file of changes to table stats like how many rows you have and how many updates have been made and that sort of thing in order to batch writes to the permanent stats file because otherwise writes to the permanent stats file would become an IO problem normally this file is a file handle located in the gdata directory itself which means that periodically the operating system will flush it to disk when it accumulates a certain number of 30 pages if you have a very high write rate database you can actually improve performance by moving that to a tempfs because if it's in a tempfs the operating system will never flush 30 pages to disk and it doesn't matter it's disposable data it's just a buffer if postgres crashes and you lose the data it means that you lose the last 10 minutes of updates to the postgres stats so if you are in a high concurrency high write rate situation do consider moving this to a temp file and then changing this variable to tell postgres where to write it to the ok pass that track functions that actually logs statistics about execution postgres functions stored procedures if you do use a lot of stored procedures you might actually want to turn that on and by turn that on turn it to the setting pl which means user stored procedures as opposed to the stored procedures that are built into postgres as part of the system because if you just set that to on it will log every stored procedure execution including postgres system functions which generates way the hell too much output these are auto vacuum tuning parameters again I have actually tuned these for cases where the defaults feel a little bit conservative etc you're not going to deal with them most of the time particularly as postgres improves we have auto vacuum do less and less work and so tuning it becomes less and less important and let's see we'll do whatever the next setting is here that is interesting so auto vacuum makes workers an auto vacuum nap time these two things go together and again like I said some people have situations where they have literally thousands of tables a common thing for this is a database back in a multi-tenant application in which every tenant gets their own set of tables common pattern can result in you having 300,000 tables maximum I've ever seen in production was 2 million tables although alvaro harera proved that you can actually do a billion tables in postgres that's the practical limit because we run out of file set of handles on the operating system but if you have a lot of tables and by a lot I mean hundreds I mean thousands then you're going to need a lot more auto vacuum demon spawning so then you might raise auto vacuum demon auto vacuum max workers to five or ten or twelve again also depending on the number of cores you have because these are parallel processes right the other thing you might do is normally what these auto vacuum workers do is they do some work they vacuum part of a table and then they go to sleep and they go to sleep is defined by an auto vacuum nap time and if you find that auto vacuum is not keeping up because you have a lot of tables it can actually be better to decrease auto vacuum nap time down to say five seconds than it is to raise the number of parallel workers particularly because the parallel workers tend to start about the same time so if you have an auto vacuum nap time of 60 seconds what can happen is if those auto vacuums are short is suddenly you've got eight parallel workers and then for 40 seconds none and then for 20 seconds eight parallel workers and then for 40 seconds none this isn't actually a good use of resources and so that is all we're going to get through in the extended ones but I think I covered about 75, 80 of the settings and I covered all the rest of the settings in writing in the GitHub repo that you don't have so we're going to take five minutes any other questions you have and then break for lunch and then the thing is over so go ahead the question was is there a specific functionality that huge pages are providing that transparent huge pages doesn't provide and I will admit here that I've never really understood the difference between huge pages and transparent huge pages so I don't have an answer to that if I don't think Robert Haas is at this event I would say ask somebody else in the Postgres room we could bug Bruce Mamjan so I don't know the answer to that obviously somebody thinks so or we wouldn't still have it because it is really only an ex-only setting but I don't know what the specific benefit is any other questions nothing else nobody's looking at their Postgres.com saying what about this setting or is it too much of a brain dump to give up on go ahead yes so there's my contact information in general whoa somebody just leaned against the lights there's my contact information in general and that includes again that github repo the annotated.conf repo so clone that or download it as a zip file and of course you can get in contact with me shortly after this conference I was making a post to database soup about the updated annotated.conf I will probably add a couple of things like parallel query settings in there and actually I want to add the tempstats file because I forgot to add that in the extras as well so there will be another commit on that repo soon so you can just actually pull a new one I do work for Red Hat I'm actually on the community team at Red Hat where I work on Kubernetes which is my thing now and Red Hat is always hiring so if people are interested in working on lots of things particularly in the container arena these days go to Red Hat jobs otherwise go to Red Hat community to see all the things we're doing in upstream open source projects which is a lot of them because everything at Red Hat is open source so thank you very much yes they are open office format well, LibreOffice format