Cluster and Non cluster index in PostgreSQL

Question

I'm using PostgreSQL 9.3 version to create database.

I have the following table test with some columns list.

create table test
(
  cola varchar(10),
  colb varchar(10),
  colc varchar(10),
  cold varchar(10)
);

Now I want to create a indexs on some columns.

For example:

I want to create clustered index for columns cola and colb.

And I want to create non clustered index for columns colc and cold.

As I referred this And this ,I come to know that there is no clustered and non clustered index in PostgreSQL.

My Question: What type of index I can use instead of clustered and non clustered index in PostgreSQL,Which does the same job as clustered and non clustered indexes does?

Solution

My Question: What type of index I can use instead of clustered and non clustered index in PostgreSQL,Which does the same job as clustered and non clustered indexes does?

PostgreSQL doesn't have the concept of clustered indexes at all. Instead, all tables are heap tables and all indexes are non-clustered indexes.

Just create a non-clustered index when you'd usually create a clustered index.

More details:

• http://use-the-index-luke.com/sql/clustering/index-organized-clustered-index
  You might want to read the whole chapter to get proper understanding of these concepts.

Comments

[a_horse_with_no_name]

@mak: I also find this blog from Markus very interesting: use-the-index-luke.com/blog/2014-01/…

[MAK]

@a_horse_with_no_name, Yeah! It is.

[StvnBrkdll]

It appears the Postgres implements something that at least approximates a clustered index: "CLUSTER instructs PostgreSQL to cluster the table specified by table_name based on the index specified by index_name" From the postgres docs: postgresql.org/docs/9.1/static/sql-cluster.html

[IMSoP]

@mangotang See this related question. In short, CLUSTER reorganises the current data in a table based on a particular index, but does not create a different data structure or maintain that order.

[StvnBrkdll]

@IMSoP Thank you for the clarification. By saying that postgres "approximates a clustered index", I was attempting to be clear that it is in fact not a clustered index, but may be a viable alternative.

[Arup Rakshit]

You are saying PG don't have clustered index, but I see they do postgresql.org/docs/current/static/sql-cluster.html .. What am I missing?

[Markus Winand]

@ArupRakshit You are missing the fact that PostgreSQL uses the term cluster for a different concept. PostgreSQL clustering is ordering the rows in a table according to the order defined by an index. Whereas a "clustered index" in some databases refers to the concept of having all columns stored in the index. Similar names, different concepts.

[Yuval]

@MarkusWinand Indeed, CLUSTER does not implement a clustered index because it does not maintain the ordering nor stores the data in the index, however I propose it should be mentioned in your answer since it does have some of the performance gain of clustered indices - as it will dramatically decrease page reads when an index is found. It would benefit both the OP as well as people searching for similar information.