Skip to content
PodSubs
ShowsPeopleGuestsJobsNewsEvents
More
BlogPodCred
Menu
ShowsPeopleGuestsJobsNewsEventsBlogPodCredNetworksSign in
Join nowSign in
  • Shows
  • People
  • Jobs
  • News
  • More
    • Guests
    • Events
    • Blog
    • PodCred
    • Networks
    Join nowSign in

Postgres FM

Postgres FM artwork

Disks

Aug 29, 202546 minAudio

Nik and Michael discuss disks in relation to Postgres - why they matter, how saturation can happen, some modern nuances, and how to prepare to avoid issues. Here are some links to things they mentioned: Nik’s tweet demonstrating a NOTIFY hot spot https://x.com/samokhvalov/status/1959468091035009245 Postgres LISTEN/NOTIFY does not scale (blog post by Recall ai) https://www.recall.ai/blog/postgres-listen-notify-does-not-scale track_io_timing https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMING pg_test_timing https://www.postgresql.org/docs/current/pgtesttiming.html PlanetScale for Postgres https://planetscale.com/blog/planetscale-for-postgres Out of disk episode https://postgres.fm/episodes/out-of-disk 100TB episode https://postgres.fm/episodes/to-100tb-and-beyond Latency Numbers Every Programmer Should Know https://gist.github.com/jboner/2841832 Fio https://github.com/axboe/fio ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork

Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.

Transcript

From the show. Tap a time to play from there.

Michael: Hello and welcome to Postgres.FM, a weekly show about all things PostgreSQL. I am Michael, founder of pgMustard, and I'm joined as usual by Nik, founder of PostgresAI. Hey Nik! Nikolay: Hi Michael, how are you? Michael: I am good, how are you? Nikolay: Very good. Michael: Great. And what are we talking about this week? Nikolay: Disks. If you imagine database regular icon or how to say like picture how we usually visualize database on various diagrams it consists of disks right?

Michael: Yeah like 3 I'm thinking of cylinder sometimes a cylinder yeah with like normally 3 layers? Nikolay: Yeah, if you 4, and obviously databases and disks, they are close to each other, right? But my first question, why do we keep calling them disks? Michael: Like outdated term you mean? Nikolay: Yeah, obviously. I don't know. Michael: What does the D in SSD stand for? Nikolay: Yeah, actually Sometimes we like logical level volumes,

storage volumes, something like this. And in cloud context, especially EBS volumes, right? We talk about them like that, but In all cases, we still, it's acceptable to say disks, but disks are, they don't look like disks anymore, right? They are rectangular and microchips instead of rotational devices, right? Michael: Yeah, makes sense.

Nikolay: In most cases, not in all cases. Rotational devices can be still seen in the world, but not often if we talk about OLTP databases because it's not okay to use rotational devices if you want good latency. But yeah, so disks, because databases, they require good disks, and they depend on it heavily in most cases, not in all. Sometimes it's fully cached, so we don't care if it's cached, right?

Michael: Yeah, I was gonna ask you about that. Because I think even in the fully cached state, if we've got a lot of writes, for example, we might still want really good disks. There's things where we're still writing out to disk and we want that to be fast, not just reading from. Nikolay: But we are not writing to disk. If we move to the Postgres context, we don't write to the disk except to WAL, right? WAL. Yes. Yeah, and that's it. Well, yeah, I agree it can be expensive if a lot of data is written.

So, yeah, you're right. Because we need to write our tuples. And if it's full page write after checkpoint, we need to write whole page, 8 kilobyte page. Yes, so and we need to get a sync before commit is finalized. So definitely it goes to disk, But data in terms of table and index, tables and indexes, it's written only to memory and it's

dropped for checkpoint normally. It dropped for checkpoint or to later write it first to page cache and then page cache can use pdflush or something like to write it further to disk. But yeah, in terms of fsync, write latency is important. It affects commit time. By the way, I just had a case, it's slightly off topic, but I published a tweet and LinkedIn post about LISTEN/NOTIFY.

I added them to the list of deprecated stuff. Michael: It's not deprecated, right? But you're saying you recommend not using it yeah at scale? Nikolay: Yeah well if... Michael: Or possibly at all. Nikolay: Yes my Postgres vision deviates from the official vision in some cases for example official documentation says don't set statement amount globally because blah, blah, blah. And I don't agree with this. I, in OLTP it's a good idea to set it globally to some value and

