Using ‘materialized views’ to cache data

Using ‘materialized views’ to cache data

Last week’s update was about helping system administrators manage large accounts. This week’s is about improving the performance for users when those accounts contain very complex reports that may slow the system down if action isn’t taken.

The PostgreSQL database that underlies agileBase is highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. It has a sophisticated ‘query planner’ built in to optimise complex reports and many other ways in which reporting can be speeded up. Today, we add support for one of these, called ‘materialized views‘.

A materialized view is basically a ‘cache’ of view data. Say you have a view which contains many calculations over lots of rows. An example could be a profit and loss report for the last decade that totals up monthly costs and income over millions of purchases and sales per year. This report only changes slowly over time. Usually in agileBase, this data will be re-generated every time you view it, which may take a number of seconds.

Now, the view can be set to cache the data. The database only does the calculations once a day, then results can be viewed  immediately as soon as someone looks at the report.

Setting up

In a view’s manage tab, click ‘advanced options’

For the ‘cache view rows’ option, select a value. You can either choose to update the view daily or every ten minutes.

Note that if no one looks at the view, it won’t be updated, to save unnecessary work for the server.

Notes

This is a feature that should’t be over-used. In fact, it should only be used once other speedup strategies have also been implemented or tried such as

• adding filters to the view to only show the subset of data that’s needed
• adding indexes (ask us)
• increasing the memory available to the view
• altering filters and calculations to speed them up

The reason is that even when you use materialised views, the system can spend a lot of time refreshing them. If a view takes longer than a certain amount of time to refresh, a database timeout will occur and no results at all will be returned.

So if you’re worried that a view seems slow, please get in touch and we can analyse it. The agileBase monitoring system also proactively highlights the views taking the most time to process so we can investigate.