Hi, On Fri, Jan 16, 2026 at 10:44:48AM -0600, Sami Imseih wrote: > I took a look at 0001 in depth.
Thanks! > > I don't think this feature could add a noticeable performance impact, so > > the tests > > have been that simple. Do you think we should worry more? > > One observation is there's no coordination between ANYTIME and > TXN_BOUNDARY flushes. While PGSTAT_MIN_INTERVAL > prevents a backend from flushing more than once per second, a backend can > still perform both an ANYTIME flush and a TXN_BOUNDARY flush within > the same 1-second window. Not saying this will be a real problem in > the real-world, > but we definitely took measures in the current implementation to avoid > this scenario. Right. I think that the PGSTAT_MIN_INTERVAL throttling was put in place to prevent flushing too frequently when the backend has a high commit rate. But here, while it's true that we don't follow that rule (means a backend could flush more than one time per second), that would be a maximum of 2 times (given that ANYTIME is flushing every second). So, I'm not sure that this single extra flush is worth worrying about. Plus we'd certainly need an extra GetCurrentTimestamp() call, so I'm not sure it's worth it. > A few other comments on 0001 > > + /* Skip if completely idle */ > + if (!DoingCommandRead || IsTransactionOrTransactionBlock()) > + pgstat_report_anytime_stat(false); > > Does this need to be conditional? worst case, we return right away with an > empty > list. Best case, is we are consistently flushing. Yeah, I think we could remove this check and just rely on the ones in pgstat_report_anytime_stat(). Done in the attached. > + Assert(!anytime_only || dlist_is_empty(&pgStatPending) == > !have_pending); > > Checking for !anytime_only is unnecessary here. > "list_is_empty(&pgStatPending) == !have_pending" > should be true regardless of ANYTIME or TXN_BOUNDARY, right? Right, thanks for catching it, it was remaining garbage from my dev iterations. > Below are a couple of edits for comments I felt would improve > readability of the code. Done as suggested. > I will start looking at the remaining patches next. Thanks! Note that I also updated the doc in 0003 for the stats that have mixed fields. BTW, I think that we could also make the Function stat kind as flush any time, thoughts? Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
>From 605cae0291397047b09aa025b742bdcaf9bdd528 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Mon, 5 Jan 2026 09:41:39 +0000 Subject: [PATCH v2 1/3] Add pgstat_report_anytime_stat() for periodic stats flushing Long running transactions can accumulate significant statistics (WAL, IO, ...) that remain unflushed until the transaction ends. This delays visibility of resource usage in monitoring views like pg_stat_io and pg_stat_wal. This commit introduces pgstat_report_anytime_stat(), which flushes non transactional statistics even inside active transactions. A new timeout handler fires every second to call this function, ensuring timely stats visibility without waiting for transaction completion. Implementation details: - Add PgStat_FlushBehavior enum to classify stats kinds: * FLUSH_ANYTIME: Stats that can always be flushed (WAL, IO, ...) * FLUSH_AT_TXN_BOUNDARY: Stats requiring transaction boundaries - Modify pgstat_flush_pending_entries() and pgstat_flush_fixed_stats() to accept a boolean anytime_only parameter: * When false: flushes all stats (existing behavior) * When true: flushes only FLUSH_ANYTIME stats and skips FLUSH_AT_TXN_BOUNDARY stats - Register ANYTIME_STATS_UPDATE_TIMEOUT that fires every 1 second, calling pgstat_report_anytime_stat(false) The force parameter in pgstat_report_anytime_stat() is currently unused (always called with force=false) but reserved for future use cases requiring immediate flushing. --- src/backend/tcop/postgres.c | 16 ++++ src/backend/utils/activity/pgstat.c | 113 ++++++++++++++++++++++++---- src/backend/utils/init/globals.c | 1 + src/backend/utils/init/postinit.c | 15 ++++ src/include/miscadmin.h | 1 + src/include/pgstat.h | 4 + src/include/utils/pgstat_internal.h | 13 ++++ src/include/utils/timeout.h | 1 + src/tools/pgindent/typedefs.list | 1 + 9 files changed, 149 insertions(+), 16 deletions(-) 8.2% src/backend/tcop/ 69.3% src/backend/utils/activity/ 9.7% src/backend/utils/init/ 7.7% src/include/utils/ 4.5% src/include/ diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index e54bf1e760f..9c4a9078ee0 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -3530,6 +3530,22 @@ ProcessInterrupts(void) pgstat_report_stat(true); } + /* + * Flush stats outside of transaction boundary if the timeout fired. + * Unlike transactional stats, these can be flushed even inside a running + * transaction. + */ + if (AnytimeStatsUpdateTimeoutPending) + { + AnytimeStatsUpdateTimeoutPending = false; + + pgstat_report_anytime_stat(false); + + /* Schedule next timeout */ + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, + PGSTAT_ANYTIME_FLUSH_INTERVAL); + } + if (ProcSignalBarrierPending) ProcessProcSignalBarrier(); diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index 11bb71cad5a..0f45a7d165e 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -187,7 +187,8 @@ static void pgstat_init_snapshot_fixed(void); static void pgstat_reset_after_failure(void); -static bool pgstat_flush_pending_entries(bool nowait); +static bool pgstat_flush_pending_entries(bool nowait, bool anytime_only); +static bool pgstat_flush_fixed_stats(bool nowait, bool anytime_only); static void pgstat_prep_snapshot(void); static void pgstat_build_snapshot(void); @@ -288,6 +289,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = false, .write_to_file = true, + .flush_behavior = FLUSH_AT_TXN_BOUNDARY, /* so pg_stat_database entries can be seen in all databases */ .accessed_across_databases = true, @@ -305,6 +307,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = false, .write_to_file = true, + .flush_behavior = FLUSH_AT_TXN_BOUNDARY, .shared_size = sizeof(PgStatShared_Relation), .shared_data_off = offsetof(PgStatShared_Relation, stats), @@ -321,6 +324,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = false, .write_to_file = true, + .flush_behavior = FLUSH_AT_TXN_BOUNDARY, .shared_size = sizeof(PgStatShared_Function), .shared_data_off = offsetof(PgStatShared_Function, stats), @@ -336,6 +340,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = false, .write_to_file = true, + .flush_behavior = FLUSH_AT_TXN_BOUNDARY, .accessed_across_databases = true, @@ -353,6 +358,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = false, .write_to_file = true, + .flush_behavior = FLUSH_AT_TXN_BOUNDARY, /* so pg_stat_subscription_stats entries can be seen in all databases */ .accessed_across_databases = true, @@ -370,6 +376,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = false, .write_to_file = false, + .flush_behavior = FLUSH_ANYTIME, .accessed_across_databases = true, @@ -388,6 +395,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = true, .write_to_file = true, + .flush_behavior = FLUSH_ANYTIME, .snapshot_ctl_off = offsetof(PgStat_Snapshot, archiver), .shared_ctl_off = offsetof(PgStat_ShmemControl, archiver), @@ -404,6 +412,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = true, .write_to_file = true, + .flush_behavior = FLUSH_ANYTIME, .snapshot_ctl_off = offsetof(PgStat_Snapshot, bgwriter), .shared_ctl_off = offsetof(PgStat_ShmemControl, bgwriter), @@ -420,6 +429,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = true, .write_to_file = true, + .flush_behavior = FLUSH_ANYTIME, .snapshot_ctl_off = offsetof(PgStat_Snapshot, checkpointer), .shared_ctl_off = offsetof(PgStat_ShmemControl, checkpointer), @@ -436,6 +446,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = true, .write_to_file = true, + .flush_behavior = FLUSH_ANYTIME, .snapshot_ctl_off = offsetof(PgStat_Snapshot, io), .shared_ctl_off = offsetof(PgStat_ShmemControl, io), @@ -453,6 +464,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = true, .write_to_file = true, + .flush_behavior = FLUSH_ANYTIME, .snapshot_ctl_off = offsetof(PgStat_Snapshot, slru), .shared_ctl_off = offsetof(PgStat_ShmemControl, slru), @@ -470,6 +482,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = true, .write_to_file = true, + .flush_behavior = FLUSH_ANYTIME, .snapshot_ctl_off = offsetof(PgStat_Snapshot, wal), .shared_ctl_off = offsetof(PgStat_ShmemControl, wal), @@ -775,23 +788,11 @@ pgstat_report_stat(bool force) partial_flush = false; /* flush of variable-numbered stats tracked in pending entries list */ - partial_flush |= pgstat_flush_pending_entries(nowait); + partial_flush |= pgstat_flush_pending_entries(nowait, false); /* flush of other stats kinds */ if (pgstat_report_fixed) - { - for (PgStat_Kind kind = PGSTAT_KIND_MIN; kind <= PGSTAT_KIND_MAX; kind++) - { - const PgStat_KindInfo *kind_info = pgstat_get_kind_info(kind); - - if (!kind_info) - continue; - if (!kind_info->flush_static_cb) - continue; - - partial_flush |= kind_info->flush_static_cb(nowait); - } - } + partial_flush |= pgstat_flush_fixed_stats(nowait, false); last_flush = now; @@ -1345,9 +1346,14 @@ pgstat_delete_pending_entry(PgStat_EntryRef *entry_ref) /* * Flush out pending variable-numbered stats. + * + * If anytime_only is true, only flushes FLUSH_ANYTIME entries. + * This is safe to call inside transactions. + * + * If anytime_only is false, flushes all entries. */ static bool -pgstat_flush_pending_entries(bool nowait) +pgstat_flush_pending_entries(bool nowait, bool anytime_only) { bool have_pending = false; dlist_node *cur = NULL; @@ -1377,6 +1383,20 @@ pgstat_flush_pending_entries(bool nowait) Assert(!kind_info->fixed_amount); Assert(kind_info->flush_pending_cb != NULL); + /* Skip transactional stats if we're in anytime_only mode */ + if (anytime_only && kind_info->flush_behavior == FLUSH_AT_TXN_BOUNDARY) + { + have_pending = true; + + if (dlist_has_next(&pgStatPending, cur)) + next = dlist_next_node(&pgStatPending, cur); + else + next = NULL; + + cur = next; + continue; + } + /* flush the stats, if possible */ did_flush = kind_info->flush_pending_cb(entry_ref, nowait); @@ -1397,11 +1417,42 @@ pgstat_flush_pending_entries(bool nowait) cur = next; } + /* + * When in anytime_only mode, the list may not be empty because + * FLUSH_AT_TXN_BOUNDARY entries were skipped. + */ Assert(dlist_is_empty(&pgStatPending) == !have_pending); return have_pending; } +/* + * Flush fixed-amount stats. + * + * If anytime_only is true, only flushes FLUSH_ANYTIME stats (safe inside transactions). + * If anytime_only is false, flushes all stats with flush_static_cb. + */ +static bool +pgstat_flush_fixed_stats(bool nowait, bool anytime_only) +{ + bool partial_flush = false; + + for (PgStat_Kind kind = PGSTAT_KIND_MIN; kind <= PGSTAT_KIND_MAX; kind++) + { + const PgStat_KindInfo *kind_info = pgstat_get_kind_info(kind); + + if (!kind_info || !kind_info->flush_static_cb) + continue; + + /* Skip transactional stats if we're in anytime_only mode */ + if (anytime_only && kind_info->flush_behavior == FLUSH_AT_TXN_BOUNDARY) + continue; + + partial_flush |= kind_info->flush_static_cb(nowait); + } + + return partial_flush; +} /* ------------------------------------------------------------ * Helper / infrastructure functions @@ -2119,3 +2170,33 @@ assign_stats_fetch_consistency(int newval, void *extra) if (pgstat_fetch_consistency != newval) force_stats_snapshot_clear = true; } + +/* + * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional + * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. + * Safe to call inside transactions. + */ +void +pgstat_report_anytime_stat(bool force) +{ + bool nowait = !force; + + pgstat_assert_is_up(); + + /* + * Exit if no pending stats at all. This avoids unnecessary work when + * backends are idle or in sessions without stats accumulation. + * + * Note: This check isn't precise as there might be only transactional + * stats pending, which we'll skip during the flush. However, maintaining + * precise tracking would add complexity that does not seem worth it from + * a performance point of view (no noticeable performance regression has + * been observed with the current implementation). + */ + if (dlist_is_empty(&pgStatPending) && !pgstat_report_fixed) + return; + + /* Flush stats outside of transaction boundary */ + pgstat_flush_pending_entries(nowait, true); + pgstat_flush_fixed_stats(nowait, true); +} diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c index 36ad708b360..ad44826c39e 100644 --- a/src/backend/utils/init/globals.c +++ b/src/backend/utils/init/globals.c @@ -40,6 +40,7 @@ volatile sig_atomic_t IdleSessionTimeoutPending = false; volatile sig_atomic_t ProcSignalBarrierPending = false; volatile sig_atomic_t LogMemoryContextPending = false; volatile sig_atomic_t IdleStatsUpdateTimeoutPending = false; +volatile sig_atomic_t AnytimeStatsUpdateTimeoutPending = false; volatile uint32 InterruptHoldoffCount = 0; volatile uint32 QueryCancelHoldoffCount = 0; volatile uint32 CritSectionCount = 0; diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c index 3f401faf3de..cb0f6aecad1 100644 --- a/src/backend/utils/init/postinit.c +++ b/src/backend/utils/init/postinit.c @@ -82,6 +82,7 @@ static void TransactionTimeoutHandler(void); static void IdleSessionTimeoutHandler(void); static void IdleStatsUpdateTimeoutHandler(void); static void ClientCheckTimeoutHandler(void); +static void AnytimeStatsUpdateTimeoutHandler(void); static bool ThereIsAtLeastOneRole(void); static void process_startup_options(Port *port, bool am_superuser); static void process_settings(Oid databaseid, Oid roleid); @@ -765,6 +766,9 @@ InitPostgres(const char *in_dbname, Oid dboid, RegisterTimeout(CLIENT_CONNECTION_CHECK_TIMEOUT, ClientCheckTimeoutHandler); RegisterTimeout(IDLE_STATS_UPDATE_TIMEOUT, IdleStatsUpdateTimeoutHandler); + RegisterTimeout(ANYTIME_STATS_UPDATE_TIMEOUT, + AnytimeStatsUpdateTimeoutHandler); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_ANYTIME_FLUSH_INTERVAL); } /* @@ -1446,3 +1450,14 @@ ThereIsAtLeastOneRole(void) return result; } + +/* + * Timeout handler for flushing non-transactional stats. + */ +static void +AnytimeStatsUpdateTimeoutHandler(void) +{ + AnytimeStatsUpdateTimeoutPending = true; + InterruptPending = true; + SetLatch(MyLatch); +} diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h index db559b39c4d..8aeb9628871 100644 --- a/src/include/miscadmin.h +++ b/src/include/miscadmin.h @@ -96,6 +96,7 @@ extern PGDLLIMPORT volatile sig_atomic_t IdleSessionTimeoutPending; extern PGDLLIMPORT volatile sig_atomic_t ProcSignalBarrierPending; extern PGDLLIMPORT volatile sig_atomic_t LogMemoryContextPending; extern PGDLLIMPORT volatile sig_atomic_t IdleStatsUpdateTimeoutPending; +extern PGDLLIMPORT volatile sig_atomic_t AnytimeStatsUpdateTimeoutPending; extern PGDLLIMPORT volatile sig_atomic_t CheckClientConnectionPending; extern PGDLLIMPORT volatile sig_atomic_t ClientConnectionLost; diff --git a/src/include/pgstat.h b/src/include/pgstat.h index fff7ecc2533..86e65397614 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -35,6 +35,9 @@ /* Default directory to store temporary statistics data in */ #define PG_STAT_TMP_DIR "pg_stat_tmp" +/* When to call pgstat_report_anytime_stat() again */ +#define PGSTAT_ANYTIME_FLUSH_INTERVAL 1000 + /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -533,6 +536,7 @@ extern void pgstat_initialize(void); /* Functions called from backends */ extern long pgstat_report_stat(bool force); +extern void pgstat_report_anytime_stat(bool force); extern void pgstat_force_next_flush(void); extern void pgstat_reset_counters(void); diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index 9b8fbae00ed..63feae640d1 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -224,6 +224,16 @@ typedef struct PgStat_SubXactStatus PgStat_TableXactStatus *first; /* head of list for this subxact */ } PgStat_SubXactStatus; +/* + * Flush behavior for statistics kinds. + */ +typedef enum PgStat_FlushBehavior +{ + FLUSH_ANYTIME, /* All fields can be flushed anytime, + * including within transactions */ + FLUSH_AT_TXN_BOUNDARY, /* All fields can only be flushed at + * transaction boundary */ +} PgStat_FlushBehavior; /* * Metadata for a specific kind of statistics. @@ -251,6 +261,9 @@ typedef struct PgStat_KindInfo */ bool track_entry_count:1; + /* Flush behavior */ + PgStat_FlushBehavior flush_behavior; + /* * The size of an entry in the shared stats hash table (pointed to by * PgStatShared_HashEntry->body). For fixed-numbered statistics, this is diff --git a/src/include/utils/timeout.h b/src/include/utils/timeout.h index 0965b590b34..10723bb664c 100644 --- a/src/include/utils/timeout.h +++ b/src/include/utils/timeout.h @@ -35,6 +35,7 @@ typedef enum TimeoutId IDLE_SESSION_TIMEOUT, IDLE_STATS_UPDATE_TIMEOUT, CLIENT_CONNECTION_CHECK_TIMEOUT, + ANYTIME_STATS_UPDATE_TIMEOUT, STARTUP_PROGRESS_TIMEOUT, /* First user-definable timeout reason */ USER_TIMEOUT, diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 3f3a888fd0e..610b35a9b31 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2268,6 +2268,7 @@ PgStat_Counter PgStat_EntryRef PgStat_EntryRefHashEntry PgStat_FetchConsistency +PgStat_FlushBehavior PgStat_FunctionCallUsage PgStat_FunctionCounts PgStat_HashKey -- 2.34.1
>From e012b211f86cba606375c8730f12a4d25dae93d4 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Tue, 6 Jan 2026 11:06:31 +0000 Subject: [PATCH v2 2/3] Remove useless calls to flush some stats Now that some stats can be flushed outside of transaction boundaries, remove useless calls to report/flush some stats. Those calls were in place because before commit <XXXX> stats were flushed only at transaction boundaries. Note that: - it reverts 039549d70f6 (it just keeps its tests) - it can't be done for checkpointer and bgworker for example because they don't have a flush callback to call - it can't be done for auxiliary process (walsummarizer for example) because they currently do not register the new timeout handler --- src/backend/replication/walreceiver.c | 10 ------ src/backend/replication/walsender.c | 36 ++------------------ src/backend/utils/activity/pgstat_relation.c | 13 ------- 3 files changed, 2 insertions(+), 57 deletions(-) 75.3% src/backend/replication/ 24.6% src/backend/utils/activity/ diff --git a/src/backend/replication/walreceiver.c b/src/backend/replication/walreceiver.c index a41453530a1..266379c780a 100644 --- a/src/backend/replication/walreceiver.c +++ b/src/backend/replication/walreceiver.c @@ -553,16 +553,6 @@ WalReceiverMain(const void *startup_data, size_t startup_data_len) */ bool requestReply = false; - /* - * Report pending statistics to the cumulative stats - * system. This location is useful for the report as it - * is not within a tight loop in the WAL receiver, to - * avoid bloating pgstats with requests, while also making - * sure that the reports happen each time a status update - * is sent. - */ - pgstat_report_wal(false); - /* * Check if time since last receive from primary has * reached the configured limit. diff --git a/src/backend/replication/walsender.c b/src/backend/replication/walsender.c index 1ab09655a70..c33185bd337 100644 --- a/src/backend/replication/walsender.c +++ b/src/backend/replication/walsender.c @@ -94,14 +94,10 @@ #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/pg_lsn.h" -#include "utils/pgstat_internal.h" #include "utils/ps_status.h" #include "utils/timeout.h" #include "utils/timestamp.h" -/* Minimum interval used by walsender for stats flushes, in ms */ -#define WALSENDER_STATS_FLUSH_INTERVAL 1000 - /* * Maximum data payload in a WAL data message. Must be >= XLOG_BLCKSZ. * @@ -1826,7 +1822,6 @@ WalSndWaitForWal(XLogRecPtr loc) int wakeEvents; uint32 wait_event = 0; static XLogRecPtr RecentFlushPtr = InvalidXLogRecPtr; - TimestampTz last_flush = 0; /* * Fast path to avoid acquiring the spinlock in case we already know we @@ -1847,7 +1842,6 @@ WalSndWaitForWal(XLogRecPtr loc) { bool wait_for_standby_at_stop = false; long sleeptime; - TimestampTz now; /* Clear any already-pending wakeups */ ResetLatch(MyLatch); @@ -1958,8 +1952,7 @@ WalSndWaitForWal(XLogRecPtr loc) * new WAL to be generated. (But if we have nothing to send, we don't * want to wake on socket-writable.) */ - now = GetCurrentTimestamp(); - sleeptime = WalSndComputeSleeptime(now); + sleeptime = WalSndComputeSleeptime(GetCurrentTimestamp()); wakeEvents = WL_SOCKET_READABLE; @@ -1968,15 +1961,6 @@ WalSndWaitForWal(XLogRecPtr loc) Assert(wait_event != 0); - /* Report IO statistics, if needed */ - if (TimestampDifferenceExceeds(last_flush, now, - WALSENDER_STATS_FLUSH_INTERVAL)) - { - pgstat_flush_io(false); - (void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO); - last_flush = now; - } - WalSndWait(wakeEvents, sleeptime, wait_event); } @@ -2879,8 +2863,6 @@ WalSndCheckTimeOut(void) static void WalSndLoop(WalSndSendDataCallback send_data) { - TimestampTz last_flush = 0; - /* * Initialize the last reply timestamp. That enables timeout processing * from hereon. @@ -2975,9 +2957,6 @@ WalSndLoop(WalSndSendDataCallback send_data) * WalSndWaitForWal() handle any other blocking; idle receivers need * its additional actions. For physical replication, also block if * caught up; its send_data does not block. - * - * The IO statistics are reported in WalSndWaitForWal() for the - * logical WAL senders. */ if ((WalSndCaughtUp && send_data != XLogSendLogical && !streamingDoneSending) || @@ -2985,7 +2964,6 @@ WalSndLoop(WalSndSendDataCallback send_data) { long sleeptime; int wakeEvents; - TimestampTz now; if (!streamingDoneReceiving) wakeEvents = WL_SOCKET_READABLE; @@ -2996,21 +2974,11 @@ WalSndLoop(WalSndSendDataCallback send_data) * Use fresh timestamp, not last_processing, to reduce the chance * of reaching wal_sender_timeout before sending a keepalive. */ - now = GetCurrentTimestamp(); - sleeptime = WalSndComputeSleeptime(now); + sleeptime = WalSndComputeSleeptime(GetCurrentTimestamp()); if (pq_is_send_pending()) wakeEvents |= WL_SOCKET_WRITEABLE; - /* Report IO statistics, if needed */ - if (TimestampDifferenceExceeds(last_flush, now, - WALSENDER_STATS_FLUSH_INTERVAL)) - { - pgstat_flush_io(false); - (void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO); - last_flush = now; - } - /* Sleep until something happens or we time out */ WalSndWait(wakeEvents, sleeptime, WAIT_EVENT_WAL_SENDER_MAIN); } diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index bc8c43b96aa..feae2ae5f44 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -260,15 +260,6 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples, } pgstat_unlock_entry(entry_ref); - - /* - * Flush IO statistics now. pgstat_report_stat() will flush IO stats, - * however this will not be called until after an entire autovacuum cycle - * is done -- which will likely vacuum many relations -- or until the - * VACUUM command has processed all tables and committed. - */ - pgstat_flush_io(false); - (void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO); } /* @@ -360,10 +351,6 @@ pgstat_report_analyze(Relation rel, } pgstat_unlock_entry(entry_ref); - - /* see pgstat_report_vacuum() */ - pgstat_flush_io(false); - (void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO); } /* -- 2.34.1
>From d71d13b8e5a938a8a94362121fa937f9026fb51a Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Mon, 19 Jan 2026 06:27:55 +0000 Subject: [PATCH v2 3/3] Add FLUSH_MIXED support and implement it for RELATION stats This commit extends the non transactional stats infrastructure to support statistics kinds with mixed transaction behavior: some fields are transactional (e.g., tuple inserts/updates/deletes) while others are non transactional (e.g., sequential scans blocks read, ...). It introduces FLUSH_MIXED as a third flush behavior type, alongside FLUSH_ANYTIME and FLUSH_AT_TXN_BOUNDARY. For FLUSH_MIXED kinds, a new flush_anytime_cb callback enables partial flushing of only the non transactional fields during running transactions. Some tests are also added. Implementation details: - Add FLUSH_MIXED to PgStat_FlushBehavior enum - Add flush_anytime_cb to PgStat_KindInfo for partial flushing callback - Update pgstat_flush_pending_entries() to call flush_anytime_cb for FLUSH_MIXED entries when in anytime_only mode - Keep FLUSH_MIXED entries in the pending list after partial flush, as transactional fields still need to be flushed at transaction boundary RELATION stats are making use of FLUSH_MIXED: - Change RELATION from TXN_ALL to FLUSH_MIXED - Implement pgstat_relation_flush_anytime_cb() to flush only read related stats: numscans, tuples_returned, tuples_fetched, blocks_fetched, blocks_hit - Clear these fields after flushing to prevent double counting when pgstat_relation_flush_cb() runs at transaction commit - Transactional stats (tuples_inserted, tuples_updated, tuples_deleted, live_tuples, dead_tuples) remain pending until transaction boundary Remark: We could also imagine adding a new flush_anytime_static_cb() callback for future FLUSH_MIXED fixed amount stats. --- doc/src/sgml/monitoring.sgml | 30 +++++++ src/backend/utils/activity/pgstat.c | 36 ++++++--- src/backend/utils/activity/pgstat_relation.c | 82 ++++++++++++++++++++ src/include/utils/pgstat_internal.h | 9 +++ src/test/isolation/expected/stats.out | 40 ++++++++++ src/test/isolation/expected/stats_1.out | 40 ++++++++++ src/test/isolation/specs/stats.spec | 12 +++ 7 files changed, 237 insertions(+), 12 deletions(-) 14.5% doc/src/sgml/ 47.2% src/backend/utils/activity/ 4.7% src/include/utils/ 29.4% src/test/isolation/expected/ 4.0% src/test/isolation/specs/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 817fd9f4ca7..15b55016b66 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -3730,6 +3730,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage </tgroup> </table> + <note> + <para> + All the statistics are updated while the transactions are in progress, except + for <structfield>xact_commit</structfield>, <structfield>xact_rollback</structfield>, + <structfield>tup_inserted</structfield>, <structfield>tup_updated</structfield> and + <structfield>tup_deleted</structfield> that are updated only when the transactions + finish. + </para> + </note> + </sect2> <sect2 id="monitoring-pg-stat-database-conflicts-view"> @@ -4186,6 +4196,17 @@ description | Waiting for a newly initialized WAL file to reach durable storage </tgroup> </table> + <note> + <para> + The <structfield>seq_scan</structfield>, <structfield>last_seq_scan</structfield>, + <structfield>seq_tup_read</structfield>, <structfield>idx_scan</structfield>, + <structfield>last_idx_scan</structfield> and <structfield>idx_tup_fetch</structfield> + are updated while the transactions are in progress. This means that we can see + those statistics being updated without having to wait until the transaction + finishes. + </para> + </note> + </sect2> <sect2 id="monitoring-pg-stat-all-indexes-view"> @@ -4367,6 +4388,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage tuples (see <xref linkend="indexes-multicolumn"/>). </para> </note> + <note> + <para> + The <structfield>idx_scan</structfield>, <structfield>last_idx_scan</structfield>, + <structfield>idx_tup_read</structfield> and <structfield>idx_tup_fetch</structfield> + are updated while the transactions are in progress. This means that we can see + those statistics being updated without having to wait until the transaction + finishes. + </para> + </note> <tip> <para> <command>EXPLAIN ANALYZE</command> outputs the total number of index diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index 0f45a7d165e..5b93683ea9b 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -289,7 +289,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = false, .write_to_file = true, - .flush_behavior = FLUSH_AT_TXN_BOUNDARY, + .flush_behavior = FLUSH_ANYTIME, /* so pg_stat_database entries can be seen in all databases */ .accessed_across_databases = true, @@ -307,7 +307,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = false, .write_to_file = true, - .flush_behavior = FLUSH_AT_TXN_BOUNDARY, + .flush_behavior = FLUSH_MIXED, .shared_size = sizeof(PgStatShared_Relation), .shared_data_off = offsetof(PgStatShared_Relation, stats), @@ -315,6 +315,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .pending_size = sizeof(PgStat_TableStatus), .flush_pending_cb = pgstat_relation_flush_cb, + .flush_anytime_cb = pgstat_relation_flush_anytime_cb, .delete_pending_cb = pgstat_relation_delete_pending_cb, .reset_timestamp_cb = pgstat_relation_reset_timestamp_cb, }, @@ -1347,10 +1348,11 @@ pgstat_delete_pending_entry(PgStat_EntryRef *entry_ref) /* * Flush out pending variable-numbered stats. * - * If anytime_only is true, only flushes FLUSH_ANYTIME entries. + * If anytime_only is true, only flushes FLUSH_ANYTIME and FLUSH_MIXED entries, + * using flush_anytime_cb for FLUSH_MIXED. * This is safe to call inside transactions. * - * If anytime_only is false, flushes all entries. + * If anytime_only is false, flushes all entries using flush_pending_cb. */ static bool pgstat_flush_pending_entries(bool nowait, bool anytime_only) @@ -1378,6 +1380,7 @@ pgstat_flush_pending_entries(bool nowait, bool anytime_only) PgStat_Kind kind = key.kind; const PgStat_KindInfo *kind_info = pgstat_get_kind_info(kind); bool did_flush; + bool is_partial_flush = false; dlist_node *next; Assert(!kind_info->fixed_amount); @@ -1397,8 +1400,21 @@ pgstat_flush_pending_entries(bool nowait, bool anytime_only) continue; } - /* flush the stats, if possible */ - did_flush = kind_info->flush_pending_cb(entry_ref, nowait); + /* flush the stats (with the appropriate callback), if possible */ + if (anytime_only && + kind_info->flush_behavior == FLUSH_MIXED && + kind_info->flush_anytime_cb != NULL) + { + /* Partial flush of non-transactional fields only */ + did_flush = kind_info->flush_anytime_cb(entry_ref, nowait); + is_partial_flush = true; + } + else + { + /* Full flush */ + did_flush = kind_info->flush_pending_cb(entry_ref, nowait); + is_partial_flush = false; + } Assert(did_flush || nowait); @@ -1408,8 +1424,8 @@ pgstat_flush_pending_entries(bool nowait, bool anytime_only) else next = NULL; - /* if successfully flushed, remove entry */ - if (did_flush) + /* if successfull non partial flush, remove entry */ + if (did_flush && !is_partial_flush) pgstat_delete_pending_entry(entry_ref); else have_pending = true; @@ -1417,10 +1433,6 @@ pgstat_flush_pending_entries(bool nowait, bool anytime_only) cur = next; } - /* - * When in anytime_only mode, the list may not be empty because - * FLUSH_AT_TXN_BOUNDARY entries were skipped. - */ Assert(dlist_is_empty(&pgStatPending) == !have_pending); return have_pending; diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index feae2ae5f44..6d6f333039e 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -887,6 +887,88 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait) return true; } +/* + * Flush only non-transactional relation stats. + * + * This is called periodically during running transactions to make some + * statistics visible without waiting for the transaction to finish. + * + * Transactional stats (inserts/updates/deletes and their effects on live/dead + * tuple counts) remain in pending until the transaction ends, at which point + * pgstat_relation_flush_cb() will flush them. + * + * If nowait is true and the lock could not be immediately acquired, returns + * false without flushing the entry. Otherwise returns true. + */ +bool +pgstat_relation_flush_anytime_cb(PgStat_EntryRef *entry_ref, bool nowait) +{ + Oid dboid; + PgStat_TableStatus *lstats; /* pending stats entry */ + PgStatShared_Relation *shtabstats; + PgStat_StatTabEntry *tabentry; /* table entry of shared stats */ + PgStat_StatDBEntry *dbentry; /* pending database entry */ + bool has_nontxn_stats = false; + + dboid = entry_ref->shared_entry->key.dboid; + lstats = (PgStat_TableStatus *) entry_ref->pending; + shtabstats = (PgStatShared_Relation *) entry_ref->shared_stats; + + /* + * Check if there are any non-transactional stats to flush. Avoid + * unnecessarily locking the entry if nothing accumulated. + */ + if (lstats->counts.numscans > 0 || + lstats->counts.tuples_returned > 0 || + lstats->counts.tuples_fetched > 0 || + lstats->counts.blocks_fetched > 0 || + lstats->counts.blocks_hit > 0) + has_nontxn_stats = true; + + if (!has_nontxn_stats) + return true; + + if (!pgstat_lock_entry(entry_ref, nowait)) + return false; + + /* Add only the non-transactional values to the shared entry */ + tabentry = &shtabstats->stats; + + tabentry->numscans += lstats->counts.numscans; + if (lstats->counts.numscans) + { + TimestampTz t = GetCurrentTimestamp(); + + if (t > tabentry->lastscan) + tabentry->lastscan = t; + } + tabentry->tuples_returned += lstats->counts.tuples_returned; + tabentry->tuples_fetched += lstats->counts.tuples_fetched; + tabentry->blocks_fetched += lstats->counts.blocks_fetched; + tabentry->blocks_hit += lstats->counts.blocks_hit; + + pgstat_unlock_entry(entry_ref); + + /* Also update the corresponding fields in database stats */ + dbentry = pgstat_prep_database_pending(dboid); + dbentry->tuples_returned += lstats->counts.tuples_returned; + dbentry->tuples_fetched += lstats->counts.tuples_fetched; + dbentry->blocks_fetched += lstats->counts.blocks_fetched; + dbentry->blocks_hit += lstats->counts.blocks_hit; + + /* + * Clear the flushed fields from pending stats to prevent double-counting + * when pgstat_relation_flush_cb() runs at transaction boundary. + */ + lstats->counts.numscans = 0; + lstats->counts.tuples_returned = 0; + lstats->counts.tuples_fetched = 0; + lstats->counts.blocks_fetched = 0; + lstats->counts.blocks_hit = 0; + + return true; +} + void pgstat_relation_delete_pending_cb(PgStat_EntryRef *entry_ref) { diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index 63feae640d1..c80b8162b37 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -233,6 +233,8 @@ typedef enum PgStat_FlushBehavior * including within transactions */ FLUSH_AT_TXN_BOUNDARY, /* All fields can only be flushed at * transaction boundary */ + FLUSH_MIXED, /* Mix of fields that can be flushed anytime + * or only at transaction boundary */ } PgStat_FlushBehavior; /* @@ -264,6 +266,12 @@ typedef struct PgStat_KindInfo /* Flush behavior */ PgStat_FlushBehavior flush_behavior; + /* + * For PGSTAT_FLUSH_MIXED kinds: callback to flush only some fields. If + * NULL for a MIXED kind, treated as PGSTAT_FLUSH_AT_TXN_BOUNDARY. + */ + bool (*flush_anytime_cb) (PgStat_EntryRef *entry_ref, bool nowait); + /* * The size of an entry in the shared stats hash table (pointed to by * PgStatShared_HashEntry->body). For fixed-numbered statistics, this is @@ -776,6 +784,7 @@ extern void AtPrepare_PgStat_Relations(PgStat_SubXactStatus *xact_state); extern void PostPrepare_PgStat_Relations(PgStat_SubXactStatus *xact_state); extern bool pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait); +extern bool pgstat_relation_flush_anytime_cb(PgStat_EntryRef *entry_ref, bool nowait); extern void pgstat_relation_delete_pending_cb(PgStat_EntryRef *entry_ref); extern void pgstat_relation_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); diff --git a/src/test/isolation/expected/stats.out b/src/test/isolation/expected/stats.out index cfad309ccf3..6d62b30e4a7 100644 --- a/src/test/isolation/expected/stats.out +++ b/src/test/isolation/expected/stats.out @@ -2245,6 +2245,46 @@ seq_scan|seq_tup_read|n_tup_ins|n_tup_upd|n_tup_del|n_live_tup|n_dead_tup|vacuum (1 row) +starting permutation: s2_begin s2_table_select s1_sleep s1_table_stats s2_table_drop s2_commit +pg_stat_force_next_flush +------------------------ + +(1 row) + +step s2_begin: BEGIN; +step s2_table_select: SELECT * FROM test_stat_tab ORDER BY key, value; +key|value +---+----- +k0 | 1 +(1 row) + +step s1_sleep: SELECT pg_sleep(1.5); +pg_sleep +-------- + +(1 row) + +step s1_table_stats: + SELECT + pg_stat_get_numscans(tso.oid) AS seq_scan, + pg_stat_get_tuples_returned(tso.oid) AS seq_tup_read, + pg_stat_get_tuples_inserted(tso.oid) AS n_tup_ins, + pg_stat_get_tuples_updated(tso.oid) AS n_tup_upd, + pg_stat_get_tuples_deleted(tso.oid) AS n_tup_del, + pg_stat_get_live_tuples(tso.oid) AS n_live_tup, + pg_stat_get_dead_tuples(tso.oid) AS n_dead_tup, + pg_stat_get_vacuum_count(tso.oid) AS vacuum_count + FROM test_stat_oid AS tso + WHERE tso.name = 'test_stat_tab' + +seq_scan|seq_tup_read|n_tup_ins|n_tup_upd|n_tup_del|n_live_tup|n_dead_tup|vacuum_count +--------+------------+---------+---------+---------+----------+----------+------------ + 1| 1| 1| 0| 0| 1| 0| 0 +(1 row) + +step s2_table_drop: DROP TABLE test_stat_tab; +step s2_commit: COMMIT; + starting permutation: s1_track_counts_off s1_table_stats s1_track_counts_on pg_stat_force_next_flush ------------------------ diff --git a/src/test/isolation/expected/stats_1.out b/src/test/isolation/expected/stats_1.out index e1d937784cb..2fade10e817 100644 --- a/src/test/isolation/expected/stats_1.out +++ b/src/test/isolation/expected/stats_1.out @@ -2253,6 +2253,46 @@ seq_scan|seq_tup_read|n_tup_ins|n_tup_upd|n_tup_del|n_live_tup|n_dead_tup|vacuum (1 row) +starting permutation: s2_begin s2_table_select s1_sleep s1_table_stats s2_table_drop s2_commit +pg_stat_force_next_flush +------------------------ + +(1 row) + +step s2_begin: BEGIN; +step s2_table_select: SELECT * FROM test_stat_tab ORDER BY key, value; +key|value +---+----- +k0 | 1 +(1 row) + +step s1_sleep: SELECT pg_sleep(1.5); +pg_sleep +-------- + +(1 row) + +step s1_table_stats: + SELECT + pg_stat_get_numscans(tso.oid) AS seq_scan, + pg_stat_get_tuples_returned(tso.oid) AS seq_tup_read, + pg_stat_get_tuples_inserted(tso.oid) AS n_tup_ins, + pg_stat_get_tuples_updated(tso.oid) AS n_tup_upd, + pg_stat_get_tuples_deleted(tso.oid) AS n_tup_del, + pg_stat_get_live_tuples(tso.oid) AS n_live_tup, + pg_stat_get_dead_tuples(tso.oid) AS n_dead_tup, + pg_stat_get_vacuum_count(tso.oid) AS vacuum_count + FROM test_stat_oid AS tso + WHERE tso.name = 'test_stat_tab' + +seq_scan|seq_tup_read|n_tup_ins|n_tup_upd|n_tup_del|n_live_tup|n_dead_tup|vacuum_count +--------+------------+---------+---------+---------+----------+----------+------------ + 0| 0| 1| 0| 0| 1| 0| 0 +(1 row) + +step s2_table_drop: DROP TABLE test_stat_tab; +step s2_commit: COMMIT; + starting permutation: s1_track_counts_off s1_table_stats s1_track_counts_on pg_stat_force_next_flush ------------------------ diff --git a/src/test/isolation/specs/stats.spec b/src/test/isolation/specs/stats.spec index da16710da0f..1b0168e6176 100644 --- a/src/test/isolation/specs/stats.spec +++ b/src/test/isolation/specs/stats.spec @@ -50,6 +50,8 @@ step s1_rollback { ROLLBACK; } step s1_prepare_a { PREPARE TRANSACTION 'a'; } step s1_commit_prepared_a { COMMIT PREPARED 'a'; } step s1_rollback_prepared_a { ROLLBACK PREPARED 'a'; } +# Has to be greater than PGSTAT_ANYTIME_FLUSH_INTERVAL +step s1_sleep { SELECT pg_sleep(1.5); } # Function stats steps step s1_ff { SELECT pg_stat_force_next_flush(); } @@ -138,6 +140,7 @@ step s2_commit { COMMIT; } step s2_commit_prepared_a { COMMIT PREPARED 'a'; } step s2_rollback_prepared_a { ROLLBACK PREPARED 'a'; } step s2_ff { SELECT pg_stat_force_next_flush(); } +step s2_table_drop { DROP TABLE test_stat_tab; } # Function stats steps step s2_track_funcs_all { SET track_functions = 'all'; } @@ -435,6 +438,15 @@ permutation s1_table_drop s1_table_stats +### Check that some stats are updated (seq_scan and seq_tup_read) +### while the transaction is still running +permutation + s2_begin + s2_table_select + s1_sleep + s1_table_stats + s2_table_drop + s2_commit ### Check that we don't count changes with track counts off, but allow access ### to prior stats -- 2.34.1
