Hi,
I am running postgres 8.3.17 on a RedHat linux derivative using a mips64 architecture.The problematic table has 245 column 107 of which are of type text and the rest are int or bigint. The table usually has very few rows (50-100) which once inserted are static.
Almost all of the the table activity is update based. Every 60 seconds most columns of each row are updated. The text columns in particular are updated quite frequently.
Almost all of the the table activity is update based. Every 60 seconds most columns of each row are updated. The text columns in particular are updated quite frequently.
So this beings me to the issue. After the date change from 2016->2015, the toast table for this table appears to be growing unbounded . Running a VACUUM FULL of this table immediately frees up the disk space. The adding the verbose option to the vacuum command shows that most of the tuples in the toast table were marked as dead and were easily removed.
I've tried restarting postgres and the server but nothing seems to make the autovacuum kick in.
The only thing that fixes the autovacuum is setting the date back to 2016.
Is the autovacuum process affected by a change in time/date/year? (I noticed in the code that the method launcher_determine_sleep a call is made to GetCurrentTimestamp)
When the year is changed backwards does postgres have to be restarted? (sorry if this is a stupid question but a restart seemed to have no affect)
Could the system year postgres was installed being different from the year its being run in have an affect on the autovacuum?
My postgresql.conf file is using the default vacuum configuration.
I also I see no autovacuum related error messages in the db log file.
thanks
-Hanns