Re: Defaults for replication/backup

On Sat, Feb 13, 2016 at 11:31 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> On 2016-02-13 11:10:58 -0500, Tom Lane wrote:
>> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> > The big thing is, IIRC, that we turn off the optimizations in
>> > min_wal_level. *most* people will see no impact of their regular
>> > application runtime from that, but it might definitely have an effect on
>> > data loads and such. For normal runtime, there should be very close to zero
>> > difference, no?
>>
>> I was asking for a demonstration of that, not just handwaving. Even if
>> it was measured years ago, I wouldn't assume the comparison would be
>> the same on current Postgres.
>
> Well, let's look at what the difference between wal_level's are:
> 1) the (currently broken) optimization of not WAL logging COPY et al,
> for newly created relations.
> 2) relation AccessExclusiveLocks are WAL logged on >= hot_standby
> 3) Subtransaction assignment records are generated for >= hot_standby
> after 64 records.
> 4) checkpoints and bgwriter occasionally generate XLOG_RUNNING_XACTS
> records
> 5) btreevacuum() and _bt_getbuf() sometimes do additional WAL logging on
> >= hot_standby
> 6) Once per vacuum we issue a XLOG_HEAP2_CLEANUP_INFO
>
>
> 1) obviously can have performance impact; but only in a relatively
> narrow set of cases. I doubt any of the others really play that major a
> role. But I really think minor efficiency differences are besides the
> point. Making backups and replication easier has a far bigger positive
> impact, for far more users.

I think that there are definitely people for whom #1 is an issue, but
maybe that's not a sufficient reason not to change the default.

As a thought experiment, how about teaching initdb how to tailor the
configuration to a couple of common scenarios via some new flag? I'll
call it --setup although that's probably not best.

--setup=replication --- Preconfigure for replication.
--setup=standalone --- Preconfigure for standalone mode.
--setup=ephemeral --- Preconfigure for an ephemeral instance that
doesn't need durability because we'll blow it up soon.

Whichever mode we make the default, I think this kind of thing would
make life easier for users.

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