override locally when needed. And here, LISTEN/NOTIFY, I just see like we should just abandon this completely until it fully redesigned because there is global lock and 1 of our customers Recall AI, they published great post about this because they had outages And it's related to the topic we discussed in an interesting way. To reproduce it, I used a bigger machine. And the issue is with NOTIFY, at commit time, It gets a global

log to serialize NOTIFY events. Global log like on database, exclusive log, insane. And if commit is fast, everything is fine. But if in the same transaction you write something, commit, like WAL, it waits a little bit, right? In this case, contention starts because of that lock. So if you have a lot of commits which are writing something to WAL, meaning they need a sync and they need to wait on disk.

If disk is slow, you use NOTIFY. This doesn't scale. Performance will be terrible very soon. At some concurrency level, you will have issues and you will see commit spans like many milliseconds and dozens of milliseconds and then up to seconds. And eventually system will be down. Anyway, this is related to slow disks. You're right if latency write is bad, we might have issues. Michael: Yeah, but you're right too, that the majority of the

time we care about the quality of our disks, it's when our data isn't fully in memory and we're worrying about reading things either from, well, from disk, or even from the operating system. It's hard to tell from Postgres sometimes where it's coming from. But we have a Nikolay: bit of documentation. It's impossible to tell in Postgres unless you have pg_stat_kcache extension. That's why, since buffers is already in Postgres 18, again, I advertised to all people who develop some systems with Postgres,

if it's possible, include extensions, pg_wait_sampling, and pg_stat_kcache. And kcache can show. Michael: Yeah. I think it's not, I think you're right, it's impossible to be certain without those, but for example with, through timings, through I/O timings, which another thing that people might want to consider having on, obviously a bit of overhead. Nikolay: track_io_timing. you mean? Michael: track_io_timing. gives you an indication, like if you're seeing not too many reads from either the disk or the operating system and the

I/O. Timings are bad, you've got a clue that it's coming from disk. Nikolay: Yeah, indirectly we can guess that this time was spent. Yeah, not many, it's a good point because sometimes it's fully cached. In page cache we see reads and since there are so many of them, are your timing is spent reading from page cache to the buffer pool and disk is not involved. If volumes are huge, but if volumes are not huge and still significant

time is spent, very likely it's from disk. Michael: Exactly. Nikolay: Yeah, yeah. Michael: Yeah. It's not a novel, like this is something we added to our product just as a tip. It doesn't come up that often. Like it's not... Nikolay: Enabled track_io_timing? Michael: We actually just use, we actually, because most people don't have that on, we actually just use the buffers, like shared red, and then the timing, the total time of the operation. Nikolay: It's a pity it's not on. In big systems, we have it on, like I never saw big problems

on modern, at least Intel and Arm, Graviton2 on Amazon. Like I just see it's working well. There is a utility you can check your infrastructure and understand if it's worth enabling, but my default recommendation is to enable it. Of course there might be an observer effect, but it can be double-checked if you want to be serious with this change, but I just see we enable it. Michael: Yeah, it's all to do with the performance of the system

clock checks. And I think, for example, the setup I've seen with really bad performance there are like running, like dev systems that are running Postgres inside Docker and things like that, that still have really slow system clock lookups. But most people aren't doing that with production Postgres databases. And I haven't seen even any of the cloud providers have slow, I think it's pg_test_timing or something like Nikolay: that. pg_test_timing, I double checked. Michael: So yeah, you can run it really easily.

And. Nikolay: But what if it's managed Postgres? You cannot run it there. In this case, you need to understand what type of instance is behind that managed Postgres instance. Take the same instance in the cloud. For example, if it's RDS, from RDS instance name, you can easily understand what EC2 instance is this. Right? Yeah. You can install it, it will be... Well, operating system matters also, right? Michael: There are some, yeah, there are some tricks you can

do, like do things that would call the system clock a lot, like nested loop type things, or count aggregations, things like that, like trying to get lots and lots of loops. Nikolay: Ah, you're talking about testing at higher level, at Postgres level. Yeah. Oh, that's a good idea. Yeah, a lot of nest loops. And you test with this, without this, completely like running like 100 times, taking average, for example, and comparing averages. And as you can guess, yeah, it's a good test, by the way.

Michael: I think the first time I saw that was Lukas Fittl. I think he must have done a 5 minutes of Postgres episode on this kind of thing. So I'll link that up. Nikolay: Yeah, I'm glad we touched this because again, like our default recommendation is to have it enabled. It's super helpful in pg_stat_statements analysis and explain analysis plans and yeah, track_io_timing, if possible, should be enabled. And this is related to disks directly, of course. Yeah. Although, strictly speaking, it's not timing of disks. It's timing of reading from page cache to buffer pool.

So it might include pure memory timing as well. That's why you- Michael: But it does. Nikolay: Yeah, yeah. That's why your comment about large or not large volumes, it's important. But it's honestly like if you even like, if you are a Backend engineer, for example, listening to this episode, I can easily imagine in 1 month you will forget about this nuance and will think about track_io_timing like only about disks. Right? And it's okay because like, it's really like super narrow topic

to remember, to memorize. Yeah. Michael: Well, and I guess this is moving the topic on a tiny bit but if you're on a managed Postgres setup which a lot of Back-end engineers are working with Postgres are, you don't have control over the disks. You're probably not going to migrate provider just for quality of disks. Maybe you would, but it would have to be really bad and you'd have to be in a setup that really was hammering them.

Maybe super write-heavy workload or huge data volumes that you can't afford to have enough memory for, you know, those kinds of edge cases where you're really hammering things. Nikolay: Well, there are 2 big areas where things can be bad. Bad means saturation, right? Yeah. We can saturate disk space, so to speak, out of disk space, and we can saturate disk I/O. Both happen quite often.

And managed Postgres providers are not all equal, and clouds are not all equal. They manage disk capacities quite differently. For example, at Google, at GCP, I know regular PD SSD, quite old stuff. They have maximum 1, 200 mbps separately for reads, separately for write, speaking of throughput.

And they have 100,000 or 120,000 IOPS maximum. And I know from the past discussions with Google engineers that actually real capacity is bigger, but it was not sustainable. So it was not guaranteed all the time and they could raise the bar but it could be it would be not guaranteed so they needed they decided to choose the guaranteed bar for us.

Michael: Makes sense. Nikolay: Yeah but like basically we're not using at full possible. We could use more, right? But we can not. So they throttle it. Michael: Okay, interesting. Artificially, Nikolay: to have guaranteed capacity for this disk I/O. Michael: Interesting. I guess the subtlety that I was missing was not when you're at the maximum.

So in between tiers, imagine you're in a much smaller setup. I see a lot of people just in upgrading to the next level up within that cloud provider to get more IOPS. You know, if you're on Aurora, just scaling up a little bit instead of switching from an Aurora to Google Cloud. But you're right. When you're at the last, or second to last level is when people start to worry, isn't it? When you're at the last level, you can't just scale up on that cloud provider anymore. So yeah, really good point.

Nikolay: And also, at Google, for example, let's say we're, like I know this, these rules, they're artificial. So this throttling, what I just told you, it also can be throttled additionally if you have not many CPUs, VCPUs. So the maximum possible throttling is achieved only if you have 32, as I remember, VCPUs or more. If it's less, also it can depend on family, I think. Instance family. Interesting. So complex rules.

On Amazon, AWS, EBS volumes, okay, there is GP2, GP3, IO1. You choose between them, also there is provisioned IOPS. Really complex, right? Michael: And you haven't even mentioned burst IOPS yet. Nikolay: Yeah, yeah. So hitting IOPS limits is really easy, actually. If you Michael: insist on smaller. Yeah, well, the times I see people hitting it is like they're

doing a massive migration. Nikolay: No, no, that's not it. Michael: Big import. Okay, when do you just like, just growing? Nikolay: Yeah, just project just grows and then latency, database latency becomes worse. Why? We check and we see, well, if you have experienced capabilities like to look at graphs, you can easily identify some plateau. It's not full, like not ideal plateau, but usually some spikes,

small, but you feel, oh, this is, we are hitting the ceiling here. Checking disk I/O. It's not cliff, no it's a wall instead of cliff. Cliff it's when, this is important distinction, cliff is when everything was okay, okay, okay, and then suddenly slightly more load or something and you completely down or down like drastically 50 plus%. Michael: Okay, yeah. Nikolay: Here we have a wall and everything is okay, okay, and

then slightly not okay. Okay, okay, slightly not okay. And then more is coming and we start scheduling processing, right, accumulating active processes. So in performance cliff, if you raise load slowly, there is acute drop in capabilities to process workload. In the case of hitting the ceiling in terms of situation of disk I/O or CPU, it's different.

You grow your load slowly and then you see you grow further and things become worse, worse, worse. It's not like acute. It's slightly more, more, more, and things become very bad only if you grow a lot further, right? So it's not acute drop. It's like hitting the wall. It feels like hitting the wall. You know, like if you imagine many lines in store, for example, we have several cashiers, 8 for example, and then normally lines

should be 1 or 2, 0 or 1 people only. This is ideal throughput, everything good. We haven't saturated them. Once we saturated, we see lines are accumulating. And latency, meaning how much we spend to process each customer, they start to grow, but they don't grow acutely, boom, no. Here we talk about, like, performance cliff is that, for example, if we talk about cash only, no cards involved, and suddenly,

like, we had remainings of cash for change in all lines, right? And cashier suddenly, they can say, okay, you do have change, I have change, okay, we're processing, and then suddenly we're out of cash to give change. This is acute performance cliff. They say, okay, we cannot work anymore. Boom. Right. We need to wait until someone goes somewhere like this, like we need 15 minutes of wait. This is like important distinction of performance cliff and hitting

the wall or ceiling. Michael: Okay, I haven't heard that strict a definition before. Like it sounds to me like you're describing the difference between blackout and a brownout. Have you heard of a brownout? Like so blackout is kind of like your database can't accept writes anymore or even SELECTs like no reads like everything is down. Brownout would be like it's still working but people like the they're spinning loaders and it may be loads after 30 seconds or maybe some people are hitting timeout some people

aren't and there's like the like the queuing issue in the supermarket you talked about. Performance is severely degraded but it's not completely offline. Still working at least for some people. So it feels like that's the kind of distinction you're talking about. Nikolay: Yeah, and brown can become dark if you keep loading a lot. So if situation happened at some workload level, but you gave it 10x, of course it will be blackout, but because of context switching, and then it's different.

But for performance cliff, it happens very quickly. It's very much more acute situation. Michael: I think I'm also biased by the cases that I've seen which are more acute because they are bulk loads or backfills where they are running at a much much higher rate than they would normally be, they're consuming IOPS at a much higher rate than they normally would so they hit it really fast and it's like running at the wall extremely fast But I guess if you approach the wall slowly, it's not going to hurt quite as much.

Yeah. Okay, I think I understand. Nikolay: Yeah, back to disks. Definitely, we should check disk IO usage and situation risks. So you Michael: mean like monitor, monitor for it alerts when we're close to our limits? Yeah. Nikolay: Yeah. And also it might be interesting, for example, I remember, I don't know right now, but many years ago in RDS, I remember we like ask, okay, a small system, maybe we need 10,000 IOPS, But

we see situation at 2,500 somehow. Oh, there is RAID, actually. We have 4 disks, and that's why. Okay, okay. So there are interesting nuances there. But also, so understanding your limits is super important. And like, I think clouds could do a better job explaining where the limits are. Because right now you need to do a lot of legwork to figure out

what is your advertised limit. For example, as I said, at GCP you need to understand how many vCPUs. Also, disk, I forgot, like 10 terabytes, I think, is when you achieve the... Or 1 terabyte. Memory fools me a little bit. So you need to take into account many factors to understand, oh, our theoretical limit is this. And then ideally you should test it to see that it can be achieved. Testing is also interesting because, of course, it depends on

block size you're using. And also it depends on, like, you're testing through page cache or direct I/O, right? So directly writing to device. And then you go to the graphs and monitoring and see some disk I/O in terms of IOPS and throughput, separately reads and writes, and then you think, okay, let's draw a line here. This is our limit. So what I'm saying, they should draw the line.

Clouds should draw the line. They know all these damned rules, right, which are really complex. So this should be automated. This line should be automated. Okay, with this, this, and this, and this, we give you this. This is your line in terms of capabilities of your disk. And here are you, okay, at 50%. Okay, I know. Now it's like a whole day of work for someone to understand all the details, double-check them and then correct mistakes.

