14

I'm considering using CLUSTER to reorder a table by an index. I understand that this recreation of the table data makes all the existing indexes either bloat or be useless. I've seen some indications that a REINDEX is required after a CLUSTER. I've found other references that indicate that CLUSTER does a REINDEX. The Official Documentation says nothing at all about REINDEX being part of CLUSTER or required (Although it does suggest running ANALYZE after the CLUSTER)

Can anyone definitively (i.e. with some sort of reference to official docs) say whether or not a REINDEX is required after a CLUSTER?

Improve this question
2
  • 2
    I don't think it's necessary. cluster relocates the rows, so it will have to update the index information anyhow.
    – user1822
    Commented Apr 10, 2013 at 15:12
  • Yes, but the theory in half the discussions I've found is that that causes the index to bloat.
    – TREE
    Commented Apr 10, 2013 at 15:31

4 Answers 4

Reset to default
17

You do not need to reindex, because CLUSTER effectively does it for you.

More specifically, CLUSTER locks the source table then creates a new copy of it ordered according to the target index. It creates indexes on the new copy then replaces the old table and indexes with the new ones.

Note that this is also true of VACUUM FULL in 9.0+.

If you've been seeing discussion suggesting that CLUSTER bloats indexes it could be people who're assuming that CLUSTER works like pre-9.0 VACUUM FULL. You might also be seeing and misreading discussions that mention index bloat caused by the old VACUUM FULL implementation and suggesting CLUSTER as an alternative.

This is implied in the documentation:

a temporary copy of the table is created that contains the table data in the index order. Temporary copies of each index on the table are created as well. Therefore, you need free space on disk at least equal to the sum of the table size and the index sizes

What it doesn't say, but should, is that those temporary copies then replace the original table. (Bold mine).

Improve this answer
4
  • 1
    Do you have any reference that CLUSTER replaces indexes?
    – TREE
    Commented Apr 11, 2013 at 13:00
  • 1
    @TREE Added. The docs don't explicitly tell you that the temporary table and indexes then replace the originals, but you'll see that's the case if you actually look at the data directory before/after a CLUSTER or if you examine the source code.
    – Craig Ringer
    Commented Apr 11, 2013 at 23:22
  • I have tested this, and in at least my test scenario, the index file size was reduced. But this is only one scenario, and there could be many variables that affect the behavior (number of indexes, total size on disk, etc) so I can't trust a simple test.
    – TREE
    Commented Apr 12, 2013 at 12:50
  • 1
    @TREE For absolute certainty in understanding the behaviour in all possible circumstances you will need to read the source code. All I can tell you is that I'm not aware of any situation in which CLUSTER does not rewrite the indexes, and examination of the actual files in base/ will clearly show new relfilenodes. It seems you're worrying about problems you don't have yet.
    – Craig Ringer
    Commented Apr 13, 2013 at 0:57
8

I'm with a_horse_with_no_name on this: you don't need to recreate the indexes. Besides that the CLUSTER documentation does not mention it, we can further consult the REINDEX page, too:

There are several scenarios in which to use REINDEX:

  • An index has become corrupted, and no longer contains valid data. Although in theory this should never happen, in practice indexes can become corrupted due to software bugs or hardware failures. REINDEX provides a recovery method.

  • An index has become "bloated", that it is contains many empty or nearly-empty pages. This can occur with B-tree indexes in PostgreSQL under certain uncommon access patterns. REINDEX provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages. See Section 23.2 for more information.

  • You have altered a storage parameter (such as fillfactor) for an index, and wish to ensure that the change has taken full effect.

  • An index build with the CONCURRENTLY option failed, leaving an "invalid" index. Such indexes are useless but it can be convenient to use REINDEX to rebuild them. Note that REINDEX will not perform a concurrent build. To build the index without interfering with production you should drop the index and reissue the CREATE INDEX CONCURRENTLY command.

Clearly, CLUSTER does not fall into any of these cases.

And there is a small sentence in the CLUSTER docs:

[while clustering] Temporary copies of each index on the table are created as well.

This suggests that just like the table itself, the indexes get reordered during the process as well - this way making reindexing useless.

Improve this answer
5
  • The suggestion is certainly there, and testing seems to confirm it. I would feel better relying on this behavior if the docs actually said that indexes were recreated (permanently).
    – TREE
    Commented Apr 10, 2013 at 21:26
  • 2
    I see stuff for a doc patch here. The manual should be more explicit about recreating indexes.
    – Erwin Brandstetter
    Commented Apr 12, 2013 at 3:07
  • My suspicion at this point is that the devs don't want to officially document this behavior because they don't want to permanently be tied to this implementation.
    – TREE
    Commented Apr 12, 2013 at 12:45
  • @TREE there are many feature changes between versions and the docs change (mostly) accordingly. Presumably the specs change also :), so I see no tie anywhere.
    – András Váczi
    Commented Apr 12, 2013 at 13:25
  • @dezso True, but they will be reluctant to remove documented functionality. Given the quality of the documentation in general, I still assume the omission of this behavior is intentional.
    – TREE
    Commented Apr 15, 2013 at 11:50
7

Found a reference, in the Recovering Disk Space section.

If you have such a table and you need to reclaim the excess disk space it occupies, you will need to use VACUUM FULL, or alternatively CLUSTER or one of the table-rewriting variants of ALTER TABLE. These commands rewrite an entire new copy of the table and build new indexes for it.

Improve this answer
-3

Analysing all the answers, in my opinion the right way to do that is to reindex BEFORE cluster. As the documentation doesn't tell if cluster do or not a reindex, and only a copy of the index, ordered or not, I think that an indexed index will result in a better clustered table. After that an analyse will finish the job. A vacuum full before all seems to be useless, unless cluster and/or reindex do not free dead tuples

Improve this answer
3
  • As I mention in the accepted answer, the documentation does say that indexes will be rebuilt, just not on the page about the CLUSTER command.
    – TREE
    Commented Sep 18, 2015 at 14:37
  • And both CLUSTER and VACUUM FULL produces a brand new physical table - there simply cannot be any dead after it. The space used by the old copy will be freed by the end of the operation.
    – András Váczi
    Commented Sep 18, 2015 at 14:42
  • Indeed. It recreate the table and all indexes. But i have a doubt about the index that the Cluster use to reorder the table. It will be reindexed first or will be used to reorder the table as is? And after that the index is recreated? Because a problematic index could generate some problems...
    – Aislan Luiz Wendling
    Commented Dec 23, 2015 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.