Today I want to walk through what SQL Views can be used for, the pros and cons of implementing them, and how I problem solved an issue I faced using a Materialized View. SQL Views are one of those things. If you had a "traditional" database class, odds are you learned about them. If you've worked in enterprise, odds are you've got horror stories about them. If you started your tech life doing Rails or Node development, you might have no idea what I'm talking about.
## What is a SQL View and what is it used for?
A SQL View is a virtual table backed by a SQL query. It carries much of the same functionality that a table has, but it's backed by a query, rather than a table in the database, and depending on how you have it set up, can pull in a wide variety of data.
Image Source: [Essential SQL](https://www.essentialsql.com/what-is-a-relational-database-view/)
**Example for using SQL view:**
You've got a properly normalized database. Your customers, products, and invoices are all in their own tables. Your database engine makes it easy to join these together, but you want to make it easier for a business analyst who's connecting to the database to look at the raw data. You create a view that groups sales by hour and product so that you can analyze when you sell the most.
-- CODE line-numbers language-sql --
<!--
create view sale_aggregate as
select extract(month from time) as month,
extract(day from time) as day,
extract(hour from time) as hour,
product_name,
count(*)
from invoices
group by month, day, hour, product_name
-->
## What are the advantages?
- It acts like a table, so most things you'd do with a table will work with a view
- if you're doing user level access control, you can give a user access to a view without giving them access to the tables behind it
- It allows you to keep the logic centralized, rather than repeating it in your code
- It can allow for massive performance improvements
## What are the disadvantages?
- If done wrong, it can result in performance issues
- You may not be able to update the view, forcing you back to the original tables
- A view is one more moving piece that has to be maintained
## When to use Materialized View in SQL
In my case, a standard SQL view wasn't enough, so I had to take a step further to a Materialized View. They act the same, except that the materialized view is generated from a query but stored (similar to a table). You can call `REFRESH` on it, and it will recalculate. This means the performance hit happens when you run the refresh, but it's available without any calculation when you access it.
## Problem solving with context: optimizing queries
So what was the problem I was trying to solve? We had a giant table of data, where multiple data types were consolidated into a row (one per day). The logic for determining what to show wasn't that complex, but it required bringing in all the different data types, merging them, and looping through. It wasn't quick, and it used a fair bit of memory.
What I ended up with was a view that had a row for each combination of user and date, and the id's of the relevant model for each. Back in the old days, I'd write a query against this, and build an app around it. BUT I'm a rails developer.
It turns out that a view really does act like a table. That means that I can create a model that looks at it and acts just like any other model (mostly). The biggest change I needed to make it to mark it as read-only, so that the app wouldn't try to write to it, which is as simple as:
-- CODE line-numbers language-ruby --
<!--
def readonly?
true
end
-->
It took a bit of trial and error to get my view right working with a model. Eventually, I found a gem ([https://github.com/scenic-views/scenic](https://github.com/scenic-views/scenic)) that helped manage this table just like I manage other tables. When I needed to make a change, I could generate a new version, and rails migrations would handle it without any effort. It also provides a handy method for refreshing the materialized table:
-- CODE line-numbers language-ruby --
<!--
def self.refresh
Scenic.database.refresh_materialized_view (
table_name,
concurrently: false,
cascade: false
)
end
-->
I was able to name all my columns with "#{model_name}_id" and use belongs_to relationships, so then for my table, all I had to do was get the list of daily records for the table, loop through them, and join in the appropriate records. This resulted in a massive code cleanup and performance improvement.
## How to avoid issues with SQL View
**Evaluate whether you really need a materialized view.**
In this case, it took about 1.4s to use a view calculated on the fly, vs. 0.01s to bring it back from a materialized view, which is obviously a large improvement. That said, if it was a fraction of that time, having a standard view that was calculated on the fly would be preferable. It takes 6-7 seconds to recalculate the table, but in the case of this app, the data is being updated via imports, and not changing all the time, so it was a positive payoff.
**Evaluate if you need the overhead of a view at all.**
You may be better off just calling the query when you need it, rather than defining it in the database. It makes maintaining it a little bit easier. It also keeps the query closer to your code, which may make improving the query easier (especially if you don't have a DBA monitoring performance). If you're not using the view exactly as it's set up (adding additional constraints, reordering rows, or selecting a subset of columns), you may also be blocking the database from properly optimizing.
**Evaluate how you're going to keep it in sync**
*Assuming you're using a materialized view.*
In our case, we have a standard import, and we were able to add the refresh logic to each of those cases. If you've got a lot of places in your app touching the data contained in the view, keeping the materialized view up to date may be a challenge. Because there's performance cost to refreshing a materialized view, you'll only want to use them if you're reading a lot more than you're writing.
## Educating your team
Spend time with your team to make sure they understand it. It's not too difficult to understand, but it can be surprising if you come across it and you aren't expecting it.
**Resources to learn more about SQL View**
- [W3 Schools - SQL Views](https://www.w3schools.com/sql/sql_view.asp)
- [GitHub - Scenic Views](https://github.com/scenic-views/scenic)
- [PostgresSQL - Materialized Views](https://www.postgresql.org/docs/9.3/rules-materializedviews.html)
