ActiveRecord + PostgreSQL Materialized Views

Wolox Engineering
Jan 5, 2015 · 4 min read

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.

First: Rails migration

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

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.

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

Run the migration:

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

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 << self
def perform

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:

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:

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:

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:

  —  postgresql

  —  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
  —  bundle exec rspec

Posted by Lucila Stancato (


Wolox stands for innovation, engineering and working culture that transforms problems into solutions and ideas into products.

Wolox Engineering

Written by

Driving Innovation



Wolox stands for innovation, engineering and working culture that transforms problems into solutions and ideas into products.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade