Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.