Autovacuum Problem

From: Kein Name <muelladdi(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Autovacuum Problem
Date: 2018-04-03 06:36:45
Message-ID: CA+XgNjKrPgQGq=DY+ixXMuCqn7dN01OXak=+dHnA7Y=+zmqbEg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Is there any way a bloody newbie can debug this behaviour?
Any help is appreciated!

Thanks
Stefan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Achilleas Mantzios 2018-04-03 06:49:04 Re: Autovacuum Problem
Previous Message Jakub Janeček 2018-04-03 06:10:45 Re: PostgreSQL Cascade streaming replication problem