Re: Getting rid of wal_level=archive and default to hot_standby + wal_senders

On 2015-02-03 11:00:43 -0500, Robert Haas wrote:
> On Tue, Feb 3, 2015 at 7:43 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > I think these days there's no reason for the split between the archive
> > and hot_standby wal levels. The split was made out of volume and
> > stability concerns. I think we can by now be confident about the
> > wal_level = hot_standby changes (note I'm not proposing hot_standby =
> > on).
> >
> > So let's remove the split. It just gives users choice between two options
> > that don't have a meaningful difference.
> >
> > Additionally I think we should change the default for wal_level to
> > hot_standby and max_wal_senders (maybe to 5). That way users can use
> > pg_basebackup and setup streaming standbys without having to restart the
> > primary. I think that'd be a important step in making setup easier.
> >
> > Previously there have been arguments against changing the default of
> > wal_level because it'd mean the regression tests wouldn't exercise
> > minimal anymore. That might be true, but then right now we just don't
> > exercise the more complex levels. If we're really concerned we can just
> > force a different value during the tests, just as we do for prepared
> > xacts.
> >
> > Comments?
> >
> > Additionally, more complex and further into the future, I wonder if we
> > couldn't also get rid of wal_level = logical by automatically switching
> > to it whenever logical slots are active.
>
> I think my vote is to maintain the status quo. What you're basically
> proposing to do is ship the system half-configured for replication,
> and I don't see the point of that.

Not only replication, but also hot backup.

I think we should actually should ship it fully configured for that in
the long term. This is the biggest step towards that.

At the moment it's really hard to get there for a beginner. Usually it
goes like
1) Try to create a base backup. Fails because of max_wal_senders.
2) Try to adjust max_wal_senders, fails because of wal_level. Set to
archive.
3) New base backup is created.
4) Try to start the new base backup with hot_standby enabled, fails
because of wal_level.
5) Enable wal_level=hot_standby, restart master
6) Restart standby. Still fails because it's trying to start from the
checkpoint with wal_level still archive.
7) Give up here. If not earlier.

I think our out of the box experience is ridiculously tuned towards
corner cases that aren't very frequent. And those need to tune further
anyway. Many, many solutions out there are much easier to setup initially.

> The people who want replication still have to do the rest of the setup
> anyway, and the people who don't want replication are losing the
> benefits of wal_level=minimal for no real gain.

How many people, with a big enough data directory that it matters, want
neither base backups nor replication? Except during initial load that's
got to be a minority these days.

> In particular, they are losing the ability to skip WAL-logging when
> bulk-loading a just-created table, which is not a small thing.

Aside from restoring from a dump it's something that's not that easy to
actually benefit from. The restore is a good use case - but for it you
usually want/need to tune lots of other things. Without fsync=off the
increased number of fsyncs and such can actually end up hurting you...

> I'm fairly sure we have customers who benefit significantly from that
> behavior.

Sure, same here. But I'll bet it's a much smaller number than those
that'd have benefited from a simpler setup of backups and
replication. I'm not out to get rid of minimal, I just don't think
there's much point in having it as default these days.

> Crazy ideas: Could we make wal_level something other than
> PGC_POSTMASTER? PGC_SIGHUP would be nice...

Unfortunately not easy, because it effectively takes a checkpoint to
make it active. But yea, I've wondered about it as well.

> Could we, maybe, even make it a derived value rather than one that is
> explicitly configured? Like, if you set max_wal_senders>0, you automatically get
> wal_level=hot_standby?

Our experience with derived gucs isn't that great. Remember the whole
effective_cache_size mess? Maybe we just need to find a better way to
implement that though, instead of avoiding it from here on.

> If you register a logical replication slot, you automatically get
> wal_level=logical?

That actually shouldn't be very hard, if the level is hot_standby
beforehand. At least not on the primary, on the standby it obviously
can't work (not that we support decoding there yet).

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services