Hi,
Here is the situation. We are at postgres version 8.1.3.
I have a table that gets many rows inserted, updated and then deleted, consistently throughout the day. At any point in time this table should have no more than 50 actual rows and many times a direct select against this table produces no rows. This table also has a VACUUM FULL ANALYZE performed against it about very 30 minutes. I noticed the vacuum was taking a considerable amount of time for a table with a small number of actual rows. The output of the first vacuum full analyze verbose I performed showed that this table had 3,699,704 dead row versions that could not be removed. This number of dead rows that could not be released increased with each vacuum full that was performed. The output of the last vacuum full is shown below.
The only way I was able to get these dead row version removed was to perform a truncate on the table. I performed the truncate when the table was empty and there was no activity (insert, updates, delete or vacuums, etc) being performed against this table. After the truncate I performed another vacuum full analyze verbose. The vacuum was very fast and the output of the vacuum showed that there were no non-removable rows versions.
So my question is what makes a dead row nonremovable?
Miscellaneous info about table
All inserts, updates and deletes to this table are performed within functions that get called when a row is inserted into another table.
Below is the output of a “VACUUM FULL VERBOSE ANALYZE nc_persistent_host_temp;” before the truncate.
INFO: vacuuming "public.nc_persistent_host_temp"
INFO: "nc_persistent_host_temp": found 0 removable, 4599704 nonremovable row versions in 90171 pages
DETAIL: 4599704 dead row versions cannot be removed yet.
Nonremovable row versions range from 132 to 184 bytes long.
There were 95884 unused item pointers.
Total free space (including removable row versions) is 7140772 bytes.
61 pages are or will become empty, including 0 at the end of the table.
9166 pages containing 2002868 free bytes are potential move destinations.
CPU 21.07s/45.15u sec elapsed 71.27 sec.
INFO: "nc_persistent_host_temp": moved 0 row versions, truncated 90171 to 90171 pages
DETAIL: CPU 2.98s/2.20u sec elapsed 101.17 sec.
INFO: vacuuming "pg_toast.pg_toast_1036640"
INFO: "pg_toast_1036640": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_toast_1036640_index" now contains 0 row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.nc_persistent_host_temp"
INFO: "nc_persistent_host_temp": scanned 3000 of 90171 pages, containing 0 live rows and 152997 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM
Thanks,
Barbara Cosentino
