Key PostgreSQL Extensions Every Developer Must Know When Migrating from Oracle.

I would like to start the blog with an interesting fact: the language PL/pgSQL, which is used to build procedural code logic in PostgreSQL, is also an extension that is installed by default when you create a database.

PostgreSQL is designed to be extensible, allowing us to load extensions that introduce additional features to functions as if they were built-in.

CREATE EXTENSION

is the most underrated Postgres feature
— Adam Hendel (@adamhendel) December 13, 2023

Let’s begin with a list of extensions that I commonly use in almost all or most of migrations we facilitate for customers transitioning from commercial engines like Oracle to MSSQL. Extensions play a crucial role in bridging gaps and providing similar features to those used in the source database we are migrating from.Most of you might be aware of the below list of extensions in PostgreSQL. The idea with this blog is to curate all the necessary extensions.

1. SQL Plan Hints – pg_hint_plan

Hints enable database performance analyst to influence cost based optimizer on access patterns, joins, cardinality, table join order and more.

Please check out below table that shows an example of how to achieve hints in PostgreSQL using pg_hint_plan extensions.

If you are interested to dive deep into understanding pg_hint_plan, please check out.
https://dev.to/franckpachot/series/18404

2. Database SQL Monitoring – pg_stat_statements

Database observability, particularly tracking the execution statistics of SQL statements, is crucial for maintaining the overall health and performance of any database.

In Oracle, various proprietary monitoring views like v$sqlstats offer detailed runtime metrics on SQL performance, including elapsed time, buffer gets, and disk reads.

One of my favorite extensions in PostgreSQL is pg_stat_statements. This extension provides a view that presents a single row for SQL operation, offering valuable information such as total_exec_time, query text, the number of rows retrieved by the query, and more.

It becomes especially useful in cases where you need to delve into nested calls within procedures, achieved by enabling pg_stat_statements.track set as ‘all’ temporary while testing.Check our my additional blog on PostgreSQL native observability.

https://cloud.google.com/blog/products/databases/guide-to-the-database-observability-with-cloud-sql

3. Database Audit – pgaudit

Database Auditing enabled Regulatory and compliance requirement on what, when and by whom for specific database operations.

Oracle provide audit trail on different access patterns ddl or dml or grants, expose as DB tables as shown in example in below table.
pgaudit extension allows use to selectively record and track SQL operations performed against a given database instance. In example we are enabling auditing on ddl, read and write only for specific user operation. (audit_test)

For more details on pgaudit, please check out
https://www.pgaudit.org/

4. Oracle functions – orafce wrapper

Migration from Oracle involves building migration patterns for proprietary oracle functions or packages.
As we can see in the sample shared for oracle.
Orafce facilitate wrappers that emulate specific set of Oracle native functions and provides minimal SQL changes embedded within application code logic or dynamic query

As shown in example.
It include most used expression or functions like NVL, Decode and INSTR. Oracle compatible Date Functions.
Oracle internals views like dba_segments and more.

It can also be included as part of Ora2pg Migration tools in it’s configuration(USE_ORAFCE)

For more details on Extension, please check
https://github.com/orafce/orafce

Oracle vs PostgreSQL – Database Functions

For more information, please check out.
https://github.com/orafce/orafce

5. Database Links – postgres_fdw

Database links typically enable access to homogeneous remote database objects primarily for reads, and in some cases, for DML. PostgreSQL provides various foreign data wrappers, with postgres_fdw being utilized for homogeneous remote data queries.

postgres_fdw is employed when a PostgreSQL database needs to communicate with another PostgreSQL database, akin to executing a SQL command in the remote database. This functionality is comparable to a standard database link in Oracle.

In the table below, on the left side, you observe an Oracle implementation where a select statement is executed in a remote database named sample_remote. On the right side, the same operation is conducted, but this is now a PostgreSQL implementation. Essentially, we configure the remote server, allowing us to list remote tables and execute SQL commands on the remote server.

6. Pragma Autonomous Transactions – dblink

Autonomous transactions offer the capability to execute subprograms that can be committed or rolled back independently of their parent transactions.

When migrating features to PostgreSQL compatibility, we adhere to the following guidelines. We either refactor the functionality implemented within autonomous transactions within the application or reverse-engineer the requirements and implement them as cloud-native solutions if feasible. In the PostgreSQL context, using dblink allows us to explicitly open a new connection and execute an autonomous component within its own transactional scope.

After adding the dblink extension, we can make remote calls by providing the necessary details. In the case of an Autonomous transaction, a remote call is implemented using dblink, but it connects to the same database and instance.

Oracle vs PostgreSQL – Pragma Autonomous Transaction.

For more information, please check out
https://databaserookies.wordpress.com/2015/11/15/autonomous-transaction-in-oracle/
https://aws.amazon.com/blogs/database/migrating-oracle-autonomous-transactions-to-postgresql/

7. Table Partitioning Maintenance – pg_partman/pg_cron

Partitioning has consistently been a core feature in all the databases I’ve worked with.

Oracle supports various partition types, ranging from Range to Automatic list and Interval Partitions. In the Oracle example, Interval Partitioning allows dynamic partitioning based on a predefined interval, such as daily, depending on the data load.

Starting from PostgreSQL 10 and subsequent releases, numerous features have been introduced as part of Native Partitioning to enhance overall performance. pg_partman is the go-to extension for creating and maintaining partitions based on dates, times, and serials. When combined with pgcron, it enables the scheduling of future partition creations according to desired intervals.

Oracle vs PostgreSQL – Interval Partitioning

Below is an reference on how to implement Interval Partitioning in PostgreSQL using pg_partman and pg_cron extension.

For more details on implementations of achieving Interval Partitioning in PostgreSQL, please do check out Blog.
https://aws.amazon.com/blogs/database/automate-interval-partitioning-maintenance-and-monitoring-in-amazon-rds-for-postgresql-and-amazon-aurora-postgresql/

8. Global Temporary Table – pgtt

Most of the Oracle databases I have worked on always have some global temporary tables. One of the core differences between Oracle and PostgreSQL regarding temporary tables is that the table structure is also temporary in PostgreSQL. By default, in PostgreSQL, temporary tables are local, including their definitions. This creates a lot of confusion for Oracle Developer., and we need to add “CREATE TEMPORARY TABLE IF NOT EXISTS” every time there is a need to access a temporary table post-migration in PostgreSQL.

The pgtt Extension, helps us to redefine or check existence of Temporary table implicitly once it is defined in PostgreSQL. It is important to note that every session in PostgreSQL must explicitly load the ‘pgtt’ extension.

Oracle vs PostgreSQL – Global Temporary Table.

For More information, Please check out below blog.
https://www.migops.com/blog/global-temporary-table-in-postgresql/

9. Heterogeneous FDW – Oracle_FDW

When migrating an Oracle application or database, there are instances where dependencies with another Oracle database might exist, either pending migration or not within the migration scope. In such cases, having the essential interfaces available to such Oracle instances becomes crucial, even if the application or database has been successfully migrated to PostgreSQL.

As the name suggests, Oracle_FDW serves as a foreign data wrapper for Oracle databases, allowing users to fetch or apply specific Data Manipulation Language (DML) operations as needed. Additionally, Oracle_FDW can be utilized as a means to validate data or schema. One illustrative example of this capability can be found here.

Sharing a sample script to Query Dual Table from Oracle.

For more information, please check out.
https://github.com/laurenz/oracle_fdw

Conclusion.

For most migrations from Oracle to PostgreSQL, they would be incomplete without the usage of at least some extensions. It’s always great to be aware of them and utilize them to accelerate and transform functionality according to specific use cases.
If you’re interested in exploring more extensions or checking out all the different available extensions, feel free to check it out.

https://pgt.dev/
open-source package installer and registry for PostgreSQL extensions.

If you really enjoyed the blog and are exploring courses on Oracle to PostgreSQL Migration, please check out my Live Courses!