Postgres FM - Citus + StackGres

Episode Date: October 16, 2026

Nik and Michael are joined by Álvaro Hernández to discuss sharding Postgres with Citus and StackGres, especially the new query router support that has been added very recently. Here are some links to things they mentioned: Álvaro Hernández https://postgres.fm/people/alvaro-hernandez-tortosaOngres https://ongres.comScaling Citus without limits: announcing Query Routers in StackGres https://stackgres.io/blog/scaling-citus-beyond-one-coordinator-announcing-query-routers-stackgres/Open-source Citus matches PlanetScale's Neki at 10M QPS on half the data nodes https://stackgres.io/blog/open-source-citus-matches-planetscales-neki-at-10m-qps-on-half-the-data-nodes/Postgres TV discussion about licenses with Álvaro https://www.youtube.com/watch?v=1rcbyIjA4gIOur episode with Sugu from Multigres https://postgres.fm/episodes/multigresOur (first) episode with Lev from PgDog https://postgres.fm/episodes/pgdogCitus https://github.com/citusdata/citusCitus goes fully open source (2022) https://www.citusdata.com/blog/2022/06/17/citus-11-goes-fully-open-source/6M requests per second using ClickHouse (blog post by Alex Bocharov / Cloudflare) https://blog.cloudflare.com/http-analytics-for-6m-requests-per-second-using-clickhouse/pg_ash https://github.com/NikolayS/pg_ash118 million queries per second on Neki (blog post by Florent Poinsard and Hirad Pourtahmasbi / PlanetScale) https://planetscale.com/blog/118-million-queries-per-second-on-nekiAnalyzing the Limits of Connection Scalability in Postgres (blog post by Andres Freund) https://www.citusdata.com/blog/2020/10/08/analyzing-connection-scalability/~~~What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!~~~Postgres FM is produced by:Michael Christofides, founder of pgMustardNikolay Samokhvalov, founder of Postgres.aiWith credit to:Jessie Draws for the elephant artwork

Hello, hello, this is PostGus FM, as usual. I'm Nick, Postgres AI, and as usual with me, Michael, Pigeamastard. Hi, Michael. Hi, Nick. We have a great guest today. Did we have you already? Alvaro from OnGras. Hello, no, it's my first time here.

At least as a speaker, not as a listener. Thank you for coming. I know you're very active listener. I will definitely have you on PostGus TV channel, on YouTube channel, talking about licenses. That's why I'm confused. Do you remember? Yeah. And I know we will touch a little bit licenses today, just knowing 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 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. No, thank you very much for having me. It's been a lot of episodes listening to you over the year and now being present here. It's very exciting for me. So thank you. And today we will talk about benchmark you haven't published 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, right. That's correct. Action happening in real time. That's great. And yeah, I received your pictures, three charts about benchmark data, benchmark results this morning.

So I'm catching up. Where should we start, actually? Because this is great topic, right? So how PostGus can scale, what numbers we can achieve. Maybe we should start mentioning that Michael and I have had an episode about our company benchmarks on one node, no sharding at all, just one node. We achieved 4 million TPS. A couple of years ago, it was a couple of years ago, right?

We select only primary key look up 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 postbios can achieve that. And you can give us numbers much bigger because you involve sharding, right? That's correct. If you want, I can start a bit with my thesis about sharding in general for Postgres, which is, 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. Let me interrupt you with plugging in episodes with Sougu,

Montegraise I think we had one or two and definitely we had two episodes so we've left Pigeedog so there is an innovation starting last year which is great and I know you have your own opinion apologies for interrupting this continued

yeah yeah I do have my opinion basically databases they take a long time to mature databases need to be the places where you can trust your data that it's going to be there to be durable and they obviously obey the rest of the 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. You really need the mature and trust solution, a boring, I would say, technology to run your shards because now you don't have one 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. And again, 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 Titus. And Titus is just great. It might not be perfect, but it's really good enough, or more than that, even. 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, okay, people need to chart Postgres and people need to scale Postgres. And not only because of the performance directly, because as you demonstrated, you can run very high PPS on a single node. 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 comfortable. integration 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 charts, right, or workers, and then one goes down because of a configuration error, then it's only a small percentage of your users that are effectively down, which is good, but it's definitely not 100%. Your PR is completely different night and day.

