pg_freespacemap

The pg_freespacemap module provides a way to examine the free space map (FSM). It provides a function called pg_freespace, or more precisely two overloaded functions. These functions display the value recorded by the free space map for a given page, or display the recorded values for all pages of a relation.

By default, usage is restricted to superusers and members of the pg_stat_scan_tables role. Access can be granted to others using GRANT.

1. Functions

pg_freespace(rel regclass IN, blkno bigint IN) returns int2

Returns the total amount of free space on the page of the relation specified by blkno, according to the FSM.

pg_freespace(rel regclass IN, blkno OUT bigint, avail OUT int2)

Displays the total amount of free space on each page of the relation, according to the FSM. A set of (blkno bigint, avail int2) tuples is returned, one tuple for each page in the relation.

The values stored in the free space map are not exact. They are rounded to BLCKSZ/256 (32 bytes for the default BLCKSZ), and they are not updated in real time when tuples are inserted and updated.

For indexes, what is tracked is entire unused pages, not free space within pages. Therefore, these values may not be meaningful beyond indicating whether a page is full or empty.

2. Sample Output

test=## SELECT * FROM pg_freespace('foo');

blkno | avail

-------+-------

0 | 0

1 | 0

2 | 0

3 | 32

4 | 704

5 | 704

6 | 704

7 | 1216

8 | 704

9 | 704

10 | 704

11 | 704

12 | 704

13 | 704

14 | 704

15 | 704

16 | 704

17 | 704

18 | 704

19 | 3648

(20 rows)


text=## SELECT * FROM pg_freespace('foo', 7);

pg_freespace

--------------

1216

(1 row)