Re: finding changed blocks using WAL scanning

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Michael Paquier <michael(at)paquier(dot)xyz>
Cc: Bruce Momjian <bruce(at)momjian(dot)us>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: finding changed blocks using WAL scanning
Date: 2019-04-20 04:21:36
Message-ID: CA+TgmobiQzLtELznmuOWzWnxociNKFDB79zuFWdj00nyHXTAsQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Thu, Apr 18, 2019 at 8:38 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> On Thu, Apr 18, 2019 at 03:51:14PM -0400, Robert Haas wrote:
> > I was thinking that a dedicated background worker would be a good
> > option, but Stephen Frost seems concerned (over on the other thread)
> > about how much load that would generate. That never really occurred
> > to me as a serious issue and I suspect for many people it wouldn't be,
> > but there might be some.
>
> WAL segment size can go up to 1GB, and this does not depend on the
> compilation anymore. So scanning a very large segment is not going to
> be free.

The segment size doesn't have much to do with it. If you make
segments bigger, you'll have to scan fewer larger ones; if you make
them smaller, you'll have more smaller ones. The only thing that
really matters is the amount of I/O and CPU required, and that doesn't
change very much as you vary the segment size.

> I think that the performance concerns of Stephen are legit
> as now we have on the WAL partitions sequential read and write
> patterns.

As to that, what I'm proposing here is no different than what we are
already doing with physical and logical replication, except that it's
probably a bit cheaper. Physical replication reads all the WAL and
sends it all out over the network. Logical replication reads all the
WAL, does a bunch of computation, and then sends the results, possibly
filtered, out over the network. This would read the WAL and then
write a relatively small file to your local disk.

I think the impact will be about the same as having one additional
standby, give or take.

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2019-04-20 04:42:38 Re: finding changed blocks using WAL scanning
Previous Message Stephen Frost 2019-04-20 04:19:51 Re: block-level incremental backup