Message-ID: <42aa5759d334a7dad91b3142d3904b14ce3cf05f.camel@cybertec.at>
Subject: Re: Postgres Load Profile
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Sam Stearns <sam.stearns@dat.com>, Pgsql-admin
	 <pgsql-admin@lists.postgresql.org>
Cc: Henry Ashu <henry.ashu@dat.com>
Date: Tue, 04 Nov 2025 06:25:35 +0100
In-Reply-To: 
 <CAN6TVjmH8=dszn3WqSE4MsYEZgCT1gQhufoSnPBn-Omcu9-EkQ@mail.gmail.com>
References: 
	<CAN6TVjmH8=dszn3WqSE4MsYEZgCT1gQhufoSnPBn-Omcu9-EkQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/42aa5759d334a7dad91b3142d3904b14ce3cf05f.camel%40cybertec.at>
Precedence: bulk

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:
> =C2=A0* Logical reads
> =C2=A0* Block changes
> =C2=A0* Physical reads
> =C2=A0* Physical writes
> =C2=A0* Read IO requests
> =C2=A0* Write IO requests
> =C2=A0* Read IO (MB)
> =C2=A0* Write IO (MB)
> =C2=A0* User calls
> =C2=A0* Parses (SQL)
> =C2=A0* Hard parses (SQL)
> =C2=A0* Executes (SQL)
> =C2=A0* Transactions per second

That smells like Oracle database.

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

PostgreSQL doesn't do direct I/O, so it has no control over which read requ=
ests
actually cause I/O to happen and which ones can be satisfied from the kerne=
l
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 operati=
on
and object type.

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

Yours,
Laurenz Albe