Mastering PostgreSQL 9.6

Clustering tables

In PostgreSQL, there is a command called CLUSTER that allows us to rewrite a table in a desired order. It is possible to point to an index and store data in the same order as the index:

test=# \h CLUSTER Command:     CLUSTER Description: cluster a table according to an index Syntax: CLUSTER [VERBOSE] table_name [ USING index_name ] CLUSTER [VERBOSE]

The CLUSTER command has been around for many years and serves its purpose well. But, there are some things to consider before blindly running it on a production system:

• The CLUSTER command will lock the table while it is running. You cannot insert or modify data while CLUSTER is running. This might not be acceptable on a production system.
• Data can only be organized according ...