alphagov/transition — pull request #475 by rgarner (2014-12-05 11:03:18+00:00)
Title: Precompute large site hits

See also https://www.pivotaltracker.com/story/show/79766966

For sites we mark as `precompute_all_hits_view = true`, the rake task `import:hits:refresh_materialized` will (re-)create a Postgres `MATERIALIZED VIEW` with a name like `#{site_abbr}_all_hits`, e.g. `hmrc_all_hits`. When All Hits/All time is selected from Analytics, transition will use this view instead of running an expensive query to compute the counts live, which may often time out due to a lack of selectivity in the query (this issue doesn't apply to, for example, All Hits/Last 30 Days, as existing indexes can be used effectively there).

As a rough guide, if a query for all hits, all-time takes around 15s (timing out in nginx and resulting in a 504), use of this view will bring it back down to around 200ms at the cost of paying (less than) that 15s up-front when the view is created by the rake task.

The rake task will need to be scheduled separately - or we can consider adding the same lib call to `import:hits`.

Note: there is no UI facility for marking a site in this way. The site will need to be updated manually through a `rails console` or `psql`. At time of writing, we have two sites that have this problem: `hmrc` and `ofsted`, so it scarcely seems worth adding a UI overhead.