hot updates and fillfactor

Started by Fabrice Chapuisalmost 2 years ago3 messages
#1Fabrice Chapuis
fabrice636861@gmail.com

Hi,

I do not understand why hot_updates value is not 0 for pg_database? Given
that reloptions is empty for this table that means it has a default value
of 100%

Regards,

Fabrice

SELECT
relname AS table_name,
seq_scan AS sequential_scans,
idx_scan AS index_scans,
n_tup_ins AS inserts,
n_tup_upd AS updates,
n_tup_hot_upd AS hot_updates
FROM
pg_stat_all_tables
ORDER BY
hot_updates DESC;

table_name | sequential_scans | index_scans | inserts |
updates | hot_updates
-------------------------+------------------+-------------+---------+---------+-------------
pg_database | 5038104 | 187427486 | 3 |
16 | * 16*

postgres [1728332]=# select t.relname as table_name,
t.reloptions
from pg_class t
join pg_namespace n on n.oid = t.relnamespace
where t.relname in ('pg_database')
and n.nspname = 'pg_catalog';

table_name | reloptions
-------------+------------
pg_database |
(1 row)

#2Aleksander Alekseev
aleksander@timescale.com
In reply to: Fabrice Chapuis (#1)
Re: hot updates and fillfactor

Hi Fabrice,

I do not understand why hot_updates value is not 0 for pg_database? Given that reloptions is empty for this table that means it has a default value of 100%

Maybe I didn't entirely understand your question, but why would you
assume they are somehow related?

According to the documentation [1]https://www.postgresql.org/docs/current/catalog-pg-class.html[2]https://www.postgresql.org/docs/current/monitoring-stats.html:

pg_class.reloptions:
Access-method-specific options, as “keyword=value” strings

pg_stat_all_tables.n_tup_hot_upd:
Number of rows HOT updated. These are updates where no successor
versions are required in indexes.

The value of n_tup_hot_upd is not zero because there are tuples that
were HOT-updated. That's it. You can read more about HOT here [3]https://www.postgresql.org/docs/current/storage-hot.html.

[1]: https://www.postgresql.org/docs/current/catalog-pg-class.html
[2]: https://www.postgresql.org/docs/current/monitoring-stats.html
[3]: https://www.postgresql.org/docs/current/storage-hot.html

--
Best regards,
Aleksander Alekseev

#3Fabrice Chapuis
fabrice636861@gmail.com
In reply to: Aleksander Alekseev (#2)
Re: hot updates and fillfactor

Thanks for your explanation and for the links

On Tue, Mar 19, 2024 at 11:17 AM Aleksander Alekseev <
aleksander@timescale.com> wrote:

Show quoted text

Hi Fabrice,

I do not understand why hot_updates value is not 0 for pg_database?

Given that reloptions is empty for this table that means it has a default
value of 100%

Maybe I didn't entirely understand your question, but why would you
assume they are somehow related?

According to the documentation [1][2]:

pg_class.reloptions:
Access-method-specific options, as “keyword=value” strings

pg_stat_all_tables.n_tup_hot_upd:
Number of rows HOT updated. These are updates where no successor
versions are required in indexes.

The value of n_tup_hot_upd is not zero because there are tuples that
were HOT-updated. That's it. You can read more about HOT here [3].

[1]: https://www.postgresql.org/docs/current/catalog-pg-class.html
[2]: https://www.postgresql.org/docs/current/monitoring-stats.html
[3]: https://www.postgresql.org/docs/current/storage-hot.html

--
Best regards,
Aleksander Alekseev