Tracking the dirty tuple read rate

From: Joseph Hammerman <joe(dot)hammerman(at)datadoghq(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Tracking the dirty tuple read rate
Date: 2023-08-09 21:55:07
Message-ID: CAHs7QM9_WdafcqoPTPAQcXeXssCQrCyVUop_k+Rz-KgHDporPQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello admins,

When autovacuum falls behind, performance eventually degrades as the
percentage of a table that is "dirty" increases. It seems to me that the
issue is not strictly the percentage of a table that is dirty, but how
often a stale tuple is read and resolved to a live tuple (or another dead
tuple). I'd like to surface the dirty tuple read rate per table, this would
enable more precise diagnosis of the autovacuum falling behind condition.

I understand Postgres 16 offers the `pg_stat_io` view, but I don't see a
way to track this information explicitly in that view.

Would this be a fruitful metric to track? Has anyone looked into
this already and has some knowledge to share?

Thanks in advance for any insight anyone can provide,
Joe

Browse pgsql-admin by date

  From Date Subject
Next Message Rajesh Kumar 2023-08-10 09:02:26 Undo Update
Previous Message Wetmore, Matthew (CTR) 2023-08-09 20:49:48 Automate to rename table