Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
(Voted to move to dba.SE). What have you tried? PostgreSQL version? Is autovacuum on? What've you read and searched for already? –  Craig Ringer Mar 19 '13 at 6:23
    
@CraigRinger See my updated question. –  ams Mar 19 '13 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. –  Craig Ringer Mar 19 '13 at 13:43
add comment

migrated from stackoverflow.com Mar 19 '13 at 9:01

This question came from our site for professional and enthusiast programmers.

4 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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