MIME-Version: 1.0
From: Rucha Kulkarni <rirukool@gmail.com>
Date: Mon, 17 Feb 2025 20:42:15 +0530
Message-ID: <CAPsneqqHWWb4RxBFeViZAVx-CBMECinVTvQzrc6H7169Zay9Bw@mail.gmail.com>
Subject: Doubts regarding pg_freespacemap extension
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000176637062e57f4c6"
Archived-At: <https://www.postgresql.org/message-id/CAPsneqqHWWb4RxBFeViZAVx-CBMECinVTvQzrc6H7169Zay9Bw%40mail.gmail.com>
Precedence: bulk

--000000000000176637062e57f4c6
Content-Type: text/plain; charset="UTF-8"

Hi PostgreSQL Community,

While exploring the pg_freespacemap extension, I came across a few doubts
and would appreciate any insights or clarifications:

1. In the code, it defines NonLeafNodesPerPage as (BLCKSZ / 2 - 1) as can
be seen here:


https://github.com/postgres/postgres/blob/master/src/include/storage/fsm_internals.h#L54


For a binary tree, (BLCKSZ - 1) / 2 seems more appropriate when calculating
the number of non-leaf nodes. Why does the code approximate it to (BLCKSZ /
2) - 1 ?

2. According to the comment mentioned here
https://github.com/postgres/postgres/blob/master/src/backend/storage/freespace/indexfsm.c#L19
, 0 is used to denote unused pages and (BLCKSZ - 1) for used pages for
indexes. This seems reversed, shouldn't 0 indicate space available for used
pages, and (BLCKSZ - 1) indicate space available for unused pages?

3. Whenever I check the values for B-tree indexes, I observe all values to
be 0.

postgres=# select * from pg_freespace('xyz_btree');
 blkno | avail
-------+-------
     0 |     0
     1 |     0
     2 |     0
     3 |     0
     4 |     0
(5 rows)

How can non-zero values be observed in B-tree indexes?

4. For BRIN indexes, I see a value of 8128 instead of 8191 (BLCKSZ - 1). Is
this due to the BRIN header occupying space, leading to category 254?

postgres=# select * from pg_freespace('xyz_brin');
 blkno | avail
-------+-------
     0 |     0
     1 |     0
     2 |  8128
(3 rows)

Regards,

Rucha Kulkarni

--000000000000176637062e57f4c6
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><p>Hi PostgreSQL Community,</p><p>While exploring the pg_f=
reespacemap extension, I came across a few doubts and would appreciate any =
insights or clarifications:</p><p>1. In the code, it defines NonLeafNodesPe=
rPage as (BLCKSZ / 2 - 1) as can be seen here:</p><p>=C2=A0<a href=3D"https=
://github.com/postgres/postgres/blob/master/src/include/storage/fsm_interna=
ls.h#L54" rel=3D"noreferrer noreferrer noreferrer noreferrer noreferrer nor=
eferrer" target=3D"_blank">https://github.com/postgres/postgres/blob/master=
/src/include/storage/fsm_internals.h#L54</a>=C2=A0 <br></p><p>For
 a binary tree, (BLCKSZ - 1) / 2=C2=A0seems more appropriate when calculati=
ng
 the number of non-leaf nodes. Why does the code approximate it to=20
(BLCKSZ / 2) - 1 ?</p><p>2. According to the comment mentioned here <a href=
=3D"https://github.com/postgres/postgres/blob/master/src/backend/storage/fr=
eespace/indexfsm.c#L19" rel=3D"noreferrer noreferrer noreferrer noreferrer =
noreferrer noreferrer" target=3D"_blank">https://github.com/postgres/postgr=
es/blob/master/src/backend/storage/freespace/indexfsm.c#L19</a> , 0 is used=
 to denote unused pages and (BLCKSZ - 1) for used pages for indexes. This s=
eems reversed, shouldn&#39;t <code>0</code> indicate space available for us=
ed pages, and (BLCKSZ - 1)=C2=A0indicate space available for unused pages?<=
br><br>3. Whenever I check the values for B-tree indexes, I observe all val=
ues to be 0.</p><p>postgres=3D# select * from pg_freespace(&#39;xyz_btree&#=
39;);<br>=C2=A0blkno | avail <br>-------+-------<br>=C2=A0 =C2=A0 =C2=A00 |=
 =C2=A0 =C2=A0 0<br>=C2=A0 =C2=A0 =C2=A01 | =C2=A0 =C2=A0 0<br>=C2=A0 =C2=
=A0 =C2=A02 | =C2=A0 =C2=A0 0<br>=C2=A0 =C2=A0 =C2=A03 | =C2=A0 =C2=A0 0<br=
>=C2=A0 =C2=A0 =C2=A04 | =C2=A0 =C2=A0 0<br>(5 rows)</p><p>How can non-zero=
 values be observed in B-tree indexes?</p><p>4.
 For BRIN indexes, I see a value of 8128 instead of 8191 (BLCKSZ - 1).=20
Is this due to the BRIN header occupying space, leading to category 254?</p=
><p>postgres=3D# select * from pg_freespace(&#39;xyz_brin&#39;);<br>=C2=A0b=
lkno | avail <br>-------+-------<br>=C2=A0 =C2=A0 =C2=A00 | =C2=A0 =C2=A0 0=
<br>=C2=A0 =C2=A0 =C2=A01 | =C2=A0 =C2=A0 0<br>=C2=A0 =C2=A0 =C2=A02 | =C2=
=A08128<br>(3 rows)</p><p>Regards,</p><p>Rucha Kulkarni</p></div>

--000000000000176637062e57f4c6--