jeff95350 - 8:25 pm on Oct 10, 2005 (gmt 0)

Of course, the downside to this is now you have a tuple in the database that will eventually be doing nothing but taking up space. This is where vacuum comes in. It scans through the table and when it runs across a deleted tuple, it checks to see if any transactions currently running can still "see" that tuple. If none can, it will remove the tuple, reclaiming the disk space.

You are referring to VACUUM FULL. Regular VACUUM just marks the deleted tuple as "dead" and PostgreSQL will reuse that space the next time you INSERT. Regular VACUUM is quite unobtrusive, and you can throttle it back so it's a low priority. It does not prevent concurrent reading/writing to the tables while the VACUUM is running.

VACUUM FULL actually moves the tuples around, packing the table more tightly to save disk space. This is sometimes useful if you have not VACUUMed in a *long* time and there are so many dead tuples that they will never all be reused. VACUUM FULL is rarely necessary; even most 24/7 systems don't need to use it.