v. 8.4.3

I have a table that has several indexes, one of which the table is clustered on. If I do an ALTER TABLE Foo ADD COLUMN bar integer not null default -1;

It re-writes the whole table.

* Does it adhere to the CLUSTER property of the table and write the new version clustered?
* Does it properly write it with the FILLFACTOR setting?
* Are all the indexes re-created too, or are they bloated and need a REINDEX?

http://www.postgresql.org/docs/8.4/static/sql-altertable.html
does not seem to answer the above, it mentions the conditions that cause a rewrite but does not say what the state is after the rewrite with respect to CLUSTER, FILLFACTOR, and index bloat.

Thanks in advance!

Search Discussions

  • Bruce Momjian at Jun 24, 2010 at 3:03 pm

    Scott Carey wrote:
    v. 8.4.3

    I have a table that has several indexes, one of which the table is
    clustered on. If I do an ALTER TABLE Foo ADD COLUMN bar integer not
    null default -1;

    It re-writes the whole table.
    All good questions:
    * Does it adhere to the CLUSTER property of the table and write the new
    version clustered?
    The new table is the exact same heap ordering as the old table; it does
    not refresh the clustering if the table has become unclustered.
    * Does it properly write it with the FILLFACTOR setting?
    Yes, inserts are used to populate the new table, and inserts honor
    FILLFACTOR.
    * Are all the indexes re-created too, or are they bloated and need a REINDEX?
    They are recreated.
    http://www.postgresql.org/docs/8.4/static/sql-altertable.html
    does not seem to answer the above, it mentions the conditions that
    cause a rewrite but does not say what the state is after the rewrite
    with respect to CLUSTER, FILLFACTOR, and index bloat.
    I have added a documentation patch to mention the indexes are rebuilt;
    applied patch attached.

    The gory details can be found in src/backend/commands/tablecmds.c.

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

    + None of us is going to be here forever. +

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedJun 22, '10 at 5:30p
activeJun 24, '10 at 3:03p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Scott Carey: 1 post Bruce Momjian: 1 post

People

Translate

site design / logo © 2022 Grokbase