By Tobias Mühl

2019-10-31 10:39:18 8 Comments

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?


@jjanes 2019-10-31 12:19:37

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.

Related Questions

Sponsored Content

1 Answered Questions

1 Answered Questions

[SOLVED] VACUUM returning disk space to operating system

3 Answered Questions

[SOLVED] Postgres - how much space is required to perform a VACUUM

1 Answered Questions

[SOLVED] How to free postgres space after a DELETE without VACUUM

  • 2018-06-28 19:01:33
  • ConanTheGerbil
  • 990 View
  • 1 Score
  • 1 Answer
  • Tags:   postgresql

4 Answered Questions

1 Answered Questions

[SOLVED] Cleanup after deleting a large table

2 Answered Questions

2 Answered Questions

[SOLVED] how do you prevent dead rows from hanging around in postgresql?

  • 2015-07-13 23:35:23
  • patrick
  • 5967 View
  • 7 Score
  • 2 Answer
  • Tags:   postgresql

1 Answered Questions

[SOLVED] VACUUM FULL fails with 'ERROR: Check free disk space'

Sponsored Content