disable heavily updated (but small) table auto-vecuuming

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello

I have a database where I daily create a table.
Every day it is being inserted with ~3mln rows and each of them is being updated two times.The process lasts ~24 hours so the db load is the same at all the time. total size of the table is ~3GB.

My current vacuum settings are:
autovacuum = on
autovacuum_max_workers = 3
autovacuum_freeze_max_age = 2000000000 (changed from 200000000)
vacuum_freeze_min_age = 100000000

I have over 250 mln of frozen ids.
# SELECT datname, age(datfrozenxid) FROM pg_database;
  datname   |    age
------------+-----------
my_database | 256938425

and every day (since max age exceeded 200mln.) the current table is being vacuumed two hours after it was created.

My goal is to set the vacuum properties so the current table is not vacuumed when it is used. And to vacuum it manually one day after it was used.

Is it enough to set
autovacuum=off
autovacuum_freeze_max_age=2000000000
vacuum_freeze_min_age = 100000000
and shedule in cron daily vacuum on selected table?


Thanks in advance for your help.

--
Ludwik Dyląg

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux