The Way for Updating Materialized Views Rapidly
Nagata, Yugo
Hoshiai, Takuma
Materialized views is a feature to store the results of view definition queries in DB in order to achieve faster query response. However, after base relations are modified, view maintenance is needed to keep the contents up to date. REFRESH MATERIALIZED VIEW command is prepared for the purpose, but this has to recompute the contents from a scratch, so this is not efficient in cases where only a small part of a base table is modified. Incremental View Maintenance (IVM) is a technique to maintain materialized views efficiently, which computes and applies only the incremental changes to the materialized views rather than recomputing. This feature is not implemented on PostgreSQL yet. We have proposed a patch to implement IVM on PostgreSQL and this is now under discussion. Since the first submission of the last year, we have made a great progress on this patch. For example, some aggregates, subqueries, self-join, and outer joins are supported now. These operations are important since they are commonly used in real applications. In this talk, we will explain the current status of our IVM implementation. The talk includes what problems are under the implementation, its solutions, what the current implementation can do, and what limitations and problems are left.
 Hi everyone, I'm Yu-Gon Nagata. In this session, I would like to talk about the way for updating materialized views rapidly. First, let me introduce us. I am Yu-Gon Nagata and a software engineer at SROASOS Inc. Japan. I am in charge of R&D and now working on incremental view maintenance called IVM. This is today's topic. Takuma Hoshiai is also a software engineer and a member of our IVM project. He will show a demonstration in this talk. This is the outline of this talk. Firstly, I'll introduce incremental view maintenance. Shortly, this is a way to refresh materialized views rapidly. Then, I'll talk about our implementation of IVM and progress since the initial patch. Next, I'll show some behavior examples, including demonstration and performance evaluation. And finally, I'll summarize this talk. First, a view is a virtual relation defined by a view definition query. The query is executed when view is referred to by a select statement. On the other hand, materialized view is a view whose results are stored in database and this enables a quick response to clients. This is useful for example for analyzing large data in the data warehouse. However, materialized view needs to be maintained after a base table is modified to keep consistency between views and base tables. Refreshing materialized view is a way of the maintenance. Then, refresh materialized view command is issued. The contents of materialized view is updated to the latest state. However, this needs to be computing the contents of the view from scratch, so it takes a long time. With concurrently option, the materialized view is refreshed with a weaker look, but it still needs to be computing the contents from scratch. Incremental view maintenance, IVM, is another technique to view maintenance. This computes and applies only the incremental changes to the materialized views. This figure shows IVM formally. From the base tables contents and view definition, we can compute the contents of materialized view. After base table is updated, we get new base table. Using this and the query, we can get the updated materialized view. This green path is a recomputation and what refresh materialized view command starts. On the other hand, we can compute the delta of view, changes on views from changes on tables. We can apply this to the materialized view. We can get the new materialized view data without recomputing. This red path is a process of incremental view maintenance. The first patch of our IVM implementation was submitted to PSKL hackers a year ago, and the subject is implementing incremental view maintenance. I also had a presentation about it at PGCon last year. Using this IVM feature, materialized views can be updated automatically and incrementally when base tables are updated. You don't need to write a trigger to maintain materialized views by yourself. How effective is our IVM implementation? This is an example of TPCH query 1.0.1.0.1. This is a query to aggregate on a large table. With skill factor 1, this query took about 11 seconds. While select on the materialized view took only 3 milliseconds, so it is a quick response. However, if the rest of this materialized view took 24 seconds, on the other hand, one table on the base table is updated. Its incremental maintenance took only 22 milliseconds, that is, the view is updated so rapidly. In the initial patch, we supported selection, projection, inadjoining, distinct class, and build with tuple duplicates. Additionally, the current patch supports also some aggregates, self-join, outer join, sub queries, including existing class. It also supports refresh with node data commands, low-level security, and pgdamp and restore. I'll also explain the progress in this talk. Okay, I'll explain the basic theory of IVM. The view definition is described in a relational algebra form. This is an example of this simple natural join of table R and S. Change of base table is represented like this. This navla, inverted Greek delta, means tuples, derived from the table. And the Greek delta means tuples inserted into the table. Using these view definitions and these changes, we can calculate the changes on the view like this, and update the view by applying these changes to views. This is an example. Contents of table R and S are like this. The natural join view with contents will be like this. After table R is changed, the deleted and inserted changes are like this. The changes on the view are calculated by joining table R changes and base table S respectively. Finally, the view is updated by applying calculated changes like this. In the bad timing of view maintenance, there are two approaches. Immediate maintenance and deferred maintenance. In immediate maintenance, views are updated in the same transaction where the base table is updated. In deferred maintenance, views are updated after the transaction is committed. For example, when view is accessed, where as a response to user command like refresh were updated periodically and so on. In our implementation, we started from immediate approach since it requires less number of calls. The third approach needs a mechanism to manage logs for recording changes of base tables. Implementing this is not trivial work. This is the overview of IBM implementation. There are three processes. First, when a base table is modified, its change is extracted. Second, changes on view is calculated from changes on tables and base tables and view definition query. Finally, the changes on views are applied to the view and view is updated. In our implementation, table changes are extracted using after-triggers and transition tables. Changes on view is calculated based on relational or bug algebra. And the changes are applied to the view using SQL query. For creating materialized view with IBM support, we use create incremental materialized view. This is a tentative syntax to create materialized views with IBM support. Incremental keyword is an extension of our implementation. At creating materialized views, after-triggers are created on all base tables. These are created automatically and internally for insert, delete, and update commands and as a statement limit trigger. And with transition tables. Transition tables is a feature of after-trigger. Using these, changes on tables can be referred to in the trigger function, like normal tables. There are two tables. One contains tables deleted from the table. Another contains tables inserted into the table. In theory, these tables are corresponding to numeral and delta-r respectively. When calculating delta on views, we use the views definition query with some relights. In these relights, the modified table is replaced with a transition table, like this. And multiplicity of duplicate tables are counted using count-as-t aggregate, like this. In theory, the results correspond to numeral v and delta-v. Finally, changes on view is applied to view. When deleting tables from views, tables to be deleted are identified by joining the delta table with the view. The tables are deleted as many as specified multiplicity by numbering numbered using row number function, like this. Then inserting a table into the view, tables are dedicated to the specified multiplicity using row-rate series function, like this. Next, I'll explain our progress since the initial patch. Now we are supporting some aggregates, self-joins, avatar-joins, and some queries, including exist-close. We also support role-level security, refresh wizard with no-data command, and pg-dump restore. And so on. We are now supporting some built-in aggregates function. That is count-sum-min-max-average, with or without group-by-close. As a restriction, expressions specified in group-by-close must appear in the target list of the view. Aggregate views have one or more hidden columns. For example, count-value is stored for multiplicity of duplicated tables, and count and some values are stored for average function. A value is performed on table-deltas, table changes, and the values in the view. Aggregate values in the view are updated using these results. The way of updating view depends on the kind of aggregates function. Here are examples of updating aggregated values. We can update count or sum value by simply applying the result calculated from the data tables. Average can be updated using sum and count values stored as hidden columns. In many or max cases, it becomes more complicated. When tables are inserted, the smaller values fit when the current mean value and the value calculated from the data table is used. When tables are deleted, if all the mean values are deleted from the view, it needs to recompute the new values from base tables. Here, I'll show a simple performance evaluation of aggregate view. I created two materialized views of aggregates. On PG-Benz account, the scale factor is 1000. One is a normal materialized view, and another is with IVM option. These are the results. The refresh of the normal materialized view took more than 30 seconds, while updating the table in the base table took only 30 milliseconds. It's a thousand times faster than the normal refresh. So IVM is so rapid. Of course, the view was updated automatically and correctly. Next, I'll explain about simultaneous modification of multiple tables. This is possible when modifying CTs, triggers, or polling key control range is used. Note that self-join is essentially the same situation since same tables in an query can be regarded as defined tables with same contents. In theory, then multiple tables are modified simultaneously. We need pre-update state of tables as well as post-update state of tables. In our implementation, pre-update table state is available by applying table deltas inversely. Especially inserted tuple can be removed by filtering with X-min and C-min system columns. This is a query to get pre-update table state. Upper part is removing inserted tuples and this part is affending deleted tuples. In addition, when multiple tables are modified simultaneously, after triggers are fired more than once. Each trigger extracts and stores each tables change. The view is updated incrementally in the final of the trigger call. Next, I'll explain after-join support. In after-join, there are new extended tuples that is dangling tuples, which appear when the join condition does not meet. So tuples are inserted into a table. Dangling tuples might be deleted from the view and when tuples are deleted from the view, dangling tuples might be inserted into the view. As a result, the view is maintained in two steps. First, calculating and applying deltas similar to in-a-join case. Second, handling additional dangling tuples. I implemented this based on Rathon and Schwarz algorithm with some theory expansion to allow tuple duplicates. Here is a performance evaluation of in-a and after-join view. There are two materialized views. One is in-a-join and another is full after-join on Pgbench tables. Also, there are three conditions of indexes on materialized view. No index without index. And index on the primary key columns of base tables. And additional two index on columns used in join conditions. This is the result of execution of updating tuples. This is the result of execution of time of updating tuples in Pgbench accounts. Refresh of this view took more than 8 seconds. In all cases, IVM is faster than refresh. However, for effective maintenance, an appropriate index is required to search tuples to be deleted. And with additional indexes, the result shows comparable performance between in-a and after-join views. However, for effective maintenance of after-join views, indexes on join condition columns are required to search dangling tuples in the view to be deleted or for inserted. As for subquery support, in our implementation, XGIST in fair close is supported. In implementation, the row number count of the XGIST subquery is stored in the view as a hidden column. And FEN, a table in this subquery is modified. If the count becomes zero, the tuple in the view should be deleted. Otherwise, the tuple remains in the view. Also, we support simple subqueries, including only selection, projection, or in-a-join. Okay. As for the progress, we are now supporting with no data. This is an option for create material view or refresh commands. If this is specified, the triggers are dropped from base tables, and view is not automatically updated even when a base table is modified. Also, the materializer view becomes not scalable. To support role-level security, FEN view is updated incrementally, base tables are accessed with view owners privilege. Also, PGDAMP and Distora are now supporting the new syntax create incremental materialized view. And previously, we are using temporary tables to store view deltas, but it caused several problems. For example, system catalog brought and prepared products and available and so on. Now, we don't use this, we don't use temporary tables, and we are using tuple store instead. Finally, we have improved view access performance. For previous read, the generate series function is used to out-of-foot duplicated tables, and so its overhead was so high. But now, we don't use generate series function at select on views. Okay, now, here is a demonstration. Hoshiai-san will show the demonstration. My name is Takuma Hoshiai. Currently, I am an engineer at SRA OSS in Japan too. I have participated in the development of IBM since last year. I am in charge of some implementation of future and performance evaluation. Today, I talk about simple demonstration of IBM. The environment used is tiny TPCC like tables created with JDBC runner. TPCC is a reproduction of the processes about transportation and sales of products in multiple warehouses. The data load was pre-created with a scale factor of 64. So, this time, I created a database with scale factor 64, and the database size is about 7GB. I try creating a mathes view that analyze the number of payment, total amount, and maximum amount for each warehouse. This can be achieved by joining the three tables about warehouse, distinct, and history table, and using group by clothes and aggregation function. The scale to use is like this. This result has about 640 rows. To create a mathes view with IBM, simply add the incremental option to the create mathes view query. Then, create ordinary mathes view like this. Create mathes view with IBM. We can create like this, only add the incremental option. By the way, IBM Trigger is created to detect the updates of the base tables and automatically updates the mathes view with IBM. We can check IBM Trigger with this command. These IBM Triggers have a new depth type of small m. These Triggers are created or deleted by creating and deleting mathes view and the deflation with no data command. Immediately after creation, the result is the same for normal mathes view and IBM. This result is the same. The different point is that the IBM is automatically updated to the latest state when the base table is updated. For example, add the payment history to the history table. This insert query is finished in a flash and I confirm IBM view. The first row, the max amount has changed and is the latest value. Of course, ordinary mathes view is still old. If we execute refresh command with mathes view, the latest value is reflected by ordinary mathes view. But it takes some time. This example isn't too much time for demo, but the defense may be even bigger for real large mathes view. By the way, in the current logic, the trigger is used to immediately update the mathes view in the transaction. So, rollback command will list IBM. The first row is rollbacked. In this way, after updating the data, you can immediately check the overall impact with mathes view and select Feather to reflect the data update. That's it for the demo. Last, I'll show current restrictions on our implementation. We are now supporting selection, projection, and outer join, distinct, some aggregates, self-join, exist, and simple subqueries. On the other hand, we are not supporting other aggregates, user-defined aggregates, and having complex subqueries, including aggregates, or outer join, and so on, city, window functions, set operations, order by, and limit offset close. After outer join, only simple equal join is supported, and it cannot be used together with aggregates or subqueries. And at exist, we don't support or exist or not exist, and it cannot be used together with aggregates. We checked how many queries in TPC benchmark are supported. As a result, 9 of 22 TPC-H queries are supported, and 20 of 99 TPC-DC queries are supported. In both cases, order by and limit offset close are ignored. In TPC-DS queries, satisfactory queries failed to city ease, and 11 queries failed due to aggregate in subquery. In summary, we are now working on implementation of incremental view maintenance on post-rescue L. This enables rapid and automatic update of materialized views. As progress since the initial patch, we are now supporting some aggregates, outer join, set joins, and some subqueries. As the future works, we plan to relax restrictions. For example, views using city ease, subqueries including aggregates, and so on. Also, we would like to work on deferred maintenance using table change logs. We would need more performance improvement and some optimization too. The patch is proposed and discussed in PGSK HACCAS mailing list. The subject is implementing incremental view maintenance, and they are our GitHub repository. We would appreciate it if you give us any feedback, any comment, any suggestion. We are waiting for your feedback. Thank you. Thank you.
 Hi everyone, I'm Yu-Gon Nagata.  In this session, I would like to talk about the way for updating materialized views rapidly.  First, let me introduce us. I am Yu-Gon Nagata and a software engineer at SROASOS Inc. Japan.  I am in charge of R&D and now working on incremental view maintenance called IVM. This is today's topic.  Takuma Hoshiai is also a software engineer and a member of our IVM project. He will show a demonstration in this talk.  This is the outline of this talk.  Firstly, I'll introduce incremental view maintenance. Shortly, this is a way to refresh materialized views rapidly.  Then, I'll talk about our implementation of IVM and progress since the initial patch.  Next, I'll show some behavior examples, including demonstration and performance evaluation.  And finally, I'll summarize this talk.  First, a view is a virtual relation defined by a view definition query.  The query is executed when view is referred to by a select statement.  On the other hand, materialized view is a view whose results are stored in database and this enables a quick response to clients.  This is useful for example for analyzing large data in the data warehouse.  However, materialized view needs to be maintained after a base table is modified to keep consistency between views and base tables.  Refreshing materialized view is a way of the maintenance.  Then, refresh materialized view command is issued.  The contents of materialized view is updated to the latest state.  However, this needs to be computing the contents of the view from scratch, so it takes a long time.  With concurrently option, the materialized view is refreshed with a weaker look, but it still needs to be computing the contents from scratch.  Incremental view maintenance, IVM, is another technique to view maintenance.  This computes and applies only the incremental changes to the materialized views.  This figure shows IVM formally.  From the base tables contents and view definition, we can compute the contents of materialized view.  After base table is updated, we get new base table.  Using this and the query, we can get the updated materialized view.  This green path is a recomputation and what refresh materialized view command starts.  On the other hand, we can compute the delta of view, changes on views from changes on tables.  We can apply this to the materialized view.  We can get the new materialized view data without recomputing.  This red path is a process of incremental view maintenance.  The first patch of our IVM implementation was submitted to PSKL hackers a year ago, and the subject is implementing incremental view maintenance.  I also had a presentation about it at PGCon last year.  Using this IVM feature, materialized views can be updated automatically and incrementally when base tables are updated.  You don't need to write a trigger to maintain materialized views by yourself.  How effective is our IVM implementation?  This is an example of TPCH query 1.0.1.0.1.  This is a query to aggregate on a large table.  With skill factor 1, this query took about 11 seconds.  While select on the materialized view took only 3 milliseconds, so it is a quick response.  However, if the rest of this materialized view took 24 seconds,  on the other hand, one table on the base table is updated.  Its incremental maintenance took only 22 milliseconds, that is, the view is updated so rapidly.  In the initial patch, we supported selection, projection,  inadjoining, distinct class, and build with tuple duplicates. Additionally, the current patch supports also some aggregates,  self-join, outer join, sub queries, including existing class.  It also supports refresh with node data commands, low-level security, and pgdamp and restore.  I'll also explain the progress in this talk.  Okay, I'll explain the basic theory of IVM.  The view definition is described in a relational algebra form.  This is an example of this simple natural join of table R and S.  Change of base table is represented like this.  This navla, inverted Greek delta, means tuples, derived from the table.  And the Greek delta means tuples inserted into the table.  Using these view definitions and these changes, we can calculate the changes on the view like this,  and update the view by applying these changes to views.  This is an example.  Contents of table R and S are like this.  The natural join view with contents will be like this.  After table R is changed, the deleted and inserted changes are like this.  The changes on the view are calculated by joining table R changes and base table S respectively.  Finally, the view is updated by applying calculated changes like this.  In the bad timing of view maintenance, there are two approaches.  Immediate maintenance and deferred maintenance.  In immediate maintenance, views are updated in the same transaction where the base table is updated.  In deferred maintenance, views are updated after the transaction is committed.  For example, when view is accessed, where as a response to user command like refresh were updated periodically and so on.  In our implementation, we started from immediate approach since it requires less number of calls.  The third approach needs a mechanism to manage logs for recording changes of base tables.  Implementing this is not trivial work.  This is the overview of IBM implementation.  There are three processes.  First, when a base table is modified, its change is extracted.  Second, changes on view is calculated from changes on tables and base tables and view definition query.  Finally, the changes on views are applied to the view and view is updated.  In our implementation, table changes are extracted using after-triggers and transition tables.  Changes on view is calculated based on relational or bug algebra.  And the changes are applied to the view using SQL query.  For creating materialized view with IBM support, we use create incremental materialized view.  This is a tentative syntax to create materialized views with IBM support.  Incremental keyword is an extension of our implementation.  At creating materialized views, after-triggers are created on all base tables.  These are created automatically and internally for insert, delete, and update commands and as a statement limit trigger.  And with transition tables.  Transition tables is a feature of after-trigger.  Using these, changes on tables can be referred to in the trigger function, like normal tables.  There are two tables.  One contains tables deleted from the table.  Another contains tables inserted into the table.  In theory, these tables are corresponding to numeral and delta-r respectively.  When calculating delta on views, we use the views definition query with some relights.  In these relights, the modified table is replaced with a transition table, like this.  And multiplicity of duplicate tables are counted using count-as-t aggregate, like this.  In theory, the results correspond to numeral v and delta-v.  Finally, changes on view is applied to view.  When deleting tables from views, tables to be deleted are identified by joining the delta table with the view.  The tables are deleted as many as specified multiplicity by numbering numbered using row number function, like this.  Then inserting a table into the view, tables are dedicated to the specified multiplicity using row-rate series function, like this.  Next, I'll explain our progress since the initial patch.  Now we are supporting some aggregates, self-joins, avatar-joins, and some queries, including exist-close.  We also support role-level security, refresh wizard with no-data command, and pg-dump restore.  And so on.  We are now supporting some built-in aggregates function.  That is count-sum-min-max-average, with or without group-by-close.  As a restriction, expressions specified in group-by-close must appear in the target list of the view.  Aggregate views have one or more hidden columns. For example, count-value is stored for multiplicity of duplicated tables, and count and some values are stored for average function.  A value is performed on table-deltas, table changes, and the values in the view.  Aggregate values in the view are updated using these results.  The way of updating view depends on the kind of aggregates function.  Here are examples of updating aggregated values.  We can update count or sum value by simply applying the result calculated from the data tables.  Average can be updated using sum and count values stored as hidden columns.  In many or max cases, it becomes more complicated.  When tables are inserted, the smaller values fit when the current mean value and the value calculated from the data table is used.  When tables are deleted, if all the mean values are deleted from the view, it needs to recompute the new values from base tables.  Here, I'll show a simple performance evaluation of aggregate view.  I created two materialized views of aggregates. On PG-Benz account, the scale factor is 1000.  One is a normal materialized view, and another is with IVM option.  These are the results.  The refresh of the normal materialized view took more than 30 seconds, while updating the table in the base table took only 30 milliseconds.  It's a thousand times faster than the normal refresh.  So IVM is so rapid.  Of course, the view was updated automatically and correctly.  Next, I'll explain about simultaneous modification of multiple tables.  This is possible when modifying CTs, triggers, or polling key control range is used.  Note that self-join is essentially the same situation since same tables in an query can be regarded as defined tables with same contents.  In theory, then multiple tables are modified simultaneously.  We need pre-update state of tables as well as post-update state of tables.  In our implementation, pre-update table state is available by applying table deltas inversely.  Especially inserted tuple can be removed by filtering with X-min and C-min system columns.  This is a query to get pre-update table state.  Upper part is removing inserted tuples and this part is affending deleted tuples.  In addition, when multiple tables are modified simultaneously, after triggers are fired more than once.  Each trigger extracts and stores each tables change.  The view is updated incrementally in the final of the trigger call.  Next, I'll explain after-join support.  In after-join, there are new extended tuples that is dangling tuples, which appear when the join condition does not meet.  So tuples are inserted into a table.  Dangling tuples might be deleted from the view and when tuples are deleted from the view, dangling tuples might be inserted into the view.  As a result, the view is maintained in two steps.  First, calculating and applying deltas similar to in-a-join case.  Second, handling additional dangling tuples.  I implemented this based on Rathon and Schwarz algorithm with some theory expansion to allow tuple duplicates.  Here is a performance evaluation of in-a and after-join view.  There are two materialized views.  One is in-a-join and another is full after-join on Pgbench tables.  Also, there are three conditions of indexes on materialized view.  No index without index.  And index on the primary key columns of base tables.  And additional two index on columns used in join conditions.  This is the result of execution of updating tuples.  This is the result of execution of time of updating tuples in Pgbench accounts.  Refresh of this view took more than 8 seconds.  In all cases, IVM is faster than refresh.  However, for effective maintenance, an appropriate index is required to search tuples to be deleted.  And with additional indexes, the result shows comparable performance between in-a and after-join views.  However, for effective maintenance of after-join views, indexes on join condition columns are required to search dangling tuples in the view to be deleted or for inserted.  As for subquery support, in our implementation, XGIST in fair close is supported.  In implementation, the row number count of the XGIST subquery is stored in the view as a hidden column.  And FEN, a table in this subquery is modified.  If the count becomes zero, the tuple in the view should be deleted.  Otherwise, the tuple remains in the view.  Also, we support simple subqueries, including only selection, projection, or in-a-join.  Okay.  As for the progress, we are now supporting with no data.  This is an option for create material view or refresh commands.  If this is specified, the triggers are dropped from base tables, and view is not automatically updated even when a base table is modified.  Also, the materializer view becomes not scalable.  To support role-level security, FEN view is updated incrementally, base tables are accessed with view owners privilege.  Also, PGDAMP and Distora are now supporting the new syntax create incremental materialized view.  And previously, we are using temporary tables to store view deltas, but it caused several problems.  For example, system catalog brought and prepared products and available and so on.  Now, we don't use this, we don't use temporary tables, and we are using tuple store instead.  Finally, we have improved view access performance.  For previous read, the generate series function is used to out-of-foot duplicated tables, and so its overhead was so high.  But now, we don't use generate series function at select on views.  Okay, now, here is a demonstration.  Hoshiai-san will show the demonstration.  My name is Takuma Hoshiai.  Currently, I am an engineer at SRA OSS in Japan too.  I have participated in the development of IBM since last year.  I am in charge of some implementation of future and performance evaluation.  Today, I talk about simple demonstration of IBM.  The environment used is tiny TPCC like tables created with JDBC runner.  TPCC is a reproduction of the processes about transportation and sales of products in multiple warehouses.  The data load was pre-created with a scale factor of 64.  So, this time, I created a database with scale factor 64, and the database size is about 7GB.  I try creating a mathes view that analyze the number of payment, total amount, and maximum amount for each warehouse.  This can be achieved by joining the three tables about warehouse, distinct, and history table,  and using group by clothes and aggregation function.  The scale to use is like this.  This result has about 640 rows.  To create a mathes view with IBM, simply add the incremental option to the create mathes view query.  Then, create ordinary mathes view like this.  Create mathes view with IBM.  We can create like this, only add the incremental option.  By the way, IBM Trigger is created to detect the updates of the base tables and automatically updates the mathes view with IBM.  We can check IBM Trigger with this command.  These IBM Triggers have a new depth type of small m.  These Triggers are created or deleted by creating and deleting mathes view and the deflation with no data command.  Immediately after creation, the result is the same for normal mathes view and IBM.  This result is the same.  The different point is that the IBM is automatically updated to the latest state when the base table is updated.  For example, add the payment history to the history table.  This insert query is finished in a flash and I confirm IBM view.  The first row, the max amount has changed and is the latest value.  Of course, ordinary mathes view is still old.  If we execute refresh command with mathes view, the latest value is reflected by ordinary mathes view.  But it takes some time.  This example isn't too much time for demo, but the defense may be even bigger for real large mathes view.  By the way, in the current logic, the trigger is used to immediately update the mathes view in the transaction.  So, rollback command will list IBM.  The first row is rollbacked.  In this way, after updating the data, you can immediately check the overall impact with mathes view and select Feather to reflect the data update.  That's it for the demo.  Last, I'll show current restrictions on our implementation.  We are now supporting selection, projection, and outer join, distinct, some aggregates, self-join, exist, and simple subqueries.  On the other hand, we are not supporting other aggregates, user-defined aggregates, and having complex subqueries, including aggregates, or outer join, and so on, city, window functions, set operations, order by, and limit offset close.  After outer join, only simple equal join is supported, and it cannot be used together with aggregates or subqueries.  And at exist, we don't support or exist or not exist, and it cannot be used together with aggregates.  We checked how many queries in TPC benchmark are supported.  As a result, 9 of 22 TPC-H queries are supported, and 20 of 99 TPC-DC queries are supported.  In both cases, order by and limit offset close are ignored.  In TPC-DS queries, satisfactory queries failed to city ease, and 11 queries failed due to aggregate in subquery.  In summary, we are now working on implementation of incremental view maintenance on post-rescue L.  This enables rapid and automatic update of materialized views.  As progress since the initial patch, we are now supporting some aggregates, outer join, set joins, and some subqueries.  As the future works, we plan to relax restrictions.  For example, views using city ease, subqueries including aggregates, and so on.  Also, we would like to work on deferred maintenance using table change logs.  We would need more performance improvement and some optimization too.  The patch is proposed and discussed in PGSK HACCAS mailing list.  The subject is implementing incremental view maintenance, and they are our GitHub repository.  We would appreciate it if you give us any feedback, any comment, any suggestion.  We are waiting for your feedback.  Thank you.  Thank you.
The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_0_107.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_108_215.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_216_635.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_636_1535.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_1536_2603.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_2604_3995.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_3996_5195.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_5196_7379.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_7380_8555.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_8556_10247.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_10248_11291.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_11292_12899.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_12900_13367.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_13368_14003.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_14004_14279.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_14280_16055.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_16056_17891.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_17892_18707.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_18708_20303.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_20304_21467.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_21468_22499.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_22500_23003.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_23004_23855.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_23856_25811.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_25812_27539.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_27540_28187.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_28188_30683.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_30684_32507.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_32508_34403.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_34404_35615.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_35616_37583.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_37584_39083.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_39084_40439.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_40440_42035.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_42036_42311.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_42312_43007.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_43008_43763.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_43764_44927.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_44928_44987.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_44988_45047.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_45048_46055.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_46056_46811.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_46812_47243.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_47244_47303.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_47304_47867.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_47868_47915.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_47916_48731.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_48732_49439.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_49440_50339.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_50340_50399.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_50400_50927.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_50928_51263.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_51264_51323.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_51324_51791.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_51792_51887.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_51888_53027.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_53028_55031.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_55032_57611.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_57612_57695.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_57696_57827.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_57828_57983.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_57984_58115.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_58116_58175.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_58176_58235.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_58236_58319.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_58320_58439.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_58440_58499.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_58500_58751.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_58752_58811.png The_Way_for_Updating_Materialized_Views_Rapidly_-__wxSJ7E8iAwQ_58812_58968.png
Hi everyone, I'm Yu-Gon Nagata. In this session, I would like to talk about the way for updating materialized views rapidly. First, let me introduce us. I am Yu-Gon Nagata and a software engineer at SROASOS Inc. Japan. I am in charge of R&D and now working on incremental view maintenance called IVM. This is today's topic. Takuma Hoshiai is also a software engineer and a member of our IVM project. He will show a demonstration in this talk. This is the outline of this talk. Firstly, I'll introduce incremental view maintenance. Shortly, this is a way to refresh materialized views rapidly. Then, I'll talk about our implementation of IVM and progress since the initial patch. Next, I'll show some behavior examples, including demonstration and performance evaluation. And finally, I'll summarize this talk. First, a view is a virtual relation defined by a view definition query. The query is executed when view is referred to by a select statement. On the other hand, materialized view is a view whose results are stored in database and this enables a quick response to clients. This is useful for example for analyzing large data in the data warehouse. However, materialized view needs to be maintained after a base table is modified to keep consistency between views and base tables. Refreshing materialized view is a way of the maintenance. Then, refresh materialized view command is issued. The contents of materialized view is updated to the latest state. However, this needs to be computing the contents of the view from scratch, so it takes a long time. With concurrently option, the materialized view is refreshed with a weaker look, but it still needs to be computing the contents from scratch. Incremental view maintenance, IVM, is another technique to view maintenance. This computes and applies only the incremental changes to the materialized views. This figure shows IVM formally. From the base tables contents and view definition, we can compute the contents of materialized view. After base table is updated, we get new base table. Using this and the query, we can get the updated materialized view. This green path is a recomputation and what refresh materialized view command starts. On the other hand, we can compute the delta of view, changes on views from changes on tables. We can apply this to the materialized view. We can get the new materialized view data without recomputing. This red path is a process of incremental view maintenance. The first patch of our IVM implementation was submitted to PSKL hackers a year ago, and the subject is implementing incremental view maintenance. I also had a presentation about it at PGCon last year. Using this IVM feature, materialized views can be updated automatically and incrementally when base tables are updated. You don't need to write a trigger to maintain materialized views by yourself. How effective is our IVM implementation? This is an example of TPCH query 1.0.1.0.1. This is a query to aggregate on a large table. With skill factor 1, this query took about 11 seconds. While select on the materialized view took only 3 milliseconds, so it is a quick response. However, if the rest of this materialized view took 24 seconds, on the other hand, one table on the base table is updated. Its incremental maintenance took only 22 milliseconds, that is, the view is updated so rapidly. In the initial patch, we supported selection, projection, inadjoining, distinct class, and build with tuple duplicates. Additionally, the current patch supports also some aggregates, self-join, outer join, sub queries, including existing class. It also supports refresh with node data commands, low-level security, and pgdamp and restore. I'll also explain the progress in this talk. Okay, I'll explain the basic theory of IVM. The view definition is described in a relational algebra form. This is an example of this simple natural join of table R and S. Change of base table is represented like this. This navla, inverted Greek delta, means tuples, derived from the table. And the Greek delta means tuples inserted into the table. Using these view definitions and these changes, we can calculate the changes on the view like this, and update the view by applying these changes to views. This is an example. Contents of table R and S are like this. The natural join view with contents will be like this. After table R is changed, the deleted and inserted changes are like this. The changes on the view are calculated by joining table R changes and base table S respectively. Finally, the view is updated by applying calculated changes like this. In the bad timing of view maintenance, there are two approaches. Immediate maintenance and deferred maintenance. In immediate maintenance, views are updated in the same transaction where the base table is updated. In deferred maintenance, views are updated after the transaction is committed. For example, when view is accessed, where as a response to user command like refresh were updated periodically and so on. In our implementation, we started from immediate approach since it requires less number of calls. The third approach needs a mechanism to manage logs for recording changes of base tables. Implementing this is not trivial work. This is the overview of IBM implementation. There are three processes. First, when a base table is modified, its change is extracted. Second, changes on view is calculated from changes on tables and base tables and view definition query. Finally, the changes on views are applied to the view and view is updated. In our implementation, table changes are extracted using after-triggers and transition tables. Changes on view is calculated based on relational or bug algebra. And the changes are applied to the view using SQL query. For creating materialized view with IBM support, we use create incremental materialized view. This is a tentative syntax to create materialized views with IBM support. Incremental keyword is an extension of our implementation. At creating materialized views, after-triggers are created on all base tables. These are created automatically and internally for insert, delete, and update commands and as a statement limit trigger. And with transition tables. Transition tables is a feature of after-trigger. Using these, changes on tables can be referred to in the trigger function, like normal tables. There are two tables. One contains tables deleted from the table. Another contains tables inserted into the table. In theory, these tables are corresponding to numeral and delta-r respectively. When calculating delta on views, we use the views definition query with some relights. In these relights, the modified table is replaced with a transition table, like this. And multiplicity of duplicate tables are counted using count-as-t aggregate, like this. In theory, the results correspond to numeral v and delta-v. Finally, changes on view is applied to view. When deleting tables from views, tables to be deleted are identified by joining the delta table with the view. The tables are deleted as many as specified multiplicity by numbering numbered using row number function, like this. Then inserting a table into the view, tables are dedicated to the specified multiplicity using row-rate series function, like this. Next, I'll explain our progress since the initial patch. Now we are supporting some aggregates, self-joins, avatar-joins, and some queries, including exist-close. We also support role-level security, refresh wizard with no-data command, and pg-dump restore. And so on. We are now supporting some built-in aggregates function. That is count-sum-min-max-average, with or without group-by-close. As a restriction, expressions specified in group-by-close must appear in the target list of the view. Aggregate views have one or more hidden columns. For example, count-value is stored for multiplicity of duplicated tables, and count and some values are stored for average function. A value is performed on table-deltas, table changes, and the values in the view. Aggregate values in the view are updated using these results. The way of updating view depends on the kind of aggregates function. Here are examples of updating aggregated values. We can update count or sum value by simply applying the result calculated from the data tables. Average can be updated using sum and count values stored as hidden columns. In many or max cases, it becomes more complicated. When tables are inserted, the smaller values fit when the current mean value and the value calculated from the data table is used. When tables are deleted, if all the mean values are deleted from the view, it needs to recompute the new values from base tables. Here, I'll show a simple performance evaluation of aggregate view. I created two materialized views of aggregates. On PG-Benz account, the scale factor is 1000. One is a normal materialized view, and another is with IVM option. These are the results. The refresh of the normal materialized view took more than 30 seconds, while updating the table in the base table took only 30 milliseconds. It's a thousand times faster than the normal refresh. So IVM is so rapid. Of course, the view was updated automatically and correctly. Next, I'll explain about simultaneous modification of multiple tables. This is possible when modifying CTs, triggers, or polling key control range is used. Note that self-join is essentially the same situation since same tables in an query can be regarded as defined tables with same contents. In theory, then multiple tables are modified simultaneously. We need pre-update state of tables as well as post-update state of tables. In our implementation, pre-update table state is available by applying table deltas inversely. Especially inserted tuple can be removed by filtering with X-min and C-min system columns. This is a query to get pre-update table state. Upper part is removing inserted tuples and this part is affending deleted tuples. In addition, when multiple tables are modified simultaneously, after triggers are fired more than once. Each trigger extracts and stores each tables change. The view is updated incrementally in the final of the trigger call. Next, I'll explain after-join support. In after-join, there are new extended tuples that is dangling tuples, which appear when the join condition does not meet. So tuples are inserted into a table. Dangling tuples might be deleted from the view and when tuples are deleted from the view, dangling tuples might be inserted into the view. As a result, the view is maintained in two steps. First, calculating and applying deltas similar to in-a-join case. Second, handling additional dangling tuples. I implemented this based on Rathon and Schwarz algorithm with some theory expansion to allow tuple duplicates. Here is a performance evaluation of in-a and after-join view. There are two materialized views. One is in-a-join and another is full after-join on Pgbench tables. Also, there are three conditions of indexes on materialized view. No index without index. And index on the primary key columns of base tables. And additional two index on columns used in join conditions. This is the result of execution of updating tuples. This is the result of execution of time of updating tuples in Pgbench accounts. Refresh of this view took more than 8 seconds. In all cases, IVM is faster than refresh. However, for effective maintenance, an appropriate index is required to search tuples to be deleted. And with additional indexes, the result shows comparable performance between in-a and after-join views. However, for effective maintenance of after-join views, indexes on join condition columns are required to search dangling tuples in the view to be deleted or for inserted. As for subquery support, in our implementation, XGIST in fair close is supported. In implementation, the row number count of the XGIST subquery is stored in the view as a hidden column. And FEN, a table in this subquery is modified. If the count becomes zero, the tuple in the view should be deleted. Otherwise, the tuple remains in the view. Also, we support simple subqueries, including only selection, projection, or in-a-join. Okay. As for the progress, we are now supporting with no data. This is an option for create material view or refresh commands. If this is specified, the triggers are dropped from base tables, and view is not automatically updated even when a base table is modified. Also, the materializer view becomes not scalable. To support role-level security, FEN view is updated incrementally, base tables are accessed with view owners privilege. Also, PGDAMP and Distora are now supporting the new syntax create incremental materialized view. And previously, we are using temporary tables to store view deltas, but it caused several problems. For example, system catalog brought and prepared products and available and so on. Now, we don't use this, we don't use temporary tables, and we are using tuple store instead. Finally, we have improved view access performance. For previous read, the generate series function is used to out-of-foot duplicated tables, and so its overhead was so high. But now, we don't use generate series function at select on views. Okay, now, here is a demonstration. Hoshiai-san will show the demonstration. My name is Takuma Hoshiai. Currently, I am an engineer at SRA OSS in Japan too. I have participated in the development of IBM since last year. I am in charge of some implementation of future and performance evaluation. Today, I talk about simple demonstration of IBM. The environment used is tiny TPCC like tables created with JDBC runner. TPCC is a reproduction of the processes about transportation and sales of products in multiple warehouses. The data load was pre-created with a scale factor of 64. So, this time, I created a database with scale factor 64, and the database size is about 7GB. I try creating a mathes view that analyze the number of payment, total amount, and maximum amount for each warehouse. This can be achieved by joining the three tables about warehouse, distinct, and history table, and using group by clothes and aggregation function. The scale to use is like this. This result has about 640 rows. To create a mathes view with IBM, simply add the incremental option to the create mathes view query. Then, create ordinary mathes view like this. Create mathes view with IBM. We can create like this, only add the incremental option. By the way, IBM Trigger is created to detect the updates of the base tables and automatically updates the mathes view with IBM. We can check IBM Trigger with this command. These IBM Triggers have a new depth type of small m. These Triggers are created or deleted by creating and deleting mathes view and the deflation with no data command. Immediately after creation, the result is the same for normal mathes view and IBM. This result is the same. The different point is that the IBM is automatically updated to the latest state when the base table is updated. For example, add the payment history to the history table. This insert query is finished in a flash and I confirm IBM view. The first row, the max amount has changed and is the latest value. Of course, ordinary mathes view is still old. If we execute refresh command with mathes view, the latest value is reflected by ordinary mathes view. But it takes some time. This example isn't too much time for demo, but the defense may be even bigger for real large mathes view. By the way, in the current logic, the trigger is used to immediately update the mathes view in the transaction. So, rollback command will list IBM. The first row is rollbacked. In this way, after updating the data, you can immediately check the overall impact with mathes view and select Feather to reflect the data update. That's it for the demo. Last, I'll show current restrictions on our implementation. We are now supporting selection, projection, and outer join, distinct, some aggregates, self-join, exist, and simple subqueries. On the other hand, we are not supporting other aggregates, user-defined aggregates, and having complex subqueries, including aggregates, or outer join, and so on, city, window functions, set operations, order by, and limit offset close. After outer join, only simple equal join is supported, and it cannot be used together with aggregates or subqueries. And at exist, we don't support or exist or not exist, and it cannot be used together with aggregates. We checked how many queries in TPC benchmark are supported. As a result, 9 of 22 TPC-H queries are supported, and 20 of 99 TPC-DC queries are supported. In both cases, order by and limit offset close are ignored. In TPC-DS queries, satisfactory queries failed to city ease, and 11 queries failed due to aggregate in subquery. In summary, we are now working on implementation of incremental view maintenance on post-rescue L. This enables rapid and automatic update of materialized views. As progress since the initial patch, we are now supporting some aggregates, outer join, set joins, and some subqueries. As the future works, we plan to relax restrictions. For example, views using city ease, subqueries including aggregates, and so on. Also, we would like to work on deferred maintenance using table change logs. We would need more performance improvement and some optimization too. The patch is proposed and discussed in PGSK HACCAS mailing list. The subject is implementing incremental view maintenance, and they are our GitHub repository. We would appreciate it if you give us any feedback, any comment, any suggestion. We are waiting for your feedback. Thank you. Thank you.
base table is modified to keep consistency Then, refresh materialized view command is to be computing the contents from scratch. technique to view maintenance. This computes and applies only the incremental changes to the definition, we can compute the contents of materialized view data without recomputing. This maintenance. The first patch of our IVM trigger to maintain materialized views by some aggregates, self-join, outer join, sub also explain the progress in this talk. Okay, tables and base tables and view definition query. tables is a feature of after-trigger. Using needs to recompute the new values from base option. These are the results. The refresh of the all cases, IVM is faster than refresh. However, or in-a-join. Okay. As for the progress, we are base tables, and view is not automatically temporary tables to store view deltas, but it caused several problems. For example, system IBM since last year. I am in charge of some way, in the current logic, the trigger is used to The first row is rollbacked. In this way, after our implementation. We are now supporting including aggregates, or outer join, and so on, supported. In both cases, order by and limit outer join, set joins, and some subqueries. As view maintenance, and they are our GitHub