Thread

Re: WAL logging volume and CREATE TABLE

Bruce Momjian <bruce@momjian.us> — 2011-08-03T02:46:55Z

Robert Haas wrote:
> On Tue, Aug 2, 2011 at 11:30 AM, Bruce Momjian <bruce@momjian.us> wrote:
> > Tom Lane wrote:
> >> Bruce Momjian <bruce@momjian.us> writes:
> >> > Our docs suggest an optimization to reduce WAL logging when you are
> >> > creating and populating a table:
> >>
> >> > ? ? http://www.postgresql.org/docs/9.0/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS
> >>
> >> > ? ? In minimal level, WAL-logging of some bulk operations, like CREATE
> >> > ? ? INDEX, CLUSTER and COPY on a table that was created or truncated in the
> >> > ? ? same transaction can be safely skipped, which can make those operations
> >> > ? ? much faster (see Section 14.4.7). But minimal WAL does not contain
> >> > ? ? enough information to reconstruct the data from a base backup and the
> >> > ? ? WAL logs, so either archive or hot_standby level must be used to enable
> >> > ? ? WAL archiving (archive_mode) and streaming replication.
> >>
> >> > I am confused why we issue significant WAL traffic for CREATE INDEX?
> >>
> >> The point is that in minimal level we *don't*. ?We just fsync the index
> >> file before committing. ?In higher levels we have to write the whole
> >> index contents to the WAL, not only the disk file, so that the info
> >> reaches the archive or standby slaves.
> >>
> >> Same for the other cases.
> >
> > I realize the need for WAL logging CREATE INDEX for non-'minimal'
> > wal_level values.
> >
> > But the documentation states the WAL logging is reduced for CREATE INDEX
> > by doing CREATE TABLE in the same transaction block. ?Why is this true?
> > Why would the CREATE TABLE affect the "CREATE INDEX" WAL volume?
> >
> > I am wondering if the documention is correct about CLUSTER and COPY, but
> > incorrect for CREATE INDEX.
> 
> I think the problem here might be ambiguous wording.  I believe that
> the modifier "on a table that was created or truncated in the same
> transaction" is intended to apply only to "COPY", but the way it's
> written, someone (such as you) might be forgiven for thinking that it
> applied to the larger phrase "CREATE INDEX, CLUSTER, or COPY".

I have created a documentation patch to clarify this, and to mention
CREATE TABLE AS which also has this optimization.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +