If you have a cost-saving strategy to replace legacy Oracle databases with open-source PostgreSQL, you’re likely asking yourself questions such as:

1. How do I pinpoint where (and why) my PostgreSQL workload is performing better or worse than it does in Oracle?
2. How will my Oracle tuning and troubleshooting skills transfer to PostgreSQL?
3. The PostgreSQL community is new to me–where do I go for tuning and configuration questions; who are the best-trusted sources?

I remember having these questions when I worked on my first Oracle to PostgreSQL migration project. I spent fourteen years as a principal software engineer for a large software company. I mainly worked on Oracle and SQL Server, so I was proficient with those databases. Then along came PostgreSQL, which we began using to save money on database licenses.

For me, the answer to the first question above, pinpointing where my workload runs better or worse on PostgreSQL, is Wait Time Analysis. It can help expedite functional and performance testing phases of a database migration. It has always been my go-to approach when trying to understand what’s working well or not so well.

First PostgreSQL Tooling—pgAdmin

On my first Oracle–to-PostgreSQL migration project, the first thing I had to do was get my tooling in place. I started with pgAdmin. I found pgAdmin was good for administrative tasks like creating users and seeing table definitions and current activity. But it missed the troubleshooting information I was used to seeing in Oracle Enterprise Manager (OEM). I couldn’t view trends over time or troubleshoot based on past data. 

Tooling Switch to Database Performance Analyzer

Someone referred me to SolarWinds^® Database Performance Analyzer (DPA), which gave me the features for PostgreSQL I was used to using for Oracle. It also provided the benefit of supporting both Oracle and PostgreSQL, which made it possible to normalize and compare the performance I saw in both databases.

Using Wait Time Analysis to Improve My PostgreSQL Performance

Suppose you know your database’s workload, understand Wait Time Analysis, and have the right tooling. In that case, you’ll be able to accelerate the tuning of your migrated database, even if you’re still learning PostgreSQL. Let’s walk through the Wait Analysis on a PostgreSQL database that has been migrated from Oracle.

Note: Wait Events were introduced in PostgreSQL v9.6, so you must be migrating to PostgreSQL 9.6 or higher for this to be possible.

DPAs Top SQL Average Wait Time dashboard below shows my PostgreSQL tuning journey. Taking a few days to reduce transaction throughput from seconds to sub-millisecond.

Figure 1: Database Performance Analyzer Average Wait Time

Day 1: Use PGTune to get off the default PostgreSQL configuration settings

I first changed the default PostgreSQL configuration using workload and hardware-specific recommendations from a free online service called PGTune. That made a big difference, as you can see by comparing Dec. 6 vs. Dec. 7 average wait time above.

Days 2 – 4: Resolve WAL and Buffer Performance Issues

DPAs Total Wait Time dashboard below shows me PostgreSQL write-ahead logging (WAL) is consuming a lot of time:

Figure 2: Total Wait Time—WAL writes taking a lot of time on December 7.

The dashboard also provides recommendations on what configuration settings I should consider changing. I found this helpful as I climbed the PostgreSQL tuning learning curve:

Figure 3: Database Performance Analyzer Tuning Recommendations

Following the advice DPA provided, I made several changes:

1. Increased shared and WAL buffer sizes
2. Moved the WAL to another location (this made a huge difference)
3. Changed the auto-fill factor from 100 to 90 to reduce buffer contention
4. Modified application code to limit the number of concurrent sessions

As shown in Figure 1 above, these changes reduced average wait times by 8x from Dec. 7 to Dec. 10 and enabled me to achieve my sub-millisecond transaction performance goal. Note: You can watch a video of this Wait Time Analysis journey here.

Visualizing Oracle vs. PostgreSQL Performance

One helpful insight, especially during parallel testing, is the ability to view Oracle vs. PostgreSQL performance on the same dashboard. DPA supports both PostgreSQL and Oracle (and many other DBMSs), making it possible to see wait time history in PostgreSQL vs. Oracle:

Figure 4: Comparing Wait Time history from Oracle vs. PostgreSQL

Using a single tool to monitor performance of both databases enables me to normalize my Oracle versus PostgreSQL performance tuning and makes it much easier to understand performance profile differences.

Other Useful Oracle-to-PostgreSQL Migration Aids in DPA

There are other DPA-provided insights into wait time that I think are helpful—namely anomaly detection and table tuning advice.

Use anomaly detection for further diagnosis

You can also use DPA for anomaly detection. It will tell you when things are deviating from the norm. For example, the Oracle wait time data on December 9 in the graph below catches my attention because it’s way out of bounds and is not typical of a Wednesday.

Figure 5: Wait time anomaly visualization in DPA

From there, you can drill down into the day or the hours of the day when the anomaly occurred and explore the specific SQL statements that contributed to the anomaly, the client machines that issued the statements, the resources used, and so on.

Get table tuning advice

Once you look at the individual statements, you can start to see if there’s anything you can do to tune them and improve performance using the Table Tuning Advisor. It helps you analyze expensive queries and identify tables that have inefficient workloads running against them. The advisor displays aggregated information about the table and the inefficient queries for each table. It can help you determine which SQL statements to tackle and which tables need to be indexed to alleviate the blocks.

A final word

These are a few examples of how to use Wait Time Analysis to help with Oracle-to-PostgreSQL database migration projects. You can use PostgreSQL wait events versus conventional statistics to identify performance bottlenecks to migrate confidently and embrace what you already know. After all, few DBAs are experts in both universes.

Using a single tool to analyze wait time for both Oracle and PostgreSQL made it easier to understand where to focus my attention and what and how to troubleshoot. In addition to Wait Time Analysis, the tuning tips in DPA helped raise my PostgreSQL IQ a lot during the migration process. I was better able to understand the environment, resource utilization, forecasting, and change impact.

If you’d like to analyze your database performance with DPA, you can try it for free here.