Contact Info

Folow us on social

Vacuum Those MVs!

  • Michael Vitale
  • 15th June 2021
  • Database, performance, postgres, postgresql

On a particular project, Materialized Views (MVs) were being used quite extensively in the Apps that were being migrated from Oracle to PostgreSQL.  One thing I noticed was missing was VACUUM ANLYZE on these MVs after being created or refreshed.  It is extremely important to integrate VACUUM ANALYZE commands into this process.  Here is the reasoning behind it.

1. ANALYZE – updates the statistics for the MV so that the PG Optimizer can make the best decision possible for access path determination for SQL statements running against these MVs.

2. VACUUM – creates/updates the PG Visibility Map (VM) so that index-only scans can be used.  Furthermore, if MV refreshes are done using the CONCURRENTLY option, which avoids locking the table in exclusive mode, then PG uses DML statements to update the original MV, thus causing bloat.  Bloat can only be alleviated by a normal vacuum operation which makes dead rows visible again for reuse.

Michael Vitale, PG DBA

Leave a comment

Your email address will not be published. Required fields are marked *