Feature and syntax comparison of versions 2.0 and 1.0 - PolarDB - Alibaba Cloud - PolarDB

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 introduces a redesigned compatibility architecture with Oracle syntax as the development baseline. Compared to 1.0, version 2.0 delivers measurable performance gains in high concurrency, write-heavy, parallel query, and distributed replication workloads. This topic covers the key differences to help you evaluate migrating to 2.0.

This topic reflects the currently released version (20250630). Some features listed here will be available in future releases.

Performance improvements

Benchmarks using pgbench on a polar.o.x8.xlarge cluster show the following results:

Scenario	Version 1.0	Version 2.0	Improvement
Read-only (select-only)	109,141.30	120,803.95	10.69%
Read-write (tpcb-like)	49,226.71	64,752.41	31.54%

In high-connection scenarios, transaction throughput increases by up to 100%.

Version 2.0 also expands parallel query support:

RETURN QUERY in PL/pgSQL functions supports parallel computation.
REFRESH MATERIALIZED VIEW uses parallel queries.
Parallel sequential scanning of chunks improves I/O throughput for large-scale data scans, approaching the I/O limit of block devices.

Index enhancements

Version 2.0 optimizes B-tree index handling of duplicate data through deduplication, which automatically removes repeating entries to reduce index size and improve query performance. It also addresses table bloat caused by frequent index updates.

GiST and SP-GiST indexes now pre-sort data during the build phase, resulting in faster index creation and smaller index sizes.

Partitioned table improvements

Partition pruning is more efficient: version 2.0 reduces child partition SubPlans and duplicate cached plans, speeding up query execution. Use ALTER TABLE DETACH|ATTACH PARTITION CONCURRENTLY to add or remove partitions without lock conflicts.

Additional improvements include:

Smarter partition pruning and more flexible JOIN operations on partitioned tables.
Enhanced trigger support for partitioned tables.
Logical replication support for partitioned tables.

Parallel VACUUM

Version 2.0 introduces a parallel index recovery mechanism for VACUUM (garbage collection), allowing multiple index-recovery workers to run concurrently. Specify the number of parallel workers to match your workload requirements.

Query optimizer enhancements

Incremental sorting: sorted intermediate results from earlier query steps accelerate subsequent sort operations, improving query efficiency.
Hash aggregation expansion: more aggregate and grouping set queries can use hash aggregation, reducing memory consumption and I/O overhead for large aggregations.
IN-clause optimization: linear search to hash table probe improves performance for IN clauses, especially in queries with multiple conditions or large filter sets.

Compression and security

Version 2.0 adds LZ4 as an option for column-level data compression alongside the original pglz method.

For permission management, use the predefined roles pg_read_all_data and pg_write_all_data to grant read-only or write-only access across all tables, views, and sequences at once. Permission management is now enforced at object-level granularity for cloud-based access control.

Core feature compatibility

Data type compatibility

Oracle standard data types

64-bit Date type storage, fully compatible with Oracle's Date representation at year, month, day, hour, minute, and second levels.
Support for Oracle standard types: CHAR, VARCHAR2, NVARCHAR2, NVARCHAR, BINARY_INTEGER, NUMBER, ROW, and ROWID.
nls_date_format and nls_timestamp_format parameters to control time-type output format.
Division results for INT2, INT4, and INT8 types return floating-point values, matching Oracle behavior.

JSON and XML types

JSON_ELEMENT_T, JSON_OBJECT_T, and JSON_ARRAY_T types for complex JSON operations.
XMLELEMENT and XMLAGG returning XmlType.
XmlType.extract() and XmlType.getStringVal() functions for XML data processing.
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

Oracle-compatible functions

Category	Functions
Operators	`DECODE`, `GROUP_ID`, `ORDER BY` in aggregate functions, `MINUS` set operator
Syntax behaviors	Function calls in `a.b` form without parentheses; empty strings treated as NULL
Time	`TO_DATE`, `ADD_MONTHS`
String	`INSTR`, `LPAD`, `RPAD`
Binary	`RAWTOHEX`, `HEXTORAW`, `VSIZE`
Regular expression	`REGEXP_COUNT`, `REGEXP_INSTR`, `REGEXP_SUBSTR`, `REGEXP_LIKE`

Additional functions in version 2.0

JSON_OBJECT and JSON_ARRAYAGG for JSON construction.
.function syntax for calling XMLELEMENT and XMLAGG.
Built-in functions CONVERT(date, name, name) and NVL(interval, interval).
TO_DATE/TO_CHAR with SYYYY format for extended date range handling.
Nested implicit type conversion for function OUT parameters.
XMLSEQUENCE function for XML data processing.
USERENV function for session environment information.

SQL syntax compatibility

Query syntax

Same-name aliases in subqueries at different nesting levels.
Case-insensitive table names and column names; full keyword alias support.
Aliases in INSERT/SELECT structures; default DISTINCT and ORDER BY in target columns; COUNT(*) in ORDER BY.
INSERT/UPDATE with namespace.table.col syntax; (+) outer join operator.
UPDATE/DELETE with WHERE in MERGE clauses.

DML and pseudocolumn support

ROWNUM for row numbering in query results, including quoted ROWNUM.
Globally unique sequences.
SELECT/DELETE/UPDATE on subqueries.
SELECT FOR UPDATE WAIT and SELECT FOR UPDATE ORDER BY.
MERGE INTO syntax in views.
Basic HINT syntax.
CONNECT BY for hierarchical queries.
EDITIONABLE keyword in views and tables.
UNION operations between TEXT and other types.

DDL syntax compatibility

Table and object management

ALTER SESSION ENABLE for session behavior control.
Generated column syntax and special keywords in CREATE TABLE.
MONITORING keyword for tracking table usage statistics.
ALTER TYPE ADD(ATTRIBUTE) for extending object types.
REFRESH COMPLETE for materialized view updates.
WITH CHECK OPTION to enforce view constraints.
Circular dependency creation for materialized views.
DROP FORCE for synonyms.

Security and permission management

CREATE PROFILE with password reuse count, time limits, and password verification functions.
CREATE TABLE CACHE syntax for data buffering.
ALTER TRIGGER ENABLE/DISABLE for system triggers.
PUBLIC synonyms under the PUBLIC schema; synonyms for PACKAGE and TYPE BODY.
MODIFY COLUMN syntax.
DROP AGGREGATE to delete aggregate functions.

Partitioned table functionality

Core partition support

Oracle-style partitioned tables with first-level and second-level partitions.
Three partition types: LIST, RANGE, and HASH.
Common partition management operations: CREATE, COALESCE, DROP, EXCHANGE, MERGE, MODIFY, MOVE, RENAME, SPLIT, and TRUNCATE.
Child template partitions, automatic interval partition creation, and Oracle-compatible A PARTITION(B) calling syntax.

Advanced partition features

Automatic index addition for partitioned tables; global index creation without explicit GLOBAL keyword.
REFERENCING OLD syntax in partitioned table triggers.
INTERVAL partitions combined with global indexes.
MAXVALUE partitions convertible to DEFAULT partitions to accept null values on partition keys.

Transaction processing

CSN transaction snapshot mechanism

Version 2.0 introduces the CSN (Commit Sequence Number) transaction snapshot mechanism to improve Multi-Version Concurrency Control (MVCC) performance. Unlike native PostgreSQL, which uses transaction ID (xid) lists for snapshots, CSN snapshots use a monotonically increasing 64-bit integer. This accelerates transaction snapshot generation and improves row visibility determination performance in MVCC.

Autonomous transactions and statement-level rollback

Autonomous transactions in stored procedures and anonymous blocks, where the commit status is independent of the parent transaction.
Statement-level rollback: with auto-commit disabled, statement-level errors roll back only the current subtransaction while preserving the main transaction state.

Triggers

DDL system triggers at schema and database levels.
ALTER TRIGGER ENABLE/DISABLE to manage system triggers.
UPDATING('col') syntax for trigger subfunctions.

PL/SQL programming

Basic syntax

PL/SQL blocks without $$ delimiters.
IN, OUT, and INOUT parameters with pass-by-reference semantics.
Variable binding and two-phase execution in anonymous blocks.
CONSTANT variable declarations.
Implicitly declared record type loop variables.
:NEW/:OLD syntax in stored procedures.
Triggers without return values.
Custom EXCEPTION variables.

Advanced PL/SQL features

PROGRAM_ERROR exception syntax.
Field existence checking for subfunction recfield fields.
FOR row IN loop statements.
record arrays as function parameters.
SQL-layer reserved keywords as function names.
COLLECT and REVERSE as stored procedure names in the PL layer.
GOTO LOOP syntax.
Package variables in subqueries within PL blocks.
func.var syntax to access variables declared in functions.

Subfunction enhancements

External variables as default parameter values in subfunctions.
:NEW/:OLD in trigger subfunctions.
Collection type elements in subfunctions referencing external variables.
Package subfunctions calling private package functions.
Package variables in subfunctions.
Subfunctions with default collation execution.
External variables in closures.
Variables under subfunctions callable from outer scope.

Cursor support

=> syntax for cursor parameter default values.
OPEN cursor() syntax for parameterless cursors.
Default parameter values in cursor definitions.
cursor%ROWTYPE for function parameter types.
Persistent cursors after ROLLBACK.
Default non-scrollable cursors for reduced memory usage.
Cursors in package functions calling other package functions.
Package variables in cursor subqueries.
Reopening explicit cursors.

Package functionality

Custom package creation and deletion.
Collection types: associative arrays, nested tables, and variable-length arrays (varrays).
Basic initialization, assignment, and calling of collection types.
One-dimensional SUBTYPE in packages.
RECORD types with CONSTRAINT in packages.
Case-insensitive package access.
Package functions depending on types defined later in the package.
Package function stored procedures ignoring default values.
Package variables as parameter type specifiers.
Package variables as stored procedure variables and subfunction parameter defaults.

Collection types

Associative arrays as parameters and return values.
Variable-length data equivalent declarations.
Range deletion for collection type elements.
Built-in associative arrays in built-in packages.
Three-dimensional associative arrays.
Associative array elements as two-dimensional record types, including nesting with records.
FETCH BULK INTO to coll(idx).recfield collection type for DML efficiency.
High-dimensional collection types in DML statements.
Default values for collection type function parameters.
Associative array constructors.
Conversion and set operations on collection types.

Built-in packages

Version 2.0 provides the following Oracle-compatible built-in packages:

Package	Description
`DBMS_AQ`	Message queue enqueue and dequeue operations
`DBMS_AQADM`	Message queue configuration and management
`DBMS_ALERT`	Registering, sending, and receiving alerts
`DBMS_APPLICATION_INFO`	Recording module and transaction names for performance tracking
`DBMS_CRYPTO`	Encryption and decryption with AES, DES, hashing, and message digest functions
`DBMS_DDL`	DDL-related operations
`DBMS_JOB`	Creating and managing scheduled tasks
`DBMS_LOB`	Access and operations on large objects (`BLOB` and `CLOB`)
`DBMS_LOCK`	Built-in `SLEEP` function to pause the current session
`DBMS_METADATA`	Retrieving metadata for database objects
`DBMS_MVIEW`	`REFRESH` without requiring dependent objects
`DBMS_OUTPUT`	Sending messages from stored procedures, packages, or triggers
`DBMS_XMLGEN`	XML data conversion
`DBMS_XMLPARSER`	Parsing XML documents to generate Document objects
`DBMS_PIPE`	Pipe communication between sessions in the same instance
`DBMS_RANDOM`	Random number generation functions
`DBMS_REDACT`	Data masking for queries by low-privilege users or applications
`DBMS_RLS`	Virtual Private Database (VPD) policies on database objects
`DBMS_SESSION`	Session, user, and environment information from PL/SQL
`DBMS_SPACE`	Space management operations
`DBMS_SQL`	Dynamic SQL for DML and DDL statements, anonymous blocks, stored procedures, and functions
`DBMS_TRANSACTION`	Transaction operations
`DBMS_UTILITY`	Utility subroutines and package dependency retrieval
`DBMS_XMLDOM`	DOM access for XML documents
`UTL_ENCODE`	Data encoding and decoding
`UTL_I18N`	Globalization support via `ESCAPE_REFERENCE` and `UNESCAPE_REFERENCE`
`UTL_MATCH`	String pattern matching
`UTL_HTTP`	HTTP interaction
`UTL_RAW`	RAW data manipulation
`UTL_TCP`	TCP communication
`UTL_URL`	URL character encoding and decoding

System view compatibility

Version 2.0 adds support for the following Oracle-compatible views:

ALL_PART_KEY_COLUMNS, USER_PART_KEY_COLUMNS, DBA_PART_KEY_COLUMNS
DBA_ROLE_PRIVS, USER_ROLE_PRIVS
DBA_COL_COMMENTS for viewing and managing column comments
NLS parameter views for internationalization configuration
DBA_TAB_COLUMNS with all parameters
SESSION_ROLE for session role management
USER_TAB_PARTITIONS with the HIGH_VALUE field
DBA_OBJECTS with the CREATED field for object creation timestamps

Other features

DBLink heterogeneous connections

Database links (DBLink): Connect to PostgreSQL and Oracle databases from PolarDB for PostgreSQL (Compatible with Oracle) 2.0 for cross-database access, data integration, and migration scenarios.

Character encoding support

GBK and GB18030 encoding: Server-side GBK and GB18030 character encoding support reduces encoding conversion overhead in Chinese-language application environments.

Syntax compatibility differences

The following features from version 1.0 are not supported in version 2.0.

Important

Review these limitations before migrating. Workloads that depend on any of the following behaviors will require code changes.

Category	Limitation
Data types	`ABSTIME` type is not supported.
DDL syntax	`CASCADE` after `DROP USER` is not supported. `GRANT EXEMPT ACCESS POLICY TO USERNAME` is not supported. `SHOW ROLE` is not supported. `NOLOGGING` after `CREATE INDEX` is not supported.
Views and partitioned tables	Some Oracle-compatible view definitions do not include a `SCHEMA_NAME` column. A partitioned table's parent table must have at least one child partition. Null values cannot be inserted into non-`DEFAULT` partitions. `ATTACH` is not supported on `HASH` partitions. For `LIST` partitioned tables with a default partition, `ADD PARTITION` is not allowed.
Functions, stored procedures, and triggers	Functions and stored procedures cannot share the same name. Functions and stored procedures with subqueries as parameters are not supported. The fourth parameter of `REGEXP_REPLACE` cannot be `'n'`. `DECODE` with only two input parameters is not supported. `COLLATE` modifier on character parameters is not supported. `GET_RAW_PAGE` with double-quoted first parameter is not supported. `ROWNUM` as a function parameter is not supported. If a stored procedure has no `ANYELEMENT` input, `ANYELEMENT` output is not allowed. If a parameter in an SQL function has a default value, all subsequent parameters must also have default values. Trigger names must be exactly 54 characters in length.
Transactions	Autonomous transactions that include write operations must be explicitly committed or rolled back.
SQL queries	The following optimizer hints are not supported: `CHOOSE`, `FIRST_ROWS`, `FIRST_ROWS_10`, `FIRST_ROWS_100`, `FIRST_ROWS_1000`. Synonyms cannot be used in a subquery to reference objects in a parent query. `INSERT ALL INTO xxx ... SELECT xxx` without `VALUES xxx` is not supported. Comparing the `DUMMY` column in the `DUAL` table with numeric types is not supported. `'!!a'` factorial notation is not supported. Non-immutable functions cannot call other functions. Setting `ICU_SHOR_FORM` to `'AN_CX_EX_LROOT_NO_S3'` when creating a `COLLATION` is not supported. `BIT` type cannot assign default values to `BOOL` type. Columns cannot assign default values to other columns.
Packages	Package variables cannot be used in SQL statements. `DBMS_SQL.TO_REFCURSOR` and `DBMS_SQL.TO_CURSOR_NUMBER` are not supported. `SELECT INTO` in `DBMS_SQL` is not supported. `DEFINE_COLUMN` for non-`SELECT` statements is not supported. `DBMS_SQL.NATIVE` constant assignment is not supported. Pure numbers as table names in `DBMS_UTILITY` are not supported. `NULL` as input for `GET_HASH_VALUE` is not supported. `0` as input for `RANDOMBYTES` in `DBMS_CRYPTO` is not supported. Starting position exceeding the RAW total length in `UTL_RAW.SUBSTR` is not supported. Negative numbers for the `TIMEOUT` parameter in `DBMS_PIPE.SEND_MESSAGE` are not supported. `DBMS_RLS` policy functions cannot return `NULL`. Negative numbers in `DBMS_LOB.INSTR` are not supported.
PL/SQL and collection types	`SQLCODE` and `SQLERRM` cannot be called outside exception handlers. Dynamic SQL cannot be bound to strongly typed cursors. DDL operations in parameterized dynamic SQL are not supported. `STRING` or `LONG` types cannot be used as index types for associative arrays. Non-null properties for collection types cannot be declared globally.