Wednesday, March 13, 2024

Postgres indices on large tables : gotchas

When you run a migration to create an index on Postgres, to allow queries to run, we create the index using the `CONCURRENTLY` flag.

But if the migration fails for any reason, the index will be created but unusable. Say, you were adding a UNIQUE index, and the migration fails since it encounters a duplicate. So the migration fails with something like:

Now imagine that your migration needed to make an existing index UNIQUE. It is usually done by first removing the index, and adding it back with the UNIQUE constraint.

Since the migration is aborted at the point of creating the new index with the constraint, now there is no index. The app will be running without an index.

If you were to check the db, you might get confused as the index *seems* to be there - it is just not operable.

To verify, run an `EXPLAIN` and you will see it does not use the index.

Now the queries that regularly use the index will be running quite slowly, as the index is not quite ready. This could add a lot of pressure to the db and make the app pretty much unusable / inoperable.