Materialized views are a special kind of view that stores the view’s output as a physical table in the cache, rather than executing the underlying query on every access.
Materialized views are available from PostgreSQL 9.3. A materialized view is a table that contains rows but behaves as a view. You are not allowed to modify or add rows, and the table is populated with a SELECT query.
See: Materialized Views
You use a SELECT statement to create the materialized view and the result is persisted as a table-like form. When you select something from that materialized view, it will use the pre-computed results instead of actually performing the query again.
You have to take into account that when the underlying data changes, the materialized view DOESN’T, unless you refresh it.
First: Rails migration
Once you defined the query that populates the materialized view, you have to create a migration.
class ExamplesView < ActiveRecord::Migration
CREATE MATERIALIZED VIEW examples
AS ( SELECT …..
DROP MATERIALIZED VIEW IF EXISTS examples
Second: Create the model
Create the model that will access the materialized view. Remember that materialized views cannot be updated, so it’s important that you specify readonly? as true.
class Example < ActiveRecord::Base
self.table_name = ‘examples’def readonly?
connection = ActiveRecord::Base.connection
connection.execute(‘REFRESH MATERIALIZED VIEW examples’)
Run the migration:
bundle exec rake db:migrate
You will notice that the schema.db does not contain the examples table. It shouldn’t! Because it is not a table, it’s a view.
Third: Use it
You can query the materialized view as if it was a normal table
examples = Example.where(….)
Fourth: Refresh the materialized view
In our example, we are not interested in having the latest information in our materialized view. Updating the view once every hour is good enough, so we create a job that will do that.
If you need to refresh the view every time you query it, then you should probably use “normal views”.
@queue = :refresh_viewclass << self
Don’t over do it
REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view. The old contents are discarded. Refreshing a materialized view means locking the entire table and therefore preventing anything querying it and if a refresh took a long time to acquire the exclusive lock (while it waits for queries using it to finish), in turn, it holds up subsequent queries.
From PostgreSQL 9.4, this can be mitigated with the CONCURRENTLY keyword:
connection = ActiveRecord::Base.connection
connection.execute(‘REFRESH MATERIALIZED VIEW CONCURRENTLY examples’)
A unique index will need to exist on the materialized view. Instead of locking the materialized view up, it creates a temporary updated version, compares the two versions, then applies INSERTs and DELETEs against the materialized view to applying the difference. This means queries can still use the materialized view while it’s being updated.
Unlike its non-concurrent form, tuples aren’t frozen, and it needs VACUUMing due to the aforementioned DELETEs that will leave dead tuples behind.
See: PostgreSQL 9.4
Notes on Rspec
If you usually use `bundle exec rake test:prepare` you will probably get this error when running the spec tests:
PG::UndefinedTable: ERROR: relation “examples” does not exist
This is because test:prepare cleans up the database and it ends up running db:test:load which creates the tables found in the schema. The db schema does not contain the views or materialized views in it, thus, the relation is not found.
Use these instructions instead:
RAILS_ENV=test bundle exec rake db:migrate
bundle exec rspec
If you are using Travis CI, make sure you specify the postgresql version as 9.3 or higher. Remember that materialized views are not supported in previous versions of postgres.
See: Selecting a PostgreSQL
Your travis configuration should look something like this:
— cp config/database.travis.yml config/database.yml
— psql -c “CREATE USER foo WITH PASSWORD ‘foo’;” -U postgres
— psql -c “DROP DATABASE IF EXISTS foo_test;” -U postgres
— psql -c “CREATE DATABASE foo_test ENCODING ‘utf8';” -U postgres
— bundle exec rake db:migratescript:
— bundle exec rspec
Posted by Lucila Stancato (firstname.lastname@example.org)