Re: WAL logging volume and CREATE TABLE

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: WAL logging volume and CREATE TABLE
Date: 2011-08-02 14:52:44
Message-ID: 19345.1312296764@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kevin Grittner 2011-08-02 15:03:15 Re: WIP fix proposal for bug #6123
Previous Message Heikki Linnakangas 2011-08-02 14:27:13 Re: WAL logging volume and CREATE TABLE