AnalyticDB for PostgreSQL V7.0 is released based on PostgreSQL 12 to provide competitive features, performance, security, and enterprise-class capabilities.
Usage notes
AnalyticDB for PostgreSQL V7.0 is supported only for instances in elastic storage mode.
Performance testing
The following figure shows the performance test results in two scenarios: the Laser compute engine that is developed in-house by Alibaba Cloud and the native compute engine of Greenplum. The Laser compute engine that is developed in-house by Alibaba Cloud provides approximate five times the performance of the native compute engine of Greenplum based on the total execution duration of 22 queries. For more information, see TPC-H performance testing for AnalyticDB for PostgreSQL V7.0.
New features
Category | Feature | Description |
N/A | Codebase | AnalyticDB for PostgreSQL V7.0 is released based on PostgreSQL 12. |
Security | Row security policies are supported to allow database accounts to have permissions on specific table rows. | |
The SCRAM-SHA-256 encryption method is supported to provide higher security than MD5. | ||
Dynamic data masking allows you to mask table columns and configure masking policies for specific users. Different masking functions are used to show masked data to different users. This protects sensitive data and improves data security. | ||
Enterprise-class capabilities | The CREATE FUNCTION and CREATE PROCEDURE statements are supported. | |
Generated columns that are computed based on the values of other columns are supported. | ||
Features and performance | Table partitions | Hash partitions are supported in addition to range and list partitions. Hash partitions can be used in the same manner as in PostgreSQL. |
All SQL:2011 standard functions are supported. | ||
The columns for collecting statistics can be specified. This improves the cost estimation accuracy and query performance. | ||
JSON functions | More JSON functions are supported. | |
Covering indexes | More columns are covered in an existing index to improve query performance. | |
Just-in-time (JIT) compilation is supported to improve query performance. | ||
Aggregate function reuse is supported to perform only one aggregation on the same columns. | ||
Indexes | Hash, BRIN, and Bloom indexes are supported. B-tree, GIN, GiST, and Bitmap indexes are optimized. |
Engine features
AnalyticDB for PostgreSQL V7.0 supports most of PostgreSQL features and uses the same syntax as PostgreSQL. The following table describes the engine features that are supported by AnalyticDB for PostgreSQL V7.0.
Category | Feature | Supported |
Basic features | ALTER TABLE | Yes |
Indexes | Yes | |
Primary keys | Yes | |
Unique constraints | Yes | |
INSERT ON CONFLICT | Yes | |
Table unlogging | Yes | |
Triggers | Yes (Partial triggers are supported.) | |
Heap tables, append-optimized row-oriented (AORO) tables, and append-optimized column-oriented (AOCO) tables | Yes | |
Custom data types | Yes | |
Explicit cursors | Yes | |
Compute engines | Orca optimizer | Yes |
Laser engine | No | |
Transaction capabilities | Subtransactions | Yes |
Transaction isolation levels | Yes (Read Committed and Repeatable Read are supported.) | |
Advanced features | Backup and restoration | Yes |
Materialized views | Yes | |
Auto-vacuum | Yes (Auto-vacuum is supported only for system tables.) | |
Auto-analyze | Yes | |
Elastic scale-out | Yes | |
Elastic scale-in | No | |
GIS/Ganos | Yes | |
Data sharing | No |
Console features
The following table describes the console features that are supported by AnalyticDB for PostgreSQL V7.0.
Category | Feature | Supported |
Instance management | Basic instance information | Yes |
Logon to databases by using Data Management (DMS) | Yes | |
Instance creation | Yes | |
Instance release | Yes | |
Instance restart | Yes | |
Instance configuration upgrade or downgrade | Yes | |
Coordinator node addition or removal | No | |
Instance scale-out | Yes | |
Instance scale-in | No | |
Minor version update | Yes | |
Account management | Account creation | Yes |
Password resetting | Yes | |
Database connection | Basic connection information (such as the endpoint and port number) | Yes |
Public endpoint application | Yes | |
Monitoring and alerting | Monitoring | Yes |
Alert rules | Yes | |
Data security | Whitelists | Yes |
SQL audit | Yes | |
SSL encryption | Yes | |
Backup and restoration | Yes | |
Configuration | Parameter settings | Yes |
Data migration
The following table describes the data migration operations that are supported by AnalyticDB for PostgreSQL V7.0.
Migration type | References | Supported |
Data write | Yes | |
Yes | ||
Yes | ||
Table data migration | Yes | |
Yes | ||
Yes | ||
Use the \copy command to import data from your computer to AnalyticDB for PostgreSQL | Yes | |
Use an external table to import data from OSS at a high speed | Yes | |
Use external tables for federated analytics of Hadoop data sources | Yes | |
Warehouse data migration | Migrate data from a self-managed Greenplum cluster to an AnalyticDB for PostgreSQL instance | Yes |
Migrate data from a Teradata database to an AnalyticDB for PostgreSQL instance | No You can import data by using external tables. | |
Migrate data from an Amazon Redshift instance to an AnalyticDB for PostgreSQL instance | No You can import data by using external tables. | |
Migrate data from a self-managed Oracle application to an AnalyticDB for PostgreSQL instance | No You can import data by using external tables. | |
Migrate data from a self-managed Oracle database to an AnalyticDB for PostgreSQL instance | No You can import data by using external tables. |
FAQ
Q: Can I upgrade the major engine version of an instance in elastic storage mode from 6.0 to 7.0?
A: The major engine version of an instance in elastic storage mode cannot be directly upgraded from 6.0 to 7.0. To upgrade to V7.0, Submit a ticket.