From: bertrand HARTWIG <hartwig.bertrand@gmail.com>
Message-Id: <873AFF54-57EA-4A69-985E-7478E25A502A@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_D860EA64-BA1B-4C55-A516-9D95DBA83C1A"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without
 Third-Party Tools
Date: Sat, 11 Apr 2026 08:07:09 +0200
In-Reply-To: 
 <CAGc_7HnrQaAna-6=jCyN+ZA1MnQ_BqJKZfWcfR8K1wKxunBVQA@mail.gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
To: mahamood hussain <hussain.ieg@gmail.com>
References: 
 <CAGc_7HnrQaAna-6=jCyN+ZA1MnQ_BqJKZfWcfR8K1wKxunBVQA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/873AFF54-57EA-4A69-985E-7478E25A502A%40gmail.com>
Precedence: bulk


--Apple-Mail=_D860EA64-BA1B-4C55-A516-9D95DBA83C1A
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

Hello Keith,

For query analysis, optimization, and overall database performance =
tuning, I recommend using pgAssistant: =
https://github.com/beh74/pgassistant-community. It requires =
pg_stat_statements but does not rely on pg_buffercache.

For monitoring, I strongly recommend pg_watch: =
https://github.com/cybertec-postgresql/pgwatch/.

In my case, I use both tools daily across an environment of around 500 =
PostgreSQL instances. They are clearly complementary: pgAssistant is =
very effective for deep analysis and reporting, while pg_watch provides =
robust monitoring and observability.

I also leverage the pgAssistant API to automatically generate weekly =
reports in Markdown format, which I then store in Git for versioning and =
tracking.

Both tools are available as Docker containers, which makes deployment =
and scaling straightforward.

Regards
, =20
Bertrand

> Le 10 avr. 2026 =C3=A0 19:48, mahamood hussain <hussain.ieg@gmail.com> =
a =C3=A9crit :
>=20
> Hi Team,
>=20
> 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.
>=20
> =46rom a DBA perspective, I=E2=80=99m looking to proactively identify =
problem areas=E2=80=94such as:
>=20
> 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 =
~1TB to 15TB).
>=20
> 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:
>=20
> 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.
>=20


--Apple-Mail=_D860EA64-BA1B-4C55-A516-9D95DBA83C1A
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html aria-label=3D"message body"><head><meta http-equiv=3D"content-type" =
content=3D"text/html; charset=3Dutf-8"></head><body =
style=3D"overflow-wrap: break-word; -webkit-nbsp-mode: space; =
line-break: after-white-space;"><div>Hello =
Keith,</div><div><br></div><div>For query analysis, optimization, and =
overall database performance tuning, I recommend using pgAssistant: =
https://github.com/beh74/pgassistant-community. It requires =
pg_stat_statements but does not rely on =
pg_buffercache.</div><div><br></div><div>For monitoring, I strongly =
recommend pg_watch: =
https://github.com/cybertec-postgresql/pgwatch/.</div><div><br></div><div>=
In my case, I use both tools daily across an environment of around 500 =
PostgreSQL instances. They are clearly complementary: pgAssistant is =
very effective for deep analysis and reporting, while pg_watch provides =
robust monitoring and observability.</div><div><br></div><div>I also =
leverage the pgAssistant API to automatically generate weekly reports in =
Markdown format, which I then store in Git for versioning and =
tracking.</div><div><br></div><div>Both tools are available as Docker =
containers, which makes deployment and scaling =
straightforward.</div><div><br></div><div>Regards</div><div>, =
&nbsp;</div><div>Bertrand</div><div><br><blockquote type=3D"cite"><div>Le =
10 avr. 2026 =C3=A0 19:48, mahamood hussain =
&lt;hussain.ieg@gmail.com&gt; a =C3=A9crit :</div><br =
class=3D"Apple-interchange-newline"><div><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>=46rom a DBA perspective, I=E2=80=99m looking to =
proactively identify problem 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 analyzing these logs and identifying =
performance bottlenecks at scale (databases ranging from ~1TB to =
15TB).</p><p>We currently don=E2=80=99t have third-party monitoring =
tools like Datadog, so I=E2=80=99m looking for <strong>recommendations =
on free or lightweight 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 in similar large-scale environments would be greatly =
appreciated.</p></div>
</div></blockquote></div><br></body></html>=

--Apple-Mail=_D860EA64-BA1B-4C55-A516-9D95DBA83C1A--