public inbox for pgsql-admin@postgresql.org  

help / color / mirror / Atom feed

From: bertrand HARTWIG <hartwig.bertrand@gmail.com>
To: mahamood hussain <hussain.ieg@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools
Date: Sat, 11 Apr 2026 08:07:09 +0200
Message-ID: <873AFF54-57EA-4A69-985E-7478E25A502A@gmail.com> (raw)
In-Reply-To: <CAGc_7HnrQaAna-6=jCyN+ZA1MnQ_BqJKZfWcfR8K1wKxunBVQA@mail.gmail.com>
References: <CAGc_7HnrQaAna-6=jCyN+ZA1MnQ_BqJKZfWcfR8K1wKxunBVQA@mail.gmail.com>

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
,  
Bertrand

> Le 10 avr. 2026 à 19:48, mahamood hussain <hussain.ieg@gmail.com> a écrit :
> 
> Hi Team,
> 
> We are currently working on a migration project from DB2 to PostgreSQL. Post-migration, we’re 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.
> 
> From a DBA perspective, I’m looking to proactively identify problem areas—such 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 ~1TB to 15TB).
> 
> We currently don’t have third-party monitoring tools like Datadog, so I’m 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.
> 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: hartwig.bertrand@gmail.com, hussain.ieg@gmail.com, pgsql-admin@lists.postgresql.org
  Subject: Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools
  In-Reply-To: <873AFF54-57EA-4A69-985E-7478E25A502A@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox