How does PostgreSQL's CLUSTER differ from a clustered index in SQL Server?

Question

Many posts like this stackoverflow link claim that there is no concept of a clustered index in PostgreSQL. However, the PostgreSQL documentation contains something similar. A few people claim it is similar to a clustered index in SQL Server.

Do you know what the exact difference between these two is, if there is any?

Answer 1

A clustered index or index organized table is a data structure where all the table data are organized in index order, typically by organizing the table in a B-tree structure.

Once a table is organized like this, the order is automatically maintained by all future data modifications.

PostgreSQL does not have such clustering indexes. What the CLUSTER command does is rewrite the table in the order of the index, but the table remains a fundamentally unordered heap of data, so future data modifications will not maintain that index order.

You have to CLUSTER a PostgreSQL table regularly if you want to maintain an approximate index order in the face of data modifications to the table.

Clustering in PostgreSQL can improve performance, because tuples found during an index scan will be close together in the heap table, which can turn random access to the heap to faster sequential access.