System Design] How to design a grid view with millions of records?

The requirements are following: 1. Data could be aggregated differently. 2. Filtering of aggregated data. 3. Underlying data is huge. Around 10 tables with millions of records.

Currently we load everything into memory and based on the filter and aggregation level prepare data on back-end side and show it on UI. It works but it has multiple downsides:

- Initial load takes multiple minutes, because we need to query db and load all required data into memory, but consequent queries load much faster because we have this data already in memory. - Filtering logic is awkward and resembles "if column1.isSelected then filter" logic. - To be able to show actual data we need to update in-memory representation if we have smth new in DB. To do it we are polling DB and reload the data if it's stale.

To me this implementation looks too overcomplicated and not scalable (e.g. the app is limited how much data it can store).

What is the best approach of implementing such thing?