Postgres Performance in 15 Minutes

Speaker 1: Hey everybody. Um I'm uh I'm gonna 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. Um and Postgres can do thousands of requests per second. So if you're not getting thousands of per requests per second, there's probably a reason why. So, uh 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.

Speaker 1: Okay, so we're done here. We got questions? Um no, seriously, unfortunately it's not that easy. Um the 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 uh 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 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.

Speaker 1: 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 the tuning of 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.

Speaker 1: The first one is do less. The fastest database request is the one that you don't make at all. Anytime that you're adding a piece of code that's going to work with 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. Um 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.

Speaker 1: 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 Um, 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 file name instead of retrieving it from the database all the time. Because there are a lot uh 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.

Speaker 1: 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 backend 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.

Speaker 1: Frequently the majority of your traffic. It's not 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. Um 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 Works great when there's only one row in the table. When there's ten million rows in the table, it does not work so well.

Speaker 1: Um 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, byte A 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'

Speaker 1: 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. 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 a 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.

Speaker 1: 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 what how how PG Badger works is that you turn on all of the logging for Postgres Um every logging option it's got. And then you collect those logs and 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, et cetera. So this is like

Speaker 1: 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. Um or fixing your filter expressions, fixing um uh 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 backend Postgres

Speaker 1: to do that. You're gonna 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 auto-analyze isn't keeping up. It could be because you turned auto vacuum slash auto-analyze off, which is a bad idea. 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, um

Speaker 1: Then you actually need to create a special index that will support starts with, which is this thing called Varecare PatternOps. Look, search on that string, there's 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, um 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 multistatement transactions, you can get into trouble in a few areas. Um

Speaker 1: one is um 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 worst thing is don't do begin, you know, let's uh 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 server is we have what's called an idle transaction. And idle transactions pin down resources, especially locks.

Speaker 1: 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 rights. 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

Speaker 1: 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. Um one of the areas where people tend to under-resource chronically is I. O. Um partly because In hosting and virtual hosting, the various hosts make I. O. your most expensive resource, and for that reason, people tend to underallocate it. And as a result, they end up with crappy performance even though they have plenty of CPU and RAM left available. Postgres write stuff all the time. You know, obviously stuff like writes and commits, but even on a read-mostly workload, Postgres

Speaker 1: is doing things like writing to support replication. uh doing this thing called hint bits, which I don't want to explain, but it does involve a lot of sort 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 I. O. Examples here. If you're on your own hardware, um 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 a quantum a an order magnitude difference in performance. Also for stuff. Anybody here still using

Speaker 1: ext3 um on Linux? Thank goodness. Um There's also been some Linux kernel issues in the recent past. You can read about these that make for terrible I. O. performance. Um now in terms of RAM, it's completely thresholded. Um 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 's dedicated cache, which is a minority of RAM. The um and you know 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

Speaker 1: Some tips here for Amazon Web Services. Use the currently generally provisioned, 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, Ahoroku, etc. 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 know you might end up actually branching off on your own. Um oh, and make sure that your app servers and uh Postgres are in the same uh uh availability zone um because latency can kill you.

Speaker 1: Uh let's talk a little bit about scaling infrastructure. Like I'm 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 Postgres QL. We put performance improvements of various kinds in every release. So upgrading is worthwhile. And make sure Postgres is running on its own server instance. Um 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.

Speaker 1: The way PGBouncer 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. Um the um and uh you know the now Load balancing has requires some kind of a proxy. Um 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 Django

Speaker 1: routes. Um And it's the most effective way because only within the application code do you know if you're about to do a read or a write. And for that reason, using Django 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 mixed and completely chaotic workload.

Speaker 1: 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 this 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. Um so I've got some configuration stuff in here. Um I will put up the slides later on um so you don't actually need to write this out. There's basically Postgres has 230 some configuration variables.

Speaker 1: You only care about a tiny handful of these. Um 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. Um, you know, again, 8 to 32 megabytes for your basic web application, maybe 128 to 1 gigabyte for a reporting analytics application. Um 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.

Speaker 1: Uh other ones are simpler, effective cache size just basically tells Postgres how much space you have for caching. Um so uh three-quarters of RAM. Um we've got one in there called wall buffers, and that's Uh it's a complicated explanation, but just set it to 64 gigabytes. Uh maintenance work memory is the memory available for things like auto vacuum auto-analyse. 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 Postgres 9. 5 comes out. Um and so you generally want to actually bump them up. Um and a few other settings, moving stats, the statistics to a RAM disk um can improve responsiveness a lot, especially in the cloud.

Speaker 1: 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 I/O 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 slides so that you know it for later. So , recap uh to get Postgre performance. Number one, do less querying, fix your resource hungry requests. Get adequate hardware, scale your infrastructure, and then finally, you know, if you've done all of those things or if you're waiting for some of those things, tune the configuration file. So questions

Speaker 2: We have three minutes for questions, so if anyone has a question.

Speaker 1: Nobody? Everything's running as fast as you could possibly want.

Speaker 3: Are there any performance hits for all that logging?

Speaker 1: Uh there 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 I/O resource? as the rest of the database. So I've seen that overhead be anywhere from not measurable to, you know, if you are already close to IO-saturated and it's on the same resource as the database. to actually causing serious problems. Um so uh it varies

Speaker 4: Could you expand a little more on analyzing and vacuuming and auto-analyzing and auto-vacuing and um what You know, when you I I know there's uh some table you can query that shows you the last time that was performed on certain tables and What kind of stuff should I be looking at?

Speaker 1: Yeah, so there's there's two parts of this. Um vacuuming is deferred is garbage collection for Postgres. What we're doing is we're garbage collecting all of the rows that are dead because they've been replaced by new rows or deleted. um and cleaning up some other stuff, cleaning up the 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 Postgres statistics about what's in your tables so that we can actually plan

Speaker 1: to execute your queries in the fastest way. Now both of these things are normally handled by something called the autovacuum daemon that uses um I that uses uh threshold algorithm to determine when it needs to vacuum and analyze various tables. But if you have an atypical usage pattern, sometimes the autovacuum 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 autovacuum daemon might not keep up, and you just need to bump up the number of workers and their frequency of work.

Speaker 4: How how do you identify that? Like if it's not keeping up?

Speaker 1: So bloated tables and search for Postgres bloated tables and this is some query examples for how you find it. If you're seeing a lot of queries, and unfortunately you have to get into sort of analyzing the queries to find out that the queries are bad because your stats are bad. Okay, thanks. Yeah

Speaker 5: You mentioned no more uh ext3. Do you have a preferred recommendation for a file system for a Postgres instance? And does that decision change whether you're talking about physical disk or SSD?

Speaker 1: Yeah. 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. Um and ext4 is fine if that's what's installed and you have to get special permission to use XFS. Thank you. Hi

Speaker 6: there. You mentioned SSDs if you're running your own hardware, but cloud providers are also provide uh offering SSDs.

Speaker 1: Yeah.

Speaker 6: So you weren't saying n not to do it there or it's not a question.

Speaker 1: Well yeah, I meant I meant actually hardware versus versus virtual hardware versus um AWS, et cetera. Yes, yeah. The it there used to be some sort of trade-off versus HTD 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. Um

Speaker 7: I come from a sad world of Microsoft where uh index fragmentation matters, and I was wondering if that matters in Postgres and how you deal with it.

Speaker 1: Yes it does. Um it does uh and um the Um there's a couple of things there. One is um put if if Postgres is being stored in like a SAN or other network share, give it its own partition. Um so that you get less fragmentation and interleaving with other files stored on the SAN , because that can be actually pretty bad. Um the The other thing that um is to reduce fragmentation. There aren't really a lot of other things you can do to reduce fragmentation except for um uh changing how data gets into the database, which is obviously an application change

Speaker 1: , you know, or you know, obviously uh recopying stuff. So that is actually one of the problems with running Postgres on NTFS for whatever reason because of how NTFS writes files, fragmentation ends up 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 on Windows and care about performance is pretty small.

Speaker 2: Okay, that's all the time we have.

Speaker 1: Okay. Thank you very much.