Date: Sat, 23 Nov 2019 11:35:09 -0500
From: Noah Misch <noah@leadboat.com>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>,
	Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>,
	Dmitry Dolgov <9erthalion6@gmail.com>,
	Andrew Dunstan <andrew.dunstan@2ndquadrant.com>,
	hlinnaka <hlinnaka@iki.fi>, Michael Paquier <michael@paquier.xyz>
Subject: Re: [HACKERS] WAL logging problem in 9.4.3?
Message-ID: <20191123163509.GA39577@gust.leadboat.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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1f9a76fe-6f77-eb5f-9292-9b1c92f4f5bd@2ndquadrant.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

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.