PostgreSQL provides a CLUSTER command, not a clustered index as a persistent storage structure.

How it works

• You choose an index
• PostgreSQL physically rewrites the table so rows follow that index order
• After clustering:
• table rows are stored in index order
• but only at that moment

Critical limitation

 The clustering is NOT maintained automatically

As soon as:

• INSERTs
• UPDATEs
• DELETEs
  occur, the physical order starts to drift.

You must re-run CLUSTER manually (or via maintenance jobs).

Why this is NOT a real clustered index

A true clustered index means:

• table storage is permanently tied to the index
• every DML operation preserves order
• the index is the table

PostgreSQL does not support this model.

Comparison with other systems

Oracle

• ❌ No clustered index
• Uses:
• heap tables
• Index Organized Tables (IOTs) → closest equivalent

SQL Server

• ✅ True clustered index
• Table data is the clustered index
• Order is always preserved

PostgreSQL

• ❌ No true clustered index
• ✅ One-time physical reordering via CLUSTER

What PostgreSQL actually is

PostgreSQL tables are heap-organized:

• MVCC creates multiple row versions
• physical order cannot be tightly controlled
• indexes are always secondary structures

This makes automatic clustering incompatible with PostgreSQL’s storage model.

Practical rule of thumb

• Use CLUSTER when:
• large read-heavy workloads
• predictable access pattern
• Expect:
• performance gain to decay over time
• Plan:
• periodic re-clustering (off-peak)

PostgreSQL has a clustering operation, not a clustered index.