This page in other versions: 9.0 / 9.1 / 9.2 / 9.3  |  Development versions: devel / 9.4  |  Unsupported versions: 8.3 / 8.4

F.21. pg_freespacemap

The pg_freespacemap module provides a means for examining the free space map (FSM). It provides two C functions: pg_freespacemap_relations and pg_freespacemap_pages that each return a set of records, plus two views pg_freespacemap_relations and pg_freespacemap_pages that wrap the functions for convenient use.

By default public access is revoked from the functions and views, just in case there are security issues lurking.

F.21.1. The pg_freespacemap views

The definitions of the columns exposed by the views are:

Table F-23. pg_freespacemap_relations Columns

Name Type References Description
reltablespace oid pg_tablespace.oid Tablespace OID of the relation
reldatabase oid pg_database.oid Database OID of the relation
relfilenode oid pg_class.relfilenode Relfilenode of the relation
avgrequest integer   Moving average of free space requests (NULL for indexes)
interestingpages integer   Count of pages last reported as containing useful free space
storedpages integer   Count of pages actually stored in free space map
nextpage integer   Page index (from 0) to start next search at

Table F-24. pg_freespacemap_pages Columns

Name Type References Description
reltablespace oid pg_tablespace.oid Tablespace OID of the relation
reldatabase oid pg_database.oid Database OID of the relation
relfilenode oid pg_class.relfilenode Relfilenode of the relation
relblocknumber bigint   Page number within the relation
bytes integer   Free bytes in the page, or NULL for an index page (see below)

For pg_freespacemap_relations, there is one row for each relation in the free space map. storedpages is the number of pages actually stored in the map, while interestingpages is the number of pages the last VACUUM thought had useful amounts of free space.

If storedpages is consistently less than interestingpages then it'd be a good idea to increase max_fsm_pages. Also, if the number of rows in pg_freespacemap_relations is close to max_fsm_relations, then you should consider increasing max_fsm_relations.

For pg_freespacemap_pages, there is one row for each page in the free space map. The number of rows for a relation will match the storedpages column in pg_freespacemap_relations.

For indexes, what is tracked is entirely-unused pages, rather than free space within pages. Therefore, the average request size and free bytes within a page are not meaningful, and are shown as NULL.

Because the map is shared by all the databases, there will normally be entries for relations not belonging to the current database. This means that there may not be matching join rows in pg_class for some rows, or that there could even be incorrect joins. If you are trying to join against pg_class, it's a good idea to restrict the join to rows having reldatabase equal to the current database's OID or zero.

When either of the views is accessed, internal free space map locks are taken for long enough to copy all the state data that the view will display. This ensures that the views produce a consistent set of results, while not blocking normal activity longer than necessary. Nonetheless there could be some impact on database performance if they are read often.

F.21.2. Sample output

regression=# SELECT c.relname, r.avgrequest, r.interestingpages, r.storedpages
             FROM pg_freespacemap_relations r INNER JOIN pg_class c
             ON r.relfilenode = c.relfilenode AND
                r.reldatabase IN (0, (SELECT oid FROM pg_database
                                      WHERE datname = current_database()))
             ORDER BY r.storedpages DESC LIMIT 10;
             relname             | avgrequest | interestingpages | storedpages
---------------------------------+------------+------------------+-------------
 onek                            |        256 |              109 |         109
 pg_attribute                    |        167 |               93 |          93
 pg_class                        |        191 |               49 |          49
 pg_attribute_relid_attnam_index |            |               48 |          48
 onek2                           |        256 |               37 |          37
 pg_depend                       |         95 |               26 |          26
 pg_type                         |        199 |               16 |          16
 pg_rewrite                      |       1011 |               13 |          13
 pg_class_relname_nsp_index      |            |               10 |          10
 pg_proc                         |        302 |                8 |           8
(10 rows)

regression=# SELECT c.relname, p.relblocknumber, p.bytes
             FROM pg_freespacemap_pages p INNER JOIN pg_class c
             ON p.relfilenode = c.relfilenode AND
                p.reldatabase IN (0, (SELECT oid FROM pg_database
                                      WHERE datname = current_database()))
             ORDER BY c.relname LIMIT 10;
   relname    | relblocknumber | bytes
--------------+----------------+-------
 a_star       |              0 |  8040
 abstime_tbl  |              0 |  7908
 aggtest      |              0 |  8008
 altinhoid    |              0 |  8128
 altstartwith |              0 |  8128
 arrtest      |              0 |  7172
 b_star       |              0 |  7976
 box_tbl      |              0 |  7912
 bt_f8_heap   |             54 |  7728
 bt_i4_heap   |             49 |  8008
(10 rows)
  
Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group