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.

How to Restrict Parallel Server Processes Running on Specific Oracle RAC Nodes

For this SQL, I want all the parallel processes running on this node or those nodes only.

All Oracle DBA knows, by default, the parallel server processes spawned to execute a SQL statement can operate on any or all Oracle RAC nodes in the cluster.

For big indexes rebuilding , a client wants all the parallel server processes running on specific node(s).

There are two easy ways to achieve this without bouncing database required.

Subscribe to get access

Read more of this content when you subscribe today.