Fix database bloated indexes

Warning:

Local installations of Flow Production Tracking are no longer offered. This documentation is intended only for those with existing instances of Flow Production Tracking Enterprise Docker. Click here for a list of our current offerings.

Follow these steps to find and rebuild bloated indexes in Local Install:

Important:

Before performing operations that modify the database, such as recreating its indexes, it is recommended to create a backup of the database. This precaution allows you to restore the database to its previous state if something goes wrong during the operation.

Open your terminal application.
List all running containers executing the following command:
```
$ sudo docker ps 
```
Find the Flow Production Tracking container name. In this case, the name is shotgun_app_1.
Start Rails Console by executing the following command:
```
$ sudo docker exec -it shotgun_app_1 script/console
```
To list the bloated indexes execute this command. This command does not change the database.
```
IndexHelper.list_bloated_indexes
```
The output will list all the bloated indexes that should be rebuilt. The Invalid column is a boolean field (F == False). Invalid indexes cannot be used by the database. Invalid indexes normally appear when the command to create an index times out. Remove the Time Out and recreate the index.
Use this command to rebuild the bloated indexes.
```
IndexHelper.find_and_replace_all_bloated_indexes 
```
Note:

This command will lock tables for the primary key indexes. We recommend running this command out of working hours as it might cause slow performance.

We recommend regularly addressing bloated indexes to maintain the efficiency of your maintenance operations.

Follow these steps to find and rebuild only one bloated indexes in Local Install:

Open your terminal application.
List all running containers executing the following command:
```
$ sudo docker ps 
```
Find the Flow Production Tracking container name. In this case, the name is shotgun_app_1.
Obtain the Database password by executing the following command:
```
docker exec -it shotgun_app_1 env | grep POSTGRES_PASSWORD 
```
Now connect to the Database by executing the following command:
```
docker exec -it shotgun_app_1 script/dbconsole 
```
Type the password to open the Console.
To recreate only one index, execute this command in the PostgreSQL Console:
- PostgreSQL version 12 and later. This command allows you to recreate an index without locking the table, meaning other operations on the table can continue while the index is being recreated.
```
REINDEX CONCURRENTLY INDEX index_name 
```
- PostgreSQL version 11 and older that supports the REINDEX command. This is the standard command to recreate an index. However, it will lock the table while the index is being recreated, preventing other operations on the table during this process.
```
REINDEX INDEX index_name 
```

Fix database bloated indexes

Related pages

Was this information helpful?