Dan Chak’s book Enterprise Rails shows us how to implement materialized views, and how to keep them up to date with database triggers.
The performance benefits are remarkable, and I think it will be easy to test that the materialized view stays up to date correctly: every once in a while, we can compare the unmaterialized view to its materialized version to make sure they match. This is called Gold Master Testing, except that in this case we can do it on live production data.
But the sheer amount of psql code needed to implement one of these successfully is very bothersome. Tens of different functions and triggers, and hundreds of lines of code for any non-trivial view. I wrote some ActiveRecord migration helpers to generate some of this boilerplate code automatically, but this still results in a lot of repetitive psql code at the database level.
Jonathan Gardner notes:
I do not recommend that PostgreSQL add Materialized Views in its core. Why?
- Materialized Views are insanely complicated, and you cannot get past their arbitrary requirements and limitations. In other words, they are not a very good abstraction because they leak details of the underlying implementations.
- Implementing Materialized Views properly is not easy. Until it is, we should avoid a general solution, relying instead on particular, detailed solutions.
True, the materialized view to be presented to users is often extremely complicated. But if we can break the view up into many smaller, simpler, cascading materialized views, and if we can write those simpler views such that they follow common patterns, then maybe it would be possible to automate much of this. (This is the approach I took in order to be able to make use of the ActiveRecord migration helpers).
Next steps include adding features to the ActiveRecord migration helpers:
- N to 1 refresh triggers
- Invalidation functions for lazy refresh, rather than just refresh functions for eager refresh
- Conditions on when update is needed (if a row is updated but only a non-important column, then maybe a refresh is not necessary)
Then maybe if the ActiveRecord migration helpers seem to be working, I can start to think how to do it one level lower, at the database level