So a lot of people now need to shard either because of performance reasons, because they cannot kill the rights to a single primary and or because of high availability and blast radius reasons. And we always had CITUS there. And CITUS is fully featured. It has almost everything that you need for charting, 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. 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.

Two points here. First of all, you said we've always had. Let me disagree with you because when I actually was looking into Citus, it was several years ago. The key features you are not open source.

For example, online rebalancing. So we didn't have it in 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 looking. 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 all we always had, right? Yeah, you're absolutely right. And thanks for that. I think, if I'm not mistaken, Cytos version 11 was the first one that include everything as open source, and that was, I think, 2022. So four years ago, four years and a half ago,

that was released as fully open source and has been since then. So, yes, you're right. For this podcast, you all right also because this podcast exists for years. Exactly. Good, good. And the second point is,

OLAB versus Ultim. My concern, like last four years, I think I didn't dive into Saitos. I remember diving means like I run it, inspect myself, the benchmark and so on. I did that 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. like you know like if somebody like githab scale would be running situs i wouldn't have any concerns at all

what are the great examples old tp style right that's 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 cyto's team some some time ago maybe a couple of years ago and they presented me some examples which were not l tp 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 OTP when we know 200 milliseconds our very first episode in this podcast means that humans already notice delay so I think there's there's two angles to what you're saying so one is the technical one and the other one is the marketing or trust one let's say

On the trust one, I would say you're right, meaning that there's no such great examples, and this actually piggyback from 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 have, because of the technological means that it conveys, right? I can tell you that we have right now significant examples of this kind of usage. We have now a customer running cytosone stackgrass that is actually growing a database towards 2.2 petabytes by the end of the year. Hold on, one second, 2.2 petabytes?

Yes. It's just one big site was cluster. Yes. Okay. Yes. How many nodes? 24. Each node is, I'm not sure if it's 64 or 96 cores and half a terabyte or more of them.

Wow. Okay. Those numbers are actually quite impressive. It's an IoT system. and they ingest data massively from millions and millions of devices that send hard bits every five minutes. And they have a 10-day retention period.

So basically they're deleting one-tenth of the database and ingesting one-tenth of the database every day. Right now, they're in ramping up period, but as of today, they're ingesting some 30 plus terabytes per day, and this is going to 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 side of cluster. We can discuss whether this is a little bit of OLTP or OLAP. 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. There'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. 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 chart by row, you short by tenant,

which is a great feat for those cases too. Typically, these cases where you create like a lot of tables for every tenant, so it's another way of isolating those workloads. But from a technical perspective and speaking, yeah, I just, I'm smiling at my glasses. because they said, they started arguing with me saying that there are public well-known names like Hib and Cloudflare named on Citus pages for years.

But both these cases are analytical cases, first of all, and both these cases went away from CITO, as well know. I was at Meetup when Cloudflare explained how they moved away from CITOS to CLEC out. And I know hip analytics moved somewhere else as well. Hypon analytics was great case but again, analytics.

And Cloudflare, it was analytics, DNS analytics. So it's not LTP. It can be aged up. Yeah, I agree. And your case is interesting. It's not what I want to see like I said GitHub or social media

or something when there are many humans working. Your case is great case for sharding specifically because it shows exactly where the problem with the biggest pain of single cluster is a lot of rights. 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. 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 one of the questions whether to say the system is ready for OLTP or not is the number of concurrent queries that can run and the latency at which those queries run. And Citus can run on millions of transactions per second, even under one millisecond P50.

I would call that OLTP. I agree here. 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. Maltigris, actually, we also tested, PG, dog, something else. And Citus written in C, you checked for Latence overhead of latency, latency, overhead benchmark, simple, which like if primary key look up is sub-millimeter, 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. Yeah, I actually have drilled down on the CPU consumption per layer for this kind of queries, and the total aggregated CPU consumption per query, including the coordinator, including the worker, including the connection pooling, excluding, excluding the network,

