- 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
Categories
Recent Blog
- Do you really need a DBA?
- Running VACUUM FULL in Non-blocking Mode
- Connection Scaling
- PSQL Helper: Managing Connections and Simplifying Queries
- Vacuum Those MVs!
- Working With Repmgr: Using Other 3rd Party Tools for Setting up a Standby
- Working with Amazon Aurora PostgreSQL: dag, standby rebooted again!
- Working with Amazon Aurora PostgreSQL: what happened to the stats?
- How to set application_name for psql command line utility?
- Is there a limit on number of partitions handled by Postgres?