Code with Jason
Code with Jason
251 - Databases at Scale with Prarthana Shiva, Sin City Ruby 2025 Speaker
In this episode of Code with Jason, host Jason Swett interviews Prarthana Shiva, a senior software engineer at NexHealth, who shares how her team is handling massive database scaling challenges. Prarthana explains their PostgreSQL database's growth to 24 terabytes (with projections to triple within a year) and details their innovative solutions including read replicas, Elasticsearch implementation, Redis caching, external write-ahead logs, and optimized vacuuming processes. The conversation also touches on Jason's own database challenges with his CI platform and concludes with Prarthana's upcoming presentation at Sin City Ruby 2025, where she'll discuss their transition from schema-based to row-based multi-tenancy for better scalability.
Life hasn't been the same since the pandemic. Instead of working at an office around people all day, like we used to, most of us now work remotely from home, in isolation and solitude. We sit and we stare at the cold blue light of our computer screens toiling away at our meaningless work, hour after hour, day after day, month after month, year after year. Sometimes you wonder how you made it this far without blowing your fucking brains out. If only there were something happening out there, something in the real world that you could be a part of, something fun, something exciting, something borderline, illegal, something that gives you a sense of belonging and companionship, something that helps you get back that zest for life that you have forgotten how to feel because you haven't felt it in so long. Well, ladies and gentlemen, hold on to your fucking asses. What I'm about to share with you is going to change your life. So listen up.
Jason Swett:I, jason Sweat, host of the Code with Jason podcast. I'm putting on a very special event. What makes this event special? Perhaps the most special thing about this event is its small size. It's a tiny conference, strictly limited to 100 attendees, including speakers. This means you'll have a chance to meet pretty much all the other attendees at the conference, including the speakers. The other special thing about this conference is that it's held in Las Vegas. This year it's going to be at the MGM Grand, and you'll be right in the middle of everything on the Las Vegas strip.
Jason Swett:You got bars, restaurants, guys dressed up like Michael Jackson. What other conference do you think you can go to, dear listener? Or you can waltz into a fancy restaurant wearing shorts and a t-shirt, order a quadruple cheeseburger and a strawberry daiquiri at 7 30 am and light up a cigarette right at your table. Well, good luck, because there isn't one Now. As if all that isn't enough, the last thing I want to share with you is the speakers. And remember, dear listener, at this conference, you won't just see the speakers up on the stage, you'll be in the same room with them, breathing the same air. Here's who's coming Irina Nazarova.
Jason Swett:Oh yeah, chris Oliver oh yeah, jason Charnes, fafridum Dumlao Prathmasiva, fido Von Zastrow, ellen Rydal Hoover and me. There you have it, dear listener, to get tickets to Sin City Ruby 2025, which takes place April 10th and 11th at the MGM Grand in Las Vegas, go to sincityrubycom. Now on to the episode. Hey, today I'm here again with Prathna Shiva. Prathna, welcome.
Prarthana Shiva:Thank you so much, Jason. Nice to see you again.
Jason Swett:Yeah, good to see you again. You've been on the show before, but that was a while ago now. And in case anybody hasn't heard that previous episode, can you tell us a little bit about yourself?
Prarthana Shiva:Yes, so I am currently working as a senior software engineer at Next Health and I work mostly with Ruby and Rails, like that's our backend, but we also have a lot of Python code which we have multiple agents written in, so I kind of switch between both the technologies. What Next Health does is that we integrate with different electronic health record systems to get their data, and then we have a SaaS platform on top of that where it makes it really convenient for doctors and patients to schedule appointments and reminders, send campaigns and campaigns. So that's that's. We also have actually developer APIs wherein we do all the tough part of, you know, connecting with different EHRs, integrating with them, getting all the information, and then we have APIs on top of that which other developers can use to build their own apps. So that's also something which we've been really involved in. But yeah, that's what I'm doing right now.
Jason Swett:Okay, and today we're going to talk about some database related stuff. Can you tell me about that?
Prarthana Shiva:Yes, so currently what we are using is Postgres AWS instance and Postgres RDS and it's kind of, you know, served us well. But as the business is growing, we have seen the need to kind of keep on re-architecting, to keep on scaling so that we keep on meeting the growing business needs. Like currently we are around, our database size is around 22 to 24 terabytes and the instance which we are using I think the max of that is 64 terabytes and we are almost certain that by the end of this year we'll almost three times we like our size will explode three times. So naturally we need to come up with very creative ways so as to say, to be able to have a very performant database because, as we know, database needs to be in top shape for the application and everything else to work as expected.
Jason Swett:Okay, and this is one single database instance.
Prarthana Shiva:Yes.
Jason Swett:Okay, it's interesting to know that just one instance can scale to that size, like many terabytes.
Prarthana Shiva:Yeah, that is true and that's the thing right. As we keep on increasing, it also keeps on getting expensive to keep on scaling and keep on getting a bigger and bigger instance. So of course, we definitely try our best to do other things we can so as to not keep on increasing the instance size and have other ways to make this much more performant.
Jason Swett:Okay, and I have a lot of questions about everything to do with your database and all that, but I don't want to steer us in a different direction than you had in mind. Was there a specific aspect of that that you were thinking we would talk about?
Prarthana Shiva:Yeah, so what I was thinking is that I'll talk about what are the different things which we are doing. To begin with, I think one of the first steps we took was to have a replica database. So rather than queryingying, putting all the strain or stress on the primary database, we have a replica. So any most of our heavy queries or which we know that it needs a lot of indexing, we have kind of put it on the replica. Of course you know that comes uh with the, with its own set of problems, in the sense that you know how much replication lag can we bear? How much fresh data do we need? And of course, like you know, there are features where we need data almost immediately. We cannot, we cannot wait for it to replicate. So in those cases we are still querying the primary database, but as much as we've gotten away from reading from the primary. So primary is mostly devoted to all the rights and for most of the reads we are using our replica database Also, like generally, if you see at the like, look at the Postgres use case.
Prarthana Shiva:It's really very good if you have a read heavy database, but we have kind of an anti-pattern. We have a lot of writes happening on a daily basis. Like there are some tables which has like around 22 million records being written daily. So we are very write-heavy. So I think that is a little bit of an anti-pattern when it comes to Postgres. So that's why we are constantly tuning the parameters the memory parameters, the vacuuming parameters to kind of adjust it to our use case.
Jason Swett:Okay, what is the anti-pattern part?
Prarthana Shiva:So basically we are much more write heavy. As our business, we do a lot of writes and as Postgres database generally is much more performative if it's a read Like you do more reads than writes, and for us it's the opposite we do more writes than reads.
Jason Swett:I see.
Prarthana Shiva:It's a very write-heavy business, like our business in general, because what's happening on a daily basis is that we are constantly reading data from this EHR. So, for example, if a doctor, if a patient, makes an appointment, they call the doctor's office and they say, hey, you know what I want to schedule an appointment. So what we need to do is immediately read this new appointment which has been created in the EHR and sync it on our end so that if anybody is using the NextHealth app to make an appointment, we don't see a reason like a dupes or you know, duplicate appointments being scheduled at the same time. So we need to constantly refresh the data or make the data fresh enough that we are showing almost like information, as like as it's being seen in the EHR. So that is why we are constantly kind of writing all of this information okay, interesting.
Jason Swett:Um, so I personally have been a developer for like 20 years or so, um, and I've never been like a database specialist or anything like that. But it sounds like in this work that you've been doing, at some point you had to like go pretty deep and get like some deeper PostgreSQL knowledge and maybe database knowledge in general. Like at what point did that start happening where you couldn't just rely on your like standard developer level? Knowledge of databases and you had to start going deeper.
Prarthana Shiva:Yeah, so I'll have to say that we have a great team of engineers and it's not all me and of course you know they are really kind of, like you said, like subject matter experts. So I think you know what happens is that generally, from a startup point of view, you start like really quick, you take decisions and then you kind of, you know, just run with it and as the business starts growing, you need to kind of step back and see that, oh, you know what, this is what we have. But now we need to get just much more creative in order to not have to kind of start from scratch again, because our tables were already there, we had a lot of data, so just we have to kind of move with what we have. So that is why it was really important, like you said, to be much, like you know, get much more in-depth knowledge and just not depend on, you know, AWS doing all the maintenance for us. Like we ourselves have to come up with different ways to make this just much more scalable.
Jason Swett:Okay, interesting. And how did you start Like okay, you said there's other developers who are working on this area too. What is your involvement Like? What was the like first thing that you like had to tackle?
Prarthana Shiva:I think the first thing which we actually tackled was that. So, yeah, I am mostly working with the Ruby on Rails app, the backend app. I think the first thing which we did was that we realized that, hey, you know, all our reads were coming from the primary database, so just changing our connections to start reading from replica wherever we could. I think that was one of the first things we did. Of the first things we did Then, the other thing which actually made a lot of difference was that we have like a lot of heavy search queries, a lot of filters being used on the front end. Like you know, we are constantly filtering with different parameters. So we realized that, of course, like one of the way to make it really performative is adding indexes to different tables, to the columns which we are heavily querying. But we started realizing that not only is that expensive, but we were denormalizing a lot of tables because, hey, let's kind of get away from the joints and have as much of a flat structure to make these queries really fast. But then I think we hit a ceiling where this was not enough and just indexing most of the columns in a given table was just not making sense. But then we kind of moved to Elasticsearch, which is much more performative, whose use case is that, hey, you know, instead of always querying the database, you use something like Elasticsearch or OpenSearch. So then those were the changes which we made then at the application level, where we were now connecting to Elasticsearch and doing all of our major searches and of course, we have a lot of things running under the hood, like if anything is changing in the database, re-indexing all of these Elasticsearch.
Prarthana Shiva:But I think that way, yeah, so I think I was really very much involved in, you know, changing the code to kind of work with the database changes we were making, or you know how to put less strain on the database, like. The other thing which we did was, like I said, right, we are reading a lot of data from the EHRs, but a lot of this data actually does not change, but we are still reading all of them. So what we started realizing was that you know processing all of this data in the database constantly, like you know, always checking oh, did this row update? Oh, it did update, so let's update our record. Oh, nothing changed. So, just, you know, skip this record. We thought that let's put a sync cache or like a caching layer on top of it, so that we are not always hitting the database.
Jason Swett:Okay, so I want to I want to pause for a second and and go back to something you said earlier. Um, I'm imagining somebody listening to this who maybe doesn't know very much about databases at all. Um, so first, at the risk of stating the obvious, let's talk about read replicas. A second the reason for doing that is because if you have just one database and you're doing writes and reads to that database, then you have the full load of your reads plus your writes on that one database. But then if you have a read replica, then you're distributing the load over two database instances. So you have a read replica, then you're distributing the load over two database instances, so you have the rights going to your main database and then your reads on your read replicas. So it's um less of a load on each one of those two. That's, that's the idea, right?
Prarthana Shiva:yes, exactly, exactly. And, like I said, there can be a replication lag and, depending on how much tolerance you have for stale data, you can customize it. But, yes, exactly, like the engineers also right, we are always querying the database to figure out something or the other. So we all use the replica database, with the caveat that, yes, we might not get the freshest data, but at least we are not putting all strain on the primary database.
Jason Swett:Yeah, and then I was going to ask about denormalization and then you mentioned it. So I found actually a lot of developers don't know a lot about normalization. But basically the idea is that you don't want to store any piece. You don't want to store any piece. You don't want to store any one fact twice, because then they can get out of sync and you can have corrupt data that way. And there's some other certain normalization rules. There's various levels of normalization.
Prarthana Shiva:Third, normal form.
Jason Swett:Fourth normal form, blah, blah blah, and that's good for data integrity, um. But then sometimes it's like there's a cost benefit thing going on, like if you normalize things as much as they can be normalized, then you have the highest assurance of data integrity that you can. But there's a performance penalty with that. Like if, for example, your database was just like one table and it just had a list of everything in there, then like that could be super fast potentially, but you'd be susceptible to all sorts of data corruption and so that would be bad.
Jason Swett:And even in like a main database, sometimes I'll denormalize stuff in a database for performance benefits. You obviously have to be very careful about that, and so I really like limit the amount that I do that, and when I do it, I make sure to like make it clear that that's what's going on, that we're making a normalization compromise for the purpose of performance. But if you have a whole separate read replica, then it seems to me like it's it's um, you can get away with more denormalization, because your source of truth and correct me if I have any of this stuff wrong your primary database is your source of truth and your read replica is getting all of its data from the primary database, and so then you can have your read replica denormalized and it doesn't matter so much, because it's not as though you're going to corrupt your data in the read replica in a way that's permanent, because that's not your source of truth. Your primary database is your source of truth. Does it seem like I have that right?
Prarthana Shiva:So currently, the way we have it configured, our replica totally mirrors the primary database schema. So the table structures or the schema in the primary is what we have in the replica, so that I think overall replication is much more easier from one table to the other. But yeah, I totally agree that of course that is definitely something which we can do, that the source of truth stays as is, in the most pristine form possible, and then you can kind of manipulate your replica to be much more consistent with searches or just much more often denormalized, or yeah.
Jason Swett:Yeah, and the thing you mentioned about data that doesn't change was interesting to me, um. So I don't know if you happen to know, pratha, I've been working on my own product. It's a continuous integration platform, um, and one of the convenient things for me about that is the fact that the past never changes. So, like all of the test runs that happened in the past, I can cache like everything because it's never going to change, and that afforded me a lot of really nice performance gains, because each test run has like a lot of data. There's like all the logs that come along with it, all the test cases there might be thousands of them, stuff like that and so I'm doing some like not really new techniques that I haven't done before, I guess, but it's well.
Jason Swett:This is the first project that I'm working on where it's like performance is critical, like from from day one, because when I like click around, like from one test run to another, like I want each of those pages to load pretty much instantaneously, and there's kind of like two kinds of performance optimization. In a way, there's like if you have like millions of users on your app, then you have to worry about like the load on your resources and latency and stuff like that. And then there's the other kind of performance where it's like, even if there's one single user, you want the app to perform well. Maybe there's things on the page you're doing inefficiently that you can optimize. I don't know how to refer to those two things. There's, like you know, the scale of users, and then there's like the other. You know what I mean.
Prarthana Shiva:Yeah, yeah, I, yeah, like. So you basically, no matter what, you always want your page to be performant, even if it's one user or it's thousands of users.
Jason Swett:Yeah, my point, though, is like sometimes you can have a very like a data intensive page, and even if there's only one user, that all that data puts a big load on the system. Or you might have a page where it's not very data intensive it just loads like one record or something like that but if you have a billion users, then that's going to be a performance issue you have to address as well, you know.
Jason Swett:so it's like performance in one dimension versus a different dimension load versus processing yeah like yeah, for some reason I never hear people talk about that distinction very much um, yeah, anyway. Okay, so you have this um primary and then you have the read replica, and I interrupted what you were saying because I wanted to go back and touch on those other things. Um, do you remember where you left off?
Prarthana Shiva:Yeah, caching.
Jason Swett:Okay.
Prarthana Shiva:Yeah, so, like you mentioned that, there's a lot of data which we realized just doesn't change and we really it's like throw away data, but of course we need to make sure that it's not changing. So what we did was we used Redis and we have a caching layer on top of our database and we have a key, you know, being able to hash, being able to identify a record uniquely. And if the hash was not changing or if we were able to confirm that nothing changed in a record like, for example, we resynced an appointment with no changes, we re-synced a patient's information with no changes, so we don't need to put it in the database, we can actually just eliminate it at the Redis level itself. So that again gave us a lot of performance gains because we were actually doing this processing much above the database. Okay, yeah, doing this processing much above the database.
Jason Swett:Okay, yeah, and for that, again, at the risk of stating the obvious, it's cheaper to get data out of something like Redis than the database, because the database data is stored on disk and the Redis data is stored in memory, right?
Prarthana Shiva:Yes, yes, exactly, so it's just much more cheaper and better and performant.
Jason Swett:Yeah, yeah, okay, what else?
Prarthana Shiva:What else. So the other thing which we sorry, I'm referring to my notes so the other thing which Postgres does is they have this concept of the write-ahead logs. What it does is that any changes which happen in the database it's an update, it's a delete, it's an insert, any change it is first written in this temporary log file before it is actually committed to the actual file. Generally, generally, like in a regular setup, everything lives on the RDS instance itself.
Prarthana Shiva:But what we did was we took out this write-ahead log and put it outside of the RDS instance in another EC2 instance where we it's just like a temporary storage. So what that again gave us was a lot of performance boost and basically we could have like a really big capacity, like we really increased the size of these logs. So what happens is that there is a checkpoint where all of these writes are put on the actual file in the database. So by keeping it outside and increasing the size, we actually reduce the frequency of these checkpoints. So we could store much more of the data in the temporary storage before actually writing it into the actual file Interesting Okay, so let me see if I understand this, because I don't know anything about this.
Jason Swett:I don't know anything about locks or anything. So, first of all, it's called a write-ahead lock. Is that the name? Okay?
Prarthana Shiva:Logs yeah. Oh, log Logs. Yeah, l-o-g, yeah, oh, okay.
Jason Swett:Write-ahead logs. Okay, okay, and so before a piece of data makes it into the database, it first gets written to this write-ahead log and then it gets transferred from the write-ahead log into the database. Yes, okay, and why those two steps? Why not just straight in?
Prarthana Shiva:I think that it is mostly done to achieve consistency and if, for any case, the database crashes, you still have your information in these logs and you can write back. So I think that is at least that is my understanding that that's why we have these logs to just increase much more consistency and have somewhere to get back to. In case you know, this data was not written, for whatever reason, into the actual database and I think once this commit happens into the database, the database then kind of point, kind of keeps like a pointer saying that hey, you know, we have written logs till this point and then now for the next, right, it will be after this point. Maybe I'm oversimplifying and but that is how I understand how it works.
Jason Swett:Okay, and so there's two steps. The first is to write to the write-ahead log and the next step is to commit that data to the database. And I'm assuming there's a cost to each one of these steps. And you said something about reducing the frequency with which the database writes. Happen, I believe, and so is the idea that you can do the commits in bigger chunks.
Prarthana Shiva:Catches yeah.
Jason Swett:Yeah, so that you pay that commit cost less frequently? Yeah, okay, interesting. And is there another part to it too? Like you said, maybe like the write ahead log, instead of happening on RDS, it happens somewhere else. Did I understand that part?
Prarthana Shiva:correctly, Exactly so. We took this process out of the RDS and we store these logs on another box, but not the actual instance itself.
Jason Swett:Okay, and this is a dumb question, but why does that make it faster?
Prarthana Shiva:Because this is what I also kind of thought. So it seems that if we can really increase the size, if it's outside the RDS instance, because now it's our own disk space and we are not consuming the already less space which we have on the RDS. So this again, you know, helps us write much more on the external disk in the external write ahead log, rather than you know, if we had it inside the instance. So this just makes it cost effective and it seems it also has performance gains by not constantly writing everything but just doing it in bulk or doing it in batches. It just makes it much more performant.
Jason Swett:Interesting. Okay, so tell me. If I understand this correctly, it seems like when you have the write log outside of RDS, then the write log and RDS are no longer competing for disk space.
Prarthana Shiva:The same resources, yeah.
Jason Swett:Yeah, okay, Okay, yeah, just resources in general, like maybe disk space, memory and CPU. Got it Interesting. Okay, and increasing the batch size, reducing the frequency of the operations? Originally I was imagining that that was only reducing the frequency of the commits. Are we also reducing the frequency of the rights to the write ahead log?
Jason Swett:no, I think that is still going on that's the Okay we're just taking it in bigger batches from the write log to RDS? Yes, Okay, and is there any like network latency factors there, Like is it faster to make a trip to your own write log than it would be to make a trip to the RDS write log?
Prarthana Shiva:Yeah, like I think that it also has. It is much more faster than actually writing in the if it is outside, so that also actually helps. So overall I think it is just a better way to keep Storia write ahead logs. It just makes it, like you said, much more performant, much more faster. And yeah and compete for like. Not everyone is competing for the same resources.
Jason Swett:Yeah, interesting. So I have a problem with my database. Maybe we could talk through it for a second. Maybe you have some ideas for me. So on my CI platform it's called Saturn CI I ran into a problem recently. Well, in the last couple days I've just been running out of disk space on my database instance and that's fine. Maybe the answer is I just need to upgrade to a bigger database instance. But another issue that I've had is for a long time I've had no database problems at all, because the only app that's really been running on Saturn CI is Saturn CI itself. It's just running its own tests and so it's a new project. There's just a few hundred tests, it's not a big deal. But then I got a customer's app partly onboarded onto Saturn CI and there's a couple problems.
Jason Swett:So when the tests run, the tests run on a virtual machine. So it's a digital ocean droplet and if anybody's not familiar with digital ocean, think of it like an Amazon EC2 instance. So just easy to instance. So just it triggers a new virtual machine and it runs the test there and as this VM runs it makes calls back to the Saturn CI server and sends its system logs. So just, you know the, the like the barest system logs just like I don't know how to say it, just the stuff that's coming across the terminal, whatever that is. It's sending that to the server. Okay, and so for a test suite that takes like 20 or 30 minutes to run, that can be a lot of logs, and so I'm running into some interesting problems. One of them is that it just like it totally eats up the browser's memory and the browser gets like really sluggish just because it has so much data.
Jason Swett:So that's one thing. That's like a front-end issue, um, but then also it's like um, not just filling up my database with data, but it's making things really slow because, like, I'll load a page and if there's like a megabyte of system logs it, it um, loads that whole thing up and um. And yeah, I guess the main thing is the storage issue.
Jason Swett:Like it seems kind of dumb to store so much system logs in the database, and so I thought, okay, maybe instead of storing it in the database I'll put it on a file in cloud storage. But then I'm like, oh wait, I have this like real-time needs because the system log streams and it updates like every one second, and so if I'm getting it like once a second, I'm like do I want to be like updating a file in the cloud once per second when I'm like trying to show it in real time scrolling across the screen, like that is maybe going to be even more latency than the database and that would be like a problem. So I'm really not sure what to do. It's kind of working fine the way it is, but I'm thinking about like ways to make this better.
Prarthana Shiva:And we are updating. So, like this is like this log text file and we are update, like we are figuring out, oh, something's changed. Or we are just every time re removing the old payload and just putting the new set. I'm thinking that do we need to update? Can we just keep on storing the latest information as yeah, Good question.
Jason Swett:So the server is only sending what's new? Oh, I see.
Prarthana Shiva:So we need, and is it kind of just adding this new information to the end of the logs, exactly?
Jason Swett:Yeah, and it's really good at like finding the right place, like it never misses lines or does a line twice or anything like that, so we don't have to worry about that.
Prarthana Shiva:Yeah, that's an interesting problem Because, like you said, right, like if you were to put it in S3 or somewhere, then it's just not worth the effort of again always getting it from S3 and then adding new information. But if you keep it in database it will bloat, like with so much of log files being added.
Jason Swett:Oh, here's an idea. Okay For the frontend by the way.
Prarthana Shiva:One thing which I am sure you must be doing is pagination, so that we are not loading Okay.
Jason Swett:Yeah, because, like up to now, even the entire log was not that big. So I didn't need to do any pagination, but I'm definitely going to need to do that Because, yeah, I think it's going to be some kind of thing where what you're seeing is just like a window of the most recent Like you know you see what's in the viewport and then there's like one more screen height worth of logs if you scroll up instead of having the entire logs loaded into there.
Prarthana Shiva:Right. One thing which you can do is, since this new information is coming in and you know the old information is not going to change, like the first page worth of data will always be the same. The new ones is always at the bottom. The first page worth of data will always be the same, the new ones is always at the bottom. Then maybe you can just cache this information and the unchanged information can always be picked from cache, and then anything new we added to the database, put it in cache and oh yeah, yeah, I didn't think about caching Cause.
Jason Swett:yeah, like, okay for the ones that are like the test runs that are already finished that can clearly just be like that can go in cloud storage, like no problem, that's fine.
Jason Swett:It's like the real time updates that I'm concerned about for that part. So, like, yeah, just store that stuff in memory memory, like it can go in the database too, that's fine, yeah. But, um, yeah, and you know what, actually, maybe I don't need to do anything different, except like I'll just use the database like I am now, like it's just going in a big text field, like the logger's all getting dumped in that text field. Maybe that's still fine, and then, once the test suite finishes, I can upload that to cloud storage and then just clear out that database column. Yeah, okay, and it's kind of undisciplined for me to even talk about this aspect of it, because it feels a little bit premature to be like trying to optimize this part that much. But it's just kind of a fun problem to think about.
Prarthana Shiva:Yeah, I know for sure it's a fun project what you're working on.
Jason Swett:Yeah, it's really interesting because it because it mixes like really back-end stuff with really front-end stuff, like it was. It was fun to figure out, like how do I take the system logs of some other machine and show those in the browser scrolling in real time? That was a really interesting thing.
Prarthana Shiva:Yeah, that's really interesting.
Jason Swett:Yeah, okay, back to your database stuff that you're working on. Did you have other things that you wanted to touch on?
Prarthana Shiva:Actually not much, but yeah, in general, I think Postgres has another mechanism called vacuuming, when what happens is that any row which is deleted or updated in Postgres is not immediately like it's not immediately removed. They maintain this version. And then there is this maintenance job which happens, which is called vacuuming in Postgres, which removes this dead tuple or dead rows or old versions of data and makes provisions for newer data. So I think what we did was to switch from the default storage to IO2. And what this does is that it gives higher IOPS and we can increase this vacuuming speed or this vacuuming frequency so that, instead of doing it like the default Postgres vacuuming, which it has as a part of its maintenance, it happens much more faster for us and that helps us in reducing the bloat of the tables. It keeps on removing deleted data which we don't need anymore and also makes it much more performant. So that was one of the other things which we did which really helped.
Jason Swett:Interesting. Okay, so once again I'll repeat this back and see if I understand it. So I learned something about the way files are deleted, and I learned this in a Richard Dawkins book of all places. So I guess when you delete a file it doesn't actually like clear out that location on disk immediately. It's a lot cheaper just to like signify that this section of memory on the disk is just like.
Jason Swett:This space is available again you can overwrite this space, and obviously that's like less work to do that than it is to actually clear out that space, and so I assume that that's why, when you delete something in the database, it doesn't immediately clear it out. It just says, okay, you can overwrite this spot. That's fine, but maybe you can like the purpose of the vacuuming is it like takes anything that was deleted and like clears it away for real, so that exactly okay, I kind of don't understand that part, because like wasn't that space already available anyway?
Prarthana Shiva:so I don't know if databases also exactly work like, at least when I kind of. What I understand about vacuuming is that they still maintain the old version. So there is the space actually still being taken by the old data which has changed or the data which has been updated, and then vacuuming actually goes and clears this information. It's no longer available, and so that there is much more space now to keep newer data.
Jason Swett:Okay, I see Interesting. Okay, and then, how frequently? If you happen to know how frequently does the vacuuming normally happen and how much did you increase it?
Prarthana Shiva:So I think I am not sure how often does it happen, but what we did is that we have a worker which now runs and calls these vacuuming jobs like we have a psychic worker which triggers and it's like multiple times a day to kind of make sure that we are vacuuming at a higher frequency interesting, okay.
Jason Swett:Um, and by the way, as we're talking about this stuff, I'm reminded um I have a friend named Andy Atkinson, who has kind of made a name for himself in the Ruby community at this point. He wrote a book called oh yeah, I have it on my shelf High Performance PostgreSQL for Rails.
Jason Swett:Wow, yeah, so dear listener, if you're interested in this stuff, you can probably read more about that there. Um, and this makes me want to go go read some of that stuff, because all this like really like nitty-gritty database stuff has always been like a total mystery to me. Not that I've ever really even made much effort to to try understand it, but it's interesting stuff to take like this mysterious thing and like find out how it actually works.
Prarthana Shiva:Yeah, I agree that it is definitely very good to know, like, how databases work. What are the things happening under the hood. Like you said that, you know just writing SQL queries. It's always simple, it's you know you get what you need, but a lot which goes behind. You know just writing SQL queries. It's always simple, it's you know you get what you need, but there are a lot which goes behind. You know keeping databases running and performant.
Jason Swett:Yeah, for sure. Well, we have a few minutes left. Do you want to talk about Sin City, ruby?
Prarthana Shiva:Yes, I'm very, very excited. Thank you so much for this opportunity. I'm one of the speakers at Sin City, ruby, and I really hope we get a lot of people come in and watch us and we have a lot of fun. And yeah, I am again planning to talk about something related to databases. What we like, a use case very specific to Next Health, which was convert schema based multi-tenancy using apartment gem to row based multi-tenancy in order to help our growing business, because the older model, which was the schema based multi-tenancy, was not very scalable. So, yeah, I'll talk all about that yeah, it's gonna be great.
Jason Swett:Yeah, and thank you for coming to speak. It's, it's much appreciated. I think you had contacted me, like I know, a year ago or something like that, and you were interested in speaking at Sin City Ruby. The spots were full at that time, but then then I reached out this time and saw if you wanted to speak and I was happy that you were interested in speaking. Yeah, and dear listener, if you are interested in coming to Sin City Ruby, there are, as of this recording, still tickets available. It happens on April 10th and 11th 2025 in Las Vegas, and if you want to grab tickets, you can go to sincityrubycom and pratham before we go. Are there any links or anything you want to share?
Jason Swett:uh, I, I have my linkedin profile, but that's pretty much it okay, um, and obviously people if they want to come to las vegas, uh, we can hang out in person.
Prarthana Shiva:Yeah, that'll be so much fun.
Jason Swett:Awesome. Well, Prathna, thanks so much for coming on the show.
Prarthana Shiva:Thank you so much, Jason.