Question 235699 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to find tables that require VACUUM FULL?

Before VACUUM FULL I want to know, which table really needs this operation. To check this I found this query:

SELECT tuple_percent, dead_tuple_count, dead_tuple_percent, free_space, free_percent FROM pgstattuple('public.book_formula_operation_log');

Based on dead_tuple_percent I can decide – run VACUUM FULL or it’s not necessary.

But this script shows only one table. How can I give this information from all tables from database sorted by dead_tuple_percent?

Tags:

2

Answers


  1. 0

    You can use pg_stat_user_tables instead:

    select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze 
from pg_stat_user_tables
order by n_dead_tup desc;
    Login or Signup to reply.
  2. 0

    You can use a lateral join to the function call, from a list of tables.

    pg_stat_user_tables provides a handy list of tables, but you might want something else.

    select schemaname, relname, pgstattuple.* from pg_stat_user_tables, pgstattuple(relid) order by dead_tuple_percent desc;

    But, it is doubtful this is useful. For one thing, dead_tuple_percent is adequately dealt with by a regular (non-FULL) vacuum, so sorting on it with the intention of driving VACUUM FULL doesn’t make sense. It might more sense to use free_space or free_percent. But even that is questionable. Why squeeze out the freespace, if you are just going to create it again soon anyway? There is no point in doing that unless the bloat is actually a problem, and until you think you fixed the problem which created the bloat in the first place, which there is no automated tool for.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search