XORuby Atlanta 2025 - Scaling PostgreSQL Beyond Query Optimization by Alex Yarotsky

What is discussed, and when

1. 5:00 – 10:00: Aurora

Notable quotes from "XORuby Atlanta 2025 - Scaling PostgreSQL Beyond Query Optimization by Alex Yarotsky"

• You even care to change them. And quite often you would think well I use like Aurora or RDS or whatnot. They optimize it for me. No, they don't. They use default config that you should go and change right after this presentation.

  about Aurora · at 6:10

Who and what is mentioned in this episode?

Companies & orgs

• HubSpot — Company · 1 mention

Products & tech

• Aurora — Product · 1 mention · first at 6:16

Concepts & topics

• PostgreSQL — Concept · 5 mentions

Full transcript with timestamps

0:20 All right. Hey everybody. How you doing today? All right. So, let's be honest. How many of you think why the hell do I know to learn anything beyond SQL? Like if I know how to write efficient queries, if I know how to use explain analyze, why the hell do I need that? I mean, honestly, like I've been there, like I was one of these guys who was like, why do you need that? Um, and I had this strong opinion until and that was the moment where the smoke was supposed to go off and the spooky sounds, but unfortunately the venue doesn't have all of that. So tell let me tell you about the course of August. So about six years ago, I joined this amazing company, Hubstiff. And at HubSp, we operate on a lot of data, on terabytes of data. We ingest gigabytes of data every day. We serve like over 2,000 requests per second. So a lot of stuff is going on. Everything is on fire all the time. And the first year I

1:16 joined the company and first month goes well, second month goes well, August. And one day we have sort of an incident where things become slowish. And I talk to the team and they're like, well, you know, it happens every August. Like it's negligible. We just repack a table, vacuum a table, and it's fine. I'm like, okay, I'm new here. If you say it's fine, it's fine. Let's let's see what happened next year. So, next year comes and all is good. And then August hits. And what you can see here, that's from RDS. Um, and it shows you the CPU load on one of our database nodes. And if you can see that dotted grayish line, that's like the maximum CPU capacity we have.

2:10 and you can tell something is off. So after the second year, this happened and I go to the team and I'm like, "You said it was negligible." They're like, "Yeah, yeah, yeah." Like, "We'll just vacuum all of that, analyze stuff, maybe add a few indexes, things will be good." Like, "Okay, I trust you." Third year goes by, June is fine, July is okay, August comes. Well, obviously you couldn't convince me that this is negligible when this happened. Um, so we started analyzing stuff and we realized that well some SQL queries that used to work fine just didn't work fine anymore.

2:54 Nothing changed. No new deployments. The code responsible for that hasn't changed for years. And well obviously we thought that well we should probably rewrite that SQL which we did and it helped and we move on. It's still the same database like you can see that like the distance between the spikes and like this dotted line keeps growing. So the fourth year approaches and well obviously the night when we were seeing this happening we freaked out like honestly we were like oh my god what's going on again nothing changed SQL hasn't changed no new deployments that would cause this to happen and we finally started digging deeper and as you can hear already it always happens in August so obviously we started calling it the curse of August.

3:52 Now, how do we overcome the curse? And before we get into how did we do that? Um, as always, behind every challenging situation, there are people stories. And as you can imagine, there were stories behind all of these incidents. So, every time we would receive a notification that database misbehaves, something like that would happen. or yeah, it's 80 times slower than it is supposed to be and we don't know why. F, I'm smoking. Dude, you always choose the wrong time to poop or smoke. [snorts] And when he was smoking, I bet that was his face because that was my face. every time I would receive a alarm at 2:00 in the morning and would go and see uh I can go backwards just FYI everybody who will be presented after me and would see like these huge spikes happening. So, um luckily uh, we figured out the root causes. We overcame the course of August, which is fantastic, but along the way, as you can

5:03 imagine, we grew a lot of gray hair and smoke a lot, unfortunately. So, it's not healthy, and I don't want any of you to go through the same learning experience we did. And today, I want to share three things with you. Just three things. But what's important about them is that all of them follow 8020 rule. Meaning 80% of results come from 20% of effort. And these three things that I'll tell you about, they're applicable on any stage of your company. So whether you're running still small possess instance or large and they will give you immediate benefit without going nuts. So let's get into it. So change the defaults. So posgress is a fantastic database. Um it is optimized to run on any machine, slow machine, large machine, whatever, which means that its defaults are suboptimal and nobody ever goes and checks like posgress config because it like feels like that because like you have like

6:06 dozens of parameters. You don't know what each of them means and why would you even care to change them. And quite often you would think well I use like Aurora or RDS or whatnot. They optimize it for me. No, they don't. They use default config that you should go and change right after this presentation. So let's go and check the first one. So the shared buffers basically this thing tells you how much memory dedicate to caching data. Well, we all know that fetching data from memory is way better than fetching it from the disk. And basically shared buffers tells posgress how much memory available on the machine do we want to allocate to store caches.

6:47 So basically all the frequently frequently fetched results will be stored there. All the large indexes everything will be there. So the data access can be like super fast. And the rule of thumb is allocate about 25 to 40% of your total memory to shared buffers. Start with 25% that's a conservative but good number. But if your workload is more read heavy go all to 40%. You won't regret it. And you will immediately see huge huge bump in your performance because [snorts] uh posgress will start fing fetching much more data from memory. So that's learning number one that we learned the hard way and and you'll see me using that a lot. Whatever I tell you don't use it as a silver bullet. Apply it and go and monitor because quite often that will be your face when you see the results. you'll be like either oh damn why didn't I do that sooner or oh please roll it back as soon as possible all right effective cache size well

7:52 people who get into like posgress optimization usually mi mix it with the shared buffers because both of them tell about the same thing how much memory is available but the thing about effective cache size is that it doesn't allocate anything it just gives hints to the posgress so how many of you know what posgress query planning planner is like how to use explain analyze great so that's just an example of explain so like you can write explain analyze SQL query and it will give you this and what it tells you is well how posgress thinks it should execute this query and basically anytime you execute a SQL query in posgress it goes through two phases phase number one planning so it takes all the information it has available and chooses is which route should I go to execute your query? Which index should I use? Should I even use the index? Should I fetch data from cache? Should I go to the table uh sorry

8:50 to the disk and fetch it from there? And it assign weights to all possible scenarios and chooses the last costly uh pass to execute. And the thing is by default effective cache size is really really small. And if you raise it up to 50 to 75% range, it tells posgress that hey actually I can put a lot of stuff into memory because you allow me to which means I can again cache more data. I can use larger indexes and again it gives you huge productivity boost boost. Don't take my word for granted. Start with 50% conservative value. Raise it up. You'll be amazed with the results along the way. And last, and I promise that's the last config, is random page cost. After applying the previous two, this one will give you the biggest boost you can even think of. Like if one of your tables at least 10 GB in size, changing this from default to the value that I'll suggest you like can easily give you like a 100 like 100 times

10:01 faster queries. So basically what it tells posgress is okay so when I analyze which query like which path do I take when execute your query what's how should I treat um taking data from indexes versus taking it from directly from disk and by default this value says four times it says reading data from indexes might be like four times slower now let me elaborate like what the what you mean like four times slower from indexes So like we discussed before some of the data posgress uploads into the memory but as you can imagine your database stores way more data than it can store in the memory. So some of the indexes live in memory some on table and most of them live on t uh sorry on disk and most of them live on disk. So every time posgress need to read something from the index it goes to the disk and this config tells it disk is slow don't go to disk read it from the table directly.

10:58 That's why sometime when you try to explain analyze some of your queries and you're like I know there is index for that why don't you do that that's because like it thinks oh going to index then loading data into memory from index then finding link there and going reading it from the table is four times slower which it isn't now we use SSDs all over the place they're so much faster the spinning disc used to be throw it away replace it with this range. Um in our particular case like we were even more conservative because the four default value is four. We changed it to three to two only then to one and a half and we actually remained at one and a half because like anything below that was worse for us. So start with that range start with one and a half and you will immediately see amazing boost.

11:51 All right. Another good thing is that you can do these changes per session. So basically instead of changing them on the database level, you might want to experiment and that's the great way to experiment. You can run write something like that and only use that in a specific part of your application where you feel it is safe to do that or roll it back super quickly with feature flags if it doesn't work. And again, as always, don't take what my word for granted. Check it out. The next thing on our list of three is tune out a vacuum. Like this is the silent hero of posgress that nobody's talking about until it's too late. Um how many of you know what that tupils are or like what MVCC is in Postgress? All right, not many. It's good. Let's quickly go through it. So simple example, we have a table uh and we just inserted three rows into it. So far so good. Now let's say we deleted one row and updated another

12:54 row. So you would think that posgress would go and change this data on disk. So now basically nothing like physically changed almost. But what posgress does is it marks records that you update or delete as obsolate and does not remove them. So basically let's say I don't know just for the sake of simplicity let's say every record on this table is 1 megaby in size. So after the first round of inserts we have three megabytes in size. Now after these two changes suddenly we have four megabytes. Like how come we deleted one record? So what happens next is we can actually check that um there is like this beautiful query that you can run on any of your tables at any time and it will tell you hey by the way you have this number of that tupils and basically that tupils are the records that are obsolete but haven't been removed from physical space and the problem with that is that these records they take space and these records are

14:04 still present in the indexes So not only you waste your memory, your indexes eventually become less efficient because they have to go bigger amount of data to find what you need. And uh the the way to solve that is to run vacuum on the table. So what vacuum does it just goes and deletes uh that tupils and posgress has this thing called add a vacuum that does it for you. So you don't have to think about it which is great. And just another example. So after we do vacuum on this table, um we don't have any dead tuples anymore. So out of vacuum is a great thing. It reclaims disk space. It prevents blood. And basically like this thing of accumulating dead tuples is called blood. Um it updates visibility maps to optimize index on scans and it mitigates transaction ID wraparound risks. Keep that thing in mind. We'll get back to it. I'll have a question for you. You'll enjoy it. Um, so out of vacuum is great

15:05 and it works until it suddenly doesn't. You've seen some of that spikes I show you before. That was the reason. Like that was the reason. So basically what happens is as soon as your table starts growing, as soon as um the amount of information you write and update on the table grows, out of vacuum can start lagging behind. It just doesn't have enough time, enough resources to clean things up. and keep your tables up to date, which means you accumulate blood, everything is slower, uh, memory is leaking, nothing is working. And again, nobody's telling you you have to change a few settings to make it work just perfectly. So there are two settings on a vacuum that you need to change to make it your best spell. So number one is at vacuum scale factor. So basically it literally tells what's the fraction of the table like should be dead so that I start vacuuming your table and default value is 0.2

16:10 which is 20%. So the outer vacuum doesn't kick in until you accumulate 20% of your table as a waste. Again, good example. If you have a 10 GBTE table with the default parameter, 2 GB of table should be waste. And like now, imagine how much slower things will be when it will start cleaning this up. And the good rule of thumb is actually drop it down to like 3 to 5% range and then optimize it from there. On a lot of our tables, for example, we just set it to 1% because like we run on like tables that are like 20, 30, 50 GB in size and it helps to keep them clean all the time. The other thing is add a vacuum analyze scale factor. It's similar to the previous one, but instead of starting vacuum, it kicks in analyze.

17:01 So, how many of you know what analyze is on posgress tables? Okay. So analyze what it does is it recalculates query plans uh based on the new information it gets. So basically uh every time your table changes indexes change everything change and posgress might lag behind with its old query plans. So you need to tell him dude just reanalyze everything you know now so that you can build more efficient query plans and add a vacuum analyze scale factor does it to tell exactly this. So default default value is 10%. Which is like analyze my table every time 10% of a change which is again way too rare. So feel free to set it to 5% or below. You will never regret it. The great part about it, you can always change these settings per table.

17:55 So if you want to experiment or if you have tables that change way faster than the others, you can always apply these changes per table. And a quick hint, look for tables that have autovacuum enabled set to false because things are about to get on fire if you have any of these. Um the best practice is for long-term monitoring of how efficient your out vacuum is. Well, first of all, monitor for dead rows. Um, as soon as you see that, um, you have more dead rows as a percentage of the table size than you want to, well, obviously you want to tune your auto vacuum. And the other query suggests you how to check when was the last time the table was vacuumed so you can be sure that a it's been run, b it's run on the um, cadence you want it to run. As always, monitor and adjust.

18:52 And now learning number three, the one that beat us in the ass too many times that I'm about to start crying. But let's get into it. Longunning transactions. So what is longunning transaction? So every time you start a transaction in Ruby or sorry in active record and you start doing things you shouldn't be doing in this transaction like so. Now see this you open a database transaction and you start doing HTTP queries. I see people saying this. Well, we all know we all did that. Another example is for example, you start a transaction, you take a database log, then you start and go doing a lot of stuff, but this log is still being taken and the other things just can't get access to this stuff until the transaction is committed. Another example is again you open a transaction, you do some SQL queries, then you go and do like a lot of Ruby related stuff that is slow and again transaction remains

