Re: Postgres Load Profile

On Mon, 2025-11-03 at 14:56 -0800, Sam Stearns wrote:
> Does Postgres have any tables you can query to find out information such as:
>  * Logical reads
>  * Block changes
>  * Physical reads
>  * Physical writes
>  * Read IO requests
>  * Write IO requests
>  * Read IO (MB)
>  * Write IO (MB)
>  * User calls
>  * Parses (SQL)
>  * Hard parses (SQL)
>  * Executes (SQL)
>  * Transactions per second

That smells like Oracle database.

PostgreSQL does things differently, so not all of the above measures make sense.
To make up, there are things that you should measure in a PostgreSQL database
that don't exist in an Oracle database.

PostgreSQL doesn't do direct I/O, so it has no control over which read requests
actually cause I/O to happen and which ones can be satisfied from the kernel
page cache.

You can find statistics about read and write activity in pg_stat_database
(per database) and pg_stat_statements (per statement).  pg_stat_statements will
also tell you how often statements were executed.

In PostgreSQL, statements are always planned, unless you explicitly use a
prepared statement or run static SQL from a function.

You might want to look at pg_stat_io for overall I/O statistics per operation
and object type.

You also should look at pg_stat_all_tables for activities per table, including
the important VACUUM-related statistics.

Yours,
Laurenz Albe