PolarDB:PolarDB for PostgreSQL(Compatible with Oracle) 2.0 available for commercial use

End date of public preview

June 30, 2023

Official release date

July 5, 2023

Billing

The subscription and pay-as-you-go billing methods are supported. For more information, see Billable items.

Benefits

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 provides higher performance than PolarDB for PostgreSQL(Compatible with Oracle) 1.0 in terms of high concurrency, high write loads, parallel queries, and logical replication. PolarDB for PostgreSQL(Compatible with Oracle) 2.0 offers the following profits:

Higher performance

The following performance results are based on pgbench and polar.o.x8.xlarge clusters and for the select-only and TPCB-like scenarios.

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 provides higher performance than PolarDB for PostgreSQL(Compatible with Oracle) 1.0 . In the TPCB-like scenario, the performance is improved by more than 30%. When a large number of connections exist, PolarDB for PostgreSQL(Compatible with Oracle) 2.0 improves the transaction throughput by up to 100% in the test.

To further improve performance, PolarDB for PostgreSQL(Compatible with Oracle) 2.0 provides several parallel query features. For example, the RETURN QUERY command in PL/pgSQL functions supports parallel computing of queries. The REFRESH MATERIALIZED VIEW command also supports parallel queries. It also supports parallel sequential scanning of chunks to improve the I/O throughput of large-scale data scanning and approach the I/O throughput limit of block devices.

Enhanced indexes

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 optimizes the processing of duplicate data in B-tree indexes (standard database indexes) to reduce the overall space used by B-tree indexes and improve the 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. PolarDB for PostgreSQL(Compatible with Oracle) 2.0 also solves the high resource consumption issue of B-tree indexes, including table bloats caused by frequent index updates. It also provides GiST indexes to pre-sort data during the construction process to accelerate the generation of GiST and SP-GiST indexes and reduce the size of indexes.

Improved partitioned tables

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 optimizes the partitioning system to improve the query performance of partitioned tables. You can use highly flexible pruning and smart joins of partitions. It also improves triggers and logical replication to enhance efficiency and reliability of data replication and synchronization.

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 also enhances partition pruning capabilities to reduce the subplans of subpartitions and duplicate cached plans and accelerate queries. You can execute the alter table detach|attach PARTITION concurrently statement to add or remove partitions. This avoids lock conflicts and improves database availability and stability.

Parallel VACUUM mechanism for garbage collection

The VACUUM mechanism is very essential to PolarDB for PostgreSQL(Compatible with Oracle) 2.0. It can release tablespaces that are no longer in use to alleviate table bloats. To further improve the efficiency and performance of garbage collection, PolarDB for PostgreSQL(Compatible with Oracle) 2.0 introduces a parallel index reclaim mechanism. You can also specify the number of parallel workers to better meet your needs.

With these optimizations, PolarDB for PostgreSQL(Compatible with Oracle) 2.0 can manage databases in a more efficient, reliable, and stable manner and simplifies data management and maintenance.

Optimized queries

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 introduces incremental sorting. The sorted data of earlier steps in the query can accelerate the sorting of subsequent steps. This optimization can significantly improve the performance and efficiency of queries.

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 also uses additional types of aggregation and group set queries to take advantage of the efficient hash aggregation feature of PostgreSQL. This means that queries with large aggregates may not be fully loaded into memory. This can significantly reduce memory usage and I/O overheads.

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 supports the linear search TO hash table probe feature to accelerate the processing of IN clauses. The performance improvement is particularly significant in SQL statements that have multiple conditions or filter a large amount of data.

LZ4 compression algorithm to enhance security

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 allows you to use the LZ4 algorithm to compress columns. This improves the compression performance and reserves the original PGLZ compression method. You can also use the two predefined roles of pg_read_all_data and pg_write_all_data to grant the read-only or write-only permissions on tables, views, and sequences. This is a more flexible data management method.

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 allows you to control object permissions to ensure data security and reliability. This optimization protects sensitive data and prevents data leaks and security breaches.

Features

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 is highly compatible with Oracle syntax and supports common Oracle features such as partitioned tables, transaction capabilities, PL/SQL statements, packages, and heterogeneous connections. It also improves performance in terms of high concurrency, high write loads, parallel queries, and logical replication.

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 provides the following Oracle-compatible features:

Oracle-compatible data types such as strings, numbers, and binary data types, and storage of 64-bit date types

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 supports 64-bit date storage. It can store and display Oracle-compatible date types of year, month, day, hour, minute, and second. The data types of char, varchar2, nvarchar2, nvarchar, binary_integer, number, row, rowid. You can use the nls_date_format and nls_timestamp_format parameter to control the output format of time data types.

Oracle-compatible operators

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 supports the following operator-related features: the decode and group_id operators, the order by operator in aggregate functions, inserting spaces between built-in binary operators, and minus set operators. You can call a.b functions without parentheses. Empty strings are considered NULL.

Oracle-compatible string functions, numeric functions, binary functions, regular functions, sampling functions, and aggregate functions

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 supports the following functions: time functions such as to_date and add_month, string functions such as instr, lpad, and rpad, binary functions such as rawtohex, hextoraw, and vsize, coding functions such as sys_guid, sampling functions such as sample function, and regular functions such as regexp_count, regex_instr, regex_substr, and regex_like. You can create Oracle-compatible aggregate functions.

Oracle-compatible aliases, column names, clauses, and views

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 supports using the same alias in subqueries at different levels, case-compatible table names and column names, and keyword aliases: adding aliases in the insert and select statements, the default distinct and order by clause for specified columns, the order by clause in the count(*) aggregate function, both the update or delete statement and where clause used in the merge clause, adding the namespace.table.col parameter in the insert and update statement, and the (+) operator to joint tables.

Common Oracle views such as ALL_PART_KEY_COLUMNS, USER_PART_KEY_COLUMNS, DBA_PART_KEY_COLUMNS, DBA_ROLE_PRIVS, and USER_ROLE_PRIVS are supported.

Oracle-compatible features such as pseudocolumns, sequences, DML statements, and DDL statements

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 supports the following features: the rownum function to mark row numbers in query results, globally unique sequences, the select, delete, and update statements effectively on subqueries, the select for update wait statement, the merge into statement used in views, basic hints, the Rename To statement, the alter table add (col1, col2) statement, the create directory statement, the create context statement, and the connect by clause used to determine the parent-child relationship of rows in hierarchical queries.

Creation, modification, and extension of Oracle-compatible partitioned tables

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 supports Oracle-style partitioned tables. You can create partitions and subpartitions, as well as list, range, and hash partitions. It supports common partition management statements such as CREATE, COALESCE, DROP, EXCHANGE, MERGE, MODIFY, MOVE, RENAME, SPLIT, and TRUNCATE. You can create templated subpartitions for partitioned tables. Interval range partitions can be automatically created. The Oracle-compatible A PARTITION(B) statement can be sued to call partitions.

CSN transaction snapshots, autonomous transactions, and statement-level transaction rollback

In PolarDB for PostgreSQL(Compatible with Oracle) 2.0 , CSN transaction snapshot is a new mechanism used to improve MVCC performance. Unlike the native PostgreSQL where transaction IDs (XIDs) are used as transaction snapshots, CSN snapshot uses a monotonically increasing 64-bit integer value as a transaction snapshot. This accelerates the generation of transaction snapshots and improves the row visibility determination performance of MVCC.

You can use autonomous transactions in stored procedures and anonymous blocks, making the commit state of the current transaction independent of the parent transaction.

Statement-level transaction rollback is supported. If automatic commit is disabled, only roll back the current sub-transaction is rolled back in case of statement-level errors. The main transaction remains running.

Oracle-compatible PL/SQL block behaviors

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 supports the following features: PL/SQL calls without $$ symbols, the IN, OUT, and INOUT parameters in functions and stored procedures for referencing parameter values, variable binding and two-phase operation of anonymous blocks, constant compatibility, implicitly declared record loop variables, the :NEW and :OLD parameters in stored procedures, no return value in triggers, custom Exception variables, and the begin and end parameters in triggers.

Oracle-compatible package features and set types

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 allows you to create and delete custom packages. You can also create and delete common set types such as associative arrays, nested tables, and variable-length arrays. You can also initialize, call, and assign values to sets.

Oracle-compatible built-in packages

• The DBMS_AQ package adds messages to a queue or deletes messages from a queue.

• The DBMS_AQADM package creates and manages message queues.

• The DBMS_ALERT package provides the capability to register for, send, and receive alerts.

• The DBMS_APPLICATION_INFO package records the names of modules or transactions that are being executed in databases, for tracking the performance of the modules and debug the modules.

• The DBMS_CRYPTO package encrypts and decrypts stored data. AES, DES, hashing, and message digests are supported.

• The DBMS_JOB package creates and manages scheduled tasks. You can submit scheduled tasks for execution in databases.

• The DBMS_LOB package provides methods to access and operate large objects, such as BLOB and CLOB data.

• The DBMS_LOCK package provides the sleep built-in function to suspend the current session for a period of time.

• The DBMS_METADATA package retrieves metadata of database objects for rebuilding objects.

• The DBMS_OUTPUT package sends messages from stored procedures, packages, or triggers.

• The DBMS_PIPE package initiates pipeline communication between different sessions of the same routine.

• The DBMS_RANDOM package provides several functions and stored procedures to generate random values.

• The DBMS_RLS package enables Virtual Private Database (VPD) to execute a specified PolarDB database object.

• The DBMS_SESSION package provides APIs for accessing sessions, users, and other information from PL/SQL.

• The DBMS_SQL package provides APIs for using dynamic SQL statements to execute DML and DDL statements, execute PL/SQL anonymous blocks, and call PL/SQL stored procedure and functions.

• The DBMS_UTILITY package provides utility programs.

• The UTL_ENCODE package provides a set of data encoding and decoding functions to facilitate data transmission between different hosts.

• The UTL_I18N package provides a set of ESCAPE_REFERENCE and UNESCAPE_REFERENCE functions for internationalization of PL/SQL applications.

• The UTL_RAW package provides functions for manipulating RAW data.

Persistent cache pool and DBLink

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 reserves the original buffer pool after a database crash or restart. You can reuse the original buffer pool information the next time the program is started.

DBLink can be used to connect to PostgreSQL databases and Oracle databases.

GBK and GB18030 encoding at the server side

PolarDB for PostgreSQL(Compatible with Oracle) 2.0 allows you to use GBK and GB18030 encoding on the server. This reduces transcoding costs when you use GBK on the client.

References

For more information about migrating data from PolarDB for PostgreSQL(Compatible with Oracle) 1.0 to PolarDB for PostgreSQL(Compatible with Oracle) 2.0, see Migrate data between clusters of different PolarDB for Oracle versions.