 I'm going to talk a little bit about some basic things you can do to improve Postgre's 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 Postgre's 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 Postgre's 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 Postgre's 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 whose turn it is to review things. And as a result, if there was something that we could do by default to make Postgre's faster, we probably already did it. So the things that you're going to do to make Postgre's 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 Postgre's.conf settings will have 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, any time 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 backends, 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, 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 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 pulling 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. This 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 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, by day fields, large text, that sort of thing. It 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. 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, 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 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 in 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 if 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 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, slowest individual queries, generally time consuming queries, most frequent queries, etc. So this is like the queries that overall 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 look up 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 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 and back end Postgres to do that. You're going to have to slurp up the whole table. Sometimes 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, or you just bulk loaded a table and 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 in coding, 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 veracare pattern ops. Look, search on that string, there's more detailed instructions. You'll find by Google on how to do that. If you need to do case insensitive, then you need to either use a function or use case insensitive 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 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 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 worst thing is don't do begin let's write some stuff, let's read some stuff. Now let's render some pages for the user to look at. 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, right? 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 HintBits, 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 AXT3 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. And 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 over-allocate 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. Let's assume 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 that 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. Now 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 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 postgres 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 in 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 postgres or whatever, that has its own very specific access pattern that's a lot easier for postgres to cope with if that's all that that particular instance of postgres is doing. Now, having gone through all the stuff to scale your infrastructure, I am actually going to have some notes on postgres.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 read this out. There's basically, postgres 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 postgres automatically how much RAM is available to postgres. So there's a few settings that you need to configure based on the amount of RAM available to postgres. One of them is shared buffers, which is postgres' 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 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 postgres how much space you have for caching. So three quarters of RAM. We've got one in there called wall buffers, and it's a complicated explanation, but just set it to 64 gigabytes. Maintenance work memory is the memory available for things like auto-vacuum, auto-analyze. There are some settings that determine the size and the rate of refresh for the transaction log. The defaults for this are kind of low, at least until postgres 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 postgres 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 postgres 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. We 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. That performance overhead is variable depending on how many queries you're 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 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-vacuum 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-vacuum 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-vacuum 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, 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. You mentioned no more EXT-3. 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 EXT-4 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 or it's not? Well, yeah, I meant actually hardware versus virtual hardware versus AWS, et cetera. So if you have the opportunity to use SSDs, go ahead and do it. Yes, yeah. There used to be some sort of trade-off 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 desk 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 like 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.