How PostgreSQL Vacuum works?

How does PostgreSQL Vacuum work?


In PostgreSQL, the vacuum process is responsible for reclaiming space from deleted or updated rows in tables. The vacuum process works by scanning the table and removing any rows that are no longer needed. Here is an overview of how the vacuum process works in PostgreSQL:
  1. When a row is deleted or updated in a table, the old version of the row is not immediately removed from the table. Instead, it is marked as “dead” and left in the table until the vacuum process can reclaim the space.
  2. The vacuum process scans the table, looking for dead rows. When it finds a dead row, it removes it from the table and frees up the space that it was using.
  3. The vacuum process also updates any indexes on the table to reflect the changes that have been made.
  4. The vacuum process can be run manually by using the VACUUM command, or it can be run automatically by the “autovacuum” process. The autovacuum process is a background process that runs periodically to reclaim space from dead rows.
  5. The vacuum process also analyzes the table and updates the statistics that the query planner uses to optimize queries. This process is called ANALYZE.
  6. PostgreSQL supports two types of vacuuming: full vacuum and lazy vacuum. Full vacuum reclaims all the space that can be reclaimed and updates all the statistics. Lazy vacuum only reclaims the space that is immediately needed by the system and does not update the statistics.
  7. PostgreSQL also support parallel vacuum that allows to run multiple vacuum processes in parallel, which can significantly improve the performance of the vacuum operation.
  8. PostgreSQL also has a feature called “Freeze map” which allows to track the age of individual pages, and thus to decide which pages need vacuuming and which do not.
  9. It’s important to monitor the vacuum process and ensure that it is running effectively to prevent table bloat and to improve the performance of the database.

Vacuuming in PostgreSQL is important for several reasons:

  1. Space Reclamation: When rows are deleted or updated in a table, the old versions of the rows are not immediately removed from the table. Instead, they are marked as “dead” and left in the table until the vacuum process can reclaim the space. The vacuum process scans the table and removes dead rows, freeing up space that can be used by other data.
  2. Index Maintenance: The vacuum process also updates any indexes on the table to reflect the changes that have been made. This helps to keep the indexes accurate and efficient, improving the performance of queries.
  3. Query Optimization: The vacuum process also analyzes the table and updates the statistics that the query planner uses to optimize queries. This helps the query planner to make better decisions about how to execute queries, improving the performance of the database.
  4. Preventing table bloat: Vacuuming helps to prevent table bloat, which occurs when the size of a table becomes much larger than the amount of data it contains. Table bloat can negatively impact the performance of the database and make it more difficult to maintain.
  5. Preventing disk fragmentation: Vacuuming also helps to prevent disk fragmentation, which occurs when data is written and deleted on disk. Disk fragmentation can negatively impact the performance of the database by slowing down disk accesses.
  6. Compliance: for some regulated industries, it is a requirement to be able to demonstrate that data that is no longer needed has been securely erased. Properly vacuuming tables in PostgreSQL can help meet.

How to monitor PostgreSQL Vacuuming?

There are several ways to monitor the vacuuming process in PostgreSQL:
  1. pg_stat_all_tables: This view contains statistics about all tables in the database, including the number of dead rows, the last time the table was vacuumed, and the number of times the table has been vacuumed. You can use this view to check the status of vacuuming for individual tables.
  2. pg_stat_progress_vacuum: This view contains statistics about the current vacuuming process, including the number of dead rows that have been removed, the number of pages that have been scanned, and the number of pages that have been vacuumed.
  3. pg_stat_bgwriter: This view contains statistics about the background writer process, which is responsible for writing dirty pages to disk. You can use this view to check the status of vacuuming for the entire database.
  4. pg_stat_user_tables: This view contains statistics about user-defined tables, including the number of live and dead rows, the last time the table was vacuumed, and the number of times the table has been vacuumed.
  5. pg_statio_user_tables: This view contains statistics about I/O performance for user-defined tables, including the number of disk reads and writes, and the number of bytes read and written.
  6. Logs: PostgreSQL logs the vacuuming process, so you can monitor it by checking the logs.
  7. Monitoring tools: There are various monitoring tools available that can be used to monitor PostgreSQL, such as pgAdmin, pgBadger, Zabbix, Grafana, etc.
  8. Using scripts: you can write scripts that will extract the monitoring data from the views and store them in a format that can be easily graphed and analyzed.
It’s important to monitor the vacuuming process in order to ensure that it is running effectively and to identify any potential issues that might be impacting the performance of the database. Additionally, monitoring the vacuuming process is also important to ensure that the database is able to reclaim space efficiently and keep the size of the database under control.
About Shiv Iyer 485 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.