Обсуждение: autovacuum question

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

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.

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.

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