Content-Type: multipart/alternative;
 boundary="------------CoHZPlfoygfLf7d70d3yINJx"
Message-ID: <29d1112e-0bc5-02d2-5f2f-7ed256edcea9@gmail.com>
Date: Mon, 28 Feb 2022 19:25:39 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.5.0
Subject: Re: Identifying "cold" data
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: 
 <CAHs7QM87xwc=h6+p-WpBEvbbJKF8wp2a7NgP-0Qv3W-bgbRhnw@mail.gmail.com>
 <9f1103cf-6073-4552-fb0e-84862bb226c3@gmail.com>
 <CAODZiv62tWEYUw4MdR_JBm8ZKJUnh8Dvh1-9N2dL5cehpiCHeg@mail.gmail.com>
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: 
 <CAODZiv62tWEYUw4MdR_JBm8ZKJUnh8Dvh1-9N2dL5cehpiCHeg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/29d1112e-0bc5-02d2-5f2f-7ed256edcea9%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------CoHZPlfoygfLf7d70d3yINJx
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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

--------------CoHZPlfoygfLf7d70d3yINJx
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 2/28/22 18:54, Keith Fiske wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAODZiv62tWEYUw4MdR_JBm8ZKJUnh8Dvh1-9N2dL5cehpiCHeg@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr"><br>
        <div class="gmail_quote">
          <div dir="ltr" class="gmail_attr">On Thu, Feb 24, 2022 at 9:48
            PM Ron &lt;<a href="mailto:ronljohnsonjr@gmail.com"
              moz-do-not-send="true" class="moz-txt-link-freetext">ronljohnsonjr@gmail.com</a>&gt;
            wrote:<br>
          </div>
          <blockquote class="gmail_quote" style="margin:0px 0px 0px
            0.8ex;border-left:1px solid
            rgb(204,204,204);padding-left:1ex">
            <div> On 2/24/22 12:37 PM, Joseph Hammerman wrote:<br>
              <blockquote type="cite">
                <div dir="ltr">Hi postgresql-admins,
                  <div><br>
                  </div>
                  <div>Has anyone put any thought or effort into
                    figuring out how to measure the total volume of data
                    in a database against <b>how much of it is hot</b>?
                    I'm looking for some automatable approaches.
                    Similarly, is there a way to measure rarely queried
                    columns, or unused functions &amp; triggers?</div>
                </div>
              </blockquote>
              <div dir="ltr">
                <div><br>
                  <a
                    href="https://www.postgresql.org/docs/9.6/monitoring-stats.html"
                    target="_blank" moz-do-not-send="true"
                    class="moz-txt-link-freetext">https://www.postgresql.org/docs/9.6/monitoring-stats.html</a><br>
                  <br>
                  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 <b>which</b>
                  records were updated,<br>
                </div>
              </div>
              <br>
              <br>
              <div>-- <br>
                Angular momentum makes the world go 'round.</div>
            </div>
          </blockquote>
        </div>
        <br clear="all">
        <div>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.<br>
        </div>
        <div><br>
        </div>
        <div>For tracking whether functions are used, you can look at
          the pg_stat_user_functions catalog (<a
href="https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-USER-FUNCTIONS-VIEW"
            moz-do-not-send="true" class="moz-txt-link-freetext">https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-USER-FUNCTIONS-VIEW</a>),
          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.</div>
        <div><br>
        </div>
        -- <br>
        <div dir="ltr" class="gmail_signature">
          <div dir="ltr">
            <div>
              <div dir="ltr">Keith Fiske<br>
                Senior Database Engineer<br>
                Crunchy Data - <a href="http://crunchydata.com"
                  target="_blank" moz-do-not-send="true"
                  class="moz-txt-link-freetext">http://crunchydata.com</a></div>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
    <p><br>
    </p>
    <p>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.<br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a>
</pre>
  </body>
</html>

--------------CoHZPlfoygfLf7d70d3yINJx--