How Postgres Workload Reports Help Optimize Database Operations

Learn how this Postgres performance tool boosts DBA productivity

To successfully migrate applications from Oracle to PostgreSQL, organizations must provide tools to those most impacted by these changes – DBAs, developers, and IT.

Postgres Workload Reports is one tool that enables migrations to open source database solutions. It uses Oracle’s Automatic Workload Report (AWR) formatting for familiarity, advances database performance analyses, and improves diagnoses and troubleshooting.

The Postgres Workload Reports feature is available for PostgreSQL and EDB Postgres Advanced Server subscription customers.

Challenges to DBA Productivity

Oracle's AWR Reports are a standard, trusted performance monitoring and optimization tool in enterprise database environments, providing detailed insights for diagnosing and tuning database operations. The absence of a similar PostgreSQL tool can lead to a steep learning curve for Oracle DBAs when migrating to Postgres.

Transition challenges can be worse in mixed database environments, where administrators oversee both Oracle and Postgres databases, because the specific format and depth of data in AWRs are not replicated in Postgres reporting.

The inability to summarize, view, and share this information is not just a feature gap – it’s a significant barrier to diagnosing Postgres performance. These challenges impact DBA productivity in Postgres environments and, as a result, can lead to potential downtime of business applications.

Improving Postgres Performance Diagnostics

Postgres Workload Reports bridge the gap between Oracle and PostgreSQL diagnostics. It’s a tool that mirrors Oracle AWR’s functionality and depth but is purpose-built for modern Postgres environments.

Postgres Workload Reports provides top-tier performance diagnostics for the Postgres ecosystem, with detailed metrics, easy-to-understand reports, and an AWR-like user interface to enhance organizational adoption. It equips DBAs with the tools needed to ensure high performance Postgres, supporting the uninterrupted operation of essential business applications.

By providing wait state information, Postgres Workload Reports is a valuable feature for DBAs. These reports also offer insight into top SQL statements and Postgres configuration information. As a result, Postgres Workload Reports allow DBAs and developers to quickly understand and resolve database performance issues by evaluating a single report in either Markdown, PDF, or HTML.

Postgres Workload Reports can also be sent to more experienced DBAs and experts for further analysis, enhancing organizational Postgres performance diagnostics and troubleshooting buy-in.

A Unique Postgres Performance Tool

What is new and unique about Postgres Workload Reports compared to other Postgres performance tools is that they allow you to evaluate what queries were waiting at a particular time. These analyses are made possible by the edb_wait_states extension. For instance, consider this output from a PWR report run using HammerDB against a Postgres server.

First, let’s examine the server information section of the report:

Server Information

Shows information about the server version.

• server version - shows server version number.
• architecture - architecture for which it is built.
• system_identifier - unique identifier for the cluster.
• redwood_mode - whether the cluster is created in redwood mode.
• current_user - current user generating the report.
• actual_start_snap_ts - actual start timestamp from which we have stat data available.
• actual_end_snap_ts - actual end timestamp till which we have stat data available.
• snapshot_duration - duration of the snapshot.

General information about the server is readily available. However, special attention should be given to two specific items: the actual_start_snap_ts (2024-02-15 07:44:18.588779+00) and the actual_end_snap_ts (2024-02-15 07:54:16.587401+00).

This indicates that the database is being evaluated over a 10-minute period. During this timeframe, we are also looking at the amount of dbtime. In this case, it was 184 seconds. This means that during this 10-minute snapshot, the database server was either executing queries or waiting for something so it could continue executing queries.

So, the questions that have perplexed many of our Postgres performance troubleshooting efforts are:

1. Is the database executing queries or waiting on resources such as locks, disk I/O, or CPU?
2. If the system is waiting, what is it waiting on?

This Postgres Workload Report helps us answer these two questions, as seen in this top wait events report.

Top wait events

Show total wait time on top wait events in seconds.

See the Wait Event Table Information here: 28.2. The Cumulative Statistics System.

• waitevent - waitevent name (waitevent name CPU means time spent working on CPU or a non-wait time).
• wait_class - waitevent type.
• waittime - waiting time in seconds spent on this waitevent.
• pct_dbtime - %dbtime spent waiting on this waitevent type.

Of the 184 seconds evaluated, 124 were spent waiting on ClientRead events. Referring to Section 28.2 of PostgreSQL documentation, this metric indicates that the system was waiting to read data from the client.

What could this mean? A variety of things, including:

1. This server had a HammerDB benchmark running against it. One possibility – and in this case, the most probable conclusion – is that the client test machine was overwhelmed and could not respond to the server fast enough.
2. The client was waiting for user input, such as a return from a prompt.

It doesn’t mean that something on the server is impacting the system’s throughput. Instead, the issue is with the client.

In looking at top SQL statements, you can see the following:

Top SQL statements

• dbtime - total dbtime spent by the sql statement.
• waittime - total waittime spent by the sql statement.
• cputime - total cputime spent by the sql statement.
• top_waitevent - waitevent name on which this statement spent maximum time.
• query - actual sql query.

Top 10 sql statements sorted by dbtime (high to low)

COPY order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info, ol_delivery_d) FROM STDIN WITH (FORMAT CSV)  

This indicates that the bottleneck during this period involved reading a CSV file from STDIN on the client machine.

Getting information about what the server is waiting on has proven extremely difficult in the past. Postgres Workload Reports now makes it simple. You can evaluate wait events for any period you’d like and determine what issues the system may be facing.

Empower DBAs and Avoid Costly Outages

The inability to summarize, view, and share information about top SQL events, wait events, and session activity is a significant barrier to diagnosing database performance in Postgres. This Postgres feature gap has adversely impacted DBA productivity and, as a result, could lead to downtime of business applications. Now, Postgres Workload Reports equip DBAs with the necessary tools to ensure high performance, supporting the uninterrupted operation of essential business applications.

Postgres Workload Reports help DBAs understand system behavior nuances like no other Postgres tool can. This level of detail is invaluable for diagnosing and resolving complex database performance issues. Postgres Workload Reports help customers avoid costly outages by proactively optimizing Postgres, with a deeper understanding of what is happening on their revenue-generating databases.