Thread: Enabling autovacuum per table

On 10/15/18 11:01 AM, Rijo Roy wrote:
> Hello Experts,
> 
> Is there any possibility for autovacuum to work on a user table if we set
> Alter table sometable set (autovacuum_enabled = true) ; even if the 
> parameter autovacuum = off in Postgresql.conf
> 
> I am using Postgresql 10 on Linux 6.9.
> 
> According to me, it won't work without setting autovacuum = on except 
> for Template0 database. What is your opinion?

I think you are right, autovacuum won't vacuum and analyze a table 
without setting autovacuum = on. But PostgreSQL might want to force 
vacuum if a table is at risk of wraparound.

-- 
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

On Mon, 15 Oct 2018 at 2:58 pm, Arthur Zakirov

<a.zakirov@postgrespro.ru> wrote:

On 10/15/18 11:01 AM, Rijo Roy wrote:

> Hello Experts,
>
> Is there any possibility for autovacuum to work on a user table if we set
> Alter table sometable set (autovacuum_enabled = true) ; even if the
> parameter autovacuum = off in Postgresql.conf
>
> I am using Postgresql 10 on Linux 6.9.
>
> According to me, it won't work without setting autovacuum = on except
> for Template0 database. What is your opinion?

I think you are right, autovacuum won't vacuum and analyze a table
without setting autovacuum = on. But PostgreSQL might want to force
vacuum if a table is at risk of wraparound.

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

On 10/15/18 1:01 AM, Rijo Roy wrote:
> Hello Experts,
> 
> Is there any possibility for autovacuum to work on a user table if we set
> Alter table sometable set (autovacuum_enabled = true) ; even if the 
> parameter autovacuum = off in Postgresql.conf
> 
> I am using Postgresql 10 on Linux 6.9.
> 
> According to me, it won't work without setting autovacuum = on except 
> for Template0 database. What is your opinion?

The docs opinion:

https://www.postgresql.org/docs/10/static/sql-createtable.html
"autovacuum_enabled, toast.autovacuum_enabled (boolean)

     Enables or disables the autovacuum daemon for a particular table. 
If true, the autovacuum daemon will perform automatic VACUUM and/or 
ANALYZE operations on this table following the rules discussed in 
Section 24.1.6. If false, this table will not be autovacuumed, except to 
prevent transaction ID wraparound. See Section 24.1.5 for more about 
wraparound prevention. Note that the autovacuum daemon does not run at 
all (except to prevent transaction ID wraparound) if the autovacuum 
parameter is false; setting individual tables' storage parameters does 
not override that. Therefore there is seldom much point in explicitly 
setting this storage parameter to true, only to false.
"

> 
> Thanks,
> Rijo Roy
> 
> Sent from Yahoo Mail on Android 
>
<https://go.onelink.me/107872968?pid=InProduct&c=Global_Internal_YGrowth_AndroidEmailSig__AndroidUsers&af_wl=ym&af_sub1=Internal&af_sub2=Global_YGrowth&af_sub3=EmailSignature>


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Rijo Roy wrote:
> Is there a possibility to disable auto analyze  if we set autovacuum_analyze_scale_factor
> to 100% and autovacuum_analyze_threshold = 0?

Why would you want that?

You are trying very hard to hurt yourself by disabling autovacuum;
maybe there is a better way to solve your problem.

Can you tell us more about the actual problem?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com