Home > mailing lists

question on visibility map - Mailing list pgsql-hackers

From	Fabrice Chapuis
Subject	question on visibility map
Date	April 24 11:13:11
Msg-id	CADmBQcP2gt1bBayp0JVHtoGp6-7VqkqsTGcSEdwKfv1opYokYw@mail.gmail.com Whole thread
List	pgsql-hackers

Tree view

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

pgsql-hackers by date:

From: Alex Guo
Date: 24 April, 11:09:06
Subject: Re: pgbench: make verbose error messages thread-safe

From: Peter Eisentraut
Date: 24 April, 11:23:23
Subject: Re: Use correct macro for accessing offset numbers.

question on visibility map - Mailing list pgsql-hackers

Previous

Next