How to Check and Fix Dead Tuples Not Removed By Vacuum From PostgreSQL Tables

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:

  1. To recover or reuse disk space occupied by updated or deleted rows.
  2. To update data statistics used by the PostgreSQL query planner.
  3. To update the visibility map, which speeds up index-only scans.
  4. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.