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

WAL logging volume and CREATE TABLE

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: WAL logging volume and CREATE TABLE
Date: 2011-08-02 13:34:56
Message-ID: 201108021334.p72DYuK08048@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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? 
Isn't the index either created or removed if the transaction fails? 
What crash recovery activity state do we need WAL logging for?  I
realize we have to do WAL logging for streaming replication, but CREATE
TABLE isn't going to affect that.   I also realize the index has to be
on disk on commit, but the same is true for doing the CREATE TABLE in
the same transaction block.

Does this optimization work for INSERT ... SELECT? Is this optimization
automatic for CREATE TABLE AS (SELECT INTO)?

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

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

Responses

pgsql-hackers by date

Next:From: Merlin MoncureDate: 2011-08-02 14:02:05
Subject: Re: WAL logging volume and CREATE TABLE
Previous:From: Achim DommaDate: 2011-08-02 12:48:02
Subject: Re: Access to current database from C-language function

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