PostgreSQL

How PostgreSQL Creates an Index Concurrently

PostgreSQL provides an efficient feature of creating an index that is “concurrent indexes.” This feature allows us to create an index on the relation without blocking the reading and writing facility. This is not easy to manage data in the PostgreSQL database. The purpose of creating concurrent indexes can be several that include the number of circumstances in which; not blocking the writing feature on the table production is the most common one.

Whenever we use this option in the command, PostgreSQL builds the index without applying any lock that can prevent the insertion, updates, or deletion concurrently on the table. There are several types of indexes, but the B-tree is the most commonly used index.

B-tree Index

A B-tree index is known to create a multi-level tree that mostly breaks the database into smaller blocks or pages of fixed size. At each level, these blocks or pages can be linked with each other through the location. Each page is called a node.

Syntax

CREATE INDEX Concurrently name_of_index ON name_of_table (column_name);

The syntax of the simple index or the concurrent index is almost the same. Only the word concurrent is used after the INDEX keyword.

Implementation of Index

Example 1:

To create indexes, we need to have a table. So, if you have to create a table, then use simple CREATE and INSERT statements to create the table and insert data. Here, we have taken a table already created in the database PostgreSQL. The table named test contains 3 columns with id, subject_name, and test_date.

>> select * from test;

Now, we will create a concurrent index on a single column of the table above. The command of index creation is similar to table creation. In this command, after the keyword creates an index, the name of the index is written. The table’s name is specified on which the index is made, specifying the column name in the parenthesis. Several indexes are used in PostgreSQL, so we need to mention them to specify a particular one. Otherwise, if you don’t mention any index, the PostgreSQL chooses the default index type, “btree”:

>> create index concurrently ''index11'' on test using btree (id);

A message is displayed that shows that the index is created.

Example 2:

Similarly, an index is applied to multiple columns by following the previous command. For example, we want to apply indexes on two columns, id, and subject_name, concerning the same previous table:

>> create index concurrently "index12" on test using btree (id, subject_name);

Example 3:

PostgreSQL allows us to create an index concurrently to create a unique index. Just like a unique key that we create on the table, unique indexes are also created in the same way. As the unique keyword deals with the distinctive value, the distinct index is applied to the column containing all the different values in the whole row. That is mostly considered as the id of any table. But using the same table above, we can see that the id column contains a single id twice. This can cause redundancy, and data will not remain intact. By applying the unique command of creating the index, we will see that an error will occur:

>> create unique index concurrently "index13" on test using btree (id);

The error explains that an id 6 is duplicated in the table. So the unique index cannot be created. If we remove this duplicity by deleting that row, a unique index will be created on the column “id”.

>> create unique index concurrently "index14" on test using btree (id);

So you can see that the index is created.

Example 4:

This example deals with creating a concurrent index on specified data in a single column where the condition is met. The index will be created on that row in the table. This is also known as partial indexing. This scenario applies to the situation where we need to ignore some data from the indexes. But once created, it is hard to remove some data from the column on which it is created. That’s why it is recommended to create a concurrent index by specifying particular rows of a column in the relation. And these rows are fetched according to the condition applied in the where clause.

For this purpose, we need a table that contains Boolean values. So, we will apply conditions on any of one value to separate the same type of data having the same Boolean value. A table named toy that contains toy id, name, availability, and the delivery_status:

>> select * from toy;

We have displayed some portions of the table. Now, we will apply the command to create a concurrent index on the availability column of the table toy by using a “WHERE” clause that specifies a condition in which the availability column has the value “true”.

>> create index concurrently "index15" on toy using btree(availability) where availability is true;

Index15 will be created on the column availability where all availability value is “true”.

Example 5

This example deals with creating concurrent indexes on the rows that contain data with lowercase. This approach will allow effective searching of case-insensitivity. For this purpose, we need to have a relation that contains data in any of its columns in both upper and lower case data. We have a table named employee having 4 columns:

>> select * from the employee;

We will create an index on the name column that contains data in both cases:

>> create index on employee ((lower (name)));

An index will be created. While creating an index, we always provide an index name that we are creating. But in the above command, the index name is not mentioned. We have removed it, and the system will give the name of the index. The lower case option can be replaced by the upper case.

View Indexes in pgAdmin

All the indexes that we created can be seen by navigating towards the left-most panels in the dashboard of pgAdmin. Here on expanding the relevant database, we further expand the schemas. There is an option of tables in schemas, expanding that all the relations will be exposed. For instance, we will see the index of the employee table that we have created in our last command. You can see that the name of the index is shown in the index portion of the table.

View Indexes in PostgreSQL Shell

Just like pgAdmin, we can also create, drop, and view indexes in psql. So, we use a simple command here:

>> \d employee;

This will display the details of the table, including the column, type, collation, Nullable, and the default values, along with the indexes we create:

Conclusion

This article contains the creation of index concurrently in a PostgreSQL management system in different ways so that the created index can discriminate from one another. PostgreSQL provides the facility of creating index concurrently to avoid blocking and updating any table through the read and write commands. We hope you found this article helpful. Check out other Linux Hint articles for more tips and information.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.