Speeding up views: Caching - agileChilli
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. One of these we support is 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.
The view can however be set to cache the data. The database only does the calculations periodically, then results can be viewed immediately as soon as someone looks at the report.
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: to save unnecessary work for the server, it won’t be updated, if no one looks at the view.
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.