I've got an app with over 2 million rows in a Postgresql database and I make complex queries with joins, unions and aggregate functions that take a lot of time. The results of some of these queries are used a lot, for example to render a cloropleth map on the app's homepage that changes based on filters on the map. This is a super expensive operation and can't scale!
In come Materialized Views to the rescue. A materialized view is a query that is persisted like a table and treated the same way. This means queries on this table are fast. No joins, or unions, just like querying any other table. Note that it needs to be refreshed to keep it up to date so it is not suited for realtime data.
Although we can map queries on our materialized view to a plain crystal class using
DB.mapping, we would miss out on Lucky's awesome typesafe queries. So in this article we're going to look at treating materialized views just like normal tables and having Lucky model them for us!
The sql for the query we'll be materializing is below. Later we'll make slight adjustments to it so it can play nicely with
SELECT posts.id, posts.title, posts.content, users.name as author FROM posts JOIN users ON users.id = posts.user_id
This isn't a complex query but the way we implement this feature can be expanded to include any query regardless of complexity. The only thing that matters is that the columns returned by our
SELECT statement match our
LuckyRecord model definition.
If you want to test this out yourself you can use my demo app, just clone the repo and checkout the
matviews-0 branch to follow along, or
matviews-3-complete to see the finished code.
git clone firstname.lastname@example.org:mikeeus/lucky_api_demo.git cd lucky_api_demo bin/setup git checkout matviews-0
git checkout matviews-0
Although we don't have a materialized view or a model to access it, we're going to write a spec for how we want to be able to use it.
# spec/matview_spec.cr require "./spec_helper" describe App do describe "ComplexPost matview" do it "should refresh and query" do user = UserBox.new.create first_post = PostBox.new.title("First").user_id(user.id).create second_post = PostBox.new.title("Second").user_id(user.id).create ComplexPost.refresh complex = ComplexPostQuery.new.title(first_post.title).first complex.title.should eq first_post.title end end end
We want to be able to refresh the materialized view using the model, and we want to query the view with Lucky's own queries which will generate helper methods for each column, eg:
Create Materialized View
git checkout matviews-1-create-matview
First lets generate a migration with
lucky gen.migration CreateComplexPostMatview.
class CreateComplexPostsMatview::V20180101010 < LuckyMigrator::Migration::V1 def migrate execute <<-SQL CREATE MATERIALIZED VIEW complex_posts AS SELECT posts.id, posts.title, posts.content, users.name as author, NOW() as created_at, NOW() as updated_at FROM posts JOIN users ON users.id = posts.user_id SQL execute "CREATE UNIQUE INDEX complex_posts_id_index ON complex_posts (id)" end def rollback execute "DROP MATERIALIZED VIEW complex_posts" end end
We need to have
updated_at columns because
LuckyRecord::Model will expect these when it generates helper methods for us. In this case we actually use
id and even add a unique index on it to speed up our queries, but I should note that neither an id column or a primary key is needed for a materialized view.
Now if we migrate with
lucky db.migrate it should pass without issue.
Generate ComplexPost Model and Query
git checkout matviews-2-create-model
Now that we have the materialized view in the database we could define a
ComplexPost class that calls
DB.mapping (like in my previous post) and just query it with
LuckyRecord::Repo like this.
LuckyRecord::Repo.run do |db| db.query_all "SELECT * FROM complex_posts", as: ComplexPost end
But that wouldn't give us the benefits of
So instead we'll generate our
ComplexPost model by running
lucky gen.model ComplexPost. We can go ahead and delete the generated
ComplexPost::Form because we'll never be inserting rows into our materialized view.
Let's fill it in as if it was a normal database model and add a class method to refresh the materialized view.
# src/models/complex_post.cr class ComplexPost < BaseModel table :complex_posts do column title : String column content : String column author : String end def self.refresh LuckyRecord::Repo.db.exec "REFRESH MATERIALIZED VIEW complex_posts" end end
Run The Specs
git clone matviews-3-complete
crystal spec spec/matviews_spec.cr and... BAM! It's working.
Although this was a simple example we can expand on it to account for complex queries. An example of a query that I use in my app is this.
class CreateCountryAnnualTradeMatview::V0000001 < LuckyMigrator::Migration::V1 def migrate execute <<-SQL CREATE MATERIALIZED VIEW country_annual_trades AS SELECT merged.id, -- FOR LUCKY merged.name, merged.short, merged.year, COALESCE(sum(merged.total_imports_cents))::bigint as total_imports_cents, COALESCE(sum(merged.total_exports_cents))::bigint as total_exports_cents, NOW() as created_at, -- FOR LUCKY NOW() as updated_at -- FOR LUCKY FROM ( ( SELECT ... ) -- some complex query UNION ( SELECT ... ) -- another complex query ) merged GROUP BY id, name, short, year ORDER BY name SQL execute <<-SQL CREATE UNIQUE INDEX country_annual_trades_id_year -- we can even add indexes! ON country_annual_trades(id, year) SQL end def rollback execute <<-SQL DROP MATERIALIZED VIEW country_annual_trades SQL end end
And the model.
class CountryAnnualTrade < BaseModel table :country_annual_trades do column name : String column short : String column year : Int32 column total_imports_cents : Int64 column total_exports_cents : Int64 end def self.refresh LuckyRecord::Repo.db.exec "REFRESH MATERIALIZED VIEW country_annual_trades" end end
Note that I cast my aggregated columns
::bigint then define them in the model as
column total_imports_cents : Int64.
I hope you enjoyed this tutorial and found it useful. Join us on the Lucky gitter channel to stay up to date on the framework or checkout the docs for more information on how to bring your app idea to life with Lucky.
Top comments (4)
You're allowed to have union in material views in Postgres?!
Hi Evaldas, I hope you enjoyed the article. You can actually create whatever kind of query you want. A materialized view is just a query whose resulting rows are persisted as if it was a normal table.
I did. I'm just used to SQL Server materialized views implementation. It has way more restrictions. That's why I was surprised 😊
Interesting, I've never used SQL server. tbh I've used Postgresql ever since I learned to code and I've never had a reason to try anything eles