public inbox for [email protected]  

help / color / mirror / Atom feed

From: Fabrice Chapuis <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: question on visibility map
Date: Fri, 24 Apr 2026 10:13:11 +0200
Message-ID: <CADmBQcP2gt1bBayp0JVHtoGp6-7VqkqsTGcSEdwKfv1opYokYw@mail.gmail.com> (raw)

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

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: question on visibility map
  In-Reply-To: <CADmBQcP2gt1bBayp0JVHtoGp6-7VqkqsTGcSEdwKfv1opYokYw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox