public inbox for [email protected]  

help / color / mirror / Atom feed

Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools
6+ messages / 6 participants
[nested] [flat]

* Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools
@ 2026-04-10 17:48  mahamood hussain <[email protected]>
  0 siblings, 4 replies; 6+ messages in thread

From: mahamood hussain @ 2026-04-10 17:48 UTC (permalink / raw)
  To: Pgsql-admin <[email protected]>

--000000000000e0c147064f1ebf67
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Team,

We are currently working on a migration project from DB2 to PostgreSQL.
Post-migration, we=E2=80=99re 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.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools
@ 2026-04-10 18:23  Ron Johnson <[email protected]>
  parent: mahamood hussain <[email protected]>
  3 siblings, 1 reply; 6+ messages in thread

From: Ron Johnson @ 2026-04-10 18:23 UTC (permalink / raw)
  To: Pgsql-admin <[email protected]>

On Fri, Apr 10, 2026 at 1:49 PM mahamood hussain <[email protected]>
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!


^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools
@ 2026-04-10 18:34  Keith <[email protected]>
  parent: mahamood hussain <[email protected]>
  3 siblings, 0 replies; 6+ messages in thread

From: Keith @ 2026-04-10 18:34 UTC (permalink / raw)
  To: mahamood hussain <[email protected]>; +Cc: Pgsql-admin <[email protected]>

For log analysis, pgbadger (https://pgbadger.darold.net/) is the best tool
I've come across. Just be sure to adjust your log_line_prefix to include
information such as client host/IP, role, database and error codes. Turn on
other settings such as logging checkpointing, temp file generation and
locking. The more you log, the more that pgbadger can help analyze. Just
keep an eye on your log generation since you don't want that to in turn
affect your database. If you can write the logs to a different mount point.
that can isolate that IO from the database.

For trending other things, I'd recommend starting with something like
Prometheus (https://prometheus.io/) & Grafana (https://grafana.com/). Get
an exporter set up (sql_exporter is a good one -
https://github.com/burningalchemist/sql_exporter) and set up alerts and
trending your metrics so you can go back and look at history when those
alerts fire. If you need to start scaling bigger, then you can start
looking into something like Datadog and since you'll already have a better
idea of what you actually need to monitor by then, it won't seem nearly as
overwhelming jumping to something like that.

Keith

On Fri, Apr 10, 2026 at 1:49 PM mahamood hussain <[email protected]>
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.
>


^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools
@ 2026-04-11 06:07  bertrand HARTWIG <[email protected]>
  parent: mahamood hussain <[email protected]>
  3 siblings, 0 replies; 6+ messages in thread

From: bertrand HARTWIG @ 2026-04-11 06:07 UTC (permalink / raw)
  To: mahamood hussain <[email protected]>; +Cc: Pgsql-admin <[email protected]>

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 <[email protected]> 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.
> 



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools
@ 2026-04-11 15:22  flatley <[email protected]>
  parent: Ron Johnson <[email protected]>
  0 siblings, 0 replies; 6+ messages in thread

From: flatley @ 2026-04-11 15:22 UTC (permalink / raw)
  To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>

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

On Fri, Apr 10, 2026 at 11:24 AM Ron Johnson <[email protected]>
wrote:

> On Fri, Apr 10, 2026 at 1:49 PM mahamood hussain <[email protected]>
> 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!
>


^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools
@ 2026-04-11 15:35  Laurenz Albe <[email protected]>
  parent: mahamood hussain <[email protected]>
  3 siblings, 0 replies; 6+ messages in thread

From: Laurenz Albe @ 2026-04-11 15:35 UTC (permalink / raw)
  To: mahamood hussain <[email protected]>; Pgsql-admin <[email protected]>

On Fri, 2026-04-10 at 23:18 +0530, mahamood hussain wrote:
> From a DBA perspective, I’m looking to proactively identify problem areas—such as:
>  * Long-running queries

log_min_duration_statement = 2000

>  * Jobs/stored procedures consuming high temp space

SELECT temp_blks_written, query
FROM pg_stat_statements
ORDER BY temp_blks_written DESC
LIMIT 10;

>  * Queries resulting in sequential scans due to missing indexes

There is no direct way to find that.

First, look at tables that receive large sequential scans frequently:

SELECT relid::regclass, seq_scan, seq_tup_read
FROM pg_stat_all_tables
ORDER BY least(seq_scan, seq_tup_read) DESC
LIMIT 10;

Then examine the long-running queries that consume a lot of database time:

SELECT total_exec_time, query
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY total_exec_time DESC
LIMIT 10;

See if any of those use one the tables found with the first query.

Then use EXPLAIN (ANALYZE, BUFFERS) on the statement to get the execution plan
and tune the query if you can.

>  * Lock waits, deadlocks, and memory-heavy operations

log_lock_waits = on

Deadlocks are logged automatically.

Memory use is not tracked.

Yours,
Laurenz Albe






^ permalink  raw  reply  [nested|flat] 6+ messages in thread

end of thread, other threads:[~2026-04-11 15:35 UTC | newest]

Thread overview: 6+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-10 17:48 Seeking Advice: PostgreSQL Performance Troubleshooting Without Third-Party Tools mahamood hussain <[email protected]>
2026-04-10 18:23 ` Ron Johnson <[email protected]>
2026-04-11 15:22   ` flatley <[email protected]>
2026-04-10 18:34 ` Keith <[email protected]>
2026-04-11 06:07 ` bertrand HARTWIG <[email protected]>
2026-04-11 15:35 ` Laurenz Albe <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox