On 01/20/2016 04:54 PM, AI Rumman wrote:
But, will it not create transaction wraparound for those table?
See below for complete details:
http://www.postgresql.org/docs/9.1/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
but short version from above:
23.1.4. Preventing Transaction ID Wraparound Failures
1)
"PostgreSQL's MVCC transaction semantics depend on being able to compare
transaction ID (XID) numbers: a row version with an insertion XID
greater than the current transaction's XID is "in the future" and should
not be visible to the current transaction. But since transaction IDs
have limited size (32 bits) a cluster that runs for a long time (more
than 4 billion transactions) would suffer transaction ID wraparound: the
XID counter wraps around to zero, and all of a sudden transactions that
were in the past appear to be in the future — which means their output
become invisible. In short, catastrophic data loss. (Actually the data
is still there, but that's cold comfort if you cannot get at it.) To
avoid this, it is necessary to vacuum every table in every database at
least once every two billion transactions."
2)
"The maximum time that a table can go unvacuumed is two billion
transactions minus the vacuum_freeze_min_age value at the time VACUUM
last scanned the whole table. If it were to go unvacuumed for longer
than that, data loss could result. To ensure that this does not happen,
autovacuum is invoked on any table that might contain XIDs older than
the age specified by the configuration parameter
autovacuum_freeze_max_age. (This will happen even if autovacuum is
disabled.)"
Thanks.
On Wed, Jan 20, 2016 at 4:44 PM, Melvin Davidson <melvin6925@xxxxxxxxx
<mailto:melvin6925@xxxxxxxxx>> wrote:
ALTER TABLE your_schema.your_table SET (autovacuum_enabled = false,
toast.autovacuum_enabled = false);
On Wed, Jan 20, 2016 at 6:22 PM, AI Rumman <rummandba@xxxxxxxxx
<mailto:rummandba@xxxxxxxxx>> wrote:
Hi,
I have a table with daily partition schema on Postgresql 9.1
where we are keeping 2 years of data.
Often I experience that autovacuum process is busy with old
tables where there is no change. How can I stop it?
Please advice.
Thanks.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
