1

VACUUM does not return disk space to the OS, instead it marks dead rows to be reused for future writes. My server has almost ran out of disk space, afterwards I ran DELETE FROM big_table to clear about 30% of rows, followed by a manual VACUUM.

No space was given back to the OS, which is fine. How many rows can I still insert before running out of space?

df -h is a useless metric for obvious reasons. Is there a query to see postgres' used disk space vs available to postgres' disk space?

3

The two extensions pgstattuple and pg_freespacemap might be helpful.

pg_freespacemap is faster, and gives a view of free-space which matches how PostgreSQL itself hunts for freespace when it is doing a tuple insertion.

SELECT sum(avail) FROM pg_freespace('foo');

But it doesn't give you view of the average tuple length, which would be needed to convert space into tuples (on the assumption new tuples will be the same length as existing ones). pgstattuples gives you info to compute the current average length.

Neither one of them accounts for the possibility that your new rows will be an awkward size which does not fit into the holes currently available.

Also, neither works very well for indexes. New entries have to go into an index where they belong, even if some other page has plenty of free space in it. So the reusability of index space depends on how the new indexed values relate to the deleted away values.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.