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.