====================
Extends 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.
Add gem 'materialized_views'
to your application's Gemfile and then execute $ bundle
Or install it yourself as $ gem install materialized_views
Place any of the below methods within an ActiveRecord::Migration
class.
Syntax:
materialize(materialized_view_name, view_definition)
Example:
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.
The refreshed data comes from the underlying, unmaterialized version
Syntax:
create_refresh_row_function_for(materialized_view_name, options={})
Example:
create_refresh_row_function_for 'order_summaries'
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'
Syntax:
create_1_to_1_refresh_triggers_for(
materialized_view_name,
origin_table_name,
foreign_key_name
)
Example:
create_1_to_1_refresh_triggers_for 'order_summaries', 'orders', 'id'
Syntax:
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
)
Example:
create_1_to_n_refresh_triggers_for 'order_summaries', 'customers', 'orders', 'code', 'customer_id'
This one is a good candidate for a Rake task. It does not go inside a migration.
Syntax:
MaterializedViews.gold_standard_test(ActiveRecordModelName)
Example:
# 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
Syntax:
add_tsvector_to(materialized_view_name, searchable_column_array)
Example:
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.
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