We are working on a multi-tenant SaaS product that has a ledger of all tenant customer transactions, which we use to track invoice states on a line-item level. This particular application will result in a lot of upstream interactions with service providers which results in a lot of line-item debits and credits. The point being that a simple action such as generating an invoice and receiving payment, could quite easily lead to 50-100 database entries.
We use a normalized relational database as we a) need to do various different joins to display the data from different vantage points, and b) need to present the data at different levels of granularity.
For instance, the application allows different customers to execute purchases on different accounts. The system allows one to view a transaction history on an account level, or to view transactions on a customer level across accounts (these are just two examples, there are many more). Ito granularity, one might want to view all debits and credits for a single invoice, or one might want to view a debtors aging analysis, which will aggregate all records for a single tenant.
To ensure theses queries run in a timeous fashion, we've indexed the hell out of the database. This works reasonably well for small tenants where query times are in seconds, but the aggregate queries are into the minute mark for large customers. Ideally we would like all queries to run sub-second.
We've been toying with the idea of building indexed views, but given that this OLTP system is quite chatty, we are concerned that table locks (caused by the updates in referenced tables) will cause all tenants to suffer when changes are made (as naturally the indexed views would need to be recalculated).
Building a data warehouse, summary tables, or some sort of data cube is not ideal as it too would need to be updated in real-time since changes need to reflect immediately and we would no longer have a single source of truth. Furthermore, cost is a big concern too. Our cloud expenses are through the roof.
At this stage, we are thinking of building a hybrid data access layer which will cache certain views in Redis and persist the data to the source database. The views will then be updated after the TTL expires or if any records the view touches are updated. Any thoughts on this approach?
