The free space map and the visibility map

The previous lesson looked inside a page. This one looks at the two small forks that summarise every page in the table, because between them they explain two of the most common production questions about PostgreSQL: why does this table keep growing when the row count is flat, and why did this query get eleven times more expensive after a deployment.

Neither fork holds any of your data. Both are maintained by vacuum. Both are why vacuum is not housekeeping you can defer.

The free space map

The free space map records, for every page in the main fork, roughly how much free space that page has. It is deliberately approximate — it stores a coarse quantity per page rather than an exact byte count, which is why the whole map fits in a few kilobytes for a table of any size.

When a row is inserted, PostgreSQL asks the free space map for a page with room. If one exists, the row goes there. If none does, the relation is extended by a new page at the end of the file.

That is the entire mechanism, and it produces the single most important consequence in this lesson: a table only reuses space that the free space map knows about.

DELETE does not update the free space map

This is the part that surprises people. Deleting rows makes space available in principle, but nothing writes that fact down.

$ psql -U postgres -c "SELECT avg(avail)::int AS avg_free_bytes, count(*) AS pages FROM pg_freespace('vm_demo')"

-- table freshly loaded, 50000 rows, 11 MB
avg_free_bytes | pages
----------------+-------
          130 |  1471

-- after DELETE FROM vm_demo WHERE id % 2 = 0;  (DELETE 25000)
avg_free_bytes | pages
----------------+-------
          130 |  1471

Twenty-five thousand rows are dead, and the free space map still reports 130 bytes free per page. The space is there on disk. Nothing can use it.

Run vacuum and the same query reports something entirely different:

$ psql -U postgres -c "VACUUM vm_demo" -c "SELECT avg(avail)::int AS avg_free_bytes, max(avail) AS max_free FROM pg_freespace('vm_demo')"

VACUUM
avg_free_bytes | max_free
----------------+----------
         4065 |     5760

Two separate facts live in that capture, and conflating them is a common source of confusion:

• The file did not shrink. It was 11 MB before the vacuum and 11 MB after. Vacuum does not return space to the filesystem except in the narrow case where the free space is a contiguous run at the very end of the relation.
• The space became reusable. Roughly 4 kB per page is now advertised as available.

What that means for a table under churn

The clearest way to see the free space map’s job is to remove it from the picture. The two tables below received an identical workload: load 50,000 rows, then twice delete half of them and insert the same number back. The only difference is that one was vacuumed between cycles and the other had autovacuum disabled.

$ psql -U postgres -c "SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size, (SELECT count(*) FROM vm_demo) AS live_rows FROM pg_class WHERE relname IN ('vm_demo','fsm_control')"

-- vacuumed table, after two delete-and-reinsert cycles
size_after_reinsert | rows
---------------------+-------
11 MB               | 50000

-- control table, autovacuum off, no manual VACUUM
after_load                  11 MB
after_delete_and_reinsert   17 MB
after_second_cycle          23 MB     (live_rows = 50000)

Same 50,000 live rows. 11 MB against 23 MB, and the gap grows by another 6 MB on every cycle. Growth is linear in the number of churn cycles, not asymptotic — there is no size at which an unvacuumed table settles.

That is the whole of what “bloat” means. It is not a mysterious condition a database contracts. It is space that was never advertised as reusable, so it was never reused.

The visibility map

The visibility map holds two bits per page. For a 1,471-page table it is one 8 kB page.

The two bits are related but not the same. A page can be all-visible without being all-frozen: nothing on it is in flux, but the transaction ids that wrote it are still recent enough that they have not been marked permanently old.

The map does not exist until vacuum builds it

$ psql -U postgres -c "SELECT pg_size_pretty(pg_relation_size('vm_demo','main')) AS main, pg_size_pretty(pg_relation_size('vm_demo','fsm')) AS fsm, pg_size_pretty(pg_relation_size('vm_demo','vm')) AS vm"

 main  |  fsm  |   vm
-------+-------+---------
11 MB | 24 kB | 0 bytes

Zero bytes. A freshly loaded table has no visibility map at all, which is the mechanical reason behind a piece of advice you will see everywhere and may have followed without knowing why: run VACUUM ANALYZE after a bulk load. ANALYZE gives the planner statistics; VACUUM gives it a visibility map. The second half matters as much as the first.

What the map is worth

The same query, against the same 50,000 rows, before and after a single vacuum:

$ psql -U postgres -c "EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(id) FROM vm_demo WHERE id BETWEEN 1 AND 20000"

 Aggregate (actual rows=1.00 loops=1)
 Buffers: shared hit=645
 ->  Bitmap Heap Scan on vm_demo (actual rows=20000.00 loops=1)
       Recheck Cond: ((id >= 1) AND (id <= 20000))
       Heap Blocks: exact=589
       Buffers: shared hit=645
       ->  Bitmap Index Scan on vm_demo_pkey (actual rows=20000.00 loops=1)
             Index Cond: ((id >= 1) AND (id <= 20000))
             Buffers: shared hit=56

$ psql -U postgres -c "VACUUM (ANALYZE) vm_demo" -c "EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(id) FROM vm_demo WHERE id BETWEEN 1 AND 20000"

 Aggregate (actual rows=1.00 loops=1)
 Buffers: shared hit=57
 ->  Index Only Scan using vm_demo_pkey on vm_demo (actual rows=20000.00 loops=1)
       Index Cond: ((id >= 1) AND (id <= 20000))
       Heap Fetches: 0
       Buffers: shared hit=57

645 buffers to 57. Eleven times less work, from 8 kB of visibility map. Before the vacuum the planner did not choose an index-only scan at all, and that was the right decision: with an empty visibility map an index-only scan has to visit the heap for every row, so it is not index-only in any useful sense.

Heap Fetches: 0 is the line to read. It is a direct report on the visibility map: it counts the rows for which the index could not be trusted alone.

The map has page granularity, not row granularity

This is the property that turns a small change into a large regression.

$ psql -U postgres -c "UPDATE vm_demo SET payload = 'changed' WHERE id = 1" -c "SELECT count(*) FILTER (WHERE all_visible) AS all_visible, count(*) AS pages FROM pg_visibility_map('vm_demo')" -c "EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(id) FROM vm_demo WHERE id BETWEEN 1 AND 20000"

UPDATE 1

all_visible | pages
-------------+-------
      1470 |  1471

Aggregate (actual rows=1.00 loops=1)
 ->  Index Only Scan using vm_demo_pkey on vm_demo (actual rows=20000.00 loops=1)
       Index Cond: ((id >= 1) AND (id <= 20000))
       Heap Fetches: 34

One row changed. Thirty-four heap fetches. The reason is arithmetic rather than anything subtle:

$ psql -U postgres -c "SELECT count(*) AS rows_on_page_zero FROM vm_demo WHERE (ctid::text::point)[0] = 0"

 rows_on_page_zero
-------------------
              34

Clearing one page’s bit costs a heap fetch for every row on that page, not for the one row that changed. Scale that up: a table where updates are scattered uniformly across pages loses its visibility map almost entirely for the cost of touching a small fraction of its rows. A table where updates cluster onto a few pages keeps most of it.

Freezing, briefly

all_frozen earns a full treatment in Part VIII alongside transaction id wraparound. What matters here is the storage-level fact: a plain VACUUM sets all_visible but generally does not set all_frozen for recently written rows, because freezing has its own age thresholds.

$ psql -U postgres -c "SELECT count(*) FILTER (WHERE all_visible) AS all_visible, count(*) FILTER (WHERE all_frozen) AS all_frozen, count(*) AS pages FROM pg_visibility_map('vm_demo')"

-- after VACUUM (ANALYZE)
all_visible | all_frozen | pages
-------------+------------+-------
      1471 |          0 |  1471

-- after VACUUM (FREEZE)
all_visible | all_frozen | pages
-------------+------------+-------
      1471 |       1471 |  1471

The transaction ids on this table were far younger than vacuum_freeze_min_age, which is 50,000,000 by default, so the plain vacuum left them alone. That is normal and correct; freezing has a cost and there is no reason to pay it early.

The operational significance is that all_frozen is what lets an aggressive vacuum skip pages. A large table that has never been frozen must be read in full when wraparound protection eventually forces the issue, which is why a table that has been quietly accumulating for months can produce a sudden, long, unexpected vacuum.

What to take from this

• The free space map is the reason vacuum is mandatory rather than optional. Without it a table under churn grows without bound, at a rate proportional to the churn.
• Vacuum making space reusable and vacuum returning space to the filesystem are different things. Routine vacuum does the first. Only a rewrite does the second, and it takes a lock you do not want to take casually.
• The visibility map is why VACUUM ANALYZE after a bulk load is worth the time, and why Heap Fetches is worth watching.
• Both bits are per page. A workload that scatters writes thinly across many pages costs far more visibility map than its row count suggests.

Cross-course references

• Linux for Production Sysadmins — Part XLI (Storage Performance) covers measuring the read amplification an unset visibility map causes, and Part XIV (Filesystems) covers the sparse-file behaviour that makes a fork’s on-disk size misleading.
• Observability for Production Sysadmins — Part LIX (Database observability) covers exporting the index-only-scan ratio these maps determine.

Quiz