On Fri, 4 Mar 2022 at 14:41, Gilles Darold <gilles@migops.com> wrote:
Hi,
When we want to vacuum and/or analyze all tables in a dedicated schema, let's say pg_catalog for example, there is no easy way to do that. The VACUUM command doesn't allow it so we have to use \gexec or a SQL script to do that. We have an external command vacuumdb that could be used to simplify this task. For example the following command can be used to clean all tables stored in the pg_catalog schema:
vacuumdb --schema pg_catalog -d foo
+1
This gives much better flexibility to users.
The attached patch implements that. Option -n | --schema can be used multiple time and can not be used together with options -a or -t.
Common use cases are an application that creates lot of temporary objects then drop them which can bloat a lot the catalog or which have heavy work in some schemas only. Of course the good practice is to find the bloated tables and execute VACUUM on each table but if most of the tables in the schema are regularly bloated the use of the vacuumdb --schema script can save time.
I do not propose to extend the VACUUM and ANALYZE commands because their current syntax doesn't allow me to see an easy way to do that and also because I'm not really in favor of such change. But if there is interest in improving these commands I will be pleased to do that, with the syntax suggested.
