Question: Why cant PostgreSQL use clustered indexes?

Answer

In PostgreSQL, the concept of clustered indexes as implemented in some other database systems does not exist. Instead, PostgreSQL uses a storage and indexing approach that differs from traditional clustered indexes.

Understanding Clustered Indexes

In databases like Microsoft SQL Server, a clustered index sorts and stores the data rows in the table based on their key values. There can be only one clustered index per table, as the data rows themselves are sorted and stored in order of the clustered index keys.

PostgreSQL's Approach: The CLUSTER Command

PostgreSQL does not support true clustered indexes, but it does offer a functionality somewhat similar through the CLUSTER command. Using CLUSTER, you can reorder a table physically based on the index, which can improve performance for certain types of queries.

The CLUSTER command reorders the table's actual data based on the specified index and stores the data physically on the disk to match this order. However, unlike a true clustered index, this order is not maintained over time as new rows are inserted or existing rows are updated.

Example Usage of CLUSTER

Here’s how you might use the CLUSTER command:

-- Assuming you have a table 'employees' and an index 'emp_index' on the column 'department'
CLUSTER employees USING emp_index;

After executing this command, the table employees will be reordered on the disk according to the index emp_index. This operation can improve the performance of queries that involve scanning large portions of the 'department' column.

Maintaining Performance Over Time

Since PostgreSQL does not automatically maintain the order after insertions or updates, you would need to periodically re-cluster the table if necessary. This can be manually done by re-running the CLUSTER command, or by setting up scheduled jobs to handle it.

In conclusion, while PostgreSQL does not support clustered indexes in the way SQL Server or Oracle might, its CLUSTER command provides a means to optimize table storage for read-heavy scenarios based on an index. It's crucial to understand the maintenance implications of this approach, as the clustering effect is not maintained automatically.