Postgres Pro
Downloads
Home   >   mailing lists

Thread: Try to understand VACUUM and its settings

Try to understand VACUUM and its settings

From
Michael Chau
Date:
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

Re: Try to understand VACUUM and its settings

From
Jim Nasby
Date:
On 10/5/15 5:00 PM, Michael Chau wrote:
> 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?

Yes, and unless you're running a very high transaction rate you probably
don't need to mess with it. Likewise you normally don't need to
explicitly freeze, though it's not a bad idea to do so after a large
data load. Just make sure that there's no open transactions that were
started before the data load transaction when you run the VACUUM.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com