Notes on Clustering

If the USING INDEX clause is specified, the rows in the table are physically rearranged according to the ordering implied by the specified index; this is known as clustering. This can have performance implications: in cases where you are accessing single rows randomly within a table, the actual order of the data in the table is unimportant. However, if you tend to access some data more than others, and there is an index that groups them together, you will benefit from using clustering. If you are requesting a range of indexed values from a table, or a single indexed value that has multiple matching rows, clustering will help because once the index identifies the table page for the first row that matches, all other rows that match are probably already on the same table page, and so you save disk accesses and speed up the query.

For B-Tree indexes, the ordering used by clustering is the index's linear sort order. Other clusterable index access methods may use a different ordering strategy, one which may not necessarily correspond to any SQL sort order. If an index name is specified in the command, that index is used and is recorded as the table's clustering index. (This also applies to an index given to the CLUSTER command.) If no index name is specified, then the index that has been configured as the clustering one is used; if none has been configured, an error is thrown. An index can be set manually using ALTER TABLE ... CLUSTER ON, and reset with ALTER TABLE ... SET WITHOUT CLUSTER.

Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. That is, no attempt is made to store new or updated rows according to the clustering order. (If one wishes, one can periodically recluster by issuing the command again. Also, setting the table's fillfactor storage parameter to less than 100% can aid in preserving cluster ordering during updates, since updated rows are kept on the same page if enough space is available there.)

When clustering on a B-Tree index, REPACK can rewrite the table using either an index scan on the specified index, or a sequential scan followed by sorting. It will attempt to choose the method that will be faster, based on planner cost parameters and available statistical information. When clustering on an index of an access method other than B-Tree, REPACK always uses an index scan.

Because the planner records statistics about the ordering of tables, it is advisable to specify the ANALYZE option, or to run ANALYZE on the newly repacked table. Otherwise, the planner might make poor choices of query plans.

If no table name is specified in REPACK USING INDEX, all tables which have a clustering index defined and which the calling user has privileges for are processed.

Notes on Resources

When an index scan or a sequential scan without sort is used, 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.

When a sequential scan and sort is used, a temporary sort file is also created, so that the peak temporary space requirement is as much as double the table size, plus the index sizes. This method is often faster than the index scan method, but if the disk space requirement is intolerable, you can disable this choice by temporarily setting enable_sort to off.

It is advisable to set maintenance_work_mem to a reasonably large value (but not more than the amount of RAM you can dedicate to the REPACK operation) before repacking.