Recenlty at Attribution, we've been implementing materiazlied views to speed up slow queries. Although the concept is nothing new, and Materialized Views have been a feature of Oracle for years. They're a new feature in Postgres 9.3.
One missing piece in 9.3 is the ability to refresh views concurrantly, without exclusivly locking the whole view. Postgres 9.4 introduces this feature, which is great if you manage your own DB. However, those of us that use hosted database solutions, such as Amazon RDS, are not so lucky.
So we were faced with a couple options:
- Hold off on materizlied views until 9.4 is available for RDS,
- Move DB providers to something that supports 9.4, or
- Hack something together.
We chose the latter of course. Our solution: to copy the view (which is pretty quick) and swap it out for the copied version while we refresh the view. Here's a sample of the SQL (our view is called 'visitors'):
CREATE TABLE visitors_copy AS (select * from visitors);
BEGIN;
ALTER MATERIALIZED VIEW visitors RENAME TO visitors_refreshing;
ALTER TABLE visitors_copy RENAME TO visitors;
COMMIT;
REFRESH MATERIALIZED VIEW visitors_refreshing;
BEGIN;
DROP TABLE visitors;
ALTER MATERIALIZED VIEW visitors_refreshing RENAME TO visitors;
COMMIT;
Now, wherever we would normally call REFRESH MATERIALIZED VIEW, we run the script. Although, it
doesn't copy over the indecies on the materialized view, I don't think that would be hard to add on.
We're pretty sure this will hold us off until RDS supports Postgres 9.4.