Re: Bump default wal_level to logical

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 agree that we should consider changing it *if* it does not come with a
>substantial overhead, but that has to be shown.
>

I agree performance evaluation is necessary, and I'm willing to spend
some time on it. But I don't think the difference will be much worse
than for the wal_level=replica, at least for common workloads. It's
certainly possible to construct workloads with significant impact, due
to the extra stuff (assignments, cache invalidations and so on).

In general I think the case is somewhat weaker compared to the replica
case, which was required for such basic things like physical backups.


>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?
>

Yeah. That would sidestep a lot of the performance concerns, because if
switching from replica to logical is fairly easy / without restart, we
could keep the current default.

Not sure if it's sufficient, though, because switching to logical may
require bumping up number of slots, walsenders, etc. At which point you
actually need a restart. Not to mention that extensions using logical
decoding (like pglogical) need to allocate shared memory etc. But for
the built-in logical replication that is not an issue, ofc.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services