800 million users, 1 database

So this is going to come as a shock to most people. It certainly came as a huge shock to me, but apparently, you can scale one single Postgres database instance to 800 million users. That's exactly what OpenAI has done. They literally only use one Postgres write instance, and they're powering 800 million ChatGPT users with it. I never had any idea that you could scale Postgres to such a large extent. And look, this hasn't been easy for them. There's been many different challenges that they've had to go through and solve. And yeah, I just stumbled on this article, and this is so well written. There's so much wisdom in here, especially if you're some sort of, like, if you're a back-end developer, this is gold for you. Like, the amount of knowledge they're sharing over here, it's insane. I'm gonna link this article in the description, and I highly recommend you go and read it yourself because I'm probably not gonna do it much justice in this video. So make sure you go read this. It's very fascinating. But let me also go through the... But before you do that, let me quickly go through all of the challenges that the OpenAI team faced and how they actually overcame them, because I think this is extremely valuable for back-end developers who are trying to scale their applications to thousands, hundreds of thousands, millions of users. So the biggest reason a Postgres database might fail is because of overload, right? And the overload happens when you're just sending way too many requests. You could have something like a cache layer failure, or expensive queries being hit very often, or you could have a huge spike of write queries. Any of these could be a potential cause, and that would cause way too much load on the database. That load would then cause requests to become slow or time out. And then, when those requests time out, we need to retry them, obviously, which would cause even more requests due to all those retries, which would then further increase the load on the database. And before you know it, the entire thing just crashes, right? So how do we solve this? Well, the first thing we need to do, and there's like seven or eight things that these guys had to do, and the very first thing is to reduce the load on the primary database as much as possible. If you look over here, we've got like a ton of different write queries being sent to the primary database. That is a lot of requests. How do we reduce that? Well, well, the first thing we can do is just offload reads, right? We don't need to use the primary database to read data. We can route that traffic to replicas, right? And the only time we should be reading data from the primary database is when we absolutely need to do it to process some write transaction. And that's exactly what OpenAI is doing right now. Along with that, they are migrating their writes to sharded systems like Azure Cosmos DB, and this just shifts the write-heavy workload from their primary database to something else. And lastly, we talk about app-level optimizations. This is going to be most applicable to most of you developers, but basically, you want to fix things like redundant write bugs, places where you're writing the same data twice or just making redundant write queries. You also want to adopt a lazy writes approach to smooth traffic spikes. So lazy writes basically just means instead of writing all the data in one go, you send multiple smaller queries that are rate-limited so you don't cause way too huge of a spike in the database CPU and whatnot. And you implement strict rate limits for things like table backfills. Again, so you don't end up sending way too many large write queries all at once. Along with that, they made sure to optimize their database queries because if you've got way too many expensive queries to run, that's going to cause a lot of load on the database, especially if you have a traffic spike for some reason. Maybe you launched some new product or something, and you've got a ton of users. That, along with all these expensive queries, is going to cause a lot of high system usage, which is going to cause slower responses, and, you know, before you know it, the database crashes, right? So you need to write better SQL queries. The way OpenAI is doing it now is by simplifying joins. We all know that multi-table joins can be very complicated and very expensive for the database to handle. The way they're handling it now is they're shifting the join logic to the application layer. As in, instead of joining multiple tables in the database using SQL, they're gonna use whatever backend they're using right now, which is Python or Node or whatever. They're gonna join those tables in the application layer over there. Along with that, make sure to audit the ORM output because you might not have noticed this, but most ORMs like Prisma, Drizzle, Django's ORM, they write very unoptimized SQL queries. And when you're at a scale like OpenAI's, you cannot tolerate unoptimized SQL queries. So regularly review those ORM-generated SQLs and try to rewrite them to just perform way faster. Along with that, enforce timeouts because you don't want some long-running idle transactions that aren't really doing anything. Sometimes that happens, and you need to make sure that those get timed out and cleared automatically. Next problem that OpenAI needs to solve is their single point of failure. Single point of failure basically means that if one server went down, everything would just crash, right? So let's talk about the write database first. I mentioned at the beginning that OpenAI runs all of this on one single write database, right? We don't have any replacements. That is a huge problem because if this one primary database were to go down for some reason, we would not be able to serve our users at all. So how do we solve that? We have a replacement write database. So make no mistake, all of the requests for writing new data are still coming into this primary database, but we also keep a replacement database that is continuously synced with the primary database. So any change in this database is going to show up in this database as well. And if for some reason this primary database were to go down, you would have a replacement over here that could take its place pretty much instantly, so we would have zero downtime. Now, along with that, we also need to have the same kind of system for our read replicas. So OpenAI runs about 50 read replica databases across the world, right, in many different regions and all that, and it maintains clones of these root replicas so that if one were to go down, like if one were to go down over here, we would still have a bunch of replicas that could continue serving users. So they maintain more than one read replica in every single region. And let's say this database were to go down. Well, we still have two more that can keep serving our users, so we're not going to suffer some regional outage. Next, they implemented something called workload isolation. And this is something called the noisy neighbor problem. Basically, you've got a limited number of system resources in your database, right? And sometimes it turns out that there's just one workload or just a few couple of workloads that are consuming most resources. And we don't want that because all of these requests over here, they need to be fulfilled as well. We can't have just one workload consuming all of the system resources, and that's basically the noisy neighbor problem. And the way OpenAI solved it is very simple. We basically just divide all of our database requests into low priority and high priority, right? We basically have these two buckets where we're going to put our requests, and we're going to make sure that all of the high priority requests get served first. And no low priority request is going to consume all of our system resources or keep our high priority requests from executing. And this is a very simple thing, right? You just have two buckets where you're going to organize all of your requests. This isn't anything complicated, but it ensures that high priority requests get executed quickly and efficiently. And low priority requests can just wait, right? Because they're low priority. The next thing they did is to implement connection pooling. So Postgres does not support more than 5,000 connections, right? That's the maximum over here. And to be honest, I haven't faced this problem yet because I'm not trying to serve hundreds of millions of users. So this might or might not be applicable to most of you. But if you're at OpenAI scale, you are going to run out of connections. And the way that OpenAI solved it is by using something called PgBouncer. So PgBouncer is basically a Postgres connection pooler, and it's going to help you manage your database connections with Postgres. The way OpenAI deployed this is you would have your user requests coming in, and then they would be sent to a Kubernetes cluster. And then, in every single Kubernetes deployment, you have more than one PgBouncer pod. You have at least two PgBouncer pods, which will route connections and manage connections to every single read replica. And that's how they're using Postgres and PgBouncer to manage their database connections so they don't run out like they did over here. Because, look, this problem is gonna come up, right? You're gonna forget to close some connections, or you're gonna have some idle connections, or you're just opening up way too many connections for every single user, and you just end up running out of it, right? So when you're at OpenAI scale, you need something like PgBouncer to manage these connections for you. Now moving on, caching, and I think this is gonna be very important for most of the people watching. Here's what happens, right? You have a caching layer that's serving your users' requests. The execution time is very fast, the response time is very fast, everything's great, everyone's happy. But suddenly, you encounter a bunch of cache misses. There's a sudden spike in cache misses for whatever reason. There's a lot of data that isn't present in the cache. What's going to happen? We're going to have to send all of those queries to the database. So we end up sending so many queries to our read databases, and that just causes such a sudden spike in the database load. And so we need to avoid that. And the way they did that is very, very interesting. Basically, you would implement some sort of a locking mechanism, and then only one query would grab the lock, right? And then it would go to the database, find the data that it needs, and then it would release that lock, and then it would update the cache. So one query is going to update the cache by fetching the data from the database, and then all subsequent queries after that, they're just going to read from the cache. And this is basically genius because we know that we just want to fetch the data from the database once, right? And then we can just use the cache. So why would we send all of these queries to the database when we only really need to send one, right? Once we have the data, we can just update the cache, and then all the other queries can just use the super fast cache that we have. And this locking mechanism over here is doing most of the work. You need to have something like that so that all these other queries aren't necessarily trying to send requests to the database. You need to have a lock that only one query can hold at one time. Next, we need to actually scale our read replicas because here's the architecture that they have right now, right? They have one primary database where all of the write changes are made, right? Users writing new data and all that. Those changes need to be synced to every single read replica over here. And I mentioned before that OpenAI maintains at least 50 read replicas. So sending the WAL records, WAL records are basically just, you know, records that tell you what changes have been made to the database, what new data was added, what data was removed, etc., etc., right? Basically, syncing changes with other database instances. If you have just one primary database sending these WAL records, sending these changes to every single read replica, remember we have at least 50 read replicas, that's going to be very hard to scale because you're putting the responsibility of this on one single database to manage the entire army of 50 other databases. That's very hard to scale. So the way we solve this is a little something called cascading replication. Basically, the primary database is going to send the WAL records to one intermediate replica, which is then going to send it over to all the other intermediate, all the other read replicas below it. Same process over here. We send it to an intermediate replica, which then sends it to the read replicas below it. And this way, the primary database just needs to send these records to just a few read replicas, and then those read replicas can, in turn, send them over to all the other replicas so that all of them stay in sync, and the primary doesn't have to manage every single read replica on the list. Another very, very important thing that they're doing is implementing proper rate limits. Basically, that means limiting the number of requests in a certain time period, right? As in, let's say one user can only send, like, five, 10, 15 requests every, I don't know, 30 seconds or something. I don't know. You need to figure this stuff out for your own unique database and needs, right? But when you have sudden traffic spikes, right? Maybe some post went viral, or you launched a new feature or something. You have sudden traffic spikes, you have surges in expensive queries, or you have retry storms. Retry storms basically mean that a lot of requests are failing. And so all of them need to be retried after some timeout or something. And that's going to cause a cascade of requests to your servers, which is going to increase the load and everything. So OpenAI made sure to implement rate limits on every single layer across their entire architecture: the application layer, this is the code, like the back-end code, like Python or TypeScript or whatever; the database, which is Postgres; and even the connection pooler, PgBouncer. They've got rate limits across every single layer in the entire architecture, and this just ensures that these sudden traffic spikes and expensive queries aren't going to just destroy the entire server, the entire system, right? Now, one thing to ensure when you're setting these rate limits: you want to avoid retry intervals that are way too short, right? Because they're going to trigger these retry storms that we're trying to avoid. If you have a very short retry interval and requests start failing, you're going to have, like, hundreds of thousands of requests just being sent constantly because there's no delay between the retries, right? So make sure you have a longer retry interval. I think for most of you, five or 10 seconds would be more than enough. And if requests keep failing even after that, you would increase it to 30 seconds, 60 seconds, and so on. And if they keep failing, you would just inform the user that, hey, something's clearly messed up because none of these requests are working. And you would have some sort of a system to notify yourself about those outages, and then you can go fix that. And lastly, OpenAI is very, very serious and strict with their schema management. So basically, there's not a lot to do here. You basically just want to avoid schema changes that are going to trigger a full table rewrite, right? So there's some schema changes. You know, schema changes are basically things like adding a new column, removing the column, adding some data to some column, et cetera, et cetera, right? Some of these schema changes are going to trigger a full table rewrite, a rewrite of your entire table. And as you can probably imagine, if you have millions of rows, that's going to be very expensive for the database. So they just avoid that like the plague, right? They avoid that as much as possible, and they enforce very strict rate limits on schema changes to prevent write spikes. Because let's say you do have to, you know, execute a full table rewrite. That's going to be very expensive for the database. That's probably going to take your entire database down and stop serving your users, right? So how do you do something like that without any downtime? You implement very, very strict rate limits where you're basically, I don't know, like, for example, updating, like, a single column every three seconds or something. Very strict rate limits, so you make sure that you don't send us a huge spike of write queries to the database. And if you want to see an example of how strict OpenAI is with this, on their blog, on the article that I showed you, they've mentioned that some of these schema changes have taken them over a week, over one week, to update the schema. For most of you, you can update your schema in minutes, right? In seconds, by just running a simple SQL query. But for OpenAI, when you're serving hundreds of millions of users, you don't get that luxury. And so there's a bunch of schema changes that have taken them weeks to fully complete because they have such strict rate limits, because they can't send too many write requests at the same time, because that would overload the database. Now, you might think that weeks to change the schema is way too long, but it ensures stability. It ensures uptime. It ensures that you don't take your website or your servers down because you send way too many requests. And all of that is how OpenAI is serving 800 million users with just a single Postgres write instance. Very fascinating, very complicated, genius stuff. And again, go read the actual article. They've explained all these things way better than me. Go make sure you read that. And yeah, that's about it. Thanks for watching. I hope that was helpful.