Effectively Using Materialized Views in Ruby on Rails

I once worked on a larger Rails project where we had decided to utilize Postgres views and materialized views to optimize the performance of parts of our application. This was about 7 years ago, so before Scenic existed to help with this (or at least before it was known). We had to manually manage the use of our views while keeping Rails apprised of the underlying schema through custom schema files and migration methods.

One of the main issues we had run into, in addition to all the complexity that comes with managing a cache and no longer being able to count on your db queries to return the latest information, was that our views had to be deleted and re-created for nearly every schema change (at least every schema change that affected the views, but we had so many views, this ended up being probably 90% of our schema changes).

We ended up overriding the standard Rails "up" and "down" migration methods to prepend the deletion of these views, append their re-creation, clean up after failed migrations, etc. I remember us spending a good amount of time across the team dealing with weird edge cases that cropped up from this. I also remember spending a fair amount of time training all the developers on these issues since almost no one on the team had experience using or dealing with database views. I assume managing migrations and schema of your views within Rails is all included in what Scenic does for you for free, since these hassles weren't mentioned in the article.

It's really nice to see something that handles all that complexity for you. However, that still leaves the standard and age-old caching issues with stale data. Another side effect was that they started being a bandaid for inefficient queries and data structures in the database, which is probably how we ended up with so many db views in the first place.

In the end, after probably 8 months of using these views, we decided to excise them from the application entirely and go back to optimizing our actual database queries. We got to delete a lot of custom code, our schema changes and migrations became simpler, our data became fresh again, and we got to reinvest that time into improving our underlying data structures and queries.

This probably is not an argument against using db views, but rather an anecdote of what can happen when you resort to them prematurely. If you have queries that are 100ms or more, there are probably more traditional optimizations you can find, such as restructuring your relational data, adding/removing indexes, etc. If you're actually trying to eliminate those last 10s of milliseconds from your queries though, as is shown in the article, I can see them being a good option.

Are there any sensible ways to do this with Django? Ideally with migrations to create and update the views.

> Are there any sensible ways to do this with Django? Ideally with migrations to create and update the views.

There are apps to integrate PG views as django models, however is kind of trivial to do it yourself.

1. Create a migration that runs the SQL to create the view like ```create VIEW active_users AS SELECT rest of view ... `

2. Create a model mapping all the fields you want to use, add the Meta.table_name with the name of the view, and Meta.managed=False to avoid adding this to your migrations in the future.

Use it as any other normal Model.

Just that

About the updates with migrations I think if you already are thinking on using VIEWS, you could just create migrations that drop and recreate it when you needed. Is what I do, and is very little work for that.

Trigger would work. But going the pg_notify approach could allow for asynchronous updating whereas the trigger approach could slow writes.

It also seems odd to me that the refresh is scheduled in the app instead of the database.

Can anybody here speak to that choice?