Re: Identifying "cold" data

On Thu, Feb 24, 2022 at 9:48 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:

> On 2/24/22 12:37 PM, Joseph Hammerman wrote:
>
> Hi postgresql-admins,
>
> Has anyone put any thought or effort into figuring out how to measure the
> total volume of data in a database against *how much of it is hot*? I'm
> looking for some automatable approaches. Similarly, is there a way to
> measure rarely queried columns, or unused functions & triggers?
>
>
> https://www.postgresql.org/docs/9.6/monitoring-stats.html
>
> pg_stat_user_tables tells you how many records have been inserted, updated
> and deleted since the instance was started. It does not, though, say
> *which* records were updated,
>
>
> --
> Angular momentum makes the world go 'round.
>

For monitoring data itself, I'm not sure of anything else built in other
than what Ron shared with the stats table. If this is something you really
need to monitor, perhaps look into adding a "changed_at" column to the
tables that really need it and set a trigger to automatically update the
timestamp of that column whenever the row is updated. Not sure how to track
column usage.

For tracking whether functions are used, you can look at the
pg_stat_user_functions catalog (
https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-USER-FUNCTIONS-VIEW),
but as the documentation there notes, you do have to enable
the "track_functions" parameter. I think this tracks trigger function usage
as well, but would be good to test to make sure.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com