v2-0001-Add-IO-wait-events-for-COPY-file-program-operations.patch

From 54a3b83f47e29181a061dea9d92f82c602bbdd2e Mon Sep 17 00:00:00 2001
From: Nik Samokhvalov <nik@postgres.ai>
Date: Thu, 8 Jan 2026 13:50:53 -0800
Subject: [PATCH v2] Add IO wait events for COPY file/program operations

Add two new IO wait events:
- COPY_FROM_READ: COPY FROM blocking on file or program read
- COPY_TO_WRITE: COPY TO blocking on file or program write

This enables diagnosing:
- Storage I/O bottlenecks during bulk loads (COPY FROM '/path/to/file')
- Slow exports to files (COPY TO '/path/to/file')
- Pipe buffer congestion in ETL pipelines (COPY FROM/TO PROGRAM)

COPY FROM/TO STDIN/STDOUT already have coverage via Client/ClientRead
and Client/ClientWrite at the protocol layer.

These events are distinct from the existing COPY_FILE_READ/WRITE events,
which instrument file-to-file copy operations in basebackup code.

v2: Renamed COPY_DATA_READ/WRITE to COPY_FROM_READ/COPY_TO_WRITE per
    feedback from Sami Imseih, Dilip Kumar, and Michael Paquier.

Example usage:

  -- Session 1:
  COPY large_table TO '/slow/nfs/mount/out.csv';

  -- Session 2:
  SELECT wait_event, wait_event_type
  FROM pg_stat_activity
  WHERE pid = <session1_pid>;
  -- Shows: COPY_TO_WRITE / IO
---
 src/backend/commands/copyfromparse.c            | 2 ++
 src/backend/commands/copyto.c                   | 2 ++
 src/backend/utils/activity/wait_event_names.txt | 2 ++
 3 files changed, 6 insertions(+)

diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 5868a7fa11f..af1df0c1f14 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -249,7 +249,9 @@ CopyGetData(CopyFromState cstate, void *databuf, int minread, int maxread)
 	switch (cstate->copy_src)
 	{
 		case COPY_FILE:
+			pgstat_report_wait_start(WAIT_EVENT_COPY_FROM_READ);
 			bytesread = fread(databuf, 1, maxread, cstate->copy_file);
+			pgstat_report_wait_end();
 			if (ferror(cstate->copy_file))
 				ereport(ERROR,
 						(errcode_for_file_access(),
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 4ab4a3893d5..7d146e95b2d 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -454,6 +454,7 @@ CopySendEndOfRow(CopyToState cstate)
 	switch (cstate->copy_dest)
 	{
 		case COPY_FILE:
+			pgstat_report_wait_start(WAIT_EVENT_COPY_TO_WRITE);
 			if (fwrite(fe_msgbuf->data, fe_msgbuf->len, 1,
 					   cstate->copy_file) != 1 ||
 				ferror(cstate->copy_file))
@@ -486,6 +487,7 @@ CopySendEndOfRow(CopyToState cstate)
 							(errcode_for_file_access(),
 							 errmsg("could not write to COPY file: %m")));
 			}
+			pgstat_report_wait_end();
 			break;
 		case COPY_FRONTEND:
 			/* Dump the accumulated row as one CopyData message */
diff --git a/src/backend/utils/activity/wait_event_names.txt b/src/backend/utils/activity/wait_event_names.txt
index 3299de23bb3..a7e4d5182a9 100644
--- a/src/backend/utils/activity/wait_event_names.txt
+++ b/src/backend/utils/activity/wait_event_names.txt
@@ -210,6 +210,8 @@ CONTROL_FILE_SYNC	"Waiting for the <filename>pg_control</filename> file to reach
 CONTROL_FILE_SYNC_UPDATE	"Waiting for an update to the <filename>pg_control</filename> file to reach durable storage."
 CONTROL_FILE_WRITE	"Waiting for a write to the <filename>pg_control</filename> file."
 CONTROL_FILE_WRITE_UPDATE	"Waiting for a write to update the <filename>pg_control</filename> file."
+COPY_FROM_READ	"Waiting to read data from a file or program during COPY FROM."
+COPY_TO_WRITE	"Waiting to write data to a file or program during COPY TO."
 COPY_FILE_COPY	"Waiting for a file copy operation."
 COPY_FILE_READ	"Waiting for a read during a file copy operation."
 COPY_FILE_WRITE	"Waiting for a write during a file copy operation."
-- 
2.50.1 (Apple Git-155)