finding tables about to be vacuum freezed

From: Steve Kehlet <steve(dot)kehlet(at)gmail(dot)com>
To: Forums postgresql <pgsql-general(at)postgresql(dot)org>
Subject: finding tables about to be vacuum freezed
Date: 2015-05-06 01:40:33
Message-ID: CA+bfosH9jo-A9t8ccO7pmJW025nHk9YtZ858JU6NhhqHsHj2ug@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello, recently one of my tables needed a vacuum (to prevent wraparound)
and of course it happened at a really bad time, so since then I've been
learning about how Transaction ID Wraparound works and its associated
parameters.

I'm trying this query to see how close my tables are to hitting the
vacuum_freeze_table_age threshold (150M in my case):

SELECT
relname,
age(relfrozenxid) as xid_age,
ROUND(100.0 * age(relfrozenxid) /
current_setting('vacuum_freeze_table_age')::numeric, 1) || '%' AS "% til
vacuum freeze"
FROM
pg_class
WHERE relkind = 'r';

For now, assume my tables have no storage parameters that override the
defaults.

I was surprised at the results, almost all my tables look like:

my_table | 160589343 | 107.1%

Or about 160m transactions old. I would have thought with my current
settings:
vacuum_freeze_min_age = 50m
vacuum_freeze_table_age = 150m
autovacuum_freeze_max_age = 200m

that the autovacuumer would have already forced a vacuum freeze on all
these tables. According to the docs, "a whole table sweep is forced if the
table hasn't been fully scanned for vacuum_freeze_table_age minus
vacuum_freeze_min_age transactions" which would be 100m transactions.

I'm guessing my understanding here is wrong. What did I miss?

Just in case it matters, here's my current txid:
mydb=# select txid_current();
txid_current
--------------
1485894081
(1 row)

Thanks!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mitu Verma 2015-05-06 06:56:49 Re: delete is getting hung when there is a huge data in table
Previous Message Venkata Balaji N 2015-05-05 22:49:22 Re: Standby problem after restore_command Implementation