The AWS SCT Extension Pack is designed to emulate Oracle’s system functions, procedures, and other database objects within PostgreSQL. This emulation is achieved by creating a special schema in PostgreSQL, named aws_oracle_ext, where these functions and objects are stored. This pack simplifies migration by replacing Oracle-specific calls with their PostgreSQL equivalents, ensuring that the code functions correctly post-migration.

For example, consider the following transformations:

Oracle

PostgreSQL
SELECT sysdate FROM dual;
SELECT aws_oracle_ext.sysdate()
SELECT
to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss'),
to_char(sysdate, 'dd.mm.rrrr') FROM dual;
SELECT
aws_oracle_ext.to_char(aws_oracle_ext.
sysdate(),'dd.mm.yyyy hh24:mi:ss'),
aws_oracle_ext.sysdate(),aws_oracle_ext.
to_char('dd.mm.rrrr');
SELECT
substr('Sample String', 1, 6),
substr('Sample String', -6) FROM dual;
SELECT
aws_oracle_ext.substr(
'Sample String', 1, 6),
aws_oracle_ext.substr(
'Sample String', -6);
SELECT * FROM all_tables;
SELECT * FROM aws_oracle_ext.sys_all_tables;
SELECT * FROM all_views;
SELECT * FROM aws_oracle_ext.sys_all_views; 

These examples illustrate how the Extension Pack provides a straightforward method to replace Oracle-specific queries with those compatible with PostgreSQL.

How to balance functionality and performance in database migrations

While the Extension Pack is incredibly useful, it can sometimes lead to performance issues. This happens because the emulated functions in PostgreSQL might perform additional checks to replicate Oracle’s behavior precisely, leading to slower execution times. For instance, a simple function call that runs in milliseconds in Oracle might take significantly longer in PostgreSQL if not optimized.

Best Practices for Optimizing Performance with AWS SCT Extension Pack

  1. Replacing emulated functions with native PostgreSQL functions: Whenever possible, replace aws_oracle_ext functions with PostgreSQL’s native functions. For example:
    • Replace aws_oracle_ext.sysdate() with localtimestamp if your system operates in a single time zone.
    • Use to_char(localtimestamp, ‘dd.mm.yyyy hh24:mi:ss’) directly in PostgreSQL, bypassing the need for the Extension Pack.
    • Modify queries that use the substr function to avoid negative parameters, which behave differently in PostgreSQL.
  2. Dealing with package variables: Oracle’s package variables, which maintain state across function calls, do not have a direct equivalent in PostgreSQL. The AWS SCT addresses this by offering functions like aws_oracle_ext.set_package_variable and aws_oracle_ext.get_package_variable, but these can be slow. The best approach is to replace package variables with local variables wherever possible or refactor the code to pass variables through function parameters.
  3. Handling collections and complex data types: Oracle collections and complex types are another challenge during migration. While the Extension Pack can emulate these, better performance is often achieved by rewriting the code to use PostgreSQL’s native arrays, records, and JSON data types.

Wrap-Up: Aligning compatibility and performance in migrations

While tools like the AWS SCT and its Extension Pack provide invaluable assistance, they are not a panacea. Database developers should evaluate each case individually and apply optimizations where necessary to ensure the migrated system is not only functional but also performant.

If you’re planning a database migration or currently dealing with performance issues post-migration, consider evaluating the possibility of transitioning away from it altogether in favor of native PostgreSQL tools. Evaluate where native PostgreSQL functionality can replace emulated Oracle features, and don’t hesitate to refactor your code to leverage PostgreSQL’s built-in capabilities for better performance. By making these adjustments, you can ensure that the migrated system is both functional and highly performant, benefiting both your future self and your users.

Questions? Contact us