Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011

    Unanswered: Identifying the critical tables for manually vacuum


    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


  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    What exactly do you mean with "lagging behind"? You can find out when tables were vacuumed the last time using this:
    select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze 
    from pg_stat_user_tables;
    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.
    Last edited by shammat; 12-09-11 at 05:19.

  3. #3
    Join Date
    Dec 2011
    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


  4. #4
    Join Date
    Aug 2011
    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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts