Citus + StackGres

0:00Nik Hello, hello, this is Postgres.FM, as usual.

0:02 I'm Nik, PostgresAI, and as usual with me Michael, pgMustard. Hi, Michael.

0:08Michael Hi, Nik.

0:09Nik We have a great guest today.

0:11 Did we have you already? Álvaro from Ongres. Álvaro: Hello. No, it's my 1st time here. At least as a speaker, not as a listener.

0:21Nik Thank you for coming.

0:22 I know you're a very active listener. We definitely have you on Postgres TV channel, on YouTube channel, talking about licenses. That's why I'm confused. Do you remember? Álvaro: Yeah.

0:35Nik And I know we will touch a little bit licenses today, just knowing

0:39 the topic. Yeah, thank you for coming. I'm especially excited that today's topic will connect so many dots, right? Performance, sharding, Kubernetes operator, what your company is doing. It's so open source and true open source, right? I'm very excited, looking forward to dive into this chat because many topics I'm very interested in myself and my company also, they are connected today.

1:07 Álvaro: No, thank you very much for having me. It's been a lot of episodes listening with you over the year and now being present here, it's very exciting for me. So thank you.

1:18Nik And today we will talk about benchmark you haven't published

1:22 yet. So thank you for coming with results being still prepared. That's great because I know we scheduled this episode before I think you decided to work on it. So this is like... Álvaro: Right, that's correct.

1:36Nik Action happening in real time.

1:38 That's great. And yeah, I received your pictures, 3 charts about benchmark data, benchmark results this morning, so I'm catching up. Where should we start actually? Because this is a great topic, right? So how Postgres can scale, what numbers we can achieve. Maybe we should start mentioning that Michael and I had an episode about our company benchmarks on 1 node, no sharding at all, just 1 node, we achieved 4 million TPS a couple of years ago.

2:07 It was a couple of years ago, right? With SELECT only primary key lookup just to see how it's not super huge compared to some other database systems, which might be faster in specific workloads, but it's great that general Postgres can achieve that. And you can give us numbers much bigger because you involve sharding, right?

2:31 Álvaro: That's correct. If you want, I can start a bit with my thesis about sharding in general for Postgres. I think it's a field where we're seeing a lot of innovation happening, and I love this. Innovation and competition are great, are what drives us forward.

2:49Nik Let me interrupt you with plugging in episodes with Sugu,

2:53 Multigres I think we had 1 or 2, and definitely we had 2 episodes with Lev PgDog. So There is an innovation starting last year, which is great. And I know you have your own opinion. Apologies for interrupting. Let's continue. Álvaro: Yeah. Yeah. I do have my opinion. Basically databases, they take a long time to mature.

3:16 Databases need to be the places where you can trust your data, that it's gonna be there, that you're durable, and they obviously obey the rest of the properties that you expect from a database. And typically new databases are risky or tend to lag adoption because they need to mature. When you're sharding, my thoughts keep being the same.

3:35 You really need the mature and trust solution, a boring, I would say, technology to run your shards because now you don't have 1 cluster, you have many clusters. Each shard, each portion of the database, it's a cluster on its own. So the software that manages all that needs to also be well-established, let's say.

3:53 And again, in competition and innovation are absolutely great and very, very welcome. But I believe that there's a great technology out there, open source for sharding Postgres that's been in the market for, I guess, like more than 10 years called Citus. And Citus is just great. Might not be perfect, but it's really good enough or more than that even.

4:15 And a lot of people may have forgotten about it or put it a little bit aside. Myself, the same several years ago, until I started with thinking, OK, people need to shard Postgres and people need to scale Postgres. And not only because of the performance directly, because as you demonstrated, you can run very high TPS on a single node.

4:37 But sometimes it's also about the blast radius. Like if you're running a SaaS shop or an e-commerce and your database cluster goes down for whatever reason. Sometimes it's a configuration issue with the network, whatever it is, right? Your cluster is down, then your whole thing is down. If you shard and you split your database into, let's say 16 logical shards, right?

5:01 Or workers. And then 1 goes down because of a configuration error, then it's only a small percentage of your users that are effectively down, which it's not good, but it's definitely not 100%. Your PR is completely different night and day, right? So a lot of people now need to shard either because of performance reasons, because they cannot scale the writes to a single primary, and or because of high availability and blast radius reasons.

5:26 And we always had Citus there. And Citus is fully featured. It has almost everything that you need for sharding, for running complex queries that go down, they're pushed down to the workers, and has the tools for rebalancing, for dealing with hot shards, for almost all operational activities that you need to perform on a sharded database and it just works.

5:48 I believe because their initial focus was more OLAP style or mixed OLTP OLAP that people started considering Citus only for that and it's definitely good fit for that but it's also really good for OLTP.

6:03Nik 2 points here.

6:04 1st of all, you said we always had. Let me disagree with you because when I actually was looking into Citus, it was several years ago. The Key features were not open source. For example, online rebalancing. So we didn't have it in the open source area. It was like you need to have a contract with them to have online rebalancing, so it quite quickly becomes out of scope if you want to rely on open source and avoid vendor lock-in.

6:33 Now it's different. It has changed a few years ago. All features after acquisition by Microsoft, at some point all features went to open source, which is great. But it's not like we always had, right? Álvaro: Yeah, you're absolutely right. And thanks for that. I think, if I'm not mistaken, Citus version 11 was the 1st 1 that included everything as open source.

6:55 And that was, I think, 2022. So 4 years ago, 4 years and 0.5 ago, that was released as fully open source and has been since then. So yes, you're right. For this podcast,

7:06Nik you're right also, because this podcast exists for years.

7:09 Exactly. So, good. And the 2nd point is OLAP versus OLTP. My concern, like, last 4 years, I think I didn't dive into Citus. I remember, diving means like I run it, inspect myself, the benchmark and so on. I did it before we started podcast actually. I remember I was reporting some bugs And since then I didn't dive into it, but my concern is we didn't see great examples of some well-known companies, SaaS companies, for example, running it.

7:44 Like, you know, if somebody like GitHub scale would be running Citus, I wouldn't have any concerns at all. What are the great examples? OLTP style. That's my concern. And I think it's still like, I agree, like what numbers you bring, and this is interesting. We dive into them today. And I was in talks with Citus team some time ago, maybe a couple of years ago.

8:07 And they presented me some examples which were not OLTP. For example, some search for, I think, lawyers of video, where I thought like, actually lawyers can wait 10 seconds to find problematic materials and so on. It's not OLTP when we know 200 milliseconds, our very 1st episode in this podcast means that humans already notice delay.

8:33 Álvaro: So I think there's 2 angles to what you're saying. So 1 is the technical 1, and the other 1 is the marketing or trust 1, let's say. On the trust 1, I would say you're right, meaning that there's no such great examples. And this actually piggybacks on what I was saying. It looks like it's a technology that didn't get the momentum it could have had, or it could still has, because of the technological means that it conveys, right?

8:58 I can tell you that we have right now significant examples of this kind of usage. We have now a customer running Citus on StackGres that is actually growing database towards 2.2 petabytes by the end of the year. And...

9:11Nik Wait, hold on 1 2nd, 2.2 petabytes?

9:14 Yes. It's just 1 big Citus cluster? Yes. Álvaro: Running on StackGres? Yes.

9:21Nik Okay.

9:21 How many nodes? Álvaro: 24. Each node is, I'm not sure if it's 64 or 96 cores, and 0.5 a terabyte or more of RAM.

9:33Nik Wow, okay.

9:34 Álvaro: Those numbers are actually quite impressive. It's an IoT system and they ingest data massively from millions and millions of devices that send heartbeats every 5 minutes. And they have a 10 day retention period. So basically they're deleting 1 10th of the database and ingesting 1 10th of the database every day.

9:55 Right now they're in ramping up period, and as of today, they're ingesting some 30 plus terabytes per day, and this is gonna grow to 200 terabytes per day. So they're growing by 6X by the end of the year. And yeah, this is running on a single Citus cluster. We can discuss whether this is a little bit OLTP or OLAP.

10:15 It's a bit of both. Ingestion is OLTP. Queries, there are a little bit of both. Also OLTP queries when they're accessing health information about a given device. Or more aggregated reports, like how many devices are down or having some issues. So it's a bit of a mixed workload, but I think it works. Again, I agree, there are not so many public great examples about using Citus.

10:40 Citus also introduced some releases ago, the support for sharding by tenant, which is actually directly towards SaaS enterprises, right? The SaaS applications where you don't shard by row, you shard by tenant, which is a great fit for those cases too. Typically, these cases where you create like a lot of tables for every tenant.

10:59 So it's another way of isolating those workloads. But from a technical perspective and speaking.

11:05Nik Yeah, I just, I just, I'm, I'm, I'm smiling at my glasses

11:08 because they said, they started arguing with me saying that there are public well-known names like Heap and the Cloudflare named on, on Citus pages for, for years. But, but both of these cases are analytical cases, 1st of all, and both these cases went away from Citus, as we know. Like, I was at Meetup when Cloudflare explained how they moved away from Citus to ClickHouse.

11:36 And I know Heap Analytics moved somewhere else as well. Heap Analytics was a great case, but again, analytics. And Cloudflare, it was analytics, DNS analytics. So it's not OLTP. It can be HTAP. Yeah, I agree. And your case is interesting. It's not what I want to see, like I said, GitHub or some social media or something, when there are many humans working.

11:56 Your case is a great case for sharding Specifically because it shows exactly where the problem with the biggest pain of single cluster is a lot of writes. This case is highlighting this particular use case, which is great. But I'm still like, my heart is with social media, SaaS, like these things, like e-commerce, online stuff, where a lot of humans try to deal with some system and then they want greater responsiveness of all API calls.

12:28 Álvaro: So let's talk about that because part of my response also was coming from a technical angle and from a technical angle we've been running some benchmarks and 1 of the questions whether to say the system is ready for OLTP or not is the number of concurrent queries that it can run and the latency at which those queries run.

12:46 And Citus can run on millions of transactions per 2nd, even under 1 millisecond p50. I would call that OLTP.

12:56Nik I agree here.

12:57 If you check very fast SELECTs, we have benchmarks. We did it multiple times for actually all sharding systems, open source sharding systems we can reach, including SPQR, Citus, Multigres actually we also tested, PgDog, something else. And Citus, you checked for latency overhead, off latency, latency overhead benchmark, simple.

13:22 Which like if primary key lookup is sub millisecond, you don't want another few milliseconds to be introduced. And Citus was actually looking very well. It was introducing like 200 microseconds or so, as I remember. Álvaro: Yeah, I actually have drilled down on the CPU consumption per layer for this kind of queries.

13:44 And the Total aggregated CPU consumption per query, including the coordinator, including the worker, including the connection pooling, excluding the network, is somewhere in between 300 to 400 microseconds.

13:59Nik Microseconds, exactly.

14:00 Yeah, yeah. Okay. Slightly different from what I remember from our benchmarks but well the point here is that it can be very quickly eaten by something like network complexity right you won't notice this small overhead behind something else like just hops between yeah Álvaro: the networks that can be worse than that yeah yeah

14:18Nik that's I agree yeah

14:20Michael Álvaro when you query when you're doing that check are

14:23 you going through a coordinator in Citus? Because I know now you can query directly 1 of the workers. Would that reduce the overhead? Álvaro: So, yes, the usual way you wire up a Citus cluster is you have workers, which are where you split the data into and then you have the coordinator, which acts as a metadata entry point, but also as a query router.

