Обсуждение: Postgres Load Profile

On Mon, Nov 3, 2025 at 5: 56 PM Sam Stearns <sam. stearns@ dat. com> wrote: Howdy, 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

ZjQcmQRYFpfptBannerStart

This Message Is From an External Sender

This message came from outside your organization.

 

ZjQcmQRYFpfptBannerEnd

On Mon, Nov 3, 2025 at 5:56 PM Sam Stearns <sam.stearns@dat.com> wrote:

Howdy,

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)

https://www.postgresql.org/docs/17/monitoring-stats.html

 

• User calls
• Parses (SQL)
• Hard parses (SQL)
• Executes (SQL)

Probably not unless you want to set log_statement=all and then parse log_directory/log_filename.

 

• Transactions per second

Does TPS make any sense beyond when DBMS is running a "strict monoculture" application like OLTP with zero report generation?  Because a five hour SELECT that joins 42 tables in addition to 37 hairy subqueries is just as much a transaction as is a 5 microsecond SELECT of one customer's records using a hash index, and an equally fast INSERT of three records.

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!

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

ZjQcmQRYFpfptBannerStart

This Message Is From an External Sender

This message came from outside your organization.

 

ZjQcmQRYFpfptBannerEnd

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