Vacuum on the database versus individual tables.

On Thu, Jun 25, 2009 at 3:40 PM, Hartman,
Matthew<Matthew.Hartman@krcc.on.ca> wrote:

On occasion I’ll perform a full vacuum on a database but will still receive
a suggestion to vacuum an individual table immediately after. Does the full
database vacuum not handle each individual table?

What's the exact message, what OS, what pg version, how do you call vacuum.

--
GJ

Windows XP, PostgreSQL 8.3.5, using pgAdmin III 1.8.4. It's the typical "Running vacuum on this table is recommended" dialog box.

Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549-6666 x4294

-----Original Message-----
From: Grzegorz Jaśkiewicz [mailto:gryzman@gmail.com]
Sent: Thursday, June 25, 2009 11:25 AM
To: Hartman, Matthew
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Vacuum on the database versus individual tables.

On Thu, Jun 25, 2009 at 3:40 PM, Hartman,
Matthew<Matthew.Hartman@krcc.on.ca> wrote:

On occasion I'll perform a full vacuum on a database but will still receive
a suggestion to vacuum an individual table immediately after. Does the full
database vacuum not handle each individual table?

What's the exact message, what OS, what pg version, how do you call vacuum.

--
GJ

2009/6/25 Hartman, Matthew <Matthew.Hartman@krcc.on.ca>:

Windows XP, PostgreSQL 8.3.5, using pgAdmin III 1.8.4. It's the typical "Running vacuum on this table is recommended" dialog box.

Well, it really has nothing to do with postgresql it self. Either it
is a bug or property of PgAdmin, but I don't know.
Either someone who knows more about pgadmin is going to respond here,
or you have to ask on pgadmin list.

--
GJ

2009/6/25 Grzegorz Jaśkiewicz <gryzman@gmail.com>:

2009/6/25 Hartman, Matthew <Matthew.Hartman@krcc.on.ca>:

Windows XP, PostgreSQL 8.3.5, using pgAdmin III 1.8.4. It's the typical "Running vacuum on this table is recommended" dialog box.

Well, it really has nothing to do with postgresql it self. Either it
is a bug or property of PgAdmin, but I don't know.
Either someone who knows more about pgadmin is going to respond here,
or you have to ask on pgadmin list.

pgAdmin will advise vacuuming a table if there is a significant
discrepancy between the number of rows in the table and the value in
pg_class.reltuples. i forget the exact algorithm off-hand, but it
takes the size of the table into account, and is looking for a %age
difference between the value, not a set number of rows.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

But it'll do so immediately after I run a full vacuum on the entire database? Nothing has changed. This is a development box.

You know, I bet it doesn't refresh the view of the database after having run the maintenance script..

Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549-6666 x4294

-----Original Message-----
From: Dave Page [mailto:dpage@pgadmin.org]
Sent: Thursday, June 25, 2009 12:00 PM
To: Grzegorz Jaśkiewicz
Cc: Hartman, Matthew; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Vacuum on the database versus individual tables.

2009/6/25 Grzegorz Jaśkiewicz <gryzman@gmail.com>:

2009/6/25 Hartman, Matthew <Matthew.Hartman@krcc.on.ca>:

Windows XP, PostgreSQL 8.3.5, using pgAdmin III 1.8.4. It's the typical "Running vacuum on this table is recommended" dialog box.

Well, it really has nothing to do with postgresql it self. Either it
is a bug or property of PgAdmin, but I don't know.
Either someone who knows more about pgadmin is going to respond here,
or you have to ask on pgadmin list.

pgAdmin will advise vacuuming a table if there is a significant
discrepancy between the number of rows in the table and the value in
pg_class.reltuples. i forget the exact algorithm off-hand, but it
takes the size of the table into account, and is looking for a %age
difference between the value, not a set number of rows.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

On Thu, Jun 25, 2009 at 10:40 AM, Hartman,
Matthew<Matthew.Hartman@krcc.on.ca> wrote:

Good morning.

On occasion I’ll perform a full vacuum on a database but will still receive
a suggestion to vacuum an individual table immediately after. Does the full
database vacuum not handle each individual table?

As of 8.3, for the most part you can trust autovacuum to do its thing
if it's enabled...there are exceptions to this but I'm curious why you
are doing 'vacuum full'.

merlin

I do have autovacuum on (as of yesterday). This was discovered when I
ran vacuum on a whim.

Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549-6666 x4294

-----Original Message-----
From: Merlin Moncure [mailto:mmoncure@gmail.com]
Sent: Thursday, June 25, 2009 12:10 PM
To: Hartman, Matthew
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Vacuum on the database versus individual tables.

On Thu, Jun 25, 2009 at 10:40 AM, Hartman,
Matthew<Matthew.Hartman@krcc.on.ca> wrote:

Good morning.

On occasion I'll perform a full vacuum on a database but will still

receive

a suggestion to vacuum an individual table immediately after. Does the

full

database vacuum not handle each individual table?

As of 8.3, for the most part you can trust autovacuum to do its thing
if it's enabled...there are exceptions to this but I'm curious why you
are doing 'vacuum full'.

merlin

Dave Page <dpage@pgadmin.org> writes:

pgAdmin will advise vacuuming a table if there is a significant
discrepancy between the number of rows in the table and the value in
pg_class.reltuples. i forget the exact algorithm off-hand, but it
takes the size of the table into account, and is looking for a %age
difference between the value, not a set number of rows.

What actually counts is the tuple density (ie, the reltuples/relpages
ratio). I would hope that it's checking whether that is off from
reality, not whether the absolute value is correct. Otherwise it's
going to be recommending useless vacuums.

(Actually, I'm not sure such a functionality is needed at all anymore
unless the user has disabled autovacuum...)

regards, tom lane

On Thu, Jun 25, 2009 at 8:40 AM, Hartman,
Matthew<Matthew.Hartman@krcc.on.ca> wrote:

Good morning.

On occasion I’ll perform a full vacuum on a database but will still receive
a suggestion to vacuum an individual table immediately after. Does the full
database vacuum not handle each individual table?

Wait, is this a regular vacuum against the whole database, or a vacuum
FULL against the whole database? Or a vacuum full on an individual
table. Vacuum full is generally not recommended for regular
maintenance, but only for getting out of bloat problems that regular
vacuum can no longer handle. It is generally best followed by a
reindex to clean up the indexes.