Use the pg_stat_kcache extension to collect file system read and write statistics - ApsaraDB RDS

The pg_stat_kcache extension collects statistics on read and write operations at the file system level.

Prerequisites

Before you begin, make sure that:

Your RDS instance runs PostgreSQL 10 or later.
Your RDS instance runs minor engine version 20230830 or later. If your instance runs PostgreSQL 17, the minor engine version must be 20241030 or later.
Both pg_stat_statements and pg_stat_kcache are listed in the shared_preload_libraries parameter, because pg_stat_kcache depends on pg_stat_statements and both must load at database startup. To update this parameter, see Manage the parameters of an ApsaraDB RDS for PostgreSQL instance.

Important

ApsaraDB RDS is standardizing extension management and tightening security in minor engine version updates. You cannot create pg_stat_kcache on instances running a minor engine version earlier than 20230830, even though the extension worked on some of those versions. For details, see [Product changes/Feature changes] Limits on extension creation for ApsaraDB RDS for PostgreSQL instances.

If you already created the extension on a minor engine version earlier than 20230830, you can continue using it.
If you are creating or recreating the extension, first update to the latest minor engine version. See Update the minor engine version.

Use a privileged account to create or drop the extension.

Create the extension:

CREATE EXTENSION pg_stat_kcache;

Drop the extension:

DROP EXTENSION pg_stat_kcache;

Any database account can run this query:

SELECT * FROM pg_stat_kcache();

Only privileged accounts can reset accumulated statistics:

select pg_stat_kcache_reset();