CODEWITHSUNDEEP
databasepostgresqlpostgresql-9.4
Larry
Larry

Reputation: 35

PostgreSQL Materialized View Refresh "stacking"

Version: 9.4.10 Do requests to refresh a materialized view stack?

Ex: I have a materialized view which refreshes concurrently based off of an INSERT/UPDATE/DELETE on my main table. If this trigger is called by multiple users frequently, will the materialized view stack these refreshes in a queue or will it cancel current refresh execution and run the most recent trigger?

If it stacks, does anyone have suggestions as to how I could resolve this issue. I have a very large data set and I expect the refresh of the view to take awhile.

Upvotes: 1

Views: 596

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51509

I don't see any reason wy it would cancel current refresh in favour of next one. With your described model I think you will have the avalanch with waiting sessions. I would suggest you abandon idea to refresh the big mview with trigger on data change. If you want to control some way the "several sources of truth" risk and thus don't want to duplicate INSERT/UPDATE/DELETE to other table so it would hold some logical copy of original table - you can create a table from materialyzed view and populate recodrs there with your triggers and hourly or so just refresh the mview to sync up possible dupes or gaps. It will still stake everything for period of refresh, but at least it won't freeze everything on every change...

Upvotes: 1

Related Questions