What are the different approaches to filtering on aggregated data?

Question

I have a feature I'm building that aggregates data across about a dozen or so tables. This data is aggregated from a heavily joined query. This page then has a lot of filtering, sorting, counting, and display options that need to work on top of this aggregated data set. There is one "global" condition/filter on the page so that it only loads data for the company viewing the page.

The site is built in Rails using Postgres as the database. Much of the filtering functionality is already built out using scopes on an ActiveRecord model of the aggregated data.

I've arrived at the following possible ways to solve this:

• Execute the full entire query each page load. This doesn't seem like a good approach because the entire query would need to be executed several times for things like different counts on the page
• Use a database view (or a materialized view). This works very well for the filtering, sorting, counting needs, but building the view is very slow since it has to look across the entire dataset and can't be scoped to just the account looking at the data. If I go with a materialized view, I have to manually update the view at different logical points in the app.
• Use a temp table. This would seem to have all the benefits of a view but load fairly fast since the temp table query would be scoped to just company viewing the page. The main problem is I haven't found a good way to have an ActiveRecord backed by a temp table (especially since the temp table query needs the context of which company is viewing)
• Use a physical table. This has the same advantages of a database view, except refreshing the table should be faster since the refresh process can execute just for a specific company' dataset.

I'm having a hard time weighing the pro's and con's of each approach. Is there anything I'm missing? Any other pros/cons or approaches to this?

Answer 1

Some techniques to consider:

Caching

Does the business need the aggregate data to be 100% up to the second accurate? If not, perhaps you can cache the results of the aggregates to save re-doing some of the calculations.

Data-warehouses take this further, often calculating aggregates on a fixed schedule so that, whilst they're not up-to-the minute accurate, they are available fast.

De-Normalization

For highly transactional data, it is usually best practice to normalize data as much as possible. But for aggregates, it is usually more performant to de-normalize the data. Data warehouses often employ snowflake / star schemas to achieve higher performance.

OLAP

OLAP databases combine the two techniques above. They are designed to support management reporting, trend-analysis etc.

Example

My business runs a set of ETL processes overnight every night to populate our data warehouse with a copy of all data needed for management reporting. Its schema is heavily de-normalized, and aggregates and other intermediate results are calculated as part of the load process. Reports run from this database are very fast. It doesn't matter to the business that the data is up to 24 hours out of date.

Answer 2

• Execute the full entire query each page load.

Although computationally inefficient, the responsiveness of the app/page is improved by applying the filters/sort in memory (ideally on the client) in response to the users actions.

You can overcome these inefficiencies by scaling your back end accordingly. But you cant overcome the speed of the data round trip. so an app that takes this approach is likely to be 'better' (if more expensive) than one which does the sorting in the database

Answer 3

One other option that is not expressed in the question is to use SQL common table expressions to prepend the query with a WITH statement to essentially create on the fly queryable datasets. Example: https://www.tutorialspoint.com/postgresql/postgresql_with_clause.htm

Pro's for this approach:

• Avoids the use of any kind of storage (possibly stays completely in memory?)

Con's for this approach:

• A CTE would have to be created for every single query on the page. This would end up being time consuming in my case, where several count queries are run.
• No out of the box support for this with ActiveRecord, although postgres_ext appears to provide good support for this functionality