Are you looking to optimize your database, the best go-to way is to add an index to execute your query faster, but this blog is not about adding indexes but removing them, why do we need to remove indexes from our table? Having lots of indexes can have several disadvantages, including:

1. Increased disk space usage: Indexes can take up a significant amount of disk space, and having too many indexes can quickly consume a large amount of storage.
2. Increased overhead for insert, update, and delete operations: Each time data is modified in a table with indexes, the corresponding indexes must be updated. This can slow down these operations and increase the time it takes to write data to the database.
3. Increased query optimization time: The more indexes that exist on a table, the longer it may take for the database to determine the optimal query execution plan. This can result in slower query performance.
4. Index fragmentation: As data is inserted, updated, and deleted in a table, indexes can become fragmented, which can lead to slower query performance.
5. Increased memory usage: The database may need to cache multiple copies of the same data to support different index structures, which can increase memory usage.

How to identify which indexes need to be deleted?

SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
   JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT i.indisunique   -- is not a UNIQUE index
  AND NOT EXISTS          -- does not enforce a constraint
         (SELECT 1 FROM pg_catalog.pg_constraint c
          WHERE c.conindid = s.indexrelid)
  AND NOT EXISTS          -- is not an index partition
         (SELECT 1 FROM pg_catalog.pg_inherits AS inh
          WHERE inh.inhrelid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

Reference: https://www.cybertec-postgresql.com/en/get-rid-of-your-unused-indexes/

In my case, our team has found 1109 such indexes in our db which was been used by our monolithic software. A space of 110 GB has been freed. This made me realize how badly we were maintaining our Database. Anyhow let this be a lesson to our future selves