Feedback-ID: i76984098:Fastmail
Message-ID: <e29dfa81-cff7-49f8-9895-f50ed187709c@aklaver.com>
Date: Wed, 26 Jun 2024 08:05:26 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Autovacuum, dead tuples and bloat
To: "Shenavai, Manuel" <manuel.shenavai@sap.com>,
 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <AM9PR02MB7410603C5F467DED657D5330E8C82@AM9PR02MB7410.eurprd02.prod.outlook.com>
 <2f2aafe6-3e29-4305-a279-1b3d56e9389e@cloud.gatewaynet.com>
 <AM9PR02MB7410AA1DEA442AED52FBB5E9E8C92@AM9PR02MB7410.eurprd02.prod.outlook.com>
 <53da5a20-ca35-40fa-85a3-c59d4302a512@aklaver.com>
 <AM9PR02MB7410F50DD4C2AFBD1E6C9032E8CA2@AM9PR02MB7410.eurprd02.prod.outlook.com>
 <e948920a-a0e2-469d-9045-94f67b9beebc@aklaver.com>
 <AM9PR02MB7410F28F66A98C62CBAF3020E8D62@AM9PR02MB7410.eurprd02.prod.outlook.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <AM9PR02MB7410F28F66A98C62CBAF3020E8D62@AM9PR02MB7410.eurprd02.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/e29dfa81-cff7-49f8-9895-f50ed187709c%40aklaver.com>
Precedence: bulk

On 6/26/24 00:03, Shenavai, Manuel wrote:
> Thanks for the suggestions.
> I checked pg_locks  shows and pg_stat_activity but I could not find a LOCK or an transaction on this (at this point in time).
> 
> I assume that this problem may relate to long running transactions which write a lot of data. Is there already something in place that would help me to:
> 1) identify long running transactions
> 2) get an idea of the data-volume a single transaction writes?
> 
> I tested the log_statement='mod' but this writes too much data (including all payloads). I rather would like to get a summary entry of each transaction like:
> "Tx 4752 run for 1hour and 1GB data was written."

https://www.postgresql.org/docs/current/runtime-config-logging.html

log_min_duration_statement

Read the Note below the entry.

This will log long running queries, though it will not show th amount of 
data written.

If you want to go more in depth there is:

https://www.postgresql.org/docs/current/pgstatstatements.html

It is an extension that you will need to install per instructions at the 
link.

> 
> Is there something like this already available in postgres?
> 
> Best regards,
> Manuel

-- 
Adrian Klaver
adrian.klaver@aklaver.com