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?

Stealthy

Every index in Postgres is "non-clustered". Postgres does not have clustered indexes.If you run create index it will create a (non-clustered) B-Tree index. Why do you think there is no non-clustered index? – Racial

@a_horse_with_no_name, Okay! My bad. But how can I create clustered index? – Stealthy

As I said: Postgres doesn't have clustered indexes. So you can't create one. Why do you think you need one? – Racial

@a_horse_with_no_name, So create index does both job in PostgreSQL. – Stealthy

It only creates non-clustered indexes because Postgres does not have a clustered indexes – Racial

Answer 1

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.

Danette

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

@a_horse_with_no_name, Yeah! It is. – Stealthy

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 – Ageless

@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. – Promotion

@Promotion 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. – Ageless

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? – Metaphrase

@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. – Danette

@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. – Haskel

Answer 2

For a clustered index, each of the desired fields must be the primary key. And for the non-clustered index, we act according to the following command :

CREATE INDEX IX_Test_Colc ON test(colc);

CREATE INDEX IX_Test_Cold ON test(cold);

Obregon