Our docs suggest an optimization to reduce WAL logging when you are
creating and populating a table:
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)?