About clustered index in postgres

postgresql clustered-index
twimo picture twimo · Jan 25, 2011 · Viewed 56k times · Source

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?

Answer

araqnid picture araqnid · Jan 25, 2011

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.

Related questions

PostgreSQL: Show tables in PostgreSQL

What's the equivalent to show tables (from MySQL) in PostgreSQL?

Read More
PostgreSQL: How to change PostgreSQL user password?

How do I change the password for PostgreSQL user?

Read More
PostgreSQL "DESCRIBE TABLE"

How do you perform the equivalent of Oracle's DESCRIBE TABLE in PostgreSQL (using the psql command)?

Read More