is somewhere in between 300 to 400 microseconds. seconds. Exactly. Yeah. Yeah, okay, slightly different from what I remember from our benchmarks. But the point here is that it can be very quickly eaten by something like network complexity, right? You notice this small overhead behind something else like just hops between. Yeah, the networks can be worse than that. Yeah. That's I agree. Yeah. Alvro, when you query, when you're doing that check, are you going through a coordinator, inciters or you, I know, because I know now you can query directly one of the workers. Would that reduce the overhead?

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. And so it is the entry point by default. It's true that Citus at version 11 introduce a functionality where you can query also the worker notes directly. There's two reasons to do this. One, 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. 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 charts. But at least Citus gives you the opportunity to that if you want to do so.

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 funds 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, Nicola, that Citus maybe was abandoned by some project before, like Cloudflare. 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-year-old. nodes or more. And they scale very well. However, the coordinator remains only one. And if you outrun the coordinator, then you're hitting a ceiling. 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. So you have notes.

that act both as query routers, which consumed 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. So it's not a perfect solution either. I think we forgot

important piece why Alvaro at all talking about CITUS right? This is a good question you're doing something else why are talking about Cytus at all this is in hands of Microsoft right? What happened here

you developed Staggress and then somehow these paths joined right they met these things and you have examples that's why you mentioned these two Betabyte example, right? Can you explain a little bit what's happening

why you focus on Saitos so much lately? Well, it's not me. It's what customers and users request. I see a growing concern and needs to scale Postgres beyond the single primary for the reasons I mentioned before, right limitations and also blast radius. And that's why we are also seeing

an explosion of new sharding technologies for Postquers. So this is a real need. And this demand is driven by market demand. We are seeing a lot of a lot of people coming to us, asking how we can share our database. And StackRES, 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. And so we saw this need and we saw early that we had to provide users a way to share. And actually, we were in a very privileged position, I would say, because what we've done in Stackers 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. 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 silos cluster involved a lot of manual work because it's no longer one cluster. Now, if you think about it and the model we follow in Staggress 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 one instance also for high availability purpose, right? 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 2448 node Citrus cluster, but also to do it right. And with our automation and orchestration that we built for Stagrass, we could do this work. And now the reality is that if you use Stagris and want to create a sharded cluster with Citus, by the way, we support all the other sharding technologies in Stagrass, 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 Tate can take thin lines of Jammel, really high level, you can create a Citus cluster of 60 nodes in a few minutes. You can even do from a web console, click, click, click, boom, and you get the shardleap. the 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 the demand and is paying

off. I would say a significant percentage of our new customers and users come for sharding. It is very widespread and is a need that few people are focused on solving, especially using a technology that is proven and well-maintained us of today like Citus. Good point about Citus being on Microsoft's platform And 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 if the only kind of 10-year-old sharding Actually, maybe it's not the only year, because it wasn't SPQR quite old as well.

As you say, though, feature complete sharding solution on Postgres. If that's 10 years old, but you can't get it on any managed services except for one, actually it makes sense that there's demand for, okay, how do we self-manage this? We need to revisit topic of AGPL misconception. 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 first thing.

Let me explain my words and then you can correct me if I'm wrong. I'm just curious if I understand it right. And then Alvarez is not doing a managed service. at all. Staggress 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 a JPL components doesn't hurt at all and finally I know Stagvius was on IGPL until lately, right? Can you elaborate this? Yeah, am I right?

It's not an HGPL anymore. It's very, very good inside of information, yes. So first of all, AGPL, I'm not a lawyer, okay? But I love AGPL and GPL owning general copy left licenses. I believe people say they're viral, though they restrict your freedom. I believe it's exactly the opposite. They provide forward freedom warranties, meaning that whatever software is published under a copy left 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 Apache 2 or BSD, they can be turned into proprietary software,

and that software may cease to exist as an open-source software. 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. So if you use Citus, and even with Citus is arguable because you don't talk directly to Citus, but even if we do a more loose interpretation or because I have Citus then unbound by these terms directly, it would suffice to publish the source code of Citus, which is a link and the brochure that you use Citus, right? Then if you patch Citus and then you improve Citus,

even if you don't submit upstream, your users have to write the right to get those benefits to, 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. But in my understanding, limited understanding, I think that's not necessarily correct. Imagine if PostGGIS was on AGPL version 3, RDS would need to publish all the changes they made to implement RDS user and so on. Yeah.

It's like very, it's simplified. But this is a really great point because I think Postgres 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 AGBL is actually cost it in terms of usage. Repaled usage. Yeah. Like Google, for example, have internal, at least in the past I had internal directive that let's avoid the JPM version 3 software completely. Yeah.

It didn't be given. We don't know the answer, most likely, because the market has this, would I believe it's a misconception, but this general wisdom that ATPL or the licenses are disviral and so on, then yes, looks like copy left software is perceived as an ideal and permissive licenses are winning. And certainly this is the reason I don't want to avoid the question. Yes, Staggress and this, I can announce this public. it's going to 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 do that and we will do that in the next days, actually. While still Saito's remains on AGPL and it's just a plug Z in, right? Yep, we support Instagram, we support more than 200 extensions. They come with a lot of licenses, and it's a user's responsibility to know that, license they're dealing with and they're wording. We just provide the software and the platform. It's like a big Lego and some pieces are different colors and so on, right? Yeah. And all 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 one 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 says that CITUS, the workers, where you split the data, you can, again, you can add as many as you need. But coordinator is only one. And if you can, if you saturate the coordinator, then you're out of luck. 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. something about it. And in the most recent version of a Stagoras 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 a modified 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. 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 DL operations. The DL 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. 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,

running in the last days about this. And all park numbers, 64 core workers running simple queries indexed by primary key, not fully in memory, but with a high hit radio. A single coordinator with three workers, the coordinator being also 64 cores, was maxing out at around 550,000 transactions per second with a pretty good latency, like under one millisecond. But that's it. more than half a million, a little bit over half a million, and the coordinator is saturated.

The reason why this number is because these queries are quite fast processed and the proportion of overhead to query processing is maxed out, right? It's a lot of overhead pair is very small query, per tiny query. Of course, if you hit all the queries and they involve I.O. and reading more, 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? More than half a million transactions per second. So, coordinators is maxed out. Then we add two coordinators with the same three charts and then three coordinators with the same three workers, sorry, the data nodes and the performance basically scaled almost linear. So from 550, it went to 1 million 50 and then to 1.5 million. So pretty much linear growth. When you say three coordinators, do you mean three query routers and the new? Yeah. Yeah, okay. Yeah, sorry. I use a terms a little bit interchangeably, because for me, they're conceptually the same, even though they're not.

You're right, is query routers, yes. So three query routers. The coordinator remains there. And actually, I am now recommending that if you use this functionality with the stackers, with the query routers, that you keep the coordinator small configuration, small thumb of course, just to run the DDL through there and do not use a coordinator for query routing. And then use a layer of query routers. You can start with one if you want and then scale them.

up as your needs grow, but they already prepare. You already, they use a different service in Kubernetes, which is a way to route the queries and entry point. And then you already have that established. 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 one character in your Jammel file, say query routers one, query routers two,

and your scale automatically. And you mentioned a while back that StackWest supports multiple ways of sharding. Could I use query routers with any of them, or is this a CITUS specific thing? It's a very good question. This is CITUSA 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.

The building blocks were surprisingly there. Actually, what I'm surprised, nobody saw this before. For us, it was crystal clear because it all started with a concept that every worker can function as a query router, as the coordinator, right? We just call them query routers. So that was already working there. The way this works is because they implement the metadata that Citus replicates across all nodes,

so you know him to talk to, essentially. And there's another property, which is what we combined into this pattern that we call the query router, that is a property that is called should have shards. And you can set it to pulse. Sharts are the way that Citus internally distributes the data, right? So also to make the context clear for everybody,

on Citus, there is workers, which is let's say they are the physical nodes that contain the data, physical, virtual, you know, the nodes that contain the data. But the data in reality is splitting to charts. And then you map shards to workers, at least one, but could be more than one. Sometimes some other people call these virtual shards versus real shards. Citrus 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 all the workers. You don't need to reshart 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 two. I think that's a mistake because then you can only grow by powers of two, which by the growth of powers of two can mean to grow a lot when maybe you don't need to grow that much.

versus having 120 is a number that has a lot of devices, 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, a software router, and you set this property should have charts to poles,

then Citus will not place any charts on it. It will be dataless. It's essentially a dataless worker. and that's like a router. So the pattern was just there. We just enabled it through architecture and automation in stackers. Simple. I like it.

Of course, there's more meat to it when you look into the internal details but conceptually this is it. Just don't write proxy just use posters. Yeah. In yet another area. Yeah, it's interesting.

Speaking of benchmarks, these numbers, What concerns me that we talk about only read-only quiz, I would like to see the series two of benchmarks talking about rights because as we already discussed, the case you will have in hand is 2 petabyte, 2.4, 2.2 petabytes, right? 2.2 petabytes, yes. It's about rights, not reads, because scaling reads,

it's less interesting, less challenging, right? 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 going to say this worked out of the box. No, it requires tuning.

And one of the hardest part of Citus actually is connection tuning, because when you look at Citus, any distributed database for that matter, any shorter database, it's no longer a single thing, right? 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 this 2.2 petabyte database, I just cannot mention the customer name, but it's a big, big telco. 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 charts 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 subcopies. Because by law of probability, all 24 shards are going to receive data, 124 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 attach all the shorts. So one connection from one client, now we'll create 24 connections. Citus implements this very well, just 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. What is proper here? Oh, well, it's not an easy exercise. The connections from the coordinator or query to the worker nodes, they're bound by several parameters. Essentially, Citus has some kind of, let's say, connection pulling system to communicate between the coordinator of corridor

to the workers, but it's not like PG-Bouncer because they open backends that are, Citus Steel PostG for the good and the bad. 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? 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. 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 could create a lot of backend. And so you need to understand the fan in rules, the fan out rules, whether to put connection pooler, 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 token, maybe hundreds of thousand transactions per second, maybe more or less trivial, like any server, modern server will deal comfortably with that. 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-H and is reporting all the time weights are mostly client read. 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 imposterous and can ingest huge amounts of data. And we have here 24 charts, really beefy servers that can ingest data at really, really high speeds. We've seen, I don't remember the numbers, but multiple gigabytes per second being ingested.

And the database is just fine. But the indetion pipeline is not. That's interesting. So I hear three different directions here. First of all, this benchmark in your blogpost, which is coming. I guess you will publish all the details as usual, right? Yes, of course. Yeah.

We share this. This is our company's common value. We share it a lot. We fully reproducible. Stagras being open source, will open source all the quote. It's postgist's open source and benchmarks should be open 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 like, I expect more benchmarks to be done because we have multiple systems competing in purely open source area. Some of them are JPL, some of them are patches. license as we mentioned already episodes with them. It's interesting to start comparing them.

It's great that it's happening and this is maybe beginning of some comparison. It's time to start comparing, right? And right now your bet is fully on Satus, like almost fully. I hear you that Stagras supports other sharding as well. And I guess at some point you can start supporting those new alternatives as well. Why not, right? It's just a Lego style thing, right? This is one direction.

Yeah. Redone the benchmarks, which I guess it's not so exciting. Second 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 to start playing with and showing capabilities. Do you plan to have this benchmark? We could do that, but I believe we will start by publishing the data that we can published about one particular case that we have already,

like this one, because I think that already shows a lot. And so to be honest, we have quite a bit on benchmarks. So let me be clear. We've done two different benchmarks. Both are going to be published and will be fully reproducible back by code, right? And so full transparency on everything. The first one is a benchmark to demonstrate the ceiling of the single coordinator and to see how we can scale with query browsers, right?

Like I just mentioned the numbers, it can go from, essentially half a million to 1.5 million, so almost linear scalability, by going from one coordinator to three 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 router's layer than on the Sharks, or the workers layer, right? And so we did some I'm tuning and around this number that the ideal is to have six, six routers of 32 cores each and three workers of 64 cores each. So that's the same number of cores on both layers, but six routers and three workers. And we call this a unit of the scaling. That package, let's say, it's well tuned so that both layers are close to saturation

