public inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: Ron Johnson <ronljohnsonjr@gmail.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools
Date: Fri, 10 Apr 2026 14:23:50 -0400
Message-ID: <CANzqJaAwwD4za3d7HA-_2HjMKH=H-0c0eeEjVazyTxkugCTLcw@mail.gmail.com> (raw)
In-Reply-To: <CAGc_7HnrQaAna-6=jCyN+ZA1MnQ_BqJKZfWcfR8K1wKxunBVQA@mail.gmail.com>
References: <CAGc_7HnrQaAna-6=jCyN+ZA1MnQ_BqJKZfWcfR8K1wKxunBVQA@mail.gmail.com>
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: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools
In-Reply-To: <CANzqJaAwwD4za3d7HA-_2HjMKH=H-0c0eeEjVazyTxkugCTLcw@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