pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIdentifying "cold" data
4+ messages / 4 participants
[nested] [flat]
* Identifying "cold" data
@ 2022-02-24 18:37 Joseph Hammerman <joe.hammerman@datadoghq.com>
0 siblings, 1 reply; 4+ messages in thread
From: Joseph Hammerman @ 2022-02-24 18:37 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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?
Thanks in advance for any assistance that anyone can provide,
Joseph Hammerman
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Identifying "cold" data
@ 2022-02-25 02:48 Ron <ronljohnsonjr@gmail.com>
parent: Joseph Hammerman <joe.hammerman@datadoghq.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ron @ 2022-02-25 02:48 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Identifying "cold" data
@ 2022-02-28 23:54 Keith Fiske <keith.fiske@crunchydata.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Keith Fiske @ 2022-02-28 23:54 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, Feb 24, 2022 at 9:48 PM Ron <ronljohnsonjr@gmail.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Identifying "cold" data
@ 2022-03-01 00:25 Mladen Gogala <gogala.mladen@gmail.com>
parent: Keith Fiske <keith.fiske@crunchydata.com>
0 siblings, 0 replies; 4+ messages in thread
From: Mladen Gogala @ 2022-03-01 00:25 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 2/28/22 18:54, Keith Fiske wrote:
>
> On Thu, Feb 24, 2022 at 9:48 PM Ron <ronljohnsonjr@gmail.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
One way would be the proper table design, with TIMESTAMP column which
records the time of the last modification. Another way would be a table
trigger which would do time accounting for the table. You can implement
a "clock" algorithm which would set a "modified" column to 0 and then
wait for any application to modify the column and set it to 1. The
second hand of the clock would then set the "old" column. That is
roughly analogous to the way Unix and Linux paging systems work.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-03-01 00:25 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-02-24 18:37 Identifying "cold" data Joseph Hammerman <joe.hammerman@datadoghq.com>
2022-02-25 02:48 ` Ron <ronljohnsonjr@gmail.com>
2022-02-28 23:54 ` Keith Fiske <keith.fiske@crunchydata.com>
2022-03-01 00:25 ` Mladen Gogala <gogala.mladen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox