From: Noah Misch <noah@leadboat.com>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Dmitry Dolgov <9erthalion6@gmail.com>
Cc: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
Cc: hlinnaka <hlinnaka@iki.fi>
Cc: Michael Paquier <michael@paquier.xyz>
Subject: Re: [HACKERS] WAL logging problem in 9.4.3?
Date: Sat, 23 Nov 2019 11:35:09 -0500
Message-ID: <20191123163509.GA39577@gust.leadboat.com> (raw)
In-Reply-To: <1f9a76fe-6f77-eb5f-9292-9b1c92f4f5bd@2ndquadrant.com>
References: <20190828.154210.204505676.horikyota.ntt@gmail.com>
<20191025.131251.322449872063947371.horikyota.ntt@gmail.com>
<CAKPRHzJSgMuNeCtKgSxBJd2zOgK3BKL13Pkn_6_Sr9qXCRU=fQ@mail.gmail.com>
<CA+TgmoYzTPpxvPUUs_ALW1YYNxV6mCrj6zwqnaN6FqdNc1JUzw@mail.gmail.com>
<1f9a76fe-6f77-eb5f-9292-9b1c92f4f5bd@2ndquadrant.com>
On Fri, Nov 22, 2019 at 01:21:31PM +0100, Peter Eisentraut wrote:
> On 2019-11-05 22:16, Robert Haas wrote:
> >First, I'd like to restate my understanding of the problem just to see
> >whether I've got the right idea and whether we're all on the same
> >page. When wal_level=minimal, we sometimes try to skip WAL logging on
> >newly-created relations in favor of fsync-ing the relation at commit
> >time.
>
> How useful is this behavior, relative to all the effort required?
>
> Even if the benefit is significant, how many users can accept running with
> wal_level=minimal and thus without replication or efficient backups?
That longstanding optimization is too useful to remove, but likely not useful
enough to add today if we didn't already have it. The initial-data-load use
case remains plausible. I can also imagine using wal_level=minimal for data
warehouse applications where one can quickly rebuild from the authoritative
data.
> Is there perhaps an alternative approach involving unlogged tables to get a
> similar performance benefit?
At wal_level=replica, it seems inevitable that ALTER TABLE SET LOGGED will
need to WAL-log the table contents. I suppose we could keep wal_level=minimal
and change its only difference from wal_level=replica to be that ALTER TABLE
SET LOGGED skips WAL. Currently, ALTER TABLE SET LOGGED also rewrites the
table; that would need to change. I'd want to add ALTER INDEX SET LOGGED,
too. After all that, users would need to modify their applications. Overall,
it's possible, but it's not a clear win over the status quo.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: noah@leadboat.com, peter.eisentraut@2ndquadrant.com, robertmhaas@gmail.com, horikyota.ntt@gmail.com, 9erthalion6@gmail.com, andrew.dunstan@2ndquadrant.com, hlinnaka@iki.fi, michael@paquier.xyz
Subject: Re: [HACKERS] WAL logging problem in 9.4.3?
In-Reply-To: <20191123163509.GA39577@gust.leadboat.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox