Re: Defaults for replication/backup

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.

- Andres