Is there a simple way to re-add missing database indexes?

Using Django Using the ORM
1

Briefly: I noticed today that many of the indexes were missing from my Postgres database’s tables. I’m wondering if there’s a way to compare the indexes that would be generated by the Django migrations, and apply them to the database if they don’t already exist?

Longer: It seemed quite random – some tables were missing a few indexes, others had none at all. Most of the ones with no indexes were later, if ordering tables by name, so I suspect that a dump and restore of the database at some point in the past went wrong.

I fixed this manually by:

  1. On my dev machine, creating a new database and running all the Django migrations on it.
  2. On there, in psql, doing:
    SELECT * FROM pg_indexes
WHERE indexname NOT LIKE 'pg_%'
ORDER BY tablename, indexname;
  3. Doing the same on the production database.
  4. Comparing the two lists and running the indexdef command on the production database for each missing index.

Obviously, this behaviour is not recommended :slight_smile: It’s not a life-or-death site and it seems to have gone OK. Only one table had some duplicate data that I had to carefully remove before applying a UNIQUE index.

So, besides wondering why I hadn’t noticed things being slow due to lack of indexes, I’m wondering if there’s a better way to get my database’s indexes in sync with those that Django’s migrations would generate.