ActiveRecord + PostgreSQL Materialized Views

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

See also: Adding a Real-Time Module to your Rails API

First: Rails migration

Once you defined the query that populates the materialized view, you have to create a migration.

class ExamplesView < ActiveRecord::Migration
 def self.up
 execute <<-SQL
 CREATE MATERIALIZED VIEW examples
 AS ( SELECT …..
 from ….
 );
 SQL
 end
 def self.down
 execute <<-SQL
 DROP MATERIALIZED VIEW IF EXISTS examples
 SQL
 end
end

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?
 true
 end
def self.refresh_view
 connection = ActiveRecord::Base.connection
 connection.execute(‘REFRESH MATERIALIZED VIEW examples’)
 end
end

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

class RefreshExampleViewJob
 @queue = :refresh_view
class << self
 def perform
 Example.refresh
 end
 end
end

See also: Wolox’s Internal Apps

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:

def self.refresh_view
 connection = ActiveRecord::Base.connection
 connection.execute(‘REFRESH MATERIALIZED VIEW CONCURRENTLY examples’)
end

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:

ActiveRecord::StatementInvalid:
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.

See: dbtestprepare

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:

…
addons:
 postgresql: 9.3
services:
  —  postgresql
…
 
before_script:
  —  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:migrate
script:
  —  bundle exec rspec
….

Posted by Lucila Stancato (lucila.stancato@wolox.com.ar)

www.wolox.com.ar