On Fri, Mar 8, 2024, at 12:40 PM, Tomas Vondra wrote: > On 3/8/24 15:50, Gayatri Singh wrote: > > Hello Team, > > > > Can you help me with steps to identify transactions which caused wal > > generation to surge ? > > > > You should probably take a look at pg_waldump, which prints information > about WAL contents, including which XID generated each record.
You can also use pg_stat_statements to obtain this information. postgres=# select * from pg_stat_statements order by wal_bytes desc; -[ RECORD 1 ]----------+--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------- userid | 10 dbid | 16385 toplevel | t queryid | -8403979585082616547 query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 plans | 0 total_plan_time | 0 min_plan_time | 0 max_plan_time | 0 mean_plan_time | 0 stddev_plan_time | 0 calls | 238260 total_exec_time | 4642.599296000018 min_exec_time | 0.011094999999999999 max_exec_time | 0.872748 mean_exec_time | 0.01948543312347807 stddev_exec_time | 0.006370786385582063 rows | 238260 . . . wal_records | 496659 wal_fpi | 19417 wal_bytes | 208501147 . . . -- Euler Taveira EDB https://www.enterprisedb.com/