back before saturation. 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 second, that unit of scale. And then we decided to multiply this unit of a scale by 2, by 4, and by 8. And this is a second benchmark that will be publishing that also compares to some data points of another Shardin solution, which has achieved those numbers and more, actually. And it also shows really good behavior, like basically to share the numbers by multiplying

this by 1.X, it passes over 10 million transactions per second. With 1.something millisecond P50 latency and 6 milliseconds P99, add 10 million transactions per second. To put this into perspective, who need to do 10 million transactions per second? Probably just a few sites on Earth do more than that. So it's maybe more. We have a genetic work close these days. I think here I I disagree. A lot of more. Like, we see crazy startups which skyrocket these days and their users are agents.

And they, like, just, it's a lot of the world. What I see is that agents are a small databases. There are many of them, but a small data. We're talking about single, single cluster, right? But anyway, and my point is that, anyway, reaching 10 million transactions per second with very decent latency numbers, right? It's an excellent.

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? It can scale almost as much or as much as you want with really distant numbers and this pure OLTP. So I have four directions already. So you will publish this. I know you will publish this.

You will probably reiterate this. I guess this benchmark also not cheap to run, right? You need to provision. Yeah. Exactly. But this is a good point. We're still a put strapped startup, and we strive to be,

we have to be, very cost-efficient. So running this benchmark, I don't have the final data, but it's very approximate. We have spent around $5,000 to $6,000 on running this benchmark, scaling to 10 million transactions per second.

It's an AWES or where? Yep. Yeah, and you didn't use spot because it's not reliable. in this case. 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 second,

this involved 72 instances plus the drivers for their workload, so like around 90 instances, just one instance behaving wrongly. The ability of failure is too high, yeah, yeah. Yeah, so, yeah, thought would have most likely had mean that the test needs to be repeated over and over again. I see. That's great. And that's great cost-wise as well for 10 million. So once again, one direction is select only primary key look up, a trivial workload, I would say.

And I think this is very adjacent to our benchmarks we did multiple times for latency overhead. It's very in this area, like very fast quiz. Second, I still think at some point there should be some benchmarks for inserts or right for clothes, right? third thing is that you mentioned many connections as a trouble. I think I know Andres Freund did a lot of great work in Postgreaves 13 and especially, I think, 14, to improve situation when you have thousands of connections. Because before that, thousands of idle connections affected performance so badly. Now it's not, but what does it mean still, right?

Like we don't have threads, right? So what's happening if you have 10,000 connections on single products? 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. We have our company is dealing with some self-implementation, like application site sharding, right? 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 Kilo-ups

for a single note this probably will encourage you to go to 50 million or something spend a few four thousand dollars because yeah I will put it to mid-term to-do list if we achieve that you will be forced to go

if you want to do that you know you only half two weeks, right? No, no, no, no, no. 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, data is shared, it's about open source components,

everything open source, data is open source, benchmarks open source, they can be revisited. 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. No, I'm very open.

If anyone wants to sponsor workloads to be tested in Citus and see how Citus can scale from 10 to 50 million transactions per second or to retryte workloads, we would be more than happy to help in that regards. So, yeah. I think it's, I think from one 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 mentioned these units of work, if you add a second 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 five times more.

But if it isn't, if it isn't quite efficient, if you lose 10% each time, if it feels like at doubling the instant size only 1.5x is the TPS, then it's less believable that it can. scale infinitely. What kind of numbers were you seeing there? 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. Okay. Yeah. There's a big challenge of scaling client in this case. Yes. I mean, I 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, more, more nodes and yeah, you scale 10 million,

just need to hit enter, right? No, it wasn't that case. We had to do a lot of tuning. The number of connections is actually mind-lawing 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. 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 passwords, right? The maximum number of connections is a multiple of the number of course. In this case, the multiples around seven, because they're very, very CPU intensive queries because they almost not done that. 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 second rank, right? And now each one is going to have outgoing the same number of connections, the same number of sessions because they're all, but they all go to all charts. So the incoming connections at the charts are the whole feat. So you're seeing the 400, most, you're seeing the 400,

