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

this might help - https://github.com/NikolayS/postgres_dba

On Fri, Apr 10, 2026 at 11:24 AM Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
wrote:

> 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!
>