Thread: Vacuum on the database versus individual tables.
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
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" 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
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
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
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
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
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
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.