public inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: flatley <tflatley@gmail.com>
To: Ron Johnson <ronljohnsonjr@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:22:28 -0700
Message-ID: <CAMCJ6Xx6qEkMvsV14-Vq7YxRPwwCMBAORNErMHkD3h4-xeHLVg@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaAwwD4za3d7HA-_2HjMKH=H-0c0eeEjVazyTxkugCTLcw@mail.gmail.com>
References: <CAGc_7HnrQaAna-6=jCyN+ZA1MnQ_BqJKZfWcfR8K1wKxunBVQA@mail.gmail.com>
<CANzqJaAwwD4za3d7HA-_2HjMKH=H-0c0eeEjVazyTxkugCTLcw@mail.gmail.com>
this might help - https://github.com/NikolayS/postgres_dba
On Fri, Apr 10, 2026 at 11:24 AM Ron Johnson <ronljohnsonjr@gmail.com>
wrote:
> On Fri, Apr 10, 2026 at 1:49 PM mahamood hussain <hussain.ieg@gmail.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!
>
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: tflatley@gmail.com, ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools
In-Reply-To: <CAMCJ6Xx6qEkMvsV14-Vq7YxRPwwCMBAORNErMHkD3h4-xeHLVg@mail.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