Why Vacuum ?
In PostgreSQL, when rows are updated or deleted from a table, dead rows are created and left behind, vacuum daemon processes will clean those dead tuples ( rows) periodically based on parameter setting for vacuum process. Generally the following four reasons explain why vacuum is needed:
- To recover or reuse disk space occupied by updated or deleted rows.
- To update data statistics used by the PostgreSQL query planner.
- To update the visibility map, which speeds up index-only scans.
- To protect against loss of very old data due to transaction ID wraparound or multixact ID wraparound.
Tables’ Dead Tuples Not Removed
The following query result shows the dead tuples are not removed by auto vacuuming for some reasons. But they should have been removed from tables.
schemaname | relname | n_live_tup | n_dead_tup | overthread | now | last_autovacuum | autovacuum_count
------------+---------------------------+------------+------------+------------------+-------------------------------+-------------------------------+------------------
pg_catalog | pg_statistic | 3298 | 48500 | 127.69878883623 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:34:11.607386+00 | 1797116
pg_catalog | pg_attribute | 1116 | 6643 | 41.1076732673267 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:34:28.653988+00 | 1589637
cdrdb | users | 27316 | 87554 | 31.4761288467069 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:35:35.891844+00 | 1717941
cdrdb | products | 645 | 1689 | 14.7510917030568 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:35:32.192822+00 | 1596895
cdrdb | price | 154492 | 168499 | 10.871464333643 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:35:05.049206+00 | 1655072
cdrdb | contract_pdf | 157701 | 165106 | 10.4364700602398 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:35:27.945506+00 | 1656837
...
..
.
(27 rows)
Subscribe to get access
Read more of this content when you subscribe today.
Make DBA Life Easy 