postgres cluster table - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/15/sql-cluster.html

CLUSTER [ ( option [, ...] ) ] [ table_name [ USING index_name ] ]

The CLUSTER clause will rewrite the table in the same order as a btree index. If you are running an analytical workload, this can make sense. When a table is clustered, it is physically reordered based on the index information. Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered.

When a table is clustered, PostgreSQL remembers which index it was clustered by. The form CLUSTER table_name reclusters the table using the same index as before. You can also use the CLUSTER or SET WITHOUT CLUSTER forms of ALTER TABLE to set the index to be used for future cluster operations, or to clear any previous setting.

Consider checking out pg_squeeze or `pg_repack' as well, which are an extensions to reorganize a table without extensive table locking.

CREATE TABLE product_comments (
  product_id bigint,
  comment_id bigint GENERATED ALWAYS AS IDENTITY,
  message text,
  PRIMARY KEY (product_id, comment_id)
);
CLUSTER product_comments USING product_comments_pkey;