public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrey Borodin <[email protected]>
To: Andres Freund <[email protected]>
Cc: Heikki Linnakangas <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: Melanie Plageman <[email protected]>
Subject: Re: Relation bulk write facility
Date: Wed, 1 Jul 2026 19:18:12 +0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
Hi Andres,
On 2023-11-19, Andres Freund wrote:
> One thing I'd like to use the centralized handling for is to track
> such writes in pg_stat_io. I don't mean as part of the initial patch,
> just that that's another reason I like the facility.
Coming back to this idea. Today the writes that build new relation
storage through bulk_write.c - a CREATE INDEX, a CLUSTER / VACUUM FULL
heap rewrite, ALTER TABLE SET TABLESPACE - go straight to smgrextend() /
smgrwrite() and never show up in pg_stat_io. So a plain CREATE INDEX on
a large table can write gigabytes that are completely invisible there.
The nearest trace is the fsync, and even that is partial: in the common
case the sync is deferred and the checkpointer's fsync shows up under
the normal context (attributed to the checkpointer, not to the backend
that did the build); and if a checkpoint runs concurrently the backend
does smgrimmedsync() instead, which is not counted anywhere at all.
This was foreseen while pg_stat_io was being built: the io_object column
was added to "pave the way for bypass IO" [0], and counting such writes
was deliberately deferred to a future central point (the smgr
wrappers discussed back then, later was implemented as Heikki's bulk
write facility). bulk_write.c now is exactly that point.
PFA a patch that does what was planned: it accounts for those writes
and extends from the one central place, smgr_bulk_flush(), in a new
IOCONTEXT_BYPASS context. The fsync path is left untouched (more on that
in point 3 below).
A few points open for discussion:
1. The context name. I went with "bypass", matching the language already
used in the docs ("bypasses shared buffers") and in this thread. It
also keeps it clearly apart from the strategy-ring "bulkwrite"
(BAS_BULKWRITE), which is a different thing. I am not attached to the
name, so other suggestions are welcome.
2. Scope. This patch tracks the write side only. The read side that
also bypasses shared buffers (e.g. the smgrread() in
RelationCopyStorage for a tablespace move) stays untracked; I left it
out to keep the first cut small, but it could be added the same way.
3. The fsync. The deferred sync is already visible (the checkpointer
counts it under normal); the immediate one - smgrimmedsync(), taken
when a checkpoint intervenes during the build - is counted nowhere.
md.c actually anticipates exactly this: a comment in mdimmedsync()
says such backend fsyncs should be tracked in a separate IOContext
from the checkpointer's, but that it was waiting until other IO that
bypasses the buffer manager is tracked too. This patch is that
prerequisite, so counting smgrimmedsync() under the new context is
the obvious next step; I left it out of this first cut to keep it
focused.
Temporary file I/O (sort/tuplestore spills, hash agg/join batches) is a
separate and larger story - it is non-block-oriented and goes through
buffile.c, not smgr, so it is out of scope of this patch. That was anticipated
when pg_stat_io was designed (the columns were left unprefixed to allow
non-block-oriented I/O later [1]); it would be a follow-up of its own.
Timing is counted as in the buffered paths (pgstat_prepare_io_time /
pgstat_count_io_op_time), so the *_time columns work when
track_io_timing is on.
WDYT?
Thank you!
Best regards, Andrey Borodin.
[0] https://www.postgresql.org/message-id/CAOtHd0ApHna7_p6mvHoO%2BgLZdxjaQPRemg3_o0a4ytCPijLytQ%40mail.g...
[1] https://www.postgresql.org/message-id/CAAKRu_ZiLuEPANqsHqqRPbgt4BTmgMqtPpyJJaTQxLs818tvKg%40mail.gma...
Attachments:
[application/octet-stream] 0001-Track-relation-writes-that-bypass-shared-buffers-in-.patch (13.9K, ../[email protected]/2-0001-Track-relation-writes-that-bypass-shared-buffers-in-.patch)
download | inline diff:
From 74d1d60db67b817ebb2dd73b64ae6fecab4d468f Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email protected]>
Date: Tue, 30 Jun 2026 18:44:02 +0500
Subject: [PATCH] Track relation writes that bypass shared buffers in
pg_stat_io
Operations that populate new relation storage via the bulk write facility
(bulk_write.c) -- building a B-tree index, a CLUSTER or VACUUM FULL heap
rewrite, ALTER TABLE SET TABLESPACE, and so on -- write pages by calling
smgrextend()/smgrwrite() directly, bypassing shared buffers. Until now
those writes were invisible in pg_stat_io; only the eventual fsync was
counted, under IOCONTEXT_NORMAL like all relation fsyncs.
Count these writes and extends in a new IOCONTEXT_BYPASS context so that
the I/O done while building, for example, a B-tree index is observable.
The matching fsync is unchanged and remains counted under IOCONTEXT_NORMAL.
---
doc/src/sgml/monitoring.sgml | 18 ++++++++++--
src/backend/storage/smgr/bulk_write.c | 27 ++++++++++++++++++
src/backend/utils/activity/pgstat_io.c | 22 +++++++++++++--
src/include/pgstat.h | 3 +-
src/test/regress/expected/stats.out | 39 +++++++++++++++++++++++++-
src/test/regress/sql/stats.sql | 12 ++++++++
6 files changed, 113 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 08d5b824552..d9fe39e9886 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2882,9 +2882,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
Currently, I/O on relations (e.g. tables, indexes) and WAL activity are
- tracked. However, relation I/O which bypasses shared buffers
- (e.g. when moving a table from one tablespace to another) is currently
- not tracked.
+ tracked. Writes that bypass shared buffers while building new relation
+ storage are tracked in the <literal>bypass</literal>
+ <varname>context</varname>. However, some relation I/O which bypasses
+ shared buffers is still not tracked, such as the reads performed when
+ moving a table from one tablespace to another.
</para>
<table id="pg-stat-io-view" xreflabel="pg_stat_io">
@@ -2995,6 +2997,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
done outside of shared buffers, such as <command>COPY</command>.
</para>
</listitem>
+ <listitem>
+ <para>
+ <literal>bypass</literal>: Write I/O operations that populate
+ newly built relation storage by bypassing shared buffers, such as
+ building a B-tree index or rewriting a table's heap during
+ <command>VACUUM FULL</command> or <command>CLUSTER</command>. The
+ associated <literal>fsync</literal> is counted separately in
+ <varname>context</varname> <literal>normal</literal>.
+ </para>
+ </listitem>
</itemizedlist>
</entry>
</row>
diff --git a/src/backend/storage/smgr/bulk_write.c b/src/backend/storage/smgr/bulk_write.c
index f3c24082a69..6e502e9d766 100644
--- a/src/backend/storage/smgr/bulk_write.c
+++ b/src/backend/storage/smgr/bulk_write.c
@@ -38,6 +38,8 @@
#include "access/xloginsert.h"
#include "access/xlogrecord.h"
+#include "pgstat.h"
+#include "storage/bufmgr.h"
#include "storage/bufpage.h"
#include "storage/bulk_write.h"
#include "storage/proc.h"
@@ -243,10 +245,21 @@ smgr_bulk_flush(BulkWriteState *bulkstate)
{
int npending = bulkstate->npending;
PendingWrite *pending_writes = bulkstate->pending_writes;
+ IOObject io_object;
if (npending == 0)
return;
+ /*
+ * These writes bypass shared buffers, so they are not accounted for by
+ * the buffer manager. Count them in pg_stat_io ourselves, under the
+ * dedicated IOCONTEXT_BYPASS context. The matching fsync happens
+ * separately (see smgr_bulk_finish()) and is counted under
+ * IOCONTEXT_NORMAL, like all relation fsyncs.
+ */
+ io_object = SmgrIsTemp(bulkstate->smgr) ?
+ IOOBJECT_TEMP_RELATION : IOOBJECT_RELATION;
+
if (npending > 1)
qsort(pending_writes, npending, sizeof(PendingWrite), buffer_cmp);
@@ -283,6 +296,8 @@ smgr_bulk_flush(BulkWriteState *bulkstate)
if (blkno >= bulkstate->relsize)
{
+ instr_time io_start;
+
/*
* If we have to write pages nonsequentially, fill in the space
* with zeroes until we come back and overwrite. This is not
@@ -292,19 +307,31 @@ smgr_bulk_flush(BulkWriteState *bulkstate)
*/
while (blkno > bulkstate->relsize)
{
+ io_start = pgstat_prepare_io_time(track_io_timing);
/* don't set checksum for all-zero page */
smgrextend(bulkstate->smgr, bulkstate->forknum,
bulkstate->relsize,
&zero_buffer,
true);
+ pgstat_count_io_op_time(io_object, IOCONTEXT_BYPASS,
+ IOOP_EXTEND, io_start, 1, BLCKSZ);
bulkstate->relsize++;
}
+ io_start = pgstat_prepare_io_time(track_io_timing);
smgrextend(bulkstate->smgr, bulkstate->forknum, blkno, page, true);
+ pgstat_count_io_op_time(io_object, IOCONTEXT_BYPASS,
+ IOOP_EXTEND, io_start, 1, BLCKSZ);
bulkstate->relsize++;
}
else
+ {
+ instr_time io_start = pgstat_prepare_io_time(track_io_timing);
+
smgrwrite(bulkstate->smgr, bulkstate->forknum, blkno, page, true);
+ pgstat_count_io_op_time(io_object, IOCONTEXT_BYPASS,
+ IOOP_WRITE, io_start, 1, BLCKSZ);
+ }
pfree(page);
}
diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/activity/pgstat_io.c
index 38bae7b15d2..58d8102dee4 100644
--- a/src/backend/utils/activity/pgstat_io.c
+++ b/src/backend/utils/activity/pgstat_io.c
@@ -245,6 +245,8 @@ pgstat_get_io_context_name(IOContext io_context)
return "bulkread";
case IOCONTEXT_BULKWRITE:
return "bulkwrite";
+ case IOCONTEXT_BYPASS:
+ return "bypass";
case IOCONTEXT_INIT:
return "init";
case IOCONTEXT_NORMAL:
@@ -414,9 +416,10 @@ pgstat_tracks_io_object(BackendType bktype, IOObject io_object,
/*
* Currently, IO on temporary relations can only occur in the
- * IOCONTEXT_NORMAL IOContext.
+ * IOCONTEXT_NORMAL and IOCONTEXT_BYPASS IOContexts.
*/
if (io_context != IOCONTEXT_NORMAL &&
+ io_context != IOCONTEXT_BYPASS &&
io_object == IOOBJECT_TEMP_RELATION)
return false;
@@ -434,7 +437,8 @@ pgstat_tracks_io_object(BackendType bktype, IOObject io_object,
bktype == B_WAL_SUMMARIZER || bktype == B_WAL_WRITER ||
bktype == B_WAL_RECEIVER;
- if (no_temp_rel && io_context == IOCONTEXT_NORMAL &&
+ if (no_temp_rel &&
+ (io_context == IOCONTEXT_NORMAL || io_context == IOCONTEXT_BYPASS) &&
io_object == IOOBJECT_TEMP_RELATION)
return false;
@@ -454,6 +458,7 @@ pgstat_tracks_io_object(BackendType bktype, IOObject io_object,
if ((bktype == B_CHECKPOINTER || bktype == B_BG_WRITER) &&
(io_context == IOCONTEXT_BULKREAD ||
io_context == IOCONTEXT_BULKWRITE ||
+ io_context == IOCONTEXT_BYPASS ||
io_context == IOCONTEXT_VACUUM))
return false;
@@ -461,7 +466,8 @@ pgstat_tracks_io_object(BackendType bktype, IOObject io_object,
return false;
if ((bktype == B_AUTOVAC_WORKER || bktype == B_AUTOVAC_LAUNCHER) &&
- io_context == IOCONTEXT_BULKWRITE)
+ (io_context == IOCONTEXT_BULKWRITE ||
+ io_context == IOCONTEXT_BYPASS))
return false;
return true;
@@ -525,6 +531,16 @@ pgstat_tracks_io_op(BackendType bktype, IOObject io_object,
if (io_context == IOCONTEXT_BULKREAD && io_op == IOOP_EXTEND)
return false;
+ /*
+ * IOCONTEXT_BYPASS covers relation data written by bypassing shared
+ * buffers (see bulk_write.c), which only ever extends or overwrites
+ * relation blocks. The matching fsync is counted separately under
+ * IOCONTEXT_NORMAL (see below).
+ */
+ if (io_context == IOCONTEXT_BYPASS &&
+ !(io_op == IOOP_EXTEND || io_op == IOOP_WRITE))
+ return false;
+
strategy_io_context = io_context == IOCONTEXT_BULKREAD ||
io_context == IOCONTEXT_BULKWRITE || io_context == IOCONTEXT_VACUUM;
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index dfa2e837638..3df2156470a 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -218,7 +218,7 @@ typedef struct PgStat_TableXactStatus
* ------------------------------------------------------------
*/
-#define PGSTAT_FILE_FORMAT_ID 0x01A5BCBC
+#define PGSTAT_FILE_FORMAT_ID 0x01A5BCBD
typedef struct PgStat_ArchiverStats
{
@@ -289,6 +289,7 @@ typedef enum IOContext
{
IOCONTEXT_BULKREAD,
IOCONTEXT_BULKWRITE,
+ IOCONTEXT_BYPASS,
IOCONTEXT_INIT,
IOCONTEXT_NORMAL,
IOCONTEXT_VACUUM,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index bbb1db3c433..80e8af9aae5 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -29,9 +29,11 @@ autovacuum worker|wal|init
autovacuum worker|wal|normal
background worker|relation|bulkread
background worker|relation|bulkwrite
+background worker|relation|bypass
background worker|relation|init
background worker|relation|normal
background worker|relation|vacuum
+background worker|temp relation|bypass
background worker|temp relation|normal
background worker|wal|init
background worker|wal|normal
@@ -45,46 +47,57 @@ checkpointer|wal|init
checkpointer|wal|normal
client backend|relation|bulkread
client backend|relation|bulkwrite
+client backend|relation|bypass
client backend|relation|init
client backend|relation|normal
client backend|relation|vacuum
+client backend|temp relation|bypass
client backend|temp relation|normal
client backend|wal|init
client backend|wal|normal
datachecksums launcher|relation|bulkread
datachecksums launcher|relation|bulkwrite
+datachecksums launcher|relation|bypass
datachecksums launcher|relation|init
datachecksums launcher|relation|normal
datachecksums launcher|relation|vacuum
+datachecksums launcher|temp relation|bypass
datachecksums launcher|temp relation|normal
datachecksums launcher|wal|init
datachecksums launcher|wal|normal
datachecksums worker|relation|bulkread
datachecksums worker|relation|bulkwrite
+datachecksums worker|relation|bypass
datachecksums worker|relation|init
datachecksums worker|relation|normal
datachecksums worker|relation|vacuum
+datachecksums worker|temp relation|bypass
datachecksums worker|temp relation|normal
datachecksums worker|wal|init
datachecksums worker|wal|normal
io worker|relation|bulkread
io worker|relation|bulkwrite
+io worker|relation|bypass
io worker|relation|init
io worker|relation|normal
io worker|relation|vacuum
+io worker|temp relation|bypass
io worker|temp relation|normal
io worker|wal|init
io worker|wal|normal
slotsync worker|relation|bulkread
slotsync worker|relation|bulkwrite
+slotsync worker|relation|bypass
slotsync worker|relation|init
slotsync worker|relation|normal
slotsync worker|relation|vacuum
+slotsync worker|temp relation|bypass
slotsync worker|temp relation|normal
slotsync worker|wal|init
slotsync worker|wal|normal
standalone backend|relation|bulkread
standalone backend|relation|bulkwrite
+standalone backend|relation|bypass
standalone backend|relation|init
standalone backend|relation|normal
standalone backend|relation|vacuum
@@ -92,6 +105,7 @@ standalone backend|wal|init
standalone backend|wal|normal
startup|relation|bulkread
startup|relation|bulkwrite
+startup|relation|bypass
startup|relation|init
startup|relation|normal
startup|relation|vacuum
@@ -101,9 +115,11 @@ walreceiver|wal|init
walreceiver|wal|normal
walsender|relation|bulkread
walsender|relation|bulkwrite
+walsender|relation|bypass
walsender|relation|init
walsender|relation|normal
walsender|relation|vacuum
+walsender|temp relation|bypass
walsender|temp relation|normal
walsender|wal|init
walsender|wal|normal
@@ -111,7 +127,7 @@ walsummarizer|wal|init
walsummarizer|wal|normal
walwriter|wal|init
walwriter|wal|normal
-(95 rows)
+(111 rows)
\a
-- ensure that both seqscan and indexscan plans are allowed
SET enable_seqscan TO on;
@@ -1798,6 +1814,27 @@ SELECT :io_sum_bulkwrite_strategy_extends_after > :io_sum_bulkwrite_strategy_ext
t
(1 row)
+-- Test that writes done while building a relation by bypassing shared buffers
+-- (here, building a B-tree index) are tracked in pg_stat_io in the bypass
+-- IOContext.
+SELECT sum(extends) AS io_sum_bypass_extends_before
+ FROM pg_stat_io WHERE context = 'bypass' AND object = 'relation' \gset
+CREATE INDEX test_io_bulkwrite_strategy_idx
+ ON test_io_bulkwrite_strategy (i);
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT sum(extends) AS io_sum_bypass_extends_after
+ FROM pg_stat_io WHERE context = 'bypass' AND object = 'relation' \gset
+SELECT :io_sum_bypass_extends_after > :io_sum_bypass_extends_before;
+ ?column?
+----------
+ t
+(1 row)
+
-- Test IO stats reset
SELECT pg_stat_have_stats('io', 0, 0);
pg_stat_have_stats
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 610fd21fae4..7fa67c98c4a 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -849,6 +849,18 @@ SELECT sum(extends) AS io_sum_bulkwrite_strategy_extends_after
FROM pg_stat_io WHERE context = 'bulkwrite' \gset
SELECT :io_sum_bulkwrite_strategy_extends_after > :io_sum_bulkwrite_strategy_extends_before;
+-- Test that writes done while building a relation by bypassing shared buffers
+-- (here, building a B-tree index) are tracked in pg_stat_io in the bypass
+-- IOContext.
+SELECT sum(extends) AS io_sum_bypass_extends_before
+ FROM pg_stat_io WHERE context = 'bypass' AND object = 'relation' \gset
+CREATE INDEX test_io_bulkwrite_strategy_idx
+ ON test_io_bulkwrite_strategy (i);
+SELECT pg_stat_force_next_flush();
+SELECT sum(extends) AS io_sum_bypass_extends_after
+ FROM pg_stat_io WHERE context = 'bypass' AND object = 'relation' \gset
+SELECT :io_sum_bypass_extends_after > :io_sum_bypass_extends_before;
+
-- Test IO stats reset
SELECT pg_stat_have_stats('io', 0, 0);
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_pre_reset
--
2.50.1 (Apple Git-155)
view thread (5+ messages)
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Relation bulk write facility
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox