Re: Can PostgreSQL create new WAL files instead of reusing old ones?

[David Pacheco <dap@xxxxxxxxxx>]

On Wed, Jun 20, 2018 at 10:35 AM, Jerry Jelinek <jerry.jelinek@xxxxxxxxxx> wrote:

As Dave described in his original email on this topic, we'd like to avoid recycling WAL files since that can cause performance issues when we have a read-modify-write on a file that has dropped out of the cache.

I have implemented a small change to allow WAL recycling to be disabled. It is visible at:

https://cr.joyent.us/#/c/4263/

I'd appreciate getting any feedback on this.

Thanks,

Jerry

For reference, there's more context in this thread from several months ago:

https://www.postgresql.org/message-id/flat/CACukRjO7DJvub8e2AijOayj8BfKK3XXBTwu3KKARiTr67M3E3w%40mail.gmail.com#CACukRjO7DJvub8e2AijOayj8BfKK3XXBTwu3KKARiTr67M3E3w@xxxxxxxxxxxxxx

I'll repeat the relevant summary here:

tl;dr: We've found that under many conditions, PostgreSQL's re-use of old
WAL files appears to significantly degrade query latency on ZFS.  The reason is
complicated and I have details below.  Has it been considered to make this
behavior tunable, to cause PostgreSQL to always create new WAL files
instead of re-using old ones?

Thanks,

Dave