multiply by 48. Those are incoming connections to every worker. So to actually deal with this, we had to introduce connection pulling before the workers. And it's also been an exercise because Stackers by default uses PG bouncer and PG bouncer is single-threaded. So where we sat trading one thread? Definitely. So actually this benchmark required, if I'm not mistaken, 15 PG bouncer per worker, running with a sole reuse port functionality, and all handled by Stacters as a custom container. So it worked beautifully well, but you need to do all the dimension incorrectly, otherwise it would not shift this numbers either.

Did you say 15 PG bouncer per worker? Yep. Oh, my goodness. Okay. Okay. Good. Because we're dealing with, I think, 19,000 incoming connections. Yeah.

And they're all sending queries at their really high pace. What would it take to reach 100 million TPS? Well, there's two things. One is just to keep multiplying. I think this unit of the 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 scaleability is going to start going,

it's going to start going less and less over time. 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 second. But it's so Titus is going to create a backend procession per router. And this again, we were just discussing this is going to create that multiplied by the number of routers will become the number of incoming connections to each worker note. So instead of having 19,000 to 100 connections, if we do this 10 times, that will be required almost 200,000 connections incoming per note.

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 funning, 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 I have doubled the CPUs, right? And this difference between this fan in and fan out creates inefficiency in the workers themselves. So it will start saturating earlier. They also creates inefficiencies in the routers because they need to talk to more notes. And so will it reach there? I don't know. If someone wants to put the money into it, we can test it out. 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 second is using half of the silicon compared to other sharding solutions. So even though the scaling maybe is likely less linear because of this Situs connection model, which is Palskrist connection model,

it's didn't half of the course 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. 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 give, by just improving a bit Citus architecture, you could take sharding even to the next level if needed, given that already 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 CITU's.

Yeah, I have only one question to ask in the end. I know you're comparing to other benchmarks, and I'm just curious. 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?

Yeah, so I did two comparisons and they will be clearly identified in the blog post that will announce these benchmarks. And so there's the appear infrastructure cost, which is silicon, like number of CPUs, where essentially it's like around 55%, so almost half the cost in terms of CPU. But then we can compute the total cost to be very clear, and there's nothing here to hide. I'm comparing right now with a benchmark that was run and published by Niki. And Niki 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 half. is depending on what you consider is between one-third or one-fair. Actually, a world-dimension system should be just five times cheaper. Basically, one-third is if you compare on-demand cost, which nobody should run like a production cluster

sharded on-demand cost. And if you use a basic saving plan, then you'll be five times cheaper. And this is obviously a managed service to a software comparison. You may discuss that there may be humans involved in the total TCO in the TCO,

in the 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 Stigras a managed software, a managed database software, because when you say a managed service, there's no human typing, right?

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. I know some show wants can send Q minus 9 in very big companies offering managed services sometimes. That kind of support is not welcome. Not welcome, right?

K9 minus 1 from support. Yeah, I also think these machines are so big. Like we're talking about sharding and we're talking about multiple huge machines really. Like they're expensive. Often you don't pay less. If you're actually a company buying those machines, You would have savings plans either through planet scale or bring your own cloud type thing. So you're comparing not apples to apples, but the numbers you provided on your side,

it could be less with savings plan if you pay in advance some number of years to addable. Yes. On the other hand, you need probably to roughly multiply it by two thinking about humans and support needed to surround this solution, right? Very roughly. Yeah, so that's what I said. And so first of all, that's why I want to give both numbers. So you can, first 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? 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 Tiger's Enterprise,

which is exactly that support for those cases, right? And that's something optional, but that's something, yes, you would add on top of that. And then the calculation will not be five times cheaper. It will be maybe, I don't have the number is exactly, three times cheaper, four times cheaper. But still, obviously, much cheaper. Yeah, thank you for this clarification.

That's interesting. And so it's also to add and that's because talking about cloud but if you talk about on-prem which is definitely in a space where we also stack resist works anywhere on-prem then the math is even much more into your favor.

Right. If you own hardware and data centers and it's open source you can troubleshoot with your engineering team if needed before going to board as well. Yeah that's good. Yeah thank you so much. 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. Anytime. I would be more than happy to come back. Thank you so much. It was interesting. Very interesting to me. Thank you for inviting me. That's been great.