MIME-Version: 1.0
References: 
 <CAGc_7HnrQaAna-6=jCyN+ZA1MnQ_BqJKZfWcfR8K1wKxunBVQA@mail.gmail.com>
In-Reply-To: 
 <CAGc_7HnrQaAna-6=jCyN+ZA1MnQ_BqJKZfWcfR8K1wKxunBVQA@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 10 Apr 2026 14:23:50 -0400
Message-ID: 
 <CANzqJaAwwD4za3d7HA-_2HjMKH=H-0c0eeEjVazyTxkugCTLcw@mail.gmail.com>
Subject: Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without
 Third-Party Tools
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000b7d8ab064f1f3cf3"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaAwwD4za3d7HA-_2HjMKH%3DH-0c0eeEjVazyTxkugCTLcw%40mail.gmail.com>
Precedence: bulk

--000000000000b7d8ab064f1f3cf3
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Apr 10, 2026 at 1:49=E2=80=AFPM mahamood hussain <hussain.ieg@gmail=
.com>
wrote:

> Hi Team,
>
> We are currently working on a migration project from DB2 to PostgreSQL.
> Post-migration, we=E2=80=99re observing several performance issues such a=
s
> long-running queries and occasional instance crashes. It also appears tha=
t
> some application-side workloads may not be optimized for PostgreSQL.
>
> From a DBA perspective, I=E2=80=99m looking to proactively identify probl=
em
> areas=E2=80=94such as:
>
>    - Long-running queries
>    - Jobs/stored procedures consuming high temp space
>    - Queries resulting in sequential scans due to missing indexes
>    - Lock waits, deadlocks, and memory-heavy operations
>
> We already have key parameters enabled (pg_stat_statements, pg_buffercach=
e,
> etc.), and PostgreSQL is generating logs in .csv format. However, the
> main challenge is efficiently analyzing these logs and identifying
> performance bottlenecks at scale (databases ranging from ~1TB to 15TB).
>
> We currently don=E2=80=99t have third-party monitoring tools like Datadog=
, so I=E2=80=99m
> looking for *recommendations on free or lightweight tools* and best
> practices to:
>
>    - Parse and analyze PostgreSQL logs (especially CSV logs)
>    - Identify top resource-consuming queries and patterns
>    - Correlate temp usage, memory pressure, and query behavior
>    - Generate actionable insights for the engineering team
>
> Any suggestions on tools, scripts, or approaches that have worked well in
> similar large-scale environments would be greatly appreciated.
>
Have you set log_min_duration_statement to some number of milliseconds?
When you do that, the query and its parameters show up in the log file.
Grep for "duration:" to find statements taking longer than *threshold*
 milliseconds.

Does it require some manual effort?  Sure.  But it's free.

Barring that, try installing pgbadger.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--000000000000b7d8ab064f1f3cf3
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Apr 10, 2026 at 1:49=E2=80=AFPM m=
ahamood hussain &lt;<a href=3D"mailto:hussain.ieg@gmail.com">hussain.ieg@gm=
ail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container=
"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><p>Hi =
Team,</p>
<p>We are currently working on a migration project from DB2 to PostgreSQL. =
Post-migration, we=E2=80=99re observing several performance issues such as =
long-running queries and occasional instance crashes. It also appears that =
some application-side workloads may not be optimized for PostgreSQL.</p>
<p>From a DBA perspective, I=E2=80=99m looking to proactively identify prob=
lem areas=E2=80=94such as:</p>
<ul>
<li>
Long-running queries
</li>
<li>
Jobs/stored procedures consuming high temp space
</li>
<li>
Queries resulting in sequential scans due to missing indexes
</li>
<li>
Lock waits, deadlocks, and memory-heavy operations
</li>
</ul>
<p>We already have key parameters enabled (<code>pg_stat_statements</code>,=
 <code>pg_buffercache</code>, etc.), and PostgreSQL is generating logs in <=
code>.csv</code> format. However, the main challenge is efficiently analyzi=
ng these logs and identifying performance bottlenecks at scale (databases r=
anging from ~1TB to 15TB).</p>
<p>We currently don=E2=80=99t have third-party monitoring tools like Datado=
g, so I=E2=80=99m looking for <strong>recommendations on free or lightweigh=
t tools</strong> and best practices to:</p>
<ul>
<li>
Parse and analyze PostgreSQL logs (especially CSV logs)
</li>
<li>
Identify top resource-consuming queries and patterns
</li>
<li>
Correlate temp usage, memory pressure, and query behavior
</li>
<li>
Generate actionable insights for the engineering team
</li>
</ul>
<p>Any suggestions on tools, scripts, or approaches that have worked well i=
n similar large-scale environments would be greatly appreciated.</p></div>
</blockquote></div><div>Have you set log_min_duration_statement to some num=
ber of milliseconds?=C2=A0 When you do that, the query and its parameters s=
how up in the log file.=C2=A0 Grep for &quot;duration:&quot; to find statem=
ents taking longer than <i>threshold</i>=C2=A0milliseconds.</div><div><br><=
/div><div>Does it require some manual effort?=C2=A0 Sure.=C2=A0 But it&#39;=
s free.</div><div><br></div><div>Barring that, try installing pgbadger.</di=
v><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><div =
dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacte=
d&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><di=
v><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--000000000000b7d8ab064f1f3cf3--