Bringing Vitess to Postgres  ## Metadata - Author: [[Changelog Master Feed]] - Full Title: Bringing Vitess to Postgres - Category: #podcasts - URL: https://share.snipd.com/episode/03a30204-7fb5-4445-a424-0ca4c73fb947 ## Document Notes ## Highlights - **Sugu's Sabbatical Reinvention** - Sugu took a three-year sabbatical to reinvent himself and do voluntary work. - He missed technical challenges and slowly got pulled back into engineering. Transcript: Sugu Sougoumarane I'm very excited to be here, too. Adam Stacoviak I'm excited to see you back at work. You've been off work. You said you just told me you took a three-year sabbatical. Is that accurate? Sugu Sougoumarane Yeah. I even wondered if I was going to come back until, you know, I started feeling the itch. Jerod Santo And yeah, that's what brought me back. Gardening, you know, ripe tomatoes. What kind of vegetable did you start growing? Sugu Sougoumarane I actually went on self-reinvention of some sort. I kind of did a full debug of myself of some sort. Adam Stacoviak Did you climb a tall mountain or how do you do that? Sugu Sougoumarane I don't know. Just sit at home, think, walks, and a lot of thinking. And I even got into doing some voluntary work because, you know, I realized, you know, as a human, I realized basically I thought I should be more human. And that led me into a bunch of voluntary work, which I actually started to do. And I even thought of going full time. So then I realized it's not something you can do full time. And that's when I realized that I'm starting to miss the fun of doing technical work. ([Time 0:03:52](https://share.snipd.com/snip/13739971-bd4c-4cba-bedb-c8ad10afebe2)) - **Vitesse's Evolution at YouTube** - Vitesse started as a solution for YouTube's frequent database outages. - It evolved from a connection pooler to a fully distributed sharded database. Transcript: Sugu Sougoumarane Yeah, yeah, definitely. The test actually, when we started with tests, we thought it was a six month project. So YouTube was actually in deep trouble scalability-wise. There were more than a handful of outages every day, sometimes 10, 12 outages per day, and they were all caused by the database. I wouldn't say outages, 10, 12 pages per day. Many of them would lead to outages, and they were all caused by the database. That's the time when my co-creator, Mike Solomon, and me kind of decided to solve this problem once and for all. So we decided to take ourselves out of this firefighting mode and think of a solution that will leap us ahead of where we are today. Because it felt like what we were doing now was a losing battle. It was like whatever we were doing, we were just going, things were getting worse by the day. And that's how Vites was born. And we wrote code for about a year, actually. It took us about a year to launch the first version. And the first version that we launched was just a connection pooler, which was actually the biggest problem we were having with MySQL at that time. And that immediately gave some breathing room for YouTube. And the way we solved it was we didn't solve it like how you would solve a problem, which is let's solve connection pooling. Because we sat and brainstormed about all future problems that we're going to have, we actually built an intelligent proxy that would not just connection pool, but would also understand Your query. So we actually built a parser in that connection pooler. And once this got launched, people started noticing that this so-called connection pooler is intelligent, and it can do other things beyond just taking care of connections, like, For example, fielding bad queries or watching queries and killing them if they run for too long. And so we slowly started adding those features, and it became an intelligent connection pooler that would protect the database completely. And that eventually evolved into, then we added sharding solutions it, like how do you reshard? And then we added one more layer, which is the routing layer. And then we realized, oh my God, this is now almost a fully distributed database. Why don't we make it a fully distributed database? Which was the final step where Vitesse became a fully sharded solution. The time we started in 2010, I think what you call is the V3 was around 2014 or so. About four years later, VTES could be adopted by anyone outside of YouTube. ([Time 0:10:50](https://share.snipd.com/snip/d7509f26-6d96-48b1-8ab5-9c9c4400e4e2)) - **Sharding's Hard Aha Moment** - Sharding is an old but underdeveloped concept that requires reinventing to work well at scale. - The crucial insight was that a sharded system needs to identify query parts that can be executed without modification on underlying databases. Transcript: Sugu Sougoumarane Think it is more of the first problem. The fact is like sharding, if you go and ask in a university sharding, they'll say sharding is one of the oldest concepts in databases. It's over 30 years old. But the issue with sharding is that nobody ever worked on it. Nobody ever developed it into something practical that will actually work in production at scale with performance, which is actually strenuously hard. Like for me to make the leap from an application sharded system to a system where the application does not care about sharding and generically implement it in a database, it took me about Like I literally sat and thought for three months straight, nothing other than just pure thinking, understanding SQL, understanding query complexity, understanding relational Algebra, going back all the way because, okay, how do I shard it? You go look for, you go hit the books, you go look at databases. Nobody has ever done this before. And so then I realized, you know, this needs to be basically reinvented from the ground up. So that was one of the hardest, one of the most strainful thinking things that I have ever done in my life. There were times when I used to get headaches thinking about it. It was that hard. And finally, one day I had the aha moment. It looks like I have an answer to all the questions to make this thing work as a sharded environment, as a sharded system. And it's possibly that is that like a typical, when you get a query, the typical way you break it down is different from how you would break it down in a sharded system. Because you get a select statement, you parse it, and then you identify each of those nodes and you just assign operations to it in a traditional database. And then you run the optimizer that rearranges these things. But in a sharded system, you have to identify groups of things that actually don't need to be changed at all because there is an underlying database that can execute that entire query With no changes. Because what's underneath is also a database. So being able to analyze queries and identify these subparts and be able to outsource it to a database and then do the rest of it on your database, your proxy that's actually sending the Queries required a different kind of thinking i think that is one reason that i could think of why sharding is hard can you describe your aha moment like what your realization was or did Adam Stacoviak You just try to describe it in other words and I missed it? Because it sounds like you're describing the problem and the system with the proxy in there, but like, what was it that cracked the nut that said, aha, we can do it this way? Sugu Sougoumarane Yeah. So how did I do it? The, the aha moment was actually the, there was an early aha moment, early aha question, which is, until the day when I said that, when, so before sharding, before this generic sharding Was there, the way the application worked was it would actually send a query. And within, along with the query, it'll give, you where to find the rows for this query. We called it the keyspace ID, which basically helped you. It says there's a lookup where you look up the keyspace ID. It tells you, oh, this keyspace ID can be found in this shard. The application had to guarantee that all the rows for that query lived in that shard. So that made it extremely simple. And keyspace ID was also a value of a column in every table that we had, which made it safe and simple, which means that you can't go wrong. And the question I asked myself is, this keyspace ID is a crutch. Can we move without it? And it was scary because the application knows the keyspace ID, but how would this system know the keyspace? If we test does not know the keyspace ID. So if you just sent me a query, what do I do with it? I don't know where to find this. And so I went to the application and figured out, the application is figuring out what this keyspace ID is. How is it figuring it out? And that's when I realized, oh, it's figuring it out based on the user ID. So there is a source. And it always turns out that that source is also in the where class of every query that it sent. And that's when I realized, oh, well, if the application is doing it, what if I did it for you? You say where user ID equal to X, and I will do this computation, which is a hash function, and figure out where it is and send it. So then the question is, oh, then in that case, how about the other queries? There were other complex ways by which application was computing the keyspace ID. And that's not like, what if we internalized each and every one of those things? Then the application does not need to ever compute the keyspace ID. We will do it. So that was the first aha moment. And then the second aha moment was, the second question is, but in that case, only these queries. So that limited the number of queries the application could send. The next question was, but then if we took this over, what if there's a query that came where a keyspace ID could not be computed? What would we do? That's when I realized, okay, then I have to understand the query. What if it's a join? What if it's a correlated subquery? What if it is a non-correlated? So that's when I had to go into relational algebra and find a way to map any random query to a query plan where I would say, oh, this query, I need to send it to all shards. Or this query, I ([Time 0:19:49](https://share.snipd.com/snip/cbacc3f2-a91b-41be-bb73-ed18d8530fc5)) - **Internalizing Shard Placement Logic** - Removing the application knowledge of shard location (keyspace ID) is challenging but possible. - The system can compute shard placement from query contents like user ID, enabling transparent sharding. Transcript: Sugu Sougoumarane The application had to guarantee that all the rows for that query lived in that shard. So that made it extremely simple. And keyspace ID was also a value of a column in every table that we had, which made it safe and simple, which means that you can't go wrong. And the question I asked myself is, this keyspace ID is a crutch. Can we move without it? And it was scary because the application knows the keyspace ID, but how would this system know the keyspace? If we test does not know the keyspace ID. So if you just sent me a query, what do I do with it? I don't know where to find this. And so I went to the application and figured out, the application is figuring out what this keyspace ID is. How is it figuring it out? And that's when I realized, oh, it's figuring it out based on the user ID. So there is a source. And it always turns out that that source is also in the where class of every query that it sent. And that's when I realized, oh, well, if the application is doing it, what if I did it for you? You say where user ID equal to X, and I will do this computation, which is a hash function, and figure out where it is and send it. So then the question is, oh, then in that case, how about the other queries? There were other complex ways by which application was computing the keyspace ID. And that's not like, what if we internalized each and every one of those things? Then the application does not need to ever compute the keyspace ID. We will do it. So that was the first aha moment. And then the second aha moment was, the second question is, but in that case, only these queries. So that limited the number of queries the application could send. The next question was, but then if we took this over, what if there's a query that came where a keyspace ID could not be computed? What would we do? That's when I realized, okay, then I have to understand the query. What if it's a join? What if it's a correlated subquery? What if it is a non-correlated? So that's when I had to go into relational algebra and find a way to map any random query to a query plan where I would say, oh, this query, I need to send it to all shards. Or this query, I can send it to just this one shard. Or this query is in multiple shards, but I cannot send it to all shards because I need to take data from here and then data from somewhere else. ([Time 0:24:01](https://share.snipd.com/snip/dc6baf84-66af-4296-afd6-3878e8eac23e)) - **No Best Sharding Scheme** - There's no universally best sharding scheme; each has tradeoffs and critics. - Vitesse treats sharding schemes as pluggable components, offering flexibility to user needs. Transcript: Sugu Sougoumarane And that's when I realized that there is no correct answer. And anything you choose, somebody going to hate it. Right. And that's when I came up with this idea of no first class citizens in sharding, which means that Vitesse will actually, every sharding scheme is a plugin, is an extension. Adam Stacoviak Gotcha. So you pick the one you want to use. You pick the one you want to use. And you don't have any opinions on that as Vitesse. Exactly. Exactly. Sugu Sougoumarane That actually goes back to actually a story from, I don't know if you've heard of Illustra. So this is 1990s. Michael Stonebraker had, I think, just launched Postgres. And from that, he founded a company called Illustra, which is a commercialization of Postgres. And the claim to fame for Illustra is that indexes need not be defined by the database. You can define your own index and do it as a plugin. And at that time, was at Informix and Illustra was actually winning deals against Informix as a startup. And Informix actually ended up acquiring Illustra. And I love that concept so much that I actually transferred myself into the Illustra team to develop pluggable indexes. And that inspiration remained with me all these years. And when I said, you know, we have to develop indexing for sharding schemes, I said, well, you know what? I'm going to use this idea where all indexes in the sharding scheme are externally defined. Adam Stacoviak Yeah. It makes sense because, I mean, oftentimes opinionated software is better. Like we do have a golden path or we do have a way that you should go. You can swap it out if you want, but this is what the framework or the tool thinks you should do. But when it comes to this, like I said, and like, you know, like the shape of people's data is so different and custom that there isn't necessarily a best way of sharding as you guys found. So it's what's best for your circumstance. And so why have a first class citizen when you can just say, no, you're going to, you're going to figure this one out yourself because we can't actually figure it out for you. I think that makes a lot of sense. ([Time 0:30:59](https://share.snipd.com/snip/76c0fbe5-a11e-44c5-a2a4-356343707671)) - **Postgres Native Principle for Multigres** - Multigres will be Postgres native, not MySQL-like, to embrace Postgres ecosystem fully. - Some Vitess features won't port over; improvements and high availability are priorities. Transcript: Sugu Sougoumarane There's a few, at this point, we have made a few policy decisions. Let's put it this way. The biggest policy decision is this has to be Postgres native. So that part, we do not want to compromise, which means that the end product should feel like it should be built for Postgres. So that is one policy decision that we have made. I don't know how far we are going to get there, but that's our North Star of some sort. And which means that from Vitesse, there are some parts we will definitely leave behind. For example, anything that's MySQL specific is not coming over. Sure. What else will we leave behind? We will leave behind anything that is legacy in Vitess. Anything that we built in Vitess, but continue to support because there are users using it. But if you built it from scratch, we wouldn't do it that way. So that we want to leave behind. And also anything that's not well implemented. If something is, it works, so we left it alone, but it was not well implemented. Given a choice, I would do it differently. So that we are not going to bring over. And everything else we want to bring over, for sure. So there are actually... And there's a fourth category, which is if there are things we can improve, we should. More specifically, I do want to improve the high availability story in Postgres. Because that is currently a big challenge that users are facing. There is no good high availability solution. So that is something that definitely want to improve on the Postgres side of things. So we'll bring some components over from Vitesse because Vitesse already has some HA components. So we'll bring some of those components, but we'll also build something that is very specific to Postgres. The place where I'm still confused about how to solve the problem, but we will figure it out, is how do we solve this sharding and query processing? Because Vitesse can do query routing. Vitesse has the concept of pluggability. The part that I'm thinking about is the Postgres plugability is more at the binary level, where you actually build extensions and link them into Postgres. Right. Which if we brought that in, how do we make these plugins work? Do we have to translate them or do we figure out a way to make them still work? If we ported the Go code over is the part that I'm working through right now. Actually, ([Time 0:38:01](https://share.snipd.com/snip/7db56970-9356-4048-8748-142f559bf2d6)) - **Prioritize Approachability Over Flexibility** - Favor approachability over extreme flexibility for users of Multigres. - Hide advanced features until users truly need them to simplify adoption. Transcript: Sugu Sougoumarane I realized that's a problem. And so what I want to do in the multigress is make it a lot more approachable. Actually, at the expense of even reducing those flexibilities. Because I think adaptability is more important, approachability is more important than flexibility. And if we add flexibilities, we want to hide them away from the user that they shouldn't have to find it until they need it. So that is kind of the approach we are going to use. And what that means is that it should be easy for a user to just take a multigress and just deploy it in their environment, and it should just work for them. Adam Stacoviak Do you know how that will manifest inside a Supabase, or is that more on the Supabase product team to decide? Will that be invisible to Supabase customers? Yes, absolutely. Sugu Sougoumarane Yeah. ([Time 0:56:03](https://share.snipd.com/snip/0f3fa7d9-fefb-4e09-bc65-cac68208444f)) - **Local Storage and Consensus Importance** - Storage should be local to the database to optimize IOPS and performance. - A strong consensus mechanism is crucial to avoid data loss on node failure. Transcript: Sugu Sougoumarane That people, because I have seen the good days of the database, where database like goes screaming fast, you know, like Slack and GitHub, they run like hundreds of terabytes of database Size, they run millions of QPS. And their latency is like, you know, millisecond, one millisecond, two milliseconds. That's like, that's the kind of latency they get. And they can't tolerate any higher latency. And I want to bring those days back into Postgres. So that is kind of what I'm shooting for. So ([Time 1:06:39](https://share.snipd.com/snip/186a58de-482c-4022-8cab-4523cc9648e9)) - **Multigres Solves Postgres Scalability** - No current PostgreSQL solution adequately solves scalability at very large scale. - Multigres aims to keep users within Postgres by providing a sharding solution at scale. Transcript: Sugu Sougoumarane Back to why I started this project, right? The reason why I started this project was because I believed, I still believe actually, that there is no scalability solution for Postgres. Not Neon, not Aurora. I'm an advisor to Metronome, which is a Postgres shop. They have one of the largest Aurora instances and they are struggling. They have no way out beyond where they are looking at moving data out of their Postgres instance because they've hit the limit. And now they're saying, oh, we need to do something about this scalability thing. And that's the reason why I wanted to start this project. And one of the things I thought is with Superbase, if not just like, so this is a problem that is meant to solve it for the industry overall. In other words, Neon could use multigress if they wanted. If they hit the limit of a machine, you could deploy multigress on Neon and run it unless there were incompatibilities for which you had to make changes. But even in those cases, the changes would be minimal. So that's what I came for. And for Superbase specifically, is that if Superbase is going in its trajectory, there are going to be users that are going to max out a single instance very, very quickly. Those limits. And their way out is you could go to Aurora maybe and that will extend your runway by maybe at best 2x. I'd be surprised even if you got like a 2x runway. As soon as your QPS doubles, you pretty much are at your limit. So the only way out of this is a sharded solution. And that is why I started this project. I said that, you know, like in MySQL, if you hit your limits, you could go to Vitesse, but there is nothing like that in Postgres. And we test for Postgres at even mediocre level of functionality will allow these people to stay in Postgres. Otherwise, ([Time 1:09:06](https://share.snipd.com/snip/8dd90688-f0ae-450b-a78e-80a4b2b67bc8))