I have frequently found myself in situations when I had to reindex a few indexes (because the index got bloated a lot), and I always have to lookup the exact commands, after searching quite a bit, just to be sure that I am doing the right thing and not making a mistake.
In the past, I have referred to the articles I have written multiple times, and I thought I need to create another Reference Guide for myself for this. Hopefully, others on the internet will also found this reference useful.
Some basic tips:
- If you have a large and/or a table which gets a lot of traffic, remember that a plain
REINDEXcommand will take a lock on the table that won’t allow any
writeoperations on the table till the command completes. Reindexing
CONCURRENTLYis almost always a better option out. You can read more about it in the official docs: Building Indexes Concurrently.
- Remember to
set statement_timeout = 0;before running the reindex command since
CREATE INDEXcommands are also taken as
statementsby Postgres, and they will be killed if they go above a decided threshold.
- To figure out indexes that a table has and the corresponding bloat percentage for each of them, you can use this query (we picked it up from PgHero’s codebase). We add a
table_name = 'my_sweet_table'to the
WHEREclause at the end of the query to only get the indexes for our table, but that is completely optional.
- You can also use a simple query to get the definition of all the indexes for a table. These definitions can be used as is when we want to recreate them.
Recreating Indexes supporting Foreign Key constraints
Indexes that are not created for a constraint can be reindexed in the same way.
We have the definition of the original index, we can just replace the name with a temporary name and use
CREATE INDEX CONCURRENTLY new_idx ON my_sweet_table USING btree (my_fk_column);
Now you can safely drop the original index. You can optionally use
CONCURRENTLY here as well, read more about it in the docs: Drop Index.
DROP INDEX my_lovely_index;
You could also rename the new index to the original name (some frameworks, like Django, autogenerate index names using the table name, the app’s name, and a hash of both of these plus the columns of the model. You might want to preserve that name).
ALTER INDEX new_idx RENAME TO my_lovely_index;
Also, if the index you are recreating is a unique index, you can add the keyword
UNIQUE to the
CREATE INDEX command.
Recreating Indexes supporting Unique constraints
Recreate the Index, with the keyword
CREATE UNIQUE INDEX CONCURRENTLY new_uniq_idx ON my_sweet_table USING btree (col_a, col_b);
Now, we want the constraint to use this new index. For that, we drop the original constraint, and add a new unique constraint that uses our new index. This is done in one atomic statement so that there is no time when there is no constraint on the table.
We don’t have to rename the index this time as Postgres automatically renames it to the name of the constraint.
ALTER TABLE my_sweet_table DROP CONSTRAINT uniq_constraint_777, ADD CONSTRAINT uniq_constraint_777 UNIQUE USING INDEX new_uniq_idx;
Recreating Indexes supporting Primary Key constraints
This is achieved in the same manner as we did for recreating the index for a unique constraint. The only difference is that this time the constraint that we add is a
PRIMARY KEY constraint, of course 😀
CREATE UNIQUE INDEX CONCURRENTLY new_pkey_idx ON my_sweet_table USING btree (id); ALTER TABLE my_sweet_table DROP CONSTRAINT my_sweet_table_pkey, ADD CONSTRAINT my_sweet_table_pkey PRIMARY KEY USING INDEX new_pkey_idx;
That’s it! Tadaaaa.