Thread: Vacuum on the database versus individual tables.

Vacuum on the database versus individual tables.

From
"Hartman, Matthew"
Date:

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?

 

Thanks,


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

 

 

Re: Vacuum on the database versus individual tables.

From
Grzegorz Jaśkiewicz
Date:
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

Re: Vacuum on the database versus individual tables.

From
"Hartman, Matthew"
Date:
Windows XP, PostgreSQL 8.3.5, using pgAdmin III 1.8.4. It's the typical "Running vacuum on this table is recommended"
dialogbox. 


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


Re: Vacuum on the database versus individual tables.

From
Grzegorz Jaśkiewicz
Date:
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"
dialogbox. 

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

Re: Vacuum on the database versus individual tables.

From
Dave Page
Date:
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

Re: Vacuum on the database versus individual tables.

From
Merlin Moncure
Date:
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

Re: Vacuum on the database versus individual tables.

From
"Hartman, Matthew"
Date:
But it'll do so immediately after I run a full vacuum on the entire database? Nothing has changed. This is a
developmentbox. 

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


Re: Vacuum on the database versus individual tables.

From
Tom Lane
Date:
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

Re: Vacuum on the database versus individual tables.

From
"Hartman, Matthew"
Date:
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


Re: Vacuum on the database versus individual tables.

From
Scott Marlowe
Date:
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.