Proper Postgresql vacuuming practices Some tables are very dynamic in terms of additions and subtraction of records.  The application (rails 4.2) deletes a large number of records daily.  However, the size of the database to the OS - or dumped via pg_dump does not seem to go on a diet.

As a test, all records from three related tables were deleted by the application. However, when  running \d+ one table has 42MB size.

So then a manual exploration.

durc_production=# VACUUM FULL commonpassaggios;
durc_production=# VACUUM FULL posiziones;
durc_production=# VACUUM FULL passaggios;


run.  The relative times they take are coherent with the table and index sizes. But then \d+ returns the same data sizes.

I need to get the vacuum demonstrably running before configuring postgresql.conf...

Update  This question ends up as a red herring.  Craig's reply allowed to pick up on the problem.  it was not related to postgresql at all.  see subsequent comments for culprit identification.
Database size as measured by

select pg_size_pretty(pg_database_size(current_database()))


or using du at the file system level 
won't usually go down when you delete records. Even after VACUUM has had time to run. Instead, when you next add new records it won't go up because the free space inside the database gets re-used.

VACUUM can sometimes shrink a table and free space to the operating system, but only if there aren't any still-needed rows near the end of the table. Just one still-needed row can prevent the compaction of a huge table. There is no automatic background defragmentation performed to stop that happening.

You can VACUUM FULL to force space to be released back to the OS. This can speed up SELECTs considerably, too. However, it will make subsequent INSERTs and UPDATEs considerably slower since they need to claim new space back from the OS again. You can mitigate that by ALTERing the FILLFACTOR on your tables and indexes to be less than 100 (percent), so there's free space within the table to re-use immediately. Also, VACUUM FULL locks the table completely while it's running, which you really don't want on a running production DB.



Now, the size of a dump is different. That does go down when you delete data. If it doesn't go down, then you didn't delete anything, you added as much data as you deleted, or the data you removed is so small that it doesn't make a significant difference to the dump size.



If you delete records then do a new dump and it doesn't change size, I'd say that:


You didn't COMMIT the delete, so it hasn't taken effect;
You added about as much data as you deleted;
You deleted it from a different copy of the database to the one you dumped


You can check all of these with things like select count(*) from table_of_interest and select pg_size_pretty(pg_total_relation_size('table_of_interest')).