Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nNQfV-0003ri-59 for pgsql-admin@arkaria.postgresql.org; Fri, 25 Feb 2022 02:49:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nNQey-0004F4-59 for pgsql-admin@arkaria.postgresql.org; Fri, 25 Feb 2022 02:48:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nNQex-0004Ev-Ou for pgsql-admin@lists.postgresql.org; Fri, 25 Feb 2022 02:48:43 +0000 Received: from mail-oi1-x235.google.com ([2607:f8b0:4864:20::235]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nNQep-0007f7-U3 for pgsql-admin@lists.postgresql.org; Fri, 25 Feb 2022 02:48:43 +0000 Received: by mail-oi1-x235.google.com with SMTP id j2so5955630oie.7 for ; Thu, 24 Feb 2022 18:48:35 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=E9bF/tTUwkD3Wi1nahtd+4XX1bqFI7mWwXw6cwh3kPQ=; b=ipFVCj82/Eha+sILXh70YQSdCTFPeE5V9N0wVQv6dCuyZcqdkS6JjbmVNWG8qzwzcp WJ5nnkmV8UDN41du/HYDNXlIPBl9nWPrQVv6dz+mNu8ZTN4oysSO7lt7LWvJdxbqmqKm W/ikW3sEBl/hp1xq2vTLFmfH42x6jv3DypN+1UpIMCzojSHSI8DSf6Yek8AIj1yALvq5 8Tc7Ti4+IPr9z+g5EuYpL8QoFBZ+YhotR8gdEH6fmb1G+sinb+o+JCurKmObugIGXCDl kIQ+rDsYLfBgzJNJMmA94xUlaDGhQ8h92JcPMC/1Hxs4FP2zQ5V6kHZlI/y2OXphhq3v R46w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=E9bF/tTUwkD3Wi1nahtd+4XX1bqFI7mWwXw6cwh3kPQ=; b=W7YxwrOtm21HO74TmWtb2LaHoCTyacra7bMc+ohq4rxakiUttHpwyG/Z4O+0HE4UyO XmEdUysF9MAd3s+DdxMHOMv/VrUDleiczVH0u4adNwdnbzFBRaDH7aTaEs0x4UrSQCI9 MBW0jtibeQugle7yYUohKayIUUiZddl4FfdoNnNiyyVHkXNqQgNNkeol/rfT4teNXiTd IcofdSRAOgzyxV4KSOXLKqfhsC75FOjeqgeg7Dwe9epdGkU2p6KQmgPdbLwuvGHzLtFW 2ycYgM6oZvFOg9Q36qr6JQ0lFpMK1pVUtnQKT/I8C8YMX3UHNVRRvN0q4gUtRulHhIAC 7qJg== X-Gm-Message-State: AOAM5329C8n8LQknRs8jxgOQol5wu81zI43Lm1hiD1MHc79zFzrH18f3 NfVB2In42XHxuj9JuP2hdmnRt33gwrU= X-Google-Smtp-Source: ABdhPJzRYu/gaS/jaN0PXgw7fjTUGhusIWl1wquiEHnBe8a34ewGCsHmqxS00NY6sLhGCbVCGqo0Hg== X-Received: by 2002:a05:6808:2209:b0:2d5:2e7:d52c with SMTP id bd9-20020a056808220900b002d502e7d52cmr558353oib.54.1645757313877; Thu, 24 Feb 2022 18:48:33 -0800 (PST) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id m2-20020a9d4002000000b005a2678bbc5csm529280ote.24.2022.02.24.18.48.32 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 24 Feb 2022 18:48:33 -0800 (PST) Subject: Re: Identifying "cold" data To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: <9f1103cf-6073-4552-fb0e-84862bb226c3@gmail.com> Date: Thu, 24 Feb 2022 20:48:32 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.14.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------DD310BA41E7ED0ADBCAF5163" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------DD310BA41E7ED0ADBCAF5163 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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. --------------DD310BA41E7ED0ADBCAF5163 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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.
--------------DD310BA41E7ED0ADBCAF5163--