Architecture Overview
Introduction to Extensions in PostgreSQL
PostgreSQL is known for being a highly scalable open source database. A large part of this reputation is due to the Extension Framework, which provides a flexible way to extend PostgreSQL's functionality. Extensions allow PostgreSQL to support a wide variety of data types and functions, and even improve its internal functionality.
An important feature of extensions is that they are dynamically loaded into the PostgreSQL instance, meaning that PostgreSQL does not need to rebuild the source or replace the executable when adding new functionality through an extension. Adding an extension also doesn't require restarting the instance, except for extensions that require initialization at instance startup. This makes adding new features very easy in PostgreSQL.
Introduction to O2 Extensions
O2 stands for OpenSQL for Oracle, which is a set of extensions that provide compatibility with Oracle data types, views, built-in functions, and built-in packages. O2 is based on the open source project Orafce, but it has changed in many ways. Orafce, which was originally managed as a single extension, has been split into multiple extensions.
O2 Extensions includes the following extensions
O2Types: Oracle Data Type Compatibility Extension
O2Views: Oracle View Compatibility Extension
O2Functions: Oracle Built-in Functions Compatible Extension
Package Extensions: A collection of extensions compatible with Oracle's built-in packages.
DBMS_ALERT
DBMS_OUTPUT
DBMS_PIPE
DBMS_RANDOM
DBMS_SQL
UTL_FILE
The O2 project was started based on the open source project Orafce, but it has changed in many ways. Originally managed as a single extension, Orafce was split into several extensions, which greatly increased the flexibility of patching. In addition, the lack of a common convention or framework was a problem due to its gradual development over a long period of time, which was improved during the integration process.
Benefits and improvements of O2 Extensions
Change custom functions written in SQL, PL/pgSQL to C code
The built-in functions or packages provided by O2 Extension are internally organized as user-defined functions (UDFs). PostgreSQL supports a variety of programming languages for writing user-defined functions, including SQL and PL/pgSQL, as well as C/C++, Python, Perl, and Rust. The O2 Extension specifically improves performance by turning custom functions written in SQL and PL/pgSQL into C code. When the PostgreSQL Query execution engine executes a UDF written in SQL or PL/pgSQL, it internally creates a cursor to execute subqueries, but functions written in C code are executed directly from the compiled library, ensuring fast performance. In addition, implementing in C code allows for more sophisticated and flexible implementations by utilizing PostgreSQL libraries.
Reduce blast radius by splitting the extension into multiple extensions
Since the existing Orafce was managed as a single Extension, if a problem occurred in one element of the entire implementation, the entire Extension had to be updated. On the other hand, O2 is managed by dividing it into several Extensions, so only the Extension that has a problem needs to be replaced.
For example, packages that use shared memory, such as DBMS_ALERT or DBMS_PIPE, need to be added to shared_preload_libraries and then start the DB instance. However, in the case of O2, these extensions are separated, so that only the necessary extensions can be added to shared_preload_libraries. This has reduced the hassle and increased flexibility during updates.
These improvements enable the O2 Extension to provide PostgreSQL users with more powerful and flexible Oracle compatibility. The introduction of the O2 Extension provides significant improvements in performance, flexibility, and stability.
Last updated