Given a PostgreSQL database what command can be used to determine if the database needs to vacuumed?

UPDATE I am working with client who is evaluating postgres but I am not a DBA, I just happen to be more experienced with postgres than they are. They are getting update times of several hours in their tests with 500K rows in a table. They had not done any changes to their GUCS for shared buffers, work_mem ... etc I just went through with them and had them do that. In some of their tests postgres reports 3674 seconds for updates that take 2.66 seconds in oracle and 33.8 seconds in MySQL. I want to be able to answer the question would Vaccum make a difference to their tests, but to be able to get real data back to them about the impact of vacuum on their tests.

Postgres 9.2 on CentOS 6 Intel Dual Process Quad Core Xeon K5570 8GB RAM

  • (Voted to move to dba.SE). What have you tried? PostgreSQL version? Is autovacuum on? What've you read and searched for already? Mar 19, 2013 at 6:23
  • @CraigRinger See my updated question.
    – ams
    Mar 19, 2013 at 13:29
  • explain (verbose, buffers, analyze). If you're concerned about confidentiality paste it to explain.depesz.com, enable anonymization when you submit, check that the result is ok, and paste a link here. Please also edit your question to show the output of wiki.postgresql.org/wiki/Server_Configuration. Read wiki.postgresql.org/wiki/Slow_Query_Questions and the pages it links to. Please also confirm if autovacuum is actually enabled. Mar 19, 2013 at 13:43

5 Answers 5


Start with the show database bloat sample query on the PostgreSQL wiki if you're investigating possible table/index bloat issues.

Also check whether autovacuum is enabled. Some people misguidedly turn it down or off because they see it creating load; they should actually be turning it up in these situations.

  • The problem is that vacuum blocks tables and when you need high availability and you don't have valley hours (hours with less work load) you can end up with an environment where vacuum and autovacuum are almost never used.
    – EAmez
    Aug 7, 2019 at 13:37

If you are administrator/developer of the database, some symptoms are when you have huge (>1m rows) tables with temp data that are regularly deleted (like deleting the entries for the last month) and the database/table size does not change.

I'm using these queries to check the table size:

* DB schema sizes
SELECT pg_size_pretty(sum(pg_relation_size(pg_class.oid))::bigint), nspname,
CASE pg_class.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 't' THEN 'toast' ELSE pg_class.relkind::text END
FROM pg_class
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace)
GROUP BY pg_class.relkind, nspname
ORDER BY sum(pg_relation_size(pg_class.oid)) DESC;

* DB table sizes
SELECT pg_size_pretty(pg_relation_size(pg_class.oid)), pg_class.relname,     pg_namespace.nspname,
CASE pg_class.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 't' THEN 'TOAST' ELSE pg_class.relkind::text END
FROM pg_class 
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace)
ORDER BY pg_relation_size(pg_class.oid) DESC;

This is not 100% relevant to your question but can give you a start.


There is no single parameter that determines, whether vacuum is necessary, but it can be quite interesting to look at the thresholds that postgres uses to trigger autovacuum.

Details and a query to do so can be found in another thread: Aggressive Autovacuum on PostgreSQL.


For general, we turn on the autovacuum parameter in postgresql.conf,after that , the database will do the vacuum work automately if required.

For more details about autovaucum , see the the routine vacuuming section of the manual.

I think ther is no a single command that can determine if the database needs to vacuumed.


pg_admin will tell you if the table should be vacuumed.

  • 2
    An explanation about how this can be achieved would make this answer usefull.
    – EAmez
    Aug 7, 2019 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.