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:
- 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. - 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.