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
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.
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”:
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:
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:
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”.
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:
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”.
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:
We will create an index on the name column that contains data in both cases:
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:
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.
