What does "modified frequently" actually mean?

[lucasmarcelli]

IVM is effective when we want to keep an IMMV up-to-date and small fraction of a base table is modified infrequently. Due to the overhead of immediate maintenance, IVM is not effective when a base table is modified frequently.

What does frequently mean in this context? Specifically for IVM is not effective when a base table is modified frequently. If I'm inserting/updating thousands of rows per hour on one or more base table, is this too frequent? The size of the base tables are in the 100-1000k rows range, so the proportion of updates is relatively low compared to the totals.

[yugo-n]

What does frequently mean in this context? Specifically for IVM is not effective when a base table is modified frequently.

This mean that pg_ivm causes low performance of base table modification due to the overhead, so pg_ivm is not suitable for use cases where the response time of table modification is crucial. If the table is modified frequently with the interval shorter than the response time, the impact on performance would be larger.

If I'm inserting/updating thousands of rows per hour on one or more base table, is this too frequent? The size of the base tables are in the 100-1000k rows range, so the proportion of updates is relatively low compared to the totals.

What frequency is acceptable would depend on the actual response time of table modification. You will need to make a performance measurement to know it.

[Nohac]

Would it be possible for this extension to support a debounce mechanism for cases where real-time updates aren’t necessary? I think a common use case for IVM would be maintaining tables that aggregate data from other tables, like statistics for dashboards or reports. In these cases, updating the view immediately on every change isn’t always needed, and a small delay (5–30 seconds) would be fine.

Take a scenario where there’s a burst of traffic lasting a few minutes, causing database operations that trigger IVM updates every second (or even more frequently). If IVM had a debounce option, it could delay updates until the requests stop instead of updating constantly. This should ideally be configurable.

Some possible configurations:

• Debounce delay (e.g. 5 seconds): Only update the IVM if no operations have happened for this period. So if updates happen at T=0s, T=3s, and T=4s, the view won’t update until T=9s.
• Max delay timeout (e.g. 30 seconds): Ensures the table still updates even if operations are continuous. If updates keep happening every second, the IVM would still apply changes every 30 seconds.

For cases like this, it would be possible to use pg_cron with a normal materialized view, but that approach requires a full refresh, which can be expensive for larger datasets. Having this built into IVM would be a lot more efficient since it avoids reloading everything and only applies incremental updates when needed.

This would help reduce unnecessary updates and hopefully help with the "modified frequently" issue. I’m not familiar with the internals of IVM, so I don’t know if this would be possible, just wanted to get the idea out there.