Michael: Hello and welcome to Postgres.FM, a weekly show about all things PostgreSQL.
I am Michael, founder of pgMustard and I'm joined as always
by Nik, founder of PostgresAI. Hey Nik. Nikolay: Hi Michael.
Michael: And today we have with us David Steele, who is a significant
contributor to PostgreSQL and the creator and maintainer of both pgBackRest, which we'll be talking about today, and pgaudit.
Welcome, David. David: Thank you very much. Good to be here, Nik and Michael. Michael: It's a pleasure to have you.
Right, to get started, I wondered if you could give us a little
bit of history, a little bit of the origin story, perhaps of
pgBackRest.
David: Sure. That's easy enough. Actually, pgBackRest was born at the Dublin conference in 2013
of conversations that Stephen Frost and Cynthia Shang and I and
Magnus Hagander and some others were having At that time, we
were working on a fairly large database for the time, around
50T.
That doesn't sound so big these days, but in 2013, that was a
pretty significant database.
You know, basically we needed to make backups, of course, as
you do.
And just the available tools were not up to the task.
It was just simply too large.
And we really need to be able to do incrementals.
And there was nothing that would do incrementals plus compression
at the same time, et cetera, et cetera.
So As you do in open source, we decided that we would build our
own thing.
I remember originally it was going to be a pretty simple project.
Write it in Perl, keep it simple, just be 1 file, so it would
be easy to copy around and distribute and etc.
Yeah, that didn't last very long.
Obviously it grew pretty quickly.
So I built it.
I built the initial software that was usable in about 40 hours
and to basically solve our initial problem and then just kept
building on it as we had problems and bugs and other things I
would build on it and build on it.
Convinced the company I was working for, Resonate, which is an
ad tech company, to open source it.
And then when I left there, I kept noodling around at it.
I took some time off and just kept working on it.
Got the restore functionality working well.
And then that's when I got hired into Crunchy.
At 1st they weren't super interested in it but Stephen was.
So I got a little bit of time to work on it and then eventually
got more time to work on it and then eventually we hired Cynthia
to come and work on it with me and that went on for a while and
then finally we decided to migrate it to C and we did the whole
C migration which was 2 years so that was painful because we
didn't we basically didn't write any new features for 2 years
we fixed bugs only And we could only write a new feature if it
lived entirely in the C code.
And over time, it was more and more possible for that to happen,
but it was a tough migration.
2 test suites, 2 of them, it was just, I didn't even want to
think about it.
It was not a lot of fun.
And that's basically the project as it is today.
Now it's, of course, we're rolling along.
It's a C project.
We have features based on user demand, based on chasing performance.
Performance is always the big thing.
Like how do we move a bunch of data quickly?
How do we do it efficiently?
How do we keep the repo as small as possible, block incremental
backups, the list goes on and on.
Nikolay: I have a question here about history.
Maybe you remember those discussions should Postgres have Remember
times of Slony and then Londiste and there was discussion
should Postgres have replication inside.
And there are many such discussions, same for auto failover and
so on.
And For the case of replication, the idea to have it in core
won.
For the case of auto failover, it lost.
Still auto failover is outside.
I'm very curious what you think about the idea to have full-fledged backup solution inside core and why it's not happening.
David: 1 interesting historical fact is, and 1 of the reasons
why some of the other committers were involved in really early
planning, and part of the reason for the migration to C was the
idea was that we could actually maybe make pgBackRest the core
solution for backup.
Now, this is not something that was endorsed by core broadly,
or it was just discussions that I had with some committers, because
they were interested in having a more comprehensive solution in core.
Obviously, they wanted it to be written in C.
And so that was 1 of the reasons that drove the adoption of C.
As for actually having something like pgBackRest in core, it's
a little tricky because the pgBackRest project moves a lot faster
than core does So to have it on the yearly cycle would be But
let's say we had put pgBackRest into core like from the beginning
Maybe as soon as it was migrated to C.
It would not be nearly as far along as it is now At the time
we were doing 12 releases a year, we went down to 6, and now
we're currently at 4, which I think is just about the right tempo
for a project of this type.
But being able to release features 4 times a year, get new stuff
out there, get people trying it, get people testing it, that
kind of stuff.
I think it's pretty important.
And as pgBackRest gets more stable, maybe it would be more appropriate.
But at the same time, the project has diverged significantly
from, you know, we use a lot of the same concepts as Postgres,
MemContext, and error handling looks a lot similar and et cetera,
et cetera.
But it's all pretty different.
2, pgBackRest grew its own way.
So it'd be very difficult to get in there.
I think the idea is pg_basebackup was going to be that tool.
Getting incremental backups into pg_basebackup was a big step,
but it's still not a complete tool.
You have to have tooling on top of pg_basebackup for it to
be at all usable, especially for incremental, because reconstructing
all those incrementals, going and fetching them and uncompressing
them and getting them all ready for the pg_combinebackup tool
to run.
That's a significant amount of work and obviously it doesn't
do anything with WAL archiving, expiration, there's just the
list of all the things that you need to do and it's a pretty
big list and it's intimidating, honestly.
You know how core goes, right?
It's intimidating to even contemplate getting something like
that into core.
So I think it would be a good idea, but also Think about storage
drivers.
Support for pg_basebackup supports POSIX, right?
But most backup tools support S3, GCS, Azure, SFTP, etc.
Because those are the tools that people are actually using.
So all that would have to go into core as well.
All the storage drivers, people would have to...
Nikolay: Maybe not everything could go.
It's possible, Postgres is very extensible.
If just the core thing would go to the core, but expose some
interfaces, particular drivers could stay outside.
David: And in theory, that's what we've done, a bit with pg_basebackup,
but the amount of stuff that needs to be done by the outside
tool is huge.
Now there are quite a few tools that are based on pg_basebackup
to do their page level slash block level incrementals.
Barman, obviously, is the most well-known 1.
I think pgmoneta is 1 of them.
There's a newer 1 that I saw recently that's also using it.
So you can certainly build a tool around pg_basebackup.
And I think the idea would be that you'd extend and extend pg_basebackup,
and then people would take that feature out of their tool.
And eventually maybe pg_basebackup would be a thing that does
everything.
But at the pace that it's actually evolving, I would expect that
to be able to reach feature parity with something like pgBackRest
or WAL-G or Barman in approximately 30 years.
I'm exaggerating a little bit, but if I look at the actual progress
that pg_basebackup has made over the years, that's where we are.
Since that incremental, nothing's been done with that, even though
there are pretty huge performance implications for restores.
Any large, very large database to restore with that page incremental
format is you basically need, let's say you've got a database
that's a terabyte, you need at least 2 terabytes to do the restore
minimum, and it depends on how many incrementals you have, so
you could need 2, 3 terabytes to do the restore.
All the files need to be copied down regardless of whether those
blocks are used or not.
Everything needs to be uncompressed and then fed into pg_combinebackup
which then rewrites everything to a different location.
So from a scalability standpoint, it has a pretty serious problem.
And we're 2 versions on from it being introduced and no 1 has
actually even thought about actually addressing any of those
issues.
I don't want to harp on this too much, but the main point is
getting stuff into core is hard.
It takes me years.
I've been working on a very small change for backup just to mark
pg_control when a backup label is required.
So you do a backup, a backup label is required to do the recovery.
If the user deletes the backup label, they can end up with corruption,
silent corruption.
It's quite annoying actually.
And I've seen people who know Postgres really well, hackers do
this and not understand what happened.
Nikolay: I saw it many times already in various teams.
It's annoying.
David: So 2 years ago I introduced a small patch for Postgres
to just put a flag in pg_control to mark it when we actually
need a backup label.
So if you start Postgres and backup label's not there, it will
just stop and say, no, you must have backup label.
Please provide.
That was 2 years ago.
It goes through review occasionally, but really nothing there.
In pgBackRest, we actually implemented that about 3 years ago.
What we do, this is a bit of a hack but it works pretty well,
what we do is we overwrite the last checkpoint in pg_control,
we write in the hex value DEAD.
So if we get a report from a user, because it will come up a
Postgres that will say unable to find checkpoint DEAD.
And that's actually not a valid checkpoint at all because it's
under the 1st WAL segment limit.
So, Postgres will mark it as invalid, it will throw an error,
and then when we get a report from the user we immediately know,
hey you tried to start this you delete a backup label and they're
like yeah I deleted backup label and boom there you go.
But I've been trying to get that that I wrote that patch 2 years
ago and I'm still hoping to get that into Postgres.
So that's like the speed at which Postgres can operate sometimes,
especially for people like me who aren't really committers.
So can you imagine trying to get something the size of pgBackRest
or complexity of pgBackRest into Postgres?
Nikolay: Yeah, I wish we could highlight this, put a link to this
patch and maybe drive some attention to it because some people
who participate in Postgres hacking listen to us and maybe...
I agree it's super annoying and right now All the other tools
they need to...
Basically, if you create full copy, full backup on a replica,
you are responsible for placing this backup label yourself.
And it's also how restore works in Postgres.
I fixed recently, and This fix was very quick because it was
obvious.
It was just a problem in code and Postgres in the store path.
How Postgres is working with the backup label and also signal
files.
If you look how people use it, they see some error in logs and
then, okay, I will just delete some file, right?
To get it going and this is what's happening all the time right
but also I noticed pgBackRest also requires maybe because of what
this mechanism you explained well when making backup on replica
it requires connection to primary And this surprised me a lot
recently when I started to code, right?
David: Yeah, that actually doesn't have anything to do with
that specifically.
The reason why we did that is you get a better backup that way.
Nikolay: Better backup.
Not all backups are equal.
David: You get a better backup.
1st of all, there are some things about backup from standby.
I don't necessarily want to get into that because it's pretty
esoteric, but there's some things about backups from standby
that I don't 100% trust.
But I do believe it works.
The reason why a primary backup is better, though, is you get
a couple things.
You get stats from the primary, which is actually pretty nice.
If you do a backup from standby and you restore, you've got stats
from, say, a standby.
And if you're actually doing analysis of statistics, it's pretty
shocking to suddenly have like all of your index patterns, scan
pattern, read patterns, full change, everything.
So I'm not talking about, sorry, table statistics like basically
like scan statistics.
Maybe I'm using the wrong word.
Michael: Shoot.
No, I think you, I think I understand number of seq scans on a
specific.
David: Yeah, I think they're both, I think they're both stats,
but, but I'm not talking about the planner statistics.
I'm talking about like the actual usage stats.
Another thing is if you're doing a backup on the standby, you
can't actually finish the backup and verify that all the WAL
has reached the archive.
You can, but you might have to wait a day for that to happen
or whatever.
We feel it's really important to make sure that this backup,
When we mark the backup as done, we want it to be done.
Not hoping that someday this WAL archive is going to arrive.
We want it to be done at that moment.
So that's pretty important.
Nikolay: Do you mean the backup is self-sufficient?
It positively affects RTO, right?
Like recovery time objective.
It reduces the time needed to recover.
David: Well, at the very least, we want to know that the backup
can be recovered to consistency.
Right.
Now, if you want to do point-in-time recovery, that's going to
be after the backup finishes, you'll continue WAL archiving,
you need to monitor that as well.
But at the point where the backup finishes, we want that to definitely
be true.
Other things like getting actual logs from the primary are more
interesting than getting logs from a standby.
Although I don't really recommend that people put logs in their
PGDATA directory at all.
It's actually quite common.
Nikolay: Hold on 1 2nd.
Too many things here.
1st of all, when you say logs, it's WAL files, right?
David: No, sorry, in this case I do mean logs.
So like basically textual logs that are generated by Postgres,
and a lot of people will put those in PGDATA.
Nikolay: Okay, I didn't get it.
Why do we care about logs when making backup?
Ah, because we don't want them to be backed up, right?
David: We do if you have them in...
So If people are putting their logs in PGDATA and they expect
them to be from the primary, it can be surprising when their
logs from a standby or something like that.
I see.
They're not seeing the information that they expect to get.
Maybe they have auditing turned on in the primary and not on
the standby.
You recover the backup and now all your audit records are gone,
etc.
So now I'm not a proponent of putting logs in PGDATA on the
primary, but there are people who do this and they want to preserve
those logs.
That's another reason.
So there's a whole raft of reasons, but basically, so what we
do is we copy everything that's replicated from the standby.
Oh, the other reason why we really wanted to do this, although
honestly we've never implemented it, is once you've done this,
you can actually parallelize a backup across all available standbys.
Because you're coordinating everything on the primary, you wait
for the standbys to reach the checkpoint where the backup started,
which we already do, of course.
We do a bunch of consistency checks.
And then you would be able to parallelize your backup across
all the available standbys and really supercharge it.
And it's not even that hard to do.
We just haven't really, there hasn't been demand for it.
And there's always 1000000 things to write.
You know how it is.
So we just haven't gotten to it yet.
But that was another big reason to do it that way, because it
allows us to parallelize backup in a way that otherwise wouldn't
be possible if you're just backing up from a single standby.
Nikolay: There is a dilemma here where, 1st of all, making full
backup on primary, especially if it's full, not incremental,
full, It's huge stress for disks.
David: Yeah, you don't want that.
Nikolay: Yeah.
So usually people have loaded to some standby, and then the problem
is also which standby, because it's also stress.
If it's a read replica, it's stress for those reads as well.
So distributing totally makes sense, but there is also, like,
since you talk and focus a lot on corruption and consistency,
I was always curious if we, for example, have allocated standby
only for backups, which I think Crunchy Bridge had an issue, we
had a customer there, Crunchy Bridge had an issue when they made
full backups on primary.
I was super shocked because like how come it was fixed then?
And I think full backups went to HA replica, which is for HA.
It's allocated, it doesn't receive reads.
But then I'm curious, if some corruption happens, it might happen
only, there are many kinds of corruption, right?
I can think about, at least theoretically, some corruption might
be only on 1 node, but not on others.
And if you always make backups from only 1 node and you don't
notice this corruption propagates to all backups.
So some rotation at least is good, but combining and parallelization,
it's also interesting.
I'm just thinking when SQL Server, for example, there is a mechanism
to self-heal if, for example, primary notices some pages are
corrupted, it can grab them from replicas, which was implemented
long ago.
This is cool technology.
I'm curious about this dilemma.
We want also distribute load not to hurt our user traffic, but
also what's happening with corruption.
This is Pandora box of topics.
David: That'd be an interesting idea actually, too.
If you were doing backups from multiple standbys, you could compare
and contrast.
If there was corruption on 1 standby, you could say, hey, why
don't I try grabbing this file from the other standby instead,
fail it, send it back to the main process, which would reissue
that job on a different standby.
That's a pretty cool idea.
Nikolay: You talk about corruption, like data checksums level
of corruption but there may be a higher level of corruption like
index level, like logical level, foreign keys, like a lot of
stuff.
David: Oh sure and your corruption scenarios on primaries and
standbys are quite different too because the way things are being
written out on a primary is actually fairly different to the
way it's being written out on a standby.
Also the standby is essentially a single threaded operation.
Let's not
Nikolay: go there,
David: it's terrible.
Yeah, obviously it's terrible for performance from the standpoint
of any issues there might be with locking and parallelism and
other things on a primary, the standby is not going to see those
bugs, probably.
And so there are whole classes of bugs that can happen on a primary
that could cause corruption that aren't going to happen on a
standby.
So from that perspective, it might actually be safer to do backups
on a standby as well, just because it's a simpler path.
The data goes through a much simpler path to get to disk on the
standby than it does on the primary, as a rule, unless you really
have 1 writer on the primary, which is not the way things tend
to run these days, not for interesting databases at least.
So yeah.
And as you say, the corruption we're detecting is only checksum
corruption.
There are some projects out there where people have combined
pgBackRest with amcheck.
So basically when it does recovery, like basically you call this
command, it automatically integrates pgBackRest with some sanity
checks afterwards and does amcheck and some other things to check
a higher level of consistency than just the page checksums.
Nikolay: Yeah, when you do recovery it's a good point usually.
This is what we usually do.
Not all recovery attempts, but some of them, percentage of them,
should also check index health.
David: This is really useful in test recovery.
So hopefully you're testing your recovery, right?
And that's a great time to do amcheck because then you can have
confidence in your emergency hair-on- fire recovery that it actually
does work because you practiced it, you ran amcheck on all the
practice runs.
So at that point you should feel pretty confident to run the
production restore without having to take the penalty of running
amcheck at that time, if you've been properly testing.
Nikolay: I agree with everything, but You cannot do it on all runs
because full-fledged amcheck takes a lot of time.
David: Yeah, that's why I say it's really for test restore,
right?
So you're testing things and it's tricky, right?
Okay, I guess you can say amcheck takes 2 days, so we're going
to do this once a week.
And then we'll do more recovery testing, but only run amcheck
once a week.
I think that's a perfectly valid way to go about it, to think
about it.
Absolutely.
Nikolay: I'm also very curious, did you think about some more observability
capabilities in pgBackRest to understand that.
My final goal is, for example, I have 100 clusters.
I just want to know actual RPO, RTO all the time, measured somehow,
during those restore tests, during the backup process itself.
And just to have some high-level view, this is what's happening.
That's it.
Now you need to build a lot to have that.
David: RPO you can measure using the information that pgBackRest
provides in the Intel command.
Because it'll tell you what the most recently, how up to date
you are on your WAL segments, right?
So that's going to give you your RPO.
So I'm 1 segment behind, 10 segments behind, 5 segments behind.
So at least you know what your recovery point is based on the
information that you get.
RTO is a much trickier scenario though.
Nikolay: RTO is also tricky because you think like technical guy
and I'm also technical guy.
We think about bytes.
Business guys think about seconds, hours of data loss.
How many hours of data were lost?
And this you need to translate somehow.
David: It's absolutely fair and you're not going to be able
to get that timing exactly from just you could use for instance
our repo-ls command to go and grab information about the WAL
and find out how much WAL you're generating per 2nd.
It's not going to give you exactly the time, but it's going to
be awfully close.
So you could estimate, you're like, OK, we're generating 5 WAL
per minute.
We're 2 WAL behind.
And therefore, our recovery point is this many bytes behind and
this many seconds behind.
So you would actually be able to estimate that reasonably well
using tools just that are available on pgBackRest.
Although actually estimating the time of the WAL, you would
have to roll your own for that because that would have to be
something you do for repo.
So the nice thing about repo-ls is it's a command we give you
and it works on any repository whether you're on S3, GCS, SFTP,
POSIX.
So you can use that tool to get information about the repository
that's not included in our JSON info and it will work in any
environment, on any repository, on any storage.
So you don't have to muck about with AWS CLI over here and the
Azure CLI over here and whatever the GCS equivalent is and et
cetera, et cetera.
And then, okay, so you could sort that out, I think, with the
tools that are available with pgBackRest, but RTO is still a
thing that needs to be tested.
We can't tell you RTO at backup time.
And since Postgres is famously slow for recovery, Maybe not slow,
but certainly things don't get written as fast as they get written
on the primary.
That much we can agree on.
Recovery actually, it goes pretty well.
And Postgres has gotten pretty good at things.
pgBackRest prefetches, WAL segments, all this combined actually
gives you a pretty good, pretty good performance overall.
But again, you're going to have to test it to know how long it
takes you to do recovery.
Fun little example, there have been, and this hasn't happened
1 time, but the 1st time it happened was in early days of
pgBackRest, because our thing has always been high volume performance,
et cetera.
So there are a number of people over the years who have used
pgBackRest because they can't use replication.
Because replication simply will not keep up with their load ever.
So what they do is they basically continuously take and restore
backups.
And that way, they can measure the RTO pretty well, actually,
because they're constantly doing these recoveries.
And they get to a point where they...
And then we'll see how long it takes to get to the point where
they were when they did the restore, right?
Because they can never actually catch up, but they know how long
it's going to take.
If the primary fails, how long will it be until the standby is
up to date using WAL from the archive?
A downtime will be this.
And they know it exactly because they do it every single day.
And this is a use case that I've seen multiple times now.
People have brought it up with me.
It's a really interesting use case.
Nikolay: So they are bumping into this 100% single CPU situation
problem of startup process on replicas.
So that means also they live without replicas.
With these replicas, they cannot use them because they are lagging
basically, right?
David: Yeah, they're pretty far behind.
So they really are just there to...
I mean, obviously you can make up scenarios where a replica like
this could be used.
Let's say you're doing reporting and you know your replica lag
is 6 hours, then you know by 8 AM you can start running reports,
that kind of thing.
So you can game it.
But you're right, it severely reduces the usability of the replica
in that scenario.
But they just need something because they can't start from...
They're just trying to minimize the time it takes for them to
get to running again.
Because if they start from a restore of their 50T backup, that's
going to be however long that takes.
pgBackRest is pretty fast, but 50 terabytes is 50 terabytes.
And then they start recovery.
Nikolay: 1 hour.
I showed an article 37 terabytes per hour.
We can do it 1 hour, but it's local NVMe to local NVMe.
It's very different.
David: Exactly.
As usual, the backup is, you know, I've gotten a lot of flack
over time about using, we do a lot of checksumming and we use
pretty heavy duty checksums and people are like oh those checksums
are so expensive how can you stand how expensive they are?
They're not expensive once you actually start pushing stuff.
If you're writing to your local SSD, then yeah, the checksum
overhead is 5% in pgBackRest.
I've measured it.
We have tests for this.
So yeah, it looks like a lot.
But then you actually start pushing stuff out to S3 and poof!
It just disappears.
You don't even see it anymore.
The trick is getting this kind of volume in a realistic environment.
People don't store their backups locally.
At least they shouldn't be.
That's the message we try to get.
Nikolay: I should repeat the tests with S3.
I'm very curious about the level of parallelization we can get.
Of course, we need the machine itself should be where we recover.
It should have local NVMes.
I'm very curious.
David: We have a...
Yeah, sorry.
Let me just address that really quickly.
We do have a good, interesting optimization coming, hopefully
in the next release, if I can get it reviewed, by July 10th,
that's our feature freeze.
It basically does, basically it's adding prefetch for object
stores and something I call I'm sure other people do this but
I haven't really figured out what it should be called so I've
been calling it readover hoping someone will come up with a better
name.
But a lot of times we're reading through say a bundle and we
need n number of files or n-m number of files and so we're basically
starting a bunch of new reads so we'll grab these 3 files and
then there's a file we're skipping so we'll start a new read
grab 4 files and then we have to skip 2 files so we'll start
a new read and then do So what we're doing is actually we have
this thing now called read over which is also under review and
it will skip over a configurable block of bytes.
So if we're reading through the file and suddenly And it's configured
by default to 64K.
So if we have this 1 small file that's breaking up the read that
we're doing, we'll just read it and throw it away and then continue.
And I picked 64 bytes because based on my testing, that was,
It's cheaper to read 64 bytes pretty much in all scenarios, no
matter what your storage type is, whether it's reduced redundancy
or call it the longer term storages and stuff, I can't think
right now.
And so 64k is basically always a win.
Even if you're paying for egress, which you will be in some cases,
you're still going to pay less if you just read the 64 bytes.
And from a latency standpoint, it's always a win.
Because starting a new read is just expensive in terms of time.
And there's actually a cost to starting a read as well.
Once you've actually got a file open you're reading and normal
S3 storage You're not paying for that egress But if you actually
start a new request you do pay for that new request Anyway, some
interesting features that would make the tests that you were
talking just talking about Very interesting to run Would be a
very different scenario than pgBackRest today.
Nikolay: Longer storm to storage, glacier, right?
David: Yeah, no, I'm not thinking of glacier.
I'm thinking of the 1 in between.
I actually set all my buckets to transition to it automatically
after 30 days.
So just transition to that.
You can still read it instantly, but it costs.
If you go and read it, it costs.
And there was a, people have been wanting to add this as a feature
where you can go into the storage class immediately for all backups
and WAL.
Nikolay: Yeah.
Standard IA.
David: IA, thank you.
Infrequent access, that's the 1.
And it's actually a bad idea to write IA initially, because in
a healthy repo, you're actually reading the repo.
And as soon as you read 1 backup out of that repo, you've destroyed
all the advantages of IA.
It's all gone.
The idea of IA is I've got an older backup from 6 months ago
that I'm holding for compliance and now suddenly I need to recover
it.
Great.
You do that once every 2 years.
Now you're using IA correctly, but if you're writing all your
backups into IA initially, you'll just end up spending more money.
The only scenario where it works out is where you write and never
ever do any recovery or any WAL recovery ever, which is just
not a use case to me.
Back to these
Nikolay: cases you are familiar with, I'm very curious how much
of all data was written per 2nd on those cases where it was like
the problem like they cannot afford replicas so if near 0 lag,
right?
David: Yeah.
Nikolay: Some hundreds of megabytes per 2nd, I suspect.
David: It can be.
It really depends on what you're doing.
Let's say you are, the simplest possible case is you're replaying
sequential writes on an unindexed table.
Right?
That's your simplest possible case.
That'll run like gangbusters and that'll definitely go into hundreds
of megabytes or gigabytes.
Maybe not per 2nd, but hundreds of megabytes per 2nd I think
becomes pretty reasonable at that point.
There's still some, still quite a bit of exchange to get the
WAL segments, move them, rename them, do this kind of stuff,
but it's quite fast.
But as soon as you start doing interesting things like say updating
indexes, then your write volume is going to go down significantly.
Then it's all going to be I/O latency.
So the CPU will drop below 100, you'll go into I/O wait, and you're
going to be looking at latency.
So if your CPU actually is 100, you're probably doing really
simple writes, generally speaking.
And you can maintain a really high throughput if you're doing
that.
If your CPU drops, if you've got complicated index writes and
stuff like that, your CPU will generally not be 100 anymore.
And you'll be into I/O wait instead.
So I played around with this in a bunch of different scenarios,
trying to figure out ways to help Postgres and maybe gain Postgres.
Certain scenarios are just slow.
Nikolay: It makes total sense because if it's like simple, it's
I/O bound and if on primary, we have hundreds of cores doing this
work, but on the back we have only 1 trying to catch up to do
the same work, basically, logically.
It's terrible.
Yeah, that makes sense.
Simple work.
Yeah.
David: And unfortunately, all the cool work that Andres et
al have been doing on async I/O, Tomas, and et cetera, it's
not going to help that scenario very much in on the standby.
It's great on the primary, but it just makes a situation where
the primary can write even more data than the standby can ever
possibly keep up with.
Again, because single threaded, so those async operations are
only going to buy you so much.
Direct I/O and stuff obviously will help.
So there are improvements being made in the single threaded recovery
So don't make no mistake I'm not saying there aren't because
there definitely are but they're not keeping pace with the improvements
that are being made on the primary side Which are can be an order
of magnitude greater than what's happening on the standby.
Nikolay: Yeah, exactly.
I used, I think, Intel machines in this experiment where I achieved
30-something terabytes per hour with pgBackRest, and I compared
it to pg_basebackup, and I expected 200-300 megabytes per 2nd
as usual.
But since it was Postgres 18, I saw 1 gigabyte per 2nd.
I was super surprised.
David: It's actually gotten better.
I remember the article.
Yeah.
And that's jibes with what I've been seeing in terms of overall
I/O improvement, I/O improvements in Postgres.
So they're impacting everything, make no mistake, but it's obviously
that recovery bottleneck is still there.
And there's not much that pgBackRest can do about that, except
just make sure that Postgres always has the WAL.
As soon as Postgres requests a WAL segment, we've got it right
there waiting for it.
And we got recommendations on how to set up your storage so that
we can do a move into the pg_wal directory so it's as fast as
it can possibly be.
We're not doing a copy.
We don't fsync things, we don't et cetera, et cetera.
So we're trying to run everything as fast as possible, but there
are limits.
Nikolay: This makes total sense and it's great insight.
Like improving things around, you might highlight some difficult
problems and they might become even more acute, right?
So Everything improved so you have better performance.
You much faster you can achieve the problem with lagging replication
Yeah,
David: it was definitely a time before pgBackRest and
other parallel WAL implementations that were getting the WAL
was the bottleneck and Postgres kind of merrily sailed along
and did its thing and it was just WAL that was the issue.
Now it's usually Postgres.
Another place this expresses is, if you're doing, like people
have noticed and started pointing out to me, although I already
knew it, is that if you're doing log shipping, you can do recovery
much faster than if you are doing replication.
Because log shipping compressed segments, which are prefetched
and decompressed asynchronously and then moved into the pg_wal
directory is quite a bit faster than streaming uncompressed WAL
segments over the network from the primary basically every day
of the week if you have high volume.
So that really brings into question the idea of I brought up
a standby and I want to do recovery.
How do I do the best things where I'm going to go and log ship
as long as I can because that's faster and also puts less load
on the primary and then only switch over to getting data from
the primary when I have to.
At 1 point you switch back and stuff like that.
Postgres doesn't currently manage that very well and the rules
are a little bit arcane.
So some archivers, we haven't really done this in pgBackRest
yet, but there's certain things you can do to basically, you
can artificially give an error to Postgres to make it switch
back over to replication.
Do you get what I'm saying?
So let's say we get to pretty much the end.
Nikolay: You're moving very fast.
So you're talking about replica where both restore_command and
primary_conninfo are configured, right?
Yes.
And then you say Postgres has a very strict precedence rule.
I think it searches local pg_wal directory 1st, then it uses
streaming replication and only then it uses restore_command,
if I'm not mistaken.
David: I believe that's the way it works, yeah.
Nikolay: And then you say you have a way to fool Postgres to achieve
what you want, and what you want is to get WALs from archive
because they are compressed and you can parallelize it at restore
command level and only switch to primary_conninfo when to streaming
replication in some cases.
Right?
And then you have to, with some error, this I don't get already.
I can imagine throwing an error from a restore_command, I cannot
imagine anything at primary_conninfo, that's a problem.
David: The situation is, let's say you are, you've been doing
log shipping because that's the fastest thing to do, so you're
getting WAL segments.
Now, Postgres, if it's basically decided on log shipping, isn't
going to switch away from that until something happens.
The idea is, like the actual, the WAL get routine, when it knows
it's at the end of what it has.
So let's say Postgres, so you get to the end and now the next
WAL segment arrives 2 minutes later.
Now you've essentially built in 2 minutes of lag because Postgres
will patiently wait 2 minutes for that next WAL segment to arrive
as long as it doesn't get an error.
So what you, in this situation, what you can do is actually inject
an error into the archive-get to say, I know I'm at the end of
the archive stream in the repo because I can see it.
I'll throw an error to force Postgres back to replication.
Nikolay: Yeah, streaming replication.
I think I got it wrong.
I think restore_command has precedence over streaming replication.
David: I think it has.
Nikolay: I always forget that.
But if the recipe you described implies that.
But it's an interesting recipe.
David: And a lot of times, even if the streaming replication
were prioritized, let's say you recover, restored a backup and
you need to get WAL from 3 days ago to start doing recovery.
There's a very good chance that's not going to be on the primary.
So you're going to, you're going to go over to log shipping at
that point anyway.
But the other thing you would want to do is, let's say you start
lagging too badly on the primary, ideally Postgres would go back
to log shipping because it's quite a lot faster.
Historically that wasn't true, but with modern archivers, like
something you'd find in pgBackRest or WAL-G, we can do that
a lot faster than replication.
So in theory, if you're lagging behind enough on replication,
you switch back to log shipping, catch up, then go back to replication
in order to have as little lag as possible.
So the question is, how do you coordinate?
On the pgBackRest side, we can game it to force pgBackRest
to switch to streaming rep, but we can't really get it to go
back to log shipping.
Now, these are discussions that have been had.
I don't know if we've...
I can't remember if we've had them on hackers, but it's something
we've certainly talked about at conferences.
Nikolay: Yeah, that's an interesting idea.
It would be great to have more control here and more flexible
configuration.
David: Some kind of...
Nikolay: Sorry.
Go on.
We have huge delay because we are on very different parts of
the world, right?
3 very different parts of the world.
David: We are very geographically distributed, that's for sure.
So yeah, so on the 1 side, the archiver would have to tell Postgres
that it's caught up.
So rather than throwing a nasty error, we could say, we could
have a code to return value 2 or something to say, hey, I'm at
the end of my, of the WAL in the repository.
Nikolay: Or just signal the lag.
Signal the lag.
This is my lag, That's it.
And then the mechanism will decide which precedence, adjust precedence
based on knowledge about lags.
David: Or that.
And honestly, if you're going to do that, the way to do that
would be inside the archive_library interface.
Nikolay: And
David: you're familiar with the archive_library, right?
Yes.
We don't have 1 for pgBackRest yet because it's not needed for
performance in pgBackRest and it doesn't really provide any
other benefits.
So we haven't really done it yet, although now that several versions
of Postgres support it, I'm looking at probably adding that this
year or next year at the latest.
Because then maybe we could push some of these ideas into Postgres
to saying, hey, let's flip back and forth between archive-get
and streaming replication based on what's...
And passing stats back and forth inside the archive_library would
be easy.
You don't need any fancy return values or JSON or something.
It would just be an API call that you would use within Postgres.
Nikolay: Postgres 15, when it was introduced.
I wanted to clarify very clearly that we talk about fetching
WALs and streaming, but after that Postgres needs to replay
and then we have this startup process, 100% CPU.
This cannot be solved anyhow, right?
So if you manage to...
Usually problem is there.
This is what I'm trying to say.
It depends on the system, I think, but even with a simple, regular
streaming replication, which is no compression, nothing, I usually
don't see any problems with receiving WAL and just putting it
to file with WAL receiver, right?
The problem is usually on the startup process.
This is my observations on multiple systems.
David: And that's true.
A lot of it depends on your network.
If you're running things in multiple availability zones and stuff
that, or across cloud providers or whatever, that situation can
definitely change.
Nikolay: Yeah.
But certainly if you've got,
David: if you've got primary and standby locally, but the network
speeds these days, you're right.
Streaming replication should not be a problem in that environment.
In this day and age.
Nikolay: My observations are with good network and single region.
I agree with you if you introduce network complexity, yes.
Michael: That sounds all very clever and like obviously needed
at super high scale.
We've talked to a lot of people and it seems like there's quite
a few projects in the space of sharding these days and it feels
like people that go the sharded route sidestep this problem because
each of the shards just has lower volume, like by definition.
So I'm wondering if it's just like an orthogonal thing to
pgBackRest, like sharding can happen and each 1 can be backed up.
Does it affect you in any way or are you thinking along those
lines at all?
David: From a pgBackRest standpoint, no, because it's really
from our...
So the things in this arena that I know support pgBackRest are
like Greenplum and Multigres.
And in both those situations, it's a very straightforward
pgBackRest scenario.
We're just doing recovery, they give us a point in time to recover
to, and that's up for them to figure out how to get the shards
back in sync, and then we just give them what they ask for.
Multigres looks enough like Postgres that pgBackRest just is
cool with it.
On the Greenplum side, there's a company that has created a fork
that specifically supports Greenplum that'll read its control
files and do that and we've never brought that into core mostly
because I don't want to get into Greenplum and testing Greenplum
and also annoyingly the oldest version of Greenplum is still
based on Postgres 9.4, which we expired a couple of years ago.
So it's a little bit painful to have.
So that company is actually basically supporting now, not only
do they have their fork, but they're also supporting versions
of Postgres that we don't support anymore so that they can still
support Greenplum 6, which is based on 9.4.
And maybe that's something we could bring into Core Postgres,
but it's a resource issue.
We just simply don't have the resources for it and it's just
easier to focus on open source Postgres.
When you drive people towards the idea that if you make your
stuff look like Postgres it'll just work, if you muck about with
pg_control, like the parts of...
So we even have a feature where you can add stuff to pg_control
as long as you add it to the end.
And we'll automatically figure out, so you tell us your Postgres
16, we'll automatically figure out where the checksum lives,
based on the size of your pg_control and all that kind of stuff,
and work all that out.
But The contract is that the part of pg_control that was part
of that version of Postgres must be the same.
Because we read all over pg_control.
It's extremely important for our operations, so we can't just,
you can't move things around, you're just allowed to add to the
end.
So we do make concessions to forks.
Things like it works with EDB, their enterprise server, and other
things that have new control versions and stuff like that.
As long as you kind of know what you're doing.
We call it a maintainer feature.
So the idea is if you shouldn't be mucking around with this feature,
if you are not a maintainer of a fork.
And deploying pgBackRest for that particular fork or documenting
how pgBackRest should be used with that fork or something.
If you have to use these options on a regular cluster, then you've
probably done something wrong.
You shouldn't be doing that.
But obviously I can't control what people do.
That's part of the fun of it, right?
Michael: Yeah.
Talking of maintenance, is it a good time to transition into
how you have been maintaining it over the years, kind of what
the situation was, and it's changed recently.
So I'm wondering if you wanted to share a bit of that.
David: Yeah, maintenance has always been a big part of pgBackRest.
1 thing is we have a very comprehensive test suite for a project
of this size.
We test on 5 different architectures.
We test on a variety of distros.
We have 100% unit test coverage.
We have integration tests.
We have, we test our doc code, our test code, or we test everything.
And just keeping that test suite up to date is a bit of a challenge.
It's not the core code.
The core code doesn't really, they don't, the C code doesn't
break because rel 10 comes out.
What breaks is our tests and the documentation and all these
sorts of things.
So it's a non-trivial task just to keep all that going.
And then you have things like say, Cirrus CI just going away
on a month's notice.
So we need to migrate off of Cirrus CI.
Luckily, we're already on GitHub Actions, so we're able to do
that fairly easily.
When I say maintenance, I also include bug fixes that come in,
So we get a pretty regular stream of bug reports.
Some are bugs or some are not.
Most of them these days tend to be pretty weird edge cases.
Enough people are using pgBackRest that the Really obvious
bugs get caught pretty quickly.
If we release something and there's bugs in it, I get reports
the next day, almost.
Bug fixes or maintenance, the documentation is maintenance, interacting
with the community and answering questions and feature requests
and other things like that's all maintenance.
So that's actually a pretty big part of the job.
And after I left, after I did not transition to Snowflake, so
Snowflake bought Crunchy Data, I did not transition.
I wasn't happy with their terms, so I decided just to take some
time and travel.
I kept maintaining pgBackRest, but at that point I was really
just maintaining it.
Which is fine, but it really got to the point of thinking about
really restarting development on pgBackRest again.
I was like, I think I might need a job.
And so I started trying to get, I actually put sponsorship links
and I've been including sponsorship with every release since
last summer.
It was around for basically a whole year, not getting a lot of
traction of course.
Then I started spending more time on it.
Then I got to the point where I realized maybe this whole sponsorship
thing isn't gonna work out.
And that got us to the recent crisis where I announced that I
wasn't gonna work on pgBackRest anymore.
And suddenly I got sponsorship.
And when I say suddenly, it really was suddenly.
Within 3 weeks, pretty much everything was worked out, and I
was able to make an announcement that the project was going to
continue to be maintained, and etc.
But not just maintained.
The key is adding new features.
Right now, I've been doing, on pgaudit and pgBackRest, I've been
doing a lot of cleanup work because they've been sitting for
a while, especially pgaudit wasn't really getting a whole lot
of love.
So I've been working on that, but I'll be done with that at the
end of this week, basically.
And then it's time just to dive into big new features again,
which I'm really excited about.
I've got a lot of ideas.
The sponsors have a lot of ideas, as you can imagine.
Nikolay: The
David: great thing is the ideas that the sponsors have are
directly aligned with big ticket items that have been on our
list for a while.
They're not asking for outlandish stuff.
They're asking for repo-to-repo backup, for instance, is a big
ask.
And that's been on the list for a long time, because that's a
pretty important thing to be able to do.
Streaming, doing streaming while replication.
So you can have RPO 0 if you want, without having a standby,
so the idea is you have RPO 0 without a standby.
Nikolay: pg_receivewal, right?
David: Yeah, although we would work directly at the protocol
level.
And in fact, we might...
I'm actually thinking, given that we have this archive_library
thing, and given that pgBackRest can just live in Postgres,
I'm not even sure if we need to deal with the protocol level.
We could just sit there as a companion to Postgres and stream
WAL out.
Nikolay: Isn't it expensive to sit as Postgres?
Like, I don't get it.
What exactly is the idea?
David: So basically, instead of a replication slot, we would
just be keeping up with the current write WAL location.
Where are we currently writing in WAL?
That's the point that we should currently be tarping.
And although we would still need a replica, if we really wanted
to say a synchronous replication, RPO 0 stuff, we'd probably
still need a replication slot for that.
The other thought we had is that we could actually game, we could
create a replication slot but not actually use it and just update
the row.
So basically do our own background archiving and then update
the row in Postgres to our current position, what we've actually
pushed out to storage.
And if I do this, I want to do it right.
In a lot of cases, the things that are doing the WAL receiver,
they're not actually, let's say your eventual destination is
S3, they're not actually writing packets off to S3.
They're storing the stuff locally on whatever host the WAL receiver
is running on and then when they get a full WAL segment, they're
pushing that to S3.
But what I would want to do here is actually have a couple of
parameters that decide what your acceptable lag is.
Let's say you're willing to have 3 seconds of lag, or so many
bytes.
And we can measure that.
In this situation, we'd be able to measure either 1 of those
very easily.
So we would say, OK, if we get to that point where we're going
to hit that lag, we'll actually write a chunk of WAL out to
S3 and store it.
And the WAL archive-get routine would actually be written so
that it would be able to...
Later these chunks would be assembled, of course, reassembled
into a WAL segment to keep things kind of tidy.
But the WAL get routine would actually be able to understand
if you get to the end of the WAL stream and all you've got is
these chunks, we'll be able to read them out and reconstruct
them and send them Postgres.
So you actually have, because I feel like the implementation
is out there right now, yeah, you're getting the WAL off of
the primary, which is good.
Don't get me wrong, that's a pretty important thing, but I want
to get the WAL all the way to the repository, all the way to
the final storage.
So if everything goes away, and all you've got is the S3 bucket,
then you still have the whatever you set that to, sorry, then
you would have that, unless we fall behind or other things happen.
Obviously, there's scenarios.
But I think it could be a lot more efficient because then we
could figure out, hey, they're generating a WAL at a ridiculous
rate.
We actually need to switch over to just compressing and pushing
whole WAL segments and then come back to chunking up portions
of this WAL segment.
And with the replication protocol you don't really have that
option because you've just got this fire hose that's sending
you data and what you really want to do is say, no, this is too
much.
Also, it's a lot slower than just compressing and shipping.
So, no, we're going to go back to doing whole segments, and when
we get caught up on the whole segments, we'll start doing the
most recent segment in chunks, back and forth.
I think the best way to do that, that I'm thinking, I'm always
an outside the box kind of guy, is actually in an archive_library
sitting next to Postgres and just asking Postgres, where are
you, where are you, you know, kind of thing.
And we can actually monitor, you know, check stuff on disk, so
we'd be able to see, oh, we've just gotten a new WAL segment,
so everything in the old WAL segment is fair game, etc., etc.
So there's plenty of heuristics we can use to improve this.
So that's the way I'm leaning right now, and that gives us the
archive_library and this WAL receiver RPO feature at the same
time.
Nikolay: RPO control.
This is better RPO control, basically.
David: Yeah, better.
Sorry, I meant to say RPO 0.
So if we could actually gain this so that we can update the node doing streaming
replication, but update the row in Postgres for replication,
then we could actually do synchronous replication this way.
And we could basically do synchronous replication to S3.
If someone wanted such a thing, yikes, It would be slow.
Nikolay: I'm very curious.
What is your opinion about, Barman was the 1st tool which used
only streaming replication, right?
I remember this.
So what's, what is your opinion on using streaming replication
for backups?
And this new CYBERTEC tool released last week, pg_hardstorage,
I quickly checked they don't use pg_receivewal as well, they
just implement protocol, they work with protocol and they pretend
to be replicable.
Yeah, That's
David: what most people have done.
I think Barman might still be using pg_receivewal, but most other
implementations have gone straight to the protocol.
The protocol's not that complicated, to be honest.
Nikolay: Like all uppercase.
David: If you've got libpq, Once you've got that set up, especially
if you're using libpq directly, it's actually fairly trivial.
The replication protocol is very simple, which it should be.
There's nothing wrong with simplicity.
I'm all about it.
I think ultimately it's a big bottleneck making backups through
the streaming replication.
So for very large databases, it's just going to be a pretty big
bottleneck.
Obviously someday we can add parallelism, do all these things
and etc.
There's a lot of...
Well, they got compression.
So that's good.
Although I think...
Is the compression server side or is it client side?
I think it might still be client side.
No, it's server
Nikolay: side Okay,
David: I can't remember.
Anyway, that's actually a pretty solvable problem But You've
got basically a scale of now you need to back up something really
large and you're pushing everything through this little pipe.
Nikolay: pg_basebackup has compression since Postgres 15 and you
can...
Yeah.
Right?
Or
David: no.
It does.
I just can't remember which side it happens on, whether it happens
on the server side or the client
Nikolay: side.
David: I think it is.
Nikolay: Compress server-gzip option.
David: I believe it is, yeah.
Nikolay: But it's still single threaded.
David: That's 1 bottleneck taken care of because you're not
pushing uncompressed data over the network, which you definitely
don't want to do.
But it's still single threaded.
And that's a huge limitation on the backup side, obviously.
And then on the recovery side, we still have this problem of
all the data needs to be recovered before anything can be done
if you're doing block incremental.
If you're not doing page incremental, then you can make that
a bit more efficient.
You can stream the tar file from S3 and decompress it as you
go and do various things.
But if you're doing page incremental, which I think pretty much
everyone wants to do, that becomes a huge bottleneck on the restore
side.
Nikolay: I'm sorry.
We never had so compressed.
I know we already are way over time, but it's so compressed.
You compress so much knowledge and I like it so much, but can
you explain, like elaborate page level versus block level and
why you think it's important?
David: That's my confusion because what was introduced in Postgres 17,
the page level incremental with the WAL summarized and everything,
in pgBackRest we call that block level incremental.
And the reason why we call it block retal is we don't always
operate at the page, at page size.
We operate at block size.
And then we actually combine blocks into what we call a super
block for compression.
So let's say I want to get 1 block out, I might have to retrieve
3 compressed blocks or 4 compressed blocks, depending on the
super block size, to actually get that.
But the important thing is we can, let's say we've got a 1 gigabyte
file and we need to recover 1 block, we can go recover 3 blocks
to do that instead of 1000 blocks, however many blocks are in
a 1 gig file.
I think it's usually 1, 200 at our largest block size.
So we can go recover 3 blocks instead of 1, 200 blocks, even
though we have to recover 3 blocks just to write 1 block.
So the main thing is we're able to go, let's say you've got a
standby or a primary that's failed for some reason, you don't
know why.
So you're gonna do a delta restore.
So in a delta restore, we go and we look at every file And for
the block level stuff, we chop it up into pieces, and then we
do a hash on each piece, and then we do a hash for the entire
file.
If the whole file hash matches what's in the backup manifest,
then we're done.
Move on.
Next file.
If the whole file manifest doesn't match, then we can go recover
what we call the block map, which is a map that tells us where
all the blocks live in all the backups.
You might have 10 incremental backups since your last full, and
now we need to go figure out where are the blocks, where are
the blocks that we need located and go grab those blocks.
And in the other implementations that have been done, you can't
do that.
You basically have to go recover everything and some of them
will have good things like WAL-G will, as it's reading it,
it'll know that it doesn't need the block and it'll just throw
it away.
That kind of read over thing I was talking about before.
So it'll just throw away the blocks it doesn't need, but it's
still reading them out sequentially.
We're actually able to go random access and pull out just the
blocks you need.
And It's extremely powerful because we have that delta restore
concept where we can actually just recover part of a cluster.
And that's where the power of those checksums that we spent in
theory, 5% of our time, but actually it's really much less than
1%, that's where that all comes in on the pg_basebackup side
right now.
You basically have to recover all the full and all the incrementals,
decompress everything all at the same time, present that to pg_basebackup
that will then rewrite that into another directory, sorry, pg_combinebackup
which will then rewrite that into another directory.
So at a minimum to do recovery, you're looking at double your
database size and actually more.
And you don't really know what that more is, unless you've stored
it.
It's up to you to actually store that information to find out
what is the total number of bytes that I'm going to need to even
pull down the data.
On the pgBackRest side, when we're doing a recovery, nothing
ever hits disk except in PGDATA.
So we're just reconstructing those files inside PGDATA.
There's no spooling, there's no spooling during backup, there's
no spooling during recovery, there's no spooling during archive
push, there is spooling during archive-get because we actually
prefetch WAL files and we store them so that we can hand them
over to Postgres.
So we, we spool on that side, but for the most part, we're 0
disk operation.
And to be really efficient, that's the way you need to be looking.
And right now the design in pg_basebackup is exactly not that.
A lot of disk I/O.
They've done some tricks with, if you're on ZFS or other file
systems that can do copy-on-write tricks and do other fun things
to try to minimize the number of writes that you're going to
do.
I don't know if it actually minimizes the space that you need.
On some file systems that might be true.
Nikolay: Thank you for explanation, it was great.
David: The whole idea was the feasibility of pg_basebackup
using the streaming replication as a backup tool.
And I think, yeah, you can do it, but when you're working at
scale, it starts to introduce a lot of limitations.
And from day 1, Gabriele Bartolini wrote a pretty good thing
after I announced that I wasn't working on pgBackRest about
his and my fundamental disagreement about how this tool should
work.
And his idea is that the copy thing should be owned by Postgres.
So Postgres should do all the file copying.
And my idea was that no, the copy should be built into the tool.
Because then we can do, the sky's the limit.
We can do anything we want.
And I think the performance that you can get from pgBackRest
speaks to the value of having that complexity.
And it is, it's a lot of complexity, don't get me wrong, to have
all that built into pgBackRest, but the payoff is performance.
And also reliability.
We have checksums that we can check on everything.
We know when the data that's coming back is good.
That's 1 thing that kind of peeves me is that if you, like let's
say you were doing pg_combinebackup it doesn't actually verify
that the data is correct.
So if you want to verify, you actually have to run pg_verifybackup
as a separate step before you run pg_combinebackup.
And in my mind, those should always be combined into a single
operation.
You're looking at the data, you're verifying it, you're writing
it out.
And the cost for that, again, oh, checksums are expensive.
Yeah, but if you're streaming the data directly from S3 and checksumming
it and writing it out as it comes in, the checksumming cost disappears.
You don't see it anymore.
If you copy all the data from S3 locally, decompress it, and
then start running checksums on it, it looks really painful because
you weren't able to, the idea for me is let's gain all the latency
that we can, the storage latency that we can.
So while we're waiting for the storage to give us something,
we'll send it the next request to S3 and while it's thinking,
sending this data, we check some.
We're done with that block, the next block is ready for us.
We pull that in, we start checksumming, we've asynchronously
set the next request to S3.
So we've already got more data coming.
In the next version of Postgres, if everything goes well, we'll
also have prefetch.
So we won't be asynchronously fetching 1 block, we'll be asynchronously
fetching by default 4.
And just pulling that data in as fast as we can, but even as
fast as you can get stuff over the network, that cost of checksumming
disappears, But it gives you power.
And pgBackRest is the only thing that has something equivalent
to delta restore.
And all of that is powered by the checksums.
That's where all that comes from.
And then we have the block level delta restore as well, which
is powered by the block level hashes, which are actually XXHash,
not SHA-1.
Because the blocks are a maximum of 88k.
Although you configure that, you can also configure how much
of the XXHash you're going to use for a particular block size.
But we did a lot of analysis on this and looking at file systems
and how many bits they were using to protect blah blah blah etc.
And basically came up with checksum sizes that were appropriate
for each block size that we support.
The whole the way we do block incremental is actually a pretty
complicated topic.
I did a talk on it once and it was a complete disaster because
it was just too much.
Nikolay: You go too deep.
Truncating 32-bits you said, right?
Or truncating hashes you said, right?
David: Oh yeah, so the 32-bits.
So what we do is we generate a 128-bit hash and then we use however
many bits out of that, well, bytes actually, that we want.
The minimum number of bytes we'd use for an 8k block is 6 bytes,
which is actually a lot.
That's 50% more than would be used for 4k pages on ZFS, for instance,
or Btrfs.
So we go a little bit crazy on the checksums because we just
we want everything to be right and then when you're doing block
increment and we actually have 2 levels of checksum so we use
the block checksums to reconstruct the file and Then we also
checksum the entire file and the SHA-1 hash of the entire file
plus all the block checksums have to match.
And the chances of that getting a collision, you know, like a
piece of data that actually satisfies the conditions, all the
block checksums matched and the SHA-1 checksum of the whole file
matches.
We're talking heat death of the universe probability here.
Right?
It's just simply not going to happen.
You're going to have disk corruption 1000000000 times before
anything like this ever fails, as far as my math works out.
Michael: Well, it sounds reasonable.
Nikolay: It's a lot of interesting topics, I feel.
And I like the direction you are thinking, especially RPO control.
This is super cool thing because I see the demand because Postgres
has been used in like critical systems and people just don't
know what's happening there.
Like how, what's happening in the case of disaster, right?
Like it's hard to answer simple questions to business leaders.
David: Yeah.
It's not 1 size fits all.
Right.
I did.
You guys are familiar with ARIN, right?
The internet number registry.
I know the people who run the database over there, and they use
pgBackRest.
I worked with them for years in another company, and they're
all RPO 0 stuff, right?
But their write volume is stupidly low.
So they've got 1 primary, which receives 100 writes an hour on
a really busy day, and then they replicate that, and then you've
got the whole internet reading to figure out what blocks are
allocated to who at any given point, and where they should be
routed, that kind of thing.
So the read volume is pretty big, the write volume is extremely
tiny, so they do synchronous replication because they can't lose
anything and they have low enough write volume they can get away
with it, so they would be a great candidate for a pgBackRest
RPO 0 solution that allows them to synchronously write to S3.
I bet they'd love it.
But for most people, that kind of solution just isn't going to
work.
People ask me how I can work on this software year after year.
And the reason is because the problems are actually really interesting
and challenging and extraordinarily complex in terms of the solutions
that we have to come up with to solve these problems.
It's, it's, pgBackRest is a relatively small project, but it's
dense.
The stuff that we do is...
Nikolay: Let me uncompress a little bit.
You mentioned ARIN, it's American Registry for Internet Numbers,
right?
David: Right.
That's the 1.
Nikolay: So it's basically the important piece of internet, right?
David: Very important piece.
Yeah.
Very important piece.
Nikolay: It's interesting that you Consider like volumes are not
huge and the streaming replication and so on.
It's interesting and challenging.
But I have cases where it's like just not answered.
RPOs are not answered and extremely important cases.
Important companies also, they simply don't know.
So if you build this, I think it will be good.
It will be useful, helpful, and so on.
David: It's a tricky thing though, to some extent, when you
put those tools in people's hands, because of course there's
some level in the hierarchy that's going to say, always RPO 0.
Of course, we have to have synchronous replication.
Nikolay: Why would we ever do that?
We cannot afford data loss.
We cannot afford, we need to use the synchronous replication
and so on.
And also we cannot, we don't have split brains.
You mentioned Gabriele Bartolini, right?
I also have my opinion about CloudNativePG and solutions he
chose in this tool.
Yeah, but like at high level we are great, but if you look inside
you see data loss is possible, split brains are possible, almost
everywhere.
And It's really extremely hard to achieve and I like so much
you focus on these topics like RPO, control, and especially corruption
and checksums everywhere.
It's great.
David: My whole job in life is to protect the data, protect
everyone's data.
And that's what I think about all the time.
Like how can pgBackRest be the most reliable thing?
Even if it's not the fastest, or I think it's generally the fastest,
but if it's not the most feature rich, there are a couple of
features that other programs have that we don't have, although
we're working on that.
But everything we introduce has to be performant and ultimately
absolutely reliable people trust the software to protect their
data.
Now they have an obligation to, they need to, I get people at
conferences that will come to me and say, you saved my job, pgBackRest
saved my job.
And I'm like, I really appreciate you saying that, but actually
you saved your job because you set up backup, you tested restores,
and when the big day happened, you were ready.
Nikolay: Yes, I set up restores.
David: Because they'll tell me, oh yeah, we set up weekly restore
tests like you recommended.
And I'm like, okay, great.
I can recommend these things, but a lot of people don't do it
so if you actually go and do the stuff that the backup people
recommend kudos to you you deserve all the credit for saving
your job not me but don't get me wrong I still like hearing the
stories It's great to know that pgBackRest is useful and valued,
of course.
Nikolay: Untested backups are Schrödinger backups.
They should not be considered proper backups.
Yeah, backups must be tested.
David: I can't remember where I found that, But I put that
on 1 of my very early talks, Schrödinger's Backup.
It wasn't me, it wasn't original to me, but I can't remember
where I saw it.
But as soon as I saw it, I was like, yes.
Nikolay: It's an obvious idea, honestly.
Multiple people might invent it.
Anyway, way over time, I enjoyed so much talking to you.
Final question, we discussed a lot of technical detail, very
advanced, I must say.
I think it's okay if some people don't get everything because
we definitely dived into multiple areas very deep.
We also touched the situation about sponsorship.
We haven't touched the situation about maintainers.
What would help you to have a 2nd big maintainer?
David: A 2nd big maintainer would be good for a couple of reasons.
1, to help me write the big features.
These days, I'm the only 1 writing big features and I'm just
1 person.
And the other thing is review.
So review is a big bottleneck for me.
I can actually produce quite a lot of code, but it's got to be
gone over carefully by somebody.
I also, I'm using LLMs now for review as well.
So I'll ask cloud code review anything I write before I send
it to any person just to catch the really obvious stuff.
And these days the not so obvious stuff is getting pretty good,
But I also need someone to bounce ideas off of.
Another maintainer would be good for that.
Someone who I could reliably chat with and be like, so I was
thinking about X, Y, and Z and I just, you know how it is when
you vocalize something, it becomes, sometimes you don't even
need to get input from them.
Just by explaining the problem to them, you, oh yeah, I know
what I need to do here.
It's so obvious to me now.
So that's why I really need another maintainer.
We're working on that right now.
That should be, I don't wanna make any announcements or say anything
regard to this, but this is something that's actively being worked
on to get another maybe not full-time maintainer, but very active,
very involved maintainer.
Nikolay: That's, that would be great.
Yeah.
David: And to some extent, it's hard to know how big we could
scale this project.
It's an interesting, how many people are interested.
Also how big are the problems we're solving?
Can 2 people handle everything?
Probably.
Especially with tools these days, helping.
We're getting more contributions from the community.
Those are coming in a pretty regular stream now so I don't have
to write everything.
I don't have to find all the bugs, I don't have to fix all the
bugs, I don't have to do little authentication tweaks for S3
or blah blah blah.
Most of that is coming from outside contributors, and I just
review it, and if it's reasonable, commit it.
So we're building community that way as well, but if we can get
1 more person who's really looking at it regularly, that would
be great.
And that would, of course, be in addition to Stefan, with Data
Egret, who actually already spends quite a bit of time on pgBackRest
as well, review and testing, et cetera.
Nikolay: Cool.
Great.
Thank you.
I don't have any more questions.
I have, but I will post them, because they are super technical
again.
Michael: David, thank you so much.
Thanks for joining us, but also thanks for all the maintenance
you've done over the years.
David: Oh, absolutely.
It's definitely been my pleasure.
Michael: Congrats on getting all the sponsors and Thanks to them
as well for keeping it going.
David: Yeah, like kudos to the sponsors.
They have made this possible.
I'm just, I'm back to work.
I'm back to what I love doing and everyone gets to benefit.
So I think this has worked out really well.
So a really cool open source story.
Michael: Yeah, it is absolutely good.
Great open source story.
Nikolay: Thank you.
Have a great week.
David: Yeah, thank you very much for having me.