EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Clustered Index
 

PostgreSQL Clustered Index

Sohel Sayyad
Article bySohel Sayyad
EDUCBA
Reviewed byRavi Rathore

Updated May 18, 2023

PostgreSQL Clustered Index

 

 

Definition of PostgreSQL Clustered Index

PostgreSQL provides clustered index functionality to the user in which every table of the database has a unique clustered index. Clustered index means it stores another value of a table on secondary storage. Clustered index is used to identify rows from a table uniquely. When we talk about clustered indexes containing groups of similar items or properties that are clustering, we can say properties. By default column name and primary key of the table have a clustered index. In PostgreSQL, clustered attributes store the metadata of the corresponding index rather than the relation. Clustering index has one to one relation in postgreSQL.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

Clustered schema name using index name;

Explanation:

In the above syntax, schema name means table name that we need to cluster, which means the content of the table physically reorders based on the indexing. Clustering is a one-time operation, meaning clustering changes are not allowed. Index name means index name. VERBOSE is used to print progress reports of database tables clustered.

How to Use a Clustered Index in PostgreSQL?

We must install PostgreSQL in your system. Required basic knowledge about PostgreSQL. We must require a database table to create clustering indexing. Basic understanding of clustered indexing, i.e., how it functions, is necessary. We can perform different operations on database tables with the help of psql and pgAdmin.

Let’s see a different example of a clustered index to understand better as follows.

Example #1

For the implementation of the clustered index, first, we need to create a table with primary, so let’s create a table by using the following statement.

CREATE TABLE test(ID INT PRIMARY KEY NOT NULL, Name TEXT NOT NULL,  Age INT NOT NULL, Address CHAR(50), Salary REAL);

Explanation

In the above example, we created a test table with different attributes such as employee ID, Name, Age, Address, and Salary with different data types and sizes. Illustrate the end result of the above declaration by using the following snapshot.

PostgreSQL Clustered Index 1

Now insert a record into the table by using the following insert into a statement as follows.

INSERT INTO test (ID,Name,Age,Address,Salary)
VALUES (3, 'Sam', 21, 'California', 40000.00),
(1, 'Karan', 25, 'Londan', 25000.00),
(4, 'Jenny', 30, 'Dubai', 31000.00),
(2, 'Jon', 28, 'Mumbai', 28000.00);

Explanation

In the above example, we inserted 4 rows into the table with a random order, as shown in the above statement. After that, when we execute the select statement, it is shown in ascending order which means every table has a by default indexing, and it physically reorders the data into the database table. Illustrate the end result of the above declaration by using the following snapshot.

PostgreSQL Clustered Index 2

Now let’s see how we can implement a clustered index as follows.

Example #2

CREATE UNIQUE INDEX "test_Age_Salary"
ON public.test USING btree
(age ASC NULLS LAST, salary ASC NULLS LAST)
WITH (FILLFACTOR=10)
TABLESPACE pg_default;

Explanation

With the help of the above statement, first, we created an index for two columns such as Age and Salary, and we arrange them in ascending order as shown in the above statement. After that, we use the alter command as follows.

ALTER TABLE public.test
CLUSTER ON "test_Age_Salary";

Explanation

In the above statement, we use the alter command to implement a cluster index in PostgreSQL. Here we use the public. test to access table names, and we created a clustered index on the Age and Salary column of the test database table. Illustrate the end result of the above declaration by using the following snapshot.

PostgreSQL Clustered Index 3

So we created a clustered index by using the above two statements, and we want to see clustered table details, so we use the following syntax as follows.

 Syntax:

\d table_name;

Explanation

In the above statement, we \d means describe table command with the table name, and it is used to show all detailed structure of the table like the primary key of the table, clustering of the table, and data type of column or we can say that size that means it shows all detail structure of the table.

Notes

  • When you create a database table with a primary key or any other index, then you can execute cluster commands by specifying the index name to get the physical order of the database.
  • PostgreSQL cluster is used to specify table names based on the index name, and the index must be already defined on the table.
  • The clustering index shows the physical ordering of data as per the clustered index of the table.
  • We can update the table as per our requirement, but changes are not allowed in clustered because clustering is a one-time operation. That means clustering is not storing any new or updated rows.
  • As per our requirement, we can recluster the table by using the cluster command.
  • For the execution of cluster commands, we required access to the
  • When we execute a cluster command, it creates a temporary copy of the table, so we need free space for that.

Uses

  • The main purpose of clustering is to rearrange the database table based on a specific index. It involves sorting the table according to that index to improve query performance.
  • When creating a cluster, it creates a temporary file on another disk location. This temporary file preserves the original content of the table even after executing an update command on the table.
  • We use a clustered index to reorder data within tables.
  • In clustering, when we insert a new record into the table, then it automatically inserts at the end of the table.
  • Clustering indexes make faster queries to access data from the tables.
  • Cluster indexes are unique indexes, as per the table.
  • We utilize clustering based on our requirements to enhance database performance. By leveraging the cluster index, we can increase the speed of the database.

Conclusion

We hope you understand the PostgreSQL Clustered Index from this article. From the above article, we have learned the basic syntax of the Clustered Index. We have also learned how we can implement them in PostgreSQL with the examples of the Clustered Index. Clustering physically reorders data within a database table. From this article, we have learned how we can handle the Clustered Index in PostgreSQL.

Recommended Articles

We hope that this EDUCBA information on the “PostgreSQL Clustered Index” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL Limit Offset
  2. PostgreSQL Float
  3. Client PostgreSQL
  4. PostgreSQL DELETE

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW