public inbox for [email protected]
help / color / mirror / Atom feedPostgres Load Profile
5+ messages / 3 participants
[nested] [flat]
* Postgres Load Profile
@ 2025-11-03 22:56 Sam Stearns <[email protected]>
2025-11-04 02:49 ` Re: Postgres Load Profile Ron Johnson <[email protected]>
2025-11-04 05:25 ` Re: Postgres Load Profile Laurenz Albe <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: Sam Stearns @ 2025-11-03 22:56 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>; +Cc: Henry Ashu <[email protected]>
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres Load Profile
2025-11-03 22:56 Postgres Load Profile Sam Stearns <[email protected]>
@ 2025-11-04 02:49 ` Ron Johnson <[email protected]>
2025-11-04 17:39 ` Re: Postgres Load Profile Sam Stearns <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2025-11-04 02:49 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Mon, Nov 3, 2025 at 5:56 PM Sam Stearns <[email protected]> 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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres Load Profile
2025-11-03 22:56 Postgres Load Profile Sam Stearns <[email protected]>
2025-11-04 02:49 ` Re: Postgres Load Profile Ron Johnson <[email protected]>
@ 2025-11-04 17:39 ` Sam Stearns <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Sam Stearns @ 2025-11-04 17:39 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Thank you, Ron!
On Mon, Nov 3, 2025 at 6:49 PM Ron Johnson <[email protected]> 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
> On Mon, Nov 3, 2025 at 5:56 PM Sam Stearns <[email protected]> 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!
>
--
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres Load Profile
2025-11-03 22:56 Postgres Load Profile Sam Stearns <[email protected]>
@ 2025-11-04 05:25 ` Laurenz Albe <[email protected]>
2025-11-04 17:39 ` Re: Postgres Load Profile Sam Stearns <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2025-11-04 05:25 UTC (permalink / raw)
To: Sam Stearns <[email protected]>; Pgsql-admin <[email protected]>; +Cc: Henry Ashu <[email protected]>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres Load Profile
2025-11-03 22:56 Postgres Load Profile Sam Stearns <[email protected]>
2025-11-04 05:25 ` Re: Postgres Load Profile Laurenz Albe <[email protected]>
@ 2025-11-04 17:39 ` Sam Stearns <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Sam Stearns @ 2025-11-04 17:39 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Pgsql-admin <[email protected]>; Henry Ashu <[email protected]>
Thank you, Laurenz!
On Mon, Nov 3, 2025 at 9:25 PM Laurenz Albe <[email protected]>
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
> 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
>
>
--
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-11-04 17:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-03 22:56 Postgres Load Profile Sam Stearns <[email protected]>
2025-11-04 02:49 ` Ron Johnson <[email protected]>
2025-11-04 17:39 ` Sam Stearns <[email protected]>
2025-11-04 05:25 ` Laurenz Albe <[email protected]>
2025-11-04 17:39 ` Sam Stearns <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox