About clustered index in postgres

I'm using psql to access a postgres database. When viewing the metadata of a table, is there any way to see whether an index of a table is a clustered index?

I heard that the PRIMARY KEY of a table is automatically associated with a clustered index, is it true?

Solution 1:

Note that PostgreSQL uses the term "clustered index" to use something vaguely similar and yet very different to SQL Server.

If a particular index has been nominated as the clustering index for a table, then psql's \d command will indicate the clustered index, e.g.,

Indexes:
    "timezone_description_pkey" PRIMARY KEY, btree (timezone) CLUSTER

PostgreSQL does not nominate indices as clustering indices by default. Nor does it automatically arrange table data to correlate with the clustered index even when so nominated: the CLUSTER command has to be used to reorganise the table data.

Solution 2:

In PostgreSQL the clustered attribute is held in the metadata of the corresponding index, rather than the relation itself. It is the indisclustered attribute in pg_index catalogue. Note, however, that clustering relations within postgres is a one-time action: even if the attribute is true, updates to the table do not maintain the sorted nature of the data. To date, automatic maintenance of data clustering remains a popular TODO item.

There is often confusion between clustered and integrated indexes, particularly since the popular textbooks use conflicting names, and the terminology is different again in the manuals of postgres and SQL server (to name just two). When I talk about an integrated index (also called a main index or primary index) I mean one in which the relation data is contained in the leaves of the index, as opposed an external or secondary index in which the leaves contain index entries that point to the table records. The former type is necessarily always clustered. Unfortunately postgres only supports the latter type. Anyhow, the fact that an integrated (primary) index is always clustered may have given rise to the belief that "a PRIMARY KEY of a table is automatically associated with a clustered index". The two statements sound similar, but are different.

Solution 3:

is there any way to see whether an index of a table is a clustered index

PostgreSQL does not have a clustered index, so you won't be able to see them.

I heard that the PRIMARY KEY of a table is automatically associated with a clustered index, is it true?

No, that's not true (see above)

You can manually cluster a table along an index, but this is nothing that will be maintained automatically (as e.g. with SQL Server's clustered indexes).

For more details, see the description of the CLUSTER command in the manual.