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

On Fri, Apr 10, 2026 at 1:49 PM mahamood hussain <hussain(dot)ieg(at)gmail(dot)com>
wrote:

> 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.
>
Have you set log_min_duration_statement to some number of milliseconds?
When you do that, the query and its parameters show up in the log file.
Grep for "duration:" to find statements taking longer than *threshold*
milliseconds.

Does it require some manual effort? Sure. But it's free.

Barring that, try installing pgbadger.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!