Skip site navigation (1) Skip section navigation (2)

Re: WAL logging volume and CREATE TABLE

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: WAL logging volume and CREATE TABLE
Date: 2011-08-02 15:30:30
Message-ID: 201108021530.p72FUUl29245@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)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.

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2011-08-02 15:32:57
Subject: Re: WAL logging volume and CREATE TABLE
Previous:From: Tom LaneDate: 2011-08-02 15:19:05
Subject: 9.1 release schedule

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group