14:49 And so it is the entry point by default. It's true that Citus at version 11 introduced a functionality where you can query also the worker nodes directly. There's 2 reasons to do this. 1, if you know from the application perspective, the sharding algorithm and you know which shard to talk directly, which is probably not a good idea, but technically sometimes you can do this.

15:11 It is a good idea actually, but the algorithm is a little bit internal to Citus, right? If it would be like clear, exposed algorithm, then it would be very good for applications that want to implement that algorithm, then talk directly to Shards. But at least Citus gives you the opportunity to do that if you want to do so.

15:26 You can target directly the workers. And if you target directly the worker, there's no query routing. It goes directly and resolves the query locally, if it's local or fans out to other workers if required. But the other reason is to try to scale the single coordinator. And I believe this actually also piggybacks a little bit with what you were saying, Nikolay, that Citus maybe was abandoned by some project before, like Cloudflare.

15:50 And Citus can scale workers horizontally as much as you need. I was referring to this 24 node cluster before, and there's been reports of much larger clusters, like 60 nodes or more. And they scale very well. However, the coordinator remains only 1. And if you outrun the coordinator, then you're hitting a ceiling.

16:11 That ceiling is normally quite high and actually we've measured that ceiling, but it exists, right? So that's why Citus introduced functionality, you can query the routers directly, and then you have like essentially multiple coordinators. But I don't think that's the way to go either, because then you're now contending workloads.

16:28 So you have nodes that act both as query routers, which consumes CPU, consume resources, notable, they require connections, and they spread all the connections to other nodes, making also connection math harder. And they're also serving data and queries. So it's a bit of a mixed workload that I believe is hard to dimension and do capacity planning around that and makes everything much more complicated.

16:53 So it's not a perfect solution either.

16:56Nik I think we forgot an important piece.

17:00 Why Álvaro at all talking about Citus, right? This is a good question. You're doing something else. Why are you talking about Citus at all? Citus is in the hands of Microsoft. What happened here? You developed StackGres, and then somehow these paths joined, right? They met these things and you have examples.

17:23 That's why you mentioned this 2 petabyte example, right? Can you explain a little bit what's happening, why you focus on Citus so much lately? Álvaro: Well, it's not me, it's what customers and users request. I see a growing concern and need to scale Postgres beyond the single primary for the reasons I mentioned before, write limitations and also blast radius.

17:45 And that's why we are also seeing an explosion of new sharding technologies for Postgres. So this is a real need and is driven by market demand. We are seeing a lot of people coming to us asking how we can shard our database. And StackGres, it's a platform for running Postgres, right? That's it, it's fully featured, it's really advanced, but it's a platform for running Postgres.

18:06 And so we saw this need and we saw early that we had to provide users a way to shard. And actually we were in a very privileged position, I would say, because what we've done in StackGres with single clusters is orchestrate and allow you to run the whole stack of components that you need to run Postgres, connection pooling, backups, monitoring, all that, all automated and all packaged.

18:28 So deploying it is extremely easy while also following best practices that we have from our company's operational experience. But then we saw that creating a Citus cluster involved a lot of manual work because it's no longer 1 cluster. Now, if you think about it, and the model we follow in StackGres is that every component of the Citus cluster, the workers, the coordinator, all of those are a Postgres cluster in itself, typically with more than 1 instance also for high availability purpose, right?

18:59 And they need to be wired up precisely with the same configuration parameters across many places or otherwise it will not behave as a single cluster. So it's not only a lot of work to wire up as let's say a 24, 48 node the Citus cluster but also to do it right. And With our automation and orchestration that we built for a StackGres, we could do this work.

19:19 And now the reality is that if you use a StackGres and want to create a sharded cluster with Citus, by the way, we support all the other sharding technologies in StackGres, not only Citus, even though it's the most used and the most feature complete, I would say solution for sharding that we have. But with the 13 lines of YAML, really high level, you can create a Citus cluster of 60 nodes in a few minutes.

19:43 You can even do from a web console, Click, click, click, boom, and you get the sharded cluster. There's nothing else compared to this other than the managed service and Microsoft offers on Azure. And that's the gap we wanted to cover because we saw demand and it's paying off. I would say a significant percentage of our new customers and users come for sharding.

20:04 It is very widespread and it's a need that few people are focused on solving, especially using a technology that is proven and well maintained as of today like Citus. It's a

20:14Michael good point about Citus being on Microsoft's platform

20:17 and it could because it's AGPL it means other platforms are going to really struggle probably just can't offer it right so actually it makes sense that the if the only kind of 10 year old sharding actually it's maybe it's not the only it because it wasn't SPQR quite old as well as you say they feature complete sharding solution on Postgres if that's 10 years old but you can't get it on any managed services except for 1, actually, it makes sense that there's demand for, okay, how do we self-manage this?

20:49Nik We need to revisit the topic of AGPL misconception.

20:52 How to say? So AGPL allows you to run it on your managed service, but you cannot modify it without not publishing source code. And right, this is 1st thing. Let me explain my words and then Álvaro you can correct me if I'm wrong. I'm just curious if I understand it right. And then Álvaro is not doing a managed service at all.

21:14 StackGres is just like operator plus of course there is enterprise edition with support and so on. This is like money coming from support, but it can be installed anywhere, right? Like having AGPL components doesn't hurt at all. And, And finally, I know StackGres was on AGPL until lately, right? Can you elaborate this?

21:37 On 1 bit. Yeah, am I right? It's not an AGPL anymore? Álvaro: It's very good insider information, yes. So, 1st of all, AGPL. I'm not a lawyer, okay? But I love AGPL and GPL or in general copyleft licenses. I believe people say they are viral, that they restrict your freedom. I believe it's exactly the opposite.

21:56 They provide forward freedom warranties, meaning that whatever software is published under a copyleft license, it is providing a warranty that will never be closed source. It will always remain under such a license. So it's a forward warranty for users of Freedom. Whereas a license like the Apache 2 or BSD, they can be turned into proprietary software, and that software may cease to exist as an open source software.

22:22 That's why I like them. Now, the reality is that a lot of people believe that they cannot be used, for example, as a managed service, but you put it very well. You can perfectly well use an AGPL software while offering a managed service. The only thing that the license force you to do is that whomever interacts over the network with your software need to provide the same warranties that you got for that software, which means publishing the source code.

22:47 So if you use Citus, and even with Citus, it's arguable because you don't talk directly to Citus. But even if we do a little more loose interpretation, or because I have Citus, then I'm bound by these terms directly, it would suffice to publish the source code of Citus, which is a link and a disclosure that you use Citus, right?

23:06 Then if you patch Citus and then you improve Citus, even if you don't submit upstream, your users have the right to get those benefits too, which is why I love this kind of licenses. But some lawyers disagree with me, probably they're right because they're lawyers, I'm not. And not many people want to use AGPL on their managed services.

23:26 But in my understanding, limited understanding, I think that's not necessarily correct.

23:32Nik Imagine if Postgres was on AGPL version 3.

23:35 RDS would need to publish all the changes they made to implement rds_superuser and so on. It's simplified as well.

23:45Michael But this is a really great point because I think Postgres

23:48 has benefited from being on a permissive license in terms of how much usage it's got. And maybe we're seeing the exact opposite with Citus. Maybe we're seeing that AGPL is actually costing in terms

23:58Nik of usage.

23:59 Repelled usage. Repelled, yeah. Like Google, for example, have internal, at least in the past, they had internal directive that let's avoid the GPL version 3 software completely. Álvaro: Yeah, it's a big if. We don't know the answer. Most likely, because The market has this, what I believe it's a misconception, but this general wisdom that AGPL or GPL licenses are this viral and so on, then yes, it looks like copyleft software is perceived as unideal and permissive licenses are winning.

24:33 And certainly this is the reason I don't want to avoid the question, yes, StackGres, and I can announce this publicly, it's gonna be licensed under the Apache 2 license, instead of AGPL, as much as I wish to do otherwise, but the market and the users demand this, so we're happy to

24:51Nik do that and we will

24:52 Álvaro: do that in the next days actually.

24:55Nik While still Citus remains on AGPL and it's just plugged

24:59 in, right? Álvaro: Yep, we support, in StackGres we support more than 200 extensions. They come with a lot of licenses and it's a user's responsibility to, to know the license they're, they're dealing with and their wording. We just provide the software and the platform.

25:15Nik It's like a big Lego and some pieces are different colors

25:18 and so on, right? Álvaro: Yeah. And to complete the picture, I think I want to go back a little bit to what you were mentioning, Michael. That also relates maybe to 1 of the reasons, or potentially, I don't know exactly the reasons, but why I believe that maybe some companies abandoned Citus at some point, which is that Citus, the workers, where you split the data, you can, again, you can add as many as you need, but coordinator is only 1.

25:47 And if you saturate the coordinator, then you're out of luck, right? You can use, as we said, you can query the nodes directly, the workers directly, but that it's complicated to operate and you still also will get as far as you can. This is something we saw some time ago and decided to do something about it.

26:08 And in the most recent version of StackGres, StackGres 1.19, we have introduced something called query routers. Query routers does nothing that we have actually patched on Citus or anything like that. It's unmodified Citus. It's just an architectural pattern that we identified that we could apply to Citus to allow to have essentially multiple coordinators, put that simple.

26:30 So a query router is like a coordinator, so it can route queries. It doesn't have data, it just route queries. The only thing it cannot do is the DDL operations. The DDL operations go through the coordinator. But now you can add as many query routers as you want. And so the limitation that Citus may have had until now with a single coordinator, it's gone.

26:52 And now you can scale by adding as many query routers as you want and scale, not only now the worker side, the layer where it keeps the data, but also the router layer to almost any size. I'm publishing some benchmarks that we have been running in the last days about this. And ballpark numbers, 64 core workers running simple queries indexed by primary key, but not fully in memory, but with a high hit ratio.

27:20 A single coordinator with 3 workers, the coordinator being also 64 cores, was maxing out at around 550, 000 transactions per 2nd with a pretty good latency, like under 1 millisecond. But that's it. More than 500000, a little bit over 500000, and the coordinator is saturated. The reason why this number is because these queries are quite fast to process, and the proportion of overhead per query processing is maxed out, right?

27:49 It's a lot of overhead per very small query, per tiny query. Of course, if you hit all the queries and they involve I/O. And reading work, you can scale further before hitting the limit of the coordinator. So we designed this benchmark to actually find the limit as soon as possible of the coordinator at still very high rates, right?

28:06 More than 500000 transactions per 2nd. So coordinator is maxed out. Then we add 2 coordinators with the same 3 shards and then 3 coordinators with the same 3 workers, sorry, the data nodes. And the performance basically scaled almost linear. So from 550, it went to 1, 050, 000 and then to 1, 500, 000.

28:26 So pretty much linear growth.

28:28Michael When you say 3 coordinators, Do you mean 3 query routers

28:32 in the new? Yeah. OK. OK. Álvaro: Yeah, sorry. I use the terms a little bit interchangeably, because for me, they're conceptually the same, even though they're not. You're right. It's query routers, yes. So 3 query routers. The coordinator remains there. And actually, I am now recommending that if you use this functionality with the StackGres, with the query routers, that you keep the coordinator small configuration, small number of cores, just to run the DDL through there, and do not use a coordinator for query routing.

29:01 And then use a layer of query routers. You can start with 1 if you want and then scale them up as your needs grow, but they're already prepared. You already, they use a different service in Kubernetes, so it's just a way to route the queries and entry point. And then you already have that established.

29:15 Your application points to the service that will balance across all the query routers you may have. And then it's just a matter of changing 1 character in your YAML file, say, query routers 1, query routers 2, and you would scale automatically.

29:29Michael And You mentioned a while back that StackGres supports

29:33 multiple ways of sharding. Could I use query routers with any of them or is this a Citus specific thing? Álvaro: It's a very good question. This is Citus specific. So if we look at the internals, Again, this is not, we didn't touch Citus source code. We could have if we needed, but it was not needed.

29:51 The building blocks were surprisingly there. Actually, what I'm surprised that nobody saw this before. For us, it was crystal clear because it all started with the concept that every worker can function as a query router, as a coordinator, right? We just call them query routers. So that was already working there.

30:09 The way this works is because they implement the metadata that Citus replicates across all nodes. So you know whom to talk to, essentially. And there's another property, which is what we combined into this pattern that we call the query router. There is a property that is called shouldhaveshards, and you can set it to false.

30:30 Shards are the way that Citus internally distributes the data, right? So also to clear, to make the context clear for everybody. On Citus, there is workers, which let's say they are the physical nodes that contain the data, physical, virtual, you name it, the nodes that contain the data. But the data in reality is split into shards.

30:49 And then you map shards to workers, at least 1, but could be more than 1. Sometimes some other people call this virtual shards versus real shards, right? Citus terminology is workers, like the physical thing, and shards like the virtual thing. And typically we recommend a higher number of shards to worker because it helps you also scale when you add more workers, you just move some of the shards to other workers.

31:13 You don't need to re-shard the whole thing. Right. It's much, much better pattern. Actually, by the way, I'm diving maybe a little bit too deep into that topic, but I use the magic number 120. Most people use powers of 2. I think that's a mistake, because then you can only grow by powers of 2, which by the growth of powers of 2, can mean to grow a lot when maybe you don't need to grow that much.

31:34 Versus having 120 is a number that has a lot of divisors, and so you can grow from 30 to 40, for example. You don't need to go from 30 to 60, you need to double because they're still evenly divided across all your workers. If you take a worker that can act as a coordinator or a router, and you set this property shouldhaveshards to false, then Citus will not place any shards on it, it will be data-less.

31:59 So essentially a data-less worker, and that's a good router. So the pattern was just there. We just enabled it through architecture and automation in StackGres.

32:11Michael Simple, I like it.

32:13 Álvaro: So of course, there's more meat to it when you look into the internal details, but conceptually this is it. Just

32:20Nik don't write proxy, just use Postgres.

32:25 In yet another area. Yeah, it's interesting. Speaking of benchmarks, these numbers, what concerns me that we talk about only read-only queries, I would like to see the series 2 of benchmarks talking about writes, because as we already discussed, the case you have in hand is 2.2 petabytes. It's about writes, not reads, because scaling reads is less interesting, less challenging, right?

32:59 Álvaro: It is actually challenging And again, I will not claim that Citus is perfect. And it is really good, but it's not perfect. I guess nothing is perfect. And it requires tuning. I'm not gonna say this worked out of the box. No, it requires tuning. And 1 of The hardest part of Citus actually is connection tuning, because when you look at Citus, at any distributed database for that matter, any sharded database, it's no longer a single thing, right?

33:26 Now you're dealing with connections from the client to the coordinator or the query routers, and from them to the workers. And sometimes these queries actually have a big fan-out. For example, on this particular case of the 2.2 petabyte database, I just cannot mention the customer name, but it's a big telco.

33:46 They are using COPY to ingest data. And that's amazing because it's the most efficient way to ingest data into Postgres. And they're using 120 shards on 24 nodes, which divides evenly. Now, if you do a COPY of 50, 000 records, a single connection, so a single connection tries to ingest 50, 000 records via a COPY, that COPY is going to create 24 sub copies.

34:14 Because by law of probability, all 24 shards are going to receive data. 1/24th of the 50, 000 records. Because the way that data is sharded is by taking a hash of the primary key, and that hash is a random, let's say, it's randomly distributed, so it's going to touch all the shards. So 1 connection from 1 client now will create 24 connections.

34:36 Citus implements this very well, just do 24 sub copies. But you can imagine the potential multiplication of connections that may happen here under high concurrency. But by applying proper tuning and doing proper connection management, this works very well.

34:51Nik What is proper here?

34:53 Álvaro: Oh, well, it's not an easy exercise. The connections from the coordinator or query routers to the worker nodes, they're bound by several parameters. Essentially, Citus has some kind of, let's say, connection pooling system to communicate between the coordinator or coordinator to the workers. But it's not like PgBouncer, because they open backends that are Citus is Postgres, for the good and the bad.

35:19 There's many good things and a few bad things. And so if there's a client talking to the coordinator, that creates a backend on the coordinator, right? And that backend needs to be open. Now, this backend may now own multiple sockets to multiple connections to the workers. And there's several parameters to tune whether you want a single connection per worker, whether you want a number of connections per worker.

35:41 You can cache connections already open per worker, but then there's also like a global pool so that you don't want to do more than X number of connections for a given coordinator across all the workers. And so you need to really understand that we all know that Postgres doesn't work well with a huge number of connections because it creates a lot of backend.

36:03 And so you need to understand the fan-in rules, the fan-out rules, whether to put connection pool, for example, in front of the coordinator or not, and so on. So the math ends up being non-trivial sometimes, especially if you're talking about huge workloads. Like for small workloads, even if you're starting with again, maybe hundreds of thousand transactions per 2nd, maybe more or less trivial, like any server, modern server will deal comfortably with that.

36:26 But when you grow to these sizes, you really need to size everything around this. But what's actually very fun of this particular case is that as of today, on this particular case, we'll need to blog about this. Right now, the database is perceived as slow, but they're running some software that I think you're familiar with called pg_ash, and it's reporting all the time, waits are mostly ClientRead.

36:53 They don't have an ingestion pipeline fast enough to saturate the database. The database is faster than their ingestion pipeline. Because again, COPY is really optimized in Postgres and can ingest huge amounts of data. And we have here 24 shards, really beefy servers that can ingest data at really, really high speeds.

37:13 We've seen, don't remember the numbers, but multiple gigabytes per 2nd being ingested. And the database is just fine. But the ingestion pipeline is not.

37:24Nik That's interesting.

37:25 So I see, I hear 3 different directions here. 1st of all, this benchmark in your blog post, which is coming, I guess you will publish all the details as usual, right? Álvaro: Yes, of course.

37:40Nik This is our company's common value, we share it a lot.

37:45 Álvaro: Fully reproducible, StackGres being open source, we'll open source all the code.

37:49Nik It's Postgres's open source and benchmarks should be open

37:52 so anyone could repeat them. Yes. Yeah, so that's great. SELECT only because this is to compare with different benchmark which is not so open, right, and not about open source. That's great. And there, I guess, this is interesting to continue exploring the limits. And I think maybe it will also... I expect more benchmarks to be done because we have multiple systems competing in purely open source areas.

38:23 Some of them are AGPL, some of them are Apache licenses, we mentioned already. It's interesting to start comparing them. It's great that it's happening and this is maybe the beginning of some comparison. It's time to start comparing. Right now your bet is fully on Citus, like almost fully. I hear you that StackGres supports other sharding as well.

38:46 And I guess at some point you can start supporting those new alternatives as well. Why not? It's just a Lego style thing, right? This is 1 direction. Read-only benchmarks, which I guess it's not so exciting. 2nd would be probably writing benchmarks like COPY. When you say COPY, you mention specific production case, but it would be great also to have some benchmark showing, to start playing with and showing capabilities.

39:15 Do you plan to have this benchmark? Álvaro: We could do that, but I believe we will start by publishing some, the data that we can publish about 1 particular case that we have already, like this 1, because I think that already shows a lot. And so to be honest, we have focused quite a bit on benchmarks.

39:34 So let me be clear, we've done 2 different benchmarks. Both are going to be published and will be fully reproducible back by code, right? And so full transparency on everything. The 1st 1 is a benchmark to demonstrate the ceiling of the single coordinator and to see how we can scale with query routers, right?

39:51 Like I just mentioned the numbers, it can go from 0.5, essentially 500000 to 1.5 million, so almost linear scalability by going from 1 coordinator to 3 query routers, right? But then we saw actually that for this particular workload, like very, very small primary key queries, mostly in memory with a non-uniform distribution, that you end up needing a similar number of cores on the routers layer than on the shards or the, sorry, the workers layer, right?

40:22 And so we did some tuning and around this number that the ideal is to have 6 routers of 32 cores each and 3 workers of 64 cores each. So that's the same number of cores on both layers, but 6 routers and 3 workers. And we call this a unit of a scaling. That package, let's say it's well tuned so that both layers are close to saturation but before saturation.

40:47 So they operate at the operational regime and all math about connection, tuning connection, pooling, and all that is well tuned. And that's basically delivering 1.5 million transactions per 2nd, that unit of a scale. And then we decided to multiply this unit of the scale by 2, by 4, and by 8. And this is a 2nd benchmark that we'll be publishing that also compares to some data points of another sharding solution, which has achieved those numbers and more actually.

41:16 And it also shows really good behavior, like basically to share the numbers by multiplying this by 8x, it passes over 10 million transactions per 2nd. With 1.something millisecond p50 latency and 6 milliseconds p99, at 10 million transactions per 2nd. To put this into perspective, who needed to do 10 million transactions per 2nd?

41:42 Probably just a few sites on earth do more than that.

41:45Nik So it's...

41:47 Maybe more. We have gigantic workloads these days. I think here I disagree. A lot of more. We see crazy startups which skyrocket these days. And their users are agents. And they, like, just... Álvaro: Yeah, but most of the time what I see is that agents are small databases. There are many of them, but a small...

42:07 We're talking about single cluster, right? But anyway, my point is that anyway, reaching 10 million transactions per 2nd with very decent latency numbers, right? It's an excellent result. It's home to most of the clusters we've ever seen. And so it's a valid solution for almost anyone. And That's actually my takeaway, right?

42:31 It can scale almost as much or as much as you want with really decent numbers on this pure OLTP.

42:38Nik So I have 4 directions already.

42:40 So you will publish. I know you will publish this. You will probably will iterate this. I guess this benchmark also not cheap to run, right? You need to provision those resources. Álvaro: Yeah, exactly. But this is a good point. We're still a bootstrapped startup, and we strive to be, we have to be, very cost efficient.

43:01 So running this benchmark, I don't have the final data, but it's very approximate. We've spent around $5, 000 to $6, 000 on running this benchmark, scaling to 10 million transactions per 2nd.

43:14Nik It's AWS or where?

43:16 Álvaro: Yep.

43:17Nik And you just use Spot because it's not reliable in this

43:21 case. Álvaro: Yeah, it's hard because, and we had some problems when you deal with such a large number of instances. So on the 10 million transactions per 2nd, this involved 72 instances plus the drivers for the workload, so like around 90 instances. Just 1 instance behaving wrongly?

43:40Nik The probability of failure is too high.

43:44 Álvaro: Yeah. So, yeah, Spot would have most likely had mean that the test needs to be repeated over and over again.

