How to find tables processed by VACCUM(FULL, ANALYZE) but not VERBOSE

user_1734032 0
Viewed 44

So I have cancelled a global VACUUM FULL out of necessity, there will be tables that were not processed and can now be individually targeted.

Problem. VACUUM(FULL, ANALYZE) does not update last_vacuum, a known issue for a decade?

How can I identify the tables that were completed, so that I might by extension identify the complement of those? I cannot find a duplicate of this, but I find it hard to believe this is the first time the question has been asked. I am aware that this could have been extracted from verbose output.

vacuum postgresql
1 Answers

VACUUM (FULL) isn't really VACUUM, strange as that seems. Rather, it is CLUSTER without a special ordering. The reason for this oddity is partly that the implementation of VACUUM (FULL) was radically changed in version 9.0. Since it is so different from normal VACUUM, it is not tracked in pg_stat_user_tables.last_vacuum, and its progress is tracked in pg_stat_progress_cluster rather than in pg_stat_progress_vacuum.

Apart from pg_stat_user_tables.last_analyze, which you can use since you ran VACUUM (FULL, ANALYZE), you could look at the creation timestamp of the data files. That would work, since VACUUM (FULL) creates a new copy of the table.

On Windows, you can use the following query for that:

SELECT t.oid::regclass,
       s.creation
FROM pg_class AS t
   JOIN pg_database AS d ON d.datname = current_database()
   JOIN pg_tablespace AS ts
      ON CASE WHEN t.reltablespace = 0 THEN d.dattablespace
              ELSE t.reltablespace
         END = ts.oid
   CROSS JOIN LATERAL pg_stat_file(
                         CASE ts.spcname
                            WHEN 'pg_default' THEN 'base/' || d.oid
                            WHEN 'pg_global' THEN 'global'
                            ELSE 'pg_tblspc/' || ts.oid || '/' || d.oid
                         END
                         || '/' || pg_relation_filenode(t.oid::regclass)
                      ) AS s
WHERE t.relkind = 'r'
ORDER BY s.creation;

On other operating systems, pg_stat_file() returns NULL for creation, and you'd have to go look into the file system yourself.

user_64643080
Related