View Backed Models are a super power. If you’re a working Rails dev and you’ve never used them before, you really owe it to yourself to check them out. IMO, they should be baked into Rails itself. They’re that useful.
Effectively Using Materialized Views in Ruby on Rails
31–37 of 37 posts
Re: Effectively Using Materialized Views in Ruby on Rails
#32Overall, 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 lik…
You can do effectively the same thing using a physical table and triggers [1] if you want a pure postgres solution. I ended up with the same problem and just ended up using a physical table and implemented the updating logic at our app layer. Not exactly fun or easy to implement all the updating logic yourself though...
[1] https://hashrocket.com/blog/posts/materialized-view-strategi...
Re: Effectively Using Materialized Views in Ruby on Rails
#33Earlier quoted context omitted.
That's a very clever concept. Could you recommend any classic old-school books about data warehousing that you'vre read that teach more such techniques? Thank you!
Its pretty much the kimball books - https://www.kimballgroup.com/data-warehouse-business-intelli...
Yup, exactly. Kimball's stuff is the best. You can achieve on a modest machine what modern techniques would require an incredibly expensive horizontally scaled MPP database. It does require a lot more planning and forethought to be certain.
https://www.amazon.com/dp/product/1118530802 and possibly https://www.amazon.com/dp/0764567578/
Re: Effectively Using Materialized Views in Ruby on Rails
#34Overall, 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 lik…
You can do effectively the same thing using a physical table and triggers [1] if you want a pure postgres solution. I ended up with the same problem and just ended up using a physical table and implemented the updating logic at our app layer. Not exactly fun or easy to implement all the updating logic yourself though... [1] https://hashrocket.com/blog/posts/materialized-view-strategi...
Like you said, not easy: Recreating the functionality of views using triggers is error prone and a heavy maintenance burden, and the triggers have to be implemented differently based on what each query is like... In a lot of cases it's better to just optimize the query and indexing.
In PG you can make a lot of queries run straight from one index if you tailor an index to a query, since you can index your own plpgsql functions.
Re: Effectively Using Materialized Views in Ruby on Rails
#35Is there an upper limit on the number of materialized views that it makes sense to have in a database?
I've been trying to figure out a way to deal with my (growing) join of 13 rather large tables when fetching all of a user's data. Are materialized views the kind of thing that I could generate these cached results _per user_ and have, like, hundreds of thousands of them sitting around to query? And then be able to query against all of user 123456's content directly instead of filtering N tables for their content and joining them all together every time?
Re: Effectively Using Materialized Views in Ruby on Rails
#36Overall, 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 lik…
You can do effectively the same thing using a physical table and triggers [1] if you want a pure postgres solution. I ended up with the same problem and just ended up using a physical table and implemented the updating logic at our app layer. Not exactly fun or easy to implement all the updating logic yourself though... [1] https://hashrocket.com/blog/posts/materialized-view-strategi...
I'm very hopeful for approaches that model around this need from the ground up. Big Data distributed stream processing frameworks like Spark or Flink allow for standing SQL queries that are incrementally build; and you can even subscribe to react to the results.
There are other initiatives for plugging it on existing RDBMSs, like Noria [1] for MySQL. It allows for subscribing to changes; and also for lazy evaluation of the materializing view rows, keeping them in cache for later.
Re: Effectively Using Materialized Views in Ruby on Rails
#37Earlier quoted context omitted.
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?
Do you mean using something like `pg_cron` instead of calling it through Scenic and a cron fired at the app level? I personally don't like having my cron distributed in different areas. All my crons are in one place/system like easycron.com or setcronjob.com or whenever. Performance is not a consideration when deciding to run the job directly from the DB or the App. I answered the question sentence directly, but mayb…
Yeah, pg_cron, then. Updating a materialized view feels like a data integrity issue which should be addressed within the DB itself, if not the DDL for the view itself.