5mins of Postgres E16: Incremental Materialized Views with pg_ivm and Looking Ahead to Postgres 15 Beta 1
Today, we're going to talk about incremental view maintenance, as well as the upcoming Postgres 15 beta release.
Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel.
Transcript
Let's take a look!
Using Incremental Materialized Views with pg_ivm
So, first of all, this is a new extension released by the SRA OSS team in Japan, which is called pg_ivm. pg_ivm is an extension you could install today on your production server, but I wouldn't recommend it because it's still early days.
This extension was released in its 1.0 release at the end of April 2022. It's a version of the incremental view maintenance feature that the SRA OSS team has been working on for a while and it's intended for Postgres 14 only, not usable on older releases.
This lets you create incremental materialized views - as the table changes they are refreshed automatically. The idea is that, instead of you having to run REFRESH MATERIALIZED VIEW, which you can do today in Postgres, this does the updates automatically.
The difference between a regular view and a materialized view, is the materialized view keeps a copy of the data, versus a regular view is essentially just a different lens of looking at the data. If you query a materialized view, you actually query that copy, whereas in a regular view, you go back to the original tables. Benefit of course is that data that's structured in a materialized view may be much faster to access because you are able to do a join ahead of time, or you are able to compute certain functions ahead of time. Read our blog posts about Materialized Views in Rails and PostgreSQL Views in Django if you want to learn more about views and materialized views!
The authors describe two different approaches to view maintenance:
- Immediate: In immediate maintenance, a view will be updated in the same transaction with the base table that's modified.
- Deferred:: In deferred maintenance, the materialized view would be updated after the transaction occurred.
The challenge they are trying to solve is that of immediate maintenance, where the materialized view is updated within the same transaction, using an AFTER trigger when the base table is modified.
The way that you use this extension is through the create_immv function. IMMV stands for incrementally maintainable materialized view. It's essentially a specialized version of a materialized view that supports incremental maintenance. In the example shown in my video,, you can see they're selecting from a table, there's three records in there, they insert into the base table and then there are four records.
Happens automatically, no extra work necessary.
If you want to use that, it's easily buildable if you run your own virtual machine. Again, I would only use it for testing at this point. Generally, this is a pretty straightforward extension.
Performance of Incremental Materialized Views with pg_ivm
In their example, the authors give performance numbers as well, where they're comparing a regular materialized view on a 10 million records pgbench table, and then they're updating a single record.
Of course, the point here is: the update itself is fast, but then I have to call this REFRESH MATERIALIZED VIEW, which takes 20 seconds. That's pretty expensive, which means I don't want to run this all the time. Versus using the incremental materialized view, you can create it in the same way, but then when you do the update it updates automatically, there's no extra work necessary. There's a little bit of overhead here, you will actually notice that the update statement here is slightly slower with the materialized view in place. Part of the reason for that is that they have to create an extra index on the base table!
There's still a bunch of restrictions here:
- It does support inner joins, but it doesn't support outer joins.
- It does support distinct, but it doesn't support window functions or CTEs.
For some use cases it may work today, for others it may not.
What's interesting about this work is that it is related to a patch that's currently being worked on for Postgres itself. The idea behind this is actually an older idea.
We can learn more about that by going to the Postgres Commitfests. Commitfests are how Postgres tracks its development cycles, and specifically its review cycle. It's where people contribute their patches, for example you or I could go in there and submit a patch to the next commitfest. It's a peer review type model. The next commifest is happening in July and that's actually the first commitfest for Postgres 16. The last commifest just concluded in March, and now we're in stabilization time for Postgres 15, and you could look through the commitfest to see what was committed.
When we look ahead, we can see in the next commifest, if we go for incremental materialized view maintenance, you can see this particular patch, the same authors that wrote the pg_ivm extension, they are contributing to Postgres itself and they're trying to make this an official Postgres feature. It's a very complex patch, unfortunately. But the hope is that Postgres 16, not the next release, but the next next release is going to be the time for incremental view maintenance in Postgres.
A first look at Postgres 15 Beta 1
Now, speaking of Postgres 15. Postgres 15 beta 1 is actually around the corner! The current plan from the release management team is that the first beta release is going to be available on May 19th. A little bit over two weeks from now, you can get your hands on the first version of Postgres 15 to test.
There's still a bunch of open items on Postgres 15, and I'm sure there will be more bugs that are discovered. You can see, for example, some of the open items are related to the new JSON features, other things are certainly worth testing.
It's very helpful for the community to get bug reports early, because then they can be fixed in subsequent beta releases before the release candidate and before the eventual release, most likely in September / October if we can believe the historic numbers.
Thanks so much for joining us for this episode of Postgres. Subscribe to our YouTube channel, sign up for our newsletter and follow us on Twitter to get updates about new episodes!