Re: block-level incremental backup

On Tue, Apr 16, 2019 at 5:44 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > > I love the general idea of having additional facilities in core to
> > > support block-level incremental backups. I've long been unhappy that
> > > any such approach ends up being limited to a subset of the files which
> > > need to be included in the backup, meaning the rest of the files have to
> > > be backed up in their entirety. I don't think we have to solve for that
> > > as part of this, but I'd like to see a discussion for how to deal with
> > > the other files which are being backed up to avoid needing to just
> > > wholesale copy them.
> >
> > I assume you are talking about non-heap/index files. Which of those are
> > large enough to benefit from incremental backup?
>
> Based on discussions I had with Andrey, specifically the visibility map
> is an issue for them with WAL-G. I haven't spent a lot of time thinking
> about it, but I can understand how that could be an issue.

If I understand correctly, the VM contains 1 byte per 4 heap pages and
the FSM contains 1 byte per heap page (plus some overhead for higher
levels of the tree). Since the FSM is not WAL-logged, I'm not sure
there's a whole lot we can do to avoid having to back it up, although
maybe there's some clever idea I'm not quite seeing. The VM is
WAL-logged, albeit with some strange warts that I have the honor of
inventing, so there's more possibilities there.

Before worrying about it too much, it would be useful to hear more
about the concerns related to these forks, so that we make sure we're
solving the right problem. It seems difficult for a single relation
to be big enough for these to be much of an issue. For example, on a
1TB relation, we have 2^40 bytes = 2^27 pages = ~2^25 bits of VM fork
= 32MB. Not nothing, but 32MB of useless overhead every time you back
up a 1TB database probably isn't going to break the bank. It might be
more of a concern for users with many small tables. For example, if
somebody has got a million tables with 1 page in each one, they'll
have a million data pages, a million VM pages, and 3 million FSM pages
(unless the new don't-create-the-FSM-for-small-tables stuff in v12
kicks in). I don't know if it's worth going to a lot of trouble to
optimize that case. Creating a million tables with 100 tuples (or
whatever) in each one sounds like terrible database design to me.

> > > I'm quite concerned that trying to graft this on to pg_basebackup
> > > (which, as you note later, is missing an awful lot of what users expect
> > > from a real backup solution already- retention handling, parallel
> > > capabilities, WAL archive management, and many more... but also is just
> > > not nearly as developed a tool as the external solutions) is going to
> > > make things unnecessairly difficult when what we really want here is
> > > better support from core for block-level incremental backup for the
> > > existing external tools to leverage.
> >
> > I think there is some interesting complexity brought up in this thread.
> > Which options are going to minimize storage I/O, network I/O, have only
> > background overhead, allow parallel operation, integrate with
> > pg_basebackup. Eventually we will need to evaluate the incremental
> > backup options against these criteria.
>
> This presumes that we're going to have multiple competeing incremental
> backup options presented, doesn't it? Are you aware of another effort
> going on which aims for inclusion in core? There's been past attempts
> made, but I don't believe there's anyone else currently planning to or
> working on something for inclusion in core.

Yeah, I really hope we don't end up with dueling patches. I want to
come up with an approach that can be widely-endorsed and then have
everybody rowing in the same direction. On the other hand, I do think
that we may support multiple options in certain places which may have
the kinds of trade-offs that Bruce mentions. For instance,
identifying changed blocks by scanning the whole cluster and checking
the LSN of each block has an advantage in that it requires no prior
setup or extra configuration. Like a sequential scan, it always
works, and that is an advantage. Of course, for many people, the
competing advantage of a WAL-scanning approach that can save a lot of
I/O will appear compelling, but maybe not for everyone. I think
there's room for two or three approaches there -- not in the sense of
competing patches, but in the sense of giving users a choice based on
their needs.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company