Even if you know all the nuances, still you return to this topic and you, oh, I forgot this. Redo. So, yeah. Yeah. Michael: When you mentioned the terabytes thing, is that I was working with somebody a while back who, they weren't using the disk space they already had at the, like, Let's say they had a 1 terabyte disk, they only had a couple hundred gigabytes. But they upgrade, they expand their disk to a few terabytes so

that they would get more provisioned IOPS. Because that was the way of, So is that what you're talking about? You need a certain size? Yeah. Nikolay: So the rule for throttling is so multi-factor. You need to read a lot of docs. And like with GCP, AWS, I have pages which I read many, many times per year, carefully trying to remember, oh, this rule I forgot again. Why isn't this automated? Someone can say, OK, these limits depend on block sizes.

OK, But if it's RDS, block size is already chosen. Postgres uses 8 kilobytes. If it's ext4, it's 4 kilobytes there. Everything is already defined. So we can talk about limits for throughput quite well, right? So yeah, this is, I think, lack of automation here. Michael: Also, if you mentioned the number of vcpus like I guess that is that they have all the setting why they have they Nikolay: have all the knowledge and yeah they define these rules

Michael: yeah Nikolay: so give me this like usage level and understanding how far from saturation I am. Because it's so important. No, in reality, we wait until that plateau I mentioned, and then only then we go and do something about it and raise the bar. There should be alerts even. You, like... your database is spending at 80 plus percent of your capacity on this guy you are prepared to upgrade you know add more Michael: yeah well I was gonna say sometimes there are perverse

incentives here where they're not incentivized to help you improve your performance so that you upgrade. But in this case, it should be the incentives should be aligned. Nikolay: Yeah, at the same time, these complaints we are currently expressing, they all are like, reminding me complaints of a guy who is sitting on airplane and saying that there is no leg room

and so on. You're sitting in the air and flying 30,000 feet above ground. And it's magic, right? So these EBS volumes, PD, SSD, like other newer disks on GCP or NVMe's they are great like I mean snapshots elasticity of everything it's great right we just yeah we just want even more Michael: you were It's good that you're being positive about

them. But I feel like I hear quite a lot of people saying that 1 of the cases still for self hosting is better this you can. So actually, I think a lot of a lot of the time with the cloud you're paying for hardware that might be a bit on the older side and you have no control over that. So it's, yeah, I'm interested in your take on that as somebody who's historically been pro-self-managing or some hybrid version.

Nikolay: So I love clones and snapshots, that's why... EBS volumes and what RDS has, and even if it's a lazy load involved, and when we restore from snapshot, it's actually getting data from S3. It still feels like magic and great, and like, we're very good for reproducing incidents and so on. And snapshots are cheap because they are stored in S3. At GCP it's the same, although there is lazy load there as well, although their documentation still doesn't admit it. But just looking at the price, we understand it's the snapshots

of Google Cloud disks stored in GCS, so S3 analog. It's great. But also, if you think about a cluster of 3 nodes or 456, up to 10 nodes and more. Some people have more. Database is basically copied to all replicas and on replicas it's stored on disk and disk becomes more and more expensive over

time. So it can be significant. It can be even more than compute sometimes. That's the point. Like if we have a large database, but working set is not that large, we can have much smaller memory, that's much smaller, like not big compute instance. We had these cases, for example, a lot of time series data. And we have much bigger disk than you could expect.

And then all replicas need to have the same disk. And this disk, if it's EBS volume, it becomes expensive. Very expensive and contributes to costs so much. So then you think, why not to use local disks? Well, we used local disks for benchmarks. It was an i3 instance like years ago, 7 years ago maybe started liking them because it's always included to price, right? Of EC2 instance.

And it's super fast. It's like basically 1 order of magnitude faster in terms of IOPS. Can give you a million IOPS these days already. And throughput, 3 gigabyte per second. Michael: Well, and the resiliency, like if you've already got replicas provisioned for failovers, you don't need the resiliency that the cloud... Nikolay: The point is they are ephemeral. So if restart happens, you might lose this data. But if restart happens, we have replicas.

Michael: Yes, that's what I mean. So that doesn't actually matter. In fact, this reminds me a lot of the PlanetScale stuff that's been... The PlanetScale Postgres, I think they call it Metal. They've got 2 products, but the metal 1 has the local disks and this is a lot of the Nikolay: yeah but you can have local ephemeral VMs only on virtual machines of course smaller size Michael: metal yeah sorry all I meant was they're doing a lot of their publicity, a lot of their blog posts and things are

relevant to this discussion. You don't have to use their services and also you could do it on a much smaller scale. Nikolay: Yeah. And it's so big cost saving and it brings so much more disk I/O capacity. Amazing. But there is a- Michael: And latency reduction, right? Like because the systems are just closer together. Nikolay: Yeah, yeah, yeah. So it's much like, it can handle workloads much better in terms of OLTP workloads. There are 2 caveats, if matter, property and also limits in terms

of we didn't touch this space topic yet. Michael: Yeah, yeah, yeah. We have a whole separate episode on that. But yeah, we should still touch on that. Nikolay: Right. And on AWS, I like local disks much more because they are usually bigger and so on. Like they are bigger, each disk is bigger and the summarized aggregated disk volume is also bigger. On GCP, I think, first of all, somehow local disks are still,

I think, 375 gigabytes only, looks like old, but you can stack a lot of them, and I think up to 72, or how many? Michael: Terabytes. Nikolay: Terabytes, yeah, quite a lot. But in this case you need to really like maybe go with metal, like the maximum, take a whole machine basically, right? But it's possible, but this 72 terabytes will be your hard limit, hard stop. And it's not that bad.

Like, good. Michael: Most people will be fine. Nikolay: Yeah, yeah. It's, it's okay. I mean, to have this limit. But it's a hard limit. Michael: But you're, yeah, the hard limit is the interesting thing. So you're saying, let's say we start on small machines and they only have a set amount, and we suddenly realize we're at 80 or 90% capacity. Nikolay: Right, but at the same time, EBS volume has limits 64 terabytes and PDSSD on GCP has the same limit, 64 terabytes. And RDS and Google Cloud, CloudSQL, they also have hard stops

at 64 terabytes. Aurora has 128, double of that size. And that's it. So these are hard stops. And I think in 2025, I think this is not a lot of data already. 50, 100TB, we had episode about it. It's already like achievable for bigger startups. So RDS, I don't know. I think we should solve it soon. And I think CloudSQL, Google Cloud SQL should solve it soon. But to my knowledge, they haven't solved it yet.

So if you approach this, it's hard stop. And basically, you need to go to self-managed maybe, right? And there you can combine multiple EBS volumes. Michael: Most that we've talked to that do this shard at that point. Nikolay: This is a different route. Yeah, that's why I think plane scale, like it's easier for them to choose local disks and deal with those hard limits in size as well. Because if there is a rebalancing, if it's 0 downtime rebalancing,

you can just make sure no shards will reach that limit, that's it. It's a good way to scale further. Michael: Yeah, they have that for MySQL, but they don't have that for Postgres, so, well not yet, they're building it. Nikolay: They announced it, right? Michael: Yeah, well, they announced building it, I think lots of people are announcing building sharding at the moment. Nikolay: Well I see Multigres already has some code. I even commented in a couple of places proposing some improvements. Michael: Yeah well I know they all have some code, right? Like PgDog's got some code. Nikolay: It's not PgDog is already, you can test it already.

Yeah. I think Multigres also will have some at some point. Michael: All I mean is that you can shard in other ways, right, without these solutions. Like Notion talked about doing it, Figma have done it. Nikolay: So-called application side sharding, as I claim. Michael: Yeah, but they did it without leaving RDS in those cases. So it is interesting. But I thought you were going to go in a different direction here like I thought it was more about the practicalities of expanding So let's say you're not at the the dozens of terabytes limit.

Whatever your provider has let's say you're at 1 terabyte and you're you just want to expand to 2 terabytes. That's often really easy. You can do it at a few clicks of a button without any downtime in a lot of providers. Whereas if you've got local disks, is it a bit more complicated? Nikolay: Yeah, you know what? I think these days RDS also provides options with local and MES. Michael: Wow, okay. I heard Nikolay: about this. Yeah, the instance I'm double-checking right now, it's instance, for example, X2idn.

Yeah, and it has, I think it has, yeah, it has local and NVMe, several terabytes, and up to, I think, not many actually, 4 terabytes. Interesting. So, there might be a hybrid approach when you have EBS volumes and you use local NVMe as a caching layer for both reads and writes. Michael: But then what would you do? Would you set up some replicas with larger disks and then fail over to those? How are you managing a migration to larger local disks?

Nikolay: When you hit 64 terabytes? Michael: Well, no, when you, let's say you've started with smaller, like you started with local disks that are smaller. Nikolay: Ah, with local disks, yeah, I think you did switch over approach, of course, yeah, so you need a different instance with bigger capacity in terms of disk space. Of course, here again, elasticity and automation of network-attached disks cloud providers have, it's great. But let's also criticize it. So they have like, EBS volume has auto-scaling, but only in 1

direction. For example, if we re-shard, we need to re-provision and then switch over. Or if we saw we didn't have autovacuum tuning in place, or we screwed up in terms of long-running transactions or abandoned logical slots, so we accumulated a lot of bloat and say we have 80% of bloat. Okay, we re-indexed, re-packed, now we sit with a lot of free

disk space. We don't need it during next year. Why should we pay for it, right? And shrinking is not automated. But of course, you can provision new replica with smaller disk and then switch over. And when I think about switch over, you know, I decided to force myself to have a mind shift and my team as well to self-driving Postgres. We talked about it. And I think when I think about this particular case we eliminated

a lot of bloat we want disk to be smaller we need to switch over but switch over also it's maintenance window yeah because... Michael: What's the shift there? What did you used to think? Nikolay: Say again? Michael: What was the mindset change that you had? Nikolay: So I think operations like adding disk, removing disk space when not needed, getting rid of bloat and so on, automation must be much higher.

So it should be like approval from DBA or some senior Backend engineer or something, a CTO if it's a small startup, just approval. Yeah, we need to shrink disk space. We don't want to pay for all those terabytes. And automation should be very high. Repacking, and then without downtime, we have a smaller disk. But to achieve this right now, so many moving parts and for example,

to have, you can provision node with smaller disk. It can be local, can be EBS volume, doesn't matter. But then you need to switchover without downtime, you need a PgBouncer or PgDog Layer with pause resume support. And then orchestrate it properly. RDS proxy doesn't, for example, support pause-resume. So you can, You must have some small downtime. And usually people say, oh, it's just 30 seconds. Well, I disagree.

Why should we lose? This is just some routine operation. Why should we show some errors to customers? Let's raise a bar and have pure 0 downtime, everything. And auto scaling, it can be auto scaling, but it can be maybe like, Auto scaling is about like it makes decision itself. It's too much. Like let's step back. I can make decision myself, but I want full automation, right?

And we don't have it. We have it for increasing, to increase disk space, which is good for EB S volumes. Which is good. We don't need to have switchover, so it will be 0 downtime. You can say add 1 terabyte. This is what people do all the time. And I think there is checkbox for auto scaling, even in RDS can decide to add more disk space itself, right? Which is good. Michael: Yeah, like if you get within 10%, for example. But yeah, only up, yeah, as you said. Nikolay: Yeah, at least we will avoid downtime. I also saw in some places people, like There's a trick to put

some file, like some gigabytes, filled with zeros. So if we are out of this request, we delete this file. Oh no. Yeah, just like something sitting there. We can invent some funny name for this approach. Yeah, but just an emergency... It's like reserved connections for max connections, 3 connections reserved for admin. So reserved disk space you can quickly delete and buy your some

time to increase disk space. Michael: Yeah, on the disk space thing, the only thing I think people sometimes get caught out by is having alerts early enough. Like you need, sometimes you need quite a lot of spare disk space in order to save this space to do a repack for example Nikolay: yeah Michael: you need the size of the table you're repacking at least 3 in order to do the operation so it Nikolay: makes us Michael: yes so that well either start with your smallest ones

which is not going to make the most difference or like try and set that alert quite early. Yeah. But yeah, is there anything else you wanted to make sure we talked about? Nikolay: Yeah, well no, I think it's a good idea to understand some numbers, right? So our very old rule was latencies also. We didn't talk about latencies. What latency is normal? Very old rule was you look at monitoring, if it's SSD, it can be best volume. If it's some, the best volume is also NVMe, They're usually these

