Dec 3, 2021

How to simplify Oracle-to-PostgreSQL migration with automatic database tuning

Bohan Zhang

otter swimming oracle-postgresql migration
otter swimming oracle-postgresql migration

Otters don’t migrate. They stay put for the most part, even in winter. They would rather die fighting than give up their holt.

On the other hand, a lot of people are migrating their databases from expensive legacy DBMSs like Oracle to open source alternatives like PostgreSQL and MySQL. By next year, Gartner predicts that most new application development projects will be built on open source DBMSs instead of costly legacy databases.

One big reason for this migration is cost savings. Open source database licensing is a lot less expensive than Oracle licensing. The enterprise edition of Oracle costs roughly $47,500 per CPU core versus $0 for PostgreSQL (unsupported). If you want commercial support for PostgreSQL, then it will cost you a few thousand of dollars per CPU core. Different PostgreSQL vendors have different pricing models, but they’re all substantially cheaper than Oracle.

Worried about PostgreSQL’s open waters?

At OtterTune, we often talk to experienced Oracle users who are new to PostgreSQL and are concerned about PostgreSQL’s performance and learning curve. Optimizing the PostgreSQL configuration is often a time-consuming part of the Oracle-to-PostgreSQL migration process, in part because they have not yet mastered the hundreds of knobs that affect PostgreSQL performance.

Driven by machine learning algorithms, automatic database tuning (using a service like OtterTune), can help accelerate the Oracle-to-PostgreSQL migration process. It can shave weeks off of the migration process by eliminating time spent consulting PostgreSQL documentation and forums for tuning best practices and performing tedious trial-and-error configuration tuning.

From zero to optimized in one hour

To illustrate the benefits of the automatic database tuning approach, we ran a quick benchmark to show how quickly one could optimize PostgreSQL performance for a specific workload, and achieve performance equal to or greater than Oracle.

To run this comparison test, we deployed an Oracle (v19) and PostgreSQL (v11) database on Amazon RDS db.m5.4xlarge instances (16 vCPUs, 64 GB of RAM) with 4000 provisioned IOPs.

We used the BenchBase SQL benchmarking framework to run the TPC-C OLTP workload. We configured BenchBase to load a TPC-C database with 200 warehouses (~25 GB of table data) and execute transactions across 50 simultaneous connections using each DBMS’s latest JDBC driver. We ran the benchmark on three different configurations:

  • Amazon RDS Oracle default configuration settings

  • Amazon RDS PostgreSQL default configuration

  • Amazon RDS PostgreSQL with OtterTune-optimized configuration

For the OtterTune-optimized PostgreSQL configuration, we first connected the DBMS to OtterTune and then ran the benchmark for one hour with OtterTune’s tuning mode enabled. We configured OtterTune to optimize PostgreSQL’s throughput as its target objective.

We ran the TPC-C benchmark three times for each DBMS and reported the average throughput over these trials:

ottertune improves postgresql throughput

The above graph is not meant to give you the impression that Oracle is slower than PostgreSQL (clearly, the default Amazon RDS Oracle configuration settings are not a good fit for TPC-C).

Instead, we want to demonstrate that, within one hour, OtterTune was able to automatically optimize the PostgreSQL configuration for its hardware and workload. OtterTune accelerated PostgreSQL by over 3x in this test, without requiring you to learn all the PostgreSQL configuration knobs or spending time tweaking them (trial and error) to optimize the DBMS’s performance.

Migrate to Open Source with Confidence (and OtterTune)

If you’re considering moving your database workloads off expensive legacy databases and onto lower cost, open-source DBMSs like PostgreSQL and MySQL, do not let the open-source database learning curve or performance worries be a blocker. OtterTune automatic configuration can help (safely — OtterTune does not need to access your user data).

Try OtterTune for free. Start using AI to optimize your PostgreSQL or MySQL databases running on Amazon RDS or Aurora.

Try OtterTune for free. Start using AI to optimize your PostgreSQL or MySQL databases running on Amazon RDS or Aurora.

Get Started

Subscribe to blog updates.