Simple thing to make pg_autovacuum more useful

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

Table "pg_catalog.pg_autovacuum"
Column | Type | Modifiers
- ------------------+---------+-----------
vacrelid | oid | not null
enabled | boolean | not null
vac_base_thresh | integer | not null
vac_scale_factor | real | not null
anl_base_thresh | integer | not null
anl_scale_factor | real | not null
vac_cost_delay | integer | not null
vac_cost_limit | integer | not null
freeze_min_age | integer | not null
freeze_max_age | integer | not null

Can we by default set vac_cost_limit and vac_cost_delay have a DEFAULT
- -1? That way by default it will use the settings in postgresql.conf?

Secondly can we set the default for freeze_min_age to 100mil (I think
that is the default in the docs) and freeize_max_age to the default
200000000 .

In the environments we manage we are using pg_autovacuum alot in order
to manage individual relations.

This is probably too late for 8.3 :( but it should would make our lives
easier.

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHj8ayATb/zqfZUUQRAjHqAJ0bkc/ik4mZ0JldO6NLLjDKDDvLogCgqCK6
er+zFTHiFO1Fbi/BXzkWCqA=
=7swT
-----END PGP SIGNATURE-----

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> Can we by default set vac_cost_limit and vac_cost_delay have a DEFAULT
> - -1? That way by default it will use the settings in postgresql.conf?

Surely we're not going to force initdb for that.

> Secondly can we set the default for freeze_min_age to 100mil (I think
> that is the default in the docs) and freeize_max_age to the default
> 200000000 .

This is an outright bad idea, because those defaults aren't hard-wired.
Why would you not use -1 if you want a default value?

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 17 Jan 2008 16:54:47 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> > - -1? That way by default it will use the settings in
> > postgresql.conf?
>
> Surely we're not going to force initdb for that.

I didn't realize it would take that so sure lets do it for 8.4.

>
> > Secondly can we set the default for freeze_min_age to 100mil (I
> > think that is the default in the docs) and freeize_max_age to the
> > default 200000000 .
>
> This is an outright bad idea, because those defaults aren't
> hard-wired. Why would you not use -1 if you want a default value?

There are two things here. One having a default value 8.2 currently
doesn't (8.3 I don't know). Two what the default value should be. I
will happily defer to you on that determination.

Sincerely,

Joshua D. Drake
- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHj9FuATb/zqfZUUQRAlt3AKCWo+v26U2iyD2wpoRAQxFlmHwfWwCePyYf
qQcaopPJ1ocYe+kcKIwEWYM=
=AtNk
-----END PGP SIGNATURE-----

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> There are two things here. One having a default value 8.2 currently
> doesn't

I'm not really convinced by this argument. pg_autovacuum was never
designed to be user-friendly; it is designed to be the back end storage
for something that *is* user-friendly (which we have not got yet).
I would say that smarts about defaults ought to go into whatever code
you are creating that inserts into pg_autovacuum.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 17 Jan 2008 17:13:52 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > There are two things here. One having a default value 8.2 currently
> > doesn't
>
> I'm not really convinced by this argument. pg_autovacuum was never
> designed to be user-friendly; it is designed to be the back end
> storage for something that *is* user-friendly (which we have not got
> yet). I would say that smarts about defaults ought to go into
> whatever code you are creating that inserts into pg_autovacuum.

My insert statements don't agree ;).

I can type it, I can script it. It's not that big of a deal but
consider what I am asking provides only a positive return that isn't
out of line with the scope of autovacuum configuration in general.

Your objection is let's keep it as difficult as possible within the
existing paradigm because nobody thought pg_autovacuum could be useful
in the first place.

That seems incorrect to me as we continue to have people that are going
to need to use pg_autovacuum.

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHj9csATb/zqfZUUQRAlpAAJ4lJcj2pqqaAVz+x8SD6DpKH3cWUgCgiAfL
edr2JA2sl72rzH2ogGHxpy8=
=9FO8
-----END PGP SIGNATURE-----

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> Your objection is let's keep it as difficult as possible within the
> existing paradigm because nobody thought pg_autovacuum could be useful
> in the first place.

No, my point is that there's no value in putting band-aids on an object
that was never designed to be user-friendly. The extra ease of use from
putting defaults on that table's columns is insignificant compared to
what we'd get by fixing its *real* problems:

* superuser-only, no mechanism to let users admin their own tables
(nor any way to reconcile user-set values with a DBA's possible
wish to override them)
* no support for dumping and restoring settings

I don't think we should be encouraging direct manual insertions into
pg_autovacuum in any case.

So I'd rather see some effort spent on figuring out what the API really
*should* look like. I don't know, other than that it should hard-wire
as little as possible because we are likely to be changing the set of
available parameters in future. Maybe we need a concept like per-table
settings for GUC variables?

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 17 Jan 2008 17:38:57 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > Your objection is let's keep it as difficult as possible within the
> > existing paradigm because nobody thought pg_autovacuum could be
> > useful in the first place.
>
> No, my point is that there's no value in putting band-aids on an
> object that was never designed to be user-friendly. The extra ease
> of use from putting defaults on that table's columns is insignificant
> compared to what we'd get by fixing its *real* problems:
>
> * superuser-only, no mechanism to let users admin their own tables
> (nor any way to reconcile user-set values with a DBA's possible
> wish to override them)
> * no support for dumping and restoring settings
>
> I don't think we should be encouraging direct manual insertions into
> pg_autovacuum in any case.
>
> So I'd rather see some effort spent on figuring out what the API
> really *should* look like. I don't know, other than that it should
> hard-wire as little as possible because we are likely to be changing
> the set of available parameters in future. Maybe we need a concept
> like per-table settings for GUC variables?

Tom I don't understand this. Your arguments above are great but let's
be realistic. I am offering something that even *I* could do with code
that is simple and useful.

You are offering what appears to be a "solution". A perfectly valid one
in fact. Which one is going to get done first? Which one is going to
provide immediate benefit?

I can't realistically code change the code for the first problem. I
might be able to hack my way through the second, I guarantee you I
could do my solution.

So why is it such a bad thing to implement something incrementally
useful? Especially considering my incremental solution doesn't
conflict with your todo for pg_autovacuum?

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHj9wwATb/zqfZUUQRArj4AJ0e2ln+tul3Z7tUHMWuwSVfBC8q6ACgocP3
j5dKNnHaoClMJgJRV2mHFTA=
=j3NJ
-----END PGP SIGNATURE-----

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> You are offering what appears to be a "solution". A perfectly valid one
> in fact. Which one is going to get done first? Which one is going to
> provide immediate benefit?

The problem is that your "immediate benefit" is to encourage people
to do direct manual insertions into pg_autovacuum, which is something
that we shouldn't be encouraging, because it's not the correct long-term
solution. Or even short-term --- it seems reasonably likely to me that
something could be done about building a decent API in the 8.4 cycle,
which is the soonest we could entertain a proposal to put defaults on
pg_autovacuum anyway.

regards, tom lane

All this thread is a waste of time. We've previously agreed that we're
going to store autovacuum per-table settings in pg_class.reloptions.
That automatically gives it pg_dump support, and moreover it means the
user needs not set the options that he/she doesn't want to change from
defaults.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> All this thread is a waste of time. We've previously agreed that we're
> going to store autovacuum per-table settings in pg_class.reloptions.

I had forgotten that discussion. So the plan is for the pg_autovacuum
catalog to go away entirely, correct?

regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > All this thread is a waste of time. We've previously agreed that we're
> > going to store autovacuum per-table settings in pg_class.reloptions.
>
> I had forgotten that discussion. So the plan is for the pg_autovacuum
> catalog to go away entirely, correct?

Yup.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>> You are offering what appears to be a "solution". A perfectly valid one
>> in fact. Which one is going to get done first? Which one is going to
>> provide immediate benefit?
>
> The problem is that your "immediate benefit" is to encourage people
> to do direct manual insertions into pg_autovacuum, which is something
> that we shouldn't be encouraging, because it's not the correct long-term
> solution. Or even short-term --- it seems reasonably likely to me that
> something could be done about building a decent API in the 8.4 cycle,
> which is the soonest we could entertain a proposal to put defaults on
> pg_autovacuum anyway.

Are you picturing adding ALTER TABLE commands to set autovacuum parameters? Or
do you mean for tools like pgadmin to control this? Because the latter could
happen even during the 8.3 cycle (though I perhaps not with pgadmin itself
which I think follows the Postgres release cycle).

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> Are you picturing adding ALTER TABLE commands to set autovacuum
> parameters?

Well, as I said, I was trying to think of an appropriate user-visible
API, which I didn't think that pg_autovacuum itself could become.

Further downthread Alvaro points out that we've already had that
discussion, and exploiting the reloptions infrastructure was agreed
to be a reasonable solution. It took a bit of searching to find
that thread, but here it is:
http://archives.postgresql.org/pgsql-hackers/2007-02/msg01440.php

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 17 Jan 2008 21:43:46 -0300
Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:

