Postgres Pro
Downloads
Home   >   mailing lists

Thread: How is autovacuum affected by a change in year.

How is autovacuum affected by a change in year.

From
Hanns Hartman
Date:
Hi,

I am running postgres 8.3.17 on a RedHat linux derivative using a mips64 architecture.

I've recently noticed some odd autovacuum behavior.

Recently we've had some systems deployed with the system clock set to the year 2016.  Postgres was installed with that date and things were fine until a user noticed the incorrect date.  They reset the system time back to 2015 and then we started seeing weird behavior where the autovacuum proccess does not seem to be maintaining the disk space.

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.

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

Re: How is autovacuum affected by a change in year.

From
Tom Lane
Date:
Hanns Hartman <hwhartman@gmail.com> writes:
> I am running postgres 8.3.17 on a RedHat linux derivative using a mips64
> architecture.

You realize of course that 8.3 is long out of support ...

> I've recently noticed some odd autovacuum behavior.

> Recently we've had some systems deployed with the system clock set to the
> year 2016.  Postgres was installed with that date and things were fine
> until a user noticed the incorrect date.  They reset the system time back
> to 2015 and then we started seeing weird behavior where the autovacuum
> proccess does not seem to be maintaining the disk space.

I would not be surprised if the autovacuum launcher thinks that it doesn't
need to touch that database again until 2016.  A quick DB restart should
fix it.

            regards, tom lane

Re: How is autovacuum affected by a change in year.

From
Hanns Hartman
Date:
Hi Tom,

Yep I know its out of date but thank you for replying anyways :)

I retried my test with the autovacuum logs turned on and confirmed that a postgresql restart fix the issue.

thanks your your help!
-HH

On Thu, Feb 26, 2015 at 11:02 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hanns Hartman <hwhartman@gmail.com> writes:
> I am running postgres 8.3.17 on a RedHat linux derivative using a mips64
> architecture.

You realize of course that 8.3 is long out of support ...

> I've recently noticed some odd autovacuum behavior.

> Recently we've had some systems deployed with the system clock set to the
> year 2016.  Postgres was installed with that date and things were fine
> until a user noticed the incorrect date.  They reset the system time back
> to 2015 and then we started seeing weird behavior where the autovacuum
> proccess does not seem to be maintaining the disk space.

I would not be surprised if the autovacuum launcher thinks that it doesn't
need to touch that database again until 2016.  A quick DB restart should
fix it.

                        regards, tom lane