A true Multi tenant SaaS application uses shared database with shared schema to achieve better cost efficiency and maintainability. But over a period of time when the application grows with 100s, or 1000s of tenants, the data stored in the database might also grow exponentially, due to which the application’s performance (while fetching the data) may slow down. Indexing the data might be a good option to improve the speed but it is also resource intensive. So what can we do to enhance the performance?

Tenant Based Indexing

Partial Indexing

It is also called as selective/Partial/Filtered indexing, as it indexes based on selective conditions. For example, there might be situations where, for a certain tenant, we may need to fetch records based on their most used column values, but the same column may not be the frequently used one for other tenants. Like this different Partial indexes can be created for each tenant depending on the unique data usage patterns of each tenant.
Note : While indexing non-unique columns, it is recommended to index the composite value (combined with a unique identifier).

Partial-Indexing-On-Widely-Used-Columns

Advantages of partial Indexing

• It takes lass space when compared to full indexes
• Increases the performance of the query.
• Considerable performance improvement when issuing DML statements since the partial index is only on selective data