IronPort-SDR: 
 9Z3456/vCLp0TUP6xLQLuNvKNMWZrfh5lNI9NljeXZJ8DmdlRpR0g8YANyWQ5HcDaISv9Il/2n
 YuDAhNylZaRkfSFmP6rqCTZTtQH7qxiSukxvWBsUjTkLIfavNjtyta/TXtg8B6VvoxqwLnOTX/
 NSjKjgIGIpBHbj2/JOMg0kbPT9q0QFFPq7ZjSXoiRJC2spJLj1G1XBdfGOt9Njx7bTAc8dFjUF
 Yt5pKET5FJ3t7sntQj5RrqJ5Jkenxk+FSOYGLeja5+TngVt+u5k99AbBbCfzGDqspxlRq4RbmQ
 esM=
From: "tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.com>
To: 'Tatsuo Ishii' <ishii@sraoss.co.jp>
CC: "nagata@sraoss.co.jp" <nagata@sraoss.co.jp>, "michael@paquier.xyz"
	<michael@paquier.xyz>, "amitlangote09@gmail.com" <amitlangote09@gmail.com>,
	"hoshiai@sraoss.co.jp" <hoshiai@sraoss.co.jp>, "alvherre@2ndquadrant.com"
	<alvherre@2ndquadrant.com>, "thomas.munro@gmail.com"
	<thomas.munro@gmail.com>, "kgrittn@gmail.com" <kgrittn@gmail.com>,
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: RE: Implementing Incremental View Maintenance
Thread-Topic: Implementing Incremental View Maintenance
Thread-Index: 
 AQHVoP5MW2HR/XuY5Ei0uAznagbD+KedDO2AgAADVYCAAtRJAIAAYCsAgADtLACAAF9RgIAAKwYAgAAtBQCAAAEXAIACuTAAgAFygACAABH5AIAce2QAgAAkDrCABF1hAIAAYlmQ
Date: Mon, 23 Dec 2019 07:43:23 +0000
Message-ID: 
 <OSAPR01MB507340694288FBA8CE0870BAFE2E0@OSAPR01MB5073.jpnprd01.prod.outlook.com>
References: <20191202110538.a6ff2cd84f488f42eab621eb@sraoss.co.jp>
	<20191220140232.57b43c4f6296f793d42946b2@sraoss.co.jp>
	<OSAPR01MB5073D680E1FB8AA4DA8EAAF7FE2D0@OSAPR01MB5073.jpnprd01.prod.outlook.com>
 <20191223.105050.1450299519125940390.t-ishii@sraoss.co.jp>
In-Reply-To: <20191223.105050.1450299519125940390.t-ishii@sraoss.co.jp>
Accept-Language: ja-JP, en-US
Content-Language: ja-JP
Content-Type: text/plain; charset="iso-2022-jp"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 237fc810-cbed-4523-3245-08d7877bc9ba
X-MS-Exchange-CrossTenant-originalarrivaltime: 23 Dec 2019 07:43:23.0675
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: a19f121d-81e1-4858-a9d8-736e267fd4c7
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 jT7KHd+lsblsjy/qTavbhf8CcH3Jwp1UOJ7pJRFZIFQkyf/ghxG7adJS+4rQ1t3f81XVlCUgXDGDHglm7UKEW8Nw0g6vw0bkoYRfn6MoA2A=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: OSAPR01MB4772
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Precedence: bulk

From: Tatsuo Ishii <ishii@sraoss.co.jp>
> the target data?  In the discussion, someone referred to master data with=
 low
> update frequency, because the proposed IVM implementation adds triggers o=
n
> source tables, which limits the applicability to update-heavy tables.
>=20
> But if you want to get always up-to-data you need to pay the cost for
> REFRESH MATERIALIZED VIEW. IVM gives a choice here.

Thank you, that clarified to some extent.  What kind of data do you think o=
f as an example?

Materialized view reminds me of the use in a data warehouse.  Oracle handle=
s the top in its Database Data Warehousing Guide, and Microsoft has just st=
arted to offer the materialized view feature in its Azure Synapse Analytics=
 (formerly SQL Data Warehouse).  AWS also has previewed Redshift's material=
ized view feature in re:Invent 2019.  Are you targeting the data warehouse =
(analytics) workload?

IIUC, to put (over) simply, the data warehouse has two kind of tables:

* Facts (transaction data): e.g. sales, user activity
Large amount.  INSERT only on a regular basis (ETL/ELT) or continuously (st=
reaming)

* Dimensions (master/reference data): e.g. product, customer, time, country
Small amount.  Infrequently INSERTed or UPDATEd.


The proposed trigger-based approach does not seem to be suitable for the fa=
cts, because the trigger overhead imposed on data loading may offset or exc=
eed the time saved by incrementally refreshing the materialized views.

Then, does the proposed feature fit the dimension tables?  If the materiali=
zed view is only based on the dimension data, then the full REFRESH of the =
materialized view wouldn't take so long.  The typical materialized view sho=
uld join the fact and dimension tables.  Then, the fact table will have to =
have the triggers, causing the data loading slowdown.

I'm saying this because I'm concerned about the trigger based overhead.  As=
 you know, Oracle uses materialized view logs to save changes and increment=
ally apply them later to the materialized views (REFRESH ON STATEMENT mater=
ialized views doesn't require the materialized view log, so it might use tr=
iggers.)  Does any commercial grade database implement materialized view us=
ing triggers?  I couldn't find relevant information regarding Azure Synapse=
 and Redshift.

If our only handy option is a trigger, can we minimize the overhead by doin=
g the view maintenance at transaction commit?


Regards
Takayuki Tsunakawa