Postgres Performance in 15 Minutes
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Berkus, Josh
In 15 minutes, plus Q&A time, Postgres expert Josh Berkus will explain the essentials of making your database performance "good enough" that you can ignore it and move on to other things. This will include: Why database configuration is less than 20% of performance The 14 settings most people need Why connection pooling is essential Avoiding bad hardware DB performance for the public cloud Stupid things your app does which kills performance Enjoy this fast-paced roundup of PostgreSQL performance essentials.
 I'm going to talk a little bit about some basic things you can do to improve Postgres performance for your Django application. We've got our running elephant here. Few people know that elephants can run at 20 to 25 miles an hour. They're actually quite fast. And Postgres can do thousands of requests per second. So if you're not getting thousands of requests per second, there's probably a reason why. So what you do is you log into Postgres and you go into postgresql.conf and you set the hidden parameter go faster to 10 and then you save it and you're done. Okay, so we're done here. We got questions? Now, seriously, unfortunately, it's not that easy. We actually spend, in Postgres, Postgres development world, we spend a lot of time talking about how can we make things faster. As a matter of fact, we probably spend more time talking about that than just about anything else except maybe the commit queue and who's turned it is to review things. And as a result, if there was something that we could do by default to make Postgres faster, we probably already did it. So the things that you're going to do to make Postgres faster are going to require work. You can't just change a few configuration settings. In fact, one of the things that I do to get paid is I do a lot of performance tuning and performance engineering on different sites. And this is more or less how I spend my time. It varies a lot per site, but more or less how I spend my time. And you'll notice that tuning the configuration is a pretty small minority of how I spend my time. And the effect that tuning the configuration has is an even smaller minority. As a matter of fact, sometimes and in some environments, changing the Postgres.conf settings will have no effect, no measurable effect at all on database throughput. So instead, we're going to talk about some of the other things you can do which have much larger effects on database throughput and responsiveness. The first one is do less. The fastest database request is the one that you don't make at all. Any time that you're adding a piece of code that's going to work with data, you know, anytime you're referencing the ORM or whatever you say, first of all, do I really need the database to answer that? Is this something that I could be answering in the individual Django session without calling out to the database? Now, one of the things that we're talking about there is caching, of course. I mean, the obvious one is to just look in the results cache and to actually make use of the results cache. That seems obvious, but for some reason people don't do it as much as they could. If the results cache isn't enough because you actually need to share things among several different back ends, then you can use things like Redis and Memcached. Also, don't forget about using CDNs for caching large objects. Even if the large objects are being stored in the database, there's some Django systems out there, people storing images in the database, they're storing compressed data and that sort of thing. Get that data, you know, when you've retrieved it once, copy it out of Postgres, load it up into a CDN, reference it in the CDN by filename instead of retrieving it from the database all the time. Because there are a lot, it's a lot easier to scale a CDN than it is to scale a relational database. So the things that you don't need a relational database for scale them elsewhere. So caching is sort of our first part of thing and do as much caching as you reasonably can with your concurrency and sort of data consistency model. The other thing that we see a lot in doing less is actually some anti-patterns, better exhibited through common mistakes that people make. One of which is polling. Now this is a very simplified example, but this is something I see a lot with salary-based apps and others, which is let's have every back end pull the database as fast as it can. So that is polling for new jobs with no weight or with a little tiny weight like 10 milliseconds. Well, this generates thousands to hundreds of thousands of database requests per second. Frequently the majority of your traffic is not a good thing to do. Another anti-pattern is requesting data you already have. Believe it or not, this is from a real life example. Let's look up users by the user ID and then return the user ID, more or less the SQL equivalent of select ID from users where ID equals something. See that a lot? The database does not need to be in the loop here at all. You can save yourself that request. Also data you don't need. For example, returning an entire table in order to get the first row. Looks great when there's only one row in the table. When there's 10 million rows in the table, it does not work so well. So avoid some of this. If you have very wide tables, use some of the values list methods in order to return only the columns you need, particularly if the table has large objects in it, you know, by day fields, large text, that sort of thing that can make a huge difference. Another thing that we see a lot, a lot is doing joins in the application code. So that is, I've got let's get this list of things and then let's take an ID from that list of things and let's loop over that and request each related set of rows from the database one at a time. This means that if my roster actually has 150 players in it, that I'm going to be actually making 150 separate requests to the database each round trip with its own latency to get those players games. You know, we've spent a good 15, 20 years in Postgres land optimizing joins and trying to make them perform fast. Let us do our job. You know, use the multi-model stuff so that it gets passed down as a join into the back end of the database and have the database return a joined result set instead of doing what amounts to an S loop join in your application. Now, having limited everything and starting to do less, the second thing that we're going to actually look at is, let's get rid of some resource hungry requests or fix them. If you actually do a lot of database performance optimization like I do, one of the things that you discover is that a tiny fraction of the requests against the database consume a vast majority of the system resources and that you really only care about those. Yeah, maybe all of that stuff and that long tail is not as efficient as it could be, but you don't care because it won't make that much of a difference to you fix it. So let's find those. One of the really good tools to actually find these in Postgres is this thing called PG Badger. And what how PG Badger works is that you turn on all of the logging for Postgres, every logging option it's got, and then you collect those logs in after a while and you run it through this program. It is Perl, but you don't have to hack it to use it. And then it provides you with this incredibly detailed report of everything you're doing. And one of the things you really care about for the slow request things is it's got this lovely sort of top query report, you know, slowest individual queries, generally time consuming queries, most frequent queries, etc. So this is like the queries that overall, you know, through repetition or through individually running slow took up the most resources and those are the ones that you want to fix. And then once you've looked at those, you start looking at ways to fix them. One of the big ways is adding indexes because you discover, hey, I'm doing this lookup on this one column that has no indexes all the time and now that my table is a million rows, that's kind of bad. Or fixing your filter expressions, fixing, you know, your searches and that sort of thing so that they can use indexes. Because for example, if you're comparing a date value to something via using date time in Python, then we're not going to be able to use an index on back end Postgres to do that. You're going to have to slurp up the whole table. Just Postgres stats get out of date. That can be because you've got a weird update pattern so autoanalyze isn't keeping up. It could be because you turned auto vacuum slash autoanalyze off, which is a bad idea. And some, you know, or you just bulk loaded a table and you need to run a manual analyze. Some other specialty tips. The Django methods allow you to do a lot of text searching. An important thing to understand is that by default that text searching is not supported by indexes in most databases. Even starts with if your database is not built with C encoding, that if it's built with actual Unicode, then you actually need to create a special index that will support starts with, which is this thing called vericare pattern ops. Look, search on that string, there's more detailed instructions. You'll find by Google how to do that. If you need to do case and sensitive, then you need to either use a function or use case and sensitive text in Postgres. Or for iContains, you'd basically need to look at some of Django's extensions that support Postgres full text search. Now, most people don't use explicit transactions with their Django applications, but some people do if you're dealing, you know, with financial stuff or other issues where you need to actually have atomic multi-statement transactions. You can get into trouble in a few areas. One is don't do write, read, read, read, read, read, read, read, read, read, commit because we're waiting for all of those reads before we do the commit. If those reads don't need to be part of the transaction, take them out. A worse thing is don't do Let's begin, let's write some stuff, let's read some stuff. Now let's render some pages for the user to look at. Oh, now let's commit the transaction. And the worst would be, let's wait for a user response and now send another query in the same transaction. Because while that's happening, what's happening on the database servers, we have what's called an idle transaction. And idle transactions pin down resources, especially locks. And the issue with locks is that locks can block other activity. This is a quick query check to check for queries that are being blocked by locks. And the terrible thing about being blocked by locks is there is no amount of system resources you can throw at things that will make stuff better because locks are self-limiting. So this is something to look at and something to think about if you're doing concurrent writes. Now, second portion is, let's get some adequate hardware. You notice I'm not saying the best hardware. Because really the best hardware is the hardware that is fast enough for what you need and no more. Because you don't want to spend extra money for performance you don't actually need. Now the corollary to this is that at its best, postgres will be as fast as your hardware. We can't be faster than your hardware. If you're throwing it on an AWS T1 Tiny, do not expect to serve 25,000 requests per second. It's not going to happen. One of the areas where people tend to under-resource chronically is IO. Partly because in hosting and virtual hosting, the various hosts make IO your most expensive resource. And for that reason, people tend to under-allocate it. And as a result, they end up with crappy performance even though they have plenty of CPU and RAM left available. Postgres writes stuff all the time. Obviously stuff like writes and commits. But even on a read mostly workload, Postgres is doing things like writing to support replication, doing this thing called hint bits, which I don't want to explain, but it does involve a lot of constant background writes, writing statistics about what's in the database. So if you are limited by IOPS and throughput on your system, that is going to limit Postgres performance. So get adequate IO. Examples here. If you're on your own hardware, just go ahead and move to SSDs if you haven't already. There's no good reason not to. They're not even more expensive anymore. If you're in the cloud, look at your IOPS allocation and the storage that you're at. Again, increasing it is not that expensive and can make an order of magnitude difference in performance. Also for stuff. Anybody here still using the EXT3 on Linux? Thank goodness. There's also been some Linux kernel issues in the recent past. You can read about these that make for terrible IO performance. Now in terms of RAM, it's completely thresholded. You basically have sort of your three thresholds of RAM for a functional system. One is that you can cache the data that you need most of the time. The second is that you can cache the whole database. And the third is that it fits in Postgres as dedicated cache, which is a minority of RAM. Where this fits into allocating RAM is if you're in one of these sort of thresholds, and by getting just a little bit more RAM, you could actually move up to the better threshold, it's generally worth doing. Some tips here for Amazon Web Services. Use the currently general provision to overallocate the heck out of your storage. And you will actually get better throughput than you do with other options. Postgres as a service, which is offered by various companies, Gondor, Heroku, et cetera, that saves you an administration. It doesn't help you with performance, necessarily. So if you are actually performance constrained by the database, then you might end up actually branching off on your own. Oh, and make sure that your app servers and Postgres are in the same availability zone, because latency can kill you. Let's talk a little bit about scaling infrastructure, because it's assumed that you've actually got adequate hardware, and that's not doing it for you. And we actually need to scale infrastructure out. So here's our first sort of easy things. One is, use the latest version of PostgresQL. We put performance improvements of various kinds in every release, so upgrading is worthwhile. And make sure Postgres is running in its own server instance. Databases tend to use all the resources, which means they don't share well with other kinds of applications. Then the other thing is, use PG Bouncer with connection pooling, specifically with transaction pooling, with your application. Because on Postgres, extra connections, even if they're idle, cost resources. And so if you have hundreds of extra connections, you're paying for that. The way PG Bouncer works is it's an event-based pooler that connections come in, but they only get allocated a real database connection when you actually have a query to run, or when you're in a transaction. And that saves you a lot of resources on the database side. If that's not enough, then if you have replicas anyway, for redundancy, let's look at load balancing some of those read requests to your replicas. And load balancing requires some kind of a proxy. There are various third-party proxies out there. You can use PG Bouncer kind of in this way, HA proxy, that sort of thing. But really, the easiest way is to actually just use Jangle Routes. And it's the most effective way, because only within the application code do you know if you're about to do a reader or a write. And for that reason, using Jangle Routes and actually having a read connection and a write connection is going to help you a lot for this. Now, you can load balance read. You can load balance generically, but it's much more efficient to actually load balance specific portions of your workload. For example, if you can move any sort of large reports and analytics you do off onto a separate server, a separate read replica, you can actually, Postgre's performance optimizes on its own a lot better when it has a consistent workload. And then you can actually even do some manual tuning for that consistent workload, as opposed to a mix and completely chaotic workload. And so moving, say, reporting off onto its own machine, moving the machine where you're actually pulling entire tables in order to refresh cache onto its own read replica, moving queuing, if anybody's doing like celery backed by Postgre or whatever, that has its own very specific access pattern that's a lot easier for Postgre's to cope with if that's all that that particular instance of Postgre's is doing. Now, having gone through all the stuff to scale your infrastructure, I am actually going to have some notes on postgre's.conf, but I'm doing this last because, like I said, it really is actually the least important thing. So I've got some configuration stuff in here. I will put up the slides later on, so you don't actually need to write this out. There's basically, Postgre's has 230 some configuration variables. You only care about a tiny handful of these. Here's a few of them. One is, we can't determine in Postgre's automatically how much RAM is available to Postgre's. So there's a few settings that you need to configure based on the amount of RAM available to Postgre's. One of them is shared buffers, which is Postgre's dedicated cache should be about a quarter of RAM. Work memory is non-shared memory limits for doing per query operations like sorts. Again, 8 to 32 megabytes for your basic web application, maybe 128 to 1 gigabyte for a reporting analytics application, don't overallocate so that you don't actually run out of RAM. The reason we have a limit there is because you don't want to run out of RAM. Other ones are simpler, effective cache size. Just basically tells Postgre's how much space you have for caching. So three quarters of RAM. We've got one in there called wall buffers. It's a complicated explanation, but just set it to 64 gigabytes. Maintenance work memory is the memory available for things like auto-vacuum model analyze. There are some settings in there that determine the size and the rate of refresh for the transaction log. The defaults for this are kind of low, at least until Postgre's 9.5 comes out. And so you generally want to actually bump them up. And a few other settings, moving stats, the statistics to a RAM desk can improve responsiveness a lot, especially in the cloud. For SSDs and for cloud, you actually want to decrease random page cost, which is the cost factor of Postgre's looks in, am I going to use an index or am I going to scan the table? Same thing with effective IO concurrency. This, by the way, I just put in here for, I said, turn on all the logging settings for PG Badger. This is that set of settings. And it's in the slide so that you know it for later. So recap to get Postgre performance. Number one, do less querying. Fix your resource hungry requests. Get adequate hardware. Scale your infrastructure. And then finally, if you've done all of those things or if you're waiting for some of those things, do the configuration file. So questions. You have three minutes for questions. So if anyone has a question. Nobody? Everything's running as fast as you could possibly want? Are there any performance hits for all that logging? There is performance overhead involved in the logging because you're doing a lot of writing. The performance overhead is variable depending on how many queries you actually running per second, how long are those queries, that sort of thing. And is the activity log being stored on the same IO resource as the rest of the database? So I've seen that overhead be anywhere from not measurable to if you are already close to IO saturated and it's on the same resource as the database to actually causing serious problems. So it varies. Could you expand a little more on analyzing and vacuuming and auto-analyzing and auto-vacuuming and what? I know there's some table you can query that shows you the last time that was performed on certain tables and what sort of stuff should I be looking for? Yeah, so there's two parts of this. Vacuuming is garbage collection for PostQuest. What we're doing is we're garbage collecting all of the rows that are dead because they've been replaced by new rows that are deleted. And cleaning up some other stuff, cleaning up their index reference and that sort of thing. And we do that asynchronously deferred because we don't want user requests to wait on that vacuum. But if vacuum can't keep up for some reason, then you end up getting what we call bloated tables and bloated indexes that have a lot of dead space in them that hasn't been collected. Now, analyze is updating the PostQuest statistics about what's in your table so that we can actually plan to execute your queries in the fastest way. Now, both of these things are normally handled by something called the auto-vacuumed demon that uses a threshold algorithm to determine when it needs to vacuum and analyze various tables. But if you have an atypical usage pattern, sometimes the auto-vacuumed demon doesn't recognize when it needs to work on things, or if you just have an enormous amount of activity under the default settings, the auto-vacuumed demon might not keep up. And you just need to bump up the number of workers in their frequency of work. How do you identify that, like if it's not keeping up? So bloated tables, and search for PostQuest bloated tables, and some query examples for how you find it. And if you're seeing a lot of queries, and unfortunately you have to get into sort of analyzing the queries to find out the queries are bad because your stats are bad. OK, thanks. Yeah. You mentioned no more EXT3. Do you have a preferred recommendation for a file system for a PostQuest instance? And does that decision change whether you're talking about physical disk or SSD? It doesn't change in terms of physical disk versus SSD. But I generally, so I use XFS for smaller transactional databases if I have a choice. And I use ZFS a lot for data warehousing analytics. And the reason for that is that ZFS has a lot of nice tools for volume extension and copying and that sort of thing. But it tends to be a little slow on small writes. And EXT4 is fine if that's what's installed, and you have to get special permission to use XFS. You mentioned SSDs if you're running your own hardware. But cloud providers are also offering SSDs. So you weren't saying not to do it there? Well, yeah, I meant actually hardware versus virtual hardware versus AWS, et cetera. If you need to use SSDs, go ahead and do it. Yes, yeah. There used to be some sort of tradeoff versus HDD versus SSD. These days, the only reason why you would use an HDD is if you have large volumes of data, as in multiple terabytes, and you're willing to live with it being slow in order to save some money. That would be the only reason to use spinning disk these days. I come from a sad world of Microsoft where index fragmentation matters. And I was wondering if that matters in Postgres and how you deal with it. Yes, it does. It does. And there's a couple of things there. One is if Postgres is being stored in a SAN or other network share, give it its own partition so that you get less fragmentation in interleaving with other files stored on the SAN, because that can be actually pretty bad. The other thing is to reduce fragmentation. There aren't really a lot of other things you can do to reduce fragmentation except for changing how data gets into the database, which is obviously an application change, or obviously recopying stuff. So that is actually one of the problems with running Postgres and NTFS. For whatever reason, because of how NTFS writes files, fragmentation is getting much worse than it is on any of the Linux file systems. And we haven't honestly really looked into why, because our population of people who run Postgres and Windows and care about performance is pretty small. OK, that's all the time we have. Thank you. Thank you very much.
 I'm going to talk a little bit about some basic things you can do to improve Postgres  performance for your Django application. We've got our running elephant here. Few people  know that elephants can run at 20 to 25 miles an hour. They're actually quite fast. And Postgres  can do thousands of requests per second. So if you're not getting thousands of requests  per second, there's probably a reason why. So what you do is you log into Postgres and  you go into postgresql.conf and you set the hidden parameter go faster to 10 and then  you save it and you're done. Okay, so we're done here. We got questions? Now, seriously,  unfortunately, it's not that easy. We actually spend, in Postgres, Postgres development world,  we spend a lot of time talking about how can we make things faster. As a matter of fact,  we probably spend more time talking about that than just about anything else except  maybe the commit queue and who's turned it is to review things. And as a result, if there  was something that we could do by default to make Postgres faster, we probably already  did it. So the things that you're going to do to make Postgres faster are going to require  work. You can't just change a few configuration settings. In fact, one of the things that  I do to get paid is I do a lot of performance tuning and performance engineering on different  sites. And this is more or less how I spend my time. It varies a lot per site, but more  or less how I spend my time. And you'll notice that tuning the configuration is a pretty  small minority of how I spend my time. And the effect that tuning the configuration has  is an even smaller minority. As a matter of fact, sometimes and in some environments,  changing the Postgres.conf settings will have no effect, no measurable effect at all on  database throughput. So instead, we're going to talk about some of the other things you  can do which have much larger effects on database throughput and responsiveness. The first one  is do less. The fastest database request is the one that you don't make at all. Any time  that you're adding a piece of code that's going to work with data, you know, anytime  you're referencing the ORM or whatever you say, first of all, do I really need the database  to answer that? Is this something that I could be answering in the individual Django session  without calling out to the database? Now, one of the things that we're talking about  there is caching, of course. I mean, the obvious one is to just look in the results cache and  to actually make use of the results cache. That seems obvious, but for some reason people  don't do it as much as they could. If the results cache isn't enough because you actually  need to share things among several different back ends, then you can use things like Redis  and Memcached. Also, don't forget about using CDNs for caching large objects. Even if the  large objects are being stored in the database, there's some Django systems out there, people  storing images in the database, they're storing compressed data and that sort of thing. Get  that data, you know, when you've retrieved it once, copy it out of Postgres, load it  up into a CDN, reference it in the CDN by filename instead of retrieving it from the  database all the time. Because there are a lot, it's a lot easier to scale a CDN than  it is to scale a relational database. So the things that you don't need a relational database  for scale them elsewhere. So caching is sort of our first part of thing and do as much  caching as you reasonably can with your concurrency and sort of data consistency model. The other  thing that we see a lot in doing less is actually some anti-patterns, better exhibited through  common mistakes that people make. One of which is polling. Now this is a very simplified  example, but this is something I see a lot with salary-based apps and others, which is  let's have every back end pull the database as fast as it can. So that is polling for  new jobs with no weight or with a little tiny weight like 10 milliseconds. Well, this generates  thousands to hundreds of thousands of database requests per second. Frequently the majority  of your traffic is not a good thing to do. Another anti-pattern is requesting data you  already have. Believe it or not, this is from a real life example. Let's look up users by  the user ID and then return the user ID, more or less the SQL equivalent of select ID from  users where ID equals something. See that a lot? The database does not need to be in  the loop here at all. You can save yourself that request. Also data you don't need. For  example, returning an entire table in order to get the first row. Looks great when there's  only one row in the table. When there's 10 million rows in the table, it does not work  so well. So avoid some of this. If you have very wide tables, use some of the values list  methods in order to return only the columns you need, particularly if the table has large  objects in it, you know, by day fields, large text, that sort of thing that can make a huge  difference. Another thing that we see a lot, a lot is doing joins in the application code.  So that is, I've got let's get this list of things and then let's take an ID from that  list of things and let's loop over that and request each related set of rows from the  database one at a time. This means that if my roster actually has 150 players in it,  that I'm going to be actually making 150 separate requests to the database each round  trip with its own latency to get those players games. You know, we've spent a good 15, 20  years in Postgres land optimizing joins and trying to make them perform fast. Let us do  our job. You know, use the multi-model stuff so that it gets passed down as a join into  the back end of the database and have the database return a joined result set instead  of doing what amounts to an S loop join in your application. Now, having limited everything  and starting to do less, the second thing that we're going to actually look at is, let's  get rid of some resource hungry requests or fix them. If you actually do a lot of database  performance optimization like I do, one of the things that you discover is that a tiny  fraction of the requests against the database consume a vast majority of the system resources  and that you really only care about those. Yeah, maybe all of that stuff and that long  tail is not as efficient as it could be, but you don't care because it won't make that  much of a difference to you fix it. So let's find those. One of the really good tools to  actually find these in Postgres is this thing called PG Badger. And what how PG Badger works  is that you turn on all of the logging for Postgres, every logging option it's got,  and then you collect those logs in after a while and you run it through this program.  It is Perl, but you don't have to hack it to use it. And then it provides you with this  incredibly detailed report of everything you're doing. And one of the things you really care  about for the slow request things is it's got this lovely sort of top query report,  you know, slowest individual queries, generally time consuming queries, most frequent queries,  etc. So this is like the queries that overall, you know, through repetition or through individually  running slow took up the most resources and those are the ones that you want to fix. And  then once you've looked at those, you start looking at ways to fix them. One of the big  ways is adding indexes because you discover, hey, I'm doing this lookup on this one column  that has no indexes all the time and now that my table is a million rows, that's kind of  bad. Or fixing your filter expressions, fixing, you know, your searches and that sort of thing  so that they can use indexes. Because for example, if you're comparing a date value  to something via using date time in Python, then we're not going to be able to use an  index on back end Postgres to do that. You're going to have to slurp up the whole table.  Just Postgres stats get out of date. That can be because you've got a weird update pattern  so autoanalyze isn't keeping up. It could be because you turned auto vacuum slash autoanalyze  off, which is a bad idea. And some, you know, or you just bulk loaded a table and you need  to run a manual analyze. Some other specialty tips. The Django methods allow you to do a  lot of text searching. An important thing to understand is that by default that text  searching is not supported by indexes in most databases. Even starts with if your database  is not built with C encoding, that if it's built with actual Unicode, then you actually  need to create a special index that will support starts with, which is this thing called vericare  pattern ops. Look, search on that string, there's more detailed instructions. You'll  find by Google how to do that. If you need to do case and sensitive, then you need to  either use a function or use case and sensitive text in Postgres. Or for iContains, you'd  basically need to look at some of Django's extensions that support Postgres full text  search. Now, most people don't use explicit transactions with their Django applications,  but some people do if you're dealing, you know, with financial stuff or other issues  where you need to actually have atomic multi-statement transactions. You can get into trouble in a  few areas. One is don't do write, read, read, read, read, read, read, read, read, read,  commit because we're waiting for all of those reads before we do the commit. If those  reads don't need to be part of the transaction, take them out. A worse thing is don't do  Let's begin, let's write some stuff, let's read some stuff.  Now let's render some pages for the user to look at.  Oh, now let's commit the transaction.  And the worst would be, let's wait for a user response  and now send another query in the same transaction.  Because while that's happening, what's happening on the  database servers, we have what's called an idle transaction.  And idle transactions pin down resources, especially locks.  And the issue with locks is that locks can block other  activity.  This is a quick query check to check for queries that are  being blocked by locks.  And the terrible thing about being blocked by locks is  there is no amount of system resources you can throw at  things that will make stuff better because locks are  self-limiting.  So this is something to look at and something to think  about if you're doing concurrent writes.  Now, second portion is, let's get some adequate hardware.  You notice I'm not saying the best hardware.  Because really the best hardware is the hardware that is  fast enough for what you need and no more.  Because you don't want to spend extra money for  performance you don't actually need.  Now the corollary to this is that at its best, postgres  will be as fast as your hardware.  We can't be faster than your hardware.  If you're throwing it on an AWS T1 Tiny, do not expect to  serve 25,000 requests per second.  It's not going to happen.  One of the areas where people tend to under-resource  chronically is IO.  Partly because in hosting and virtual hosting, the various  hosts make IO your most expensive resource.  And for that reason, people tend to under-allocate it.  And as a result, they end up with crappy performance even  though they have plenty of CPU and RAM left available.  Postgres writes stuff all the time.  Obviously stuff like writes and commits.  But even on a read mostly workload, Postgres is doing  things like writing to support replication, doing this  thing called hint bits, which I don't want to explain, but  it does involve a lot of constant background writes,  writing statistics about what's in the database.  So if you are limited by IOPS and throughput on your  system, that is going to limit Postgres performance.  So get adequate IO.  Examples here.  If you're on your own hardware, just go ahead and move to  SSDs if you haven't already.  There's no good reason not to.  They're not even more expensive anymore.  If you're in the cloud, look at your IOPS allocation and the  storage that you're at.  Again, increasing it is not that expensive and can make an  order of magnitude difference in performance.  Also for stuff.  Anybody here still using the EXT3 on Linux?  Thank goodness.  There's also been some Linux kernel issues in the recent  past.  You can read about these that make for  terrible IO performance.  Now in terms of RAM, it's completely thresholded.  You basically have sort of your three thresholds of RAM for  a functional system.  One is that you can cache the data that you need most of  the time.  The second is that you can cache the whole database.  And the third is that it fits in Postgres as dedicated  cache, which is a minority of RAM.  Where this fits into allocating RAM is if you're in one of  these sort of thresholds, and by getting just a little bit  more RAM, you could actually move up to the better  threshold, it's generally worth doing.  Some tips here for Amazon Web Services.  Use the currently general provision to overallocate the  heck out of your storage.  And you will actually get better throughput than you do  with other options.  Postgres as a service, which is offered by various  companies, Gondor, Heroku, et cetera, that saves you an  administration.  It doesn't help you with performance, necessarily.  So if you are actually performance constrained by  the database, then you might end up actually branching off  on your own.  Oh, and make sure that your app servers and Postgres are in  the same availability zone, because latency can kill you.  Let's talk a little bit about scaling infrastructure, because  it's assumed that you've actually got adequate hardware,  and that's not doing it for you.  And we actually need to scale infrastructure out.  So here's our first sort of easy things.  One is, use the latest version of PostgresQL.  We put performance improvements of various kinds in every  release, so upgrading is worthwhile.  And make sure Postgres is running in its own server  instance.  Databases tend to use all the resources, which means they  don't share well with other kinds of applications.  Then the other thing is, use PG Bouncer with connection  pooling, specifically with transaction pooling, with  your application.  Because on Postgres, extra connections, even if they're  idle, cost resources.  And so if you have hundreds of extra connections, you're  paying for that.  The way PG Bouncer works is it's an event-based pooler  that connections come in, but they only get allocated a  real database connection when you actually have a query to  run, or when you're in a transaction.  And that saves you a lot of resources on the database side.  If that's not enough, then if you have replicas anyway, for  redundancy, let's look at load balancing some of those read  requests to your replicas.  And load balancing requires some kind of a proxy.  There are various third-party proxies out there.  You can use PG Bouncer kind of in this way, HA proxy, that  sort of thing.  But really, the easiest way is to actually just use  Jangle Routes.  And it's the most effective way, because only within the  application code do you know if you're about to do a  reader or a write.  And for that reason, using Jangle Routes and actually  having a read connection and a write connection is going to  help you a lot for this.  Now, you can load balance read.  You can load balance generically, but it's much  more efficient to actually load balance specific portions of  your workload.  For example, if you can move any sort of large reports and  analytics you do off onto a separate server, a separate  read replica, you can actually, Postgre's performance  optimizes on its own a lot better when it has a consistent  workload.  And then you can actually even do some manual tuning for  that consistent workload, as opposed to a mix and completely  chaotic workload.  And so moving, say, reporting off onto its own machine,  moving the machine where you're actually pulling entire  tables in order to refresh cache onto its own read replica,  moving queuing, if anybody's doing like celery backed by  Postgre or whatever, that has its own very specific access  pattern that's a lot easier for Postgre's to cope with if  that's all that that particular instance of  Postgre's is doing.  Now, having gone through all the stuff to scale your  infrastructure, I am actually going to have some notes on  postgre's.conf, but I'm doing this last because, like I said,  it really is actually the least important thing.  So I've got some configuration stuff in here.  I will put up the slides later on, so you don't actually need  to write this out.  There's basically, Postgre's has 230 some configuration  variables.  You only care about a tiny handful of these.  Here's a few of them.  One is, we can't determine in Postgre's automatically how  much RAM is available to Postgre's.  So there's a few settings that you need to configure based  on the amount of RAM available to Postgre's.  One of them is shared buffers, which is Postgre's  dedicated cache should be about a quarter of RAM.  Work memory is non-shared memory limits for doing per  query operations like sorts.  Again, 8 to 32 megabytes for your basic web application,  maybe 128 to 1 gigabyte for a reporting analytics  application, don't overallocate so that you don't  actually run out of RAM.  The reason we have a limit there is because you don't want  to run out of RAM.  Other ones are simpler, effective cache size.  Just basically tells Postgre's how much space you have for  caching.  So three quarters of RAM.  We've got one in there called wall buffers.  It's a complicated explanation, but just set it to 64  gigabytes.  Maintenance work memory is the memory available for things  like auto-vacuum model analyze.  There are some settings in there that determine the size  and the rate of refresh for the transaction log.  The defaults for this are kind of low, at least until  Postgre's 9.5 comes out.  And so you generally want to actually bump them up.  And a few other settings, moving stats, the statistics  to a RAM desk can improve responsiveness a lot,  especially in the cloud.  For SSDs and for cloud, you actually want to decrease  random page cost, which is the cost factor of Postgre's  looks in, am I going to use an index or am I going to scan  the table?  Same thing with effective IO concurrency.  This, by the way, I just put in here for, I said, turn on  all the logging settings for PG Badger.  This is that set of settings.  And it's in the slide so that you know it for later.  So recap to get Postgre performance.  Number one, do less querying.  Fix your resource hungry requests.  Get adequate hardware.  Scale your infrastructure.  And then finally, if you've done all of those things or if  you're waiting for some of those things, do the  configuration file.  So questions.  You have three minutes for questions.  So if anyone has a question.  Nobody?  Everything's running as fast as you could possibly want?  Are there any performance hits for all that logging?  There is performance overhead involved in the logging  because you're doing a lot of writing.  The performance overhead is variable depending on how  many queries you actually running per second, how long are  those queries, that sort of thing.  And is the activity log being stored on the same IO  resource as the rest of the database?  So I've seen that overhead be anywhere from not measurable  to if you are already close to IO saturated and it's on the  same resource as the database to actually causing  serious problems.  So it varies.  Could you expand a little more on analyzing and vacuuming  and auto-analyzing and auto-vacuuming and what?  I know there's some table you can query that shows you the  last time that was performed on certain tables and what  sort of stuff should I be looking for?  Yeah, so there's two parts of this.  Vacuuming is garbage collection for PostQuest.  What we're doing is we're garbage collecting all of the  rows that are dead because they've been replaced by new  rows that are deleted.  And cleaning up some other stuff, cleaning up their  index reference and that sort of thing.  And we do that asynchronously deferred because we don't want  user requests to wait on that vacuum.  But if vacuum can't keep up for some reason, then you end up  getting what we call bloated tables and bloated indexes  that have a lot of dead space in them that  hasn't been collected.  Now, analyze is updating the PostQuest statistics about  what's in your table so that we can actually plan to execute  your queries in the fastest way.  Now, both of these things are normally handled by something  called the auto-vacuumed demon that uses a threshold  algorithm to determine when it needs to vacuum and analyze  various tables.  But if you have an atypical usage pattern, sometimes the  auto-vacuumed demon doesn't recognize when it needs to work  on things, or if you just have an enormous amount of  activity under the default settings, the auto-vacuumed  demon might not keep up.  And you just need to bump up the number of workers in their  frequency of work.  How do you identify that, like if it's not keeping up?  So bloated tables, and search for PostQuest bloated tables,  and some query examples for how you find it.  And if you're seeing a lot of queries, and unfortunately  you have to get into sort of analyzing the queries to find  out the queries are bad because your stats are bad.  OK, thanks.  Yeah.  You mentioned no more EXT3.  Do you have a preferred recommendation for a file  system for a PostQuest instance?  And does that decision change whether you're talking about  physical disk or SSD?  It doesn't change in terms of physical disk versus SSD.  But I generally, so I use XFS for smaller  transactional databases if I have a choice.  And I use ZFS a lot for data warehousing analytics.  And the reason for that is that ZFS has a lot of nice tools  for volume extension and copying and that sort of thing.  But it tends to be a little slow on small writes.  And EXT4 is fine if that's what's installed, and you have  to get special permission to use XFS.  You mentioned SSDs if you're running your own hardware.  But cloud providers are also offering SSDs.  So you weren't saying not to do it there?  Well, yeah, I meant actually hardware versus virtual  hardware versus AWS, et cetera.  If you need to use SSDs, go ahead and do it.  Yes, yeah.  There used to be some sort of tradeoff versus  HDD versus SSD.  These days, the only reason why you would use an HDD is if  you have large volumes of data, as in multiple terabytes,  and you're willing to live with it being slow in order to  save some money.  That would be the only reason to use  spinning disk these days.  I come from a sad world of Microsoft where index  fragmentation matters.  And I was wondering if that matters in Postgres and how  you deal with it.  Yes, it does.  It does.  And there's a couple of things there.  One is if Postgres is being stored in a SAN or other  network share, give it its own partition so that you get  less fragmentation in interleaving with other files  stored on the SAN, because that can be actually pretty bad.  The other thing is to reduce fragmentation.  There aren't really a lot of other things you can do to  reduce fragmentation except for changing how data gets into  the database, which is obviously an application  change, or obviously recopying stuff.  So that is actually one of the problems with running  Postgres and NTFS.  For whatever reason, because of how NTFS writes files,  fragmentation is getting much worse than it is on any of  the Linux file systems.  And we haven't honestly really looked into why, because our  population of people who run Postgres and Windows and care  about performance is pretty small.  OK, that's all the time we have.  Thank you.  Thank you very much.
DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_0_155.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_156_287.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_288_395.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_396_1211.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_1212_1943.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_1944_2723.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_2724_3899.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_3900_6827.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_6828_9203.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_9204_10667.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_10668_11015.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_11016_17159.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_17160_18815.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_18816_19355.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_19356_21095.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_21096_24323.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_24324_25403.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_25404_27515.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_27516_29507.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_29508_31187.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_31188_37463.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_37464_37847.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_37848_39551.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_39552_39599.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_39600_40415.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_40416_40475.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_40476_40523.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_40524_40571.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_40572_40655.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_40656_40739.png DjangoCon_US_2015_-_Postgres_Performance_in_15_Minutes_by_Josh_Berkus_dBeXS5aFLNc_40740_40743.png