Incremental view maintenance (IVM) is a great addition to PostgreSQL. You may already know it, even if you don’t, I believe you can catch up by reading its README.
I think IVM gives many use cases to your PostgreSQL. In Jetelina, we have adopted it to ‘js*’ in Jeteina-API.
IVM has an opportunity, not only to make your select sentence extremely fast, but also to ease the creation of a new table that you want in PostgreSQL. We strongly recommend adopting PostgreSQL for your web system due to this function if you are supposed to use something RDBMS.
Quick explanation of IVM, you can imagine it in a similar way as a ‘materialized view’ on Oracle Database. For example, there are two tables on PostgreSQL,the ‘A’ table and the ‘B’ table. In case you create a ‘select sentence’ from these two tables, you may want to use the ‘view’ function if these tables were bigger ones, because selecting big tables usually takes a higher cost. But as you know, the update on ‘A’ and/or ‘B’ is not reflected in the created ‘view’, you have to re-create it each time you update and/or delete data. IVM resolves this issue by using ‘trigger’ function. Let’s say simply, IVM creates a kind of ‘view’ table from multi tables that is able to reflect in the process of ‘insert/update/delete’ data. This ‘view’ table is usually smaller than its original tables because it is a part of the original ones, so the execution speed of selecting is quicker than the IVM one.
I have already explained about multi tables, however, of course, it can say the same thing in the case of a single big table.
Indeed, Jetelina adopts only in the case of multi tables.
A quick note, the use case of IVM in Jetelina is the same as js*(Jetelina-API), but behind it, it works differently with js*.
- Jetelina challenges to compare the execution speed of the SQL select sentence between non-IVM and IVM.
- Jetelina adopts ‘js*’ on IVM if speed(IVM) < speed(non-IVM).
- When you post ‘js*’ to Jetelina through HTTP, Jetelina executes the ‘js*’ that is for IVM, in case of 2 is.
I mean, Jetelina manages if it should use the IVM table or not. You simply post ‘js*’ to Jetelina, you do not need to care about using non-IVM or IVM.
Jetelina probes IVM is available or not on your system as well. So you can adopt IVM to your system later. You do not need to set any special information for Jetelina.
There are some details to use IVM, you can check them by opening the TIPS here.