Free yourself from Oracle

This article is part of a series on Cloud journey specialized on Financial Institutions.

Feedback is very welcome.?

Articles in this Series:

1. Cloud vs. on Premise for Data Warehouse
2. Road to the Cloud
3. Free yourself from Oracle

Part 3 covers the difficulties of moving away from Oracle. Suppose you've decided to move your app to the Cloud. In that case, you'll soon find that switching to an open-source solution is a wise and necessary choice if you want to avoid budget limitations, which may also become the bottleneck of scalability.

??

According to the 2022 Developer Survey from Stackoverflow, "PostgreSQL became the most loved and wanted database after five years of Redis being the most loved".

https://survey.stackoverflow.co/2022/#most-loved-dreaded-and-wanted-database-love-dread

Therefore, this article focuses on the main challenges of migrating from Oracle to PostgreSQL database.

Table of Contents

1. Main challenges

1.1. Language differences PL/SQL vs PL/pgSQL

1.2. Clustering – RAC (Real Application Cluster)

1.3. Messaging - AQ (Advanced Queuing)

1.4. Application Code

2. Possible Solutions

2.1. Assessment & Migration tools

2.2. Clustering alternatives

2.3. Messaging alternatives

2.4. Application Code the real threat

3. Next steps

1. Main challenges

Oracle and PostgreSQL are popular relational database management systems (RDBMS) widely used in the financial industry. However, migrating from Oracle to PostgreSQL can present several challenges, including differences in SQL syntax, data type support, and system architecture.

1.1. Language differences PL/SQL vs PL/pgSQL

One of the most significant challenges is the difference in SQL syntax. While both systems conform to the ANSI SQL standard, there are subtle differences in the way the two systems handle specific commands and clauses. For example, Oracle supports hierarchical queries with the "CONNECT BY" clause in SQL statements, while PostgreSQL does it with "WITH RECURSIVE".?

There are more or less 50+ similar transformation challenges where you cannot expect a 1to1 mapping.

Additionally, Oracle uses the "PL/SQL" procedural language, while PostgreSQL uses "PL/pgSQL." These differences can make it difficult to convert existing Oracle SQL scripts to work with PostgreSQL.?

Another challenge is the difference among the built-in database system catalog views

Oracle: DBA_TABLES, ALL_TAB_COLS, DBA_USERS

PG: pg_tables, information_schema.columns, pg_user        

Oracle has more than 1600+ catalog views, though the most frequently used in stored procedures and application codes are less than 20.

The next challenge is the difference in data type support between the two systems. Oracle and PostgreSQL support many of the same data types, such as INTEGER and VARCHAR, but there are some notable differences. For example, Oracle has a "BFILE" data type for storing binary files or supports the use of "CLOB" (Character Large Object) and "BLOB" (Binary Large Object) data types. In contrast, PostgreSQL uses the "BYTEA" data type for this purpose. These differences can make it difficult to convert existing data to work with PostgreSQL.

1.2. Clustering - RAC (Real Application Cluster)

Oracle Real Application Clusters (RAC) is a feature of the Oracle Database that enables multiple instances of the database to work together to provide high availability, scalability, and load balancing. RAC uses ASM (Automatic Storage Management) to provide a unified storage management solution for all instances, which makes it easy to manage storage and ensure data consistency.?

It's important to note that the RAC feature comes at a license cost and is not a free feature of the Oracle Database.

1.3. Messaging - AQ (Advanced Queuing)

Oracle Advanced Queuing (AQ) is a proprietary feature that is only available as part of the Oracle database management system and may require a separate license. It allows asynchronous communication between parts of an application through message queues. It provides improved performance, scalability, and reliability benefits, with features such as rule-based message transformation, persistent messaging, priority-based delivery, and message expiration. AQ is commonly used in enterprise systems where reliable messaging is required. However, it is not supported in PostgreSQL, and migration may require third-party tools or custom development.

1.4. Application Code

There are several different ways applications apply Oracle SQL syntax, which sometimes means the most significant effort in Assessment and Migration projects, whether embedded in Java, stored as text in files or database tables, or dynamically generated by 3rd party tools like Business Intelligence and ETL frameworks.?

2. Possible solutions

There are existing tools which can automate the assessment and/or the migration to some extent. Still, in my experience, all of them are limited in many aspects, a separate topic that goes beyond this article's scope. However, my strict approach to this problem is a tool which combines traditional rule-based translator engines and state-of-the-art deep learning models within the branch of Natural Language Processing and Machine translation. It is important to note that neither of the techniques possesses the necessary strength to handle this task alone.

2.1. Translation of the language differences

The so-called rule-based translators are strong in trivial use cases like replacing function NVL to Coalesce; however, they struggle with more complex use cases like old-style Oracle Outer Join Syntax:

Oracle: select * from foo, bar where foo.id = bar.id(+

PG: select * from foo?left outer join?bar on(foo.id = bar.id))        

According to my research and experience, the possible accuracy in language translation can achieve?95%?with the state-of-the-art techniques mentioned earlier. Before choosing any tools or frameworks, it's important to carefully read the fine print in product descriptions. Many frameworks don't actually support migration from Oracle to Open Source PostgreSQL but instead offer a proprietary product based on an older version of the PostgreSQL database. For example, AWS Aurora may seem like a good option. Still, it can result in vendor lock-in similar to your Oracle contracts, negating the benefits of using a constantly improving open-source ecosystem.

2.2. RAC (Real Application Cluster) with 3rd party tools

The key here is to understand the primary purpose of the cluster of databases, whether it is high availability, scalability, or load balancing. This task is not easily replicable and requires proper analysis and planning, though it can immediately highlight the advantage of the open-source world. There are several free, widely used products that can offer similar functionality with a vibrating community to support any technical challenges.??

The most popular cluster management tools for PostgreSQL are:

• pgpool-II: a middleware that sits between PostgreSQL servers and a PostgreSQL database client. It can perform connection pooling, load balancing, and replication.
• patroni: an open-source template for creating and managing a highly available PostgreSQL cluster using Python and Zookeeper.
• pgCluster?is a simple HA and load balancing solution with fewer features than Pgpool-II and Patroni but simpler to set up.

2.3.?Messaging - AQ (Advanced Queuing)

There are a number of third-party tools that can be used to replicate the functionality of Oracle Advanced Queuing (AQ) in PostgreSQL. Some of the most popular options include:

• pg_message_queue: This is a PostgreSQL extension that provides message queuing functionality similar to Oracle AQ. It supports features such as message persistence, priority-based message delivery, and message expiration.
• RabbitMQ: This is an open-source message broker that can be used in conjunction with PostgreSQL to provide advanced queuing functionality. It supports a wide range of messaging protocols, including AMQP, which can be used to connect PostgreSQL to other systems.
• Kafka: Another popular open-source message broker that can be used with PostgreSQL to provide advanced queuing functionality. It is designed to handle large amounts of data and is widely used in big data and streaming scenarios.
• ActiveMQ: This is another open-source message broker that can be used with PostgreSQL to provide advanced queuing functionality. It supports a wide range of messaging protocols, including JMS, and is widely used in enterprise environments.

It's worth noting that these tools are not direct replacements of Oracle AQ and they may require additional configuration and development effort to fully replicate the functionality. Additionally, the selection of the right tool depends on the specific use case and requirements of the organization, such as performance, scalability, and reliability.?

2.4. Application Code migration

The most challenging aspect of migration projects is undoubtedly the application code, the least assessable and automatable part.?

This may entail changes to the application code that interacts with the database. If your backend application uses an ORM tool, you may be able to simply switch between database implementations. The same applies to Business Intelligence and ETL frameworks; however, as a rule of thumb, you should expect at least 20-30 percent of custom implementations typically due to performance improvements. One of the most proven effective methods is monitoring the entire Oracle database for a given period, like 1-2 months, to capture all access patterns and SQL statements used.

3. Next steps

In conclusion, migrating from Oracle to PostgreSQL can present a number of challenges, including differences in SQL syntax, data type support, and system architecture. Additionally, the lack of support for Oracle-specific features like RAC and AQ and changes to the application code may also be a challenge. However, with proper planning, testing, and execution, these challenges can be overcome, and organizations can harvest the benefits of using a more cost-effective and open-source database management system like PostgreSQL.

If you have a specific question or want more information, just let me know, and I'll be happy to help you out.