disable heavily updated (but small) table auto-vecuuming

From: Ludwik Dylag <ldylag(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: disable heavily updated (but small) table auto-vecuuming
Date: 2009-09-15 08:06:21
Message-ID: 2fe468a20909150106i6b432110j87b837f3d71976a9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message std pik 2009-09-15 09:27:41 statistical table
Previous Message Andrzej Zawadzki 2009-09-15 07:36:38 Re: CLUSTER and a problem