Skip site navigation (1) Skip section navigation (2)

Re: pg_autovacuum: short, wide tables

From: "Matthew T(dot) O'Connor" <matthew(at)tocr(dot)com>
To: mark reid <mail(at)markreid(dot)org>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: pg_autovacuum: short, wide tables
Date: 2005-07-07 20:23:04
Message-ID: 42CD8F28.7050600@tocr.com (view raw or flat)
Thread:
Lists: pgsql-bugs
mark reid wrote:

> I've been using pg_autovacuum for a while, and for the most part it's 
> been great.  There's one case in my system where it won't run on a 
> particular type of table, even though the table apparently needs it.
>
> I have a table called "properties" that has key->value pairs.  Usually 
> there are only a handful of rows, some of which are updated relatively 
> frequently compared to the number of rows (hundreds or thousands of 
> times per day).  The problem is that some of the rows have long 
> strings for their value (on the order of a few hundred kilobytes), so 
> if I understand correctly, the bulk of the data gets offloaded to a 
> toast table.
> What I believe is happening is that the main table doesn't meet the 
> minimum activity level for pg_autovacuum based on size / update 
> frequency, but the toast table would, though it isn't specifically 
> checked by pg_autovacuum.  The result is that the toast table grows 
> really big before triggering autovacuum (or until I manually vacuum 
> the "properties" table).  Not the end of the world, obviously, but 
> might be a "gotcha" for some people with similar situations.


I don't think the problem has to do with toast, or pg_autovacuum missing 
the fact that the toast table has been updated.  Rather I think the 
problem is that autovacuum believes that all updates are created equal.  
That is 1 update is 1 update even though a single update may effect 1 
page or thousands of pages.  This is where FSM data needs to be 
integrated into pg_autovacuum.  This isn't going to happen soon (not for 
8.1) but it is definatley planned. 

With the inclusion of autovacuum into the backend for 8.1, you will at 
least be able to set per table thresholds and set a more aggressive 
threshold for this table.

Matt



In response to

Responses

pgsql-bugs by date

Next:From: Jim C. NasbyDate: 2005-07-07 20:29:07
Subject: FreeBSD 4.x pthreads issues
Previous:From: Alvaro HerreraDate: 2005-07-07 17:43:57
Subject: Re: BUG #1756: PQexec eats huge amounts of memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group