3 moving my follow-up remark to its own space
source | link

What version of PostgreSQL are you using? If it is prior to 9.0 (iirc), you may have inadequate free space map settings and thus be leaking space that vacuum can't recover efficiently. In this case, the sort of bloat you are seeing would be affecting the production db.

At the same time, this certainly does raise red flags. The question is what those red flags mean. is it a badly configured server? Is it a corrupted back-up? To test, what you should do is map out the number of rows in the tables on the production server and the backup. If they are close (remember the production server is likely having new updates and deletes) then you are probably pretty good. If there are wide deviations, you may want to look into those more carefully. Assuming nothing comes up there, I would suspect a problem with the free space map settings.

[Addendum] in the tradition of RTFM I have gone hunting in the manual for PostrgeSQL. Here are some more pieces to the puzzle

#is the autovacuum switched on?
postgres@db1:/tmp$ psql -c "SHOW autovacuum;"
 autovacuum
------------
 on
(1 row)

#The "track counts" parameter is needed by autovacuum which uses statistics from database activity to know where to do its job
postgres@db1:/tmp$ psql -c "SHOW track_counts;"
 track_counts
--------------
 on
(1 row)

#is there an autovacuum daemon resident in memory?
postgres@db1:/tmp$ ps  -ef | grep 'autovacuum'
postgres  1261  1021  0 Jan23 ?        00:08:27 postgres: autovacuum launcher process                             
postgres 18347 18149  0 00:33 pts/0    00:00:00 grep autovacuum


#what's been happening on the live server?
postgres@LIVEdb1:/tmp$ psql -c "SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_all_tables;"
#result is list of 65 tables (out about 300), all empty (no dates at all)

Gratefully following up on @craig-ringer's advice to look into VACUUM FULL I turned to PostgreSQL documentation, (paraphrased) "... In the worst case where your disk is nearly full, VACUUM FULL may be the only workable alternative. ... (but) the usual goal of routine vacuuming is to avoid needing VACUUM FULL. The autovacuum daemon attempts to do standard VACUUMs often enough to maintain steady-state usage of disk space ..."

I will follow @chris-travers advice and map out the number of rows in tables from each version of the database. I think in my case it'd be fair to say that VACUUM FULL will relieve the pressure on disk space, and it'll make original_vs_restored look better but there's still this red flag of excessive bloat. I think autovacuum isn't doing anything and that's a worry! Thx for the guidance so far, it's fascinating.

What version of PostgreSQL are you using? If it is prior to 9.0 (iirc), you may have inadequate free space map settings and thus be leaking space that vacuum can't recover efficiently. In this case, the sort of bloat you are seeing would be affecting the production db.

At the same time, this certainly does raise red flags. The question is what those red flags mean. is it a badly configured server? Is it a corrupted back-up? To test, what you should do is map out the number of rows in the tables on the production server and the backup. If they are close (remember the production server is likely having new updates and deletes) then you are probably pretty good. If there are wide deviations, you may want to look into those more carefully. Assuming nothing comes up there, I would suspect a problem with the free space map settings.

[Addendum] in the tradition of RTFM I have gone hunting in the manual for PostrgeSQL. Here are some more pieces to the puzzle

#is the autovacuum switched on?
postgres@db1:/tmp$ psql -c "SHOW autovacuum;"
 autovacuum
------------
 on
(1 row)

#The "track counts" parameter is needed by autovacuum which uses statistics from database activity to know where to do its job
postgres@db1:/tmp$ psql -c "SHOW track_counts;"
 track_counts
--------------
 on
(1 row)

#is there an autovacuum daemon resident in memory?
postgres@db1:/tmp$ ps  -ef | grep 'autovacuum'
postgres  1261  1021  0 Jan23 ?        00:08:27 postgres: autovacuum launcher process                             
postgres 18347 18149  0 00:33 pts/0    00:00:00 grep autovacuum


#what's been happening on the live server?
postgres@LIVEdb1:/tmp$ psql -c "SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_all_tables;"
#result is list of 65 tables (out about 300), all empty (no dates at all)

Gratefully following up on @craig-ringer's advice to look into VACUUM FULL I turned to PostgreSQL documentation, (paraphrased) "... In the worst case where your disk is nearly full, VACUUM FULL may be the only workable alternative. ... (but) the usual goal of routine vacuuming is to avoid needing VACUUM FULL. The autovacuum daemon attempts to do standard VACUUMs often enough to maintain steady-state usage of disk space ..."

I will follow @chris-travers advice and map out the number of rows in tables from each version of the database. I think in my case it'd be fair to say that VACUUM FULL will relieve the pressure on disk space, and it'll make original_vs_restored look better but there's still this red flag of excessive bloat. I think autovacuum isn't doing anything and that's a worry! Thx for the guidance so far, it's fascinating.

What version of PostgreSQL are you using? If it is prior to 9.0 (iirc), you may have inadequate free space map settings and thus be leaking space that vacuum can't recover efficiently. In this case, the sort of bloat you are seeing would be affecting the production db.

At the same time, this certainly does raise red flags. The question is what those red flags mean. is it a badly configured server? Is it a corrupted back-up? To test, what you should do is map out the number of rows in the tables on the production server and the backup. If they are close (remember the production server is likely having new updates and deletes) then you are probably pretty good. If there are wide deviations, you may want to look into those more carefully. Assuming nothing comes up there, I would suspect a problem with the free space map settings.

2 clarification of the matter, more info, following sage advice from @craig-ringer and @chris-travers
source | link

What version of PostgreSQL are you using? If it is prior to 9.0 (iirc), you may have inadequate free space map settings and thus be leaking space that vacuum can't recover efficiently. In this case, the sort of bloat you are seeing would be affecting the production db.

At the same time, this certainly does raise red flags. The question is what those red flags mean. is it a badly configured server? Is it a corrupted back-up? To test, what you should do is map out the number of rows in the tables on the production server and the backup. If they are close (remember the production server is likely having new updates and deletes) then you are probably pretty good. If there are wide deviations, you may want to look into those more carefully. Assuming nothing comes up there, I would suspect a problem with the free space map settings.

[Addendum] in the tradition of RTFM I have gone hunting in the manual for PostrgeSQL. Here are some more pieces to the puzzle

#is the autovacuum switched on?
postgres@db1:/tmp$ psql -c "SHOW autovacuum;"
 autovacuum
------------
 on
(1 row)

#The "track counts" parameter is needed by autovacuum which uses statistics from database activity to know where to do its job
postgres@db1:/tmp$ psql -c "SHOW track_counts;"
 track_counts
--------------
 on
(1 row)

#is there an autovacuum daemon resident in memory?
postgres@db1:/tmp$ ps  -ef | grep 'autovacuum'
postgres  1261  1021  0 Jan23 ?        00:08:27 postgres: autovacuum launcher process                             
postgres 18347 18149  0 00:33 pts/0    00:00:00 grep autovacuum


#what's been happening on the live server?
postgres@LIVEdb1:/tmp$ psql -c "SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_all_tables;"
#result is list of 65 tables (out about 300), all empty (no dates at all)

Gratefully following up on @craig-ringer's advice to look into VACUUM FULL I turned to PostgreSQL documentation, (paraphrased) "... In the worst case where your disk is nearly full, VACUUM FULL may be the only workable alternative. ... (but) the usual goal of routine vacuuming is to avoid needing VACUUM FULL. The autovacuum daemon attempts to do standard VACUUMs often enough to maintain steady-state usage of disk space ..."

I will follow @chris-travers advice and map out the number of rows in tables from each version of the database. I think in my case it'd be fair to say that VACUUM FULL will relieve the pressure on disk space, and it'll make original_vs_restored look better but there's still this red flag of excessive bloat. I think autovacuum isn't doing anything and that's a worry! Thx for the guidance so far, it's fascinating.

What version of PostgreSQL are you using? If it is prior to 9.0 (iirc), you may have inadequate free space map settings and thus be leaking space that vacuum can't recover efficiently. In this case, the sort of bloat you are seeing would be affecting the production db.

At the same time, this certainly does raise red flags. The question is what those red flags mean. is it a badly configured server? Is it a corrupted back-up? To test, what you should do is map out the number of rows in the tables on the production server and the backup. If they are close (remember the production server is likely having new updates and deletes) then you are probably pretty good. If there are wide deviations, you may want to look into those more carefully. Assuming nothing comes up there, I would suspect a problem with the free space map settings.

What version of PostgreSQL are you using? If it is prior to 9.0 (iirc), you may have inadequate free space map settings and thus be leaking space that vacuum can't recover efficiently. In this case, the sort of bloat you are seeing would be affecting the production db.

At the same time, this certainly does raise red flags. The question is what those red flags mean. is it a badly configured server? Is it a corrupted back-up? To test, what you should do is map out the number of rows in the tables on the production server and the backup. If they are close (remember the production server is likely having new updates and deletes) then you are probably pretty good. If there are wide deviations, you may want to look into those more carefully. Assuming nothing comes up there, I would suspect a problem with the free space map settings.

[Addendum] in the tradition of RTFM I have gone hunting in the manual for PostrgeSQL. Here are some more pieces to the puzzle

#is the autovacuum switched on?
postgres@db1:/tmp$ psql -c "SHOW autovacuum;"
 autovacuum
------------
 on
(1 row)

#The "track counts" parameter is needed by autovacuum which uses statistics from database activity to know where to do its job
postgres@db1:/tmp$ psql -c "SHOW track_counts;"
 track_counts
--------------
 on
(1 row)

#is there an autovacuum daemon resident in memory?
postgres@db1:/tmp$ ps  -ef | grep 'autovacuum'
postgres  1261  1021  0 Jan23 ?        00:08:27 postgres: autovacuum launcher process                             
postgres 18347 18149  0 00:33 pts/0    00:00:00 grep autovacuum


#what's been happening on the live server?
postgres@LIVEdb1:/tmp$ psql -c "SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_all_tables;"
#result is list of 65 tables (out about 300), all empty (no dates at all)

Gratefully following up on @craig-ringer's advice to look into VACUUM FULL I turned to PostgreSQL documentation, (paraphrased) "... In the worst case where your disk is nearly full, VACUUM FULL may be the only workable alternative. ... (but) the usual goal of routine vacuuming is to avoid needing VACUUM FULL. The autovacuum daemon attempts to do standard VACUUMs often enough to maintain steady-state usage of disk space ..."

I will follow @chris-travers advice and map out the number of rows in tables from each version of the database. I think in my case it'd be fair to say that VACUUM FULL will relieve the pressure on disk space, and it'll make original_vs_restored look better but there's still this red flag of excessive bloat. I think autovacuum isn't doing anything and that's a worry! Thx for the guidance so far, it's fascinating.

    Post Migrated Here from stackoverflow.com
1
source | link

What version of PostgreSQL are you using? If it is prior to 9.0 (iirc), you may have inadequate free space map settings and thus be leaking space that vacuum can't recover efficiently. In this case, the sort of bloat you are seeing would be affecting the production db.

At the same time, this certainly does raise red flags. The question is what those red flags mean. is it a badly configured server? Is it a corrupted back-up? To test, what you should do is map out the number of rows in the tables on the production server and the backup. If they are close (remember the production server is likely having new updates and deletes) then you are probably pretty good. If there are wide deviations, you may want to look into those more carefully. Assuming nothing comes up there, I would suspect a problem with the free space map settings.