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: Keith <keith@keithf4.com>
Date: Fri, 10 Apr 2026 14:34:22 -0400
Message-ID: 
 <CAHw75vtLsBL-O5EGVTgzZR=MUXFG=k0r3vEYma7YVdaiaDZJ1Q@mail.gmail.com>
Subject: Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without
 Third-Party Tools
To: mahamood hussain <hussain.ieg@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e82be2064f1f63f4"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHw75vtLsBL-O5EGVTgzZR%3DMUXFG%3Dk0r3vEYma7YVdaiaDZJ1Q%40mail.gmail.com>
Precedence: bulk

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

For log analysis, pgbadger (https://pgbadger.darold.net/) is the best tool
I've come across. Just be sure to adjust your log_line_prefix to include
information such as client host/IP, role, database and error codes. Turn on
other settings such as logging checkpointing, temp file generation and
locking. The more you log, the more that pgbadger can help analyze. Just
keep an eye on your log generation since you don't want that to in turn
affect your database. If you can write the logs to a different mount point.
that can isolate that IO from the database.

For trending other things, I'd recommend starting with something like
Prometheus (https://prometheus.io/) & Grafana (https://grafana.com/). Get
an exporter set up (sql_exporter is a good one -
https://github.com/burningalchemist/sql_exporter) and set up alerts and
trending your metrics so you can go back and look at history when those
alerts fire. If you need to start scaling bigger, then you can start
looking into something like Datadog and since you'll already have a better
idea of what you actually need to monitor by then, it won't seem nearly as
overwhelming jumping to something like that.

Keith

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.
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><div>For log analysis, pgbadger (<a href=
=3D"https://pgbadger.darold.net/">https://pgbadger.darold.net/</a>) is the =
best tool I&#39;ve come across. Just be sure to adjust your log_line_prefix=
 to include information such as client host/IP, role, database and error co=
des. Turn on other settings such as logging checkpointing, temp file genera=
tion and locking. The more you log, the more that pgbadger can help analyze=
. Just keep an eye on your log generation since you don&#39;t want that to =
in turn affect your database. If you can write the logs to a different moun=
t point. that can isolate that IO from the database.</div><div><br></div><d=
iv>For trending other things, I&#39;d recommend starting with something lik=
e Prometheus (<a href=3D"https://prometheus.io/">https://prometheus.io/</a>=
) &amp; Grafana (<a href=3D"https://grafana.com/">https://grafana.com/</a>)=
. Get an exporter set up (sql_exporter is a good one -=C2=A0<a href=3D"http=
s://github.com/burningalchemist/sql_exporter">https://github.com/burningalc=
hemist/sql_exporter</a>) and set up alerts and trending your metrics so you=
 can go back and look at history when those alerts fire. If you need to sta=
rt scaling bigger, then you can start looking into something like Datadog a=
nd since you&#39;ll already have a better idea of what you actually need to=
 monitor by then, it won&#39;t seem nearly as overwhelming jumping to somet=
hing like that.</div><div><br></div><div>Keith</div></div><br><div class=3D=
"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">O=
n Fri, Apr 10, 2026 at 1:49=E2=80=AFPM mahamood hussain &lt;<a href=3D"mail=
to:hussain.ieg@gmail.com">hussain.ieg@gmail.com</a>&gt; wrote:<br></div><bl=
ockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-lef=
t: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 <b>recommendations on free or lightweight too=
ls</b> 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>

--000000000000e82be2064f1f63f4--