Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vGL0U-00CsW9-QZ for pgsql-admin@arkaria.postgresql.org; Tue, 04 Nov 2025 17:39:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vGL0T-00FZfU-NI for pgsql-admin@arkaria.postgresql.org; Tue, 04 Nov 2025 17:39:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vGL0T-00FZed-AJ for pgsql-admin@lists.postgresql.org; Tue, 04 Nov 2025 17:39:44 +0000 Received: from mx0b-0039f802.pphosted.com ([205.220.176.45]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vGL0P-005y8o-2g for pgsql-admin@lists.postgresql.org; Tue, 04 Nov 2025 17:39:44 +0000 Received: from pps.filterd (m0209982.ppops.net [127.0.0.1]) by mx0b-0039f802.pphosted.com (8.18.1.11/8.18.1.11) with ESMTP id 5A4GZVQh3531265 for ; Tue, 4 Nov 2025 09:39:40 -0800 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=dat.com; h=cc :content-type:date:from:in-reply-to:message-id:mime-version :references:subject:to; s=ppdk230404; bh=xFj6bjE5Yx+Ao0PHqZHxm8/ mTnFqj6LXX50+vgx7O3Q=; b=t7kwt288CzwOtvgRoqaIOTPTWWghx4ha1HQdH6W 8lC7tom2piHgWnufZEzlhfL7kCit0rb4mZBtSYUMyj8d7kANo7Yu7NN9UXxR8iTl 174lNKdV29AEJ4Kf1+h3HU9MCzA0jQ6v47suu0howo0hz3MUvrft3NPCw5rORjDG PKlDM0bfw8rVX/lioMyxVJf8FqusJp1muQIU+F4SBFDaAQW/gGp8ocea8jfz4CXK RJmg/9Karb5M1yAwzhFaHXDPge9/j0pDN1ZrR5IoHjZYUHZJMyT1jSP0i9GAw0b/ DqcD54TVyijLkI1KmJTCjku8yjjzdO9bez/OdbGYuTnswig== Received: from mail-ej1-f70.google.com (mail-ej1-f70.google.com [209.85.218.70]) by mx0b-0039f802.pphosted.com (PPS) with ESMTPS id 4a6uq3tn3k-1 (version=TLSv1.3 cipher=TLS_AES_128_GCM_SHA256 bits=128 verify=NOT) for ; Tue, 04 Nov 2025 09:39:40 -0800 (PST) Received: by mail-ej1-f70.google.com with SMTP id a640c23a62f3a-b4813c6cbeeso541634266b.3 for ; Tue, 04 Nov 2025 09:39:39 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1762277978; x=1762882778; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=xFj6bjE5Yx+Ao0PHqZHxm8/mTnFqj6LXX50+vgx7O3Q=; b=e7C8zmM09R4//xg50cRvyRFLBs0PoEOMOpkChQXpjhkjnlMgPvA3n8v/oyq4Acw7ml eF7X+lEVsZBIm10o3BV4DeR6BS0446VjFEO0nmD8MuGyNCaVAymG89zwFkwK0SCh2fmD UJShI+8XMJwAbn3vpSgNOTDm2nIEv0EiNCyToTWITkgG0pYOms2GIc/m5ihLYmMxb1M1 xMg3cw5myvrwn4rE4U649OZezhf8XA2NzbFQhkGw5WFIZr59u+RCUsmgzPmgMQY9Rjf3 Ss0Lq9Lf3hTJhzpDUpnbatBQIOzDm37K4/jndyvNGlHy23hOrQ66KCbOW+rvyp6RlZid bhkw== X-Gm-Message-State: AOJu0Yy00DljIz9gJJTzymfILD50aYwKdNOjoAVn35RA7qE8NkmkR06w Ng2D97xI6kT8/PbLMES2u6ZmNfu1EtSyRnuXgDtPoxgYf99jWdH7RTJsbBQ/QKEQgLXvesgNCin pdSMWp7v1CUZtXg5gQ7Hr9WFmb2kVeZYzkQdymd+7I6GFSmgMCour9TadRZVwJQm48UAXmsKKPx whgntXXmX94U6kN1RZB+mXeTuR1hQJrhL0Eujcwdr4Q2PINLslkUdmise0Rsx/W0nYkc1lEaA= X-Gm-Gg: ASbGnct8JTyeMb5RcINUo77O+5ZpI2lFsM5oe8485pYe0yQoc2FWqzLAQ6EeS0BCCMT Gc0Gi7W9xiHGUsZOtH8hPC0iv5Yo1qRtaIdAViEV5iuNLcuHQkS7HNzPoagushI5b4WF8970wq2 kMQ/K3jckOBjNIGnZxKwPxa2JoLdwC4bOzm4udFhC/MqOjVrJvYGNs0g== X-Received: by 2002:a17:907:728c:b0:b6d:2f3f:3f98 with SMTP id a640c23a62f3a-b70704bc291mr1687676466b.41.1762277978003; Tue, 04 Nov 2025 09:39:38 -0800 (PST) X-Google-Smtp-Source: AGHT+IGh2tBFYLFyGl5+fvj4+5Fdn/pIoOA0VJrlxNT/y6tF9QwzJDradSUwf2nNnKYPQyB4LMQMKBu0NiRgfEPozmo= X-Received: by 2002:a17:907:728c:b0:b6d:2f3f:3f98 with SMTP id a640c23a62f3a-b70704bc291mr1687673966b.41.1762277977573; Tue, 04 Nov 2025 09:39:37 -0800 (PST) MIME-Version: 1.0 References: <42aa5759d334a7dad91b3142d3904b14ce3cf05f.camel@cybertec.at> In-Reply-To: <42aa5759d334a7dad91b3142d3904b14ce3cf05f.camel@cybertec.at> From: Sam Stearns Date: Tue, 4 Nov 2025 09:39:26 -0800 X-Gm-Features: AWmQ_bkr6drNrlTkc1y58ceul---CnHVN9UQ28MZ3D_aTb4sI6x-vtE5WVlWPR4 Message-ID: Subject: Re: Postgres Load Profile To: Laurenz Albe Cc: Pgsql-admin , Henry Ashu Content-Type: multipart/alternative; boundary="000000000000d966820642c85043" X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1121,Hydra:6.1.9,FMLib:17.12.100.49 definitions=2025-11-04_02,2025-11-03_03,2025-10-01_01 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d966820642c85043 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you, Laurenz! On Mon, Nov 3, 2025 at 9:25=E2=80=AFPM Laurenz Albe 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: > * Logic= al > reads > * Block changes > * Physical reads > * Physical writes > * Re= ad > 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 suc= h 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 data= base > that don't exist in an Oracle database. > > PostgreSQL doesn't do direct I/O, so it has no control over which read re= quests > actually cause I/O to happen and which ones can be satisfied from the ker= nel > 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_statement= s 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 opera= tion > and object type. > > You also should look at pg_stat_all_tables for activities per table, incl= uding > the important VACUUM-related statistics. > > Yours, > Laurenz Albe > > --=20 Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com --000000000000d966820642c85043 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you, Laurenz!

On Mon, Nov 3, 20= 25 at 9:25=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
<= /u>
On Mon, 2025-11-03 at 14:=E2=80=8A56 -0800, Sam Stearns wrote: > Does Po= stgres have any tables you can query to find out information such as: > = =C2=A0* Logical reads > =C2=A0* Block changes > =C2=A0* Physical read= s > =C2=A0* Physical writes > =C2=A0* Read IO
ZjQcmQRYFpfptBannerStart
This Message Is From an External Sender
This message came from outside your organization.
=C2=A0
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 su=
ch 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 =
requests
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


--

Samuel Stearns
Team Lead - Databas= e
c: 971 76= 2 6879 = | o: 97= 1 762 6879 | D= AT.com


--000000000000d966820642c85043--