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.
