pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Julien Rouhaud <rjuju123@gmail.com>
To: Kirill Bychik <kirill.bychik@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: Thomas Munro <thomas.munro@gmail.com>
Subject: Re: WAL usage calculation patch
Date: Wed, 18 Mar 2020 18:19:16 +0100
Message-ID: <20200318171916.GA69354@nol> (raw)
In-Reply-To: <CAB-hujqi8=Tc567FVKaifVdV6f7PbFZeKzivnHX2TMaMN6RD3w@mail.gmail.com>
References: <CAB-hujqqsdeTq_eMihTamJ3dhG64EDdp-fWrLYVZ=3CfApfrfw@mail.gmail.com>
	<CAB-hujr8aPN6sY--qB1bidfsxE0kKkkXDe_LeaL1CRv6KYYGpg@mail.gmail.com>
	<CAOBaU_aECK1Z7Nn+x=MhvEwrJzK8wyPsPtWAafjqtZN1fYjEmg@mail.gmail.com>
	<CAB-hujqEdFjZy=tZHeWm0BGPGs7DiahNNdrhKhN0o0YzDsofkg@mail.gmail.com>
	<CAOBaU_Z+Z+nR5hLsAjXZhGoGosfBjrt8VWmHTWAZiR4Y5uy7oA@mail.gmail.com>
	<CAB-hujoJ7dUcy=upG+3QOzrad0qWdCW+LkC10vSW2UNLVvhEbA@mail.gmail.com>
	<20200317153136.GA63950@nol>
	<CAB-hujrd+8kb5o+k1dy=r3oeuxnWO_PuaQiOiPHQHc8unH5ywg@mail.gmail.com>
	<20200317203222.GA44781@nol>
	<CAB-hujqi8=Tc567FVKaifVdV6f7PbFZeKzivnHX2TMaMN6RD3w@mail.gmail.com>

On Wed, Mar 18, 2020 at 09:02:58AM +0300, Kirill Bychik wrote:
>
> There is a higher-level Instrumentation API that can be used with
> INSTRUMENT_WAL flag to collect the wal usage information. I believe
> the instrumentation is widely used in the executor code, so it should
> not be a problem to colelct instrumentation information on autovacuum
> worker level.
>
> Just a recommendation/chat, though. I am happy with the way the data
> is collected now. If you commit this variant, please add a TODO to
> rework wal usage to common instr API.


The instrumentation is somewhat intended to be used with executor nodes, not
backend commands.  I don't see real technical reason that would prevent that,
but I prefer to keep things as-is for now, as it sound less controversial.
This is for the 3rd patch, which may not even be considered for this CF anyway.


> > > As for the tests, please get somebody else to review this. I strongly
> > > believe checking full page writes here could be a source of
> > > instability.
> >
> >
> > I'm also a little bit dubious about it.  The initial checkpoint should make
> > things stable (of course unless full_page_writes is disabled), and Cfbot also
> > seems happy about it.  At least keeping it for the temporary tables test
> > shouldn't be a problem.
>
> Temp tables should show zero FPI WAL records, true :)
>
> I have no objections to the patch.


I'm attaching a v5 with fp records only for temp tables, so there's no risk of
instability.  As I previously said I'm fine with your two patches, so unless
you have objections on the fpi test for temp tables or the documentation
changes, I believe those should be ready for committer.
From a41a58c51e15c31524ea28be8e31bccbf8d5b343 Mon Sep 17 00:00:00 2001
From: Kirill Bychik <kirill.bychik@gmail.com>
Date: Tue, 17 Mar 2020 14:41:50 +0100
Subject: [PATCH v5 1/3] Track WAL usage.

---
 src/backend/access/transam/xlog.c       |  8 ++++
 src/backend/access/transam/xloginsert.c |  6 +++
 src/backend/executor/execParallel.c     | 22 ++++++++++-
 src/backend/executor/instrument.c       | 51 ++++++++++++++++++++++---
 src/include/executor/execParallel.h     |  1 +
 src/include/executor/instrument.h       | 16 +++++++-
 src/tools/pgindent/typedefs.list        |  1 +
 7 files changed, 95 insertions(+), 10 deletions(-)

diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index de2d4ee582..7cab00323d 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -42,6 +42,7 @@
 #include "commands/progress.h"
 #include "commands/tablespace.h"
 #include "common/controldata_utils.h"
+#include "executor/instrument.h"
 #include "miscadmin.h"
 #include "pg_trace.h"
 #include "pgstat.h"
@@ -1231,6 +1232,13 @@ XLogInsertRecord(XLogRecData *rdata,
 	ProcLastRecPtr = StartPos;
 	XactLastRecEnd = EndPos;
 
+	/* Provide WAL update data to the instrumentation */
+	if (inserted)
+	{
+		pgWalUsage.wal_bytes += rechdr->xl_tot_len;
+		pgWalUsage.wal_records++;
+	}
+
 	return EndPos;
 }
 
diff --git a/src/backend/access/transam/xloginsert.c b/src/backend/access/transam/xloginsert.c
index 2fa0a7f667..1f71cc0a76 100644
--- a/src/backend/access/transam/xloginsert.c
+++ b/src/backend/access/transam/xloginsert.c
@@ -25,6 +25,7 @@
 #include "access/xloginsert.h"
 #include "catalog/pg_control.h"
 #include "common/pg_lzcompress.h"
+#include "executor/instrument.h"
 #include "miscadmin.h"
 #include "pg_trace.h"
 #include "replication/origin.h"
@@ -635,6 +636,11 @@ XLogRecordAssemble(RmgrId rmid, uint8 info,
 			 */
 			bkpb.fork_flags |= BKPBLOCK_HAS_IMAGE;
 
+			/*
+			 * Report a full page image constructed for the WAL record
+			 */
+			pgWalUsage.wal_fp_records++;
+
 			/*
 			 * Construct XLogRecData entries for the page content.
 			 */
diff --git a/src/backend/executor/execParallel.c b/src/backend/executor/execParallel.c
index a753d6efa0..017367878f 100644
--- a/src/backend/executor/execParallel.c
+++ b/src/backend/executor/execParallel.c
@@ -62,6 +62,7 @@
 #define PARALLEL_KEY_DSA				UINT64CONST(0xE000000000000007)
 #define PARALLEL_KEY_QUERY_TEXT		UINT64CONST(0xE000000000000008)
 #define PARALLEL_KEY_JIT_INSTRUMENTATION UINT64CONST(0xE000000000000009)
+#define PARALLEL_KEY_WAL_USAGE			UINT64CONST(0xE00000000000000A)
 
 #define PARALLEL_TUPLE_QUEUE_SIZE		65536
 
@@ -573,6 +574,7 @@ ExecInitParallelPlan(PlanState *planstate, EState *estate,
 	char	   *pstmt_space;
 	char	   *paramlistinfo_space;
 	BufferUsage *bufusage_space;
+	WalUsage	*walusage_space;
 	SharedExecutorInstrumentation *instrumentation = NULL;
 	SharedJitInstrumentation *jit_instrumentation = NULL;
 	int			pstmt_len;
@@ -646,6 +648,13 @@ ExecInitParallelPlan(PlanState *planstate, EState *estate,
 						   mul_size(sizeof(BufferUsage), pcxt->nworkers));
 	shm_toc_estimate_keys(&pcxt->estimator, 1);
 
+	/*
+	 * Same thing for WalUsage.
+	 */
+	shm_toc_estimate_chunk(&pcxt->estimator,
+						   mul_size(sizeof(WalUsage), pcxt->nworkers));
+	shm_toc_estimate_keys(&pcxt->estimator, 1);
+
 	/* Estimate space for tuple queues. */
 	shm_toc_estimate_chunk(&pcxt->estimator,
 						   mul_size(PARALLEL_TUPLE_QUEUE_SIZE, pcxt->nworkers));
@@ -728,6 +737,12 @@ ExecInitParallelPlan(PlanState *planstate, EState *estate,
 	shm_toc_insert(pcxt->toc, PARALLEL_KEY_BUFFER_USAGE, bufusage_space);
 	pei->buffer_usage = bufusage_space;
 
+	/* Same for WalUsage. */
+	walusage_space = shm_toc_allocate(pcxt->toc,
+									  mul_size(sizeof(WalUsage), pcxt->nworkers));
+	shm_toc_insert(pcxt->toc, PARALLEL_KEY_WAL_USAGE, bufusage_space);
+	pei->wal_usage = walusage_space;
+
 	/* Set up the tuple queues that the workers will write into. */
 	pei->tqueue = ExecParallelSetupTupleQueues(pcxt, false);
 
@@ -1113,7 +1128,7 @@ ExecParallelFinish(ParallelExecutorInfo *pei)
 	 * finish, or we might get incomplete data.)
 	 */
 	for (i = 0; i < nworkers; i++)
-		InstrAccumParallelQuery(&pei->buffer_usage[i]);
+		InstrAccumParallelQuery(&pei->buffer_usage[i], &pei->wal_usage[i]);
 
 	pei->finished = true;
 }
@@ -1333,6 +1348,7 @@ ParallelQueryMain(dsm_segment *seg, shm_toc *toc)
 {
 	FixedParallelExecutorState *fpes;
 	BufferUsage *buffer_usage;
+	WalUsage *wal_usage;
 	DestReceiver *receiver;
 	QueryDesc  *queryDesc;
 	SharedExecutorInstrumentation *instrumentation;
@@ -1408,7 +1424,9 @@ ParallelQueryMain(dsm_segment *seg, shm_toc *toc)
 
 	/* Report buffer usage during parallel execution. */
 	buffer_usage = shm_toc_lookup(toc, PARALLEL_KEY_BUFFER_USAGE, false);
-	InstrEndParallelQuery(&buffer_usage[ParallelWorkerNumber]);
+	wal_usage = shm_toc_lookup (toc, PARALLEL_KEY_WAL_USAGE, false);
+	InstrEndParallelQuery(&buffer_usage[ParallelWorkerNumber],
+						  &wal_usage[ParallelWorkerNumber]);
 
 	/* Report instrumentation data if any instrumentation options are set. */
 	if (instrumentation != NULL)
diff --git a/src/backend/executor/instrument.c b/src/backend/executor/instrument.c
index bc1d42bf64..4bcb06f6e1 100644
--- a/src/backend/executor/instrument.c
+++ b/src/backend/executor/instrument.c
@@ -24,6 +24,11 @@ static void BufferUsageAdd(BufferUsage *dst, const BufferUsage *add);
 static void BufferUsageAccumDiff(BufferUsage *dst,
 								 const BufferUsage *add, const BufferUsage *sub);
 
+WalUsage pgWalUsage;
+static WalUsage save_pgWalUsage;
+
+static void WalUsageAdd(WalUsage *dst, WalUsage *add);
+static void WalUsageAccumDiff(WalUsage *dst, const WalUsage *add, const WalUsage *sub);
 
 /* Allocate new instrumentation structure(s) */
 Instrumentation *
@@ -33,15 +38,17 @@ InstrAlloc(int n, int instrument_options)
 
 	/* initialize all fields to zeroes, then modify as needed */
 	instr = palloc0(n * sizeof(Instrumentation));
-	if (instrument_options & (INSTRUMENT_BUFFERS | INSTRUMENT_TIMER))
+	if (instrument_options & (INSTRUMENT_BUFFERS | INSTRUMENT_TIMER | INSTRUMENT_WAL))
 	{
 		bool		need_buffers = (instrument_options & INSTRUMENT_BUFFERS) != 0;
+		bool 		need_wal	 = (instrument_options & INSTRUMENT_WAL) != 0;
 		bool		need_timer = (instrument_options & INSTRUMENT_TIMER) != 0;
 		int			i;
 
 		for (i = 0; i < n; i++)
 		{
 			instr[i].need_bufusage = need_buffers;
+			instr[i].need_walusage = need_wal;
 			instr[i].need_timer = need_timer;
 		}
 	}
@@ -55,6 +62,7 @@ InstrInit(Instrumentation *instr, int instrument_options)
 {
 	memset(instr, 0, sizeof(Instrumentation));
 	instr->need_bufusage = (instrument_options & INSTRUMENT_BUFFERS) != 0;
+	instr->need_walusage = (instrument_options & INSTRUMENT_WAL) != 0;
 	instr->need_timer = (instrument_options & INSTRUMENT_TIMER) != 0;
 }
 
@@ -69,6 +77,9 @@ InstrStartNode(Instrumentation *instr)
 	/* save buffer usage totals at node entry, if needed */
 	if (instr->need_bufusage)
 		instr->bufusage_start = pgBufferUsage;
+
+	if (instr->need_walusage)
+		instr->walusage_start = pgWalUsage;
 }
 
 /* Exit from a plan node */
@@ -97,6 +108,10 @@ InstrStopNode(Instrumentation *instr, double nTuples)
 		BufferUsageAccumDiff(&instr->bufusage,
 							 &pgBufferUsage, &instr->bufusage_start);
 
+	if (instr->need_walusage)
+		WalUsageAccumDiff(&instr->walusage,
+						  &pgWalUsage, &instr->walusage_start);
+
 	/* Is this the first tuple of this cycle? */
 	if (!instr->running)
 	{
@@ -160,6 +175,9 @@ InstrAggNode(Instrumentation *dst, Instrumentation *add)
 	/* Add delta of buffer usage since entry to node's totals */
 	if (dst->need_bufusage)
 		BufferUsageAdd(&dst->bufusage, &add->bufusage);
+
+	if (dst->need_walusage)
+		WalUsageAdd(&dst->walusage, &add->walusage);
 }
 
 /* note current values during parallel executor startup */
@@ -167,21 +185,25 @@ void
 InstrStartParallelQuery(void)
 {
 	save_pgBufferUsage = pgBufferUsage;
+	save_pgWalUsage = pgWalUsage;
 }
 
 /* report usage after parallel executor shutdown */
 void
-InstrEndParallelQuery(BufferUsage *result)
+InstrEndParallelQuery(BufferUsage *bufusage, WalUsage *walusage)
 {
-	memset(result, 0, sizeof(BufferUsage));
-	BufferUsageAccumDiff(result, &pgBufferUsage, &save_pgBufferUsage);
+	memset(bufusage, 0, sizeof(BufferUsage));
+	memset(walusage, 0, sizeof(WalUsage));
+	BufferUsageAccumDiff(bufusage, &pgBufferUsage, &save_pgBufferUsage);
+	WalUsageAccumDiff(walusage, &pgWalUsage, &save_pgWalUsage);
 }
 
 /* accumulate work done by workers in leader's stats */
 void
-InstrAccumParallelQuery(BufferUsage *result)
+InstrAccumParallelQuery(BufferUsage *bufusage, WalUsage *walusage)
 {
-	BufferUsageAdd(&pgBufferUsage, result);
+	BufferUsageAdd(&pgBufferUsage, bufusage);
+	WalUsageAdd(&pgWalUsage, walusage);
 }
 
 /* dst += add */
@@ -223,3 +245,20 @@ BufferUsageAccumDiff(BufferUsage *dst,
 	INSTR_TIME_ACCUM_DIFF(dst->blk_write_time,
 						  add->blk_write_time, sub->blk_write_time);
 }
+
+/* helper functions for WAL usage accumulation */
+static void
+WalUsageAdd(WalUsage *dst, WalUsage *add)
+{
+	dst->wal_bytes += add->wal_bytes;
+	dst->wal_records += add->wal_records;
+	dst->wal_fp_records += add->wal_fp_records;
+}
+
+static void
+WalUsageAccumDiff(WalUsage *dst, const WalUsage *add, const WalUsage *sub)
+{
+	dst->wal_bytes += add->wal_bytes - sub->wal_bytes;
+	dst->wal_records += add->wal_records - sub->wal_records;
+	dst->wal_fp_records += add->wal_fp_records - sub->wal_fp_records;
+}
diff --git a/src/include/executor/execParallel.h b/src/include/executor/execParallel.h
index 17d07cf020..1cc5b524fd 100644
--- a/src/include/executor/execParallel.h
+++ b/src/include/executor/execParallel.h
@@ -26,6 +26,7 @@ typedef struct ParallelExecutorInfo
 	PlanState  *planstate;		/* plan subtree we're running in parallel */
 	ParallelContext *pcxt;		/* parallel context we're using */
 	BufferUsage *buffer_usage;	/* points to bufusage area in DSM */
+	WalUsage *wal_usage;		/* walusage area in DSM */
 	SharedExecutorInstrumentation *instrumentation; /* optional */
 	struct SharedJitInstrumentation *jit_instrumentation;	/* optional */
 	dsa_area   *area;			/* points to DSA area in DSM */
diff --git a/src/include/executor/instrument.h b/src/include/executor/instrument.h
index f48d46aede..f79fac8f8c 100644
--- a/src/include/executor/instrument.h
+++ b/src/include/executor/instrument.h
@@ -32,12 +32,20 @@ typedef struct BufferUsage
 	instr_time	blk_write_time; /* time spent writing */
 } BufferUsage;
 
+typedef struct WalUsage
+{
+	long		wal_records;	/* # of wal records produced */
+	long		wal_fp_records; /* # of full page wal records produced */
+	long		wal_bytes;		/* size of wal records produced */
+} WalUsage;
+
 /* Flag bits included in InstrAlloc's instrument_options bitmask */
 typedef enum InstrumentOption
 {
 	INSTRUMENT_TIMER = 1 << 0,	/* needs timer (and row counts) */
 	INSTRUMENT_BUFFERS = 1 << 1,	/* needs buffer usage */
 	INSTRUMENT_ROWS = 1 << 2,	/* needs row count */
+	INSTRUMENT_WAL = 1 << 3,	/* needs wal usage */
 	INSTRUMENT_ALL = PG_INT32_MAX
 } InstrumentOption;
 
@@ -46,6 +54,7 @@ typedef struct Instrumentation
 	/* Parameters set at node creation: */
 	bool		need_timer;		/* true if we need timer data */
 	bool		need_bufusage;	/* true if we need buffer usage data */
+	bool		need_walusage;	/* true if we need wal usage data */
 	/* Info about current plan cycle: */
 	bool		running;		/* true if we've completed first tuple */
 	instr_time	starttime;		/* start time of current iteration of node */
@@ -53,6 +62,7 @@ typedef struct Instrumentation
 	double		firsttuple;		/* time for first tuple of this cycle */
 	double		tuplecount;		/* # of tuples emitted so far this cycle */
 	BufferUsage bufusage_start; /* buffer usage at start */
+	WalUsage	walusage_start; /* wal usage at start */
 	/* Accumulated statistics across all completed cycles: */
 	double		startup;		/* total startup time (in seconds) */
 	double		total;			/* total time (in seconds) */
@@ -62,6 +72,7 @@ typedef struct Instrumentation
 	double		nfiltered1;		/* # of tuples removed by scanqual or joinqual */
 	double		nfiltered2;		/* # of tuples removed by "other" quals */
 	BufferUsage bufusage;		/* total buffer usage */
+	WalUsage    walusage;		/* total wal usage */
 } Instrumentation;
 
 typedef struct WorkerInstrumentation
@@ -71,6 +82,7 @@ typedef struct WorkerInstrumentation
 } WorkerInstrumentation;
 
 extern PGDLLIMPORT BufferUsage pgBufferUsage;
+extern PGDLLIMPORT WalUsage pgWalUsage;
 
 extern Instrumentation *InstrAlloc(int n, int instrument_options);
 extern void InstrInit(Instrumentation *instr, int instrument_options);
@@ -79,7 +91,7 @@ extern void InstrStopNode(Instrumentation *instr, double nTuples);
 extern void InstrEndLoop(Instrumentation *instr);
 extern void InstrAggNode(Instrumentation *dst, Instrumentation *add);
 extern void InstrStartParallelQuery(void);
-extern void InstrEndParallelQuery(BufferUsage *result);
-extern void InstrAccumParallelQuery(BufferUsage *result);
+extern void InstrEndParallelQuery(BufferUsage *bufusage, WalUsage *walusage);
+extern void InstrAccumParallelQuery(BufferUsage *bufusage, WalUsage *walusage);
 
 #endif							/* INSTRUMENT_H */
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e216de9570..88aed4c652 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2632,6 +2632,7 @@ WalSndCtlData
 WalSndSendDataCallback
 WalSndState
 WalTimeSample
+WalUsage
 WalWriteMethod
 Walfile
 WindowAgg
-- 
2.20.1
From 892a6cb3ffb235a82c9dec8bb9e5c52a4250f853 Mon Sep 17 00:00:00 2001
From: Kirill Bychik <kirill.bychik@gmail.com>
Date: Tue, 17 Mar 2020 14:42:02 +0100
Subject: [PATCH v5 2/3] Keep track of WAL usage in pg_stat_statements.

---
 contrib/pg_stat_statements/Makefile           |   3 +-
 .../expected/pg_stat_statements.out           | 147 +++++++++++++++---
 .../pg_stat_statements--1.7--1.8.sql          |  50 ++++++
 .../pg_stat_statements/pg_stat_statements.c   |  51 +++++-
 .../pg_stat_statements.control                |   2 +-
 .../sql/pg_stat_statements.sql                |  68 +++++++-
 doc/src/sgml/pgstatstatements.sgml            |  27 ++++
 7 files changed, 322 insertions(+), 26 deletions(-)
 create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql

diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 80042a0905..081f997d70 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -6,7 +6,8 @@ OBJS = \
 	pg_stat_statements.o
 
 EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.6--1.7.sql \
+DATA = pg_stat_statements--1.4.sql \
+	pg_stat_statements--1.7--1.8.sql pg_stat_statements--1.6--1.7.sql \
 	pg_stat_statements--1.5--1.6.sql pg_stat_statements--1.4--1.5.sql \
 	pg_stat_statements--1.3--1.4.sql pg_stat_statements--1.2--1.3.sql \
 	pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.0--1.1.sql
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 6787ec1efd..46b59f56c5 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -195,20 +195,129 @@ SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5);
  3 | c                   
 (8 rows)
 
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                            query                            | calls | rows 
--------------------------------------------------------------+-------+------
- DELETE FROM test WHERE a > $1                               |     1 |    1
- INSERT INTO test (a, b) VALUES ($1, $2), ($3, $4), ($5, $6) |     1 |    3
- INSERT INTO test VALUES(generate_series($1, $2), $3)        |     1 |   10
- SELECT * FROM test ORDER BY a                               |     1 |   12
- SELECT * FROM test WHERE a > $1 ORDER BY a                  |     2 |    4
- SELECT * FROM test WHERE a IN ($1, $2, $3, $4, $5)          |     1 |    8
- SELECT pg_stat_statements_reset()                           |     1 |    1
- UPDATE test SET b = $1 WHERE a = $2                         |     6 |    6
- UPDATE test SET b = $1 WHERE a > $2                         |     1 |    3
+-- SELECT check WAL usage stats to confirm temp tables do not get stored in WAL
+SELECT query, calls, rows,
+wal_write_bytes, wal_write_records, wal_write_fp_records
+FROM pg_stat_statements ORDER BY query COLLATE "C";
+                            query                            | calls | rows | wal_write_bytes | wal_write_records | wal_write_fp_records 
+-------------------------------------------------------------+-------+------+-----------------+-------------------+----------------------
+ DELETE FROM test WHERE a > $1                               |     1 |    1 |               0 |                 0 |                    0
+ INSERT INTO test (a, b) VALUES ($1, $2), ($3, $4), ($5, $6) |     1 |    3 |               0 |                 0 |                    0
+ INSERT INTO test VALUES(generate_series($1, $2), $3)        |     1 |   10 |               0 |                 0 |                    0
+ SELECT * FROM test ORDER BY a                               |     1 |   12 |               0 |                 0 |                    0
+ SELECT * FROM test WHERE a > $1 ORDER BY a                  |     2 |    4 |               0 |                 0 |                    0
+ SELECT * FROM test WHERE a IN ($1, $2, $3, $4, $5)          |     1 |    8 |               0 |                 0 |                    0
+ SELECT pg_stat_statements_reset()                           |     1 |    1 |               0 |                 0 |                    0
+ UPDATE test SET b = $1 WHERE a = $2                         |     6 |    6 |               0 |                 0 |                    0
+ UPDATE test SET b = $1 WHERE a > $2                         |     1 |    3 |               0 |                 0 |                    0
 (9 rows)
 
+-- CHECKPOINT before WAL tests to ensure test stability
+CHECKPOINT;
+--
+-- CRUD: INSERT SELECT UPDATE DELETE on test non-temp table to validate WAL generation metrics
+--
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+-- utility "create table" should not be shown
+CREATE TABLE pgss_test (a int, b char(20));
+INSERT INTO pgss_test VALUES(generate_series(1, 10), 'aaa');
+UPDATE pgss_test SET b = 'bbb' WHERE a > 7;
+DELETE FROM pgss_test WHERE a > 9;
+-- explicit transaction
+BEGIN;
+UPDATE pgss_test SET b = '111' WHERE a = 1 ;
+COMMIT;
+BEGIN \;
+UPDATE pgss_test SET b = '222' WHERE a = 2 \;
+COMMIT ;
+UPDATE pgss_test SET b = '333' WHERE a = 3 \;
+UPDATE pgss_test SET b = '444' WHERE a = 4 ;
+BEGIN \;
+UPDATE pgss_test SET b = '555' WHERE a = 5 \;
+UPDATE pgss_test SET b = '666' WHERE a = 6 \;
+COMMIT ;
+-- many INSERT values
+INSERT INTO pgss_test (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c');
+-- SELECT with constants
+SELECT * FROM pgss_test WHERE a > 5 ORDER BY a ;
+ a |          b           
+---+----------------------
+ 6 | 666                 
+ 7 | aaa                 
+ 8 | bbb                 
+ 9 | bbb                 
+(4 rows)
+
+SELECT *
+  FROM pgss_test
+  WHERE a > 9
+  ORDER BY a ;
+ a | b 
+---+---
+(0 rows)
+
+-- SELECT without constants
+SELECT * FROM pgss_test ORDER BY a;
+ a |          b           
+---+----------------------
+ 1 | a                   
+ 1 | 111                 
+ 2 | b                   
+ 2 | 222                 
+ 3 | c                   
+ 3 | 333                 
+ 4 | 444                 
+ 5 | 555                 
+ 6 | 666                 
+ 7 | aaa                 
+ 8 | bbb                 
+ 9 | bbb                 
+(12 rows)
+
+-- SELECT with IN clause
+SELECT * FROM pgss_test WHERE a IN (1, 2, 3, 4, 5);
+ a |          b           
+---+----------------------
+ 1 | 111                 
+ 2 | 222                 
+ 3 | 333                 
+ 4 | 444                 
+ 5 | 555                 
+ 1 | a                   
+ 2 | b                   
+ 3 | c                   
+(8 rows)
+
+-- DROP test table
+SET pg_stat_statements.track_utility = TRUE;
+DROP TABLE pgss_test;
+SET pg_stat_statements.track_utility = FALSE;
+-- SELECT usage data, check WAL usage is reported, wal_write_records equal rows count for INSERT/UPDATE/DELETE
+SELECT query, calls, rows,
+wal_write_bytes > 0 as wal_bytes_written,
+wal_write_records > 0 as wal_records_written,
+wal_write_records = rows as wal_records_as_rows
+FROM pg_stat_statements ORDER BY query COLLATE "C";
+                              query                               | calls | rows | wal_bytes_written | wal_records_written | wal_records_as_rows 
+------------------------------------------------------------------+-------+------+-------------------+---------------------+---------------------
+ DELETE FROM pgss_test WHERE a > $1                               |     1 |    1 | t                 | t                   | t
+ DROP TABLE pgss_test                                             |     1 |    0 | t                 | t                   | f
+ INSERT INTO pgss_test (a, b) VALUES ($1, $2), ($3, $4), ($5, $6) |     1 |    3 | t                 | t                   | t
+ INSERT INTO pgss_test VALUES(generate_series($1, $2), $3)        |     1 |   10 | t                 | t                   | t
+ SELECT * FROM pgss_test ORDER BY a                               |     1 |   12 | f                 | f                   | f
+ SELECT * FROM pgss_test WHERE a > $1 ORDER BY a                  |     2 |    4 | f                 | f                   | f
+ SELECT * FROM pgss_test WHERE a IN ($1, $2, $3, $4, $5)          |     1 |    8 | f                 | f                   | f
+ SELECT pg_stat_statements_reset()                                |     1 |    1 | f                 | f                   | f
+ SET pg_stat_statements.track_utility = FALSE                     |     1 |    0 | f                 | f                   | t
+ UPDATE pgss_test SET b = $1 WHERE a = $2                         |     6 |    6 | t                 | t                   | t
+ UPDATE pgss_test SET b = $1 WHERE a > $2                         |     1 |    3 | t                 | t                   | t
+(11 rows)
+
 --
 -- pg_stat_statements.track = none
 --
@@ -287,13 +396,13 @@ SELECT PLUS_ONE(10);
        11
 (1 row)
 
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-               query               | calls | rows 
------------------------------------+-------+------
- SELECT $1::TEXT                   |     1 |    1
- SELECT PLUS_ONE($1)               |     2 |    2
- SELECT PLUS_TWO($1)               |     2 |    2
- SELECT pg_stat_statements_reset() |     1 |    1
+SELECT query, calls, rows, wal_write_bytes, wal_write_records FROM pg_stat_statements ORDER BY query COLLATE "C";
+               query               | calls | rows | wal_write_bytes | wal_write_records 
+-----------------------------------+-------+------+-----------------+-------------------
+ SELECT $1::TEXT                   |     1 |    1 |               0 |                 0
+ SELECT PLUS_ONE($1)               |     2 |    2 |               0 |                 0
+ SELECT PLUS_TWO($1)               |     2 |    2 |               0 |                 0
+ SELECT pg_stat_statements_reset() |     1 |    1 |               0 |                 0
 (4 rows)
 
 --
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql b/contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql
new file mode 100644
index 0000000000..f8b79f2277
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql
@@ -0,0 +1,50 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.8'" to load this file. \quit
+
+/* First we have to remove them from the extension */
+ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
+ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean);
+
+/* Then we can drop them */
+DROP VIEW pg_stat_statements;
+DROP FUNCTION pg_stat_statements(boolean);
+
+/* Now redefine */
+CREATE FUNCTION pg_stat_statements(IN showtext boolean,
+    OUT userid oid,
+    OUT dbid oid,
+    OUT queryid bigint,
+    OUT query text,
+    OUT calls int8,
+    OUT total_time float8,
+    OUT min_time float8,
+    OUT max_time float8,
+    OUT mean_time float8,
+    OUT stddev_time float8,
+    OUT rows int8,
+    OUT shared_blks_hit int8,
+    OUT shared_blks_read int8,
+    OUT shared_blks_dirtied int8,
+    OUT shared_blks_written int8,
+    OUT local_blks_hit int8,
+    OUT local_blks_read int8,
+    OUT local_blks_dirtied int8,
+    OUT local_blks_written int8,
+    OUT temp_blks_read int8,
+    OUT temp_blks_written int8,
+    OUT blk_read_time float8,
+    OUT blk_write_time float8,
+    OUT wal_write_bytes int8,
+    OUT wal_write_records int8,
+    OUT wal_write_fp_records int8
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_stat_statements_1_4'
+LANGUAGE C STRICT VOLATILE;
+
+CREATE VIEW pg_stat_statements AS
+  SELECT * FROM pg_stat_statements(true);
+
+GRANT SELECT ON pg_stat_statements TO PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 20dc8c605b..1c256fc395 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -120,7 +120,8 @@ typedef enum pgssVersion
 	PGSS_V1_0 = 0,
 	PGSS_V1_1,
 	PGSS_V1_2,
-	PGSS_V1_3
+	PGSS_V1_3,
+	PGSS_V1_4
 } pgssVersion;
 
 /*
@@ -161,6 +162,9 @@ typedef struct Counters
 	int64		local_blks_written; /* # of local disk blocks written */
 	int64		temp_blks_read; /* # of temp blocks read */
 	int64		temp_blks_written;	/* # of temp blocks written */
+	int64 		wal_bytes_written;		/* total amount of wal bytes written */
+	int64 		wal_records_written;	/* # of wal records written */
+	int64 		wal_fp_records_written; /* # of full page wal records written */
 	double		blk_read_time;	/* time spent reading, in msec */
 	double		blk_write_time; /* time spent writing, in msec */
 	double		usage;			/* usage factor */
@@ -293,6 +297,7 @@ PG_FUNCTION_INFO_V1(pg_stat_statements_reset);
 PG_FUNCTION_INFO_V1(pg_stat_statements_reset_1_7);
 PG_FUNCTION_INFO_V1(pg_stat_statements_1_2);
 PG_FUNCTION_INFO_V1(pg_stat_statements_1_3);
+PG_FUNCTION_INFO_V1(pg_stat_statements_1_4);
 PG_FUNCTION_INFO_V1(pg_stat_statements);
 
 static void pgss_shmem_startup(void);
@@ -313,6 +318,7 @@ static void pgss_store(const char *query, uint64 queryId,
 					   int query_location, int query_len,
 					   double total_time, uint64 rows,
 					   const BufferUsage *bufusage,
+					   const WalUsage* walusage,
 					   pgssJumbleState *jstate);
 static void pg_stat_statements_internal(FunctionCallInfo fcinfo,
 										pgssVersion api_version,
@@ -841,6 +847,7 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query)
 				   0,
 				   0,
 				   NULL,
+				   NULL,
 				   &jstate);
 }
 
@@ -944,6 +951,7 @@ pgss_ExecutorEnd(QueryDesc *queryDesc)
 				   queryDesc->totaltime->total * 1000.0,	/* convert to msec */
 				   queryDesc->estate->es_processed,
 				   &queryDesc->totaltime->bufusage,
+				   &queryDesc->totaltime->walusage,
 				   NULL);
 	}
 
@@ -989,7 +997,11 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
 		BufferUsage bufusage_start,
 					bufusage;
 
+		WalUsage	walusage_start,
+					walusage;
+
 		bufusage_start = pgBufferUsage;
+		walusage_start = pgWalUsage;
 		INSTR_TIME_SET_CURRENT(start);
 
 		nested_level++;
@@ -1041,6 +1053,13 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
 		bufusage.blk_write_time = pgBufferUsage.blk_write_time;
 		INSTR_TIME_SUBTRACT(bufusage.blk_write_time, bufusage_start.blk_write_time);
 
+		walusage.wal_bytes =
+			pgWalUsage.wal_bytes - walusage_start.wal_bytes;
+		walusage.wal_records =
+			pgWalUsage.wal_records - walusage_start.wal_records;
+		walusage.wal_fp_records =
+			pgWalUsage.wal_fp_records - walusage_start.wal_fp_records;
+
 		pgss_store(queryString,
 				   0,			/* signal that it's a utility stmt */
 				   pstmt->stmt_location,
@@ -1048,6 +1067,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
 				   INSTR_TIME_GET_MILLISEC(duration),
 				   rows,
 				   &bufusage,
+					&walusage,
 				   NULL);
 	}
 	else
@@ -1083,13 +1103,14 @@ pgss_hash_string(const char *str, int len)
  *
  * If jstate is not NULL then we're trying to create an entry for which
  * we have no statistics as yet; we just want to record the normalized
- * query string.  total_time, rows, bufusage are ignored in this case.
+ * query string.  Time and usage are ignored in this case.
  */
 static void
 pgss_store(const char *query, uint64 queryId,
 		   int query_location, int query_len,
 		   double total_time, uint64 rows,
 		   const BufferUsage *bufusage,
+		   const WalUsage* walusage,
 		   pgssJumbleState *jstate)
 {
 	pgssHashKey key;
@@ -1281,6 +1302,9 @@ pgss_store(const char *query, uint64 queryId,
 		e->counters.blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_read_time);
 		e->counters.blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_write_time);
 		e->counters.usage += USAGE_EXEC(total_time);
+		e->counters.wal_bytes_written += walusage->wal_bytes;
+		e->counters.wal_records_written += walusage->wal_records;
+		e->counters.wal_fp_records_written += walusage->wal_fp_records;
 
 		SpinLockRelease(&e->mutex);
 	}
@@ -1328,7 +1352,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
 #define PG_STAT_STATEMENTS_COLS_V1_1	18
 #define PG_STAT_STATEMENTS_COLS_V1_2	19
 #define PG_STAT_STATEMENTS_COLS_V1_3	23
-#define PG_STAT_STATEMENTS_COLS			23	/* maximum of above */
+#define PG_STAT_STATEMENTS_COLS_V1_4 	26
+#define PG_STAT_STATEMENTS_COLS			26	/* maximum of above */
 
 /*
  * Retrieve statement statistics.
@@ -1340,6 +1365,15 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
  * expected API version is identified by embedding it in the C name of the
  * function.  Unfortunately we weren't bright enough to do that for 1.1.
  */
+Datum pg_stat_statements_1_4(PG_FUNCTION_ARGS)
+{
+	bool showtext = PG_GETARG_BOOL(0);
+
+	pg_stat_statements_internal(fcinfo, PGSS_V1_4, showtext);
+
+	return (Datum)0;
+}
+
 Datum
 pg_stat_statements_1_3(PG_FUNCTION_ARGS)
 {
@@ -1445,6 +1479,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
 			if (api_version != PGSS_V1_3)
 				elog(ERROR, "incorrect number of output arguments");
 			break;
+		case PG_STAT_STATEMENTS_COLS_V1_4:
+			if (api_version != PGSS_V1_4)
+				elog(ERROR, "incorrect number of output arguments");
+			break;
 		default:
 			elog(ERROR, "incorrect number of output arguments");
 	}
@@ -1641,11 +1679,18 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
 			values[i++] = Float8GetDatumFast(tmp.blk_read_time);
 			values[i++] = Float8GetDatumFast(tmp.blk_write_time);
 		}
+		if (api_version >= PGSS_V1_4)
+		{
+			values[i++] = Int64GetDatumFast(tmp.wal_bytes_written);
+			values[i++] = Int64GetDatumFast(tmp.wal_records_written);
+			values[i++] = Int64GetDatumFast(tmp.wal_fp_records_written);
+		}
 
 		Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 :
 					 api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 :
 					 api_version == PGSS_V1_2 ? PG_STAT_STATEMENTS_COLS_V1_2 :
 					 api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 :
+					 api_version == PGSS_V1_4 ? PG_STAT_STATEMENTS_COLS_V1_4 :
 					 -1 /* fail if you forget to update this assert */ ));
 
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 14cb422354..7fb20df886 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.control
+++ b/contrib/pg_stat_statements/pg_stat_statements.control
@@ -1,5 +1,5 @@
 # pg_stat_statements extension
 comment = 'track execution statistics of all SQL statements executed'
-default_version = '1.7'
+default_version = '1.8'
 module_pathname = '$libdir/pg_stat_statements'
 relocatable = true
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index 8b527070d4..5184a4bbb0 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -99,7 +99,71 @@ SELECT * FROM test ORDER BY a;
 -- SELECT with IN clause
 SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5);
 
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+-- SELECT check WAL usage stats to confirm temp tables do not get stored in WAL
+SELECT query, calls, rows,
+wal_write_bytes, wal_write_records, wal_write_fp_records
+FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- CHECKPOINT before WAL tests to ensure test stability
+CHECKPOINT;
+
+--
+-- CRUD: INSERT SELECT UPDATE DELETE on test non-temp table to validate WAL generation metrics
+--
+SELECT pg_stat_statements_reset();
+
+-- utility "create table" should not be shown
+CREATE TABLE pgss_test (a int, b char(20));
+
+INSERT INTO pgss_test VALUES(generate_series(1, 10), 'aaa');
+UPDATE pgss_test SET b = 'bbb' WHERE a > 7;
+DELETE FROM pgss_test WHERE a > 9;
+
+-- explicit transaction
+BEGIN;
+UPDATE pgss_test SET b = '111' WHERE a = 1 ;
+COMMIT;
+
+BEGIN \;
+UPDATE pgss_test SET b = '222' WHERE a = 2 \;
+COMMIT ;
+
+UPDATE pgss_test SET b = '333' WHERE a = 3 \;
+UPDATE pgss_test SET b = '444' WHERE a = 4 ;
+
+BEGIN \;
+UPDATE pgss_test SET b = '555' WHERE a = 5 \;
+UPDATE pgss_test SET b = '666' WHERE a = 6 \;
+COMMIT ;
+
+-- many INSERT values
+INSERT INTO pgss_test (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c');
+
+-- SELECT with constants
+SELECT * FROM pgss_test WHERE a > 5 ORDER BY a ;
+
+SELECT *
+  FROM pgss_test
+  WHERE a > 9
+  ORDER BY a ;
+
+-- SELECT without constants
+SELECT * FROM pgss_test ORDER BY a;
+
+-- SELECT with IN clause
+SELECT * FROM pgss_test WHERE a IN (1, 2, 3, 4, 5);
+
+-- DROP test table
+SET pg_stat_statements.track_utility = TRUE;
+DROP TABLE pgss_test;
+SET pg_stat_statements.track_utility = FALSE;
+
+-- SELECT usage data, check WAL usage is reported, wal_write_records equal rows count for INSERT/UPDATE/DELETE
+SELECT query, calls, rows,
+wal_write_bytes > 0 as wal_bytes_written,
+wal_write_records > 0 as wal_records_written,
+wal_write_records = rows as wal_records_as_rows
+FROM pg_stat_statements ORDER BY query COLLATE "C";
 
 --
 -- pg_stat_statements.track = none
@@ -144,7 +208,7 @@ $$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
 SELECT PLUS_ONE(8);
 SELECT PLUS_ONE(10);
 
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT query, calls, rows, wal_write_bytes, wal_write_records FROM pg_stat_statements ORDER BY query COLLATE "C";
 
 --
 -- pg_stat_statements.track = all
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index 26bb82da4a..40e79f1866 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -221,6 +221,33 @@
       </entry>
      </row>
 
+     <row>
+      <entry><structfield>wal_write_bytes</structfield></entry>
+      <entry><type>bigint</type></entry>
+      <entry></entry>
+      <entry>
+        Total amount of WAL bytes generated by the statement
+      </entry>
+     </row>
+
+     <row>
+      <entry><structfield>wal_write_records</structfield></entry>
+      <entry><type>bigint</type></entry>
+      <entry></entry>
+      <entry>
+        Total count of WAL records generated by the statement
+      </entry>
+     </row>
+
+     <row>
+      <entry><structfield>wal_write_fp_records</structfield></entry>
+      <entry><type>bigint</type></entry>
+      <entry></entry>
+      <entry>
+        Total count of WAL full page images generated by the statement
+      </entry>
+     </row>
+
     </tbody>
    </tgroup>
   </table>
-- 
2.20.1
From e1e1d6d38895151d287515dafb9d9ee8f8cc912e Mon Sep 17 00:00:00 2001
From: Julien Rouhaud <julien.rouhaud@free.fr>
Date: Wed, 4 Mar 2020 20:09:22 +0100
Subject: [PATCH v5 3/3] Keep track of (auto)vacuum WAL usage in
 pg_stat_database.

---
 src/backend/catalog/system_views.sql |  6 ++
 src/backend/commands/vacuum.c        | 11 ++++
 src/backend/postmaster/pgstat.c      | 56 ++++++++++++++++
 src/backend/utils/adt/pgstatfuncs.c  | 96 ++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat      | 24 +++++++
 src/include/pgstat.h                 | 27 +++++++-
 src/test/regress/expected/rules.out  |  6 ++
 7 files changed, 225 insertions(+), 1 deletion(-)

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index b8a3f46912..a3d1ac2523 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -892,6 +892,12 @@ CREATE VIEW pg_stat_database AS
             pg_stat_get_db_checksum_last_failure(D.oid) AS checksum_last_failure,
             pg_stat_get_db_blk_read_time(D.oid) AS blk_read_time,
             pg_stat_get_db_blk_write_time(D.oid) AS blk_write_time,
+            pg_stat_get_db_vac_wal_records(D.oid) AS vac_wal_records,
+            pg_stat_get_db_vac_wal_fp_records(D.oid) AS vac_wal_fp_records,
+            pg_stat_get_db_vac_wal_bytes(D.oid) AS vac_wal_bytes,
+            pg_stat_get_db_autovac_wal_records(D.oid) AS autovac_wal_records,
+            pg_stat_get_db_autovac_wal_fp_records(D.oid) AS autovac_wal_fp_records,
+            pg_stat_get_db_autovac_wal_bytes(D.oid) AS autovac_wal_bytes,
             pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
     FROM (
         SELECT 0 AS oid, NULL::name AS datname
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index d625d17bf4..14a235a2ed 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -275,6 +275,8 @@ vacuum(List *relations, VacuumParams *params,
 	   BufferAccessStrategy bstrategy, bool isTopLevel)
 {
 	static bool in_vacuum = false;
+	WalUsage walusage_start = pgWalUsage;
+	WalUsage walusage = {0, 0, 0};
 
 	const char *stmttype;
 	volatile bool in_outer_xact,
@@ -489,6 +491,15 @@ vacuum(List *relations, VacuumParams *params,
 	{
 		in_vacuum = false;
 		VacuumCostActive = false;
+		walusage.wal_records = pgWalUsage.wal_records -
+			walusage_start.wal_records;
+		walusage.wal_fp_records = pgWalUsage.wal_fp_records -
+			walusage_start.wal_fp_records;
+		walusage.wal_bytes = pgWalUsage.wal_bytes - walusage_start.wal_bytes;
+		pgstat_report_vac_wal_usage(walusage.wal_records,
+									walusage.wal_fp_records,
+									walusage.wal_bytes,
+									IsAutoVacuumWorkerProcess());
 	}
 	PG_END_TRY();
 
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index f9287b7942..c9d65669ce 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -330,6 +330,7 @@ static void pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int le
 static void pgstat_recv_deadlock(PgStat_MsgDeadlock *msg, int len);
 static void pgstat_recv_checksum_failure(PgStat_MsgChecksumFailure *msg, int len);
 static void pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len);
+static void pgstat_recv_vac_walusage(PgStat_MsgVacWalUsage *msg, int len);
 
 /* ------------------------------------------------------------
  * Public functions called from postmaster follow
@@ -1572,6 +1573,30 @@ pgstat_report_tempfile(size_t filesize)
 	pgstat_send(&msg, sizeof(msg));
 }
 
+/* --------
+ * pgstat_report_vac_wal_usage() -
+ *
+ *	Tell the collector about (auto)vacuum WAL usage.
+ * --------
+ */
+void
+pgstat_report_vac_wal_usage(long wal_records, long wal_fp_records,
+							long wal_bytes, bool autovacuum)
+{
+	PgStat_MsgVacWalUsage msg;
+
+	if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_counts)
+		return;
+
+	pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_VACWALUSAGE);
+	msg.m_databaseid = MyDatabaseId;
+	msg.m_wal_records = wal_records;
+	msg.m_wal_fp_records = wal_fp_records;
+	msg.m_wal_bytes = wal_bytes;
+	msg.m_autovacuum = autovacuum;
+	pgstat_send(&msg, sizeof(msg));
+}
+
 
 /* ----------
  * pgstat_ping() -
@@ -4525,6 +4550,10 @@ PgstatCollectorMain(int argc, char *argv[])
 					pgstat_recv_tempfile(&msg.msg_tempfile, len);
 					break;
 
+				case PGSTAT_MTYPE_VACWALUSAGE:
+					pgstat_recv_vac_walusage(&msg.msg_vac_walusage, len);
+					break;
+
 				case PGSTAT_MTYPE_CHECKSUMFAILURE:
 					pgstat_recv_checksum_failure(&msg.msg_checksumfailure,
 												 len);
@@ -6282,6 +6311,33 @@ pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len)
 	dbentry->n_temp_files += 1;
 }
 
+/* ----------
+ * pgstat_recv_vac_walusage() -
+ *
+ *	Process a VACWALUSAGE message.
+ * ----------
+ */
+static void
+pgstat_recv_vac_walusage(PgStat_MsgVacWalUsage *msg, int len)
+{
+	PgStat_StatDBEntry *dbentry;
+
+	dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
+
+	if (msg->m_autovacuum)
+	{
+		dbentry->n_autovac_wal_records += msg->m_wal_records;
+		dbentry->n_autovac_wal_fp_records += msg->m_wal_fp_records;
+		dbentry->autovac_wal_bytes += msg->m_wal_bytes;
+	}
+	else
+	{
+		dbentry->n_vac_wal_records += msg->m_wal_records;
+		dbentry->n_vac_wal_fp_records += msg->m_wal_fp_records;
+		dbentry->vac_wal_bytes += msg->m_wal_bytes;
+	}
+}
+
 /* ----------
  * pgstat_recv_funcstat() -
  *
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index cea01534a5..1bc7291c2b 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1606,6 +1606,102 @@ pg_stat_get_db_blk_write_time(PG_FUNCTION_ARGS)
 	PG_RETURN_FLOAT8(result);
 }
 
+Datum
+pg_stat_get_db_vac_wal_records(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	/* convert counter from microsec to millisec for display */
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_vac_wal_records);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_vac_wal_fp_records(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	/* convert counter from microsec to millisec for display */
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_vac_wal_fp_records);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_vac_wal_bytes(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	/* convert counter from microsec to millisec for display */
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->vac_wal_bytes);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_autovac_wal_records(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	/* convert counter from microsec to millisec for display */
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_autovac_wal_records);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_autovac_wal_fp_records(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	/* convert counter from microsec to millisec for display */
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_autovac_wal_fp_records);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_autovac_wal_bytes(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	/* convert counter from microsec to millisec for display */
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->autovac_wal_bytes);
+
+	PG_RETURN_INT64(result);
+}
+
 Datum
 pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7fb574f9dc..8b1c1487ca 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5372,6 +5372,30 @@
   proname => 'pg_stat_get_db_blk_write_time', provolatile => 's',
   proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
   prosrc => 'pg_stat_get_db_blk_write_time' },
+{ oid => '8176', descr => 'statistics: number of vacuum wal records',
+  proname => 'pg_stat_get_db_vac_wal_records', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_vac_wal_records' },
+{ oid => '8177', descr => 'statistics: number of vacuum wal full page records',
+  proname => 'pg_stat_get_db_vac_wal_fp_records', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_vac_wal_fp_records' },
+{ oid => '8178', descr => 'statistics: number of vacuum wal bytes',
+  proname => 'pg_stat_get_db_vac_wal_bytes', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_vac_wal_bytes' },
+{ oid => '8179', descr => 'statistics: number of autovacuum wal records',
+  proname => 'pg_stat_get_db_autovac_wal_records', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_autovac_wal_records' },
+{ oid => '8180', descr => 'statistics: number of autovacuum wal full page records',
+  proname => 'pg_stat_get_db_autovac_wal_fp_records', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_autovac_wal_fp_records' },
+{ oid => '8181', descr => 'statistics: number of autovacuum wal bytes',
+  proname => 'pg_stat_get_db_autovac_wal_bytes', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_autovac_wal_bytes' },
 { oid => '3195', descr => 'statistics: information about WAL archiver',
   proname => 'pg_stat_get_archiver', proisstrict => 'f', provolatile => 's',
   proparallel => 'r', prorettype => 'record', proargtypes => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 1a19921f80..40fb97bdc9 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -65,7 +65,8 @@ typedef enum StatMsgType
 	PGSTAT_MTYPE_RECOVERYCONFLICT,
 	PGSTAT_MTYPE_TEMPFILE,
 	PGSTAT_MTYPE_DEADLOCK,
-	PGSTAT_MTYPE_CHECKSUMFAILURE
+	PGSTAT_MTYPE_CHECKSUMFAILURE,
+	PGSTAT_MTYPE_VACWALUSAGE
 } StatMsgType;
 
 /* ----------
@@ -544,6 +545,21 @@ typedef struct PgStat_MsgChecksumFailure
 	TimestampTz m_failure_time;
 } PgStat_MsgChecksumFailure;
 
+/* ----------
+ * PgStat_MsgVacWalUsage	Sent by the backend to tell the collector
+ *								about (auto)vacuum WAL usage.
+ * ----------
+ */
+typedef struct PgStat_MsgVacWalUsage
+{
+	PgStat_MsgHdr m_hdr;
+	Oid			m_databaseid;
+	long		m_wal_records;
+	long		m_wal_fp_records;
+	long		m_wal_bytes;
+	bool		m_autovacuum;
+} PgStat_MsgVacWalUsage;
+
 
 /* ----------
  * PgStat_Msg					Union over all possible messages.
@@ -571,6 +587,7 @@ typedef union PgStat_Msg
 	PgStat_MsgDeadlock msg_deadlock;
 	PgStat_MsgTempFile msg_tempfile;
 	PgStat_MsgChecksumFailure msg_checksumfailure;
+	PgStat_MsgVacWalUsage msg_vac_walusage;
 } PgStat_Msg;
 
 
@@ -613,6 +630,12 @@ typedef struct PgStat_StatDBEntry
 	TimestampTz last_checksum_failure;
 	PgStat_Counter n_block_read_time;	/* times in microseconds */
 	PgStat_Counter n_block_write_time;
+	PgStat_Counter n_vac_wal_records;
+	PgStat_Counter n_vac_wal_fp_records;
+	PgStat_Counter vac_wal_bytes;
+	PgStat_Counter n_autovac_wal_records;
+	PgStat_Counter n_autovac_wal_fp_records;
+	PgStat_Counter autovac_wal_bytes;
 
 	TimestampTz stat_reset_timestamp;
 	TimestampTz stats_timestamp;	/* time of db stats file update */
@@ -1261,6 +1284,8 @@ extern void pgstat_bestart(void);
 
 extern void pgstat_report_activity(BackendState state, const char *cmd_str);
 extern void pgstat_report_tempfile(size_t filesize);
+extern void pgstat_report_vac_wal_usage(long wal_records, long wal_fp_record,
+										long wal_bytes, bool autovacuum);
 extern void pgstat_report_appname(const char *appname);
 extern void pgstat_report_xact_timestamp(TimestampTz tstamp);
 extern const char *pgstat_get_wait_event(uint32 wait_event_info);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index c7304611c3..aa311cd594 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1833,6 +1833,12 @@ pg_stat_database| SELECT d.oid AS datid,
     pg_stat_get_db_checksum_last_failure(d.oid) AS checksum_last_failure,
     pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time,
     pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time,
+    pg_stat_get_db_vac_wal_records(d.oid) AS vac_wal_records,
+    pg_stat_get_db_vac_wal_fp_records(d.oid) AS vac_wal_fp_records,
+    pg_stat_get_db_vac_wal_bytes(d.oid) AS vac_wal_bytes,
+    pg_stat_get_db_autovac_wal_records(d.oid) AS autovac_wal_records,
+    pg_stat_get_db_autovac_wal_fp_records(d.oid) AS autovac_wal_fp_records,
+    pg_stat_get_db_autovac_wal_bytes(d.oid) AS autovac_wal_bytes,
     pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset
    FROM ( SELECT 0 AS oid,
             NULL::name AS datname
-- 
2.20.1

Attachments:

  [text/plain] v5-0001-Track-WAL-usage.patch (13.3K, ../20200318171916.GA69354@nol/2-v5-0001-Track-WAL-usage.patch)
  download | inline diff:
From a41a58c51e15c31524ea28be8e31bccbf8d5b343 Mon Sep 17 00:00:00 2001
From: Kirill Bychik <kirill.bychik@gmail.com>
Date: Tue, 17 Mar 2020 14:41:50 +0100
Subject: [PATCH v5 1/3] Track WAL usage.

---
 src/backend/access/transam/xlog.c       |  8 ++++
 src/backend/access/transam/xloginsert.c |  6 +++
 src/backend/executor/execParallel.c     | 22 ++++++++++-
 src/backend/executor/instrument.c       | 51 ++++++++++++++++++++++---
 src/include/executor/execParallel.h     |  1 +
 src/include/executor/instrument.h       | 16 +++++++-
 src/tools/pgindent/typedefs.list        |  1 +
 7 files changed, 95 insertions(+), 10 deletions(-)

diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index de2d4ee582..7cab00323d 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -42,6 +42,7 @@
 #include "commands/progress.h"
 #include "commands/tablespace.h"
 #include "common/controldata_utils.h"
+#include "executor/instrument.h"
 #include "miscadmin.h"
 #include "pg_trace.h"
 #include "pgstat.h"
@@ -1231,6 +1232,13 @@ XLogInsertRecord(XLogRecData *rdata,
 	ProcLastRecPtr = StartPos;
 	XactLastRecEnd = EndPos;
 
+	/* Provide WAL update data to the instrumentation */
+	if (inserted)
+	{
+		pgWalUsage.wal_bytes += rechdr->xl_tot_len;
+		pgWalUsage.wal_records++;
+	}
+
 	return EndPos;
 }
 
diff --git a/src/backend/access/transam/xloginsert.c b/src/backend/access/transam/xloginsert.c
index 2fa0a7f667..1f71cc0a76 100644
--- a/src/backend/access/transam/xloginsert.c
+++ b/src/backend/access/transam/xloginsert.c
@@ -25,6 +25,7 @@
 #include "access/xloginsert.h"
 #include "catalog/pg_control.h"
 #include "common/pg_lzcompress.h"
+#include "executor/instrument.h"
 #include "miscadmin.h"
 #include "pg_trace.h"
 #include "replication/origin.h"
@@ -635,6 +636,11 @@ XLogRecordAssemble(RmgrId rmid, uint8 info,
 			 */
 			bkpb.fork_flags |= BKPBLOCK_HAS_IMAGE;
 
+			/*
+			 * Report a full page image constructed for the WAL record
+			 */
+			pgWalUsage.wal_fp_records++;
+
 			/*
 			 * Construct XLogRecData entries for the page content.
 			 */
diff --git a/src/backend/executor/execParallel.c b/src/backend/executor/execParallel.c
index a753d6efa0..017367878f 100644
--- a/src/backend/executor/execParallel.c
+++ b/src/backend/executor/execParallel.c
@@ -62,6 +62,7 @@
 #define PARALLEL_KEY_DSA				UINT64CONST(0xE000000000000007)
 #define PARALLEL_KEY_QUERY_TEXT		UINT64CONST(0xE000000000000008)
 #define PARALLEL_KEY_JIT_INSTRUMENTATION UINT64CONST(0xE000000000000009)
+#define PARALLEL_KEY_WAL_USAGE			UINT64CONST(0xE00000000000000A)
 
 #define PARALLEL_TUPLE_QUEUE_SIZE		65536
 
@@ -573,6 +574,7 @@ ExecInitParallelPlan(PlanState *planstate, EState *estate,
 	char	   *pstmt_space;
 	char	   *paramlistinfo_space;
 	BufferUsage *bufusage_space;
+	WalUsage	*walusage_space;
 	SharedExecutorInstrumentation *instrumentation = NULL;
 	SharedJitInstrumentation *jit_instrumentation = NULL;
 	int			pstmt_len;
@@ -646,6 +648,13 @@ ExecInitParallelPlan(PlanState *planstate, EState *estate,
 						   mul_size(sizeof(BufferUsage), pcxt->nworkers));
 	shm_toc_estimate_keys(&pcxt->estimator, 1);
 
+	/*
+	 * Same thing for WalUsage.
+	 */
+	shm_toc_estimate_chunk(&pcxt->estimator,
+						   mul_size(sizeof(WalUsage), pcxt->nworkers));
+	shm_toc_estimate_keys(&pcxt->estimator, 1);
+
 	/* Estimate space for tuple queues. */
 	shm_toc_estimate_chunk(&pcxt->estimator,
 						   mul_size(PARALLEL_TUPLE_QUEUE_SIZE, pcxt->nworkers));
@@ -728,6 +737,12 @@ ExecInitParallelPlan(PlanState *planstate, EState *estate,
 	shm_toc_insert(pcxt->toc, PARALLEL_KEY_BUFFER_USAGE, bufusage_space);
 	pei->buffer_usage = bufusage_space;
 
+	/* Same for WalUsage. */
+	walusage_space = shm_toc_allocate(pcxt->toc,
+									  mul_size(sizeof(WalUsage), pcxt->nworkers));
+	shm_toc_insert(pcxt->toc, PARALLEL_KEY_WAL_USAGE, bufusage_space);
+	pei->wal_usage = walusage_space;
+
 	/* Set up the tuple queues that the workers will write into. */
 	pei->tqueue = ExecParallelSetupTupleQueues(pcxt, false);
 
@@ -1113,7 +1128,7 @@ ExecParallelFinish(ParallelExecutorInfo *pei)
 	 * finish, or we might get incomplete data.)
 	 */
 	for (i = 0; i < nworkers; i++)
-		InstrAccumParallelQuery(&pei->buffer_usage[i]);
+		InstrAccumParallelQuery(&pei->buffer_usage[i], &pei->wal_usage[i]);
 
 	pei->finished = true;
 }
@@ -1333,6 +1348,7 @@ ParallelQueryMain(dsm_segment *seg, shm_toc *toc)
 {
 	FixedParallelExecutorState *fpes;
 	BufferUsage *buffer_usage;
+	WalUsage *wal_usage;
 	DestReceiver *receiver;
 	QueryDesc  *queryDesc;
 	SharedExecutorInstrumentation *instrumentation;
@@ -1408,7 +1424,9 @@ ParallelQueryMain(dsm_segment *seg, shm_toc *toc)
 
 	/* Report buffer usage during parallel execution. */
 	buffer_usage = shm_toc_lookup(toc, PARALLEL_KEY_BUFFER_USAGE, false);
-	InstrEndParallelQuery(&buffer_usage[ParallelWorkerNumber]);
+	wal_usage = shm_toc_lookup (toc, PARALLEL_KEY_WAL_USAGE, false);
+	InstrEndParallelQuery(&buffer_usage[ParallelWorkerNumber],
+						  &wal_usage[ParallelWorkerNumber]);
 
 	/* Report instrumentation data if any instrumentation options are set. */
 	if (instrumentation != NULL)
diff --git a/src/backend/executor/instrument.c b/src/backend/executor/instrument.c
index bc1d42bf64..4bcb06f6e1 100644
--- a/src/backend/executor/instrument.c
+++ b/src/backend/executor/instrument.c
@@ -24,6 +24,11 @@ static void BufferUsageAdd(BufferUsage *dst, const BufferUsage *add);
 static void BufferUsageAccumDiff(BufferUsage *dst,
 								 const BufferUsage *add, const BufferUsage *sub);
 
+WalUsage pgWalUsage;
+static WalUsage save_pgWalUsage;
+
+static void WalUsageAdd(WalUsage *dst, WalUsage *add);
+static void WalUsageAccumDiff(WalUsage *dst, const WalUsage *add, const WalUsage *sub);
 
 /* Allocate new instrumentation structure(s) */
 Instrumentation *
@@ -33,15 +38,17 @@ InstrAlloc(int n, int instrument_options)
 
 	/* initialize all fields to zeroes, then modify as needed */
 	instr = palloc0(n * sizeof(Instrumentation));
-	if (instrument_options & (INSTRUMENT_BUFFERS | INSTRUMENT_TIMER))
+	if (instrument_options & (INSTRUMENT_BUFFERS | INSTRUMENT_TIMER | INSTRUMENT_WAL))
 	{
 		bool		need_buffers = (instrument_options & INSTRUMENT_BUFFERS) != 0;
+		bool 		need_wal	 = (instrument_options & INSTRUMENT_WAL) != 0;
 		bool		need_timer = (instrument_options & INSTRUMENT_TIMER) != 0;
 		int			i;
 
 		for (i = 0; i < n; i++)
 		{
 			instr[i].need_bufusage = need_buffers;
+			instr[i].need_walusage = need_wal;
 			instr[i].need_timer = need_timer;
 		}
 	}
@@ -55,6 +62,7 @@ InstrInit(Instrumentation *instr, int instrument_options)
 {
 	memset(instr, 0, sizeof(Instrumentation));
 	instr->need_bufusage = (instrument_options & INSTRUMENT_BUFFERS) != 0;
+	instr->need_walusage = (instrument_options & INSTRUMENT_WAL) != 0;
 	instr->need_timer = (instrument_options & INSTRUMENT_TIMER) != 0;
 }
 
@@ -69,6 +77,9 @@ InstrStartNode(Instrumentation *instr)
 	/* save buffer usage totals at node entry, if needed */
 	if (instr->need_bufusage)
 		instr->bufusage_start = pgBufferUsage;
+
+	if (instr->need_walusage)
+		instr->walusage_start = pgWalUsage;
 }
 
 /* Exit from a plan node */
@@ -97,6 +108,10 @@ InstrStopNode(Instrumentation *instr, double nTuples)
 		BufferUsageAccumDiff(&instr->bufusage,
 							 &pgBufferUsage, &instr->bufusage_start);
 
+	if (instr->need_walusage)
+		WalUsageAccumDiff(&instr->walusage,
+						  &pgWalUsage, &instr->walusage_start);
+
 	/* Is this the first tuple of this cycle? */
 	if (!instr->running)
 	{
@@ -160,6 +175,9 @@ InstrAggNode(Instrumentation *dst, Instrumentation *add)
 	/* Add delta of buffer usage since entry to node's totals */
 	if (dst->need_bufusage)
 		BufferUsageAdd(&dst->bufusage, &add->bufusage);
+
+	if (dst->need_walusage)
+		WalUsageAdd(&dst->walusage, &add->walusage);
 }
 
 /* note current values during parallel executor startup */
@@ -167,21 +185,25 @@ void
 InstrStartParallelQuery(void)
 {
 	save_pgBufferUsage = pgBufferUsage;
+	save_pgWalUsage = pgWalUsage;
 }
 
 /* report usage after parallel executor shutdown */
 void
-InstrEndParallelQuery(BufferUsage *result)
+InstrEndParallelQuery(BufferUsage *bufusage, WalUsage *walusage)
 {
-	memset(result, 0, sizeof(BufferUsage));
-	BufferUsageAccumDiff(result, &pgBufferUsage, &save_pgBufferUsage);
+	memset(bufusage, 0, sizeof(BufferUsage));
+	memset(walusage, 0, sizeof(WalUsage));
+	BufferUsageAccumDiff(bufusage, &pgBufferUsage, &save_pgBufferUsage);
+	WalUsageAccumDiff(walusage, &pgWalUsage, &save_pgWalUsage);
 }
 
 /* accumulate work done by workers in leader's stats */
 void
-InstrAccumParallelQuery(BufferUsage *result)
+InstrAccumParallelQuery(BufferUsage *bufusage, WalUsage *walusage)
 {
-	BufferUsageAdd(&pgBufferUsage, result);
+	BufferUsageAdd(&pgBufferUsage, bufusage);
+	WalUsageAdd(&pgWalUsage, walusage);
 }
 
 /* dst += add */
@@ -223,3 +245,20 @@ BufferUsageAccumDiff(BufferUsage *dst,
 	INSTR_TIME_ACCUM_DIFF(dst->blk_write_time,
 						  add->blk_write_time, sub->blk_write_time);
 }
+
+/* helper functions for WAL usage accumulation */
+static void
+WalUsageAdd(WalUsage *dst, WalUsage *add)
+{
+	dst->wal_bytes += add->wal_bytes;
+	dst->wal_records += add->wal_records;
+	dst->wal_fp_records += add->wal_fp_records;
+}
+
+static void
+WalUsageAccumDiff(WalUsage *dst, const WalUsage *add, const WalUsage *sub)
+{
+	dst->wal_bytes += add->wal_bytes - sub->wal_bytes;
+	dst->wal_records += add->wal_records - sub->wal_records;
+	dst->wal_fp_records += add->wal_fp_records - sub->wal_fp_records;
+}
diff --git a/src/include/executor/execParallel.h b/src/include/executor/execParallel.h
index 17d07cf020..1cc5b524fd 100644
--- a/src/include/executor/execParallel.h
+++ b/src/include/executor/execParallel.h
@@ -26,6 +26,7 @@ typedef struct ParallelExecutorInfo
 	PlanState  *planstate;		/* plan subtree we're running in parallel */
 	ParallelContext *pcxt;		/* parallel context we're using */
 	BufferUsage *buffer_usage;	/* points to bufusage area in DSM */
+	WalUsage *wal_usage;		/* walusage area in DSM */
 	SharedExecutorInstrumentation *instrumentation; /* optional */
 	struct SharedJitInstrumentation *jit_instrumentation;	/* optional */
 	dsa_area   *area;			/* points to DSA area in DSM */
diff --git a/src/include/executor/instrument.h b/src/include/executor/instrument.h
index f48d46aede..f79fac8f8c 100644
--- a/src/include/executor/instrument.h
+++ b/src/include/executor/instrument.h
@@ -32,12 +32,20 @@ typedef struct BufferUsage
 	instr_time	blk_write_time; /* time spent writing */
 } BufferUsage;
 
+typedef struct WalUsage
+{
+	long		wal_records;	/* # of wal records produced */
+	long		wal_fp_records; /* # of full page wal records produced */
+	long		wal_bytes;		/* size of wal records produced */
+} WalUsage;
+
 /* Flag bits included in InstrAlloc's instrument_options bitmask */
 typedef enum InstrumentOption
 {
 	INSTRUMENT_TIMER = 1 << 0,	/* needs timer (and row counts) */
 	INSTRUMENT_BUFFERS = 1 << 1,	/* needs buffer usage */
 	INSTRUMENT_ROWS = 1 << 2,	/* needs row count */
+	INSTRUMENT_WAL = 1 << 3,	/* needs wal usage */
 	INSTRUMENT_ALL = PG_INT32_MAX
 } InstrumentOption;
 
@@ -46,6 +54,7 @@ typedef struct Instrumentation
 	/* Parameters set at node creation: */
 	bool		need_timer;		/* true if we need timer data */
 	bool		need_bufusage;	/* true if we need buffer usage data */
+	bool		need_walusage;	/* true if we need wal usage data */
 	/* Info about current plan cycle: */
 	bool		running;		/* true if we've completed first tuple */
 	instr_time	starttime;		/* start time of current iteration of node */
@@ -53,6 +62,7 @@ typedef struct Instrumentation
 	double		firsttuple;		/* time for first tuple of this cycle */
 	double		tuplecount;		/* # of tuples emitted so far this cycle */
 	BufferUsage bufusage_start; /* buffer usage at start */
+	WalUsage	walusage_start; /* wal usage at start */
 	/* Accumulated statistics across all completed cycles: */
 	double		startup;		/* total startup time (in seconds) */
 	double		total;			/* total time (in seconds) */
@@ -62,6 +72,7 @@ typedef struct Instrumentation
 	double		nfiltered1;		/* # of tuples removed by scanqual or joinqual */
 	double		nfiltered2;		/* # of tuples removed by "other" quals */
 	BufferUsage bufusage;		/* total buffer usage */
+	WalUsage    walusage;		/* total wal usage */
 } Instrumentation;
 
 typedef struct WorkerInstrumentation
@@ -71,6 +82,7 @@ typedef struct WorkerInstrumentation
 } WorkerInstrumentation;
 
 extern PGDLLIMPORT BufferUsage pgBufferUsage;
+extern PGDLLIMPORT WalUsage pgWalUsage;
 
 extern Instrumentation *InstrAlloc(int n, int instrument_options);
 extern void InstrInit(Instrumentation *instr, int instrument_options);
@@ -79,7 +91,7 @@ extern void InstrStopNode(Instrumentation *instr, double nTuples);
 extern void InstrEndLoop(Instrumentation *instr);
 extern void InstrAggNode(Instrumentation *dst, Instrumentation *add);
 extern void InstrStartParallelQuery(void);
-extern void InstrEndParallelQuery(BufferUsage *result);
-extern void InstrAccumParallelQuery(BufferUsage *result);
+extern void InstrEndParallelQuery(BufferUsage *bufusage, WalUsage *walusage);
+extern void InstrAccumParallelQuery(BufferUsage *bufusage, WalUsage *walusage);
 
 #endif							/* INSTRUMENT_H */
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e216de9570..88aed4c652 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2632,6 +2632,7 @@ WalSndCtlData
 WalSndSendDataCallback
 WalSndState
 WalTimeSample
+WalUsage
 WalWriteMethod
 Walfile
 WindowAgg
-- 
2.20.1

  [text/plain] v5-0002-Keep-track-of-WAL-usage-in-pg_stat_statements.patch (22.2K, ../20200318171916.GA69354@nol/3-v5-0002-Keep-track-of-WAL-usage-in-pg_stat_statements.patch)
  download | inline diff:
From 892a6cb3ffb235a82c9dec8bb9e5c52a4250f853 Mon Sep 17 00:00:00 2001
From: Kirill Bychik <kirill.bychik@gmail.com>
Date: Tue, 17 Mar 2020 14:42:02 +0100
Subject: [PATCH v5 2/3] Keep track of WAL usage in pg_stat_statements.

---
 contrib/pg_stat_statements/Makefile           |   3 +-
 .../expected/pg_stat_statements.out           | 147 +++++++++++++++---
 .../pg_stat_statements--1.7--1.8.sql          |  50 ++++++
 .../pg_stat_statements/pg_stat_statements.c   |  51 +++++-
 .../pg_stat_statements.control                |   2 +-
 .../sql/pg_stat_statements.sql                |  68 +++++++-
 doc/src/sgml/pgstatstatements.sgml            |  27 ++++
 7 files changed, 322 insertions(+), 26 deletions(-)
 create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql

diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 80042a0905..081f997d70 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -6,7 +6,8 @@ OBJS = \
 	pg_stat_statements.o
 
 EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.6--1.7.sql \
+DATA = pg_stat_statements--1.4.sql \
+	pg_stat_statements--1.7--1.8.sql pg_stat_statements--1.6--1.7.sql \
 	pg_stat_statements--1.5--1.6.sql pg_stat_statements--1.4--1.5.sql \
 	pg_stat_statements--1.3--1.4.sql pg_stat_statements--1.2--1.3.sql \
 	pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.0--1.1.sql
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 6787ec1efd..46b59f56c5 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -195,20 +195,129 @@ SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5);
  3 | c                   
 (8 rows)
 
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                            query                            | calls | rows 
--------------------------------------------------------------+-------+------
- DELETE FROM test WHERE a > $1                               |     1 |    1
- INSERT INTO test (a, b) VALUES ($1, $2), ($3, $4), ($5, $6) |     1 |    3
- INSERT INTO test VALUES(generate_series($1, $2), $3)        |     1 |   10
- SELECT * FROM test ORDER BY a                               |     1 |   12
- SELECT * FROM test WHERE a > $1 ORDER BY a                  |     2 |    4
- SELECT * FROM test WHERE a IN ($1, $2, $3, $4, $5)          |     1 |    8
- SELECT pg_stat_statements_reset()                           |     1 |    1
- UPDATE test SET b = $1 WHERE a = $2                         |     6 |    6
- UPDATE test SET b = $1 WHERE a > $2                         |     1 |    3
+-- SELECT check WAL usage stats to confirm temp tables do not get stored in WAL
+SELECT query, calls, rows,
+wal_write_bytes, wal_write_records, wal_write_fp_records
+FROM pg_stat_statements ORDER BY query COLLATE "C";
+                            query                            | calls | rows | wal_write_bytes | wal_write_records | wal_write_fp_records 
+-------------------------------------------------------------+-------+------+-----------------+-------------------+----------------------
+ DELETE FROM test WHERE a > $1                               |     1 |    1 |               0 |                 0 |                    0
+ INSERT INTO test (a, b) VALUES ($1, $2), ($3, $4), ($5, $6) |     1 |    3 |               0 |                 0 |                    0
+ INSERT INTO test VALUES(generate_series($1, $2), $3)        |     1 |   10 |               0 |                 0 |                    0
+ SELECT * FROM test ORDER BY a                               |     1 |   12 |               0 |                 0 |                    0
+ SELECT * FROM test WHERE a > $1 ORDER BY a                  |     2 |    4 |               0 |                 0 |                    0
+ SELECT * FROM test WHERE a IN ($1, $2, $3, $4, $5)          |     1 |    8 |               0 |                 0 |                    0
+ SELECT pg_stat_statements_reset()                           |     1 |    1 |               0 |                 0 |                    0
+ UPDATE test SET b = $1 WHERE a = $2                         |     6 |    6 |               0 |                 0 |                    0
+ UPDATE test SET b = $1 WHERE a > $2                         |     1 |    3 |               0 |                 0 |                    0
 (9 rows)
 
+-- CHECKPOINT before WAL tests to ensure test stability
+CHECKPOINT;
+--
+-- CRUD: INSERT SELECT UPDATE DELETE on test non-temp table to validate WAL generation metrics
+--
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+-- utility "create table" should not be shown
+CREATE TABLE pgss_test (a int, b char(20));
+INSERT INTO pgss_test VALUES(generate_series(1, 10), 'aaa');
+UPDATE pgss_test SET b = 'bbb' WHERE a > 7;
+DELETE FROM pgss_test WHERE a > 9;
+-- explicit transaction
+BEGIN;
+UPDATE pgss_test SET b = '111' WHERE a = 1 ;
+COMMIT;
+BEGIN \;
+UPDATE pgss_test SET b = '222' WHERE a = 2 \;
+COMMIT ;
+UPDATE pgss_test SET b = '333' WHERE a = 3 \;
+UPDATE pgss_test SET b = '444' WHERE a = 4 ;
+BEGIN \;
+UPDATE pgss_test SET b = '555' WHERE a = 5 \;
+UPDATE pgss_test SET b = '666' WHERE a = 6 \;
+COMMIT ;
+-- many INSERT values
+INSERT INTO pgss_test (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c');
+-- SELECT with constants
+SELECT * FROM pgss_test WHERE a > 5 ORDER BY a ;
+ a |          b           
+---+----------------------
+ 6 | 666                 
+ 7 | aaa                 
+ 8 | bbb                 
+ 9 | bbb                 
+(4 rows)
+
+SELECT *
+  FROM pgss_test
+  WHERE a > 9
+  ORDER BY a ;
+ a | b 
+---+---
+(0 rows)
+
+-- SELECT without constants
+SELECT * FROM pgss_test ORDER BY a;
+ a |          b           
+---+----------------------
+ 1 | a                   
+ 1 | 111                 
+ 2 | b                   
+ 2 | 222                 
+ 3 | c                   
+ 3 | 333                 
+ 4 | 444                 
+ 5 | 555                 
+ 6 | 666                 
+ 7 | aaa                 
+ 8 | bbb                 
+ 9 | bbb                 
+(12 rows)
+
+-- SELECT with IN clause
+SELECT * FROM pgss_test WHERE a IN (1, 2, 3, 4, 5);
+ a |          b           
+---+----------------------
+ 1 | 111                 
+ 2 | 222                 
+ 3 | 333                 
+ 4 | 444                 
+ 5 | 555                 
+ 1 | a                   
+ 2 | b                   
+ 3 | c                   
+(8 rows)
+
+-- DROP test table
+SET pg_stat_statements.track_utility = TRUE;
+DROP TABLE pgss_test;
+SET pg_stat_statements.track_utility = FALSE;
+-- SELECT usage data, check WAL usage is reported, wal_write_records equal rows count for INSERT/UPDATE/DELETE
+SELECT query, calls, rows,
+wal_write_bytes > 0 as wal_bytes_written,
+wal_write_records > 0 as wal_records_written,
+wal_write_records = rows as wal_records_as_rows
+FROM pg_stat_statements ORDER BY query COLLATE "C";
+                              query                               | calls | rows | wal_bytes_written | wal_records_written | wal_records_as_rows 
+------------------------------------------------------------------+-------+------+-------------------+---------------------+---------------------
+ DELETE FROM pgss_test WHERE a > $1                               |     1 |    1 | t                 | t                   | t
+ DROP TABLE pgss_test                                             |     1 |    0 | t                 | t                   | f
+ INSERT INTO pgss_test (a, b) VALUES ($1, $2), ($3, $4), ($5, $6) |     1 |    3 | t                 | t                   | t
+ INSERT INTO pgss_test VALUES(generate_series($1, $2), $3)        |     1 |   10 | t                 | t                   | t
+ SELECT * FROM pgss_test ORDER BY a                               |     1 |   12 | f                 | f                   | f
+ SELECT * FROM pgss_test WHERE a > $1 ORDER BY a                  |     2 |    4 | f                 | f                   | f
+ SELECT * FROM pgss_test WHERE a IN ($1, $2, $3, $4, $5)          |     1 |    8 | f                 | f                   | f
+ SELECT pg_stat_statements_reset()                                |     1 |    1 | f                 | f                   | f
+ SET pg_stat_statements.track_utility = FALSE                     |     1 |    0 | f                 | f                   | t
+ UPDATE pgss_test SET b = $1 WHERE a = $2                         |     6 |    6 | t                 | t                   | t
+ UPDATE pgss_test SET b = $1 WHERE a > $2                         |     1 |    3 | t                 | t                   | t
+(11 rows)
+
 --
 -- pg_stat_statements.track = none
 --
@@ -287,13 +396,13 @@ SELECT PLUS_ONE(10);
        11
 (1 row)
 
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-               query               | calls | rows 
------------------------------------+-------+------
- SELECT $1::TEXT                   |     1 |    1
- SELECT PLUS_ONE($1)               |     2 |    2
- SELECT PLUS_TWO($1)               |     2 |    2
- SELECT pg_stat_statements_reset() |     1 |    1
+SELECT query, calls, rows, wal_write_bytes, wal_write_records FROM pg_stat_statements ORDER BY query COLLATE "C";
+               query               | calls | rows | wal_write_bytes | wal_write_records 
+-----------------------------------+-------+------+-----------------+-------------------
+ SELECT $1::TEXT                   |     1 |    1 |               0 |                 0
+ SELECT PLUS_ONE($1)               |     2 |    2 |               0 |                 0
+ SELECT PLUS_TWO($1)               |     2 |    2 |               0 |                 0
+ SELECT pg_stat_statements_reset() |     1 |    1 |               0 |                 0
 (4 rows)
 
 --
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql b/contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql
new file mode 100644
index 0000000000..f8b79f2277
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql
@@ -0,0 +1,50 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.8'" to load this file. \quit
+
+/* First we have to remove them from the extension */
+ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
+ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean);
+
+/* Then we can drop them */
+DROP VIEW pg_stat_statements;
+DROP FUNCTION pg_stat_statements(boolean);
+
+/* Now redefine */
+CREATE FUNCTION pg_stat_statements(IN showtext boolean,
+    OUT userid oid,
+    OUT dbid oid,
+    OUT queryid bigint,
+    OUT query text,
+    OUT calls int8,
+    OUT total_time float8,
+    OUT min_time float8,
+    OUT max_time float8,
+    OUT mean_time float8,
+    OUT stddev_time float8,
+    OUT rows int8,
+    OUT shared_blks_hit int8,
+    OUT shared_blks_read int8,
+    OUT shared_blks_dirtied int8,
+    OUT shared_blks_written int8,
+    OUT local_blks_hit int8,
+    OUT local_blks_read int8,
+    OUT local_blks_dirtied int8,
+    OUT local_blks_written int8,
+    OUT temp_blks_read int8,
+    OUT temp_blks_written int8,
+    OUT blk_read_time float8,
+    OUT blk_write_time float8,
+    OUT wal_write_bytes int8,
+    OUT wal_write_records int8,
+    OUT wal_write_fp_records int8
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_stat_statements_1_4'
+LANGUAGE C STRICT VOLATILE;
+
+CREATE VIEW pg_stat_statements AS
+  SELECT * FROM pg_stat_statements(true);
+
+GRANT SELECT ON pg_stat_statements TO PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 20dc8c605b..1c256fc395 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -120,7 +120,8 @@ typedef enum pgssVersion
 	PGSS_V1_0 = 0,
 	PGSS_V1_1,
 	PGSS_V1_2,
-	PGSS_V1_3
+	PGSS_V1_3,
+	PGSS_V1_4
 } pgssVersion;
 
 /*
@@ -161,6 +162,9 @@ typedef struct Counters
 	int64		local_blks_written; /* # of local disk blocks written */
 	int64		temp_blks_read; /* # of temp blocks read */
 	int64		temp_blks_written;	/* # of temp blocks written */
+	int64 		wal_bytes_written;		/* total amount of wal bytes written */
+	int64 		wal_records_written;	/* # of wal records written */
+	int64 		wal_fp_records_written; /* # of full page wal records written */
 	double		blk_read_time;	/* time spent reading, in msec */
 	double		blk_write_time; /* time spent writing, in msec */
 	double		usage;			/* usage factor */
@@ -293,6 +297,7 @@ PG_FUNCTION_INFO_V1(pg_stat_statements_reset);
 PG_FUNCTION_INFO_V1(pg_stat_statements_reset_1_7);
 PG_FUNCTION_INFO_V1(pg_stat_statements_1_2);
 PG_FUNCTION_INFO_V1(pg_stat_statements_1_3);
+PG_FUNCTION_INFO_V1(pg_stat_statements_1_4);
 PG_FUNCTION_INFO_V1(pg_stat_statements);
 
 static void pgss_shmem_startup(void);
@@ -313,6 +318,7 @@ static void pgss_store(const char *query, uint64 queryId,
 					   int query_location, int query_len,
 					   double total_time, uint64 rows,
 					   const BufferUsage *bufusage,
+					   const WalUsage* walusage,
 					   pgssJumbleState *jstate);
 static void pg_stat_statements_internal(FunctionCallInfo fcinfo,
 										pgssVersion api_version,
@@ -841,6 +847,7 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query)
 				   0,
 				   0,
 				   NULL,
+				   NULL,
 				   &jstate);
 }
 
@@ -944,6 +951,7 @@ pgss_ExecutorEnd(QueryDesc *queryDesc)
 				   queryDesc->totaltime->total * 1000.0,	/* convert to msec */
 				   queryDesc->estate->es_processed,
 				   &queryDesc->totaltime->bufusage,
+				   &queryDesc->totaltime->walusage,
 				   NULL);
 	}
 
@@ -989,7 +997,11 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
 		BufferUsage bufusage_start,
 					bufusage;
 
+		WalUsage	walusage_start,
+					walusage;
+
 		bufusage_start = pgBufferUsage;
+		walusage_start = pgWalUsage;
 		INSTR_TIME_SET_CURRENT(start);
 
 		nested_level++;
@@ -1041,6 +1053,13 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
 		bufusage.blk_write_time = pgBufferUsage.blk_write_time;
 		INSTR_TIME_SUBTRACT(bufusage.blk_write_time, bufusage_start.blk_write_time);
 
+		walusage.wal_bytes =
+			pgWalUsage.wal_bytes - walusage_start.wal_bytes;
+		walusage.wal_records =
+			pgWalUsage.wal_records - walusage_start.wal_records;
+		walusage.wal_fp_records =
+			pgWalUsage.wal_fp_records - walusage_start.wal_fp_records;
+
 		pgss_store(queryString,
 				   0,			/* signal that it's a utility stmt */
 				   pstmt->stmt_location,
@@ -1048,6 +1067,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
 				   INSTR_TIME_GET_MILLISEC(duration),
 				   rows,
 				   &bufusage,
+					&walusage,
 				   NULL);
 	}
 	else
@@ -1083,13 +1103,14 @@ pgss_hash_string(const char *str, int len)
  *
  * If jstate is not NULL then we're trying to create an entry for which
  * we have no statistics as yet; we just want to record the normalized
- * query string.  total_time, rows, bufusage are ignored in this case.
+ * query string.  Time and usage are ignored in this case.
  */
 static void
 pgss_store(const char *query, uint64 queryId,
 		   int query_location, int query_len,
 		   double total_time, uint64 rows,
 		   const BufferUsage *bufusage,
+		   const WalUsage* walusage,
 		   pgssJumbleState *jstate)
 {
 	pgssHashKey key;
@@ -1281,6 +1302,9 @@ pgss_store(const char *query, uint64 queryId,
 		e->counters.blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_read_time);
 		e->counters.blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_write_time);
 		e->counters.usage += USAGE_EXEC(total_time);
+		e->counters.wal_bytes_written += walusage->wal_bytes;
+		e->counters.wal_records_written += walusage->wal_records;
+		e->counters.wal_fp_records_written += walusage->wal_fp_records;
 
 		SpinLockRelease(&e->mutex);
 	}
@@ -1328,7 +1352,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
 #define PG_STAT_STATEMENTS_COLS_V1_1	18
 #define PG_STAT_STATEMENTS_COLS_V1_2	19
 #define PG_STAT_STATEMENTS_COLS_V1_3	23
-#define PG_STAT_STATEMENTS_COLS			23	/* maximum of above */
+#define PG_STAT_STATEMENTS_COLS_V1_4 	26
+#define PG_STAT_STATEMENTS_COLS			26	/* maximum of above */
 
 /*
  * Retrieve statement statistics.
@@ -1340,6 +1365,15 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
  * expected API version is identified by embedding it in the C name of the
  * function.  Unfortunately we weren't bright enough to do that for 1.1.
  */
+Datum pg_stat_statements_1_4(PG_FUNCTION_ARGS)
+{
+	bool showtext = PG_GETARG_BOOL(0);
+
+	pg_stat_statements_internal(fcinfo, PGSS_V1_4, showtext);
+
+	return (Datum)0;
+}
+
 Datum
 pg_stat_statements_1_3(PG_FUNCTION_ARGS)
 {
@@ -1445,6 +1479,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
 			if (api_version != PGSS_V1_3)
 				elog(ERROR, "incorrect number of output arguments");
 			break;
+		case PG_STAT_STATEMENTS_COLS_V1_4:
+			if (api_version != PGSS_V1_4)
+				elog(ERROR, "incorrect number of output arguments");
+			break;
 		default:
 			elog(ERROR, "incorrect number of output arguments");
 	}
@@ -1641,11 +1679,18 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
 			values[i++] = Float8GetDatumFast(tmp.blk_read_time);
 			values[i++] = Float8GetDatumFast(tmp.blk_write_time);
 		}
+		if (api_version >= PGSS_V1_4)
+		{
+			values[i++] = Int64GetDatumFast(tmp.wal_bytes_written);
+			values[i++] = Int64GetDatumFast(tmp.wal_records_written);
+			values[i++] = Int64GetDatumFast(tmp.wal_fp_records_written);
+		}
 
 		Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 :
 					 api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 :
 					 api_version == PGSS_V1_2 ? PG_STAT_STATEMENTS_COLS_V1_2 :
 					 api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 :
+					 api_version == PGSS_V1_4 ? PG_STAT_STATEMENTS_COLS_V1_4 :
 					 -1 /* fail if you forget to update this assert */ ));
 
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 14cb422354..7fb20df886 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.control
+++ b/contrib/pg_stat_statements/pg_stat_statements.control
@@ -1,5 +1,5 @@
 # pg_stat_statements extension
 comment = 'track execution statistics of all SQL statements executed'
-default_version = '1.7'
+default_version = '1.8'
 module_pathname = '$libdir/pg_stat_statements'
 relocatable = true
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index 8b527070d4..5184a4bbb0 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -99,7 +99,71 @@ SELECT * FROM test ORDER BY a;
 -- SELECT with IN clause
 SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5);
 
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+-- SELECT check WAL usage stats to confirm temp tables do not get stored in WAL
+SELECT query, calls, rows,
+wal_write_bytes, wal_write_records, wal_write_fp_records
+FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- CHECKPOINT before WAL tests to ensure test stability
+CHECKPOINT;
+
+--
+-- CRUD: INSERT SELECT UPDATE DELETE on test non-temp table to validate WAL generation metrics
+--
+SELECT pg_stat_statements_reset();
+
+-- utility "create table" should not be shown
+CREATE TABLE pgss_test (a int, b char(20));
+
+INSERT INTO pgss_test VALUES(generate_series(1, 10), 'aaa');
+UPDATE pgss_test SET b = 'bbb' WHERE a > 7;
+DELETE FROM pgss_test WHERE a > 9;
+
+-- explicit transaction
+BEGIN;
+UPDATE pgss_test SET b = '111' WHERE a = 1 ;
+COMMIT;
+
+BEGIN \;
+UPDATE pgss_test SET b = '222' WHERE a = 2 \;
+COMMIT ;
+
+UPDATE pgss_test SET b = '333' WHERE a = 3 \;
+UPDATE pgss_test SET b = '444' WHERE a = 4 ;
+
+BEGIN \;
+UPDATE pgss_test SET b = '555' WHERE a = 5 \;
+UPDATE pgss_test SET b = '666' WHERE a = 6 \;
+COMMIT ;
+
+-- many INSERT values
+INSERT INTO pgss_test (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c');
+
+-- SELECT with constants
+SELECT * FROM pgss_test WHERE a > 5 ORDER BY a ;
+
+SELECT *
+  FROM pgss_test
+  WHERE a > 9
+  ORDER BY a ;
+
+-- SELECT without constants
+SELECT * FROM pgss_test ORDER BY a;
+
+-- SELECT with IN clause
+SELECT * FROM pgss_test WHERE a IN (1, 2, 3, 4, 5);
+
+-- DROP test table
+SET pg_stat_statements.track_utility = TRUE;
+DROP TABLE pgss_test;
+SET pg_stat_statements.track_utility = FALSE;
+
+-- SELECT usage data, check WAL usage is reported, wal_write_records equal rows count for INSERT/UPDATE/DELETE
+SELECT query, calls, rows,
+wal_write_bytes > 0 as wal_bytes_written,
+wal_write_records > 0 as wal_records_written,
+wal_write_records = rows as wal_records_as_rows
+FROM pg_stat_statements ORDER BY query COLLATE "C";
 
 --
 -- pg_stat_statements.track = none
@@ -144,7 +208,7 @@ $$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
 SELECT PLUS_ONE(8);
 SELECT PLUS_ONE(10);
 
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT query, calls, rows, wal_write_bytes, wal_write_records FROM pg_stat_statements ORDER BY query COLLATE "C";
 
 --
 -- pg_stat_statements.track = all
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index 26bb82da4a..40e79f1866 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -221,6 +221,33 @@
       </entry>
      </row>
 
+     <row>
+      <entry><structfield>wal_write_bytes</structfield></entry>
+      <entry><type>bigint</type></entry>
+      <entry></entry>
+      <entry>
+        Total amount of WAL bytes generated by the statement
+      </entry>
+     </row>
+
+     <row>
+      <entry><structfield>wal_write_records</structfield></entry>
+      <entry><type>bigint</type></entry>
+      <entry></entry>
+      <entry>
+        Total count of WAL records generated by the statement
+      </entry>
+     </row>
+
+     <row>
+      <entry><structfield>wal_write_fp_records</structfield></entry>
+      <entry><type>bigint</type></entry>
+      <entry></entry>
+      <entry>
+        Total count of WAL full page images generated by the statement
+      </entry>
+     </row>
+
     </tbody>
    </tgroup>
   </table>
-- 
2.20.1

  [text/plain] v5-0003-Keep-track-of-auto-vacuum-WAL-usage-in-pg_stat_da.patch (13.1K, ../20200318171916.GA69354@nol/4-v5-0003-Keep-track-of-auto-vacuum-WAL-usage-in-pg_stat_da.patch)
  download | inline diff:
From e1e1d6d38895151d287515dafb9d9ee8f8cc912e Mon Sep 17 00:00:00 2001
From: Julien Rouhaud <julien.rouhaud@free.fr>
Date: Wed, 4 Mar 2020 20:09:22 +0100
Subject: [PATCH v5 3/3] Keep track of (auto)vacuum WAL usage in
 pg_stat_database.

---
 src/backend/catalog/system_views.sql |  6 ++
 src/backend/commands/vacuum.c        | 11 ++++
 src/backend/postmaster/pgstat.c      | 56 ++++++++++++++++
 src/backend/utils/adt/pgstatfuncs.c  | 96 ++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat      | 24 +++++++
 src/include/pgstat.h                 | 27 +++++++-
 src/test/regress/expected/rules.out  |  6 ++
 7 files changed, 225 insertions(+), 1 deletion(-)

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index b8a3f46912..a3d1ac2523 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -892,6 +892,12 @@ CREATE VIEW pg_stat_database AS
             pg_stat_get_db_checksum_last_failure(D.oid) AS checksum_last_failure,
             pg_stat_get_db_blk_read_time(D.oid) AS blk_read_time,
             pg_stat_get_db_blk_write_time(D.oid) AS blk_write_time,
+            pg_stat_get_db_vac_wal_records(D.oid) AS vac_wal_records,
+            pg_stat_get_db_vac_wal_fp_records(D.oid) AS vac_wal_fp_records,
+            pg_stat_get_db_vac_wal_bytes(D.oid) AS vac_wal_bytes,
+            pg_stat_get_db_autovac_wal_records(D.oid) AS autovac_wal_records,
+            pg_stat_get_db_autovac_wal_fp_records(D.oid) AS autovac_wal_fp_records,
+            pg_stat_get_db_autovac_wal_bytes(D.oid) AS autovac_wal_bytes,
             pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
     FROM (
         SELECT 0 AS oid, NULL::name AS datname
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index d625d17bf4..14a235a2ed 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -275,6 +275,8 @@ vacuum(List *relations, VacuumParams *params,
 	   BufferAccessStrategy bstrategy, bool isTopLevel)
 {
 	static bool in_vacuum = false;
+	WalUsage walusage_start = pgWalUsage;
+	WalUsage walusage = {0, 0, 0};
 
 	const char *stmttype;
 	volatile bool in_outer_xact,
@@ -489,6 +491,15 @@ vacuum(List *relations, VacuumParams *params,
 	{
 		in_vacuum = false;
 		VacuumCostActive = false;
+		walusage.wal_records = pgWalUsage.wal_records -
+			walusage_start.wal_records;
+		walusage.wal_fp_records = pgWalUsage.wal_fp_records -
+			walusage_start.wal_fp_records;
+		walusage.wal_bytes = pgWalUsage.wal_bytes - walusage_start.wal_bytes;
+		pgstat_report_vac_wal_usage(walusage.wal_records,
+									walusage.wal_fp_records,
+									walusage.wal_bytes,
+									IsAutoVacuumWorkerProcess());
 	}
 	PG_END_TRY();
 
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index f9287b7942..c9d65669ce 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -330,6 +330,7 @@ static void pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int le
 static void pgstat_recv_deadlock(PgStat_MsgDeadlock *msg, int len);
 static void pgstat_recv_checksum_failure(PgStat_MsgChecksumFailure *msg, int len);
 static void pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len);
+static void pgstat_recv_vac_walusage(PgStat_MsgVacWalUsage *msg, int len);
 
 /* ------------------------------------------------------------
  * Public functions called from postmaster follow
@@ -1572,6 +1573,30 @@ pgstat_report_tempfile(size_t filesize)
 	pgstat_send(&msg, sizeof(msg));
 }
 
+/* --------
+ * pgstat_report_vac_wal_usage() -
+ *
+ *	Tell the collector about (auto)vacuum WAL usage.
+ * --------
+ */
+void
+pgstat_report_vac_wal_usage(long wal_records, long wal_fp_records,
+							long wal_bytes, bool autovacuum)
+{
+	PgStat_MsgVacWalUsage msg;
+
+	if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_counts)
+		return;
+
+	pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_VACWALUSAGE);
+	msg.m_databaseid = MyDatabaseId;
+	msg.m_wal_records = wal_records;
+	msg.m_wal_fp_records = wal_fp_records;
+	msg.m_wal_bytes = wal_bytes;
+	msg.m_autovacuum = autovacuum;
+	pgstat_send(&msg, sizeof(msg));
+}
+
 
 /* ----------
  * pgstat_ping() -
@@ -4525,6 +4550,10 @@ PgstatCollectorMain(int argc, char *argv[])
 					pgstat_recv_tempfile(&msg.msg_tempfile, len);
 					break;
 
+				case PGSTAT_MTYPE_VACWALUSAGE:
+					pgstat_recv_vac_walusage(&msg.msg_vac_walusage, len);
+					break;
+
 				case PGSTAT_MTYPE_CHECKSUMFAILURE:
 					pgstat_recv_checksum_failure(&msg.msg_checksumfailure,
 												 len);
@@ -6282,6 +6311,33 @@ pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len)
 	dbentry->n_temp_files += 1;
 }
 
+/* ----------
+ * pgstat_recv_vac_walusage() -
+ *
+ *	Process a VACWALUSAGE message.
+ * ----------
+ */
+static void
+pgstat_recv_vac_walusage(PgStat_MsgVacWalUsage *msg, int len)
+{
+	PgStat_StatDBEntry *dbentry;
+
+	dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
+
+	if (msg->m_autovacuum)
+	{
+		dbentry->n_autovac_wal_records += msg->m_wal_records;
+		dbentry->n_autovac_wal_fp_records += msg->m_wal_fp_records;
+		dbentry->autovac_wal_bytes += msg->m_wal_bytes;
+	}
+	else
+	{
+		dbentry->n_vac_wal_records += msg->m_wal_records;
+		dbentry->n_vac_wal_fp_records += msg->m_wal_fp_records;
+		dbentry->vac_wal_bytes += msg->m_wal_bytes;
+	}
+}
+
 /* ----------
  * pgstat_recv_funcstat() -
  *
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index cea01534a5..1bc7291c2b 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1606,6 +1606,102 @@ pg_stat_get_db_blk_write_time(PG_FUNCTION_ARGS)
 	PG_RETURN_FLOAT8(result);
 }
 
+Datum
+pg_stat_get_db_vac_wal_records(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	/* convert counter from microsec to millisec for display */
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_vac_wal_records);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_vac_wal_fp_records(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	/* convert counter from microsec to millisec for display */
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_vac_wal_fp_records);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_vac_wal_bytes(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	/* convert counter from microsec to millisec for display */
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->vac_wal_bytes);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_autovac_wal_records(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	/* convert counter from microsec to millisec for display */
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_autovac_wal_records);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_autovac_wal_fp_records(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	/* convert counter from microsec to millisec for display */
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_autovac_wal_fp_records);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_autovac_wal_bytes(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	/* convert counter from microsec to millisec for display */
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->autovac_wal_bytes);
+
+	PG_RETURN_INT64(result);
+}
+
 Datum
 pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7fb574f9dc..8b1c1487ca 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5372,6 +5372,30 @@
   proname => 'pg_stat_get_db_blk_write_time', provolatile => 's',
   proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
   prosrc => 'pg_stat_get_db_blk_write_time' },
+{ oid => '8176', descr => 'statistics: number of vacuum wal records',
+  proname => 'pg_stat_get_db_vac_wal_records', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_vac_wal_records' },
+{ oid => '8177', descr => 'statistics: number of vacuum wal full page records',
+  proname => 'pg_stat_get_db_vac_wal_fp_records', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_vac_wal_fp_records' },
+{ oid => '8178', descr => 'statistics: number of vacuum wal bytes',
+  proname => 'pg_stat_get_db_vac_wal_bytes', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_vac_wal_bytes' },
+{ oid => '8179', descr => 'statistics: number of autovacuum wal records',
+  proname => 'pg_stat_get_db_autovac_wal_records', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_autovac_wal_records' },
+{ oid => '8180', descr => 'statistics: number of autovacuum wal full page records',
+  proname => 'pg_stat_get_db_autovac_wal_fp_records', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_autovac_wal_fp_records' },
+{ oid => '8181', descr => 'statistics: number of autovacuum wal bytes',
+  proname => 'pg_stat_get_db_autovac_wal_bytes', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_autovac_wal_bytes' },
 { oid => '3195', descr => 'statistics: information about WAL archiver',
   proname => 'pg_stat_get_archiver', proisstrict => 'f', provolatile => 's',
   proparallel => 'r', prorettype => 'record', proargtypes => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 1a19921f80..40fb97bdc9 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -65,7 +65,8 @@ typedef enum StatMsgType
 	PGSTAT_MTYPE_RECOVERYCONFLICT,
 	PGSTAT_MTYPE_TEMPFILE,
 	PGSTAT_MTYPE_DEADLOCK,
-	PGSTAT_MTYPE_CHECKSUMFAILURE
+	PGSTAT_MTYPE_CHECKSUMFAILURE,
+	PGSTAT_MTYPE_VACWALUSAGE
 } StatMsgType;
 
 /* ----------
@@ -544,6 +545,21 @@ typedef struct PgStat_MsgChecksumFailure
 	TimestampTz m_failure_time;
 } PgStat_MsgChecksumFailure;
 
+/* ----------
+ * PgStat_MsgVacWalUsage	Sent by the backend to tell the collector
+ *								about (auto)vacuum WAL usage.
+ * ----------
+ */
+typedef struct PgStat_MsgVacWalUsage
+{
+	PgStat_MsgHdr m_hdr;
+	Oid			m_databaseid;
+	long		m_wal_records;
+	long		m_wal_fp_records;
+	long		m_wal_bytes;
+	bool		m_autovacuum;
+} PgStat_MsgVacWalUsage;
+
 
 /* ----------
  * PgStat_Msg					Union over all possible messages.
@@ -571,6 +587,7 @@ typedef union PgStat_Msg
 	PgStat_MsgDeadlock msg_deadlock;
 	PgStat_MsgTempFile msg_tempfile;
 	PgStat_MsgChecksumFailure msg_checksumfailure;
+	PgStat_MsgVacWalUsage msg_vac_walusage;
 } PgStat_Msg;
 
 
@@ -613,6 +630,12 @@ typedef struct PgStat_StatDBEntry
 	TimestampTz last_checksum_failure;
 	PgStat_Counter n_block_read_time;	/* times in microseconds */
 	PgStat_Counter n_block_write_time;
+	PgStat_Counter n_vac_wal_records;
+	PgStat_Counter n_vac_wal_fp_records;
+	PgStat_Counter vac_wal_bytes;
+	PgStat_Counter n_autovac_wal_records;
+	PgStat_Counter n_autovac_wal_fp_records;
+	PgStat_Counter autovac_wal_bytes;
 
 	TimestampTz stat_reset_timestamp;
 	TimestampTz stats_timestamp;	/* time of db stats file update */
@@ -1261,6 +1284,8 @@ extern void pgstat_bestart(void);
 
 extern void pgstat_report_activity(BackendState state, const char *cmd_str);
 extern void pgstat_report_tempfile(size_t filesize);
+extern void pgstat_report_vac_wal_usage(long wal_records, long wal_fp_record,
+										long wal_bytes, bool autovacuum);
 extern void pgstat_report_appname(const char *appname);
 extern void pgstat_report_xact_timestamp(TimestampTz tstamp);
 extern const char *pgstat_get_wait_event(uint32 wait_event_info);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index c7304611c3..aa311cd594 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1833,6 +1833,12 @@ pg_stat_database| SELECT d.oid AS datid,
     pg_stat_get_db_checksum_last_failure(d.oid) AS checksum_last_failure,
     pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time,
     pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time,
+    pg_stat_get_db_vac_wal_records(d.oid) AS vac_wal_records,
+    pg_stat_get_db_vac_wal_fp_records(d.oid) AS vac_wal_fp_records,
+    pg_stat_get_db_vac_wal_bytes(d.oid) AS vac_wal_bytes,
+    pg_stat_get_db_autovac_wal_records(d.oid) AS autovac_wal_records,
+    pg_stat_get_db_autovac_wal_fp_records(d.oid) AS autovac_wal_fp_records,
+    pg_stat_get_db_autovac_wal_bytes(d.oid) AS autovac_wal_bytes,
     pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset
    FROM ( SELECT 0 AS oid,
             NULL::name AS datname
-- 
2.20.1

view thread (130+ messages)  latest in thread

Message-ID: <20200318171916.GA69354@nol>
Permalink:  ../20200318171916.GA69354@nol/
Also on:    postgresql.org/message-id/20200318171916.GA69354@nol

copy link · copy postgr.es · copy git am

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-hackers@postgresql.org
  Cc: rjuju123@gmail.com, kirill.bychik@gmail.com, thomas.munro@gmail.com
  Subject: Re: WAL usage calculation patch
  In-Reply-To: <20200318171916.GA69354@nol>

* 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