Menu
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?
457
PostgreSQL does not have a clustered index, so you won't be able to see them.
Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
Clustered Index – Index data gets stored along with other part of data and data gets sorted based on index key. At most there can be only one index in this category for a specified table. Non-Clustered Index – Index data gets stored separately and it has a pointer to the storage where other part of data is stored.
A database cluster is a collection of databases that is managed by a single instance of a running database server. After initialization, a database cluster will contain a database named postgres , which is meant as a default database for use by utilities, users and third party applications.
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.
59
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