43:51Nik I see.

43:52 That's great. And that's great cost-wise as well for 10 million. So once again, 1 direction is SELECT only primary key lookup, trivial workload, I would say. And I think this is very adjacent to our benchmarks we did multiple times for latency overhead is very in this area, like very fast queries. 2nd, I still think at some point there should be some benchmarks for INSERTs or write workloads, right?

44:19 3rd thing is that you mentioned many connections is a trouble. I think, I know Andres Freund did a lot of great work in Postgres 13 and especially I think 14 to improve the situation when you have thousands of connections. Because before that, thousands of idle connections affected performance so badly.

44:42 Now it's not so bad. What does it mean still? We don't have threads, right? What's happening if you have 10, 000 connections on single Postgres? I think this research is very interesting. I'm talking for myself because we have a lot of interesting directions. We have benchmarks. Our focus mostly on single node clusters.

45:03 Our company is dealing with some self-implementation, like application side sharding. But it's so interesting, I see some interesting directions here to benchmark further. And finally, I'm thinking if you stop at 10 million, I'm going to explore the possibility to have 10 million TPS primary key lookups for a single node.

45:28 This probably will encourage you to go to 50 million or something, spend a few thousand dollars. Because if, yeah, I will put it to midterm to-do list. If we achieve that, you will be forced Álvaro: to cover. But you know, if you want to do that, you know, you only have 2 weeks, right?

45:46Nik No, no, no, no, no.

45:48 Go ahead, publish your results. This is an interesting, friendly competition and I think some systems will need it to join. What I like about this is that data is shared. It's about open source components. Everything is open source. Data is open source. Benchmarks are open source. They can be revisited.

46:04 This is great. This is very good style of competition. Everyone benefits from, right? So I'm not going to chase you right now. I'm just saying maybe later. Álvaro: No, I'm very open if anyone wants to sponsor workloads to be tested on Citus and see how Citus can scale to from 10 to 50 million transactions per 2nd or to read-write workloads We would be more than happy to help in that regards.

46:33Michael So yeah.

46:34 I think it's, I think what, from 1 of the charts that you sent over, it's quite an interesting chart of how the throughput changes as you scale up. So I think if you, if you, you mentioned these units of work, if, if you add a 2nd unit of work and the TPS exactly doubles if it's linear like as you add more units it's very easy to extrapolate that of course it can get to 50 I just need 5 times more but if it isn't if it isn't quite efficient if you lose 10 each time if it if it feels like at doubling the instance size only 1.5x is the TPS, then it's less believable that it can scale infinitely.

47:15 What kind of numbers were you seeing there? Álvaro: So that's a really good question. Scaling is slightly sublinear, with most systems are. And on our scaling from 1 to 8, it was 1.94 every time we doubled.

47:34Nik Okay.

47:34 Yeah. There is a big challenge of scaling client in this case. Yes. That's it. I mean, I Álvaro: can go very deep into this topic. I guess it's not the moment. But it's a really, really interesting capacity planning we had to do to achieve these numbers. Again, it sounds like, oh, just put more nodes and yeah, you scale 10 million, you just need to hit enter, right?

47:56 No, it wasn't that case. We had to do a lot of tuning. The number of connections is actually mind-blowing because even though these queries are really fast and take hundreds of microseconds to run in reality across all the fleet, across the CPU aggregated all the fleet, right? The number of connections that you need, the sessions that you need to drive this is very, very large.

48:16 It's many, many thousands of sessions. And then they multiply because if each query router is, I think where our optimal regime was around 400 connections per query router on 32 cores, which by the way, if you look at the numbers, it's always like the usual rule for tuning Postgres, right? The maximum number of connections is a multiple of the number of cores.

48:38 In this case, the multiple is around 7 because they're very, very CPU intensive queries because they almost not touch disk, right? So the multiple is a bit high. Anyway, do you think of 400 connections coming to each query router of which there are 48 on the 10 million transactions per 2nd run, right?

48:56 And now each 1 is gonna have outgoing the same number of connections in the same number of sessions, because they're all, but they all go to all shards. So the incoming connections at the shards are the whole fleet. So you're seeing the 400 multiplied by 48. Those are incoming connections to every worker.

49:19 So to actually deal with this, we had to introduce connection pooling before the workers. And it's also been an exercise because StackGres by default uses PgBouncer, and PgBouncer is single-threaded. So were we saturating 1 thread? Definitely. So actually this benchmark required, if I'm not mistaken, 15 PgBouncers per worker, running with a SO_REUSEPORT functionality and all handled by StackGres as a custom container.

49:50 So it worked beautifully well, but you need to do all the dimensioning correctly, otherwise it will not achieve these numbers either.

49:57Michael Did you say 15 PgBouncers per Worker?

50:01 Álvaro: Yep.

50:01Michael Oh my goodness.

50:04 Okay. Okay. Álvaro: Good. Because we're dealing with, I think, 19, 000 incoming connections. And they're all sending queries at a really high pace.

50:15Nik What would it take to reach 100 million TPS?

50:20 Álvaro: Well, there's 2 things. 1 is just to keep multiplying. I think this unit of scale is very well tuned as of now, and it just keep multiplying. As of now, it should be an easy exercise, other than money-wise. But I believe the scalability is gonna start going, is gonna start going less and less over time.

50:41 And this is, again, because of how Citus works. As I said, it's not perfect. You may need to question whether you need more than 10 million transactions per 2nd. But it's, so Citus is gonna create a backend per session, per router. And this, again, we were just discussing this, is gonna create, that multiplied by the number of routers will become the number of incoming connections to each worker node.

