Hi,
In this test I found that not all pages are not marked as all_visible after
vacuuming, any explanation?
CREATE TABLE test (
id SERIAL PRIMARY KEY,
name TEXT,
created_at TIMESTAMP DEFAULT now()
);
INSERT INTO test (name)
SELECT
'name_' || g
FROM generate_series(1, 100000) AS g;
CREATE TABLE
INSERT 0 100000
cpr [2720498]=#
cpr [2720498]=#
cpr [2720498]=# table test limit 10;
+----+---------+----------------------------+
| id | name | created_at |
+----+---------+----------------------------+
| 1 | name_1 | 2026-04-24 09:35:46.561014 |
| 2 | name_2 | 2026-04-24 09:35:46.561014 |
| 3 | name_3 | 2026-04-24 09:35:46.561014 |
| 4 | name_4 | 2026-04-24 09:35:46.561014 |
| 5 | name_5 | 2026-04-24 09:35:46.561014 |
| 6 | name_6 | 2026-04-24 09:35:46.561014 |
| 7 | name_7 | 2026-04-24 09:35:46.561014 |
| 8 | name_8 | 2026-04-24 09:35:46.561014 |
| 9 | name_9 | 2026-04-24 09:35:46.561014 |
| 10 | name_10 | 2026-04-24 09:35:46.561014 |
+----+---------+----------------------------+
(10 rows)
# ALTER TABLE test SET (autovacuum_enabled = false);
ALTER TABLE
# SELECT reloptions
FROM pg_class
WHERE relname = 'test';
+----------------------------+
| reloptions |
+----------------------------+
| {autovacuum_enabled=false} |
+----------------------------+
(1 row)
# update test set name = 'name_x' where id = 1;
UPDATE 1
# create extension pg_visibility;
CREATE EXTENSION
# SELECT
c.relpages AS total_pages,
s.all_visible,
s.all_frozen,
round(100.0 * s.all_visible / NULLIF(c.relpages, 0), 1) AS pct_visible,
round(100.0 * s.all_frozen / NULLIF(c.relpages, 0), 1) AS pct_frozen
FROM pg_class c
CROSS JOIN LATERAL pg_visibility_map_summary(c.oid) s
WHERE c.relname = 'test';
+-------------+-------------+------------+-------------+------------+
| total_pages | all_visible | all_frozen | pct_visible | pct_frozen |
+-------------+-------------+------------+-------------+------------+
| 637 | 635 | 0 | 99.7 | 0.0 |
+-------------+-------------+------------+-------------+------------+
(1 row)
vacuum test;
VACUUM
# SELECT
c.relpages AS total_pages,
s.all_visible,
s.all_frozen,
round(100.0 * s.all_visible / NULLIF(c.relpages, 0), 1) AS pct_visible,
round(100.0 * s.all_frozen / NULLIF(c.relpages, 0), 1) AS pct_frozen
FROM pg_class c
CROSS JOIN LATERAL pg_visibility_map_summary(c.oid) s
WHERE c.relname = 'test';
+-------------+-------------+------------+-------------+------------+
| total_pages | all_visible | all_frozen | pct_visible | pct_frozen |
+-------------+-------------+------------+-------------+------------+
| 637 | 636 | 0 | 99.8 | 0.0 |
+-------------+-------------+------------+-------------+------------+
(1 row)
Regards
Fabrice
