Hello hackers, This comes from Tomas's patch idea from his website[1], i thought this patch makes sense to have.
PostgreSQL's virtual file descriptor (VFD) maintains a per-backend cache of open file descriptors, bounded by max_files_per_process (default 1000). When the cache is full, the least-recently-used entry is evicted so its OS fd is closed, so a new file can be opened. On the next access to that file, open() must be called again, incurring a syscall that a larger cache would have avoided. A trivial example is with partitioned tables: a table with 1500 partitions requires even more than 1500 file descriptors per full scan (main fork, vm ...), which is more than the default limit, causing potential evictions and reopens. The problem is well-understood and the fix is straightforward: raise max_files_per_process. Tomas showed a 4-5x throughput improvement in [1] sometimes, on my end i see something less than that, depending on the query itself, but we get the idea. AFAIK there is currently no way from inside PostgreSQL to know whether fd cache pressure is occurring. Implementation is trivial, because the VFD cache is strictly per-backend, the counters are also per-backend and require no shared memory or locking. Three macros (pgstat_count_vfd_hit/miss/eviction) update fields in PendingVfdCacheStats directly from fd.c. I find this a bit useful, I would love to hear about anyone's thoughts whether this is useful or not. [1] https://vondra.me/posts/patch-idea-statistics-for-file-descriptor-cache Regards, Ayoub
From 1124d345945ecd68ca6019e6f1d24f1a5cb81861 Mon Sep 17 00:00:00 2001 From: AyoubKAZ <[email protected]> Date: Sat, 21 Mar 2026 17:03:10 +0100 Subject: [PATCH] Add pg_stat_vfdcache view for VFD cache statistics PostgreSQL's virtual file descriptor (VFD) layer maintains a per-backend cache of open file descriptors bounded by max_files_per_process (default 1000). When the cache is full, the least-recently-used entry is evicted (its OS fd closed) so a new file can be opened. A subsequent access to an evicted file must call open() again. A trivial example is with partitioned tables: a table with 1500 partitions requires up to many file descriptors per full scan (main fork, vm ...), which is more than the default limit, causing potential evictions and reopens. To my knowledge there's no way to observe this from inside PostgreSQL. This commit adds: pg_stat_vfdcache -- a view exposing, for the current session: hits number of FileAccess() calls that found the fd open misses number of FileAccess() calls that found fd closed evictions number of LRU evictions (close() to free a slot) cache_size physical fds currently open max_cache_size current value of max_files_per_process hit_ratio hits / (hits + misses) stats_reset timestamp of last counter reset pg_stat_reset_vfdcache() -- resets counters for the current session Because the VFD cache is strictly per-backend, the counters are backend-local and require no shared memory, locking, or atomic operations. Three macros (pgstat_count_vfd_hit/miss/eviction) update fields in PendingVfdCacheStats directly from fd.c. Hit and miss counters are placed in FileAccess(), which is the single gate through which all VFD-mediated file reads, writes, truncations, and size checks pass. The eviction counter is placed in ReleaseLruFile(), before LruDelete() is called. --- doc/src/sgml/monitoring.sgml | 138 +++++++++++++++++++ src/backend/catalog/system_views.sql | 17 +++ src/backend/storage/file/fd.c | 22 +++ src/backend/utils/activity/Makefile | 1 + src/backend/utils/activity/meson.build | 1 + src/backend/utils/activity/pgstat_vfdcache.c | 75 ++++++++++ src/backend/utils/adt/pgstatfuncs.c | 52 +++++++ src/include/catalog/pg_proc.dat | 36 +++++ src/include/pgstat.h | 31 +++++ src/include/storage/fd.h | 1 + src/test/regress/expected/rules.out | 10 ++ 11 files changed, 384 insertions(+) create mode 100644 src/backend/utils/activity/pgstat_vfdcache.c diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 462019a972c..23ea643fcab 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -472,6 +472,19 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser </entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structname>pg_stat_vfdcache</structname> + <indexterm><primary>pg_stat_vfdcache</primary></indexterm> + </para> + <para> + One row only, showing statistics about the current session's virtual + file descriptor (VFD) cache activity. See + <link linkend="monitoring-pg-stat-vfdcache-view"> + <structname>pg_stat_vfdcache</structname></link> for details. + </para></entry> + </row> + <row> <entry><structname>pg_stat_checkpointer</structname><indexterm><primary>pg_stat_checkpointer</primary></indexterm></entry> <entry>One row only, showing statistics about the @@ -3352,6 +3365,118 @@ description | Waiting for a newly initialized WAL file to reach durable storage </sect2> + <sect2 id="monitoring-pg-stat-vfdcache-view"> + <title><structname>pg_stat_vfdcache</structname></title> + + <indexterm zone="monitoring-pg-stat-vfdcache-view"> + <primary>pg_stat_vfdcache</primary> + </indexterm> + + <para> + The <structname>pg_stat_vfdcache</structname> view will always have a + single row, containing data about the VFD (Virtual File Descriptor) cache activity of the + current session. + </para> + + <table id="pg-stat-vfdcache-view" xreflabel="pg_stat_vfdcache"> + <title><structname>pg_stat_vfdcache</structname> View</title> + <tgroup cols="3"> + <thead> + <row> + <entry role="catalog_table_entry">Column</entry> + <entry>Type</entry> + <entry>Description</entry> + </row> + </thead> + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>hits</structfield> <type>bigint</type> + </para></entry> + <entry><type>bigint</type></entry> + <entry> + Number of file accesses where the physical file descriptor was + already open in the cache, requiring no system call. + </entry> + </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>misses</structfield> <type>bigint</type> + </para></entry> + <entry><type>bigint</type></entry> + <entry> + Number of file accesses where the physical file descriptor had + been evicted from the cache, requiring <function>open()</function> + to be called again before the access could proceed. + </entry> + </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>evictions</structfield> <type>bigint</type> + </para></entry> + <entry><type>bigint</type></entry> + <entry> + Number of times a physical file descriptor was closed to make + room in the cache for a new one (LRU eviction). Each eviction + will eventually produce a miss when the evicted file is accessed + again. When the cache is thrashing, this value will be close to + or equal to <structfield>misses</structfield>. + </entry> + </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>cache_size</structfield> <type>integer</type> + </para></entry> + <entry><type>integer</type></entry> + <entry> + Current number of physical file descriptors open in the VFD + cache at the time the view is queried. + </entry> + </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>max_cache_size</structfield> <type>integer</type> + </para></entry> + <entry><type>integer</type></entry> + <entry> + Current value of <varname>max_files_per_process</varname>. The + effective limit on simultaneously open file descriptors may be + lower than this value if the operating system's per-process file + descriptor limit is smaller. + </entry> + </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>hit_ratio</structfield> <type>float8</type> + </para></entry> + <entry><type>float8</type></entry> + <entry> + Fraction of file accesses that were cache hits: + <literal>hits / (hits + misses)</literal>. A value of + <literal>1.0</literal> indicates no cache pressure. Values + significantly below <literal>1.0</literal> indicate that + <varname>max_files_per_process</varname> should be increased. + <literal>NULL</literal> if no accesses have been recorded yet. + </entry> + </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>stats_reset</structfield> + <type>timestamp with time zone</type> + </para></entry> + <entry><type>timestamp with time zone</type></entry> + <entry> + Time at which the counters were last reset by + <function>pg_stat_reset_vfdcache()</function>. + <literal>NULL</literal> if the counters have never been reset. + </entry> + </row> + </tbody> + </tgroup> + </table> + + </sect2> + <sect2 id="monitoring-pg-stat-checkpointer-view"> <title><structname>pg_stat_checkpointer</structname></title> @@ -5530,6 +5655,19 @@ description | Waiting for a newly initialized WAL file to reach durable storage can be granted EXECUTE to run the function. </para></entry> </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm><primary>pg_stat_reset_vfdcache</primary></indexterm> + <function>pg_stat_reset_vfdcache</function> () + <returnvalue>void</returnvalue> + </para> + <para> + Reset the VFD cache statistics counters for the current session to + zero. The reset timestamp is recorded in + <structname>pg_stat_vfdcache</structname>.<structfield>stats_reset</structfield>. + </para></entry> + </row> </tbody> </tgroup> </table> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index f1ed7b58f13..e395fe556eb 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1507,3 +1507,20 @@ CREATE VIEW pg_aios AS SELECT * FROM pg_get_aios(); REVOKE ALL ON pg_aios FROM PUBLIC; GRANT SELECT ON pg_aios TO pg_read_all_stats; + +CREATE VIEW pg_stat_vfdcache AS + SELECT + pg_stat_get_vfd_hits() AS hits, + pg_stat_get_vfd_misses() AS misses, + pg_stat_get_vfd_evictions() AS evictions, + pg_stat_get_vfd_cache_size() AS cache_size, + current_setting('max_files_per_process')::int AS max_cache_size, + CASE + WHEN pg_stat_get_vfd_hits() + pg_stat_get_vfd_misses() = 0 + THEN NULL::float8 + ELSE pg_stat_get_vfd_hits()::float8 + / (pg_stat_get_vfd_hits() + pg_stat_get_vfd_misses()) + END AS hit_ratio, + pg_stat_get_vfd_stat_reset_time() AS stats_reset; + +GRANT SELECT ON pg_stat_vfdcache TO PUBLIC; \ No newline at end of file diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c index 01f1bd6e687..480ad604115 100644 --- a/src/backend/storage/file/fd.c +++ b/src/backend/storage/file/fd.c @@ -1378,6 +1378,7 @@ ReleaseLruFile(void) * in the ring. */ Assert(VfdCache[0].lruMoreRecently != 0); + pgstat_count_vfd_eviction(); LruDelete(VfdCache[0].lruMoreRecently); return true; /* freed a file */ } @@ -1491,6 +1492,7 @@ FileAccess(File file) if (FileIsNotOpen(file)) { + pgstat_count_vfd_miss(); returnValue = LruInsert(file); if (returnValue != 0) return returnValue; @@ -1501,14 +1503,34 @@ FileAccess(File file) * We now know that the file is open and that it is not the last one * accessed, so we need to move it to the head of the Lru ring. */ + pgstat_count_vfd_hit(); Delete(file); Insert(file); } + else + { + /* fd is open and already at MRU end */ + pgstat_count_vfd_hit(); + } return 0; } +/* + * GetVfdCacheOccupancy + * + * Return the number of physical file descriptors currently open in the + * VFD cache (nfile). This is the live cache size exposed by + * pg_stat_vfdcache.cache_size. + * + */ +int +GetVfdCacheOccupancy(void) +{ + return nfile; +} + /* * Called whenever a temporary file is deleted to report its size. */ diff --git a/src/backend/utils/activity/Makefile b/src/backend/utils/activity/Makefile index c37bfb350bb..da9ee955aec 100644 --- a/src/backend/utils/activity/Makefile +++ b/src/backend/utils/activity/Makefile @@ -31,6 +31,7 @@ OBJS = \ pgstat_shmem.o \ pgstat_slru.o \ pgstat_subscription.o \ + pgstat_vfdcache.o \ pgstat_wal.o \ pgstat_xact.o \ wait_event.o \ diff --git a/src/backend/utils/activity/meson.build b/src/backend/utils/activity/meson.build index 53bd5a246ca..0178d446106 100644 --- a/src/backend/utils/activity/meson.build +++ b/src/backend/utils/activity/meson.build @@ -16,6 +16,7 @@ backend_sources += files( 'pgstat_shmem.c', 'pgstat_slru.c', 'pgstat_subscription.c', + 'pgstat_vfdcache.c', 'pgstat_wal.c', 'pgstat_xact.c', ) diff --git a/src/backend/utils/activity/pgstat_vfdcache.c b/src/backend/utils/activity/pgstat_vfdcache.c new file mode 100644 index 00000000000..ca117b09ee6 --- /dev/null +++ b/src/backend/utils/activity/pgstat_vfdcache.c @@ -0,0 +1,75 @@ +/*------------------------------------------------------------------------- + * + * pgstat_vfdcache.c + * Implementation of VFD (Virtual File Descriptor) cache statistics. + * + * The VFD cache in fd.c maintains a cache of open file + * descriptors, bounded by max_files_per_process. When the cache is full, + * the least-recently-used entry is evicted (its OS fd closed) so a new + * file can be opened. A subsequent access to an evicted VFD must call + * open() again, incurring a syscall that a warm cache would have avoided. + * + * This module tracks hits (fd was open, no syscall), misses (fd was + * closed, open() required), and evictions (close() to make room) for the + * current backend. Because the VFD cache is strictly per-backend, these + * counters are also per-backend so no shared memory or locking is used. + * + * The view pg_stat_vfdcache exposes these counters for the current session + * together with the live cache occupancy and the configured limit, giving + * possibility to diagnose fd-cache pressure. + * + * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group + * + * IDENTIFICATION + * src/backend/utils/activity/pgstat_vfdcache.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include "pgstat.h" +#include "storage/fd.h" +#include "utils/timestamp.h" + +/* + * Per-backend VFD cache counters. + * + * Updated directly by fd.c via the inline macros in pgstat.h. Because + * only the owning backend ever writes these variables, no atomic ops or + * locks are required. + */ +PgStat_VfdCacheStats PendingVfdCacheStats; + +/* + * Timestamp of the last pg_stat_reset_vfdcache() call for this backend. + * Zero just means "never reset". + */ +static TimestampTz vfd_stats_reset_timestamp = 0; + +/* + * pgstat_fetch_stat_vfdcache + * + * Return a pointer to a filled PgStat_VfdCacheStats for the current + * backend. The returned pointer is valid until the next call. + */ +PgStat_VfdCacheStats * +pgstat_fetch_stat_vfdcache(void) +{ + /* counters live directly in PendingVfdCacheStats; just attach timestamp */ + PendingVfdCacheStats.stat_reset_timestamp = vfd_stats_reset_timestamp; + return &PendingVfdCacheStats; +} + +/* + * pgstat_reset_vfdcache + * + * Reset all VFD cache counters for the current backend and record the + * reset timestamp. + */ +void +pgstat_reset_vfdcache(void) +{ + memset(&PendingVfdCacheStats, 0, sizeof(PendingVfdCacheStats)); + vfd_stats_reset_timestamp = GetCurrentTimestamp(); +} \ No newline at end of file diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 5f907335990..24b8e0208c8 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1340,6 +1340,58 @@ pg_stat_get_buf_alloc(PG_FUNCTION_ARGS) PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->buf_alloc); } +Datum +pg_stat_get_vfd_hits(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(pgstat_fetch_stat_vfdcache()->vfd_hits); +} + +Datum +pg_stat_get_vfd_misses(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(pgstat_fetch_stat_vfdcache()->vfd_misses); +} + +Datum +pg_stat_get_vfd_evictions(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(pgstat_fetch_stat_vfdcache()->vfd_evictions); +} + +Datum +pg_stat_get_vfd_cache_size(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT32(GetVfdCacheOccupancy()); +} + +/* + * pg_stat_get_vfd_stat_reset_time + * Timestamp of the last pg_stat_reset_vfdcache() call, or NULL if + * the counters have never been reset. + */ +Datum +pg_stat_get_vfd_stat_reset_time(PG_FUNCTION_ARGS) +{ + TimestampTz ts = pgstat_fetch_stat_vfdcache()->stat_reset_timestamp; + + if (ts == 0) + PG_RETURN_NULL(); + + PG_RETURN_TIMESTAMPTZ(ts); +} + +/* + * pg_stat_reset_vfdcache + * Reset VFD cache counters for the current backend. + */ +Datum +pg_stat_reset_vfdcache(PG_FUNCTION_ARGS) +{ + pgstat_reset_vfdcache(); + PG_RETURN_VOID(); +} + + /* * When adding a new column to the pg_stat_io view and the * pg_stat_get_backend_io() function, add a new enum value here above diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 84e7adde0e5..ac46f7e9c25 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12842,5 +12842,41 @@ { oid => '8281', descr => 'hash', proname => 'hashoid8extended', prorettype => 'int8', proargtypes => 'oid8 int8', prosrc => 'hashoid8extended' }, +{ oid => '9560', + descr => 'statistics: number of VFD cache hits in current session', + proname => 'pg_stat_get_vfd_hits', + provolatile => 'v', proparallel => 'r', + prorettype => 'int8', proargtypes => '', + prosrc => 'pg_stat_get_vfd_hits' }, +{ oid => '9561', + descr => 'statistics: number of VFD cache misses in current session', + proname => 'pg_stat_get_vfd_misses', + provolatile => 'v', proparallel => 'r', + prorettype => 'int8', proargtypes => '', + prosrc => 'pg_stat_get_vfd_misses' }, +{ oid => '9562', + descr => 'statistics: number of VFD cache evictions in current session', + proname => 'pg_stat_get_vfd_evictions', + provolatile => 'v', proparallel => 'r', + prorettype => 'int8', proargtypes => '', + prosrc => 'pg_stat_get_vfd_evictions' }, +{ oid => '9563', + descr => 'statistics: number of physical fds currently open in VFD cache', + proname => 'pg_stat_get_vfd_cache_size', + provolatile => 'v', proparallel => 'r', + prorettype => 'int4', proargtypes => '', + prosrc => 'pg_stat_get_vfd_cache_size' }, +{ oid => '9564', + descr => 'statistics: timestamp of last VFD cache stats reset', + proname => 'pg_stat_get_vfd_stat_reset_time', + provolatile => 'v', proparallel => 'r', + prorettype => 'timestamptz', proargtypes => '', + prosrc => 'pg_stat_get_vfd_stat_reset_time' }, +{ oid => '9565', + descr => 'statistics: reset VFD cache counters for the current session', + proname => 'pg_stat_reset_vfdcache', + provolatile => 'v', proparallel => 'r', + prorettype => 'void', proargtypes => '', + prosrc => 'pg_stat_reset_vfdcache' }, ] diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 216b93492ba..ffbc1584a70 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -271,6 +271,22 @@ typedef struct PgStat_CheckpointerStats TimestampTz stat_reset_timestamp; } PgStat_CheckpointerStats; +/* --------- + * PgStat_VfdCacheStats Virtual File Descriptor cache statistics + * + * Tracks hit/miss/eviction events in the per-backend VFD cache (fd.c). + * Because the VFD cache is strictly per-backend these counters are updated + * without any locking. The view pg_stat_vfdcache exposes them together + * with the current cache occupancy. + * --------- + */ +typedef struct PgStat_VfdCacheStats +{ + PgStat_Counter vfd_hits; /* fd was open, no open() syscall needed */ + PgStat_Counter vfd_misses; /* fd was VFD_CLOSED, open() was required */ + PgStat_Counter vfd_evictions; /* close() called to free a slot for a new fd */ + TimestampTz stat_reset_timestamp; +} PgStat_VfdCacheStats; /* * Types related to counting IO operations @@ -592,6 +608,15 @@ extern PgStat_BgWriterStats *pgstat_fetch_stat_bgwriter(void); extern void pgstat_report_checkpointer(void); extern PgStat_CheckpointerStats *pgstat_fetch_stat_checkpointer(void); +/* + * Functions in pgstat_vfdcache.c + */ + +extern PgStat_VfdCacheStats *pgstat_fetch_stat_vfdcache(void); +extern void pgstat_reset_vfdcache(void); +#define pgstat_count_vfd_hit() (PendingVfdCacheStats.vfd_hits++) +#define pgstat_count_vfd_miss() (PendingVfdCacheStats.vfd_misses++) +#define pgstat_count_vfd_eviction() (PendingVfdCacheStats.vfd_evictions++) /* * Functions in pgstat_io.c @@ -823,6 +848,12 @@ extern PGDLLIMPORT int pgstat_fetch_consistency; /* updated directly by bgwriter and bufmgr */ extern PGDLLIMPORT PgStat_BgWriterStats PendingBgWriterStats; +/* + * Variables in pgstat_vfdcache.c + */ + +/* updated directly by fd.cn (per-backend) */ +extern PGDLLIMPORT PgStat_VfdCacheStats PendingVfdCacheStats; /* * Variables in pgstat_checkpointer.c diff --git a/src/include/storage/fd.h b/src/include/storage/fd.h index 8ac466fd346..9a54bbdb18a 100644 --- a/src/include/storage/fd.h +++ b/src/include/storage/fd.h @@ -149,6 +149,7 @@ extern char *FilePathName(File file); extern int FileGetRawDesc(File file); extern int FileGetRawFlags(File file); extern mode_t FileGetRawMode(File file); +extern int GetVfdCacheOccupancy(void); /* Operations used for sharing named temporary files */ extern File PathNameCreateTemporaryFile(const char *path, bool error_on_failure); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 32bea58db2c..be717049da1 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2356,6 +2356,16 @@ pg_stat_user_tables| SELECT relid, stats_reset FROM pg_stat_all_tables WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); +pg_stat_vfdcache| SELECT pg_stat_get_vfd_hits() AS hits, + pg_stat_get_vfd_misses() AS misses, + pg_stat_get_vfd_evictions() AS evictions, + pg_stat_get_vfd_cache_size() AS cache_size, + (current_setting('max_files_per_process'::text))::integer AS max_cache_size, + CASE + WHEN ((pg_stat_get_vfd_hits() + pg_stat_get_vfd_misses()) = 0) THEN NULL::double precision + ELSE ((pg_stat_get_vfd_hits())::double precision / ((pg_stat_get_vfd_hits() + pg_stat_get_vfd_misses()))::double precision) + END AS hit_ratio, + pg_stat_get_vfd_stat_reset_time() AS stats_reset; pg_stat_wal| SELECT wal_records, wal_fpi, wal_bytes, -- 2.34.1