51:03 So instead of having 19, 200 connections, if we do this 10 times, that will require almost 200, 000 connections incoming per node. Not all of them are active. Are active actually are that number divided by the number of workers, but still a very high number, a very, very, very fun difference between the fan-in, the number of connections incoming to the workers through the poolers versus the number of outgoing connections, the pool connections to Postgres, which needs to remain constant at around 600 in the case of the workers or 800, something like this, because they have double the CPUs, right?

51:38 And this difference between this fan-in and fan-out creates inefficiency in the workers themselves. So it will start saturating earlier. They also create inefficiencies in the routers because they need to talk to more nodes. And so, will it reach there? I don't know. If someone wants to pour the money into it, we can test it out.

52:00 I believe it may reach to a very high number, maybe with a slightly less efficiency, but it will reach maybe 50 million, maybe more with more effort, but it will do so. It's still compared to other sharding solutions that can reach those numbers. I don't think all can, but out of those that we have tested, it doing so, this 10 million transactions per 2nd, is using 0.5 of the silicon compared to other sharding solutions.

52:30 So even though the scaling may be slightly less linear because of this Citus connection model, which is Postgres connection model, it needed 0.5 of the cores to achieve those results. So it's still very impressive, right? Now, Do I see a future different than this? I do. I believe there may be an option where the transport that Citus uses could benefit from a multiplexing transport, something like gRPC, which other of the modern solutions are adopting.

53:03 And that significantly reduces the fan-in and fan-out connection problem from the routers to the workers. So if I were to complete my initial thesis that Citus is a great solution as of today that is very mature, well proven, featureful, and so on, and that new solutions may take time to mature. Maybe, maybe, and it's a big if, by just improving a bit Citus architecture, it could take sharding even to the next level if needed, given that already it reaches levels that are more than enough for mostly everybody, but even for those who may need an even bigger scale, then maybe It's just a matter of improving Citus.

53:47Nik Yeah, I have only 1 question to ask in the end.

53:50 I know you're comparing to other benchmarks, and I'm just curious in terms of, I saw some pictures you shared with us before this recording that was involving money, like some dozens of thousands of dollars to have this cluster. I'm very curious, do you compare with humans involved with support or just pure open source and plus cloud costs and that's it?

54:15 Álvaro: Yeah, so I did 2 comparisons and they will be clearly identified in the blog post that will announce these benchmarks. And so there's the pure infrastructure cost, which is silicon, like number of CPUs, where essentially it's like around 55%. So almost 0.5 the cost in terms of CPU. But then we can compute the total cost to be very clear.

54:41 And there's nothing here to hide. I'm comparing right now with a benchmark that was run and published by Neki. And Neki is a managed service, and they publish their list price. And their list price is not the instance cost. It is the instance cost plus their managing fee or their margin. And when comparing with that, the cost is not 0.5.

55:04 It's depending on what you consider is between 0.333 or 0.2. Actually, a well-dimensioned system should be just 5 times cheaper. Basically, 0.333 is if you compare on-demand cost, which nobody should run like a production cluster sharded on on-demand cost. And if you use a basic saving plans, then you'll be 5 times cheaper.

55:24 Yeah. And this is obviously a managed service to a software comparison. You may discuss that there may be humans involved in the total TCO calculation. But my take is that a managed service and software that does a managed service for you is not so much different. Actually, I call StackGres a managed software, a managed database software, because when you say a managed service, there's no human typing, right?

55:51 There may be, if there's a support ticket or something happens, but the operation is just software. So it's essentially the same for me.

56:00Nik I know some humans can send kill -9 in very big companies offering

56:08 managed services sometimes. That kind of support is not welcome. Not welcome, right? kill -9 from support.

56:18Michael I also think these machines are so big, like we're talking

56:21 about sharding and we're talking about multiple huge machines really, like they're expensive. Often you don't pay this. If you're actually a company,

56:30Nik saving

56:31Michael those machines, you would, you would have like savings

56:34 plans either through PlanetScale and or bring your own cloud type thing.

56:38Nik So you're comparing not apples to apples, but the numbers

56:41 you provided on your side, it could be less with savings plan if you pay in advance some number of years to AWS. On the other hand, you need probably to roughly multiply it by 2, thinking about humans and the support needed to surround this solution, right? Very roughly. Álvaro: Yeah, so that's what I said.

57:04 And so 1st of all, that's why I want to give both numbers. So you can 1st of all, compare from the pure Silicon perspective to pure architecture perspective, and then from the sale price. But as I said, For me, a managed software, it's just software. A managed service is just software, right? And if the software automates and runs everything for you, which is the case in both cases, right?

57:25 Because all managed services, there's no humans doing operations, It's only software. So humans only intervene in the case of issues. And in those cases, yes. And you can get like a, we sell enterprise, the StackGres Enterprise, which is exactly that. Support for those cases, right? And that's something optional, but that's something you would add on top of that.

57:49 And then the calculation will not be 5 times cheaper, it will be

57:52Nik maybe, I

57:52 Álvaro: don't have the numbers exactly, 3 times cheaper, 4 times cheaper, but still obviously much cheaper.

57:57Nik Yeah, thank you for this clarification, that's interesting.

58:00 Álvaro: And sorry, also to add, and that's because talking about cloud, but if you talk about on-prem, which is definitely in the space where we also, the StackGres works anywhere on-prem, then the math is even much more into your favor.

58:14Nik Right, if you own hardware.

58:15 If you own Álvaro: hardware and data centers,

58:19Nik then it's open source, you can troubleshoot with your engineering

58:23 team if needed. That's Álvaro: great. Thank

58:26Nik you so much.

58:29 Very interesting. And also I see several paths for future work. Maybe we should have another episode at some point in the future to revisit the topics in some directions. Álvaro: Any time. I would be more than happy to come back.

58:45Nik Thank you so much.

58:46 It was interesting. Very interesting to me. Álvaro: Thank you for inviting me.