From everything I have read about VACUUM FULL, I would expect it to lock the database I'm running it on, but it renders every database in the cluster inaccessible. Is there perhaps something we might have wrong in our configuration? I do have autovacuum on, but we have a few unwieldy databases I'd like to clean up thoroughly. This is PostgreSQL v10. Thanks for any insights.
-
Why don't you run VACUUM FULL on only those tables that need it? – a_horse_with_no_name Nov 16 '19 at 7:53
-
Please describe what you are seeing. Do new connections get errors? Block indefinitely? Just very slow? If you have existing connections you can use, what do you see in pg_locks? – jjanes Nov 16 '19 at 13:54
-
@a_horse_with_no_name - I think that is what I'll end up doing. – Andy Norris Nov 18 '19 at 17:44
-
@jjanes - Nobody else can log in, and any current sessions, all queries just hang. – Andy Norris Nov 18 '19 at 17:45
I'm considering below is the reason from PostgreSQL v10 documentation:(https://www.postgresql.org/docs/10/sql-vacuum.html)
The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans. VACUUM FULL will usually shrink the table more than a plain VACUUM would.
VACUUM causes a substantial increase in I/O traffic, which might cause poor performance for other active sessions. Therefore, it is sometimes advisable to use the cost-based vacuum delay feature. See Section 19.4.4 for details.
https://www.postgresql.org/docs/10/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST
In your case, it might have happened because all databases in the cluster shares common resource, if it's on the same server.
answered Nov 16 '19 at 10:07
Some tables are shared between all databases of a cluster: pg_database, pg_tablespace, pg_authid and pg_shdepend (these are the ones I know by heart; I am talking about the tables in the pg_global tablespace).
Running VACUUM (FULL) will also treat these tables, so other databases are affected.
