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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Search Discussions

  • Merlin Moncure at Aug 2, 2011 at 2:02 pm

    On Tue, Aug 2, 2011 at 8:34 AM, Bruce Momjian wrote:
    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?
    I don't think so -- insert/select doesn't take a full table lock and
    it writes to the heap. The optimization only works when other
    backends will never see/touch the data being written out until it is
    finished and it doesn't matter if the data is scrambled due to a
    crash. CREATE INDEX might work though.

    merlin
  • Bruce Momjian at Aug 2, 2011 at 3:33 pm

    Merlin Moncure wrote:
    On Tue, Aug 2, 2011 at 8:34 AM, Bruce Momjian wrote:
    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?
    I don't think so -- insert/select doesn't take a full table lock and
    it writes to the heap. The optimization only works when other
    My question is whether INSERT ... SELECT is/could be optimized when the
    CREATE TABLE happens in the same transaction block.

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Heikki Linnakangas at Aug 2, 2011 at 2:27 pm

    On 02.08.2011 16:34, Bruce Momjian wrote:
    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.
    I'm confused about what you're confused about. Crash recovery doesn't
    need the WAL for CREATE INDEX, but WAL archiving does.

    --
    Heikki Linnakangas
    EnterpriseDB http://www.enterprisedb.com
  • Tom Lane at Aug 2, 2011 at 2:52 pm

    Bruce Momjian 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
  • Bruce Momjian at Aug 2, 2011 at 3:30 pm

    Tom Lane wrote:
    Bruce Momjian <bruce@momjian.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.
    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.

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Tom Lane at Aug 2, 2011 at 3:54 pm

    Bruce Momjian writes:
    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 the documentation states the WAL logging is reduced for CREATE INDEX
    by doing CREATE TABLE in the same transaction block. Why is this true?
    It's not true, and it doesn't say that, or at least doesn't intend to
    say that. That sentence is meant to be read as:

    1. The optimization applies to CREATE INDEX.
    2. The optimization applies to CLUSTER or COPY on a table that was
    created or truncated in the current transaction.

    I now see your point, which is that the sentence is easily misparsed.

    regards, tom lane
  • Robert Haas at Aug 3, 2011 at 2:30 am

    On Tue, Aug 2, 2011 at 11:30 AM, Bruce Momjian wrote:
    Tom Lane wrote:
    Bruce Momjian <bruce@momjian.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.
    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".

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Bruce Momjian at Aug 3, 2011 at 2:47 am

    Robert Haas wrote:
    On Tue, Aug 2, 2011 at 11:30 AM, Bruce Momjian wrote:
    Tom Lane wrote:
    Bruce Momjian <bruce@momjian.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.
    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 <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Alvaro Herrera at Aug 3, 2011 at 3:53 am

    Excerpts from Bruce Momjian's message of mar ago 02 22:46:55 -0400 2011:

    I have created a documentation patch to clarify this, and to mention
    CREATE TABLE AS which also has this optimization.
    It doesn't seem particularly better to me. How about something like

    In minimal level, WAL-logging of some operations can be safely skipped,
    which can make those operations much faster (see <blah>). Operations on
    which this optimization can be applied include:
    <simplelist>
    <item>CREATE INDEX</item>
    <item>CLUSTER</item>
    <item>CREATE TABLE AS</item>
    <item>COPY, when tables that were created or truncated in the same
    transaction
    </simplelist>

    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 ...

    --
    Álvaro Herrera <alvherre@commandprompt.com>
    The PostgreSQL Company - Command Prompt, Inc.
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support
  • Bruce Momjian at Aug 3, 2011 at 6:52 pm

    Alvaro Herrera wrote:
    Excerpts from Bruce Momjian's message of mar ago 02 22:46:55 -0400 2011:
    I have created a documentation patch to clarify this, and to mention
    CREATE TABLE AS which also has this optimization.
    It doesn't seem particularly better to me. How about something like

    In minimal level, WAL-logging of some operations can be safely skipped,
    which can make those operations much faster (see <blah>). Operations on
    which this optimization can be applied include:
    <simplelist>
    <item>CREATE INDEX</item>
    <item>CLUSTER</item>
    <item>CREATE TABLE AS</item>
    <item>COPY, when tables that were created or truncated in the same
    transaction
    </simplelist>

    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 ...
    Good idea --- updated patch attached.

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Bruce Momjian at Aug 4, 2011 at 4:07 pm
    Patch applied.

    ---------------------------------------------------------------------------

    Bruce Momjian wrote:
    Alvaro Herrera wrote:
    Excerpts from Bruce Momjian's message of mar ago 02 22:46:55 -0400 2011:
    I have created a documentation patch to clarify this, and to mention
    CREATE TABLE AS which also has this optimization.
    It doesn't seem particularly better to me. How about something like

    In minimal level, WAL-logging of some operations can be safely skipped,
    which can make those operations much faster (see <blah>). Operations on
    which this optimization can be applied include:
    <simplelist>
    <item>CREATE INDEX</item>
    <item>CLUSTER</item>
    <item>CREATE TABLE AS</item>
    <item>COPY, when tables that were created or truncated in the same
    transaction
    </simplelist>

    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 ...
    Good idea --- updated patch attached.

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
    --
    Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers
    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 2, '11 at 1:35p
activeAug 4, '11 at 4:07p
posts12
users6
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase