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 a 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 it. Because I think this is extremely valuable for backend 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 just sending way too many requests you could have something like a cash layer failure or expensive queries being hit very often or you could have a huge spike of right 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 go 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 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 reading data from the primary database is when we absolutely need to do it to process some right transaction and that's exactly what openai is doing right now along with that they are migrating their rights to sharded systems like azure cosmos db and this just shifts the right 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 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 handling it now is they shifting the join logic to the application layer as in instead of joining multiple tables in the database using sql they gonna use whatever backend they 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 jangles orm they write very unoptimized SQL queries. And when you're 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. The 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 right database first. I mentioned in the beginning that OpenAI runs all of this on one single right 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 open ai 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 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 5000 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 open ai scale you are going to run out of connections and the way that openai solved it is by using something called pg bouncer so pg bouncer 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 going to 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 open ai scale you need something like pg bouncer to manage these connections for you now moving on caching and i think this is going to 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 database by fetching the data from the database and then all subsequent queries after that they're just gonna 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 right 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 wild records while records are basically just you know records that tell you what changes have been made to the 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 vile 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 wild 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 root 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 or 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 know 30 seconds or something I don 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 means 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 pg bouncer 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 right queries to the database and if you want to see an example of how strict open ai 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. Hope that was helpful.