OpenAI's Postgres Scaling — Full Breakdown & Transcript

[00:01] Postgres on a global scale with 800 million users and millions of queries per second? Well, we no longer have to guess because OpenAI just released a new article giving us a glimpse into their database infrastructure. And it turns

[00:16] out that what they are doing to scale Postgres isn't all that different from things that I've done on some of my projects. Here's the article that I'm referring to, released on January 22nd, titled "Scaling Postgres to Power 800

[00:30] Million ChatGPT Users". Now, it's a pretty long article with some interesting technical insights into the OpenAI database infrastructure, and I'm step-by-step. Instead, I'm going to jump into my drawing board and show you an

[00:45] example of what scaling Postgres looks like on a global scale. So, we have to start from somewhere, and that somewhere is going to be a single relational database. And of course, we're going to need some application users that are

[00:58] going to be sending queries to our database instance. So, this is how most systems start. You have just one database, and whenever you run into any database, and whenever you run into any trouble, the safest and simplest scaling

[01:10] approach is vertical scaling, where you add more infrastructure to your database. And I'm sure this is how they started with OpenAI, and this might be enough for most systems out there. Now, let's discuss some more advanced scaling

[01:23] approaches that you might explore if vertical scaling just doesn't cut it. One of the first things that the OpenAI team did is introduce read replicas of their primary database. Now, what's interesting to note is that they still

[01:38] only have just one primary write database, even to this day, but they are database, even to this day, but they are running a fleet of around 50 read replicas. So, what is a read replica? Well, most databases support the concept

[01:53] of replication. And what makes Postgres interesting is that it uses our write-ahead log to store all the changes that you make to the database and then Postgres allows you to create another database instance that's going to hook

[02:06] database instance that's going to hook into this stream of events and use those to replicate the changes made to the primary write database into as many read there's going to be a physical limit because a primary database has to stream

[02:20] the changes to all the read replicas, but this is a surprisingly scalable what that's going to look like is I'm going to expose a box that we're going to call the write-ahead log. Let's place it right under our primary write

[02:36] database and then we're going to have replication going from the write-ahead log into the read replicas. Now, what does this allow you to do? It seems like we're introducing more problems because the primary database has to do more

[02:48] work, but the reality is somewhat different. In most production systems, the read to write ratio is something like 80 to 20 or even 90 to 10 where you've got a significantly larger number of queries or reads than you've got

[03:03] writes. And this is also true for Open ISIS. So, what they did with the replication approach is route the queries so that insert, queries so that insert, update, and delete statements all still

[03:15] go to the primary write database. So, these are going to land here, but then we can introduce some sort of load balancer in front of our read replicas where we can send a read request which I'm going to represent with a select

[03:29] statement and then our load balancer is going to distribute this request into the available read replicas. So, that might look something like this where the load balancer picks and chooses which of the read replicas is going to satisfy

[03:45] the current query. And this gives you a significant advantage to scaling your ability to introduce more additional replicas if you need to satisfy more querying capability or more application reads. This also unlocks another

[04:00] which is avoiding a single point of failure. In a system where you've only got a single database, this database is your single point of failure. In a read replicated system, if your primary goes down for whatever reason, you can still

[04:15] satisfy at least reads inside our application by connecting to one of the replicas going down at the same time are pretty minimal. So, this is a good way to avoid a single point of failure, even though writes will stop working until

[04:30] back to being functional. Now, there's also another thing that you can do, which is maintain a read replica, which you're going to use as a standby write database. So, we're still going to have replication going to this database, but

[04:45] maybe we don't want to use it for satisfying the main write queries. Now, what this allows you to do is if the primary write database does go down, you still have your standby replica, which you can then promote to the new primary,

[04:58] effectively replacing it. And in theory, this should give you a pretty minimal amount of downtime. Cloud services have a lot of this automated. For example, Azure Postgres flexible servers or Amazon's RDS service that exposes

[05:11] of the box, where it's relatively easy to configure it, and they manage the infrastructure setup behind the scenes. And I have to note that OpenAI's workload is running on Azure, where I do believe Microsoft has a stake in OpenAI,

[05:25] which also explains the reasoning behind this. And we can expect that they have a lot of technical expertise from the Azure team on hand to help them scale this. So, what's the next thing after adding replication and a standby write

[05:38] replica? Well, if you've run Postgres on any large scale, you know that one of connections that you can spin up on your database server. And this is why connection pooling where you're not constantly recycling connections.

[05:52] Instead, you store them in a pool and reuse them for subsequent requests. If you're using EF Core, it also supports connection pooling on the application level, but there's a more robust approach called PG Bouncer, which is an

[06:05] additional component that you can run that acts as a connection pooler for your database. So, that's one of the next things they did, which was introducing PG Bouncer. And from my understanding, they have one PG Bouncer

[06:18] understanding, they have one PG Bouncer instance running per read replica inside of their system. So, all of the read replicas that we have here are going to have a PG Bouncer associated with them, and then a load balancer is going to sit

[06:30] in front and distribute the incoming requests to the available read replicas. Now, it's also important to note that this is all distributed on a global scale. So, there's also some level of routing the incoming requests based on

[06:42] your geographical location into the closest available instance. Now, all of this pretty complicated to comprehend because this is definitely not a trivial system, but there are some ideas here that you can take home and possibly use

[06:54] inside of the applications that you're working on. For example, I've used this approach with a primary write database and a couple of read replicas inside of working on. And obviously, we don't operate on this scale, but we still did

[07:09] users and a significant load that the system had to sustain. There were a few other things which we were using and also Open AI uses which I'm going to mention in a moment, but let's go back to PG Bouncer. So, as I said, this is a

[07:23] connection pooler. So, let me add a comment making that clear. I don't want this to be read. I'm going to add an arrow here just to denote what I'm speaking of. From the article, it says that introducing PG Bouncer reduced the

[07:37] cost of obtaining a connection from around 50 milliseconds to 5 milliseconds. And this is obviously a huge speed up when you're running millions of queries per second. One other thing that they are exploring is

[07:49] how to scale replication further by reusing existing read replicas. As I said, the primary write database is going to stream the updates from the write ahead log to the subscribed read replicas. And this is how they are kept

[08:02] in sync. Although this process is asynchronous. Now, obviously when you introduce a lot of read replicas, you run into some issues with being able to stream the write ahead log entries to the replicas in a timely fashion. And to

[08:14] solve this problem, they are exploring an interesting approach, which I haven't think about it, and it's called cascading replication. And the idea behind this is instead of streaming the write ahead log from the main write

[08:29] database, you can also reuse the existing read replicas that you have to further stream the write ahead log to any new read replicas that you might from my understanding, this is still a work in progress, and it's not

[08:42] implemented yet, but it's something they're exploring to scale this system further. Another thing you're absolutely going to need at this scale is a cache. they're using, but I assume it's going to be some variation of Redis because

[08:56] they're running this in Azure. And the idea is very simple. You introduce a cache in front of your database queries, and before sending a query to the database, you ask the question if this data is available in the cache, and if

[09:09] you can serve it. A big problem that you run into at this scale is a cache stampede, where a cache value might expire, and you run into a problem of sending multiple queries to the underlying databases to fetch the

[09:22] missing data. This is where they implemented strict locking to prevent a cache stampede, which is something I talked about in a recent video that's going to pop up in the right hand corner here, where I showed you a step-by-step

[09:34] example of how we could solve this on a single system, and I gave you an idea of distributed system. And this is essentially what OpenAI is doing at global scale. A couple of more things to note is query optimizations. So, just

[09:48] making sure you've got right indexes in place where you aren't over-fetching any data that you might not need. Then things like doing joins in memory server to save CPU resources on the

[10:01] database servers. They also have robust rate limiting at the load balancer at the PG bouncer level at the application levels to prevent a huge burst of queries at a single point in time reaching their underlying databases

[10:14] that they are trying to avoid. And then another thing that you have to think about at this scale is schema management and any operations that are going to lock the entire database. For example, changing the data type are strictly

[10:27] forbidden. Now, obviously this applies to all existing tables, but they also article which I'm going to leave in the description of this video if you want to learn more. So, let me zoom out to show you the whole picture of what the OpenAI

[10:43] on the article that they released. And I have used some of these techniques myself with success. However, I never really had a chance to operate on a global scale with millions of concurrent users like OpenAI does which obviously

[10:56] introduces some serious infrastructure problems that you have to solve in order to continue operating at that scale. If you want to see a sample code example of replica database would look like, you're going to find that in the pinned comment

[11:10] right below where I shared a project that I worked on which is using AWS RDS for the replication part. If you enjoyed this video, go ahead and smash the like Subscribe to this channel if you want to see more videos like this one. Thanks a

[11:24] lot for watching and until next time stay awesome.