PostgreSQL

REINDEX Postgres

Like many commands of PostgreSQL Database, the REINDEX command comes very handily when it comes to refreshing the database system. It has been formed to recreate the already made INDEX of your database. There may come some situations when there is some problem with your database data due to software and hardware problems. These problems may lead to invalid data in your database, and views cannot fetch the required records. This is the time to recover our already made views to use them again using the REINDEX instruction. This article will discuss the method to refresh the already made indexes in the PostgreSQL database.

Using PostgreSQL PgAdmin GUI:

Let’s take a new start from our Windows 10 desktop screen. Make sure your Windows 10 has already a PostgreSQL database configured fully to use it. To open the GUI of the PostgreSQL database, tap on the Windows 10 search bar from the left bottom corner of your desktop screen. Write “pgAdmin”, and the application will pop up. Select the pgAdmin 4 to open it quickly. After selecting, it will take up to 20 seconds to open normally.

A launch will require your database Server password, which you have added at the time of installation. Add that password and tap on the “OK” button to continue using it. On the left-most side, you will see the option “Servers”. Expand it to see the databases. Expand your database option, and tap on the database you want to use for this implementation. We have been using “aqsayasin” here. Click on the query tool icon to open the query area for the database, i.e., “aqsayasin”.

Before doing anything, we need to list all the already made indexes of our database. For that, you need to use the SELECT instruction using “pg_indexes” of public schema fetching the column “schema name”, “table name”, “index name”, and “index def” from it. We have got a total of 6 indexes that are already there in our database “aqsayasin” with this query.

You can see the names of indexes already here in the 3rd column from the left side from the above output. Let’s REINDEX the “in_new” index of table “New” and schema “public” using the REINDEX command here. Use the “REINDEX” keyword with the name of the index preceded by the keyword “INDEX” as shown in the query below. The execution of this query was successful as far as the output is showing the query success message. Now, this index is refreshed, and if there is an issue, it will be no more.

Not only indexes, but the REINDEX command can also be cast-off to refresh the whole table of your database. For that, you need to add the name of a table instead of Index. Let’s reindex the table “Atest” with the REINDEX command followed by the “TABLE” keyword before the table name. On running this query, we encountered the success query message once again on our query output area. The table “Atest” is also reindexed or recreated for use.

Not only tables and indexes, but you will also be using the REINDEX command to refresh the whole schema of your system. This means, on reindexing the schema, all your databases or tables will be refreshed with a single query. Thus, we are using the REINDEX query in our query tool once again to reindex the “public” schema of our database. Use the keyword “SCHEMA” after the REINDEX keyword and before the name of a schema within this query. On execution, your whole schema will be reindexed, and it will show you the success message after a while, i.e., it takes a little more time than a usual query. This query will reindex all the databases on your system, i.e., Postgres and others.

To refresh a single database with many tables, we can also use the REINDEX command. You just need to use the keyword “DATABASE” with the name of a database to be reindexed preceded by the keyword REINDEX. Let’s reindex the default database “Postgres” while using the query tool of another database, i.e., “aqsayasin. You will encounter an error shown in the image beneath on this query execution. It’s stating that we are currently using another database and trying to reindex the “Postgres” database. Thus we are unable to do what we want to do. Thus, we can only reindex the database we are working on. Let’s update our command.

We have replaced the name of a database in the same query, i.e., “aqsayasin”. The REINDEX query is now successfully after execution showing that the database is reindexed properly.

Let’s use the keyword SCHEMA within the same REINDEX command to refresh or recreate the indexes found in our currently opened database catalog, i.e., “aqsayasin”. This will recreate the shared indexes as well. On this instruction execution on the query tool, we have been successful so far.

Using PostgreSQL Shell (psql):

The same functionality can be achieved with the PostgreSQL Shell. Open the PostgreSQL shell, searching the “psql” keyword in the search bar. Tap on the MySQL shell application and write the name of the local host you have been using. Now, make sure to add the correct name of your database in which you want to do reindexing along with the correct port number. After this, you will be urged to add the username and password for your database user. You can also go with the “Postgres” database and username, i.e., default. Your shell will be ready for use.

Let’s see how many indexes we have in our “Ftest” table. To list them, use the “\d+” command with the table name “Ftest”. The output below shows the total of 2 indexes found in this table so fat, i.e., iftest and inftest. We will be reindexing these indexes in the coming queries.

Using the same “\d+” command, we have got the indexes displayed for the table “Values”. It shows the single index for this table as below. We will be reindexing it as well.

Let’s start reindexing the table “Ftest” first. Cast off the “REINDEX” instruction on the PostgreSQL shell with the table name, i.e., Ftest. It’s successful, and the table is reindexed now. The resultant word “REINDEX” is the proof of our success here.

To reindex the PostgreSQL schema named “public”, we have to use the same REINDEX command with the title “SCHEMA”. It will be successful again as per the result. The word “REINDEX” is the success message on our screen.

To recreate or deindex a single database, you must specify the database name. Only the records related to this database will be indexed, and no other database will be modified.

You will find yourself unable to reindex the other database other than the currently opened one as per the given command below.

Conclusion:

We have learned about the concept of REINDEX in the PostgreSQL database. We have covered the examples to reindex the existing INDEX for a particular table and reindexed all the indexes found in the other tables in a single step. This whole concept of reindexing is used to refresh or recreate the indexes for tables, databases, and schema, i.e., all in one. All the new users of the PostgreSQL database will find this article quite useful for learning.

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.