Scaling up Your Database With ReadySet


The database can be one of the hardest parts of a web app to scale. Many projects end up using ad-hoc caching systems that are complex, error-prone, and expensive to build. What if you could drop in a ready-built caching system to enable better throughput and latency with no code changes to your application?

Join developers Griffin Smith and Nick Marino to see how you can change one line of config in your app and use ReadySet to scale up your query performance by orders of magnitude today.


Hey everybody, I'm Nick Marino. Hi, I'm Griffin Smith. And we're going to talk about scaling up your database with ReadySet. So just to summarize, we're going to start off and give kind of some background information, explain what the problem is that ReadySet is trying to solve, how it solves it, and then Griffin's going to give a demonstration that kind of shows how to use ReadySet with a real-world application and kind of gives you an idea of some of the performance benefits and features you can expect to see there. And finally, after that, we'll open it up for questions that any of you might have. All right, so first off, what is ReadySet? So, you know, if you look at the ReadySet website, it says it's a caching system for MySQL and Postgres, which is true. And, you know, when I first looked at the website, though, I kind of saw that and thought, oh, maybe it's just a wrapper around, you know, Redis or Memcached or a clone of some kind of existing popular caching system. So, you know, it's something like a cool, useful product, but maybe not anything that revolutionary. It does turn out it's actually much more than that. It allows you to quickly and easily create caches for individual SQL queries on the fly with no changes to your application needed. And other than maybe like a config change or two. And, you know, I don't want to spoil too much of the demonstration later, but in one of the queries that we've benchmarked, we see something like a 25,000x speedup. So that's pretty exciting, I think. But before we get into all that, let's discuss the problem. So I think this quote kind of encapsulates the problem we're trying to solve pretty well, so I'll just read it aloud. The data access layer of a million dollar idea starts out as a single server relational database. You're hardly worried about scale issues. You have an application to write. However, if your million dollar idea ends up being worth even 100,000, you'll likely find your database struggling to keep up with the scale. And this is certainly a story that I'm personally familiar with. I know a lot of people out there probably are as well. When you're starting a startup or a website and you're very early stage, you know, you don't really have the time and resources to build something using the latest fancy, no SQL scalable fad of the day. And, you know, and there's nothing wrong with using MySQL or Postgres, right? They're free. They're very popular. They're easy to use. And when you're just trying to get a simple initial version of something, a minimum viable product, a prototype, whatever you want to call it, it's, you know, you don't want to spend too much time dealing with really complex, you know, sophisticated scaling systems. You just want to get something that works. But of course, if and when you do hit scaling limits in your database, it can be a pretty tricky problem to solve. And, you know, it's a good problem to have, but it often comes at a pretty critical point in the life of a website or an organization. So if we're sort of looking at the problem that we're running into here, I just want to sort of frame it very briefly. When you're scaling out a production web application, I think the first thing that you start to really care about as you sort of hit the scaling limits of a single database server is read performance. You know, I think for most web applications, that's where sort of the critical point for user experience. You know, there's this number that's quoted and, you know, there's some pretty extensive research that shows that loading a web page feels instantaneous at about 200 milliseconds. And that 200 milliseconds, most of the time you're spending is reading data out of your database. And so if you need to scale up those database reads, if you need to make those database reads faster, there are three sort of high level main options that you're going to take there. You could spend a lot of time optimizing your queries. That can be as simple as adding indexes or something more involved. You could use a read replica to scale out the query throughput. So if you're getting a lot of queries, you could use read replicas to increase the number of queries that you can handle. Or you could build a custom caching solution where you store frequently accessed data that's not changing a lot in some sort of key value store that you can access super, super fast. All of these options come with tradeoffs, right? So our first option, optimizing queries, you know, this is something that takes a lot of expertise. You have to understand how SQL databases work. You know, you have this sort of notion, right, that like your database should be able to just perform the query for you in the way that is the fastest. But every database server on the market that I'm aware of, at least, needs a little bit of help to do this. You know, as I said earlier, that could be as simple as just adding indexes or as complex as completely changing the way that the query is written. And in the latter case, frequently you're fighting your ORM, right? We all sort of, anybody who's used an ORM has run into this problem where, you know, your ORM wants to issue queries in a particular way, but you know that a different way of writing the query that could return the same results would execute faster against the database. And you have to sort of fight this battle against your ORM or client library. And even indexes come at a cost, right? They increase the amount of time it takes to perform a write. They increase disk usage. They increase memory usage. It's nothing here is free. The other big thing is that your application is changing, right? If you're spending a lot of time optimizing your queries, someday you're going to add a new feature and a new query is going to become important, right? Like this is a thing that is every application is changing all of the time. And if you're adding this extra time to create indexes or optimize queries to every single feature you have to write, that means that you're shipping fewer features, right? The second option, scaling up with read replicas. You know, this is this is sort of a pretty standard way of scaling out the number of queries that you can handle. The, you know, both Postgres and MySQL, I'm pretty sure fork processes per connection. So they have sort of a hard upper limit on the number of connections that they can handle. And so you can scale out throughput by running read replicas. But this also isn't free. Your read replicas have to connect to your primary database in order to stream the replication log, which adds load to your primary database. And you're adding like not insignificant operational overhead to running your database in production. So this is still a lot of work. It's a lot of cost. And at the end of the day, you're only improving throughput, not latency, because it's the same database engine executing the same query just on another server. And then that takes us to sort of the third option, which is building this custom caching solution. The idea here is you execute a query once, you store the results in something like Redis or Memcached. And then if the data hasn't changed, you're able to read the data out of Redis or Memcached. But this is, you know, this is a lot of work. This is code that you have to write. You know, previously, your application was just making SQL queries against MySQL or Postgres. Now you have to you know, you have a totally different access pattern for accessing these caches. You have to deal with invalidating the caches on writes. And frequently, this is like something that's like totally manual. So you can introduce bugs where you forget to invalidate a cache on a particular write. And these bugs, like I've spent months of my life tracking down bugs that eventually, you know, turned out it was just we were forgetting to invalidate the cache on a write. And you're still adding, you know, operational overhead because you have to run this extra service. And there's a lot of other problems, you know, like little fringe problems that come with running these caches, you know, fallback and failover. You know, running these caches and, you know, distributed scenario can be really tricky. And you have this problem where because you're invalidating on writes, if you get a lot of writes, then, you know, you have to run the query against your primary database again. And you have this like thundering herd problem where, you know, if a bunch of people request the same data against an invalidated cache entry, you know, you could really put a lot of load on your upstream database and, you know, cause a lot of production problems. But the idea here, the idea behind ReadySet is all three of these options kind of suck. And it would be awesome if we didn't have to do any of them at all. You know, we want to be focused instead of focusing on, you know, scaling out our database. We want to be focused on building features that our users want, you know, sort of making our customers happy. And all of this database scaling issue is kind of a distraction. So this kind of brings us to what I think is so exciting about ReadySet is that it enables you to scale up without dealing with any of those headaches that we were kind of talking about earlier that Griffin discussed in the last few slides. So this kind of shows some of the most exciting features I think about ReadySet. Number one, it's plug and play. So it speaks the same protocol as Postgres and MySQL. So what this means is that, you know, unlike with a custom caching solution where you might have to integrate, say, a Redis client library into your application and you might even need to speak multiple protocols so you can talk directly to the upstream database if you need to fall back, if your cache has a problem. You literally just use the exact same code and client libraries you're already using. You know, we've implemented the entire, you know, wire protocol for these existing databases. And so you don't have to change any of the code in your application. All you have to do is change a line of config typically to point your application at ReadySet instead of the database. Second off, it's very flexible. So we can turn caching on and off for individual queries at will. So that makes it really, really easy to very quickly iterate on the performance impact of caching. You can run experiments. You can see what happens if I cache this query. What happens if I cache this other query? How does it affect performance? How does it affect memory usage? It makes it very easy to quickly, you know, run these kinds of experiments, get results, see what works. Additionally, ReadySet is very, very scalable. I kind of hinted at this a little bit earlier, but when you're doing a read against a query that's been cached and the cache is available, it's really just look into a hash map. So that's very, you know, very quick and efficient. Typically, we can see even like sub millisecond latencies there. And additionally, you know, if the performance benefits you're getting from a single node of ReadySet are not enough or you don't have enough memory on a single machine, we also support scaling out to multiple nodes. And so not only does that kind of give you even more headroom and breathing room in terms of performance, but, you know, additionally, one of the cool things about that approach is that we still only act from the perspective of the upstream database as a single read replica. So, you know, in the read replica slide that Griffin was showing earlier, if you have more replicas, they're all, you know, getting updates from the upstream database. So it kind of puts more load from having additional replicas needing to, you know, retrieve updates and get data replicated to them. But with ReadySet, it acts like a single replica. All of the scaling out in a multi-node setup happens internally to ReadySet. So you don't necessarily incur additional load on your database just by adding more ReadySet nodes. Finally, this last point, negative latency. That might sound like a little bit of a buzzword, but let me explain what I mean here. Latency, right, it's the span of time between when you request data or request a result and when the result comes in. And part of the big idea with ReadySet is that we're continually updating your cached data on the fly as new data comes in before you ask for it. So unlike in a custom caching solution where maybe, you know, you have to invalidate things periodically and then when you ask for the new data, it's temporarily slower because it has to rerun the query. We're constantly trying to keep your cached results up to date in real time as new data comes into the database and is replicated to ReadySet. So to show how that works a little bit, we have this little diagram here. So that, what you can see here is that when your application makes writes to the database, that is, you know, inserts, updates, deletes, it goes directly to the database, but then ReadySet acts kind of like a read replica and it just gets updates from the database using the same protocol that a read replica would use. So, you know, you don't need any kind of fancy database plugins or changes. It still speaks the same protocol and replication as well as querying. And then, yeah, and then ReadySet uses those replicated updates to keep your caches up to date so that when the application asks for a query result, it hopefully gets it straight from ReadySet with no, you know, no additional like cold read lag or anything like that. Cool. So next up, I'm going to sort of show you the process of getting up and running with ReadySet. We've worked really hard to sort of make the process of getting ReadySet set up as simple as possible. There's really two steps to this process. It is run ReadySet pointed at your database, change configuration in your application to point your application at ReadySet. As Nick said, you know, we speak the Postgres and MySQL client library, like the wire protocol for Postgres and MySQL. So if your application is already talking to one of those databases, your application can just talk to ReadySet. And then it just works. You can cache queries and you can, you know, play around with which queries are cached or which aren't. And, you know, ideally see some pretty significant speed up. So I'm going to do a live demo using is an open source application. It's a sort of real world production application. We didn't write it. We're not involved in writing this application. We picked it for a couple of reasons. One, it is a real world application. So the idea here is that this is as sort of, you know, as realistic as possible of a scenario for running ReadySet. It's built on next.js and prisma. So it's a stack that's hopefully relevant to some of the audience here. It's, as I said, mature. It's got a lot of queries that are some of which are simple, some of which are have gone through a lot of optimization, some of which are very slow. Some of those queries ReadySet can't cache. As Nick said, you know, we can't cache everything because we're still sort of working to support all of SQL. But the idea is, you know, we don't have to cache everything. We only cache the queries that are the most important and sort of the most critical to the performance of the application. We also picked because it's pretty easy to set up. You know, it's a node.js application. You just sort of run, you know, a yarn command and things just work. So I'm going to switch over to doing that demo. So this is I've got it running in a web browser here. It's running via a dev server on my local machine over here. It's the application is basically, if you're y'all are familiar with Calendly, it's basically like an open source version of Calendly. So the idea here is you can create like event types that allow people to book time on your calendar subject to your availability and then people can book various different kinds of events on your calendar. So some of those events can be recurring. They can have meeting links associated with them. You can create group events with people. It's actually a pretty nice application. So this is running just sort of out of the box pointed at Postgres. You can see I just clicked on the bookings tab here and just to sort of demonstrate ready set I've generated about 10 million rows in this booking table. So it's a, it takes a little bit to load this page when we're just running against Postgres. So an example of you know what happens when suddenly your application hits an unexpectedly high amount of load right you know and we can we can see things do indeed slow down. Yep. Yep. So, as I said, I'm running locally. Let's just go through the process of pointing this at ready set. So I'm also running ready set locally on my machine. It's running on port 5435. So, all I have to do to switch to ready set is I go to my config, which is just a dot n file and switch the port. And then I can run the dev server again, and it'll take a second to compile because it's a dev server so it's recompiling everything. And just like normal you probably have to switch more than just the port it's just that Griffin is running everything on a single machine so it happens that the URL change to point to ready set is pretty minimal in this case. Yeah, that's correct. Um, but we can see once once next JS compiles everything that I can still click around and the application still works perfectly fine I'm still getting the same results everywhere. But I haven't actually cached any queries yet so I'm not seeing any speed up on this bookings page. What we can do, sort of, there's, there's a few paths here to sort of explore what it looks like to cache individual queries. One, you can see I'm just logging queries here, prisma by default logs queries that it runs. So you can see there's there's a bunch of queries in the logs here. Ready set also keeps track of all of the queries that we're not caching that we're when we're not caching a query we proxy it to the upstream database. And I can look get a list of those queries by just connecting to ready set by a P SQL show, like I've done over here, and then running show proxy queries. And that gives me a list of sort of all of the queries that ready set is proxying to the upstream database, along with whether or not this query can be cached. And, you know, as you can see, this is this application is running quite a few queries. And just to be clear, when we say the word query here, it's useful to sort of be precise about what we're talking about here. Really, I think the easiest way to think about this is prepared statements. So you can see you know when prisma runs a query we've got these placeholders with the dollar sign here. And, you know, ready set also sort of thinks about queries in terms of sort of a query is the same query modulo a particular value for a placeholder in a prepared statement. And then when we cache a query. We're actually saying that we want to be able to cache individual result sets for values of those placeholders so you can imagine a query to load data for a particular user filtering by a user ID, we cache a query and we say cache this query for all values of the user ID, we don't actually spend memory caching result sets for every single user, we only store the result sets for the query for the users who are actually requesting that data so for the users who are actually logging in, and the idea here is, you know, if you have an application that might have, you know, thousands or even millions of users in your database, but only a few actually log in on a particular week you know there's this sort of log normal distribution of which users are logging in you have you really active users and then your users who sort of never open the application. And for a user who's never logging in, we don't want to spend memory and we don't want to spend compute maintaining the data in the cache for those users. So what we'll do is we'll only store data in memory for the queries that are actually the result sets that are actually being requested. And then as we sort of hit memory pressure will evict on on a least recently used basis. This sounds a bit abstract I think the takeaway here is that if you tell it to cache, you know, select star from users where user ID equals 14. It's not just going to cast the result where user ID equals 14 it's actually intelligent enough to kind of generalize from that and cache, the same query for other user IDs as well. Yep. So, I did some playing around with with this application ahead of time and I found one query that you know performs particularly poorly and that ready set can help with quite a bit. So this is query, it's got a, it's got a couple of aggregates, it's got you know I'm in an account, and it's filtering by as I said filtering by user ID. And this is this is, you know, I'm not entirely familiar with but just from reading the query it looks like this is getting sort of the earliest booking per recurring event, so you can have recurring events and you might want to say the next booking for a particular recurring event. And we can see if I run this query, I happen to know my user ID is 14 so I'm filtering by 14 by user ID 14. If you're on this query against Postgres. So like uncached by ready set, we can see it takes a fair amount of time. I mentioned earlier that most page loads have a budget of about 200 milliseconds for the page load to feel like instantaneous subjectively to the user. And, you know, a particular page load might do 10s of queries. So you, you really need, you know, each individual query to be pretty quick in order to fit that 200 millisecond budget and this one query just on its own is already blowing way past that by like a factor of, you know, like three. So, let's like walk through the process of speeding up this query with ready set. So, if I want to cache this query. All I have to do is just take the query and prepend create hash from in again this is all just in a P SQL shell. And now ready set is going to be caching the result sets for that query, and will, as Nick said, be able to say okay, we know that this user ID is 14 here but we're going to actually cache results that's for any user ID. And then, as I said, only store the result sets for the user IDs that are actually read. So now after I created the cache, I run the query again, it'll take a second to pre compute the results that's the first time you know it's warming up the cache, but then if I run the query again we can see it's running pretty quickly. So we've gone from, you know, about 700 milliseconds to a fraction of a millisecond. And, you know, you sort of this, I think, kind of changes the game, you know, another thing I wanted to talk about here is that a problem that you run into with a lot of web applications is that you, you tend to like query more data than you need there's this over here problem. You might have a function that says, give me all of the information that you have about this particular user, and then every page load calls that function, because some pages need some data and some pages need other data. But, you know, that function might be making a query that never ends up getting used on a particular page. And so one of the steps that you might take if you're trying to optimize this application would be to go through and you know add flags your function add a bunch of configuration to make sure that you're only querying the data that you need, you know, this is like why people use graph QL, but graph QL is complex and comes with its own set of set of trade offs. I think, you know, a much more compelling in my opinion solution to this problem is just to make querying data that you don't need so cheap that you don't care anymore. And I think that this, this sort of latency if you know if we're looking at sort of a fraction of a millisecond. You know, we could be doing 10s of these queries on a page load and we're not going to get anywhere close to our children millisecond page load time budget. And then the other thing that I wanted to talk about here is that, you know, ready set, you know, most caching solutions if you were to like do an insert into this table, you have to throw the results away, and then recompute the results. With ready set, if I do an insert into this table that's going to update the result set of the result of this cache. And then I rerun the query. We can see you know this number it used to be 100,008 now it's 100,009. This number changed, but the latency has not gone up, that's because we're not recomputing the results set for the query we're actually keeping the results set up to date in place, based on the data that is flowing into the upstream database. So, the idea here is that no matter how many writes you're getting your reads are still fast, and they're still as fast as you're seeing here. And all of these sort of queries that I've been running are just like sort of a one off query that I'm running in the P SQL shell which is not super realistic. In a real world web application you're not just going to be running one query at a time, you're going to be running, you know, thousands or hundreds of thousands of queries, a second, potentially if you're getting if your application is getting a lot of load. And to sort of walk through what that looks like. I'm just going to run PG bench which is a benchmarking tool that's that's distributed with Postgres. And just to establish a baseline I'm going to run this against Postgres itself. This benchmark dot SQL file contains the same query that we've been running. And what we're doing here is we're running 32 concurrent clients, and we're making 32 queries at a time. And we can see that when we when we run this against Postgres, our latency actually spikes, quite a bit so you know previously we were looking at, you know, somewhere around 700 milliseconds. Now we're looking at, you know, three and a half to four seconds of latency, because Postgres, a single Postgres database server doesn't scale super well. And our queries per second is like, you know, nine ish. That's not good enough for a real world production application that's getting a lot of load. So, this is the scenario where you might have no choice but to scale out with read replicas. With ReadySet, and you know I can run the same benchmark against ReadySet again by just changing the port. This is, you know, default connection settings I'm running this all locally. So if I'm going to run the same same benchmark against ReadySet. And, you know, I just just to sort of frame this. When we're running against the, we're running the PSQL shell we're using sort of the ad hoc query mode. These benchmarks are using prepared statements because ReadySet, like all databases, does better with prepared statements because we're able to only parse the query once. And we can see the latency actually gets even better than we were seeing in the PSQL shell for that reason. And the queries per second can scale up as high as you know around 200,000. And I think 200,000 is usually good enough for most applications I think you have to be doing pretty well for yourself for 200,000 queries per second to be not enough, but in the case that you are doing that well for yourself, as Nick said earlier, ReadySet can horizontally scale so we can run double the ReadySet servers, double the ReadySet cache servers, put no additional load on the upstream database because we're only replicating data out of the upstream database once and serve double this so we can serve 400,000 queries per second with double the servers. And we've actually seen, you know, even significantly better numbers than this on single nodes but this is just you know Griffin's development machine with the same machine so it's running on my desktop. Alright, so you might see all that and say well there's no such thing as a free lunch what do I have to give up. Is this too good to be true. So there are, you know, some small trade offs that you have to make with ReadySet, I think in general they're often not a huge deal but I'm going to talk about them a little bit so that you're aware of them. First off, most notable is, you do have to be aware that you're getting eventual consistency, you know you're not getting the same strongly consistent guarantees that you would get querying your, your upstream database. And what that means in practice is just that for ReadySet, the results you get in a cache, maybe very slightly out of date, you know there's a little bit of lag time for the data to replicate through and update your cache data. And, you know, that that may be less bad than you might think a lot of queries that's fine some it's not but you know as we saw, you can very easily enable and disable caching for individual queries as needed. But there's particular queries that you know you know that you really have to have those guarantees that that everything is up to date, you know, for example, like with a bank account balance you don't want to have eventual consistency there because you don't want the user to be able to spend the same money twice, because you know maybe they tried to spend it a second time before the the balance it updated in the cache results or what have you, you know, in that case you would you would obviously not want that to happen, obviously. But at the same time, you know, if you're saying hypothetical bank has a dashboard that the user can look at to see your recent transactions that might be completely fine to cash that because, you know, if they make a purchase and it doesn't show up on their phone app for, you know, a split second. No one's going to really notice or care about that that's not a big deal at all. Memory usage, I mean obviously this is an issue with with any caching solution when you're caching data, it has to go somewhere. So you know the more data you cache, the more memory use. But again, being able to turn caching on and off for individual queries makes that generally pretty easy to manage you can, you know, decide which which queries are worth caching and spending the memory on. And additionally, as we also mentioned, you know, you can scale out to multiple nodes if needed. If you if you really want to cache more data than will fit on a single node. That's also an option. Finally, this may also, you know, go without saying, but it works best for read heavy loads. I think, you know, when we looked at that diagram earlier where we kind of showed how the replication happens and how ready set keeps, you know, results up to date, you may have noticed that there was an arrow for for rights that goes straight from the client to the database rights go directly to the database, they don't go through ready set. So if you're building some kind of, you know, crazy right heavy application where you've got, you know, millions of iot sensors dumping data every second then you know ready sets just not going to help you with that. Maybe it will still help you with reads if you have, you know, a read performance issue for that same application but this is all about doing queries and not about ingesting data. And finally, you know, we're still early stage. We're still finding and fixing bugs, although we've been making really great progress with that lately. And, you know, not all queries are supported because in order to know how to keep data up to date in our caches we kind of have to actually execute the queries and know how the different SQL functions and operators and things work. And so we haven't implemented support for all of those yet but we're adding more all the time. And, you know, we're fully open source so we always welcome community feedback if there's any feature that's not there that you'd really like to see, or any kind of issues you're having. Please absolutely just go on to GitHub. Leave a comment file and issue and we'll be very happy to receive that and talk to you and take a look at whatever it is that you're finding and seeing. Oh, we do have a question. Oh yeah. Must must ready set be installed locally. Oh, that's a great question. It definitely doesn't have to be installed locally. It can be installed and you know, whatever, whatever kind of supported machine you want. But additionally we are. We are offering a cloud version of ready set that we set up and administer and run and support. So, you know, if you want to just play around with it absolutely download the open source version. And, you know, but if you want to run it in production for your company, and you don't want to deal with installing and maintaining it yourself we're more than happy to talk about cloud customers. We, in terms of deployment options we have a helm chart for running in kubernetes. You can also deploy it using you know sort of on bare metal or on an easy to instance. But yeah, if there's a particular way you want to deploy ready set you want help doing that we, I would say make a GitHub issue and we're going to be happy to help you. Yeah, yeah, absolutely. Thank you guys so much. Great. All right. Well, have a good one everyone. Thanks again.
33 min
12 Apr, 2023

Watch more workshops on topic

Check out more articles and videos

We constantly think of articles and videos that might spark Git people interest / skill us up or help building a stellar career