> Tom Lane wrote:
> > Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > > All this thread is a waste of time. We've previously agreed that
> > > we're going to store autovacuum per-table settings in
> > > pg_class.reloptions.
> >
> > I had forgotten that discussion. So the plan is for the
> > pg_autovacuum catalog to go away entirely, correct?
>
> Yup.
>
Wait .reloptions is a column... how are we going to populate it and
array?

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHkBosATb/zqfZUUQRAru/AJ90HXXRHP17GmfzGa1I5nc34VCNGQCeM5Gb
6CeL1LyVZuOvYgG/OAoz7rw=
=EEuY
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 17 Jan 2008 20:34:07 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> > Are you picturing adding ALTER TABLE commands to set autovacuum
> > parameters?
>
> Well, as I said, I was trying to think of an appropriate user-visible
> API, which I didn't think that pg_autovacuum itself could become.
>
> Further downthread Alvaro points out that we've already had that
> discussion, and exploiting the reloptions infrastructure was agreed
> to be a reasonable solution. It took a bit of searching to find
> that thread, but here it is:
> http://archives.postgresql.org/pgsql-hackers/2007-02/msg01440.php

Interesting. O.k. I must admit that I would sure rather use ALTER TABLE
to do this than INSERT, SELECT. Let's not lose sight that we still need
to be able to manage large sets of relations though.

We need to make sure there is a readily available way to retrieve the
data as well.

Sincerely,

Joshua D. Drake
- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHkBrAATb/zqfZUUQRApUHAJ9MvS3pcZAa2ix+8pCrmGQ4G1VdiQCaA+44
YwbncnAmby4pzCZqnRn6ANM=
=cbaq
-----END PGP SIGNATURE-----

On Fri, Jan 18, 2008 at 01:07:27AM +0000, Gregory Stark wrote:
> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> > "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> >> You are offering what appears to be a "solution". A perfectly valid one
> >> in fact. Which one is going to get done first? Which one is going to
> >> provide immediate benefit?
> >
> > The problem is that your "immediate benefit" is to encourage people
> > to do direct manual insertions into pg_autovacuum, which is something
> > that we shouldn't be encouraging, because it's not the correct long-term
> > solution. Or even short-term --- it seems reasonably likely to me that
> > something could be done about building a decent API in the 8.4 cycle,
> > which is the soonest we could entertain a proposal to put defaults on
> > pg_autovacuum anyway.
>
> Are you picturing adding ALTER TABLE commands to set autovacuum parameters? Or
> do you mean for tools like pgadmin to control this? Because the latter could
> happen even during the 8.3 cycle (though I perhaps not with pgadmin itself
> which I think follows the Postgres release cycle).

Of course, pgadmin already does this :-P Ask that tall guy sitting next to
you for a demo...

What it doesn't do is help you figure out what values to put in...

//Magnus

On 18/01/2008, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> Are you picturing adding ALTER TABLE commands to set autovacuum parameters? Or
> do you mean for tools like pgadmin to control this? Because the latter could
> happen even during the 8.3 cycle (though I perhaps not with pgadmin itself
> which I think follows the Postgres release cycle).

pgAdmin already has an interface to pg_autovacuum.

/D

On Thursday 17 January 2008 19:17:00 Joshua D. Drake wrote:
> On Thu, 17 Jan 2008 21:43:46 -0300
>
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> > Tom Lane wrote:
> > > Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > > > All this thread is a waste of time. We've previously agreed that
> > > > we're going to store autovacuum per-table settings in
> > > > pg_class.reloptions.
> > >
> > > I had forgotten that discussion. So the plan is for the
> > > pg_autovacuum catalog to go away entirely, correct?
> >
> > Yup.
>
> Wait .reloptions is a column... how are we going to populate it and
> array?

ALTER TABLE foo SET autovacuum to true;
ALTER TABLE foo SET autovacuum_naptime to '1min';

(or there about)

I suppose we could also have pg_autovacuum become a view, with rules to manage
relopts.

>
> Sincerely,
>
> Joshua D. Drake

--
Darcy Buskermolen
Command Prompt, Inc.
+1.503.667.4564 X 102
http://www.commandprompt.com/
PostgreSQL solutions since 1997

Added to TODO:

o Store per-table autovacuum settings in pg_class.reloptions.

http://archives.postgresql.org/pgsql-hackers/2007-02/msg01440.php
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00724.php

---------------------------------------------------------------------------

Alvaro Herrera wrote:
> All this thread is a waste of time. We've previously agreed that we're
> going to store autovacuum per-table settings in pg_class.reloptions.
> That automatically gives it pg_dump support, and moreover it means the
> user needs not set the options that he/she doesn't want to change from
> defaults.
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +