Hi,

I am new to postgres and i have a task to find out the tables which need to be manually vacuumed in case of vacuum process lagging behind..
Can you please help me out

Thanks

What exactly do you mean with "lagging behind"? You can find out when tables were vacuumed the last time using this:Is that what you had in mind?

I don't think you can find out if a table needs vacuum unless you run a vacuum on them. But I might be mistaken.

Thanks for your prompt reply.

But, I am looking for some thing which gives me the list of tables.
For example here is the scenario:

When you run the query once a day

select datname, age(datfrozenxid) from pg_database;

If you find the datfrozenxid is increasing then it means the auto vacuuming process is lagging behind. This will cause a transaction xid wrap around soon and will result in force shut down of the postgres.

So, When i find the age of the datfrozenxid is increasing I want to check the tables and manually vacuum the tables.


I hope this is clear

Thanks

Look into pg_stat_all_tables

and see column- n_dead_tup

if the figure is high in comparison with total rows, then this means this table requires
vacumming.

how much is the % , is matter of internal policy. But it is safe to assume anything above 4% is a need-to-vaccum