MIME-Version: 1.0
References: 
 <CAGc_7HnrQaAna-6=jCyN+ZA1MnQ_BqJKZfWcfR8K1wKxunBVQA@mail.gmail.com>
 <CANzqJaAwwD4za3d7HA-_2HjMKH=H-0c0eeEjVazyTxkugCTLcw@mail.gmail.com>
In-Reply-To: 
 <CANzqJaAwwD4za3d7HA-_2HjMKH=H-0c0eeEjVazyTxkugCTLcw@mail.gmail.com>
From: flatley <tflatley@gmail.com>
Date: Sat, 11 Apr 2026 08:22:28 -0700
Message-ID: 
 <CAMCJ6Xx6qEkMvsV14-Vq7YxRPwwCMBAORNErMHkD3h4-xeHLVg@mail.gmail.com>
Subject: Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without
 Third-Party Tools
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f4f8da064f30d119"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMCJ6Xx6qEkMvsV14-Vq7YxRPwwCMBAORNErMHkD3h4-xeHLVg%40mail.gmail.com>
Precedence: bulk

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

this might help -  https://github.com/NikolayS/postgres_dba

On Fri, Apr 10, 2026 at 11:24=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.c=
om>
wrote:

> On Fri, Apr 10, 2026 at 1:49=E2=80=AFPM mahamood hussain <hussain.ieg@gma=
il.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 =
as
>> long-running queries and occasional instance crashes. It also appears th=
at
>> some application-side workloads may not be optimized for PostgreSQL.
>>
>> From a DBA perspective, I=E2=80=99m looking to proactively identify prob=
lem
>> 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_buffercache, 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 ~1T=
B
>> to 15TB).
>>
>> We currently don=E2=80=99t have third-party monitoring tools like Datado=
g, 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 i=
n
>> 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.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>

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

<div dir=3D"ltr">this might help -=C2=A0=C2=A0<a href=3D"https://github.com=
/NikolayS/postgres_dba">https://github.com/NikolayS/postgres_dba</a></div><=
br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=
=3D"gmail_attr">On Fri, Apr 10, 2026 at 11:24=E2=80=AFAM Ron Johnson &lt;<a=
 href=3D"mailto:ronljohnsonjr@gmail.com">ronljohnsonjr@gmail.com</a>&gt; wr=
ote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D=
"ltr"><div dir=3D"ltr">On Fri, Apr 10, 2026 at 1:49=E2=80=AFPM mahamood hus=
sain &lt;<a href=3D"mailto:hussain.ieg@gmail.com" target=3D"_blank">hussain=
.ieg@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote"><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d 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>
</blockquote></div>

--000000000000f4f8da064f30d119--