Horton

Re: [HACKERS] WAL logging problem in 9.4.3?

Noah Misch <noah@leadboat.com>

View thread View on postgr.es
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>
Date: 2019-11-23T16:35:09Z
Lists: pgsql-hackers 
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.

Commits

  1. Add perl2host call missing from a new test file.

  2. Skip WAL for new relfilenodes, under wal_level=minimal.

  3. Revert "Skip WAL for new relfilenodes, under wal_level=minimal."

  4. Back-patch log_newpage_range().

  5. During heap rebuild, lock any TOAST index until end of transaction.

  6. In log_newpage_range(), heed forkNum and page_std arguments.

  7. Back-patch src/test/recovery and PostgresNode from 9.6 to 9.5.

  8. Reduce pg_ctl's reaction time when waiting for postmaster start/stop.

  9. Accelerate end-of-transaction dropping of relations

  10. Redesign the planner's handling of index-descent cost estimation.

  11. Make TRUNCATE do truncate-in-place when processing a relation that was created