title: performance of refreshing postgres materialized view
body: <p>I am exploring materialized views to create de-normalized view to avoid joining multiple tables for read performance. APIs will read from the materialized views to provide data to clients.</p>
  <p>I am using amazon aurora postgres (version 11).</p>
  <p>I am using a unique index on the materialized view (MV) so that I can use the “refresh concurrently” option.</p>
  <p>What I am noticing though is that when only a fraction of the rows get updated in one of the source tables and I try to refresh the view, it's pretty slow. In fact slower than populating the view for the first time. e.g.: to populate MV first time takes ~30 mins, refresh is taking more than an hour. less than 1% of rows have been updated. The main three tables involved in generating the MV have ~18 million, 27 million &amp; 40 million rows.</p>
  <p>The timeliness of the materialized view refresh is important so that data is not stale for too long.</p>
  <p>I could go with custom tables to store the denormalized data instead of materialized views but would have to implement logic to refresh data. So planning to avoid that if possible.</p>
  <p>Is there anything that can be done to speed up the refresh process of the materialized views?</p>
  <p>Please let me know if you need more details.</p>
  <p>thanks
  Kiran</p>