1) How to Enable and Desable autovacuum on a table/toasttable.
To Change the auto-vacuum parameters at Table level
---------------------------------------------------
ALTER TABLE "PlayerEvents" SET (autovacuum_analyze_threshold = 2500, autovacuum_vacuum_threshold = 2500);
To Check the status weather at got applied thos changes are not use :
---------------------------------------------------------------------
For getting OID of Schema:
--------------------------
select nspname from pg_namespace ,pg_class where pg_namespace.oid = pg_class.relnamespace;
checking auto vacuum manual settings:
--------------------------------------------------------
SELECT c.relname, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ') FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) where c.relnamespace=62952 and c.relname ='PlayerEvents';
--disable auto vacuum
ALTER TABLE my_table SET (
autovacuum_enabled = FALSE, toast.autovacuum_enabled = FALSE
);
--enable auto vacuum
ALTER TABLE my_table SET (
autovacuum_enabled = TRUE, toast.autovacuum_enabled = TRUE
);
To Change the auto-vacuum parameters at Table level
---------------------------------------------------
ALTER TABLE "PlayerEvents" SET (autovacuum_analyze_threshold = 2500, autovacuum_vacuum_threshold = 2500);
To Check the status weather at got applied thos changes are not use :
---------------------------------------------------------------------
For getting OID of Schema:
--------------------------
select nspname from pg_namespace ,pg_class where pg_namespace.oid = pg_class.relnamespace;
checking auto vacuum manual settings:
--------------------------------------------------------
SELECT c.relname, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ') FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) where c.relnamespace=62952 and c.relname ='PlayerEvents';
2)when the auto vacuum demon is ariseing for vacuum.
autovacuum_starts=(autovacum_scale_factor*totale tuples of a table) + autovacuum_threshold_value
Eg: total tuples of a emp table =1000
av threshold =100
av scal factor=0.2
then avstart=(1000*0.2)+100
=200+100
=300.
NOTE: when ever 300 updates/delete are performed on the table then only autovacuumed emp table.
ANALYZE operation also same.
