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-SETTINGSIn minimal level,
WAL-loggingof some bulk operations, like CREATEINDEX, CLUSTER and COPY on a table that was created or truncated in
thesametransaction can be safely skipped, which can make those operationsmuch faster (see Section 14.4.7). But minimal
WALdoes not containenough information to reconstruct the data from a base backup and theWAL logs, so either archive or
hot_standbylevel must be used to enableWAL 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@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ It's impossible for everything to be true. +