–> is there a query to check dead tuples are beyond the high water mark or not?

We have a hidden column called ctid which is the physical location of the row version within its table. Both Table and its Indexes would have same matching ctid. It may be used as a row identifier that would change upon Update/Table rebuild.

percona=# CREATE TABLE percona (id int, name varchar(20));
CREATE TABLE
percona=# CREATE INDEX percona_id_index ON percona (id);
CREATE INDEX
percona=# INSERT INTO percona VALUES (1,’avinash’),(2,’vallarapu’),(3,’avi’),;
INSERT 0 3
percona=# SELECT id, name, ctid from percona;
id | name | ctid
—-+———–+——-
1 | avinash | (0,1)
2 | vallarapu | (0,2)
3 | avi | (0,3)
(3 rows)

percona=# DELETE from percona where id < 3;
DELETE 2

After deleting the records, let us see the items inside table/index pages

Table
=======
percona=# SELECT t_xmin, t_xmax, tuple_data_split('percona'::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page('percona', 0));
t_xmin | t_xmax | tuple_data_split
——–+——–+——————————————-
3825 | 3826 | {"\\x01000000","\\x116176696e617368"}
3825 | 3826 | {"\\x02000000","\\x1576616c6c6172617075"}
3825 | 0 | {"\\x03000000","\\x09617669"}
(3 rows)

Index
=======
percona=# SELECT * FROM bt_page_items('percona_id_index', 1);
itemoffset | ctid | itemlen | nulls | vars | data
————+——-+———+——-+——+————————-
1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00
2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00
3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00
(3 rows)

After running VACUUM, corresponding pointers with same ctid are also removed from Index through a RowExclusiveLock.

percona=# VACUUM ANALYZE percona;
VACUUM
percona=# SELECT t_xmin, t_xmax, tuple_data_split('percona'::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page('percona', 0));
t_xmin | t_xmax | tuple_data_split
——–+——–+——————————-
| |
| |
3825 | 0 | {"\\x03000000","\\x09617669"}
(3 rows)

percona=# SELECT * FROM bt_page_items('percona_id_index', 1);
itemoffset | ctid | itemlen | nulls | vars | data
————+——-+———+——-+——+————————-
1 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00
(1 row)

This is a good explanation which related to the data. What is about the bloat in the indexes, which I assume also can contain old pointers. How does this play in the picture ?