Episode 8 in the PostgreSQL for MySQL DBAs series covers materialized views. MySQL has had views for many years and they are very useful, but it has never had materialized views. Those who use materialized views appreciate their utility and here we will cover how they are used. This episode starts with regular, non-materialized views to help those who are not used to using them and then moves on to materialized views so you can see the difference.
Quick recap: Why views?
Views are often used to hide underlying column and table names from users. This obfuscation works well as the user may have permission to query the view but is denied access to the base tables. By having users standardize on using the view to get just the needed columns, it does simplify things. It allows the joining process and simplifies the use of multiple tables into a single virtual table. The view becomes a ‘common’ shorthand for required data. It can also encapsulate the details of the structure of your tables, which might change as your application evolves, behind consistent interfaces.
Views are great places for aggregate values like SUM(), AVG(), etc., to keep the calculations separate from the ‘pure’ data. They take up little space as compared to a table. Views can be built on top of other views, and views can be used like a table.
A basic view
Let’s start with a simple table and corresponding simple data.
1 2 3 4 | test=# create table base (a int, b int, c int); CREATE TABLE test=# insert into base values (1,2,3),(4,5,6),(7,8,9); INSERT 0 3 |
Our first view draws values from the above table.
1 2 3 | <span style="font-weight: 400;">test=# </span><b>create view v1 as SELECT a, b, c*4 from base;</b> <span style="font-weight: 400;">CREATE VIEW</span> |
The view returned the values of the columns of a and b plus the value of c multiplied by four.
1 2 3 4 5 6 7 | test=# select * from v1; a | b | ?column? ---+---+---------- 1 | 2 | 12 4 | 5 | 24 7 | 8 | 36 (3 rows) |
I can use this view like a table by using a WHERE clause.
1 2 3 4 | test=# select * from v1 where a > 6; a | b | ?column? ---+---+---------- 7 | 8 | 36 |
Materialized views
A materialized view is a static snapshot of the data with the two keywords being ‘static’ and ‘snapshot’. If you have a case where you do not want many users asking for the same data frequently, then you can use materialized views as a cache. You may be familiar with stock quotation services that only show updated values every five or so minutes. This can take a major load off the database instance as it handles requests in the base tables.
1 2 3 4 5 6 7 | test=# create materialized view v2 as SELECT a, b, c*4 from base; SELECT 3 test=# select * from v2 where a > 6; a | b | ?column? ---+---+---------- 7 | 8 | 36 (1 row) |
The keyword MATERIALIZED is added to the view definition. In the above example, the materialized view uses the same logic as the previous non-materialized view. This caches the values for later use.
But be careful. This materialized view is a snapshot of a particular point in time. Why be careful? Consider the following situation where we update a value in the table.
1 2 3 4 5 6 7 | test=# update base set b=99 where a = 7; UPDATE 1 test=# select * from v2 where a > 6; a | b | ?column? ---+---+---------- 7 | 8 | 36 (1 row) |
The value for b is updated in the base table however the materialized view value is not updated.
But it is easy to ‘update’ the materialized view with the REFRESH keyword.
1 2 3 4 5 6 7 | test=# refresh materialized view v2; REFRESH MATERIALIZED VIEW test=# select * from v2 where a > 6; a | b | ?column? ---+----+---------- 7 | 99 | 36 (1 row) |
Materialized views are a great way to cache answers but you have to be aware that the data may be out of date.
Need materialized views for MySQL today?
MySQL and MariaDB do not have materialized views. You could make a feature request that expresses your desire for this feature with those vendors, but you will not see materialized views implemented in the next release or so.
Another option is EdgeDB, which has materialized views along with a columnar storage engine that is compatible with both MySQL and MariaDB. This builds on the previous work of Justin Swanhart in the areas of materialized views and the WARP storage engine.
Summary
Now you know how materialized views work and how they differ from regular views.
The past videos for PostgreSQL for MySQL Database Administrators (DBA) can be found here: episode one, episode two, episode three, episode four, episode five, episode six, and episode seven.
