How to determine if a postgres database needs to be vacuumed?

Question

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

score 8 · Answer 1 · answered Mar 19 '13 at 13:53

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.

score 7 · Answer 2 · edited Mar 19 '13 at 11:24

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.

score 5 · Answer 3 · edited Apr 13 '17 at 12:42

5

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.

edited Apr 13 '17 at 12:42

Community

1

answered Mar 19 '13 at 10:30

pygrac

766
5
7

score 2 · Answer 4 · edited Mar 19 '13 at 13:54

2

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.

edited Mar 19 '13 at 13:54

Craig Ringer

57,821
6
162
193

answered Mar 19 '13 at 07:59

francs

1,457
1
15
25

score 0 · Answer 5 · edited May 02 '15 at 18:48

0

pg_admin will tell you if the table should be vacuumed.

edited May 02 '15 at 18:48

Colin 't Hart

9,455
15
36
44

answered May 02 '15 at 16:10

ABCD EFGHIJK

109
1
1
7

How to determine if a postgres database needs to be vacuumed?

5 Answers5