Try to understand VACUUM and its settings

From: Michael Chau <michael(dot)chau(at)gameyourgame(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Try to understand VACUUM and its settings
Date: 2015-10-05 22:00:46
Message-ID: CALE++3Q20EAVM0NuS0Wst3yBSjKKYvpJU-Sdbs6ssJXG1UbHDg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Last Friday, I ran :

postgres=# select max(age(datfrozenxid)) from pg_database;

max
42579490

and then I ran :

SELECT relname, age(relfrozenxid) as xid_age,
pg_size_pretty(pg_table_size(oid)) as table_size
FROM pg_class
WHERE relkind = 'r' and pg_table_size(oid) > 1073741824
ORDER BY age(relfrozenxid) DESC LIMIT 20;

"table_1";42574012;"10111 MB"
"table_2";42567398;"5961 MB"
"table_3";20320509;"12 GB"

Today, the max is

43068744

and

"table_1";43063214;"10134 MB"
"table_2";43056600;"5984 MB"
"table_3";20809711;"12 GB"

====

It looks like the age(relfrozenxid) of the tables go up in tandem with the
max. autovacuum_freeze_max_age and vacuum_freeze_table_age are commented
out in postgresql.conf

#autovacuum_freeze_max_age = 200000000
#vacuum_freeze_table_age = 150000000

So, do I need to run vacuum freeze on those tables? Also, if
autovacuum_freeze_max_age is commented, does it still mean that the default
is 200M?

Thanks,
Michael

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Munro 2015-10-05 23:27:51 Re: Replication with 9.4
Previous Message Kevin Grittner 2015-10-05 21:17:52 Re: Serialization errors despite KEY SHARE/NO KEY UPDATE