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.

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.