Michael and Nikolay are joined by Alexander Kukushkin, PostgreSQL contributor and maintainer of Patroni, to discuss all things Patroni - what it is, how it works, recent improvements, and more. Here are some links to things they mentioned: Alexander Kukushkin https://postgres.fm/people/alexander-kukushkin Patroni https://github.com/patroni/patroni Spilo https://github.com/zalando/spilo Zalando Postgres Operator https://github.com/zalando/postgres-operator Crunchy Data Postgres Operator https://github.com/CrunchyData/postgres-operator Split-brain https://en.wikipedia.org/wiki/Split-brain_(computing) repmgr https://github.com/EnterpriseDB/repmgr CloudNativePG https://github.com/cloudnative-pg/cloudnative-pg Patroni release notes https://patroni.readthedocs.io/en/latest/releases.html Citus & Patroni talk and demo by Alexander (at Citus Con 2023) https://www.youtube.com/watch?v=Mw8O9d0ez7E ~~~ 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 pgMustard Nikolay Samokhvalov, founder of Postgres.ai With special thanks to: Jessie Draws for the elephant artwork
Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.
Transcript
From the show. Tap a time to play from there.
Michael: Hello and welcome to Postgres.FM, a weekly show about all things PostgreSQL. I'm Michael, founder of pgMustard, I'm joined as usual by Nikolay, founder of Postgres.AI. Hey Nikolay. Nikolay: Hi Michael. Michael: And today we are delighted to be joined by Alexander Kukushkin, a Postgres contributor currently working at Microsoft and most famously, maintainer of Patroni. We had a listener request to discuss Patroni, so we're delighted you agreed to join us for an episode. Alexander. Alexander: Yeah, hello Michael, hello Nikolay. Thank you for inviting me. I'm really excited to talk about my favorite project.
Michael: Us too. Perhaps as a starting point, could you give us an introduction? Like for most people, I think will have heard of Patroni and know what it is, but for anybody that doesn't, could you give an introduction what it is and why it's important? Alexander: Yeah, so Patroni, like in the simple words, it's a failover manager for Postgres. It solves the problem of availability of a primary. In Postgres, we don't use some words that are non-inclusive, like master. That's why we call it primary, and Patroni actually recently
get rid of this non-inclusive words completely. And the way how Patroni does, it makes sure that we are running just a single primary at a time, and at the same time, Patroni helps you to manage as many read-only replicas as you like to have, and keeping those replicas ready to become primary in case the primary has failed. At the same time, Patroni helps to automate usual DBA tasks like switchover, configuration management, stuff like that.
Nikolay: Node provisioning also, right? Alexander: Node provisioning, not really. Node provisioning is a task for DBA. DBA has to start Patroni, and Patroni will take care of bootstrapping this node. In case it's a totally new cluster, Patroni will start as a primary. In case the node joins an existing cluster, the replica node will take a pg_basebackup by default from the running primary and start the replica. And the most interesting part, let's say we bring node back which
was previously running as a primary, And Patroni does everything to convert this failed primary as a new standby to join the cluster and be prepared for next unforeseen event. Nikolay: At least you agree that it does part of node provisioning, because otherwise, we wouldn't have situations when old data directory, old PGDATA was copied and new 1 is created and we are suddenly out of disk space. And if you don't expect Patroni to participate in node provisioning,
then you think, what's happening? Why am I out of disk space? Right? It happens sometimes. Alexander: It used to happen, I think, with bootstrap mode. Like, when Patroni, like, I don't remember up until which version, but Patroni, when it tries to create a new cluster, usually by using initDB, but in some cases you can configure Patroni to create a cluster from existing backup, like from base backup.
And if something goes wrong, Patroni does not remove this data directory, but renames it. And it used to apply current timestamp to the file name. And therefore, after the first failure, it gives up, waits a little bit and does the next attempt. Nikolay: To directory, right? Alexander: Yeah, it creates, it uses yet another base backup, creates a new data directory, fails, and renames. Now it is not working like this, it just renames PGDATA to PGDATA-old,
something like this, and that's why you will not have an infinite number of directories. And having just 1 is enough to investigate the failure. Nikolay: But maximum we end up like if we if our data directory we expected to fill 70% of the disk we still might have out of disk space. Alexander: Yeah that's unfortunate but the other option really like you just drop it but at the same time like all the evidences of what failed, why it failed are also gone.
You have nothing to investigate. Nikolay: Okay. To me it still sounds like Patroni participates in node provisioning. Yes, it doesn't bring you resources like disk and virtual machine and so on, but it brings data, like the most important part of Postgres node provisioning, right? Okay, I just wanted to be right a little bit. Okay. Alexander: Okay. Nikolay: It's a joke. Okay. Michael: I think diving deep quickly is great. It'd be good to discuss complex topics but I think something simple would also be good.
I would love to hear a little bit almost about the history of Patroni. Like the early days, what were you doing before Patroni to solve this kind of issue and why was it built? What problems were there with the existing setups? Alexander: To be honest, while working for my previous company We didn't have any automatic failover solution in place. What we relied on was just a good monitoring system that sent you a message or some on-call engineer just calls you in the
night, the database failed. There were a lot of false positives, unfortunately, but it still felt more reliable than using solutions like replication manager, repmgr. Nikolay: Yeah, I remember this very well. Like people constantly saying we don't need autofailover, it's evil because it can switch over suddenly, failover suddenly, and it's a mess. Let's rely on manual actions. I remember this time very well.
Alexander: Yeah, to our excuse, an amount of databases, like database clusters that we run wasn't so high, like I think a few dozens, and it was running on-prem, didn't fail so often, and therefore it was manageable. A bit later, we started moving to the cloud, and suddenly, not suddenly, but luckily for us, we found a project named Governor, which basically brought an idea of how to implement autofailover
in a very nice manner, without having so much false positives and without risks of running to a split-brain. Nikolay: Was it abandoned project already? Alexander: No, no, so it was not really abandoned, but it wasn't also very active. So we started applying it, found some problems, reported problems to the maintainer of the Governor, got no reaction unfortunately, started fixing those problems on our own, and at some moment
a number of fixes and some new nice features accumulated and we decided just to fork it and give a new name to the project. So this is how Patroni Nikolay: was born. Georgian name, right? Alexander: Right. Nikolay: What does Michael: it mean? Governor in Georgian. Nikolay: Oh, governor. I think so. Michael: Yeah, Alexander: almost, almost. Almost. Very close, but I'm not a good person to explain like or to translate
from Georgian because I don't Nikolay: Name Alexander: I know yet another word in Georgian and it's a spillo. Yeah, it translates from Georgian as elephant. Nikolay: And the name chose I guess Valentin Gogitseshvili, right? Alexander: Yes, he was, no, at that time he wasn't my boss anymore but we still worked close together and I really appreciate his creativity in inventing good names for projects.
Michael: Yeah, great names. And is this a good time to bring up Spilo? Like, what is Spilo and how is that relevant? Alexander: Spilo, as I said, it translates from Georgian elephant. When we started playing with Governor, we were already targeting to deploy everything in the cloud. We had no other choice but to build a Docker image and provision Postgres in a container. And we called this Docker image Spilo basically we packaged for
Governor Postgres if you Postgres extensions and I think it was WAL-G back then as a backup and point-and-tumble recovery solution. Michael: And it still exists to this day as Spilo, but now with Patroni? Alexander: Yeah, of course. Now there is a Patroni inside, and now Spilo includes plenty of Postgres major versions, which may be an anti-pattern, but it allows you to run major upgrades, like in-place major upgrades.
It also includes WAL-G nowadays, as a modern replacement of WAL-G. And it's used not only by... Operator, right? Not really part of Operator. Spilo is a product on its own. I know that some people run Postgres on Kubernetes or even just on virtual machines with Spilo, without using the Operator.
Nikolay: But using Docker for example? Alexander: Yeah, of course. Michael: But that is a good opportunity to discuss Postgres-operator. Postgres-operator was Zalando's... Was that one of the first operators of its type? I know we've got lots these days. Alexander: Well, maybe it was, but at the same time, the same name was used by Crunchy for their Operator. They were developed in parallel and back then Crunchy wasn't relying on Patroni yet.
As I said, we started moving things to the cloud and at some point, Vector moved a little bit and started running plenty of workloads on Kubernetes, including Postgres. Since deploying everything manually, and more importantly, managing so many Postgres clusters manually was really a nightmare, we started building Postgres-Operator. Back then, I don't think some very nice Go library to implement
the Operator pattern existed and therefore people had to invent everything from scratch and there is a lot of boilerplate code that copied over and so on. Nikolay: Is it only the move to the cloud what mattered here, but maybe also moving to microservices, splitting everything to microservices? Because I remember from Valentin, for example... Alexander: Microservices, of course, played a big role. And probably...
Not probably... Microservices were really driving force to move to the cloud, Because with the scale of the organization, it wasn't possible to keep monolith. And the idea was, let's split everything to microservices, and every microservice usually requires its own Database. Nikolay: Right. Alexander: Sometimes sharded Database, like we used application sharding. In certain cases, the same Database is used by multiple microservices,
but it's a different story. But really, the number of Database clusters that we had to support exploded. From dozens to hundreds and then to thousands. Nikolay: Yeah. And this is already when you cannot rely on humans to perform a failover, right? Alexander: Even when you run a few hundred Database clusters, better not to rely on humans to do maintenance, in my opinion.
Nikolay: Right, so that's interesting and maybe it's also the right time to discuss why Postgres doesn't have internal built-in to failover. I remember discussions about replication when we relied on Slony and Londiste and some people resisted to bring replication inside Postgres, but somehow it was resolved eventually. And Postgres has good replication, physical, logical, sometimes
not good, but it's a different story. In general, it's very good and improving, improving every release. We just last week discussed with Michael what improvements of logical replication in 17, and maybe it will resonate a little bit with topic today, Patroni, but it doesn't happen to autofailover at all, right? Why so? Alexander: I can only guess, because to do it correctly, we cannot just have 2 nodes, which most people run, like primary and standby,
because there are many different factors involved. 1 of the most critical ones is the network between those nodes. When just having 2 machines, you cannot distinguish between failure on the networking and failure of the primary. If you just run health check from a standby and making decision based on the health check, you may have a false positive. Basically, the network just experienced some short glitch, which
could last even a few seconds, sometimes a few minutes, but at the same time the old primary is still there. If we promote a standby, we get to a split-brain situation. With 2 primaries and not being clear to which 1 transactions are running. In the worst case, you end up in an application connecting to both of them. Good luck with assembling all these changes together. Nikolay: This is what tools like repmgr do.
So I ended up calling repmgr a split-brain solution. Because I observed it many, many times. Alexander: Like as a mitigation, what maybe is possible to do, the primary can also run a health check and in case if standby is not available, just stop accepting writes by either restarting in read-only or maybe by implementing some other mechanisms.
But it also means that we lose availability without a good reason. So with this scenario, when we promote standby, technically if standby cannot access someone else, it shouldn't be accepting writes either, like in the network split. Basically, we closely come to set up with how repmgr call it, witness node.
Nikolay: Witness node, yes exactly. Alexander: Witness node, basically you need to have more than 2. And the witness node should help in making decisions. Let's say we have a witness node in some third failure domain, the primary can see the witness node, therefore it can continue to run as a primary. And standby shouldn't be allowed to promote if it cannot access the witness node. And it already reminds some systems like ETCD that complement
consensus algorithm and write is possible when it is accepted by majority of nodes. Nikolay: This wheel already invented, right? Alexander: Yeah, so this is already invented, and what Patroni is really relying on to implement after failover reliably. I can guess that at some moment in Postgres it will be added, and we already have plenty of such components in Postgres that exist.
We have write-ahead log with LSN which is always incremented. We have timelines which is very similar to terms in etcd. So basically at the end we will just need to have more than 2 nodes, better 3, so that we don't stop writes while 1 node is temporarily down. It will give possibility to implement after failover without even doing pg_rewind, let's say.
Because when primary writes to write-ahead log, it will be first confirmed by standby nodes, and only after that. So effectively, this is what we already have, but it's not enough, unfortunately. Nikolay: S. So do you think at some point Patroni will not be needed and everything will be inside Postgres or no? A. Alexander: I hope so, really. Nikolay: S. I hope so. Alexander: A. No, no, no, no, no, no. I'm tired of maintaining Patroni, but because this is what people
really want to have. To deploy highly available Postgres without necessity to research and learn a lot of external tools like Patroni, solutions for backup and point... Nikolay: Upgrade them sometimes because we're always lagging with these Alexander: upgrades. Yeah. But at the same time, Let's imagine that it happens in a couple of years, but with a five-year support cycle, there will still
be a lot of setups that are running not recent Postgres versions, and they still need to use something external, like Patroni. Nikolay: Yeah, I'm actually looking right now at commits of repmgr. It looks like the project is inactive for more than 1 year, almost. Like a few commits, that's it. It's like going down. Alexander: So I have probably some insights about it, not about repmgr, but I know that EnterpriseDB was contributing some features and bug fixes to Patroni, so they officially support
Patroni. Nikolay: So it sounds interesting, right? So Patroni is a winner, obviously. It's used by many Kubernetes operators, many of them, and not only Kubernetes, of course, and winning, of course, some projects were abandoned, not only repmgr, we know some others, right? But you thinking about 1 day everything will be in core and Patroni will be abandoned maybe, right? And you think it's maybe for good. Alexander: So every project has its own life cycle.
At some moment, the project is abandoned and not used by anyone. We are not there yet. Nikolay: Right, right. While we're in this area, I wanted to ask you what you think about, Kubernetes also has, it also relies on consensus algorithm, right? Itself, it has it. Why some operators choose, why do they choose to use Patroni while others like CloudNativePG decide to rely on Kubernetes native mechanisms and avoid using Patroni?
Alexander: To be honest, I don't know what driving people that build cloud-native Postgres. Nikolay: But what's better in general? What are pros and cons? How to compare? What would you do? Alexander: In a sense, CloudNativePG, there is a component that tries to manage all Postgres clusters and decide whether some primary is failed and promote 1 of the standbys.
I'm not sure how they implement in the fencing of the failed primary, because if you don't correctly implement fencing and promote the standby to the primary, you again end up in a split-brain situation. And let's imagine that 1 Kubernetes node is isolated in the network. Nikolay: Network partition. Alexander: Yeah. And it automatically means that you will not be able to stop pods for containers that are running on this node.
At the same time, applications that are running on this node will still use Kubernetes services to be able to connect to the isolated primary. Nikolay: Right, yeah. Alexander: So Patroni detects such scenarios very easily, because Patroni component runs in the same port as Postgres, and in case it cannot write to Kubernetes API, it just does self-fencing, It stops Postgres to read only. Nikolay: It's simple, by the way, right? Alexander: Yeah, so I don't know if they do something similar.
In case if they don't, it's dangerous. Michael: We should do a whole separate episode of CloudNativePG actually I think that would be a good 1 Alexander: yeah I'm not saying that CloudNativePG is better like does something wrong Nikolay: I'm just raising questions Alexander: raising my concerns Michael: of course right back to Patroni it worked like this from the beginning, but it feels like
Alexander: in version 10, which is end of life for a couple of years, by the way. From the very beginning, we wanted to support this feature, but what was stopping us was the promise of Patroni with synchronous replication that we want to promote a node that was synchronous at the time when primary failed. If we just have a single name in synchronous standby names, like single node, it's very easy to say, okay, so this node was synchronous and therefore we can just promote it.
When there are more than 1 node and we require all of them to be synchronous, we can promote any of them. But with quorum-based replication, you can have something like any 1 from a list of, let's say, 3 nodes. Which 1 is synchronous when the primary failed? I'm not demanding to answer this question, So I will just explain how it works in Patroni, like in the last major release. This information about current value of synchronized and bynames is also stored in etcd.
Therefore, those 3 nodes that are listed in synchronized and bynames know that we are listed as quorum nodes and during the leader race they need to access each other and get some number of votes. If there are 3 nodes, It means that every node, to become a new primary, like a new candidate, needs to access 2 remaining nodes, at least. And get confirmation that they're not ahead of all LSN on the
current node. Is it clear? I should elaborate a little bit more. Michael: So if they were ahead, let me ask the stupid question, If a node checks that it is ahead of the current candidate to be leader, that's then a bad decision to promote that leader because a different 1 would... Alexander: So just for your understanding, in Patroni there is no central component that decides on which node to promote.
Every node makes a decision on its own. Therefore, every standby node, like listed in Synchronous Standby Names, goes through the cycle of health checks. It accesses remaining nodes from synchronous to node names and checks at what LSN are there. And if they're on the same LSN or behind, we can assume that this node is the healthiest 1. And the same procedure happens on remaining nodes.
Basically this way we can find, okay, so this node is eligible to become a new primary. In case if we have something like any 2 and 3 nodes, we can make a decision by asking just a single node. Because we know that 2 nodes will have the latest commits, like the latest commits that are reported to the client. And it will be enough to just ask a single node. Although it will ask all nodes from synchronous standby names,
but in case if 1 of them, let's say, failed, together with the primary, it is still enough to make a decision by asking the remaining 1. Nice. And the tricky part comes when we need to change synchronous standby names and the values that we store in etcd. Let's say we want to increase the number of synchronous nodes from 1 to 2.
What should we change first, synchronous standby names, GUK, or value in etcd? So that we can correctly make a decision. If we change first value in etcd, it will assume, okay, so we need to ask just a single node to make a decision, although there is just 1 node that has the latest commits, 100%. And in fact we need to ask 2. Therefore, when we increase this from 1 to 2, first we need to
update the synchronous standby names, and only after that change in etcd. And there are almost a dozen of rules that 1 needs to follow to do such changes in the correct order. Because it's not only about changing replication factor, It's also about adding new nodes to synchronize standby names or removing nodes that are gone and so on. And I don't think any other failover solution implements a general algorithm to do such changes.
Nikolay: How much time did you spend to develop this? Alexander: Originally this feature was implemented by Ants Aasma, he's working for CYBERTEC, it happened in 2018. I did a few attempts to understand this great logic of this algorithm. And finally, almost 5 years after, I was able to get enough time
to fully focus on the problem. And even after that I spent, I don't know, a couple of months implementing and fixing some bugs and corner cases and implementing all possible unit tests to cover all such transitions. Nikolay: There is no book which describes this, that you could follow. This is something really new that needs to be invented, right?
Alexander: Well, the idea was obvious, like how to do it, like, or what to do, but like implementing it correctly and proving that it is really working correctly, like, it's really a challenge. Nikolay: Finding all the edge cases, right. There is another thing I would like to discuss a little bit. It was in Patroni 3, version 3.0, DCS failsafe mode. So DCS is distributed configuration storage.
And actually we just experienced a couple of outages because we are in Google Cloud and they're running Salon operator, Patroni of course. And I just checked the version of Patroni, and it seems to have it. But we... Alexander: But I don't think it is enabled by default. Nikolay: Exactly, this is my second question, actually, why it's not enabled. So, first question, what is it, like, how do you solve this problem when etcd or console is temporarily out?
Alexander: Let's start from problem statement. The promise of Patroni is that it will run as a primary when it can write to a distributed configuration store, like to etcd. If it cannot write to etcd, it means that maybe something is wrong with etcd, or maybe this node is isolated, and therefore writes are failing. And when node is isolated, it's apparently working by design, Patroni cannot write to etcd, it will stop Postgres in read-only
mode, but in case if etcd is totally down, because of some human mistake, you cannot access any single node of etcd. And in this case, Patroni also stops primary and starts it in read-only to protect from the case, let's say, some standby nodes can access DCS at the same time and promote 1 of the nodes. So people were really annoyed by this problem, and were asking
why we are demoting primary. So far the answer was always, alright, so we cannot determine the state, and therefore we demote to be on the safe side. The idea how to improve on that came at one of Postgres conferences after talking with other Patroni users. Like, how it is improved using the failsafe mode. The primary, like when it can determine that none of etcd nodes
are accessible, it will try to access all Patroni nodes in the cluster using the Patroni REST API. And in case if the Patroni primary can get a response from all nodes in the Patroni cluster in the failsafe mode, it will continue to run as a primary. In this case, it's a much stronger requirement than quorum or consensus. So it is not expecting to get responses from, let's say, majority.
It really wants to get responses from all standby nodes to continue to run as a primary. This feature was introduced in Patroni version 3, but it is not enabled by default, because I think there are some side effects when you enable this mode in certain environments. Probably it is related to environments where your node may respond
with a different name. Nikolay: I need to think about it. Alexander: This behavior is documented. Nikolay: Yeah, we will explore this. Thank you so much for it. But it sounds Alexander: like... On Kubernetes it is safe to enable it. Nikolay: Yeah, we should start using this, this is what I think as well. Yeah, definitely we'll explore, thanks. Alexander: Like pods always have the same name, just different IP addresses. Nikolay: I just got help for it. And as usual, I just wanted to publicly thank you for all the help you do for me and actually many companies. Many years it's
huge. Thank you so much So another thing I wanted to discuss is probably replication slots. And I remember a few years ago you implemented support for failover of logical slots. Now we have it in Postgres, right? So one more, finally, yeah. One thing was basically removed, I guess, from Patroni, right? Or you still keep this functionality?
Alexander: No, We still keep it and we didn't do anything special in Postgres 17. Nikolay: It was, I think it was 16 even, no? Alexander: Failover of, ah. Nikolay: Or 17. Well, ability to use a logical slot on physical standbys was in 16, but fell over in 17, we just discussed it. Alexander: Yes, exactly, exactly. I confused you. That's why I'm saying we didn't do anything special. Although I did some tweaks to make this feature work with Patroni,
because it requires to have your database name in the primary coninfo. Patroni wasn't putting the DB name to primary coninfo, because for physical replication, it's not useful. Nikolay: But I wonder... Alexander: How it does it? Nikolay: I wonder in my head, like, of course, we create slot on the primary, it's clear, but Patroni main task is to keep primary alive, to take care of high availability HA for the primary.
Okay, but if we have multiple replicas, multiple standby nodes, and 1 of them is used, or maybe a few, but at least 1, 1 of them is used to logically replicate to some Postgres or Snowflake or anywhere or Kafka or something in this case if this... Yeah, from standby because it's good, we like, less risks on the primary and so on and wall sender is not using CPU and so on. And no out of disk risks.
So now we have this standby and it's dead suddenly. It's not the job of Patroni to take care of it, right? Because we need some mechanism to failover standby now. Alexander: Well, you mean to keep logical replication slot on a new standby where you would like to connect. In theory, Patroni maybe can take care of it, since it's possible to do logical replication from standby nodes since Postgres 16. So how it's implemented currently in Patroni, like logical failover
slots, it creates logical slots on standby nodes and uses pg_replication_slot_advance() to move the slot to the same LSN as it's currently on the primary. So basically the assumption is that logical replication happens on the primary. In theory, there is no reason why it cannot be done for standby nodes. Let's say we create logical slots on all standby nodes with the
same name, and Patroni can watch which 1 is active and publish this information to ATCD and remaining standby nodes will again, like Patroni remaining standby nodes will use pg_replication_slot_advance() to move LSN on standby nodes. So in theory it could work, but Nikolay: I don't Alexander: know if I would have time to work on it. Nikolay: I'm just trying to understand, This is a relatively
new feature since 16 to be able to logically replicate from physical standbys, but... Alexander: But please keep in mind that it still affects primary. Nikolay: Right. Alexander: So, Maybe like pg_wal will not bloat, but pg_catalog certainly will. Nikolay: Yeah, this for sure. I was referring to the need to preserve WAL files on the primary. This risk has gone if we do this. But I cannot imagine how we can start using logical slots on
physical standbys in serious projects without HA ideas. Because right now I don't understand how we solve HA for this. Alexander: Yeah, and unfortunately, this hack that Patroni implements with pg_replication_slot_advance() has its downsides. It literally takes as much time to move the position of the logical slot as you consume it from the slot. That's unfortunate. And how it's solved in Postgres 17, it basically does not need
to parse the whole file and decode it, so it just literally overwrites some values in the replication slot, because it knows exact locations and does it safely. Patroni cannot do it. Although, probably, pg_failover_slots can also do the same. For older versions. Nikolay: Okay, some area, additionally, for me to explore deeper, because I like understanding many places here.
Good pieces of advice as well, thank you so much. Anything else, Michael, you wanted to discuss? Like, obviously, like 1 of the biggest features was Citus support, right? But I'm not using Citus actively, so I don't know. If you want to discuss it, let's discuss. Alexander: I know that some people certainly do, because from time to time I get questions about Citus with Patroni on Slack, or maybe not Citus-specific questions, but according to the output
of the Patroni control list, they are running Citus Cluster. There is certainly a demand, and I believe with Patroni implementing Citus support, it improved quality of life of some organizations and people that want to run sharded setups. Nikolay: Is there anything specific you needed to solve to support this or like technical details? Alexander: To support Citus? So, Citus, I wouldn't say that it was very hard, but it wasn't
very easy either. So, Citus has a notion of Citus Coordinator, where you, like Originally you're supposed to use coordinator for everything, to do DDL, to run transactional workload and so on. And on coordinator there is a metadata table where you register all worker nodes. And worker nodes, This is where you keep the actual data, like charts. And what I had to implement in Patroni is registering automatically
worker nodes inside this metadata. And in case of failover happens For the worker nodes, we need to update metadata and put new IPs or host names, whatever. Basically, when you want to scale out your Citus cluster, you just start more worker nodes. Every worker node, in fact, is another small Patroni cluster. So technically, in Patroni control, it looks like just a single
cluster, but in fact it's 1 cluster for a coordinator, 1 cluster for every worker node, and on each of them there is its own failover happening. If you start worker nodes in a different group, like in the new 1, it joins existing Citus cluster and Patroni, the coordinator, registers new worker nodes. But what Patroni will not do, it will not redistribute existing data to the new workers.
This is something that you will have to do manually afterwards and it has to be your own decision how to scale your data and replicate to other nodes. Although, like nowadays it's possible to do it without downtime because all enterprise features of Citus are included in Citus version 10. So everything that was enterprise now is an open source. Nikolay: That's cool. Michael: I saw Alexander has a good demo of this, of Citus and
Patroni working together, including rebalancing. I think it was Citus Con last year? Alexander: Yeah, it was Citus Con. Michael: Nice, I'll include that video in the show notes. Nikolay: I wish I had all this a few years ago. Alexander: When I... Yeah, of course, like, There was a little bit more work under the hood. In case if you do write workload via coordinator, it's possible, like Patroni can do some tricks to avoid client connection termination,
like while switchover of working nodes is happening. This is what I did during the demo. There are certain tricks, but unfortunately it works only on coordinator and only for write workloads. For read-only workloads, your connection will be broken. That's unfortunate. Maybe 1 day it will be fixed. So in the Citus, maybe 1 day the same stuff will also work on worker nodes. And by the way, on Citus, you can run transactional workload
by connecting to every worker node. Only DDL must happen via coordinator. Michael: Nice. Speaking of improvements in the future, do you have anything lined up that you still want to improve in Patroni? Alexander: That's a very good question. Usually some nice improvements are coming out of nothing. You don't plan anything, but you talk to people and they say,
it would be nice to have this improvement or this feature. And you start thinking about it, wow, yeah, it's a very nice idea and it's great to have it. But I rarely plan some big features from the ground up, let's say. So what I had in my mind, for example, it's a failover to a standby cluster, like in Patroni. Right now it's possible to run a standby cluster which is not
aware of the source where it replicates from. It could be replicating from another Patroni cluster. And what people ask, we have a primary Patroni cluster, we have standby Patroni clusters, but there is no mechanism to automatically promote standby cluster, because it's running in a different region and it is using completely another etcd. So they simply don't know about each other. It would be nice to have, but again I cannot promise when I can start working on it and whether it will happen.
I know that people from CYBERTEC did some experiments and have some proof-of-concept solutions that seem to work but for some reason they also they're also not happy with such solution they implemented. Michael: Yeah, sounds tricky. Alexander: Distributed systems are always tricky. Michael: Yeah, get that on a t-shirt. Nikolay: Thank you for coming. I, as usual, I use podcast and all events, I participate and
organize and so on. I use just for my personal education and daily work as well. I just thank you so much for help. Again. Alexander: Yes, thank you for inviting me. Yeah, it's a nice job that you are doing. I know that many people listening to your podcasts and very happy about it. They learn a lot of great stuff and also making a big list of to-do items like what to check and what to learn.
I cannot say the same about myself that I watch every single episode but sometimes I do. Nikolay: Cool, thank you. Michael: Thanks so much Alexander. Cheers Nikolay.
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...
Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...