PostgreSQL's Free Space Map: Finding Room to Write
A Free Space Map is a table of contents for empty space in a Postgres table. It lets Postgres quickly find a page with enough room for a new row or index entry, avoiding a slow scan. It's used on every INSERT/UPDATE.
Why it exists
Without a map, finding a page with enough free space for a new row would require scanning a table page-by-page. For large tables, this would make INSERT and UPDATE operations prohibitively slow. The Free Space Map (FSM) provides a fast, specialized index to solve this problem.
The mental model
Think of the FSM as a cheat sheet for free space. It's a separate data structure, a tree, that summarizes the available room in the main table file. Instead of reading every page of the table to find space, Postgres consults this much smaller map to get a direct pointer to a suitable page.
How it works
Each table or index has a corresponding FSM file (e.g., a table file named '12345' has an FSM file '12345_fsm'). The FSM itself is a tree of pages. The bottom-level pages use one byte to record the amount of free space on each corresponding data page. Upper-level FSM pages aggregate this information by storing the maximum free space value found in the level below them. This hierarchy allows Postgres to quickly traverse the tree from the root down to find a data page with enough space for a new tuple, without scanning the actual table data.
When to use it
You don't choose to use it; PostgreSQL uses it automatically for nearly all tables and indexes (except hash indexes). It is fundamental to how Postgres manages INSERT and UPDATE operations, ensuring new data and new row versions are placed efficiently.
When not to use it
This is an internal mechanism you cannot disable. The only common structure that doesn't use it is a hash index. Your interaction with the FSM is indirect, primarily through tuning VACUUM operations, which are responsible for keeping the FSM up-to-date.
One canonical example
You run an INSERT that needs 500 bytes. Postgres consults the FSM. The root of the FSM tree might indicate that the page with the most free space in the entire table has 2KB available. Postgres follows the path down the tree to the leaf node representing that specific data page. It then writes the new row to that page. If VACUUM hasn't run recently, the FSM might be inaccurate and point to a page that is now full, forcing Postgres to continue searching and slowing the insert.
Interview question
Which problem does PostgreSQL's Free Space Map (FSM) primarily address to maintain database performance?
- a.Minimizing disk I/O during full table scans by pre-caching frequently accessed data blocks.
- b.Efficiently locating data pages with sufficient capacity for new row insertions or updates.Correct
- c.Speeding up SELECT queries by providing a direct lookup for row data based on primary keys.
- d.Ensuring data integrity by preventing concurrent transactions from overwriting each other's changes.
Why? this is the answer
The FSM's core function is to quickly find a data page with enough free space for new rows or updated row versions, avoiding prohibitively slow page-by-page scans during INSERT and UPDATE operations. Option C describes the role of a standard index for existing data, not the FSM's purpose for free space.
Just read this? Test yourself on what you have been reading.
Read the original → postgresql.org
- #postgresql
- #databases
- #storage engine
- #performance
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles