Unlocking Hidden Storage Insights with PostgreSQL’s pg_freespacemap

PostgreSQL is protected for its powerful storage engine and reliability. But like a database system, it's sensitive to bloat — wasted storage space that slowly chips away at performance. Even with VACUUM and autovacuum, inefficient space reuse can creep in unnoticed.

What if you close peer directly into PostgreSQL's internal page document to detect and understand these hidden inefficiencies?

That's exactly what the pg_freespacemap extension lets you do. By exposing the contents of PostgreSQL's Free Space Map (FSM), you gain a behind-the-scenes view of how much unused space exists on each page of a table or index, empowering you to act with surgical precision.

In this Post, you'll Learn:

* What the Free Space Map (FSM) is

*Why pg_freespacemap is crucial for tuning and diagnostics 

*How to install and use it effectively 

*How to visualize and interpret space usage

*How to correlate with real usage using pgstattuple 

*Real-world applications and practical reclaim strategies 

What Is the Free Space Map?

FSM — A Hidden Performance Lever

The Free Space Map is an internal data structure PostgreSQL uses to track the amount of free space available on each 8KB page in a relation (i.e., a table or index). This information helps the storage engine:

*Officially locate pages with enough room for new rows 

*Avoid scanning all pages during inserts or updates. 

However, FSM data is not exposed by default — it's managed behind the scenes.

Why You Should Care About FSM

As rows are updated or deleted, Old tuple versions become dead. PostgreSQL uses MVCC (Multi-Version Concurrency Control) and doesn't immediately reclaim that space.

This can lead to:

*Pages with only a small portion used 

*FSM not being updated (e.g., if autovacuum is delayed) 

*Inserts bypassing reusable space 

*Disk bloat and slower queries 

In short, you might be wasting storage and hurting performance, without knowing it.

Installing pg_freespacemap

Most PostgreSQL installations already include this extraction.

CREATE EXTENSION IF NOT EXISTS pg_freespacemap;

Using pg_freespacemap to Inspect Tables

Once enabled, you can query any table's free space map like this:

SELECT * FROM pg_freespace('orders');

OUTPUT:

Fields:

*blkno: Logical page number 

*Avail: Free space on that page (bytes) 

Pages with high avail may be underutilized — potential signs of bloat or flagmentation.

Visualizing Free Space Distribution

To analyze patterns and fragmentation levels, bucket the space into ranges:

SELECT through_bucket(avail, 0, 8000, 8) AS bucket,
       COUNT(*) AS pages
FROM pg_freespace("orders")
GROUP BY bucket
ORDER BY bucket;

This group's pages into 1KB ranges:

Interpretation:

A spike in bucket 8? You may have heavily bloated pages.

Cross-checking with pgstatuple

While pg_freespacemap shows what the FSM thinks, it doesn't reflect actual table contents.

To get a precise breakdown of live/dead tuples and bloat, use the pgstattuple extension:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');

Sample output:

Combine both tools:

*pg_freespacemap: Where is the free space? 

*pgstattuple: Why is it there? 

Real-World Use Case: Diagnosing Slow Queries

Scenario

A logistics company reports a slowdown in their shipments table despite indexes being in place.

Step 1: Check Page Free Space

SELECT COUNT(*) 
FROM pg_freespace('shipments') 
WHERE avail > 4000;

Step 2: Analyze Tuple Statistics

SELECT * FROM pgstattuple('shipments');

*dad_tuple_count: 1.8 million 

*Free_space: 35% 

*Approx_bloat: 33% 

Step 3: Repack with Zero Downtime

pg_repack -t shipments -d mydb

?? Reclaiming Free Space: 3 Options

Commands:

VACUUM FULL oorders;
CLUSTER oders USING oders_pkey;
-- or
pg_repack -t orderers -d mydb

Conclusion

PostgreSQL is excellent at managing storage automatically, but not perfectly. As tables evolve with inserts, updates, and deletes, internal fragmentation creeps in, often invisible until performance suffers.

The pg_freespacemap extension allows you to tap into PostgreSQL's internal FSM and spot hidden inefficiencies. When fed with pgstatuple, you gain a holistic, page-level, and tuple-level view of the actual storage utilization.

Don't wait for disk space to max out or queries to crawl. USE these tools to:

*Diagnose slowdowns 

*Plan reclaim strategies 

*Build smarter, stage-aware dashboards. 

Discover how to use Generated Columns in PostgreSQL to automatically compute Column values based on expressions, simplifying your database design and ensuring data consistency.