ActiveRecord::Migration with methods for creating auto-updating materialized views in Postgres.
Can perform gold standard tests to check if a materialized view is up-to-date with its unmaterialized version.
Here are some resources on materialized views:
Dan Chak's chapter on Materialized Views from his book Enterprise Rails
A blog post I wrote on materialized views.
Suggestions on alternatives to materialized views.
gem 'materialized_views' to your application's Gemfile and then execute
Or install it yourself as
$ gem install materialized_views
Place any of the below methods within an
Create a materialized view:
materialize 'order_summaries', 'select * from orders order by placed_on'
This creates a regular view
order_summaries_unmaterialized, and a table
order_summaries to hold its materialized version.
Create a function to refresh a row of the materialized view
The refreshed data comes from the underlying, unmaterialized version
If your materialized view's primary key is not an integer or is not named 'id':
create_refresh_row_function_for 'order_summaries', primary_key: 'order_code', primary_key_data_type: 'text'
Create 1 to 1 refresh triggers
create_1_to_1_refresh_triggers_for( materialized_view_name, origin_table_name, foreign_key_name )
create_1_to_1_refresh_triggers_for 'order_summaries', 'orders', 'id'
Create 1 to n refresh triggers
create_1_to_n_refresh_triggers_for( materialized_view_name, origin_table_name, join_table_name, join_table_materialized_view_foreign_key, join_table_origin_table_foreign_key )
create_1_to_n_refresh_triggers_for 'order_summaries', 'customers', 'orders', 'code', 'customer_id'
Test if a materialized view is up-to-date
This one is a good candidate for a Rake task. It does not go inside a migration.
# in lib/tasks/materialized_view_test.rake namespace :materialized do desc 'Tests that materialized views are up to date' task test: :environment do MaterializedViews.gold_standard_test(OrderSummary).result end end # Then at the console in your project root directory: $ rake materialized:test
Add a tsvector column for faster full text searching:
add_tsvector_to 'order_summaries', %w(order_code customer payment_status shipping_status)
And then configure
pg_search or whatever you are using to use the resulting tsvector column.
Note on materialized views vs. tables
For compatibility with older versions of Postgres, these create 'tables', NOT 'materialized views'.
- Fork it
- Create your feature branch (
git checkout -b my-new-feature)
- Commit your changes (
git commit -am 'Add some feature')
- Push to the branch (
git push origin my-new-feature)
- Create a Pull Request