Questions about Free Space Map of PostgreSQL

Question

Let's create a temporary table (I choose temporary table because autovacuum don't run for this kind of tables):

CREATE TEMP TABLE test (
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  value int
);
INSERT INTO test (value) SELECT 0 FROM generate_series(0, 250);
SELECT ctid, * FROM test;

We will see that the table consists of two pages:

(0,1)   1   0
(0,2)   2   0
...
(0,226) 226 0
(1,1)   227 0
...
(1,25)  251 0

Now if we update one row

UPDATE test SET value = -1 WHERE id = 1;
SELECT ctid, * FROM test WHERE value <> 0;

we will see that the new row version was inserted at the end of the table (in the second page, which has enough free space for this operation) and this is a standard PostgreSQL behavior (the old row verison from (0,1) was marked as dead)

(1,26)  1   -1

Let's check the free space of pages:

CREATE EXTENSION pg_freespacemap;
SELECT * FROM pg_freespace('test');

we get

0   0
1   0

i.e. no free space; documentations says that the Free Space Map (FSM) is updated after vacuum run. Now if we update another row from the first page:

UPDATE test SET value = -1 WHERE id = 2;
SELECT ctid, * FROM test WHERE value <> 0;

we will see

(0,227) 2   -1
(1,26)  1   -1

The new row version was not added to the 2nd page but was added to the current page, because we made free space when we updated the record with id 1. But this approach does not correspond to my understanding of FSM. Here are my questions:

1. How PostgreSQL knew at the second UPDATE that there was free space in the first page if pg_freespace('test') returned zero? I thought that this function gives you the FSM which in turn is used in case of UPDATEs in order to decide which page has enough free space to store the new row version.
2. Generally I was thinking that even if first row was marked as dead it however occupies some space in the 1st page and this space will be freed only after vacuum. Thus I was expecting that the second UPDATE will also add the new row version to the 2nd page.

Answer 1

This will contain some PostgreSQL internals; sorry, you asked for it.

Autovacuum doesn't run on temporary tables (because they are invisible outside your session), and at any rate, one or two updates won't trigger autovacuum. Now it is VACUUM that creates and maintains the free space map, so there is no free space map in your case. pg_freespacemap does not error out, but reports that as 0.

Lacking a free space map, PostgreSQL has to look at the individual blocks to find a block with free space in it. That leaves the riddle why the second update could find space to create a tuple in block 0.

To answer the riddle, let's discuss your statements and their effect. You can verify all this using the pageinspect extension.

• the first UPDATE:

  UPDATE test SET value = -1 WHERE id = 1;
  

  That marks tuple (0,1) as updated (it sets xmax) and creates the new tuple (1,26), just as you expected.

• the following query:

  SELECT ctid, * FROM test WHERE value <> 0;
  

  This query can get a page lock on block 0 and performs heap pruning, that is a “micro-vacuum” that frees the space occupied by dead tuples. Note that it cannot remove (0,1), because it is still referenced in an index, but line pointer (0,1) becomes a “stub” marked LP_DEAD that does not occupy any space. The space occupied by that dead tuple is freed.

  Note that if you don't run that SELECT, the following UPDATE will perform heap pruning on block 0, so the effect will be the same.

• the second UPDATE:

  UPDATE test SET value = -1 WHERE id = 2;
  

  Lacking a free space map, the statement looks a block 0 and notices that there is enough free space, so it performs a HOT update and puts the new tuple into block 0.