Effectively Using Materialized Views in Ruby on Rails
1–10 of 37 posts
Re: Effectively Using Materialized Views in Ruby on Rails
#2How often does rails refresh the materialized views? And is it automatic or do you have to explicitly refresh them?
Re: Effectively Using Materialized Views in Ruby on Rails
#3How often does rails refresh the materialized views? And is it automatic or do you have to explicitly refresh them?
You typically do it on a schedule, e.g. once per hour, or once every day.
In the post the whenever gem (https://github.com/javan/whenever) is used, but you could use anything that is able to run things periodically.
If you'd want to stay solely within Postgres, there is also pg_cron (https://github.com/citusdata/pg_cron) which could be used to call REFRESH MATERIALIZED VIEW from within the database.
Re: Effectively Using Materialized Views in Ruby on Rails
#4How often does rails refresh the materialized views? And is it automatic or do you have to explicitly refresh them?
The post covers this under the section "Refreshing a materialized view".
Re: Effectively Using Materialized Views in Ruby on Rails
#5Another approach could be to create the materialized view and then use some sort of event system say the pg_notify subsystem to capture an event and then increment some aggregation -- this would eliminate the need for scheduled view refreshes and keep the view almost real-time and quick.
Re: Effectively Using Materialized Views in Ruby on Rails
#6Another approach could be to create the materialized view and then use some sort of event system say the pg_notify subsystem to capture an event and then increment some aggregation -- this would eliminate the need for scheduled view refreshes and keep the view almost real-time and quick.
This might be best done using triggers. I've had good experiences with the Hair Trigger gem for managing these in rails.
Re: Effectively Using Materialized Views in Ruby on Rails
#7I used a technique very much like this in the past. Here are two potential gotchas using matviews with rails, both related to the inevitable reality that DDLs evolve over time:
- postgres supports concurrently refreshing the contents of existing materialized views but there's no built in way to change the structure of the matview concurrently. Which means any `ALTER MATERIALIZED VIEW` will result in all reads to the view blocking for potentially many minutes or longer, for views over nontrivial data. Adding a column to a table is a cheap and non-blocking migration, but allowing the new column to appear in matviews can require an outage or fancy transactional view name swapping that libraries like scenic don't support.
- a column that is included in a view of any kind cannot change in any way, even ways that are binary-compatible like VARCHAR->TEXT. This comes up surprisingly often and getting around it is annoying.
Re: Effectively Using Materialized Views in Ruby on Rails
#8Another approach could be to create the materialized view and then use some sort of event system say the pg_notify subsystem to capture an event and then increment some aggregation -- this would eliminate the need for scheduled view refreshes and keep the view almost real-time and quick.
You could do something similar with built-in ActiveRecord callbacks as well.
Re: Effectively Using Materialized Views in Ruby on Rails
#9"There are two hard things in computer science: cache invalidation, naming things, and off-by-one errors." (Phil Karlton, et al)
Views (both regular and materialized) are really useful, but remember that storing data in two places really is one of the of the hard problems in CS. Refreshing the materialized view on a timer might seem easy, but 6 months from now when something important reads from the stale view instead of the real table could become a really frustrating bug. A materialized view might still be a worthwhile optimization; just remember that you're also adding cache management, which might be more complicated than you suspect.
Re: Effectively Using Materialized Views in Ruby on Rails
#10Overall, I love this approach, but the big pain for me is the lack of incremental view refreshes. I end up needing to recreate giant tables each refresh even though the underlying changes are small. Sure I could implement that myself, but that sacrifices the correctness guarantees that materialized views provide!
Two things that would help:
1. Getting Incremental View Maintenance (IVM) [1] into Postgres. It looks like work is beginning on this but it's been 7+ years coming. If there are any Postgres devs looking at this, I'm cheering for you!
2. There's a commercial group doing an implementation of this, Materialize [2], but they don't have any publicly released product yet. I mention it because their interview on Data Engineering Podcast is really good [3] and I really like their focus on correctness by working from replication logs.
[1] https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc...
[3] https://www.dataengineeringpodcast.com/materialize-streaming...