days with most modern instance families. And you just see very rough old rule, 1 millisecond. I already have a feeling we have a discussion about previous episode where I shared some old rule and someone disagreed with this old rule. Yeah, rules might be already outdated. So If it's 1 millisecond, these days maybe we should go lower, half of a millisecond.

If it's local disk, it should be even lower. This is our point when we think it's okay. If it's more, well, Back in those days we thought up to 5 to 10 milliseconds is okay. But these days already this is not okay. 10 milliseconds is definitely slow these days for SSDs and the NVMe specifically, like, particularly. This is latency which you should start worrying. This is latency, which is like you should start worrying. So basically, in monitoring, we should control usage, situation

risks, and latency as well. This is like regular use or 4 golden signals, right? So we control these things and also errors. And yeah, we check these things and understand where we are right now. Should we start worrying already? Yeah, simple, it's actually simple. And my recommendation also to know your theoretical limits, based on the docs, as I said, it's not trivial.

But also recommendation, if you use some particular setups in cloud, always test them to understand actual limits and if they don't match theoretical advertised limits, You should understand why. And to test it's easy. I usually prefer Fio, simple program. I like snippets GCP provides. They have snippets if you just check SSD, disk, GCP, performance, you will see a bunch of snippets.

The only warning, I managed to destroy, several times I destroyed PGDATA because it was like, you know, like, some of those snippets are Direct I/O, and if you try to test, like, It was always not production, but still I made mistakes. And if you try to test your disk capabilities with Direct I/O and you use volume which is used for PGDATA, forget about your PGDATA.

And this is a good way, for example, to have silent corruption as well, because Postgres even might work for some time until you reach a point when it will touch the areas you had rights to. So yeah, those are practical pieces of advice. Michael: Given pgbench stress, we've talked in the past about pgbench stress, Is this actually a benefit in this case? Could we use it because it's kind of what we want to do is stress

test at this point. Nikolay: Right, but pgbench tests everything, including Postgres. In our methodology, let's split everything to pieces and study them quite well if possible separately. So disk I-O should be understood separately from Postgres. We had it many times, by the way. We started, oh, let's pgbench... We talk about disk here. Let's forget about Postgres completely for now. Right? Michael: So try and isolate. Nikolay: Not completely, actually. It will usually keep in mind that pages are 8 kilobytes.

Michael: Yeah. Well, I was thinking on managed providers, like, it's a bit, like, how would you test on RDS what the, what IOPs Nikolay: should be. That's a tricky question, right? That's a tricky question. Michael: I think pgbench would be a good solution there. Nikolay: pgbench, yes, but you can try to guess which instance, well, instance is easy to guess, but which disks are there and like IOPS and so on, right? And then you can provision the same instance, EC2 instance and disk you guessed. And, but again, as I said, 1 day I discovered they use RAID.

So there's a stripe there. And if you want to do the same, probably you will have a different setup. That's an issue. Also, with those, like I know CloudSQL has it for bigger customers. I don't remember, Enterprise Plus or something, they also have caching with local NVMes. Michael: Yes, Nikolay: yes. Yeah, it's good, but to reproduce it, it's really tricky to test, right? So yeah, I think It's tricky how to test disks for RDS.

But yet another reason to think about who controls your database, and why you cannot connect to your own database using SSH and see what's happening under the hood. Michael: Probably a good place to end it. Nikolay: Yeah, let's do it. Michael: All right, Nice one, Nikolay, thanks so much. Nikolay: Thank you. Michael: See you next week.

Open the transcript file

Hosts and Guests

Hosts

  • Michael Christofides Host Feed-stated
  • Nikolay Samokhvalov Host Feed-stated

Credit the people on this episode

Be the first to rate this episode

More episodes

All episodes
  1. Postgres 19

    Nov 19, 202653 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...

    Credit the people on this episode

  2. The hidden costs of CREATE TEMP TABLE

    Nov 13, 202639 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...

    Credit the people on this episode

  3. Extensions on demand

    Nov 6, 20261 hr 9 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...

    Credit the people on this episode

  4. UUIDv4 vs UUIDv7

    Oct 30, 202648 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...

    Credit the people on this episode

  5. Dump/restore done. Now what?

    Oct 23, 202651 minAudio

    Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...

    Credit the people on this episode