pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter Geoghegan <pg@bowt.ie>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Count updates that move row to a new page.
Date: Thu, 23 Mar 2023 18:17:24 +0000
Message-ID: <E1pfPV5-0051VA-HP@gemulon.postgresql.org> (raw)

Count updates that move row to a new page.

Add pgstat counter to track row updates that result in the successor
version going to a new heap page, leaving behind an original version
whose t_ctid points to the new version.  The current count is shown by
the n_tup_newpage_upd column of each of the pg_stat_*_tables views.

The new n_tup_newpage_upd column complements the existing n_tup_hot_upd
and n_tup_upd columns.  Tables that have high n_tup_newpage_upd values
(relative to n_tup_upd) are good candidates for tuning heap fillfactor.

Corey Huinker, with small tweaks by me.

Author: Corey Huinker <corey.huinker@gmail.com>
Reviewed-By: Peter Geoghegan <pg@bowt.ie>
Reviewed-By: Andres Freund <andres@anarazel.de>
Discussion: https://postgr.es/m/CADkLM=ded21M9iZ36hHm-vj2rE2d=zcKpUQMds__Xm2pxLfHKA@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/ae4fdde1352fa6b2c9123e91435efafc78c370a0

Modified Files
--------------
doc/src/sgml/monitoring.sgml                 | 28 +++++++++++++++++++++++-----
src/backend/access/heap/heapam.c             |  2 +-
src/backend/catalog/system_views.sql         |  4 +++-
src/backend/utils/activity/pgstat_relation.c | 12 ++++++++++--
src/backend/utils/adt/pgstatfuncs.c          | 18 ++++++++++++++++++
src/include/catalog/catversion.h             |  2 +-
src/include/catalog/pg_proc.dat              | 10 ++++++++++
src/include/pgstat.h                         |  8 +++++---
src/test/regress/expected/rules.out          | 12 +++++++++---
9 files changed, 80 insertions(+), 16 deletions(-)



Message-ID: <E1pfPV5-0051VA-HP@gemulon.postgresql.org>
Permalink:  ../E1pfPV5-0051VA-HP@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1pfPV5-0051VA-HP@gemulon.postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-committers@postgresql.org
  Cc: pg@bowt.ie, pgsql-committers@lists.postgresql.org
  Subject: Re: pgsql: Count updates that move row to a new page.
  In-Reply-To: <E1pfPV5-0051VA-HP@gemulon.postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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