3

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?

6
  • "... 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". How many different accounts are there? Nov 18, 2016 at 14:37
  • A couple thousand. I say "very slow" but that's not exactly accurate - it's just too slow to run on every page load (about ~2 seconds).
    – Andy Baird
    Nov 18, 2016 at 15:55
  • Have you looked into materialzied views?
    – JeffO
    Nov 18, 2016 at 18:04
  • Yes. See my second bullet point.
    – Andy Baird
    Nov 18, 2016 at 19:53
  • Could you explain why the view "can't be scoped to just the account looking at the data"? Nov 18, 2016 at 22:39

3 Answers 3

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.

1
  • It's critical that the data is kept up to date in real time, so I don't think a full blown data warehouse would be appropriate. Several of the approaches I mentioned take advantage of caching and denormalization already.
    – Andy Baird
    Nov 18, 2016 at 15:59
0
  • 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

18
  • I really can't imagine sorting and filtering on the client side - that seems like an objectively bad idea given the potential volume of the data set. Even keeping it in the server side code seems a bit foolish - databases are designed to efficiently sort and filter on your data. I can think of some edge cases where that might be a good idea, but not many where it should be your go-to option.
    – Andy Baird
    Nov 21, 2016 at 6:50
  • its pretty standard behaviour for websites these days. If the user changes these options on the fly then you will actually send less data this way
    – Ewan
    Nov 21, 2016 at 8:26
  • Sorting and filtering on the client side is standard behavior? Maybe on small datasets, but I don't think that's the norm for large sets of paginated data. I also don't see how you'd ever send less data unless you're talking about filtering on only 1 page worth of data at a time, otherwise you'd have to download all pages of data at once.
    – Andy Baird
    Nov 29, 2016 at 2:49
  • yes download all data.
    – Ewan
    Nov 29, 2016 at 7:38
  • Yeah, that's definitely not a good idea in my case. I can't think of any other website that would download force a user to download megabytes of data on page load just to accomplish client side filtering and sorting. That sounds like a nightmare for the end user.
    – Andy Baird
    Nov 29, 2016 at 22:32
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.