Robert Haas wrote:
> On Tue, Aug 2, 2011 at 11:30 AM, Bruce Momjian <[email protected]> wrote:
> > Tom Lane wrote:
> >> Bruce Momjian <[email protected]> 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.
>
> I think the problem here might be ambiguous wording. I believe that
> the modifier "on a table that was created or truncated in the same
> transaction" is intended to apply only to "COPY", but the way it's
> written, someone (such as you) might be forgiven for thinking that it
> applied to the larger phrase "CREATE INDEX, CLUSTER, or COPY".
I have created a documentation patch to clarify this, and to mention
CREATE TABLE AS which also has this optimization.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
new file mode 100644
index 4fadca9..a1f51ec
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
*************** SET ENABLE_SEQSCAN TO OFF;
*** 1452,1461 ****
</para>
<para>
In <literal>minimal</> level, WAL-logging of some bulk operations, like
! <command>CREATE INDEX</>, <command>CLUSTER</> and <command>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
! <xref linkend="populate-pitr">). But minimal WAL does not contain
enough information to reconstruct the data from a base backup and the
WAL logs, so either <literal>archive</> or <literal>hot_standby</>
level must be used to enable
--- 1452,1463 ----
</para>
<para>
In <literal>minimal</> level, WAL-logging of some bulk operations, like
! <command>CREATE INDEX</>, <command>CLUSTER</>, and <command>CREATE
! TABLE AS</>, can be safely skipped, which can make those
! operations much faster (see <xref linkend="populate-pitr">).
! In minimal WAL-logging mode, it is also possible to skip WAL-logging of
! and <command>COPY</> operations on tables that were created
! or truncated in the same transaction. But minimal WAL does not contain
enough information to reconstruct the data from a base backup and the
WAL logs, so either <literal>archive</> or <literal>hot_standby</>
level must be used to enable
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
