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.
CodePudding user response:
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.