Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Effectively Using Materialized Views in Ruby on Rails

pganalyze.com

Post reply on HN
Print view View original

21–30 of 37 posts

Re: Effectively Using Materialized Views in Ruby on Rails

#21

Post by meritt »

code_biologist wrote: post #10

Overall, 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…

Likely too boring and old school data warehousing, but this is what the notion of rollup/aggregate tables achieve. You define a table that's the result of some sort of an aggregation query (time is almost always involved: minutely, hourly, daily, monthly plus other business-specific dimensions) and at the conclusion of each corresponding interval you insert the next respective batch.

    e.g. insert into rollup_clicks_hourly select hour, count(*) clicks from clicks where created_at >= ? and created_at
Then from a reporting perspective, users/dashboards/etc read from the highest relevant table that meets their criteria.

Re: Effectively Using Materialized Views in Ruby on Rails

#22

Post by djrobstep »

j4mie wrote: post #14

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

If you use a migration tool that works at the database level rather than the model level, the the migration handling of views will be automatically supported.

Last time I tried to do model a database view in Django it was painful, but support may have improved since.

But worst case you can drop down to a vanilla query.

Re: Effectively Using Materialized Views in Ruby on Rails

#23

Post by irjustin »

Roboprog wrote: post #20
code_biologist wrote: post #10

Overall, 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…

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 maybe that's not what you were asking?

Re: Effectively Using Materialized Views in Ruby on Rails

#24

Post by code_biologist »

Roboprog wrote: post #20
code_biologist wrote: post #10

Overall, 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…

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?

Yeah. Coming from a Django world it's about avoiding surprises for application programmers. I suspect it's the same in Rails. Historically Django has almost entirely had logic at the application level rather than pushing it into the DB: scheduled jobs, data validation, trigger-ish logic (via `post_save` signals and such).

Part of the power of OP's technique is that it looks exactly like a standard Django/Rails data model with a tiny sprinkle of magic -- no surprises. It's surprising for a Django programmer to hear "all periodic tasks are handled via celery, except these table refreshes" or "signals are responsible all work in response to model changes, except these triggers".

Obviously in some cases you need a trigger for correctness, but in general I try to stick to the conventions of the ecosystem.

Re: Effectively Using Materialized Views in Ruby on Rails

#25

Post by erik_seaberg »

gigatexal wrote: post #5

Another 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.

Real-time means latency is predictably within some deadline. But the system he describes is returning wrong answers quickly rather than invalidating stale cache hits and recomputing right answers too slowly.

Re: Effectively Using Materialized Views in Ruby on Rails

#26

Post by djrobstep »

code_biologist wrote: post #10

Overall, 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…

Materialized views are fundamentally just some syntactical sugar over the results of a query cached in a table.

The syntactical sugar does have advantages, but comes at a cost of flexibility - if you need more control over the update process or modification of the data than a full refresh, then you should just use a table (create table whatever as select * from ...).

Re: Effectively Using Materialized Views in Ruby on Rails

#27

Post by jimnotgym »

meritt wrote: post #21
code_biologist wrote: post #10

Overall, 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…

Likely too boring and old school data warehousing, but this is what the notion of rollup/aggregate tables achieve. You define a table that's the result of some sort of an aggregation query (time is almost always involved: minutely, hourly, daily, monthly plus other business-specific dimensions) and at the conclusion of each corresponding interval you insert the next respective batch. e.g. insert into rollup_clicks_ho…

I worked with a company who were doing (basic)analytics on their live ERP db, sales and margin reporting etc. There was a view written to join in all of the common tables so the in house people could query it easily, things like product names, customer names and addresses etc. When performance fell off they materialised the view as a stop gap... until they got around to a data warehouse. 10 years later it is still there, building each night! It was actually the perfect solution for them, simple analytics with no more infrastructure!

Re: Effectively Using Materialized Views in Ruby on Rails

#28

Post by purerandomness »

meritt wrote: post #21
code_biologist wrote: post #10

Overall, 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…

Likely too boring and old school data warehousing, but this is what the notion of rollup/aggregate tables achieve. You define a table that's the result of some sort of an aggregation query (time is almost always involved: minutely, hourly, daily, monthly plus other business-specific dimensions) and at the conclusion of each corresponding interval you insert the next respective batch. e.g. insert into rollup_clicks_ho…

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!

Re: Effectively Using Materialized Views in Ruby on Rails

#29

Post by hobs »

purerandomness wrote: post #28
meritt wrote: post #21

Earlier quoted context omitted.

Likely too boring and old school data warehousing, but this is what the notion of rollup/aggregate tables achieve. You define a table that's the result of some sort of an aggregation query (time is almost always involved: minutely, hourly, daily, monthly plus other business-specific dimensions) and at the conclusion of each corresponding interval you insert the next respective batch. e.g. insert into rollup_clicks_ho…

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...

Re: Effectively Using Materialized Views in Ruby on Rails

#30

Post by toasterlovin »

geori wrote: post #2

How often does rails refresh the materialized views? And is it automatic or do you have to explicitly refresh them?

In addition to what the other commenters mentioned, if you’re importing data, you can refresh the affected materialized views after the data import is complete.
Post reply on HN