Hello List,
I inherited a machine with an Postgres Database, which I am fighting with right now.
It seems as if the Database is growing bigger and bigger over time.
Once in a while I have to run a VACUUM FULL statement on a few tables, which then releases a lot of space to the OS.
By reading the documentation, I found out that there should be a Autovacuum job running which is supposed to do this automatically for me.
I can see it running here and there, first ANALYZING and the VACUUM:
SELECT pid, query FROM pg_stat_activity WHERE query LIKE 'autovacuum: %';
pid | query
-------+-------------------------------------------------
15456 | autovacuum: VACUUM table1
16782 | autovacuum: VACUUM table2
(2 rows)
And it seems to complete as well:
select relname,last_vacuum, last_autovacuum, last_analyze, vacuum_count, autovacuum_count,last_autoanalyze from pg_stat_user_tables where relname like 'event%' order by relname ASC;
relname | last_vacuum | last_autovacuum | last_analyze | vacuum_count | autovacuum_count | last_autoanalyze
-------------------------+-------------+-------------------------------+--------------+--------------+------------------+-------------------------------
table1 | | 2018-04-03 02:11:41.983786+02 | | 0 | 89 | 2018-04-03 01:42:41.12331+02
table2 | | 2018-04-03 07:58:19.478713+02 | | 0 | 1696 | 2018-04-03 07:59:01.567328+02
However no space seems to be freed to the system.