Postgres - indexing multi-tenant database

Question

Our multi-tenant application uses Postgres database. The application was created many years ago, and now we are seeing some performance issues when load increases.

Every database table we have has at least two indexes - Id (PK) and TenantId (FK). On every select we added TenantId filter, and when we fetch an item by Id there also TenantId is added in the where clause.

Now, my question is that did we do it wrong. Instead of adding two indexes we should have added index on (TenantId, Id) and that would have improved our query performance? Would this have improved both the scenarios - when selecting using tenantId and tenantId + Id?

Answer 1

Yes, that single index would serve both queries. But you cannot get rid of the index on id, since it is necessary to implement the primary key constraint.

I would research if the indexes are really the cause of your performance problem.