Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
I don't think it's necessary. cluster relocates the rows, so it will have to update the index information anyhow. –  a_horse_with_no_name Apr 10 '13 at 15:12
    
Yes, but the theory in half the discussions I've found is that that causes the index to bloat. –  TREE Apr 10 '13 at 15:31
add comment

3 Answers

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.

share|improve this answer
    
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 Apr 10 '13 at 21:26
2  
I see stuff for a doc patch here. The manual should be more explicit about recreating indexes. –  Erwin Brandstetter Apr 12 '13 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 Apr 12 '13 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. –  dezso Apr 12 '13 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 Apr 15 '13 at 11:50
add comment

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

share|improve this answer
    
Do you have any reference that CLUSTER replaces indexes? –  TREE Apr 11 '13 at 13:00
    
@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 Apr 11 '13 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 Apr 12 '13 at 12:50
    
@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 Apr 13 '13 at 0:57
add comment
up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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