1

For tables, I can simply use pg_freespace to check available spaces like below:

postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename');
  count  |        ?column?        
---------+------------------------
 1978140 | 1.00397105361602313000

But for indexes, it does not work.

postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename_pkey');
  count  |          ?column?          
---------+----------------------------
 1224867 | 0.000000000000000000000000

Additionally, this is the result of same queries after VACUUM FULL:

postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename');
  count  |          ?column?          
---------+----------------------------
 1833413 | 0.000000000000000000000000
postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename_pkey');
 count  |          ?column?          
--------+----------------------------
 836994 | 0.000000000000000000000000
2

Albeit not very prominently, the documentation of pg_freespacemap mentions the following:

For indexes, what is tracked is entirely-unused pages, rather than free space within pages. Therefore, the values are not meaningful, just whether a page is full or empty.

Your query results simply mean that you don't have a single fully free index page.

  • Thanks,but is there any extension for indexes available space check? – Bill Greens Apr 10 at 3:18
  • No, I am not aware of any. Why do you need it? There might be something else that fits your use case. – dezso Apr 10 at 7:31

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.