Skip to content

2.25.1.0-b273

2.25.1.0-b273: [#24732] YSQL: Refresh materialized view in-place during upgrade
@hari90 hari90 tagged this 31 Jan 06:02 
Summary:
**Behavior before this change:**
Non-concurrent refresh of a materialized view involves generating the new data in a new DocDB table, and then swapping the tables by replacing the `relfinenode` info of the matview. This involves 16 RPCs to yb-master (pg catalog) with 73 pgsql_write_batches. All of these are not allowed during the ysql major upgrade.
Concurrent refresh of a supported materialized view involves generating the new data in temp table, followed by doing a merge of the new data with the old data already in the matview using another temp table.
This involves 24 RPC calls to yb-master (pg catalog) with 241 pgsql_write_batches. However 23 of these RPCs are related to the temp tables (force_catalog_modifications set) which are allowed during ysql major upgrade. The only other RPC is related to modifying the `relispopulated` field.

**Behavior with this change:**
During ysql major upgrade changes to the `relispopulated` field is blocked. This is not an issue with concurrent refresh, since those are always blocked.
The non-concurrent refresh of matview now generates the new data in a temporary table, and use the more expensive method of running `DELETE FROM <matview>` followed by a `INSERT INTO <matview> SELECT * FROM <temp_table>`.
The advantage of this method is that all pg catalog modifications are only done to the temp table, which is allowed during the upgrade.
The performance tradeoff is acceptable, since we only expect small matviews to be refreshed frequently, hences needed during the upgrade, whereas the bigger ones can wait till after the upgrade to be refreshed.

This behavior is enabled by `yb_refresh_matview_in_place` or `yb_major_version_upgrade_compatibility>0`.

Concurrent refresh has not been modified, since it only creates temporary tables, and does not involve any other catalog modifications.

Fixes #24732
Jira: DB-13813

Test Plan: YsqlMajorUpgradeMatviewTest.TestMatView

Reviewers: telgersma, smishra, myang

Reviewed By: telgersma, myang

Subscribers: svc_phabricator, ybase, yql

Differential Revision: https://phorge.dev.yugabyte.com/D41504
Assets 2
Loading