pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuum Question
17+ messages / 12 participants
[nested] [flat]
* Autovacuum Question
@ 2006-05-16 17:27 Chris Hoover <revoohc@gmail.com>
2006-05-16 17:27 ` Re: Autovacuum Question Ian Westmacott <ianw@intellivid.com>
0 siblings, 1 reply; 17+ messages in thread
From: Chris Hoover @ 2006-05-16 17:27 UTC (permalink / raw)
To: pgsql-admin
PostgreSQL 8.1.3
Question on autovacuum.
autovacuum_naptime (integer)
Specifies the delay between activity rounds for the autovacuum subprocess.
In each round the subprocess examines one database and issues VACUUM and
ANALYZE commands as needed for tables in that database. The delay is
measured in seconds, and the default is 60. This option can be set at server
start or in the postgresql.conf file.
Does this mean that each run of autovacuum will vacuum/analyze all tables
that need work in the database that autovacuum is looking at?
The reason I'm asking is that I have ~172 non system tables in each
database, so if autovacuum only does one table per run, I don't think it
will ever get caught up (since there are almost 35k tables in total).
Also, with over 200 databases, does this mean that a database will only get
checked once about every 3.5 hours, or does autovacuum run on the database
that needs the vacuuming the most?
Thanks,
Chris
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Autovacuum Question
2006-05-16 17:27 Autovacuum Question Chris Hoover <revoohc@gmail.com>
@ 2006-05-16 17:27 ` Ian Westmacott <ianw@intellivid.com>
0 siblings, 0 replies; 17+ messages in thread
From: Ian Westmacott @ 2006-05-16 17:27 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; pgsql-admin
I just happened to be reading this page from the 8.1 docs:
"The autovacuum daemon, when enabled, runs every autovacuum_naptime seconds
and determines which database to process. Any database which is close to
transaction ID wraparound is immediately processed. In this case, autovacuum
issues a database-wide VACUUM call, or VACUUM FREEZE if it's a template
database, and then terminates. If no database fulfills this criterion, the
one that was least recently processed by autovacuum is chosen. In this case
each table in the selected database is checked, and individual VACUUM or
ANALYZE commands are issued as needed."
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Chris Hoover
Sent: Tuesday, May 16, 2006 1:27 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Autovacuum Question
PostgreSQL 8.1.3
Question on autovacuum.
autovacuum_naptime (integer)
Specifies the delay between activity rounds for the autovacuum
subprocess. In each round the subprocess examines one database and issues
VACUUM and ANALYZE commands as needed for tables in that database. The delay
is measured in seconds, and the default is 60. This option can be set at
server start or in the postgresql.conf file.
Does this mean that each run of autovacuum will vacuum/analyze all tables
that need work in the database that autovacuum is looking at?
The reason I'm asking is that I have ~172 non system tables in each
database, so if autovacuum only does one table per run, I don't think it
will ever get caught up (since there are almost 35k tables in total).
Also, with over 200 databases, does this mean that a database will only
get checked once about every 3.5 hours, or does autovacuum run on the
database that needs the vacuuming the most?
Thanks,
Chris
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Autovacuum Question
@ 2006-05-16 17:30 Mark Liberman <mliberman@mixedsignals.com>
2006-05-16 18:31 ` Re: Autovacuum Question Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 17+ messages in thread
From: Mark Liberman @ 2006-05-16 17:30 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; pgsql-admin
>Does this mean that each run of autovacuum will vacuum/analyze all tables
>that need work in the database that autovacuum is looking at?
Yes, it will do all tables that satisfy the criteria.
>Also, with over 200 databases, does this mean that a database will only get
>checked once about every 3.5 hours, or does autovacuum run on the database
>that needs the vacuuming the most?
If, you have it set to 60 seconds, then yes. It hits one db each minute. Actually, to be more clear, it sleeps 1 minute between each check. So, if, on average, it takes 60 seconds to perform the necessary vacuum/analyzes, then it there will be, on average, 2 minutes between each db, or 400 minutes to get through all 200 dbs.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Autovacuum Question
2006-05-16 17:30 Re: Autovacuum Question Mark Liberman <mliberman@mixedsignals.com>
@ 2006-05-16 18:31 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 17+ messages in thread
From: Jim C. Nasby @ 2006-05-16 18:31 UTC (permalink / raw)
To: Mark Liberman <mliberman@mixedsignals.com>; +Cc: Chris Hoover <revoohc@gmail.com>; pgsql-admin
On Tue, May 16, 2006 at 10:30:26AM -0700, Mark Liberman wrote:
> >Does this mean that each run of autovacuum will vacuum/analyze all tables
> >that need work in the database that autovacuum is looking at?
>
> Yes, it will do all tables that satisfy the criteria.
>
> >Also, with over 200 databases, does this mean that a database will only get
> >checked once about every 3.5 hours, or does autovacuum run on the database
> >that needs the vacuuming the most?
>
> If, you have it set to 60 seconds, then yes. It hits one db each minute. Actually, to be more clear, it sleeps 1 minute between each check. So, if, on average, it takes 60 seconds to perform the necessary vacuum/analyzes, then it there will be, on average, 2 minutes between each db, or 400 minutes to get through all 200 dbs.
The sleep time is so that the daemon doesn't just sit there hammering
the database, checking to see if it needs to vacuum anything. If you've
got 200 tables in one cluster, you should probably drop the sleep time
way down; probably closer to 10 or even 1.
Ideally, every time the daemon runs, there would be exactly one database
that needed vacuuming.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 17+ messages in thread
* autovacuum question
@ 2007-03-26 17:17 Laszlo Nagy <gandalf@designaproduct.biz>
2007-03-26 22:10 ` Re: autovacuum question Jim Nasby <decibel@decibel.org>
0 siblings, 1 reply; 17+ messages in thread
From: Laszlo Nagy @ 2007-03-26 17:17 UTC (permalink / raw)
To: pgsql-admin
Hello,
I had a problem in my computer system yesterday. Some data has
disappeared. I was looking for a program that might have deleted it
accidentally. Before I found anything, the data suddenly came back. I
thought it must be a transaction id wraparound problem.
The system is running on FreeBSD 6.2. Postgresql version is 8.1.8 and it
was installed from FreeBSD's standard ports tree. It also installed a
script that vacuums all databases daily. This script was turned on, and
I got emails every day, telling me that the databases were vacuumed.
However, when I opened some tables from within pgadmin3, I got the
message "pg_autovacuum recommened".
Summary: I'm vacuuming all databases daily, but probably I had a
transaction wraparound.
I thought I should enable autovacuum. I read the docs and did the following:
1. Changed postgresql.conf:
stats_start_collector = on
stats_row_level = on
autovacuum = on
2. Restarted the postgresql service.
Now, I do not see a "separate autovacuum daemon" running. What did I
wrong? Here are my tips:
a.) atovacuum daemon is part of the "postgres" process, so I won't see
it running in the process list (how can I check if it is working?)
b.) there is a separate program that I should start (where?)
c.) I missed some extra configuration (what should I do?)
d.) something else
Please help me out.
Thanks,
Laszlo
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum question
2007-03-26 17:17 autovacuum question Laszlo Nagy <gandalf@designaproduct.biz>
@ 2007-03-26 22:10 ` Jim Nasby <decibel@decibel.org>
2007-03-27 10:36 ` Re: autovacuum question Laszlo Nagy <gandalf@designaproduct.biz>
0 siblings, 1 reply; 17+ messages in thread
From: Jim Nasby @ 2007-03-26 22:10 UTC (permalink / raw)
To: Laszlo Nagy <gandalf@designaproduct.biz>; +Cc: pgsql-admin
Autovacuum doesn't launch a separate daemon; you'll only see an
autovac process when an autovac is actually working.
Also, 8.1 has code to prevent wraparound, so that's not it (see
backend/access/transam/varsup.c)
On Mar 27, 2007, at 5:17 AM, Laszlo Nagy wrote:
>
> Hello,
>
> I had a problem in my computer system yesterday. Some data has
> disappeared. I was looking for a program that might have deleted it
> accidentally. Before I found anything, the data suddenly came back.
> I thought it must be a transaction id wraparound problem.
>
> The system is running on FreeBSD 6.2. Postgresql version is 8.1.8
> and it was installed from FreeBSD's standard ports tree. It also
> installed a script that vacuums all databases daily. This script
> was turned on, and I got emails every day, telling me that the
> databases were vacuumed. However, when I opened some tables from
> within pgadmin3, I got the message "pg_autovacuum recommened".
>
> Summary: I'm vacuuming all databases daily, but probably I had a
> transaction wraparound.
>
> I thought I should enable autovacuum. I read the docs and did the
> following:
>
> 1. Changed postgresql.conf:
>
> stats_start_collector = on
> stats_row_level = on
> autovacuum = on
>
> 2. Restarted the postgresql service.
>
> Now, I do not see a "separate autovacuum daemon" running. What did
> I wrong? Here are my tips:
>
> a.) atovacuum daemon is part of the "postgres" process, so I won't
> see it running in the process list (how can I check if it is working?)
> b.) there is a separate program that I should start (where?)
> c.) I missed some extra configuration (what should I do?)
> d.) something else
>
> Please help me out.
> Thanks,
>
> Laszlo
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum question
2007-03-26 17:17 autovacuum question Laszlo Nagy <gandalf@designaproduct.biz>
2007-03-26 22:10 ` Re: autovacuum question Jim Nasby <decibel@decibel.org>
@ 2007-03-27 10:36 ` Laszlo Nagy <gandalf@designaproduct.biz>
2007-03-27 17:06 ` Re: autovacuum question Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 17+ messages in thread
From: Laszlo Nagy @ 2007-03-27 10:36 UTC (permalink / raw)
To: Jim Nasby <decibel@decibel.org>; pgsql-admin
Jim Nasby írta:
> Autovacuum doesn't launch a separate daemon; you'll only see an
> autovac process when an autovac is actually working.
Where should I see it? The output of "ps ax | grep auto" is empty. Does
it mean that auto vacuum is not running? How can I check if there is a
problem?
>
> Also, 8.1 has code to prevent wraparound, so that's not it (see
> backend/access/transam/varsup.c)
Oh, then do you have any idea what caused this? E.g. a postgres client
seeing an older version of the data? "Older" means that the postgres
client was started today and it saw the data from 2 days ago, then
suddenly it started to see the fresh data.
Thanks,
Laszlo
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum question
2007-03-26 17:17 autovacuum question Laszlo Nagy <gandalf@designaproduct.biz>
2007-03-26 22:10 ` Re: autovacuum question Jim Nasby <decibel@decibel.org>
2007-03-27 10:36 ` Re: autovacuum question Laszlo Nagy <gandalf@designaproduct.biz>
@ 2007-03-27 17:06 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 17+ messages in thread
From: Alvaro Herrera @ 2007-03-27 17:06 UTC (permalink / raw)
To: Laszlo Nagy <gandalf@designaproduct.biz>; +Cc: Jim Nasby <decibel@decibel.org>; pgsql-admin
Laszlo Nagy wrote:
> Jim Nasby írta:
> >Autovacuum doesn't launch a separate daemon; you'll only see an
> >autovac process when an autovac is actually working.
> Where should I see it? The output of "ps ax | grep auto" is empty. Does
> it mean that auto vacuum is not running? How can I check if there is a
> problem?
Do "show autovacuum", and it will tell you if autovac is running.
> >Also, 8.1 has code to prevent wraparound, so that's not it (see
> >backend/access/transam/varsup.c)
> Oh, then do you have any idea what caused this? E.g. a postgres client
> seeing an older version of the data? "Older" means that the postgres
> client was started today and it saw the data from 2 days ago, then
> suddenly it started to see the fresh data.
Maybe because they issued "commit" in the session where they had an idle
open transaction?
--
Alvaro Herrera http://www.PlanetPostgreSQL.org
"La libertad es como el dinero; el que no la sabe emplear la pierde" (Alvarez)
^ permalink raw reply [nested|flat] 17+ messages in thread
* autovacuum question
@ 2007-03-30 14:43 Carol Walter <walterc@indiana.edu>
2007-03-30 14:59 ` Re: autovacuum question Thomas Pundt <mlists@rp-online.de>
2007-03-30 15:04 ` Re: autovacuum question Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 17+ messages in thread
From: Carol Walter @ 2007-03-30 14:43 UTC (permalink / raw)
To: pgsql-admin
I ran the command "show autovacuum" and postgres responds with "ON."
My colleague says that I should be able to see it running if I run ps
-aef. He said that he doesn't trust postgres reporting on itself.
When I run the Unix ps command as above I don't see autovacuum. Does
anyone know if it's running or not? This is on a box running Solaris.
Carol
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum question
2007-03-30 14:43 autovacuum question Carol Walter <walterc@indiana.edu>
@ 2007-03-30 14:59 ` Thomas Pundt <mlists@rp-online.de>
2007-04-04 18:40 ` Re: autovacuum question Carol Walter <walterc@indiana.edu>
1 sibling, 1 reply; 17+ messages in thread
From: Thomas Pundt @ 2007-03-30 14:59 UTC (permalink / raw)
To: pgsql-admin@postgresql.org, Carol Walter <walterc@indiana.edu>
Hi,
On Friday 30 March 2007 16:43, Carol Walter wrote:
| I ran the command "show autovacuum" and postgres responds with "ON."
| My colleague says that I should be able to see it running if I run ps
| -aef. He said that he doesn't trust postgres reporting on itself.
| When I run the Unix ps command as above I don't see autovacuum. Does
| anyone know if it's running or not? This is on a box running Solaris.
you will only see it if it is currently processing your databases. Look
at the PostgreSQL log file, if it has entries from autovacuum.
Ciao,
Thomas
--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum question
2007-03-30 14:43 autovacuum question Carol Walter <walterc@indiana.edu>
2007-03-30 14:59 ` Re: autovacuum question Thomas Pundt <mlists@rp-online.de>
@ 2007-04-04 18:40 ` Carol Walter <walterc@indiana.edu>
2007-04-05 07:19 ` Re: autovacuum question Thomas Pundt <mlists@rp-online.de>
0 siblings, 1 reply; 17+ messages in thread
From: Carol Walter @ 2007-04-04 18:40 UTC (permalink / raw)
To: Thomas Pundt <thomas.pundt@rp-online.de>; +Cc: pgsql-admin
Hi, I'm looking in my log file with today's date. I'm using VIM and
have done a search for a pattern match on "autovac", "vacuum", and
"auto" VIM returned "string not found" for each search. Am I
looking for the wrong string, or is there another log file I should
be looking at?
Carol
On Mar 30, 2007, at 10:59 AM, Thomas Pundt wrote:
> Hi,
>
> On Friday 30 March 2007 16:43, Carol Walter wrote:
> | I ran the command "show autovacuum" and postgres responds with "ON."
> | My colleague says that I should be able to see it running if I
> run ps
> | -aef. He said that he doesn't trust postgres reporting on itself.
> | When I run the Unix ps command as above I don't see autovacuum.
> Does
> | anyone know if it's running or not? This is on a box running
> Solaris.
>
> you will only see it if it is currently processing your databases.
> Look
> at the PostgreSQL log file, if it has entries from autovacuum.
>
> Ciao,
> Thomas
>
> --
> Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/
> ----
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum question
2007-03-30 14:43 autovacuum question Carol Walter <walterc@indiana.edu>
2007-03-30 14:59 ` Re: autovacuum question Thomas Pundt <mlists@rp-online.de>
2007-04-04 18:40 ` Re: autovacuum question Carol Walter <walterc@indiana.edu>
@ 2007-04-05 07:19 ` Thomas Pundt <mlists@rp-online.de>
0 siblings, 0 replies; 17+ messages in thread
From: Thomas Pundt @ 2007-04-05 07:19 UTC (permalink / raw)
To: pgsql-admin@postgresql.org, Carol Walter <walterc@indiana.edu>
On Wednesday 04 April 2007 20:40, Carol Walter wrote:
| Hi, I'm looking in my log file with today's date. I'm using VIM and
| have done a search for a pattern match on "autovac", "vacuum", and
| "auto" VIM returned "string not found" for each search. Am I
| looking for the wrong string, or is there another log file I should
| be looking at?
No; mine has entries like
[::2007-04-05 09:04:10 CEST] LOG: autovacuum: processing database "postgres"
and the only settings different from default are
log_min_error_statement = warning
log_min_duration_statement = 1000
log_line_prefix = '[%u:%d:%t] '
autovacuum = on
autovacuum_naptime = 30
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 250
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
Ciao,
Thomas
--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum question
2007-03-30 14:43 autovacuum question Carol Walter <walterc@indiana.edu>
@ 2007-03-30 15:04 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 17+ messages in thread
From: Alvaro Herrera @ 2007-03-30 15:04 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
Carol Walter wrote:
> I ran the command "show autovacuum" and postgres responds with "ON."
> My colleague says that I should be able to see it running if I run ps
> -aef. He said that he doesn't trust postgres reporting on itself.
I wouldn't trust your colleague then ...
> When I run the Unix ps command as above I don't see autovacuum. Does
> anyone know if it's running or not? This is on a box running Solaris.
It is running. It will only appear on the ps output when it's actually
processing a database; after it's done, it'll go away. Check the logs;
if autovacuum is getting stuck on something (for example failing to
start for some databases), then an error will be logged.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 17+ messages in thread
* autovacuum question
@ 2009-01-30 16:31 Marc Mamin <M.Mamin@intershop.de>
2009-02-04 16:59 ` Re: autovacuum question Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 17+ messages in thread
From: Marc Mamin @ 2009-01-30 16:31 UTC (permalink / raw)
To: pgsql-admin
autovacuum question
Hello,
I'not using autovacuum as the largest part of my tables are filled with
COPY FROM, not modified afterward and dropped when obsolete.
Other tables are regularily vacuumed from an application driven job
that knows which tables were modified recently.
(It also vacuums the pg_catalog tables).
I've choosen this scenario about 2 years ago, as autovacuum didn't seem
to be very mature.
I manage a couple of DBs, currently up to 800GB, with data similar to
wharehouses, with mainly bulk operations, so that the transaction IDs
are growing slowly. A single DB can contain up to 30'000 tables and
45'000 indexes
I want to simplify this process as I'm migrating from 8.2 to 8.3.
I could use pg_autovacuum to track all tables that do not need being
vacuumed, but this would not simplify much as I have a lot of tables
created/dropped dynamically.
There are 2 features that would be helpfull in my case:
- define vacuum properties on table groups rather than on given tables:
For example, I don't want to vacuum daily tables which can be
identified with a regexp:
tablename ~ '\.*d_[0-9]{8}'
- automatically remove the pg_autovacuum entry when a table is dropped
(well it's not such complicated...)
And 2 questions:
Can I disable track_counts when I'm not using autovacuum, or is it used
for other purposes ?
Free Space Map: is it affected by dropped tables ? Or more generally,
must I care with it in a special way in my situation ?
many thanks,
Marc Mamin
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum question
2009-01-30 16:31 autovacuum question Marc Mamin <M.Mamin@intershop.de>
@ 2009-02-04 16:59 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-02-05 12:02 ` Re: autovacuum question Harald Fuchs <hari.fuchs@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Alvaro Herrera @ 2009-02-04 16:59 UTC (permalink / raw)
To: Marc Mamin <M.Mamin@intershop.de>; +Cc: pgsql-admin
Marc Mamin wrote:
> There are 2 features that would be helpfull in my case:
>
> - define vacuum properties on table groups rather than on given tables:
>
> For example, I don't want to vacuum daily tables which can be
> identified with a regexp:
> tablename ~ '\.*d_[0-9]{8}'
Hmm. I'm not really sure how could we handle this kind of situation.
Something that strikes me as useful is the ability to say "please don't
autovacuum any tables except those that I mark it so". Right now you
can only disable autovac for particular tables, not enable them.
> - automatically remove the pg_autovacuum entry when a table is dropped
> (well it's not such complicated...)
Yep.
> And 2 questions:
>
> Can I disable track_counts when I'm not using autovacuum, or is it used
> for other purposes ?
Other than the various pg_stat views, they are not used anywhere else.
> Free Space Map: is it affected by dropped tables ? Or more generally,
> must I care with it in a special way in my situation ?
No, don't worry about it.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum question
2009-01-30 16:31 autovacuum question Marc Mamin <M.Mamin@intershop.de>
2009-02-04 16:59 ` Re: autovacuum question Alvaro Herrera <alvherre@commandprompt.com>
@ 2009-02-05 12:02 ` Harald Fuchs <hari.fuchs@gmail.com>
2009-02-05 18:27 ` Re: autovacuum question Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 17+ messages in thread
From: Harald Fuchs @ 2009-02-05 12:02 UTC (permalink / raw)
To: pgsql-admin
In article <20090204165933.GB32680@alvh.no-ip.org>,
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Marc Mamin wrote:
>> There are 2 features that would be helpfull in my case:
>>
>> - define vacuum properties on table groups rather than on given tables:
>>
>> For example, I don't want to vacuum daily tables which can be
>> identified with a regexp:
>> tablename ~ '\.*d_[0-9]{8}'
> Hmm. I'm not really sure how could we handle this kind of situation.
Couldn't we use schemas for that? Put all those tables into a
separate schema and somehow tell autovacuum to leave that schema
alone.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum question
2009-01-30 16:31 autovacuum question Marc Mamin <M.Mamin@intershop.de>
2009-02-04 16:59 ` Re: autovacuum question Alvaro Herrera <alvherre@commandprompt.com>
2009-02-05 12:02 ` Re: autovacuum question Harald Fuchs <hari.fuchs@gmail.com>
@ 2009-02-05 18:27 ` Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 17+ messages in thread
From: Alvaro Herrera @ 2009-02-05 18:27 UTC (permalink / raw)
To: Harald Fuchs <hari.fuchs@gmail.com>; +Cc: pgsql-admin
Harald Fuchs escribió:
> In article <20090204165933.GB32680@alvh.no-ip.org>,
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>
> > Marc Mamin wrote:
> >> There are 2 features that would be helpfull in my case:
> >>
> >> - define vacuum properties on table groups rather than on given tables:
> > Hmm. I'm not really sure how could we handle this kind of situation.
>
> Couldn't we use schemas for that? Put all those tables into a
> separate schema and somehow tell autovacuum to leave that schema
> alone.
I don't like that. It would force the users to use schemas in a certain
way, taking flexibility away from them.
In an old design for autovacuum "maintenance windows" (which were
promised for 8.2 and still haven't seen the light of day) we were
introducing "table groups" as a concept. Sounds like something that
applies here too. If you can attach a table to a group during creation,
you can decide its autovacuum schedule right there. You should be able
to do something like
create table d_123 ( ... ) WITH (autovacuum_group = 'dont_ever_vacuum');
which means autovacuum automatically ignores that table.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2009-02-05 18:27 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-16 17:27 Autovacuum Question Chris Hoover <revoohc@gmail.com>
2006-05-16 17:27 ` Ian Westmacott <ianw@intellivid.com>
2006-05-16 17:30 Re: Autovacuum Question Mark Liberman <mliberman@mixedsignals.com>
2006-05-16 18:31 ` Jim C. Nasby <jnasby@pervasive.com>
2007-03-26 17:17 autovacuum question Laszlo Nagy <gandalf@designaproduct.biz>
2007-03-26 22:10 ` Re: autovacuum question Jim Nasby <decibel@decibel.org>
2007-03-27 10:36 ` Re: autovacuum question Laszlo Nagy <gandalf@designaproduct.biz>
2007-03-27 17:06 ` Re: autovacuum question Alvaro Herrera <alvherre@alvh.no-ip.org>
2007-03-30 14:43 autovacuum question Carol Walter <walterc@indiana.edu>
2007-03-30 14:59 ` Re: autovacuum question Thomas Pundt <mlists@rp-online.de>
2007-04-04 18:40 ` Re: autovacuum question Carol Walter <walterc@indiana.edu>
2007-04-05 07:19 ` Re: autovacuum question Thomas Pundt <mlists@rp-online.de>
2007-03-30 15:04 ` Re: autovacuum question Alvaro Herrera <alvherre@commandprompt.com>
2009-01-30 16:31 autovacuum question Marc Mamin <M.Mamin@intershop.de>
2009-02-04 16:59 ` Re: autovacuum question Alvaro Herrera <alvherre@commandprompt.com>
2009-02-05 12:02 ` Re: autovacuum question Harald Fuchs <hari.fuchs@gmail.com>
2009-02-05 18:27 ` Re: autovacuum question Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox