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 table on secondary storage. Clustered index is used to uniquely identify rows from a table. When we talk about clustered indexes that contain groups of similar items or we can say properties that are clustering. 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.
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 that means changes in clustering does not allow. Index name means index name. VERBOSE is used to print progress reports of database tables in clustered.
How to Use a Clustered Index in PostgreSQL?
We must install PostgreSQL in your system. We required basic knowledge about PostgreSQL. We must require a database table to create clustering indexing. We just need basic knowledge about the clustering indexing that means how it is used. 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 better understand as follows.
Example #1
For the implementation of 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.
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 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.
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 by 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.
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 table like the primary key of the table, clustering of 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 then it creates a temporary copy of the table so we need to free space for that.
Uses
- Mainly Clustering index is used to resort to the database table by using a specific index from the table.
- Cluster is created as a temporary file on another disk location so it keeps the original content of the table after performing update command on the table.
- A clustered index is used to reorder data from 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.
- Basically, the Cluster index is used to speed up the database performance so we use clustering as per our requirement to increase the speed of the database.
Conclusion
We hope from this article you have understood about the PostgreSQL Clustered Index. 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 is used to physically reorder data from a database table. From this article, we have learned how we can handle the Clustered Index in PostgreSQL.
Recommended Articles
This is a guide to PostgreSQL Clustered Index. Here we discuss the definition, How to Use a Clustered Index in PostgreSQL? and examples respectively. You may also have a look at the following articles to learn more –
