2009/9/15 Ludwik Dylag <ldylag@gmail.com>:
> 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?
How about just disabling autovacuum for that table?
http://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS
...Robert
