Materialized views in Ecto

A materialized view is a view that is materialized.

Image for post
Image for post

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

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.

Written by

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store