Subject: Postgres Load Profile

From: Sam Stearns <[email-masked]>
Date: Mon, 3 Nov 2025 14:56:27 -0800

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)
   - User calls
   - Parses (SQL)
   - Hard parses (SQL)
   - Executes (SQL)
   - Transactions per second

Thanks,

Sam

-- 

Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com

<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link>

---

From: Ron Johnson <[email-masked]>
Date: Mon, 3 Nov 2025 21:49:01 -0500

> Does Postgres have any tables you can query to find out information such
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.

> 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!

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 04 Nov 2025 06:25:35 +0100

On Mon, 2025-11-03 at 14:56 -0800, Sam Stearns wrote:
>  * 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

---

From: Sam Stearns <[email-masked]>
Date: Tue, 4 Nov 2025 09:39:04 -0800

Thank you, Ron!

On Mon, Nov 3, 2025 at 6:49 PM Ron Johnson <[email-masked]> wrote:

> 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
>> 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.
>
> --

-- 

Samuel Stearns
Team Lead - Database

---

From: Sam Stearns <[email-masked]>
Date: Tue, 4 Nov 2025 09:39:26 -0800

Thank you, Laurenz!

On Mon, Nov 3, 2025 at 9:25 PM Laurenz Albe <[email-masked]>
wrote:

> 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

-- 

Samuel Stearns
Team Lead - Database