Re: Bump default wal_level to logical - Mailing list pgsql-hackers

On Mon, Jun 08, 2020 at 11:10:38AM +0200, Magnus Hagander wrote:
> On Mon, Jun 8, 2020 at 8:46 AM Michael Paquier <michael@paquier.xyz> wrote:
> 
> > On Mon, Jun 08, 2020 at 11:59:14AM +0530, Amit Kapila wrote:
> > > I think we should first do performance testing to see what is the
> > > overhead of making this default.  I think pgbench read-write at
> > > various scale factors would be a good starting point.  Also, we should
> > > see how much additional WAL it generates as compared to current
> > > default.
> >
> > +1.  I recall that changing wal_level to logical has been discussed in
> > the past and performance was the actual take to debate on.
> >
> 
> That was at least the discussion (long-going and multi-repeated) before we
> upped it from minimal to replica. There were some pretty extensive
> benchmarking done to prove that the difference was very small, and this was
> weighed against the ability to take basic backups of the system (which
> arguably is more important than being able to do logical replication).

I'd argue this a different direction.

Logical replication has been at fundamental to how a lot of systems
operate since Slony came out for the very good reason that it was far
and away the simplest way to accomplish a bunch of design goals. There
are now, and have been for some years, both free and proprietary
systems whose sole purpose is change data capture. PostgreSQL can play
nicely with those systems with this switch flipped on by default.

Looking into the future of PostgreSQL itself, there are things we've
been unable to do thus far that logical replication makes tractable.
These include:

- Zero downtime version changes
- Substantive changes to our on-disk representations between versions
  because upgrading in place places sharp limits on what we could do.

> I agree that we should consider changing it *if* it does not come
> with a substantial overhead, but that has to be shown.

What overhead would be substantial enough to require more work than
changing the default, and under what circumstances?

I ask this because on a heavily loaded system, the kind where
differences could be practical as opposed to merely statistically
significant, statement logging at even the most basic level is a much
bigger burden than the maxed-out WALs are.  Any overhead those WALs
might impose simply disappears in the noise.  The difference is even
more stark in systems subject to audit.

> Of course, what would be even neater would be if it could be changed
> so you don't have to bounce the server to change the wal_level.
> That's a bigger change though, but perhaps it is now possible once
> we have the "global barriers" in 13?

As much as I would love to have this capability, I was hoping to keep
the scope of this contained.  As pointed out down-thread, there's lots
more to doing this dynamically that just turning up the wal_level.

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate