Differences between PolarDB for PostgreSQL (Compatible with Oracle) 2.0 and PolarDB for PostgreSQL (Compatible with Oracle) 1.0 - PolarDB

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 features a new compatibility architecture compared to version 1.0, with Oracle syntax compatibility as the development baseline. Version 2.0 delivers significant performance improvements over version 1.0 in high concurrency, high write loads, parallel queries, and logical replication under distributed load conditions. This topic describes the specific differences between the two versions for your reference. Please refer to the currently released PolarDB for PostgreSQL (Compatible with Oracle) 2.0 version (20250630). Some features will be gradually supported in future versions.

Significant performance improvements

Performance tests were conducted using pgbench for read-only scenarios (select-only) and read-write scenarios (tpcb-like) with a cluster specification of polar.o.x8.xlarge. The test results are as follows:

Scenario	PolarDB for PostgreSQL (Compatible with Oracle) 1.0	PolarDB for PostgreSQL (Compatible with Oracle) 2.0	Performance improvement
Read-only scenario (select-only)	109141.30	120803.95	10.69%
Read-write scenario (tpcb-like)	49226.71	64752.41	31.54%

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 demonstrates significant performance improvements compared to version 1.0, especially in tpcb-like read-write scenarios where performance has improved by over 30%. Additionally, in scenarios with many connections, PolarDB for PostgreSQL (Compatible with Oracle) 2.0 has significantly increased transaction throughput, with up to 100% improvement in test scenarios.

To further enhance performance, version 2.0 has added numerous parallel query features. For example, the RETURN QUERY in PL/pgSQL functions now supports parallel computation when returning results, and the REFRESH MATERIALIZED VIEW command can also use parallel queries. Additionally, parallel sequential scanning of chunks is supported, improving I/O throughput for large-scale data scanning, bringing it close to the I/O throughput limit of block devices.

Index enhancements

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 has effectively optimized the handling of duplicate data in B-tree indexes (standard database indexes), reducing the overall space usage of B-tree indexes while improving overall query performance. Specifically, it introduces the index compression policy of deduplication technology to automatically deduplicate and compress duplicate data in B-tree indexes and reduce the space occupied by indexes. At the same time, version 2.0 also addresses resource consumption issues with B-tree indexes, including table bloat problems caused by frequent index updates.

Additionally, this version provides GiST indexes that pre-sort data during the building process, significantly improving the creation speed of GiST and SP-GiST indexes and reducing index size.

Partitioned table improvements

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 has improved the partition system, resulting in better performance when querying partitioned tables. Now, you can use more flexible partition pruning and intelligent JOIN operations. Additionally, this version has enhanced support for triggers and added support for logical replication, making data replication and synchronization more efficient and reliable.

Furthermore, version 2.0 has optimized partition pruning capabilities, reducing child partition SubPlans and duplicate cached plans, allowing for faster query execution. At the same time, when adding or removing partitions, using the alter table detach|attach PARTITION concurrently mode avoids lock conflicts, improving database availability and stability.

Parallel VACUUM (garbage collection)

The VACUUM mechanism is very important in database management in PolarDB for PostgreSQL (Compatible with Oracle) 2.0, as it can release table space that is no longer in use to alleviate common table bloat problems. To further optimize the efficiency and performance of garbage collection, version 2.0 introduces a parallel index recovery mechanism. Additionally, you can specify the number of parallel workers to better meet your needs.

Through these optimizations, database management in PolarDB for PostgreSQL (Compatible with Oracle) 2.0 has higher efficiency, reliability, and stability, making it easier for you to manage and maintain your data.

Query optimization

Introduced incremental sorting, where sorted data from earlier steps in a query can accelerate sorting in subsequent steps. This optimization can significantly improve query performance and efficiency.
Added more types of aggregate and grouping set queries that can leverage PostgreSQL's efficient hash aggregation functionality. This means that queries with large aggregations do not need to be fully loaded into memory, significantly reducing memory usage and I/O overhead.
Supports linear search TO hash table probe, which can significantly improve the processing performance of IN statements. The performance improvement is more significant, especially in SQL with multiple conditions or requiring filtering of large amounts of data.

Support for LZ4 compression algorithm and enhanced security features

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 introduces several new features, including support for selecting the LZ4 algorithm for data column compression to improve compression performance, while still maintaining support for the original pglz compression method. Additionally, you can use the predefined roles pg_read_all_data and pg_write_all_data to grant read-only or write-only permissions for tables/views/sequences objects at once, providing you with more flexible data management options.

Version 2.0 adopts object permission granularity for cloud-based permission management, ensuring data security and reliability. This optimization protects sensitive data and prevents data leaks and security breaches.

Core features

Data type compatibility

Comprehensive support for Oracle standard data types

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 extends support for 64-bit Date type storage, fully compatible with Oracle's Date type storage and display at year, month, day, hour, minute, and second levels.
Supports Oracle standard types such as char, varchar2, nvarchar2, nvarchar, binary_integer, number, row, rowid, and provides nls_date_format, nls_timestamp_format to control the output format of time types.
For numerical calculations, division results for INT2, INT4, and INT8 types are all floating-point numbers, improving compatibility with Oracle behavior in numerical calculations.

Enhanced JSON and XML type support

Supports JSON_ELEMENT_T, JSON_OBJECT_T, JSON_ARRAY_T types, enhancing the flexibility of JSON data processing and more complex JSON operation scenarios.
Supports XMLELEMENT, XMLAGG returning XmlType type, facilitating the generation and manipulation of XML data.
Supports XmlType's extract and getStringVal functions, extending XML data processing capabilities.
Supports ROW type NULL/NOT NULL constraint validation, ODCI type extension, and conversion between composite types and record types with default values.

Function and operator compatibility

Rich Oracle-compatible function library

Supports Oracle-specific features such as decode, group_id operators, ORDER BY operator in aggregate functions, spaces between built-in binary operators, minus set operator, function calls in a.b form without parentheses, and empty strings treated as NULL.
Supports time functions including to_date, add_month, and other built-in time type functions.
Supports string functions including instr, lpad, rpad, and other built-in string functions.
Supports binary functions including rawtohex, hextoraw, vsize, and other binary-related functions.
Supports regular functions including regexp_count, regex_instr, regex_substr, regex_like, and other regular functions.

Continuously enhanced function compatibility

Added JSON_OBJECT, JSON_ARRAYAGG functions to optimize JSON data processing.
Supports calling XMLELEMENT, XMLAGG using .function, enhancing flexibility in XML composite operations.
Supports built-in functions CONVERT(date,name,name) and NVL(interval, interval) for handling date and interval type data.
Upgraded TO_DATE/TO_CHAR function syyyy format processing to ensure date processing accuracy.
Supports nested implicit type conversion for function OUT parameters, extends XMLSEQUENCE function XML data processing capabilities.
Supports USERENV function for managing session environment information.

SQL syntax compatibility

Comprehensive query syntax compatibility

Supports using the same aliases in subqueries at different levels, case-insensitive table names and column names, and full compatibility with keyword aliases.
Supports using aliases in INSERT/SELECT syntax structures, supports default DISTINCT, ORDER BY clauses in target columns, and supports COUNT(*) aggregate function in ORDER BY clauses.
Supports INSERT/UPDATE followed by syntax like namespace.table.col, supports (+) table join operator.
Supports using UPDATE/DELETE syntax with WHERE in parallel in MERGE clauses.

Pseudocolumns and enhanced DML syntax

Supports ROWNUM syntax to mark row numbers in query results, and calling ROWNUM with quotes.
Supports globally unique sequences.
Supports SELECT/DELETE/UPDATE acting on subqueries.
Supports SELECT FOR UPDATE WAIT and SELECT FOR UPDATE ORDER BY syntax.
Supports using MERGE INFO syntax in views.
Supports basic HINT syntax.
Supports CONNECT BY clause to determine parent-child relationship of rows when executing hierarchical queries.
Supports using EDITIONABLE keyword in views and tables.
Supports UNION operations between TEXT type and other types.

DDL syntax compatibility

Table and object management syntax

Supports ALTER SESSION ENABLE syntax for controlling session behavior.
Supports generated column syntax when creating tables and some special keywords.
Supports MONITORING keyword for tracking table usage.
Supports ALTER TYPE ADD(ATTRIBUTE) syntax to enhance flexibility in object type management.
Supports REFRESH COMPLETE syntax for updating materialized view data.
Supports WITH CHECK OPTION to enforce view constraints.
Supports manual creation of circular dependencies for materialized views.
Supports DROP FORCE to forcibly delete synonyms.

Security and permission management

Supports CREATE PROFILE functionality, providing password reuse count, time, and password verification functions, significantly enhancing database security.
Supports CREATE TABLE CACHE syntax to optimize data buffering strategies.
System triggers support ALTER TRIGGER ENABLE/DISABLE syntax.
Supports creating PUBLIC synonyms under PUBLIC SCHEMA, and supports synonyms for PACKAGE and TYPE BODY.
Supports MODIFY COLUMN syntax.
Supports deleting aggregate functions using DROP AGGREGATE.

Partitioned table functionality

Complete partitioned table support

Supports Oracle-style partitioned tables, including creation of first-level and second-level partitions, and three partition types: LIST, RANGE, and HASH.
Supports common partition management operations such as CREATE, COALESCE, DROP, EXCHANGE, MERGE, MODIFY, MOVE, RENAME, SPLIT, TRUNCATE.
Supports partitioned table child template partitions, automatic creation of interval partitions, and Oracle-compatible A PARTITION(B) partition calling syntax.

Advanced partition features

Supports automatic addition of partitioned table indexes. When a global index needs to be created, it supports calling without explicitly using GLOBAL, and allows using REFERENCING OLD syntax in partitioned table triggers.
Supports using Interval partitions in partitioned tables, and using them with Global Index.
Allows converting MAXVALUE partitions to DEFAULT partitions in partitioned tables to accept null values on partition keys.
Fixed stability issues related to partitioned table creation and defects in partitioned table view queries.

Transaction processing capabilities

CSN transaction snapshot mechanism

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 introduces a new transaction snapshot mechanism, the CSN transaction snapshot mechanism, to improve MVCC performance. Unlike native PostgreSQL, which uses xid transaction number lists as transaction snapshots, CSN snapshots use a monotonically increasing 64-bit integer value, which not only accelerates transaction snapshot generation but also improves the performance of row visibility determination in MVCC.

Autonomous transactions and statement-level rollback

Supports using autonomous transactions in stored procedures and anonymous blocks, making the commit status of the transaction independent of the parent transaction.
Supports statement-level transaction rollback. When auto-commit is disabled, statement-level errors only roll back the current subtransaction while preserving the main transaction's running state.

These features provide more flexible transaction control mechanisms for complex business scenarios.

Trigger functionality

Supports DDL system triggers, facilitating the handling of DDL operation-related triggers.
Supports ALTER TRIGGER ENABLE/DISABLE to manage system triggers, making it easier to control trigger behavior.
Supports DDL system triggers at Schema/Database levels, facilitating the handling of DDL operations at corresponding levels.
Supports updating('col') syntax for trigger subfunctions, facilitating complex trigger logic handling.

PL/SQL programming support

Basic syntax compatibility

Supports PL/SQL calling without $$ symbols.
Functions/stored procedures support IN, OUT, INOUT parameters for pass-by-reference parameter values.
Anonymous blocks support variable binding and two-phase execution.
Supports constant variable compatibility.
Supports implicitly declared record type loop variables.
Supports :NEW/:OLD syntax in stored procedures.
Supports using no return value in triggers.
Supports custom Exception variables.

Advanced PL/SQL features

Supports PROGRAM_ERROR exception syntax.
Supports existence checking for subfunction recfield fields.
Supports FOR row IN loop statements.
Supports using record arrays as function parameters.
Supports SQL layer reserved keywords as function names.
Supports using collect and reverse as stored procedure names in PL layer.
Supports goto loop syntax.
Supports using package variables in subqueries within PL blocks.
Supports using func.var to call variables declared in functions.

Subfunction functionality

Supports subfunctions using external variables as default parameter values, simplifying variable passing logic.
Supports trigger subfunctions using :new/:old, enhancing trigger subfunction compatibility.
Supports collection type elements in subfunctions using external variables.
Supports package subfunctions using private functions within the package, ensuring unified scheduling of package resources.
Supports using package variables in subfunctions.
Supports subfunctions executing with default collation.
You can use external variables in closures.
Supports calling variables under subfunctions.

Cursor functionality

Supports using => to specify default values for cursor parameters.
Supports open cursor() syntax for parameterless cursors, simplifying cursor initialization logic.
Supports specifying default parameter values when defining cursors.
Supports using cursor%rowtype to specify function parameter types.
Supports using persistent cursors after ROLLBACK, extending data query continuity.
Supports default non-scrollable cursors, reducing memory usage and optimizing execution performance.
Supports cursors in package functions using other functions within the package.
Supports using package variables in cursor subqueries.
Supports reopening explicit cursors.

Package functionality

Supports creation and deletion of custom package functionality.
Supports common collection types such as associative arrays, nested tables, and variable-length arrays.
Supports basic initialization, calling, and assignment operations for collection types.
Supports one-dimensional SUBTYPE in packages, extending data model support for packages.
Package record supports constraint, ensuring data integrity.
Supports case-insensitive package access.
Supports package functions depending on subsequently defined types.
Supports package function stored procedures ignoring default values.
Supports using package variables to specify package function parameter types.
Supports package variables as stored procedure variables and default values for subfunction parameters.

Collection type support

Supports using associative arrays as parameters and return values, facilitating handling of complex data structures.
Supports variable-length data equivalent declarations, simplifying collection type space management logic.
The collection type range can be deleted.
Supports built-in associative arrays in built-in packages.
Supports three-dimensional associative arrays, extending collection type functionality.
Supports associative array elements as two-dimensional record, enhancing composite type expression, and supports nesting with records.
Supports fetch bulk into to coll(idx).recfield collection type, optimizing DML execution efficiency.
Supports using high-dimensional collection types in DML statements.
Supports default values for collection type function parameters.
Supports associative array constructors.
Supports conversion operations and set operations for collection types.

Built-in package ecosystem

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 provides a rich set of Oracle-compatible built-in packages covering various aspects of enterprise applications:

DBMS_AQ: Provides functions for message queue enqueue and dequeue operations.
DBMS_AQADM: Provides configuration and management functions for message queues.
DBMS_ALERT: Provides a set of stored procedures for registering, sending, and receiving alerts.
DBMS_APPLICATION_INFO: Records the names of modules or transactions being executed in the database for performance tracking and debugging.
DBMS_CRYPTO: Provides encryption and decryption functionality for stored data, supporting AES, DES, hashing, and message digest functions.
DBMS_DDL: Facilitates DDL-related operations.
DBMS_JOB: Used to create and manage scheduled tasks, allowing users to submit scheduled tasks to be executed in the database at scheduled times.
DBMS_LOB: Provides access and operation methods for large objects, including BLOB and CLOB.
DBMS_LOCK: Provides the built-in SLEEP function to pause the current session for a period of time.
DBMS_METADATA: Provides methods to obtain metadata information for database objects, used for rebuilding objects in the database.
DBMS_MVIEW: Supports REFRESH without including dependent objects.
DBMS_OUTPUT: Can send messages from stored procedures, packages, or triggers.
DBMS_XMLGEN: Provides convert function for handling XML data conversion.
DBMS_XMLPARSER: Provides parsing methods for XML documents, which can be used to generate Document objects.
DBMS_PIPE: Supports pipe communication between different sessions within the same instance.
DBMS_RANDOM: Provides a series of random functions and stored procedures.
DBMS_REDACT: Provides functions for data masking of queries by low-privilege users or applications, protecting database data security.
DBMS_RLS: Allows Virtual Private Database (VPD) to execute on specific PolarDB database objects.
DBMS_SESSION: Provides interfaces for accessing session, user, and other information from PL/SQL.
DBMS_SPACE: Facilitates space management-related operations.
DBMS_SQL: Provides interfaces for executing DML and DDL statements using dynamic SQL, executing PL/SQL anonymous blocks, and calling PL/SQL stored procedures and functions.
DBMS_TRANSACTION: Used for transaction-related operations.
DBMS_UTILITY: Provides various utility subroutines, package dependency retrieval.
DBMS_XMLDOM: Provides DOM access interfaces for XML documents, supporting the definition and generation of various DOM objects.
UTL_ENCODE: Provides a set of functions for data encoding and decoding, facilitating data transmission between different hosts.
UTL_I18N: Provides services consisting of ESCAPE_REFERENCE and UNESCAPE_REFERENCE functions, providing additional globalization capabilities for applications written in PL/SQL.
UTL_MATCH: Enhances string pattern matching functionality, simplifying text operation processes.
UTL_HTTP: Extends Web interaction functionality.
UTL_RAW: Provides functions for manipulating RAW type data.
UTL_TCP: Strengthens TCP communication functionality, expanding network transmission scenarios.
UTL_URL: Provides methods for converting invalid characters and reserved characters in URLs.

System view compatibility

Supports Oracle common views such as ALL_PART_KEY_COLUMNS, USER_PART_KEY_COLUMNS, DBA_PART_KEY_COLUMNS, DBA_ROLE_PRIVS, USER_ROLE_PRIVS.
Supports DBA_COL_COMMENTS view for viewing and managing column comment information.
Supports nls parameter views, extending support for internationalization parameters, enhancing multilingual configuration management.
Supports DBA_TAB_COLUMNS view with all parameters, providing more comprehensive table column information.
Supports SESSION_ROLE built-in view, extending session role management functionality.
Supports USER_TAB_PARTITIONS view HIGH_VALUE field, optimizing partition table high value management.
Supports DBA_OBJECTS view CREATED field for viewing object creation time.

Other advanced features

DBLink heterogeneous connections

Supports DBLink functionality, enabling connections to PostgreSQL and Oracle databases. This feature provides you with cross-database access capabilities, facilitating data integration and migration scenarios.

Character encoding support

Supports server-side GBK and GB18030 encoding to reduce additional encoding conversion costs in client GBK environments. This feature is particularly suitable for application scenarios in Chinese environments, enhancing the system's localization support capabilities.

Syntax compatibility differences

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 has the following differences compared to version 1.0:

Type	Description
Data types	Does not support `ABSTIME` type.
DDL syntax	Does not support using `CASCADE` syntax after `DROP USER`. Does not support `GRANT EXEMPT ACCESS POLICY TO USERNAME` syntax. Does not support `SHOW ROLE` syntax. Does not support using `NOLOGGING` syntax after `CREATE INDEX`.
Views and partitioned tables	Some Oracle-compatible view definitions do not have a `SCHEMA_NAME` column. In a partitioned table, the parent table has at least one child partition by default. Does not support inserting null values into non-`DEFAULT` partitions. Does not allow ATTACH operations on `HASH` partitions. For `LIST` type partitioned tables, if they contain a default partition, `ADD` partition operations are not allowed.
Functions, stored procedures, and triggers	Does not support functions and stored procedures having the same name, and does not support functions and stored procedures with parameters as subqueries. Does not support the fourth input parameter of the `REGEXP_REPLACE` function being `'n'`. Does not support the `DECODE` function with only two input parameters, does not support using `COLLATE` modifier on character parameters. Does not support the function `GET_RAW_PAGE` with double quotes as the first parameter. Does not support using `ROWNUM` as a function parameter. If a stored procedure does not have `ANYELEMENT` type input, there cannot be `ANYELEMENT` type in the output. In an SQL function, if a parameter has a default value, subsequent parameters must also have default values. Trigger names must be 54 characters in length.
Transactions	If a write operation occurs in an autonomous transaction, the transaction must be explicitly committed or rolled back. Otherwise, the transaction fails.
SQL queries	Does not support the following optimization methods: `CHOOSE/FIRST_ROWS/FIRST_ROWS_10/FIRST_ROWS_100/FIRST_ROWS_1000`. You cannot use a synonym in a subquery to call an object in a parent query. Does not support syntax without `VALUES xxx` between `INSERT ALL INTO xxx` and `SELECT xxx`. Does not support comparing the `DUMMY` column in the `DUAL` table with numeric types. Does not support using `'!!a'` to represent the factorial of a. You cannot call other functions in a non-invariant function. Does not support setting `ICU_SHOR_FORM` to `'AN_CX_EX_LROOT_NO_S3'` when creating `COLLATION`. Does not support using `BIT` type to assign default values to `BOOL` type. You cannot use columns to assign default values to other columns.
Packages	You cannot use package variables in SQL statements. Does not support using `DBMS_SQL.TO_REFCURSOR` and `DBMS_SQL.TO_CURSOR_NUMBER` in the `DBMS_SQL` package. Does not support using `SELECT INTO` statements in the `DBMS_SQL` package, does not support using `DEFINE_COLUMN` for non-`SELECT` statements, does not support assigning values using `DBMS_SQL.NATIVE` constants. Does not support using pure numbers as table names in the `DBM_UTILITY` package, does not support using `NULL` as an input parameter for `GET_HASH_VALUE`. Does not support using 0 as an input parameter for `RANDOMBYTES` in the `DBMS_CRYPTO` package. Does not support setting the starting position to a value exceeding the total length of `RAW` in the `SUBSTR` function in the `UTLRAW` package. Does not support using negative numbers for the `TIMEOUT` parameter in the `SEND_MESSAGE` function in the `DBMS_PIPE` package. Does not support `DBMS_RLS` policy functions returning `NULL`. Does not support using negative numbers as input parameters in the `INSTR` function of the `DBMS_LOB` package.
PL/SQL and collection types	Does not support calling `SQLCODE` and `SQLERRM` in non-exception conditions. Dynamic SQL statements cannot be bound to strongly typed cursors. DDL operations cannot be executed in dynamic SQL statements that have parameters. Does not support using `STRING` or `LONG` types as index types for associative arrays. You cannot globally declare non-null properties for set types.