On Tue, 2024-06-18 at 15:28 -0700, Wells Oliver wrote: > Apologies for the daft question, but I am surprised to see materialized views > show up in pg_stat_user_tables with lots of dead tuples. These are rematerialized > nightly and, I thought, this had the effect of replacing/recreating them anew. > Can someone shed some light on this?
It makes a difference if you use REFRESH MATERIALIZED VIEW or REFRESH MATERIALIZED VIEW CONCURRENTLY.
The first statement will just discard the materialized table and create it anew, and you will never see a dead tuple. The second statement executes the query and updates the materialized table, which can lead to dead tuples just like a normal UPDATE or DELETE.