2

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:

free space graph

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.