Materialized views in Ecto

A materialized view is a view that is materialized.

Let’s break this apart.

A view

In SQL, a view is a table-like structure that is generated by a query, and that can be queried. Say I have a posts table, with a title column. I could create a view posts_titles defined by this query :

SELECT DISTINCT title FROM posts;

Then I could query this view :

SELECT * FROM posts_titles;

And it would return all the titles from my posts table.
Every time I query the view, the query that defines the view is executed.

Pros

• The view is always up-to-date

Cons

• Two queries are executed each time I query the view

A materialized view

A materialized view is a view that is cached. A table-like structure is created from a query and is persisted. This means that when you query the materialized view, no additional query is executed. The materialized view is updated on demand.

Pros

• The view is cached

Cons

• You have to explicitly refresh the view

Creating the materialized view

Say we have a category column in our posts table. We’d like to create a materialized view categories which we’ll contain rows of categories and the count of posts of each category.

We’ll create the materialized view using this query :

CREATE MATERIALIZED VIEW categories AS
  SELECT category, count(id) AS count FROM posts GROUP BY category;

And the function that will refresh it :

CREATE OR REPLACE FUNCTION refresh_categories()
RETURNS trigger AS $$
BEGIN
  REFRESH MATERIALIZED VIEW categories;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

For more information on functions and triggers, see my previous article.

Finally, the trigger itself :

CREATE TRIGGER refresh_categories_trg
AFTER INSERT OR UPDATE OR DELETE
ON posts
FOR EACH STATEMENT
EXECUTE PROCEDURE refresh_categories();

The Ecto model

An Ecto model expects an id column by default. We should change that :

defmodule Posts.Category do
  use Posts.Web, :model
  @primary_key {:category, :string, []}
  schema "categories" do
    field :count, :integer
  end
end

And we’re done! Say our db has 2 posts with category dev and 1 post with category misc, calling Category |> Repo.all will produce :

[
  %Category{category: "dev", count: 2},
  %Category{category: "misc", count: 1}
]

Special thank to Ricardo García Vega for all his supportive comments. Go check out his (on-going) tutorial to learn how to create a Trello-like app, using Phoenix and React.