Docs

RDS for PostgreSQL

Extensions

System read and write information statistics (pg_stat_kcache)

pg_stat_kcache is an extension that collects statistics on PostgreSQL's file system read/write information during operation.

Implementation principle

pg_stat_kcache calls the Linux interface getrusage before and after query execution to retrieve process-related resource information, then calculates the difference to determine the resources used by the current query.

tip

The definitions of functions and views related to pg_stat_kcache depend on the pg_stat_statements extension. Before use, you need to Modify the shared_preload_libraries parameter, adding pg_stat_statements and pg_stat_kcache to its Running value.

Modifying the shared_preload_libraries parameter requires a Restart instance. Refer to Modifying parameters and operate with caution.

Core objects

The core objects of pg_stat_kcache are shown in the following table.

Type of object	Object name	Object description	Examples
GUC parameter	pg_stat_kcache.linux_hz	Integer type, minimum value -1, maximum value INT_MAX, default value -1. Compensation value for sampling error, see: linux CONFIG_HZ.	set pg_stat_kcache.linux_hz = -1;
		pg_stat_kcache.track	Enumeration type with possible values: [none, top, all]. Default value is top, which controls which SQL statements to trace, with meanings as follows: top: trace the top-level SQL statements, usually those sent directly by the client. all: trace both top-level and nested statements, such as nested SQL statements executed within a Function. none: disable tracing.	set pg_stat_kcache.track = all;
		pg_stat_kcache.track_planning	bool type, default value is false. Effective in PostgreSQL 13 and later versions. Indicates whether to track resource usage during query optimization.	set pg_stat_kcache.track_planning = true;
Functions	pg_stat_kcache()	Return tracing information recorded in shared memory. It can be called directly but is usually accessed through the pg_stat_kcache_detail view.	select * from pg_stat_kcache(); select pg_stat_kcache();
Functions		pg_stat_kcache_reset()	Clear recorded trace information.	select pg_stat_kcache_reset();
Views	pg_stat_kcache_detail	Results of join queries on pg_stat_kcache(), pg_stat_statements, pg_database, and pg_roles.	select * from pg_stat_kcache_detail;
Views		pg_stat_kcache	Aggregate the pg_stat_kcache_detail view by datname dimension with sum.	Select * from pg_stat_kcache;

Meaning of function and view column names

Column name	Type	Description
datname	name	Database name.
plan_user_time	double precision	pg_stat_kcache.track_planning enables and represents the user CPU time consumed for SQL query optimization, in seconds or milliseconds. Effective in PostgreSQL 13 and later versions.
plan_system_time	double precision	pg_stat_kcache.track_planning enables and represents the system CPU time consumed for SQL query optimization, in seconds or milliseconds. Effective for PostgreSQL version 13 and above.
plan_minflts	bigint	pg_stat_kcache.track_planning enabled indicates the number of soft page faults during SQL query optimization. Effective in PostgreSQL 13 and later versions.
plan_majflts	bigint	pg_stat_kcache.track_planning is enabled, indicating the number of hard page faults during SQL query optimization. Effective for PostgreSQL 13 and above.
plan_nswaps	bigint	Enabled with pg_stat_kcache.track_planning, indicating the number of swaps during SQL query optimization. Effective in PostgreSQL 13 and above.
plan_reads	bigint	pg_stat_kcache.track_planning enabled means the number of read bytes initiated through the file system during SQL query optimization. Effective in PostgreSQL 13 and later versions.
plan_reads_blks	bigint	Enabling pg_stat_kcache.track_planning tracks the number of 8K blocks read via the file system during SQL query optimization. Effective in PostgreSQL 13 and later versions.
plan_writes	bigint	Enabling pg_stat_kcache.track_planning means tracking the number of write bytes initiated through the file system during SQL query optimization. Effective in PostgreSQL 13 and later versions.
plan_writes_blks	bigint	Effective when pg_stat_kcache.track_planning is enabled, indicates the number of 8K blocks written via the file system during SQL query planning. Effective from PostgreSQL 13 and later versions.
plan_msgsnds	bigint	pg_stat_kcache.track_planning enable takes effect, representing the number of IPC messages sent during SQL query optimization. Effective in PostgreSQL 13 and later versions.
plan_msgrcvs	bigint	pg_stat_kcache.track_planning enabled means the number of IPC messages received during SQL query optimization. Effective in PostgreSQL 13 and above.
plan_nsignals	bigint	pg_stat_kcache.track_planning is enabled and effective, indicating the number of signals received during SQL query optimization. Effective for PostgreSQL 13 and above.
plan_nvcsws	bigint	Enabled with pg_stat_kcache.track_planning, indicating the number of voluntary context switches during SQL query optimization. Effective for PostgreSQL 13 and above.
plan_nivcsws	bigint	pg_stat_kcache.track_planning enable takes effect, representing the number of passive context switches occurring during SQL query optimization. Effective in PostgreSQL 13 and later versions.
exec_user_time	double precision	Indicates the user CPU time consumed during the SQL execution phase, in seconds or milliseconds.
exec_system_time	double precision	Indicates system CPU time consumed during the SQL execution phase, in seconds or milliseconds.
exec_minflts	bigint	Represents the number of soft page faults during SQL execution.
exec_majflts	bigint	Indicates the number of hard page faults during the SQL execution phase.
exec_nswaps	bigint	Represents the number of swaps during SQL execution.
exec_reads	bigint	Indicates the number of read bytes initiated through the file system during the SQL execution phase.
exec_reads_blks	bigint	Represents the SQL execution phase, indicating the number of 8K blocks read through the file system.
exec_writes	bigint	Indicates the number of bytes written through the file system during the SQL execution phase.
exec_writes_blks	bigint	Represents the SQL execution phase, indicating the number of 8K blocks written through the file system.
exec_msgsnds	bigint	Indicates the number of IPC messages sent during the SQL execution phase.
exec_msgrcvs	bigint	Represents the number of IPC messages received during SQL execution stage.
exec_nsignals	bigint	Indicates the number of signals received during the SQL execution phase.
exec_nvcsws	bigint	The number of voluntary context switches during the SQL execution phase.
exec_nivcsws	bigint	Represents the number of passive context switches occurring during the SQL execution phase.

Install extension

Use the following command to install the extension.

tip

A super account is required to create the extension.

Using extensions

Prerequisites

GUC parameter configuration is completed.

Added pg_stat_statements and pg_stat_kcache to the shared_preload_libraries list.

Extension is installed.

Data is imported.

Extension usage

Enable read/write and check resource usage.

sysbench --test=./tests/include/oltp_legacy/oltp.lua --db-driver=pgsql --pgsql-db=sysbench --pgsql-user=root --pgsql-password=Bd2021_bd --pgsql-port=5432 --pgsql-host=10.249.184.214 --oltp-tables-count=10 --oltp-table-size=1000000 --max-time=120 --max-requests=0 --num-threads=50 --report-interval=3 --forced-shutdown=1 run

Check resource usage.

select * from pg_stat_kcache_detail;
select * from pg_stat_kcache;
select pg_stat_kcache_reset();

Last updated: 2026.01.13 18:44:18