19:52 open. Um what's the problem actually with that? Well, first of all, your application becomes slower because you waste a lot of time just waiting. Secondly, interesting fact, even simple select query can block subsequent DDL operations like alter table. So now imagine you have your application running and you're like well we have zero downtime migrations. No you don't. You run your database migration to add a column on the table that is being logged by a few slowly running transactions and you're about to face a disaster.

20:27 Uh it slows down out of vacuum. So basically what happens is out of vacuum can't start cleaning your stuff until transactions or locks prevent its access to information. So let's say you open a transaction and you change some stuff on it and while it's still open out the vacuum kicks in automatically and it's like well I would love to change that data but it's still being logged so I'll wait. And then it waits and then you have another longunning transaction and then it has to wait again and like the amount of plot keeps accumulating because auto vacuum can keep up with that. And my favorite part is it increases the likelihood of transaction ID wraparound. Remember that slide I told you to keep in mind.

21:17 So you may think like what the hell is transaction ID wrap around? And let me tell you because I didn't know until I saw one. So posgress assigns ID to every transaction that happen in your table. Basically every time you do like any operation like update, delete, insert like it has an X ID, internal X ID in postgress and X ID is just 32-bit number with the limit of 4 billion. Just we're about to have some fun. Just bear with me. And every create, update, and delete that touches a row uh attaches the x ID of this transaction.

21:55 So that you can see, oh, that was the last transaction that touched this particular row. Okay, so far so good. And when the maximum number of XIDs, which is 4 billion, are used, posgress just starts over. So okay, we hit 4 billion. Let's start over from number one. Easy. The problem is if posgress reaches two billion of unvacued that tupils it won't allow any further write operations. So you can write anything anymore forever. Like literally it just says I'm sorry just you I won't let you write anything. Now imagine seeing that in two in the morning on Sunday night and um want to ask me that question and I know after like everything I told you you'll be like yeah of course it happened. No, it almost happened.

22:49 Almost. We caught it like 10 minutes before it happened. And um our CEO Jared is on this call on on in the room today and that's the first time he hears that. So if next week you see me on LinkedIn searching for a new job, well, say a good word. Um all right so you can see okay like these longunning transactions are dangerous how do we save us from them? Well first of all statement timeout like this is like the example of configuration from your uh database YAML just always add this variable statement timeout and set it to whatever reasonable number of seconds is for your application. Honestly we have it set at 20 seconds which is too large.

23:44 Yes, I'm ashamed for that. But ideally keep it under five ideally. Um the other thing you need to change it on idle on posgress itself idle in transaction session time out. Basically what it tells you is how for how long does posgress allow transaction to be open before it just shuts it down even without like you doing anything. So like if you have a transaction that is open for 30 second but like idle in transaction timeout is 20, it will just shut it down after 20 seconds and save your application from burning down. All right, these are all the lessons. Like I said, I didn't try to overwhelm you.

24:19 Three important lessons that you can apply right now right after this presentation. I beg you, please go and adjust all these parameters because I don't want any of you to look like that guy. Believe me, I looked like that so many times. My wife recognizes this space better than anything else. She's like, "Oh, an incident." Okay. Okay. So, um I hope I didn't bore you to death. I hope you find them useful, these lessons, and I really urge you to go and apply this because it will save you from a lot of trouble. Thank you.

Questions about this episode

What is discussed in "XORuby Atlanta 2025 - Scaling PostgreSQL Beyond Query Optimization by Alex Yarotsky"?

In order: 5:00 Aurora.

What is mentioned in this Confreaks episode?

3 people, works and ideas are mentioned, including PostgreSQL, Aurora, HubSpot. Each one on this page links to its own page with every episode that mentions it.

How long is "XORuby Atlanta 2025 - Scaling PostgreSQL Beyond Query Optimization by Alex Yarotsky"?

The episode runs 25 min; the transcript is about 3,953 words in 27 timestamped sections.

More from Confreaks

• All analyzed episodes
• Books mentioned on Confreaks
• People mentioned on Confreaks
• Topics mentioned on Confreaks