Re: wal_level=minimal produces more data than archive level

Tomas Vondra <tv(at)fuzzy(dot)cz> writes:
> I've been playing with various wal_level settings, mainly to find out
> what is the overhead of hot standby, and I've noticed a strange thing.
> In some cases the wal_level=minimal produces signigicantly more xlog
> data than wal_level=archive (and hot_standby).
> ...
> Which means the pgbench init takes about 142kB with 'minimal' level and
> about 163MB with 'archive' level (which is expected).

> But the actual pgbench run produces much more xlog data with minimal wal
> level compared to archive level. With minimal level it produces about
> 90MB and with archive level it produces just about 5MB.

> I've check the docs and the only possible explanation I've noticed when
> reading the docs is that while wal_level=minimal allows to skip logging
> of some bulk operations (e.g. COPY, which is exactly what pgbench does
> when initializing the DB), it may need to log more data later (when
> actually running the bench).

I wonder whether the discrepancy relates to having to produce
full-page-image WAL entries during the first touch of a page during the
pgbench run, versus having already done so in initialization. If you
force a checkpoint after the init step, do the results change?

regards, tom lane