Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools

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(dot)ieg(at)gmail(dot)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.
>