Nik and Michael are joined by Lev Kokotov for an update on all things PgDog. Here are some links to things they mentioned: Lev Kokotov https://postgres.fm/people/lev-kokotov PgDog https://github.com/pgdogdev/pgdog Our first PgDog episode (March 2025) https://postgres.fm/episodes/pgdog Sharding pgvector (blog post by Lev) https://pgdog.dev/blog/sharding-pgvector Prepared statements and partitioned table lock explosion (series by Nik) https://postgres.ai/blog/20251028-postgres-marathon-2-009 ~~~ 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 credit 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.
Nikolay: Hello, hello, this is PostgresFM, your favorite podcast about Postgres. And as usual, my name is Nik, PostgresAI, and Michael is here as usual as well from pgMustard. Hi, Michael. Michael: Hello, Nik. Nikolay: And we have a great guest for the second time, Lev, PgDog. Hello, Lev, thank you for coming. Lev: Hello. So thank you for having me for a second time. Nikolay: Yeah, I'm sure there's a lot new to discuss.
So yeah, I think it's a great idea to see what has changed since last time we talked, because it was around your first release, I guess, right? And yeah, so where to start? Lev: Where to start? Yeah, we're in a different year, 2026. So it's been almost 8 months since we last chatted, something like that. 7 months. Now we're in January. Michael: Yeah, I think 10. Lev: 10 months. Oh boy. I mean, I've been working full time on this stuff, so I have
a whole list of things that have been developed ever since. I think the biggest thing is PgDog is very much real and in production with real companies and real customers. You know, last time we chatted, it was like a thing, but now it's a real thing. So that's the big update. It's in prod. Most of the code I think I would comfortably say is stable and performant, which obviously I would say that, but there's real usage behind this which is really cool.
Let's see, there's a long list of features that's been developed since then. Michael: Before we go on to those, I wondered of the people using it in production, what are the primary drivers? Is it sharding or is it like an upgraded pooler? You know, I feel like there are a few selling points to PgDog that we talked about last time and I wondered which ones were driving like the primary adoption. Lev: That's a great question. One of our early adopters, they went straight for sharding, which to me was like, wow, you're brave, sir.
And they went straight for it and they replaced Citus with it, so that was really cool. Nikolay: Can you name some names or? Lev: They're on my website. 1 of their logos, I'll let you guess which 1. I'm still trying to close them as an official customer, so soon I'll be able to say their name, but their logo is there. And yeah, they're pushing, I think, over 100k QPS per shard. And they have like 12 shards, I think Yeah, yeah, it's it's kind of crazy they're using direct to shard query so that's that's
the real you know why it worked so early is because They didn't use any of the fancy like aggregates and sorting across multiple shards. That's the real tricky part. Although we've made some progress there, but it's still not 100% compatible to Postgres does it. So on its way though. But other use cases, load balancer is a huge thing. Like people love having the ability to talk to multiple replicas without actually manually creating a connection pool for each.
You know, there's health checks and failover. 1 of the big uses is we deployed alongside, you know, Aurora or RDS where Patroni is basically not used And it can detect primary replica failover, and it'll move the traffic to the new primary within a second or so, which is really great. And then, of course, when 1 of the replicas starts failing, it'll ban it and then use the remaining replicas for re-query. So you get that, you know, that amazing, like 4 nines of uptime for Postgres, which is pretty Nikolay: cool. I have questions about all these features.
Yeah. All right. Before that, I have underlying question. 10 months, Is it really 10 months or 10 years? Because you use AI. Do you use AI to develop? I do. Yes. Claude, right? Lev: Yeah. I use Claude, Opus specifically. Nikolay: Claude code, Opus 4.5 right now. Right. So this is what you use. Lev: That's right. Yeah. For only certain areas of the code, not all of it. There's some areas where I don't trust it at all, but some areas like, like we have a, we're building like a dashboard like with
all the metrics and stuff. Nikolay: Not critical paths, right? Lev: Yeah. Critical paths are still organic code, if you will. Organic code. Exactly. Yeah. This is the first Nikolay: time I hear this. It's, it's hilarious. Lev: Yeah. Homegrown organic code. But, I don't write JavaScript anymore, for example. I don't write CSS anymore, like that kind of stuff. Nikolay: So you use code for this like additional pieces of software, not super critical person.
And I'm sure you ask it to duplicate and reuse the code as much as possible, recycle existing pieces of code. Stop. Okay. It's Lev: crazy how much that machine loves to generate like copy-based code around. It's allergic to function reuse. You really have to tell it like, hey, yeah. Nikolay: Yeah, I have a special additional code-based review process, which like basically targeting this like deduplication
vector. But it's possible to achieve. If you don't do that, by default, you will get a lot of duplicated code. Yeah, that's great to hear. Okay, I see by the way, great logos on your website. I missed that, haven't visited front page for a while. I see Coinbase Circleback, we use Circleback at work. Some other names, that's great, congrats. Lev: Thank you, hard at work. Nikolay: Yeah, So what is your current pace? How often releases happen?
How do you ship? And if you still write a code a lot, like I guess it's not like every day new version. It's less, right? Lev: Yep. Not every day. Our release schedule is every Thursday. I'm trying to be very diligent about it. Like whatever code that's in main is usually stable, but I give it a week before I tag a release. And then every Thursday I compile release notes and ship it out. And then you get it in GitHub and there's a Docker image that gets built for both x86 and ARM.
So you can pretty much run it anywhere, including on your Mac, if you wanted to. And we have a Helm chart for if you're using Kubernetes, our Helm chart is getting a lot of good usage and contributions from a bunch of people. So, you know, I'm not a Kubernetes expert, but They are, so they're helping me along. So if you're running Kube, you should be able to deploy PgDog in like 30 seconds, honestly. But otherwise, you know, it's just a container. You can run it anywhere. Nikolay: Right, and you use libpg_query for query parsing, so
to build AST trees and yeah, and we recently optimized it, right? So it's not merged, your optimization is not merged to upstream yet, but can you tell us a little bit more about it? Also, specifically, I think My question is also about is it worth optimizing? Like how much of microseconds we save on each query? Should we worry about those microseconds at all? Lev: Yeah, you should. It was... First of all, full disclosure.
I told Claude to do it. And then he did it. I checked it. I was very open about it. I put in the PR. I know how open source projects hate this kind of stuff. Like, you know, 3,000 lines of boilerplate. I was up front, look, guys, this is Claude-generated. I reviewed it. It works okay. But give it a few months before seriously looking at it. But the idea is in libpg_query, there's a translation layer between the C library and a bunch of other bindings. So notably, like we use the Rust binding and the translation
layer is in protobuf. So every single time you call parse or fingerprint, every time you get the AST back, basically, it goes through a serialization and deserialization process with protobuf inside the same process. And that's, and that's really slow actually. So I just replaced it with like only for the rust bindings with just like direct C to rust and rust to C mappings of the Postgres AST and That improved the translation part of it by, honestly, I'm not even exaggerating, like 5x faster. The throughput went from like, you know, 1000 per, I think per
millisecond or per second to like 5 or 10k or something like that. The benchmarks are in the PR, but It's huge. And I think another important factor is CPU utilization. So for us, we're a network proxy, so our CPU budget has to be pretty reasonable. If you can decrease it by 5x, and that's most of our CPU comes from query parsing. Everything else is pretty straightforward stuff, just I.O. Back and forth. And Atomics. So for us, it's huge. Nikolay: So this optimization is just like, you reduced the number of times how much query is copied between like various buffers.
Lev: Exactly. And the protobuf serialization is Probably one of the fastest in the industry, but it's still very, very slow compared to just copying a C struct in memory, like a memcpy. Nikolay: It sounds to me like shorter queries should suffer less from this than huge CTEs, you know, like multi-kilobytes in size. They should suffer even more, right? Lev: Yeah, yeah, definitely. I haven't measured how much faster are slow queries or long queries. I just took up a pretty sizable query, like 600 lines, and used
that as my benchmark. Because a lot of people who use PgDog surprisingly like to write long and complex SQL statements for their database. So that's what I went with and that's where latency will really matter. So yeah, for small ones, maybe it's smaller, but doing protobuf, DCR in memory, it's a big waste of time when you can just map like a C-struct directly with a few allocations. Nikolay: Right, right. I also saw there was optimization of like SIMD vector calculations.
So like, this is like cool stuff or what? Like is it, or you saw some real benefit? Lev: Well, the vector one is kind of funny. There's a blog post about it, but basically I tried to shard a pgvector index among multiple Postgres instances. And to do that, I implemented one of their algorithms, IVFFlat inside PgDog, and then it's able to basically like separate the vectors between multiple databases and then search for them using IVFFlat.
And for that I needed SIMD to calculate, I think I'm supporting, we're supporting cosine distance only, just as a POC, just to see if anyone even cares about this stuff. And that's where the SIMD optimization comes from, which is to calculate the cosine distance between the input and one of the centroids, because we store the centroids basically in a config file. Everything is in PgDog right now is config driven because I trust the disk not to do anything. Eventually we'll move away into something more dynamic. Right now it's like you pre-calculate your centroids in advance
and then you store them and then you can basically query one shard or multiple shards to get your probes, the number of probes, if you will. And then underneath each Postgres database has their own IVFFlat or HNSW index. So it's an approximation on top of an approximation, which is fun because you can scale pgvector into like presumably billions of billions Nikolay: of vectors like others like announced, right, and we said it's not true because it's not a single instance. So, yeah.
Yeah. Yeah. 1,000,000,000 vectors on a single instance, I haven't seen this yet. Lev: No, no, no, it's not possible with pgvector, but with sharding it is. Nikolay: Right. So if you have sharding schema defined, like you can store a lot. Right, so it's a great feature. Anyone using it or is it just showcase and that's it? Lev: I don't think anyone, I think I got an email from somebody who was like, this is really cool and I'm like, well, this is how you need to use it. And they're like, cool. I haven't heard back from them.
But yeah, it's more like a... Nikolay: Speaking of performance, let's talk about two-phase commit. There is support for it, right? Can you describe what's your experience and view on using two-phase commit in larger systems? Oh, we have a dog. Lev: We have a dog. And we have a dog on two-phase commit as well. Okay, just for Nikolay: those who are listening on the audio version, we just saw a dog actually walking in the background.
That's great. Lev: Big dog. She is the reason why PgDog is named PgDog. Nikolay: Right. You explained that last time. Right. So what about two-phase commit and performance overhead it has and so on? Lev: I'll be completely honest with you. I haven't benchmarked it very much. Nobody really talked to me about using it yet in production. So I don't know how much the Postgres internals themselves create an overhead. I just know from the proxy side, it's 2 extra statements to execute.
And also the additional complexity of managing phase 1, phase 2 of the commit. So if phase 1 fails, you need to roll back. And if phase 2 fails, you need to roll forward or commit. And you also need to make it PgDog crash resistant, which we haven't done yet. So there's still work to do there to make it, I think, production grade. But from our point of view, it was just an extra couple of statements to execute. Nikolay: Yeah, okay, understood. So this is just like, also like some foundation for future work, maybe, right? Definitely, yeah.
Lev: After we get Nikolay: some feedback from production systems. Okay, I see. Another thing I noticed also, like, it's fun, fun feature. You created ID generator, right? Which is like, we, like, Postgres 18 brings UUID version 7, but it's 16 bytes. It's quite a lot of space spent for a single ID value. Yours is 8 bytes, right? Lev: Yeah, big end, yeah. Nikolay: It's 8 byte integer, but it behaves similarly. So there is timestamp component in it.
This is cool feature and it's going to serve us until when? 70 years, right? 70 years left. It's not a lot, but for many systems should be enough. What inspired you to build this? Lev: I have a potential production deployment and there were big usage of Omni-sharded tables or replicated tables or mirrored tables or whatever you want to call it Basically the the data in the table is the same on all shards And they needed to generate the primary key for that row And you couldn't rely on the sequence
because even if you used to face commit, which they originally thought they would do, there's still a chance that the sequence numbers on each shard will be different, especially because sequences are consumed whether the transaction succeeds or fails. So the ID could be different on each shard. So you needed an external ID generation service. And the first thought about using a third database or another database to use its sequence, to pull it in and then insert into the query, which creates a single point of failure, but it's much simpler to actually be able to generate a unique big int
inside your proxy, inside PgDog, which is not that hard Nikolay: to do. Could you sequence, we just could have some extra added, like for example, if we say we don't need more than 100 billion per shard, we could say on shard number 1, we always add 100 billion, on the second shard we add 200 billion. Why not? Michael: They need to be the same on every shard. This is the table that's replicated. Nikolay: Okay. I see. Lev: I like Michael: it. I think it's interesting. So I guess it's because we don't have unique constraints across
shards, right? Lev: Yeah, that's right. Well, we don't need them basically because the ID generation is guaranteed to be unique. Before. Yeah, before. Yeah. I mean, all the good database engineers will tell you should still have a Unique Constraint, even for UIDs, which are theoretically guaranteed to be unique, but even in this case, you don't necessarily need 1. So the idea is, again, so you insert the same ID into all the shards and you need to be able to generate it quickly and efficiently. And that's why this like, it's the first of its kind, PgDog executed
Postgres function, okay? Cause we're using the Postgres parser, right? So why not, you know, just hook into the Query execution. Nikolay: Extract. 1 Lev: step towards Citus Nikolay: and having whole Postgres in your middle there, no? Lev: I mean, you heard it here first in Postgres FM. No, I don't know. I think it's a good start, but it was so simple to implement. You extract, you find the function execution, and then you pull, you generate a unique ID inside the pooler.
And the way you do that is, well, you get the timestamp. You kind of assume your clock is reasonably accurate. Then you have an internal sequence number so you can generate multiple IDs per millisecond. So the clock tick is millisecond. And then there's another unique number, call it node ID or PgDog node ID. So you can have multiple PgDog containers in front of the same deployment and it will guarantee that none of them will hit a duplicate from the Nikolay: same list. No collisions, yeah. Lev: No collisions, exactly.
So, you know, the 70 years is a little bit arbitrary. We can reduce any 1 of these components and increase the timestamp component to exit by just reducing the number of PgDog nodes that you can deploy. Right now it's 1,024, which if you're running that much, you're Facebook probably. But you can reduce that by half and give that extra bit to the timestamp. And then you can also reduce the number of per millisecond generation. Right now it's 4 million. You can generate 4 million per millisecond.
It's a lot. You could probably reduce that. Actually, no. Let me double check. I think it's probably 4,000 per millisecond. That sounds more reasonable. Michael: 4 million per second would make sense. 4 million. Lev: That's right, yeah. This is where documentation, or good documentation, Michael: comes Lev: in handy. Michael: That's a big change from last time as well. Lev: Yep. The biggest thing I'm proud of is adding an icon next to every single link. Just, you know, being very, like, attention to detail, I suppose.
But yeah, like, every single comment I get from people is like, well, is this documented? Is this documented? And somebody's talking about our feature, and I'm like, good point. Now it is. Thank you. So if it's not in the docs, I want to say it doesn't exist, but that's not true. There's a backlog of documentation to be done. Yeah. Nikolay: I should help with this. Lev: I would love for you guys to help out with anything. Nikolay: Not I, AI. Oh, AI. Maybe I as well.
I would love to help with AI. Yeah, I'm looking at PgDog from time to time and definitely looking for some use case. And as we all know, maybe not all that we, my team benchmarked. And so I'm looking forward to revisit that benchmark to see how that optimization related to protobuf helped. I'm very curious. This time we are going to visit not with trivial queries like SELECT, that's it, but with some heavy... That's why I mentioned, like, I expect some benefits from...
For heavier queries, bigger text. Yeah, so yeah. Documentation, I think AI should help with it. Like this is my plan. Lev: No, no way, man. Like, because they all sound the same. They all look the same. Like you could give it a couple of tips, like, you'll sound original, but like if you write text using AI, it just all reads like it was written by the same. Nikolay: But at least the reference could be like the usual problem with reference-like documentation, which lists various functions, config params, everything, it usually lags.
And to maintain it, I think AI is great to maintain it. Lev: Yeah, I mean, I don't disagree. For me, writing documentation is kind of like a, it's like a ritual, you know, I go through the things that we built, and I'm like, oh, document, and then I look at what I wrote. I'm like, wait a minute. This is wrong. I'm putting myself into the user's shoes. I'm reading the docs. I'm like, OK, this is what it looks like. Nikolay: It's an additional point where you can verify the whole picture and see if all that makes sense, all that needs to be
adjusted additionally. Yeah, I agree. Also, demos, right? Like when you think how to show what was built, sometimes you end up changing everything. Lev: Yep. That has happened before. Nikolay: Because you simply cannot explain it, it means that it probably should be done differently, right? Lev: Exactly, exactly. So, Nikolay: let's talk maybe about hard stuff, like prepared statements, what happened to them, like how PgDog supports prepared statements,
transaction mode and so on. Can you, can you... Lev: Funny. Yeah. You know, we have to stop sharding, but prepared statements is where we really have issues. Nikolay: Yeah, let's talk about it Lev: a little bit. Sure. They work. They're fine. Everything's great. It works, I think. Nikolay: You saved them, right? Lev: Yeah, they're cached. It's a global cache. So if multiple clients send the same statement, the same statement on the server side is executed. So pretty high cache hits. Nikolay: Implicit or explicit, like protocol level or prepare
keyword? Lev: Protocol level right now and partial support for explicit, Nikolay: you Lev: know, since we have the parser. For the explicit ones I need to differentiate on the number. Sometimes you can pass the data types in the prepared statement, sometimes you don't. I just need to add a little bit more support to the parser there to extract the differentiation between like different unique... Identical prepared statements, but they expect different data types for some reason. So protocol ones work.
I guarantee it. If they don't, I swear. They do. But the other kind is still a work in progress. Nikolay: Yeah, and have you seen any issues with prepared statements used together with partitioning or your clients don't use partitioning at all because they have sharding? Why use partitioning? Lev: I think we use them with partitioning. I haven't seen any issues. Like if the query, if the prepared statement plan is wrong, Postgres
will return an error and we evict it from the cache and then we prepare it. Nikolay: It's not that simple, right? Because the first few calls are not cached yet in prepared statements because first it uses custom plan for the first 5 times. There is a hard-coded constant in PostgreSQL. And with partitioning, it's much more complicated. I have a series of blog posts about this. It's like super complicated. Yeah, I'm curious about this. Maybe just that nobody encountered any issues yet, but yeah,
I'm curious about this. Lev: No clue. Yeah, let me know what I need to fix. Nikolay: Yeah, I understand. Very practical. I understand that. Michael: Yeah, but it's also, also Nik, I think you're like, this feels like the kind of thing that actually gets a little bit better with sharding. Like you just reduce the number, you reduce the QPS, you reduce the load on each like, it, everything that could cause issues around that is, however many shards you have, times less bad
or less likely to hit the cliff? Nikolay: Absolutely, sharding reduces the need in partitioning, 100% so, but maybe not fully eliminates it because partitioning, if it's time-based, it still makes sense within 1 shard. We know cases of really large databases which combine both approaches. Michael: Yeah, for maintenance reasons it still makes complete sense, right? Nikolay: Right. Yeah, yeah. So yeah, it's sticky. So and if you want prepared statements, oh, It's like, it's
super complex. Lev: So what's the deal? What doesn't work? Nikolay: So partitioning brings cache not at planning time, but at execution time. And this increases complexity because if you just deal with planning time cache, it's quite simple. So if it's not partitioned, only planner can decide the cache or not, right? That's it. If it's partitioned, there is a decision point shifted to execution
time. And there is a point when basically you are without cache and you cannot avoid it because when cache is being built, at that execution, it's number 6 execution or fifth execution. So at that point, executor doesn't have this cache because it's being built. So we are without cache. And in high QPS cases, when frequency of calls is super high, We basically, there is a moment when we must leave without cache
and it might be a super non-performance. So performance can be terrible in that 0, Lev: I see. Nikolay: Which doesn't happen with unpartitioned case at all. This is like, I already keep, I already start forgetting details because so many nuances if you don't leave there, if you don't only visit it like I did for a couple of weeks and then, but I have several blog posts about this problem with demonstrations. Lev: See, this is the prime example why being a proxy for Postgres sharding is the best place to be because to me, that problem
is completely irrelevant. Right? Because I'm just passing statements through, right? I think, unless I'm, because like, they will work or they won't, but from a performance perspective, like if you have trouble with partition tables and prepared statements, you just don't use prepared statements, right? Nikolay: Yeah, yeah, yeah. Partitioning, we must, so Partitioning also brings the problem of explosion of access share locks. Because...
I'm talking about this particular, but yeah. So if you have a lot of partitions, and we have plan cache 1 idea, but also we have routing to specific partition, which is called constraint exclusion, right? We need to route to specific partition. This is another idea. And at planning time, if planner knows where to route, this is normal case, when you don't have prepared statements. It just routes to specific partition, all good.
No problem. We lock only 1 partition with all its indexes. But if we postpone it to execution, it needs to lock all tables with all its indexes, and it's terrible. With sharding, I don't know, you just... Lev: Well, you reduce the QPS, so... Nikolay: Yeah, this is already... And the lock manager is not a problem anymore. Or you just need to use Postgres 18 and raise max locks per transaction.
In this case, lock manager also is the same. So yeah, anyway, this is an interesting topic. And I'm curious if some people combine sharding and partitioning I think they should sometimes they should sometimes and it's just it's in really big setups yeah Lev: I mean as a guy who's trying to build sharding you should always use sharding unless you can't but like that's you know Michael: I think there's a maintenance argument. Imagine if you've got like a logs table per shard and You want
to delete old logs after a certain you've got a retention period you probably still want partitioning to make those deletes really efficient. I think there's still a use case for it but it's not so much for performance reasons but it's for maintenance reasons, that'd be my guess. Nikolay: Yeah, for to build indexes, to have vacuum working more efficiently, and so on and so on. Michael: Yeah, but I think those are, I still characterize those slight, I know they are maintenance, but they're kind of still performance reasons, right? You want them, because you don't want the table to be too big.
I'm talking about doing like deletes efficiently, being able to, instead of having to like do batch deletes you can... Exactly. Nikolay: It's exactly how's it called not data retention but there is some additional term Michael: yeah yeah the opposite of Lev: yeah there could be like a legal requirement for data retention. I think that's the only reason why people would ever delete data, right? Well, the other reason is performance. So if you eliminate the performance argument, like if you make performance just free because of sharding makes that, I mean,
Obviously that's not the case, but like, no. Nikolay: Yeah, and if you compare sharding and partitioning, with partitioning, we often are willing to have 1 active partition where we write and all others are archived. In case of sharding, it's strange, right? Because a lot of hardware is not used. Like if you have only 1 shard read-write and others like archive, it's strange, simply strange, right? So you want to distribute writes usually. Lev: You want it to be equivalent, like you want like a perfect, like all shards have the same load, And that's why you'd usually
use like hash-based partitioning if you can, again. Or the way you design your schema for sharding is you make sure that each shard roughly has the same load. If you have hot shard, you're gonna have a hot shard problem sooner rather than later. Nikolay: Yeah. Lev: Exactly, especially for like multi-tenant, like what I call multi-tenant is B2B basically, where your sharding key is like a customer ID, if you will, and then 1 of your customers grows big and you need a second sharding tier, or if you will, a second partitioning tier. Nikolay: And does PgDog support resharding without downtime
already, you know? Oh yeah, yeah, yeah, of course. How's it technically working? Lev: I mean, it's good. It's been tested, so that's kind of cool. The idea is, I mean, I don't want to say simple, but I think practical. For re-sharding, this is where the contrast between how this works and like NoSQL databases work is a little bit more apparent. With NoSQL, you kind of add another machine and then it kind of rebalances all the data using existing machines. The PgDog approach is you create a brand new empty cluster, and
then you copy all the data out into the new cluster. And this decision is actually not because of a particular limitation, but it's intentional. Because you want to reduce the load on the existing cluster during re-sharding, because usually when you start re-sharding, it's already under load, it's already underperforming, it's already having problems. So you don't want to add additional load with resharding to it. So you create a new, brand new cluster, you move the data over and we are able to use replicas since Postgres 16 to copy data out with logical replication, that was a huge thing.
That was, I think, one of the game changers that made this possible, is logical replication slots on replicas. Why? Why does it matter? Why does it matter? Because you can create 16 replicas and parallelize your copying. Why does it matter? Nikolay: Disco or what? Why does it matter? Like you don't want to use disco on primary or what? Lev: Well, you definitely don't. Your primary is already shot. Like if when you're sharding, your primary is already having a really, really bad time. That's the main reason behind sharding. You want to scale your writes.
So adding additional replication load to an already underwater database is bad. And especially when you want to create, let's say, you go from one shard to 16 shards. Extreme example, but realistic. You're going to add 16 replication streams to your primary. That's like 16 times like streaming the same data. The thing is going to fall over. I guarantee it. Nikolay: But overhead is amplified here. So the same work is done on different CPUs, VCPUs. Lev: Yeah, yeah, decoded and recoded.
Nikolay: Yeah, yeah, yeah. So. You want to shard this load, right? Lev: Exactly, exactly. We're able to use one replica per table basically for, for re-sharding. So if you have like, say 1000 tables, you're able to shard your sharding stream and you're both like split it into 16 different machines or 32 different machines and sync it in parallel, which I think is pretty huge. It's all the same LSN, right? Because they're replicas, right? So you can synchronize at the end and make sure that you got, you're at the right point and like pause the live traffic and
then wait for the logical stream to catch up and cut over to the new cluster. Like all of that is, the pieces are there, you just need a button to make it work. We haven't built the button yet, but we've built all the levers you need to push as an operator. So when Nikolay: you say you create cluster, you mean create Postgres cluster as just basically 1 new primary node. And when you say you copy, it means that there is already something in PgDog that copies. You just need to use it. It's not on the shoulders of the user, right?
Lev: No, we hook into the replication protocol to stream the rows and we use copy, binary copy underneath, and we shard the copy stream. Nikolay: All right, this is the option since Postgres 16, or 14, I think. Binary. Lev: Oh, it's been a minute, yeah. Nikolay: When you initiate logical replica, I guess. There is option binary equals true for initialization part. Interesting, actually. Lev: Yeah, look at this. Nikolay: There is also trick to convert physical to logical,
which is official since Postgres 17, I think it's called pg_createsubscriber, CLI. Lev: Oh, interesting. I'm using like pgoutput, which streams everything in text Nikolay: format. Yeah, yeah, yeah. But yes, but right. But still there are additional things here. You can convert physical replica to logical replica, and we could do it before, but now it's official. It's official. Lev: Oh, I Nikolay: see. pg_createsubscriber. Unfortunately, we won't be able to use it on most managed services
because they don't expose this. They should, actually. Yeah. They should expose this in API. I hope they will. But in case of self-managed, it's good. Lev: Yeah, with self-managed, you have a lot more options for sure. I think the original goal was to make this work in managed environments because that's where them. Nikolay: Right. Yeah. You mentioned Aurora already, support of Aurora. Lev: That's great. Nikolay: Yeah. And these replicas, so you create logical replica out of physical copying data and, and, and performing CDC.
So, so that's ranges from a slot on replica, which is supported since recently, right? I'm curious this, when you initialize logical replica from this, is this physical standby? Does it have hot standby feedback on or off? Does it report to the primary that it still needs some data? Because if it does, the primary health can be affected. If you eventually switch over, that bloat doesn't matter.
But if you roll back... Lev: So I think Nikolay: if this physical replica is not used by anyone else, I would keep HBF off. To let it lag, I would raise the max. There are a couple of settings which allow a replica to lag. If hot standby feedback is off, it's allowed to lag. There are many, many nuances here in this process. I'm curious how you do it. But okay, you're using logical replica here.
Lev: Yeah, that's a good call out actually. Like my recommendation is to create a dedicated replica for this or dedicated set of replicas, but. Nikolay: Without hot standby feedback, I would Lev: do it. Nikolay: If it's temporary replica, why not? Like, And also how this replica is created, right? Like it's an interesting topic. Do you integrate with some backup tools or you just, it's out of scope, right? For PgDog. Lev: I feel like at the moment it is, but maybe in 6 months it
won't be. Like right now I'm relying on people using like some kind of reliable way to create replicas. If you use RDS, you just click a button. Again, like if you're using like any of the managed services, you just click a button, you get a replica, it's using disk snapshots. You're self-hosting. Like if you have ZFS, presumably you'll have your snapshots somewhere. You can restore from, from a snapshot and resynchronize pretty quickly. But even then it takes a while to, to sync the ZFS snapshot on disk. Tested that 1. Takes minutes, sometimes hours.
Oh, well, if you have like a 2 terabyte file system. Nikolay: You mean the first send receive? So between the hosts? Yeah, I understand. Lev: Yeah. It's surprisingly limited by, I think, actually network. I needed to like compress it and then do something else to it. And I wrote a blog post to it on our old blog at Postgres. Oh my god. Michael: ML? Lev: Thank you. But the website went down so my shame went down with it as well.
The blog post is gone. I think it was a good blog post but whatever. I learned how to use compression tools and ZFS and, you know, big deal. Michael: Just out of interest, like, are people generally using this with a managed service or is it quite split between managed service and self-managed? Lev: Right now it's split. A lot of management or service use, especially like failover for Aurora is a big deal. Like failover detection, because you don't run Patroni typically. So it's able to like query the pg..., sorry, what was the
name of the function? Nikolay: pg_is_in_recovery. Lev: pg_is_in_recovery, thank you. Nikolay: In my opinion, to stop confusing people. Lev: The people who know, know, and the people who don't know, don't need to know. Yeah, if you know, you know. Yeah. Right. Yeah, and I'm sure you know Aurora loves to failover on a perfectly good day. So it helps a lot with uptime. But 1 of my biggest sharded deployments is literally like on-prem, on-prem, like They rent their own data center and everything.
So it varies. Like that's the beauty of it. Like operating on the application network layer, you really don't care that much where you're deployed. Nikolay: So based on these, almost a year since you started this, What's your current vision for your customer who needs pg_auto_failover? And what's your vision for next future steps? Where do you move? Lev: Yeah, this is a really interesting question because sharding is always going to be like the north star because it did it explains
to people like this is a scaling product ultimately you can start anywhere I already feel like it's a better connection pool than pgBouncer Nikolay: I Lev: already feel like the load balancing aspect of it is a game changer and sharding as much as it is a work in progress, is the North Star for us. So that's how we want this project to evolve. We want to make Postgres as scalable as Cassandra or Scylla or Elasticsearch. I don't know what people think about those particular databases, but I think they're horizontally scaled, we can all agree.
So we want Postgres to be like that as well. We're building, it hasn't been released yet, we're building an enterprise edition of PgDog, which adds the classic things like monitoring, understanding how everything works, a control plane, something that we can. Sell as a product to people, you know, like you don't have to like go through like many, many steps requires to set up like a, like a sharding layer and network proxy, So that should come out within the next few months, hopefully. Nikolay: Will it include automated sharding with AI?
Like, press a button and your database is sharded already. Lev: You want automatic sharding with AI as part of our future set? Nikolay: I think we Michael: just want more consulting clients. Lev: I don't think we'll ever get automatic sharding with AI, but I don't know. Right now my idea for that is like dump the, use pg_dump, get the schema out, pipe it into ChatGPT and ask it what to do. With a prompt. And test.
Yeah, exactly. And test. Yeah. I don't know. A human being is able to come up with a sharding key really quickly. Like I talked to like a senior database person at a company or an app engineer and they're like, oh, my sharding key is like, they already know it's org ID or customer ID or like they instinctively know how the data is organized and they even know what the edge cases are. You know, like I don't think you need AI for that 1 unless you literally have you've never seen this database before but yeah, that's That's the way I think about it.
Nikolay: I don't think Michael: yeah on the vision stuff. How how closely are you paying attention to the other sharding projects in the space and how do you differ in terms of your North Star versus what you think theirs is based on what they're saying? Lev: I'll be completely honest with you, I literally never look at them. And it's not because I think I'm cool or something, It's literally I just don't have the time like I have I have something in my mind I'm building it what the other guys are doing is usually more of a distraction than the learning point to me personally
So I have my vision of my North Star and I'm gonna follow it until I hit a wall or something. And so far I haven't. So that's how I think about it. Nikolay: Great words to finish this podcast, I guess. Michael: Is there, is there anything left that you wanted to like mention that we haven't asked about? Lev: Ah, well, let's see, I have like a, I'm gonna do a post on Hacker News soon with all the stuff that we've done, but I'm just gonna blab with you, I know it's kind of boring. We added a bunch of support for Aggregate functions, you know,
we do query rewriting now, which is really cool. Like we take the AST and we add whatever needs, whatever we need to that query to execute it. 1 thing that I think is really, really phenomenal is we support sharding key updates. That's something I think Citus never got around to do is like changing the actual value of the sharding key and moving the rows between the shards. Wow. That one's really fun. I don't know how often it's gonna be used, but anytime somebody asks us about sharding, they ask about that feature, so it must be useful. We support multi-Tuple inserts, we rewrite insert statements,
we separate the Tuples, and we send them individually to each shard, you know, all of that happens, you know, transparently, which is really cool. We're just, we're focusing on usability, like the idea is you just deploy this and you don't change your app, like don't rewrite your queries, don't rewrite your code and just make it work. But, you know, obviously We're not there yet, but we're getting closer. So every week there's something new that comes out. Nikolay: And it comes from the fields, right? Lev: Yep. It comes from people asking me, hey, is this supported? I'm like, yes. Almost, almost.
Next week. Thank you for the suggestion. Nikolay: Yeah. Cool. Yeah. Production driven development. Lev: It is. I really enjoy it because every single feature is immediately used. Like, it's great. Nikolay: Cool. Great. Thank you. Thank you for update. It's really good to see the project is growing. Excited to test it once again soon. Lev: That would be great. And I still have to review and merge your PR. Nikolay: It's OK.
Lev: It's been open for, I believe, 10 months, since the last time we talked. Nikolay: Something like this. Good. Michael: Well yeah really good to see you again Lev. Take care. Lev: Thank you so much. Michael: Catch you soon. Lev: Yeah Thanks so much for having me guys. Bye bye.
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...