Question: Is the primary key in PostgreSQL clustered?

Answer

In PostgreSQL, a primary key is not automatically clustered. This differs from some other database systems like SQL Server, where a primary key is clustered by default unless explicitly specified otherwise.

A clustered index determines the physical order of data in a table based on the columns included in the index. However, PostgreSQL does not support clustered indexes in the same way. Instead, it offers something similar through the CLUSTER command, which allows you to physically reorder a table based on the index specified. This reordering is manual and not maintained automatically during subsequent inserts or updates.

Here's how you can use the CLUSTER command with a primary key:

-- Assume we have a table defined as follows:
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INT
);

-- Creating an index on the primary key
CREATE INDEX idx_employees_id ON employees (id);

-- Clustering the table based on the primary key index
CLUSTER employees USING idx_employees_id;

After executing the CLUSTER command, the rows of the table employees are reordered on disk to follow the order of the id index. It's important to note that this order will not be maintained after insertions, updates, or deletions. If you need to reapply the clustering, you must run the CLUSTER command again.

Therefore, while PostgreSQL does not have clustered indexes in the traditional sense, it provides tools for manually clustering data around an index, including a primary key.