Postgres dead tuple space reused without vacuum
Tags: postgres, vacuum, bloat, space
Published: 2024-03-03 21:51:43

There are numerous talks and articles about PostgreSQL bloat and vacuum, but few of them provide a detailed explanation of how it works and why auto-vacuum must be more aggressive. In this post, I will demonstrate a simple test case to illustrate how dead tuples (old versions of rows) may be cleaned out before any vacuum (the PostgreSQL garbage collection) runs on them.

I will demonstrate an example using a temporary table where auto-vacuum is not applicable. You can perform the same on a permanent table with `autovacuum_enabled=off`. The goal is to run it manually to understand how it works before and after vacuuming. Below, I will show the syntax for both cases. I add an index and insert sixteen rows:
```sql
postgres=# select version();
                                                      version
-------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.2 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20.0.3), 64-bit
(1 row)

postgres=# create temporary table demo
           ( id bigint, value text )
           with ( autovacuum_enabled=off )
;
CREATE TABLE

postgres=# create unique index demo_pkey on demo (id)
           include (value)
;

postgres=# insert into demo
           select generate_series(0,15), lpad('x',500,'x')
;
INSERT 0 16
```
For the demonstration, I will use large rows and index entries to have only a few per block, which will be easier to display.

### PageInspect

I will examine the rows in pages, even those that are no longer active, using the PageInspect extension:
```sql
postgres=# create extension if not exists pageinspect;
CREATE EXTENSION
```

I can see the first 15 rows in the first block of my heap table:
```sql
postgres=# select t_ctid, lp, lp_flags, t_xmin, t_xmax, substr(t_data,1,30), lp_len
           from heap_page_items(get_raw_page('demo', 0))
;
  t_ctid | lp | lp_len | lp_flags | t_xmin | t_xmax |                                         substr
--------+----+--------+----------+--------+--------+----------------------------------------------------------------------------------------
 (0,1)  |  1 |    536 |        1 |   3231 |      0 | \x0000000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,2)  |  2 |    536 |        1 |   3231 |      0 | \x0100000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,3)  |  3 |    536 |        1 |   3231 |      0 | \x0200000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,4)  |  4 |    536 |        1 |   3231 |      0 | \x0300000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,5)  |  5 |    536 |        1 |   3231 |      0 | \x0400000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,6)  |  6 |    536 |        1 |   3231 |      0 | \x0500000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,7)  |  7 |    536 |        1 |   3231 |      0 | \x0600000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,8)  |  8 |    536 |        1 |   3231 |      0 | \x0700000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,9)  |  9 |    536 |        1 |   3231 |      0 | \x0800000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,10) | 10 |    536 |        1 |   3231 |      0 | \x0900000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,11) | 11 |    536 |        1 |   3231 |      0 | \x0a00000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,12) | 12 |    536 |        1 |   3231 |      0 | \x0b00000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,13) | 13 |    536 |        1 |   3231 |      0 | \x0c00000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,14) | 14 |    536 |        1 |   3231 |      0 | \x0d00000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,15) | 15 |    536 |        1 |   3231 |      0 | \x0e00000000000000e0070000787878787878787878787878787878787878787878787878787878787878
(15 rows)
```
The value 1 of `lp_flags` means "normal". We will see other values later.

I can see the first 14 index entries in the first leaf block of the B-Tree index:
```sql
postgres=# select substr(data,1,30), itemoffset, ctid, itemlen, nulls, vars, dead
           from bt_page_items(get_raw_page('demo_pkey', 1))
           order by data
;
                   substr                   | itemoffset |  htid  | itemlen | nulls | vars | dead
--------------------------------------------+------------+--------+---------+-------+------+------
 00 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          2 | (0,1)  |     520 | f     | t    | f
 01 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          3 | (0,2)  |     520 | f     | t    | f
 02 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          4 | (0,3)  |     520 | f     | t    | f
 03 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          5 | (0,4)  |     520 | f     | t    | f
 04 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          6 | (0,5)  |     520 | f     | t    | f
 05 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          7 | (0,6)  |     520 | f     | t    | f
 06 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          8 | (0,7)  |     520 | f     | t    | f
 07 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          9 | (0,8)  |     520 | f     | t    | f
 08 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         10 | (0,9)  |     520 | f     | t    | f
 09 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         11 | (0,10) |     520 | f     | t    | f
 0a 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         12 | (0,11) |     520 | f     | t    | f
 0b 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         13 | (0,12) |     520 | f     | t    | f
 0c 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         14 | (0,13) |     520 | f     | t    | f
 0d 00 00 00 00 00 00 00                    |          1 |        |      16 | f     | f    |
(14 rows)
```
The TID that identifies the tuple (table row version) has two numbers: the block number and the item number within the page. 

Here is the size on disk for the table and index:
```sql
postgres=# select relname,pg_table_size(oid),pg_size_pretty(pg_table_size(oid))
           from pg_class where relname like 'demo%'
;
  relname  | pg_table_size | pg_size_pretty
-----------+---------------+----------------
 demo      |         49152 | 48 kB
 demo_pkey |         32768 | 32 kB
(2 rows)
```

### Select

Without PageInspect, I can see the visible rows with their TID be we will see later that it is not a good idea to understand what happens step by step:
```sql
postgres=# select ctid, id, xmin, xmax, id, substr(value,1,30) , length(value)
           from demo
;
  ctid  | id | xmin | xmax | id |             substr             | length
--------+----+------+------+----+--------------------------------+--------
 (0,1)  |  0 | 3231 |    0 |  0 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,2)  |  1 | 3231 |    0 |  1 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,3)  |  2 | 3231 |    0 |  2 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,4)  |  3 | 3231 |    0 |  3 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,5)  |  4 | 3231 |    0 |  4 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,6)  |  5 | 3231 |    0 |  5 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,7)  |  6 | 3231 |    0 |  6 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,8)  |  7 | 3231 |    0 |  7 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,9)  |  8 | 3231 |    0 |  8 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,10) |  9 | 3231 |    0 |  9 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,11) | 10 | 3231 |    0 | 10 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,12) | 11 | 3231 |    0 | 11 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,13) | 12 | 3231 |    0 | 12 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,14) | 13 | 3231 |    0 | 13 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,15) | 14 | 3231 |    0 | 14 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (1,1)  | 15 | 3231 |    0 | 15 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
(16 rows)
```

If you check again with PageInspect you can see that nothing has changed after my select. But do not rely too much on that, reads, in a SQL database, can change the state, as we will see later.

### Update

An update or delete creates another version. I'm updating one row in the first block:
```sql
postgres=# update demo set value=upper(value) where id=2
;
UPDATE 1
```
The version in block 0 of the heap table is marked as deleted with its XMAX set to it's end of visibility:
```sql
postgres=# select t_ctid, lp, lp_flags, t_xmin, t_xmax, substr(t_data,1,30), lp_len
           from heap_page_items(get_raw_page('demo', 0))
;
 t_ctid | lp | lp_len | lp_flags | t_xmin | t_xmax |                                         substr
--------+----+--------+----------+--------+--------+----------------------------------------------------------------------------------------
 (0,1)  |  1 |    536 |        1 |   3231 |      0 | \x0000000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,2)  |  2 |    536 |        1 |   3231 |      0 | \x0100000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (1,2)  |  3 |    536 |        1 |   3231 |   3232 | \x0200000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,4)  |  4 |    536 |        1 |   3231 |      0 | \x0300000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,5)  |  5 |    536 |        1 |   3231 |      0 | \x0400000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,6)  |  6 |    536 |        1 |   3231 |      0 | \x0500000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,7)  |  7 |    536 |        1 |   3231 |      0 | \x0600000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,8)  |  8 |    536 |        1 |   3231 |      0 | \x0700000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,9)  |  9 |    536 |        1 |   3231 |      0 | \x0800000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,10) | 10 |    536 |        1 |   3231 |      0 | \x0900000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,11) | 11 |    536 |        1 |   3231 |      0 | \x0a00000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,12) | 12 |    536 |        1 |   3231 |      0 | \x0b00000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,13) | 13 |    536 |        1 |   3231 |      0 | \x0c00000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,14) | 14 |    536 |        1 |   3231 |      0 | \x0d00000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (0,15) | 15 |    536 |        1 |   3231 |      0 | \x0e00000000000000e0070000787878787878787878787878787878787878787878787878787878787878
(15 rows)
```
The new version has been appended in block 1 with its XMIN set to its start of visibility:
```sql
postgres=# select t_ctid, lp, lp_len, lp_flags, t_xmin, t_xmax, substr(t_data,1,42)
           from heap_page_items(get_raw_page('demo', 1))
;
 t_ctid | lp | lp_len | lp_flags | t_xmin | t_xmax |                                         substr
--------+----+--------+----------+--------+--------+----------------------------------------------------------------------------------------
 (1,1)  |  1 |    536 |        1 |   3231 |      0 | \x0f00000000000000e0070000787878787878787878787878787878787878787878787878787878787878
 (1,2)  |  2 |    536 |        1 |   3232 |      0 | \x0200000000000000e0070000585858585858585858585858585858585858585858585858585858585858
(2 rows)
```

In the B-Tree index, there is an entry for each version. The value `id=2` has an entry to `(1,2)` for the new version and another to `(0,2)` for the old version:
```sql
postgres=# select substr(data,1,42), itemoffset, htid, itemlen, nulls, vars, dead
           from bt_page_items(get_raw_page('demo_pkey', 1))
           order by data
;
                   substr                   | itemoffset |  htid  | itemlen | nulls | vars | dead
--------------------------------------------+------------+--------+---------+-------+------+------
 00 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          2 | (0,1)  |     520 | f     | t    | f
 01 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          3 | (0,2)  |     520 | f     | t    | f
 02 00 00 00 00 00 00 00 e0 07 00 00 58 58  |          5 | (1,2)  |     520 | f     | t    | f
 02 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          4 | (0,3)  |     520 | f     | t    | f
 03 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          6 | (0,4)  |     520 | f     | t    | f
 04 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          7 | (0,5)  |     520 | f     | t    | f
 05 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          8 | (0,6)  |     520 | f     | t    | f
 06 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          9 | (0,7)  |     520 | f     | t    | f
 07 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         10 | (0,8)  |     520 | f     | t    | f
 08 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         11 | (0,9)  |     520 | f     | t    | f
 09 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         12 | (0,10) |     520 | f     | t    | f
 0a 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         13 | (0,11) |     520 | f     | t    | f
 0b 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         14 | (0,12) |     520 | f     | t    | f
 0c 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         15 | (0,13) |     520 | f     | t    | f
 0d 00 00 00 00 00 00 00                    |          1 |        |      16 | f     | f    |
(15 rows)
```

The size of the table and index on disk has not changed because the blocks were already allocated:
```sql
postgres=# select relname,pg_table_size(oid),pg_size_pretty(pg_table_size(oid))
           from pg_class where relname like 'demo%'
;
  relname  | pg_table_size | pg_size_pretty
-----------+---------------+----------------
 demo      |         49152 | 48 kB
 demo_pkey |         32768 | 32 kB
(2 rows)
```

### Visibility

When older versions of data are stored, it leads to space amplification. This happens because of Multi-Version Concurrency control, which ensures that reads are consistent with the transaction or statement read time. These older versions are stored in the heap tables and indexes and are known as "bloat" in PostgreSQL. Additionally, these old versions are responsible for read amplification. For instance, a Seq Scan will read old versions that are no longer needed. An Index Scan may have to read multiple versions scattered within the heap table. Even an Index Only Scan may have to read heap tuples because that's where XMIN/XMAX is stored.

Those old versions stay even when they are not needed anymore by consistent reads. In my example, there's no ongoing transaction, or replication, that need to read the old version:
```sql
postgres=# select xmin from (
 select xmin from pg_replication_slots
 union all
 select catalog_xmin from pg_replication_slots
 union all
 select backend_xmin from pg_stat_replication
 union all
 select backend_xid from pg_stat_activity
 union all
 select backend_xmin from pg_stat_activity
 union all
 select transaction from pg_prepared_xacts
) order by age(xmin) nulls last limit 1
;
 xmin
------
 3233
(1 row)
```

To minimize the amount of space used, it is recommended that you use VACUUM. This process will automatically remove old versions of data, known as "dead tuples," when Auto Vacuum detects that they are no longer needed. However, I disabled Auto Vacuum and will show that this space may be reused before vacuum happens.

### Select (Seq Scan)

I'm running a select to read all rows, also displaying CTID to show where the current version is stored:
```sql
postgres=# select ctid, id, xmin, xmax, id, substr(value,1,30) , length(value)
           from demo
;

  ctid  | id | xmin | xmax | id |             substr             | length
--------+----+------+------+----+--------------------------------+--------
 (0,1)  |  0 | 3231 |    0 |  0 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,2)  |  1 | 3231 |    0 |  1 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,4)  |  3 | 3231 |    0 |  3 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,5)  |  4 | 3231 |    0 |  4 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,6)  |  5 | 3231 |    0 |  5 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,7)  |  6 | 3231 |    0 |  6 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,8)  |  7 | 3231 |    0 |  7 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,9)  |  8 | 3231 |    0 |  8 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,10) |  9 | 3231 |    0 |  9 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,11) | 10 | 3231 |    0 | 10 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,12) | 11 | 3231 |    0 | 11 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,13) | 12 | 3231 |    0 | 12 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,14) | 13 | 3231 |    0 | 13 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (0,15) | 14 | 3231 |    0 | 14 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (1,1)  | 15 | 3231 |    0 | 15 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |    500
 (1,2)  |  2 | 3232 |    0 |  2 | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |    500
(16 rows)
```
The updated row comes at the end because it was appended. The table was created with the default FILLFACTOR=100 and had no place to insert the new version closer to the old one.

Remember that no VACUUM was run and Auto Vacuum was disabled. I've run only a SELECT. The tuples in block 0 have changed:
```sql
postgres=# select t_ctid, lp, lp_flags, t_xmin, t_xmax, substr(t_data,1,30), lp_len
           from heap_page_items(get_raw_page('demo', 0))
;
 t_ctid | lp | lp_flags | t_xmin | t_xmax |                             substr                             | lp_len
--------+----+----------+--------+--------+----------------------------------------------------------------+--------
 (0,1)  |  1 |        1 |   3231 |      0 | \x0000000000000000e0070000787878787878787878787878787878787878 |    536
 (0,2)  |  2 |        1 |   3231 |      0 | \x0100000000000000e0070000787878787878787878787878787878787878 |    536
        |  3 |        3 |        |        |                                                                |      0
 (0,4)  |  4 |        1 |   3231 |      0 | \x0300000000000000e0070000787878787878787878787878787878787878 |    536
 (0,5)  |  5 |        1 |   3231 |      0 | \x0400000000000000e0070000787878787878787878787878787878787878 |    536
 (0,6)  |  6 |        1 |   3231 |      0 | \x0500000000000000e0070000787878787878787878787878787878787878 |    536
 (0,7)  |  7 |        1 |   3231 |      0 | \x0600000000000000e0070000787878787878787878787878787878787878 |    536
 (0,8)  |  8 |        1 |   3231 |      0 | \x0700000000000000e0070000787878787878787878787878787878787878 |    536
 (0,9)  |  9 |        1 |   3231 |      0 | \x0800000000000000e0070000787878787878787878787878787878787878 |    536
 (0,10) | 10 |        1 |   3231 |      0 | \x0900000000000000e0070000787878787878787878787878787878787878 |    536
 (0,11) | 11 |        1 |   3231 |      0 | \x0a00000000000000e0070000787878787878787878787878787878787878 |    536
 (0,12) | 12 |        1 |   3231 |      0 | \x0b00000000000000e0070000787878787878787878787878787878787878 |    536
 (0,13) | 13 |        1 |   3231 |      0 | \x0c00000000000000e0070000787878787878787878787878787878787878 |    536
 (0,14) | 14 |        1 |   3231 |      0 | \x0d00000000000000e0070000787878787878787878787878787878787878 |    536
 (0,15) | 15 |        1 |   3231 |      0 | \x0e00000000000000e0070000787878787878787878787878787878787878 |    536
(15 rows)
```
The value 3 of `lp_flags` means "dead". The dead tuple is still there but doesn't use any space except to store the header. That's the first point I wanted to show in this blog post: **some limited garbage collection can happen on heap tables even without running VACUUM to reduce the space amplification**.

In the B-Tree index, nothing has changed. There are still two entries, with one pointing to the dead tuple:
```sql
postgres=# select substr(data,1,42), itemoffset, htid, itemlen, nulls, vars, dead
           from bt_page_items(get_raw_page('demo_pkey', 1))
           order by data
;
                   substr                   | itemoffset |  htid  | itemlen | nulls | vars | dead
--------------------------------------------+------------+--------+---------+-------+------+------
 00 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          2 | (0,1)  |     520 | f     | t    | f
 01 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          3 | (0,2)  |     520 | f     | t    | f
 02 00 00 00 00 00 00 00 e0 07 00 00 58 58  |          5 | (1,2)  |     520 | f     | t    | f
 02 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          4 | (0,3)  |     520 | f     | t    | f
 03 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          6 | (0,4)  |     520 | f     | t    | f
 04 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          7 | (0,5)  |     520 | f     | t    | f
 05 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          8 | (0,6)  |     520 | f     | t    | f
 06 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          9 | (0,7)  |     520 | f     | t    | f
 07 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         10 | (0,8)  |     520 | f     | t    | f
 08 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         11 | (0,9)  |     520 | f     | t    | f
 09 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         12 | (0,10) |     520 | f     | t    | f
 0a 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         13 | (0,11) |     520 | f     | t    | f
 0b 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         14 | (0,12) |     520 | f     | t    | f
 0c 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         15 | (0,13) |     520 | f     | t    | f
 0d 00 00 00 00 00 00 00                    |          1 |        |      16 | f     | f    |
(15 rows)
```

### Select (Index Only Scan)

The read amplification is still visible when accessing though the index:
```sql
postgres=# explain (analyze, buffers)
           select * from demo where id=2
;

                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using demo_pkey on demo  (cost=0.28..8.29 rows=1 width=40) (actual time=0.124..0.125 rows=1 loops=1)
   Index Cond: (id = 2)
   Heap Fetches: 2
   Buffers: local hit=4
 Planning Time: 0.057 ms
 Execution Time: 0.140 ms
(6 rows)
```
Reading from the index has to read the B-Tree root and one leaf, the block 1, that contains the entries for index condition. It shows 4 buffers reads because there were 2 additional reads from the heap table to check the XMIN/XMAX visibility of both.

If you want to look at the B-Tree root, it is in block 3 with a pointer to each leaf:
```sql
postgres=# select substr(data,1,42), itemoffset, ctid, htid, itemlen, nulls, vars, dead
           from bt_page_items(get_raw_page('demo_pkey', 3))
           order by data
;
         substr          | itemoffset | ctid  | htid | itemlen | nulls | vars | dead
-------------------------+------------+-------+------+---------+-------+------+------
                         |          1 | (1,0) |      |       8 | f     | f    |
 0d 00 00 00 00 00 00 00 |          2 | (2,1) |      |      16 | f     | f    |
(2 rows)
```

So, what we have seen here is that even an Index Only Scan has to go to the table, and may read many versions to find the one that is visible to the query read time.

But this read has modified the state and the next one has one less buffer to read:
```sql
postgres=# explain (analyze, buffers)
select * from demo where id=2;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using demo_pkey on demo  (cost=0.28..8.29 rows=1 width=40) (actual time=0.015..0.016 rows=1 loops=1)
   Index Cond: (id = 2)
   Heap Fetches: 1
   Buffers: local hit=3
 Planning Time: 0.054 ms
 Execution Time: 0.030 ms
(6 rows)
``` 

This subtle change is visible in the `dead` column of the B-Tree leaf block which has been set to `true` by the previous read when it has found the dead tuple, so that the next reads don't have to do the same.
```sql
postgres=# select substr(data,1,42), itemoffset, htid, itemlen, nulls, vars, dead
           from bt_page_items(get_raw_page('demo_pkey', 1))
           order by data
;
                   substr                   | itemoffset |  htid  | itemlen | nulls | vars | dead
--------------------------------------------+------------+--------+---------+-------+------+------
 00 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          2 | (0,1)  |     520 | f     | t    | f
 01 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          3 | (0,2)  |     520 | f     | t    | f
 02 00 00 00 00 00 00 00 e0 07 00 00 58 58  |          5 | (1,2)  |     520 | f     | t    | f
 02 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          4 | (0,3)  |     520 | f     | t    | t
 03 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          6 | (0,4)  |     520 | f     | t    | f
 04 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          7 | (0,5)  |     520 | f     | t    | f
 05 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          8 | (0,6)  |     520 | f     | t    | f
 06 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          9 | (0,7)  |     520 | f     | t    | f
 07 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         10 | (0,8)  |     520 | f     | t    | f
 08 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         11 | (0,9)  |     520 | f     | t    | f
 09 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         12 | (0,10) |     520 | f     | t    | f
 0a 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         13 | (0,11) |     520 | f     | t    | f
 0b 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         14 | (0,12) |     520 | f     | t    | f
 0c 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         15 | (0,13) |     520 | f     | t    | f
 0d 00 00 00 00 00 00 00                    |          1 |        |      16 | f     | f    |
(15 rows)
```

**Without running VACUUM, the read amplification has been reduced for the next reads.** What about space amplification?

### Insert

I insert additional rows:
```sql
postgres=# insert into demo
           select generate_series(16,1000), lpad('y',50,'y')
;
INSERT 0 985
```
None of them were able to reuse the space in block 0:
```sql
 t_ctid | lp | lp_flags | t_xmin | t_xmax |                             substr
--------+----+----------+--------+--------+----------------------------------------------------------------
 (0,1)  |  1 |        1 |   3231 |      0 | \x0000000000000000e0070000787878787878787878787878787878787878
 (0,2)  |  2 |        1 |   3231 |      0 | \x0100000000000000e0070000787878787878787878787878787878787878
        |  3 |        3 |        |        |
 (0,4)  |  4 |        1 |   3231 |      0 | \x0300000000000000e0070000787878787878787878787878787878787878
 (0,5)  |  5 |        1 |   3231 |      0 | \x0400000000000000e0070000787878787878787878787878787878787878
 (0,6)  |  6 |        1 |   3231 |      0 | \x0500000000000000e0070000787878787878787878787878787878787878
 (0,7)  |  7 |        1 |   3231 |      0 | \x0600000000000000e0070000787878787878787878787878787878787878
 (0,8)  |  8 |        1 |   3231 |      0 | \x0700000000000000e0070000787878787878787878787878787878787878
 (0,9)  |  9 |        1 |   3231 |      0 | \x0800000000000000e0070000787878787878787878787878787878787878
 (0,10) | 10 |        1 |   3231 |      0 | \x0900000000000000e0070000787878787878787878787878787878787878
 (0,11) | 11 |        1 |   3231 |      0 | \x0a00000000000000e0070000787878787878787878787878787878787878
 (0,12) | 12 |        1 |   3231 |      0 | \x0b00000000000000e0070000787878787878787878787878787878787878
 (0,13) | 13 |        1 |   3231 |      0 | \x0c00000000000000e0070000787878787878787878787878787878787878
 (0,14) | 14 |        1 |   3231 |      0 | \x0d00000000000000e0070000787878787878787878787878787878787878
 (0,15) | 15 |        1 |   3231 |      0 | \x0e00000000000000e0070000787878787878787878787878787878787878
(15 rows)
```

However with smaller rows, some can reuse the space:
```sql
postgres=# insert into demo
           select generate_series(3001,4000), lpad('y',30,'y')
;
INSERT 0 1000
postgres=# select t_ctid, lp, lp_flags, t_xmin, t_xmax, substr(t_data,1,30)
           from heap_page_items(get_raw_page('demo', 0))
;
 t_ctid | lp | lp_flags | t_xmin | t_xmax |                             substr
--------+----+----------+--------+--------+----------------------------------------------------------------
 (0,1)  |  1 |        1 |   3231 |      0 | \x0000000000000000e0070000787878787878787878787878787878787878
 (0,2)  |  2 |        1 |   3231 |      0 | \x0100000000000000e0070000787878787878787878787878787878787878
        |  3 |        3 |        |        |
 (0,4)  |  4 |        1 |   3231 |      0 | \x0300000000000000e0070000787878787878787878787878787878787878
 (0,5)  |  5 |        1 |   3231 |      0 | \x0400000000000000e0070000787878787878787878787878787878787878
 (0,6)  |  6 |        1 |   3231 |      0 | \x0500000000000000e0070000787878787878787878787878787878787878
 (0,7)  |  7 |        1 |   3231 |      0 | \x0600000000000000e0070000787878787878787878787878787878787878
 (0,8)  |  8 |        1 |   3231 |      0 | \x0700000000000000e0070000787878787878787878787878787878787878
 (0,9)  |  9 |        1 |   3231 |      0 | \x0800000000000000e0070000787878787878787878787878787878787878
 (0,10) | 10 |        1 |   3231 |      0 | \x0900000000000000e0070000787878787878787878787878787878787878
 (0,11) | 11 |        1 |   3231 |      0 | \x0a00000000000000e0070000787878787878787878787878787878787878
 (0,12) | 12 |        1 |   3231 |      0 | \x0b00000000000000e0070000787878787878787878787878787878787878
 (0,13) | 13 |        1 |   3231 |      0 | \x0c00000000000000e0070000787878787878787878787878787878787878
 (0,14) | 14 |        1 |   3231 |      0 | \x0d00000000000000e0070000787878787878787878787878787878787878
 (0,15) | 15 |        1 |   3231 |      0 | \x0e00000000000000e0070000787878787878787878787878787878787878
 (0,16) | 16 |        1 |   3236 |      0 | \xe40b0000000000003f797979797979797979797979797979797979797979
 (0,17) | 17 |        1 |   3236 |      0 | \xe50b0000000000003f797979797979797979797979797979797979797979
 (0,18) | 18 |        1 |   3236 |      0 | \xe60b0000000000003f797979797979797979797979797979797979797979
 (0,19) | 19 |        1 |   3236 |      0 | \xe70b0000000000003f797979797979797979797979797979797979797979
 (0,20) | 20 |        1 |   3236 |      0 | \xe80b0000000000003f797979797979797979797979797979797979797979
 (0,21) | 21 |        1 |   3236 |      0 | \xe90b0000000000003f797979797979797979797979797979797979797979
 (0,22) | 22 |        1 |   3236 |      0 | \xea0b0000000000003f797979797979797979797979797979797979797979
 (0,23) | 23 |        1 |   3236 |      0 | \xeb0b0000000000003f797979797979797979797979797979797979797979
(23 rows)
```

**Even before VACUUM, the space released in the heap table when dead tuples are converted to metadata stubs, limiting the consequence of space amplification.**

This is possible in heap tables because a row has no specific place. It can be inserted wherever there is enough free space.

The table size is now:
```sql
postgres=# select relname,pg_table_size(oid),pg_size_pretty(pg_table_size(oid))
           from pg_class where relname like 'demo%'
;
  relname  | pg_table_size | pg_size_pretty
-----------+---------------+----------------
 demo      |        204800 | 200 kB
 demo_pkey |        172032 | 168 kB
(2 rows)
```

### Delete large ranges

In B-Tree, each value has a place so the space can be reused only if we insert values in the same range. However, we have seen that the old versions were marked as dead, reducing read amplification, but not removed,  so space amplification cannot be reused.

Let's delete a large range of rows that concern all entries in the block:
```sql
postgres=# delete from demo
           where id <= 13 -- #0d
;
```
They have been marked as old version in the heap table:
```sql
postgres=# select t_ctid, lp, lp_flags, t_xmin, t_xmax, substr(t_data,1,30)
           from heap_page_items(get_raw_page('demo', 0))
;
 t_ctid | lp | lp_flags | t_xmin | t_xmax |                             substr
--------+----+----------+--------+--------+----------------------------------------------------------------
 (0,1)  |  1 |        1 |   3231 |   3237 | \x0000000000000000e0070000787878787878787878787878787878787878
 (0,2)  |  2 |        1 |   3231 |   3237 | \x0100000000000000e0070000787878787878787878787878787878787878
        |  3 |        3 |        |        |
 (0,4)  |  4 |        1 |   3231 |   3237 | \x0300000000000000e0070000787878787878787878787878787878787878
 (0,5)  |  5 |        1 |   3231 |   3237 | \x0400000000000000e0070000787878787878787878787878787878787878
 (0,6)  |  6 |        1 |   3231 |   3237 | \x0500000000000000e0070000787878787878787878787878787878787878
 (0,7)  |  7 |        1 |   3231 |   3237 | \x0600000000000000e0070000787878787878787878787878787878787878
 (0,8)  |  8 |        1 |   3231 |   3237 | \x0700000000000000e0070000787878787878787878787878787878787878
 (0,9)  |  9 |        1 |   3231 |   3237 | \x0800000000000000e0070000787878787878787878787878787878787878
 (0,10) | 10 |        1 |   3231 |   3237 | \x0900000000000000e0070000787878787878787878787878787878787878
 (0,11) | 11 |        1 |   3231 |   3237 | \x0a00000000000000e0070000787878787878787878787878787878787878
 (0,12) | 12 |        1 |   3231 |   3237 | \x0b00000000000000e0070000787878787878787878787878787878787878
 (0,13) | 13 |        1 |   3231 |   3237 | \x0c00000000000000e0070000787878787878787878787878787878787878
 (0,14) | 14 |        1 |   3231 |   3237 | \x0d00000000000000e0070000787878787878787878787878787878787878
 (0,15) | 15 |        1 |   3231 |      0 | \x0e00000000000000e0070000787878787878787878787878787878787878
 (0,16) | 16 |        1 |   3236 |      0 | \xe40b0000000000003f797979797979797979797979797979797979797979
 (0,17) | 17 |        1 |   3236 |      0 | \xe50b0000000000003f797979797979797979797979797979797979797979
 (0,18) | 18 |        1 |   3236 |      0 | \xe60b0000000000003f797979797979797979797979797979797979797979
 (0,19) | 19 |        1 |   3236 |      0 | \xe70b0000000000003f797979797979797979797979797979797979797979
 (0,20) | 20 |        1 |   3236 |      0 | \xe80b0000000000003f797979797979797979797979797979797979797979
 (0,21) | 21 |        1 |   3236 |      0 | \xe90b0000000000003f797979797979797979797979797979797979797979
 (0,22) | 22 |        1 |   3236 |      0 | \xea0b0000000000003f797979797979797979797979797979797979797979
 (0,23) | 23 |        1 |   3236 |      0 | \xeb0b0000000000003f797979797979797979797979797979797979797979
(23 rows)
```
Nothing changed in the B-Tree leaf:
```sql
postgres=# select substr(data,1,42), itemoffset, htid, itemlen, nulls, vars, dead
           from bt_page_items(get_raw_page('demo_pkey', 1))
           order by data
;
                   substr                   | itemoffset |  ctid  |  htid  | itemlen | nulls | vars | dead
--------------------------------------------+------------+--------+--------+---------+-------+------+------
 00 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          2 | (0,1)  | (0,1)  |     520 | f     | t    | f
 01 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          3 | (0,2)  | (0,2)  |     520 | f     | t    | f
 02 00 00 00 00 00 00 00 e0 07 00 00 58 58  |          5 | (1,2)  | (1,2)  |     520 | f     | t    | f
 02 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          4 | (0,3)  | (0,3)  |     520 | f     | t    | t
 03 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          6 | (0,4)  | (0,4)  |     520 | f     | t    | f
 04 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          7 | (0,5)  | (0,5)  |     520 | f     | t    | f
 05 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          8 | (0,6)  | (0,6)  |     520 | f     | t    | f
 06 00 00 00 00 00 00 00 e0 07 00 00 78 78  |          9 | (0,7)  | (0,7)  |     520 | f     | t    | f
 07 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         10 | (0,8)  | (0,8)  |     520 | f     | t    | f
 08 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         11 | (0,9)  | (0,9)  |     520 | f     | t    | f
 09 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         12 | (0,10) | (0,10) |     520 | f     | t    | f
 0a 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         13 | (0,11) | (0,11) |     520 | f     | t    | f
 0b 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         14 | (0,12) | (0,12) |     520 | f     | t    | f
 0c 00 00 00 00 00 00 00 e0 07 00 00 78 78  |         15 | (0,13) | (0,13) |     520 | f     | t    | f
 0d 00 00 00 00 00 00 00                    |          1 | (0,1)  |        |      16 | f     | f    |
(15 rows)
```
I query this range of rows:
```sql
postgres=# explain (analyze, buffers)
           select from demo
           where id <= 13 
;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on demo  (cost=0.00..52.50 rows=840 width=0) (actual time=0.161..0.161 rows=0 loops=1)
   Filter: (id <= 13)
   Rows Removed by Filter: 1987
   Buffers: local hit=21
 Planning Time: 0.047 ms
 Execution Time: 0.172 ms
(6 rows)
```
This has removed data for the old versions in the heap table:
```sql
postgres=# select t_ctid, lp, lp_flags, t_xmin, t_xmax, substr(t_data,1,30)
           from heap_page_items(get_raw_page('demo', 0))
;
 t_ctid | lp | lp_flags | t_xmin | t_xmax |                             substr
--------+----+----------+--------+--------+----------------------------------------------------------------
        |  1 |        3 |        |        |
        |  2 |        3 |        |        |
        |  3 |        3 |        |        |
        |  4 |        3 |        |        |
        |  5 |        3 |        |        |
        |  6 |        3 |        |        |
        |  7 |        3 |        |        |
        |  8 |        3 |        |        |
        |  9 |        3 |        |        |
        | 10 |        3 |        |        |
        | 11 |        3 |        |        |
        | 12 |        3 |        |        |
        | 13 |        3 |        |        |
        | 14 |        3 |        |        |
 (0,15) | 15 |        1 |   3231 |      0 | \x0e00000000000000e0070000787878787878787878787878787878787878
 (0,16) | 16 |        1 |   3236 |      0 | \xe40b0000000000003f797979797979797979797979797979797979797979
 (0,17) | 17 |        1 |   3236 |      0 | \xe50b0000000000003f797979797979797979797979797979797979797979
 (0,18) | 18 |        1 |   3236 |      0 | \xe60b0000000000003f797979797979797979797979797979797979797979
 (0,19) | 19 |        1 |   3236 |      0 | \xe70b0000000000003f797979797979797979797979797979797979797979
 (0,20) | 20 |        1 |   3236 |      0 | \xe80b0000000000003f797979797979797979797979797979797979797979
 (0,21) | 21 |        1 |   3236 |      0 | \xe90b0000000000003f797979797979797979797979797979797979797979
 (0,22) | 22 |        1 |   3236 |      0 | \xea0b0000000000003f797979797979797979797979797979797979797979
 (0,23) | 23 |        1 |   3236 |      0 | \xeb0b0000000000003f797979797979797979797979797979797979797979
(23 rows)
```

However, as it was a Seq Scan, nothing changed in the index.

To mark the index entries as dead, I need an Index Scan (not a Bitmap Index Scan) but this will not release the space.

The size on disk didn't change:
```sql
postgres=# select relname,pg_table_size(oid),pg_size_pretty(pg_table_size(oid))
           from pg_class where relname like 'demo%'
;
  relname  | pg_table_size | pg_size_pretty
-----------+---------------+----------------
 demo      |        204800 | 200 kB
 demo_pkey |        172032 | 168 kB
(2 rows)
```

### Vacuum

To release the space in the B-Tree, we need to run VACUUM:
```sql
postgres=# vacuum demo;
VACUUM
```
This completely removes the dead tuples (data and header):
```sql
postgres=# select t_ctid, lp, lp_flags, t_xmin, t_xmax, substr(t_data,1,30)
           from heap_page_items(get_raw_page('demo', 0))
;
 t_ctid | lp | lp_flags | t_xmin | t_xmax |                             substr
--------+----+----------+--------+--------+----------------------------------------------------------------
        |  1 |        0 |        |        |
        |  2 |        0 |        |        |
        |  3 |        0 |        |        |
        |  4 |        0 |        |        |
        |  5 |        0 |        |        |
        |  6 |        0 |        |        |
        |  7 |        0 |        |        |
        |  8 |        0 |        |        |
        |  9 |        0 |        |        |
        | 10 |        0 |        |        |
        | 11 |        0 |        |        |
        | 12 |        0 |        |        |
        | 13 |        0 |        |        |
        | 14 |        0 |        |        |
 (0,15) | 15 |        1 |   3231 |      0 | \x0e00000000000000e0070000787878787878787878787878787878787878
 (0,16) | 16 |        1 |   3236 |      0 | \xe40b0000000000003f797979797979797979797979797979797979797979
 (0,17) | 17 |        1 |   3236 |      0 | \xe50b0000000000003f797979797979797979797979797979797979797979
 (0,18) | 18 |        1 |   3236 |      0 | \xe60b0000000000003f797979797979797979797979797979797979797979
 (0,19) | 19 |        1 |   3236 |      0 | \xe70b0000000000003f797979797979797979797979797979797979797979
 (0,20) | 20 |        1 |   3236 |      0 | \xe80b0000000000003f797979797979797979797979797979797979797979
 (0,21) | 21 |        1 |   3236 |      0 | \xe90b0000000000003f797979797979797979797979797979797979797979
 (0,22) | 22 |        1 |   3236 |      0 | \xea0b0000000000003f797979797979797979797979797979797979797979
 (0,23) | 23 |        1 |   3236 |      0 | \xeb0b0000000000003f797979797979797979797979797979797979797979
(23 rows)
```
The value 0 of `lp_flags` means "unused".

**It is important to understand that running VACUUM on a table only makes some space reusable and does not reduce the size on disk**, except when there are no rows above the dead tuples so the file high water mark can be lowered.

**In the index, VACUUM removes dead entries, freeing space for new inserts in the same value range.**

Here because I deleted all index entries in the B-Tree leaf block, the full block has been reclaimed to be reused:
```sql
postgres=# select substr(data,1,42), itemoffset, ctid, htid, itemlen, nulls, vars, dead
           from bt_page_items(get_raw_page('demo_pkey', 1))
           order by data
;
NOTICE:  page is deleted
NOTICE:  page from block is deleted
 substr | itemoffset | ctid | htid | itemlen | nulls | vars | dead
--------+------------+------+------+---------+-------+------+------
(0 rows)
```

On disk, the space used didn't reduce and the heap table is even larger:
```sql
postgres=# select relname,pg_table_size(oid),pg_size_pretty(pg_table_size(oid))
           from pg_class where relname like 'demo%'
;
  relname  | pg_table_size | pg_size_pretty
-----------+---------------+----------------
 demo      |        212992 | 208 kB
 demo_pkey |        172032 | 168 kB
(2 rows)
```

### Insert again

I insert the same amount of rows that have been deleted and vacuumed:
```sql
postgres=# insert into demo
           select generate_series(5000,5013), lpad('x',500,'x')
;
INSERT 0 14
```
The table size did not change because the rows were inserted into block 0 where space has been released, but the index is larger by one block:
```sql
  relname  | pg_table_size | pg_size_pretty
-----------+---------------+----------------
 demo      |        212992 | 208 kB
 demo_pkey |        180224 | 176 kB
(2 rows)
```
This blog is already too long to explain the block 1 was not reused. PostgreSQL MVCC is full of suprises.

### Reindex

Indexes can be reorganized without blocking the application:
```sql
postgres=# reindex index concurrently demo_pkey;
;
REINDEX
```

The block that was emptied and deleted is finally reclaimed:
```sql
postgres=# select relname,pg_table_size(oid),pg_size_pretty(pg_table_size(oid))
           from pg_class where relname like 'demo%'
;
  relname  | pg_table_size | pg_size_pretty
-----------+---------------+----------------
 demo      |        212992 | 208 kB
 demo_pkey |        172032 | 168 kB
(2 rows)
```
Before rebuilding indexes, ensure they have enough free space to run efficiently. Packing too much can cause block splitting and fragmentation.

### Vacuum full

If you know that you won't be inserting any new data into a table, you can reclaim disk space by reorganizing the table and its index. PostgreSQL offers a tool called VACUUM FULL, but it locks the table during the process and can take a long time. Alternatively, you can use external solutions like `pg_repack` or extensions like `pg_squeeze`. This is also a good opportunity to consider the FILLFACTOR: by leaving some free space in the blocks, you can reduce the read amplification that follows updates and allow new versions to be stored in the same block as the old version.

### A bit more about the terminology

We have seen multiple states of "dead tuples" here: before being dead, when an old version can still be read, when being expired but still taking full space, when space is released except the header, and when it is definitely gone. [Here](https://x.com/mmeent_pg/status/1764658227906585039?s=20) is more explanation about the right definition of "dead tuple", by Matthias van de Meent. In this article I used the same definition: the dead tuples have passed the longest transaction XMIN horizon and that's why they were cleaned up. Long queries a blocking this process.

Both this article and this explanation has been greatly summarized by Hussein Nasser in a short [video](https://www.youtube.com/watch?v=5RVqebc7-Gc&ab_channel=Hussein).