We've got a IP address range dataset stored in a table that was truncated and then re-populated with a slightly modified version of the dataset (almost identical in size).
However, after truncating the table and re-populating there is a missing approx. 1.5GB of free space even after running VACUUM ANALYZE
See graph of free space for the PostgreSQL Amazon RDS instance:
Output from VACUUM ANALYZE:
INFO: vacuuming "global.ipranges"
INFO: index "ipranges_pkey" now contains 9357978 row versions in 69803 pages
DETAIL: 0 index row versions were removed.
22995 index pages have been deleted, 22995 are currently reusable.
CPU 0.92s/0.18u sec elapsed 10.36 sec.
INFO: index "ip2location_range_gist" now contains 14036924 row versions in 113046 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.34s/0.37u sec elapsed 13.81 sec.
INFO: "ipranges": found 0 removable, 9357978 nonremovable row versions in 162161 out of 324319 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 104 unused item pointers.
0 pages are entirely empty.
CPU 11.29s/14.38u sec elapsed 290.64 sec.
INFO: vacuuming "pg_toast.pg_toast_16398"
INFO: index "pg_toast_16398_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_16398": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "global.ipranges"
INFO: "ipranges": scanned 30000 of 324319 pages, containing 865906 live rows and 0 dead rows; 30000 rows in sample, 13604393 estimated total rows
Some additional strange things are that the statistics for the table show both a table size and index size almost double what is expected. It also estimates 13604393 rows but a SELECT COUNT(*) returns the correct 9357978
How can we recover this 1.5GB of space?
