Query data across instances-AnalyticDB(AnalyticDB)-阿里云帮助中心

Introduction

Organizations often run multiple database instances to support different applications and business units. This can create challenges when you need to run analytics across these separate instances. Traditional approaches have significant drawbacks:

Storing duplicate data across instances leads to data redundancy and complicates data management.
Moving data to a central storage location, such as an object storage service, can introduce latency and prevent real-time analysis.

To solve the preceding problems, the Alibaba Cloud team designed and implemented a Foreign Data Wrapper (FDW) based on the MPP architecture of AnalyticDB for PostgreSQL. This FDW fully leverages the interconnectivity of compute nodes to enable parallel data access between instances and improve data access efficiency. Its performance is several times higher than that of the native postgres_fdw in PostgreSQL.

Usage notes

The source and destination instances must belong to the same Alibaba Cloud account and reside in the same region and availability zone.
If the source instance is in Serverless mode, its data is inaccessible during scaling operations.
High-speed cross-instance queries are supported only on the following database kernel versions:
- elastic storage mode V7.0: V7.0.1.x and later.
- elastic storage mode V6.0: V6.3.11.2 and later.
- Serverless mode: V1.0.6.x and later.
Because the password authentication method for AnalyticDB for PostgreSQL V7.0 instances has changed, you must Submit a ticket to access AnalyticDB for PostgreSQL V7.0 instances from AnalyticDB for PostgreSQL V6.0 instances or Serverless instances.
Foreign tables support SELECT and INSERT only. UPDATE and DELETE are not supported.
JOIN pushdown and AGG pushdown are supported only in elastic storage mode V7.0.
In elastic storage mode V7.0, the ORCA optimizer can generate execution plans for foreign tables. In elastic storage mode V6.0 and Serverless mode, the ORCA optimizer cannot process foreign tables and falls back to the native optimizer to generate the execution plan.

Procedure

The following steps describe how to enable cross-instance queries between Instance A and Instance B, which are in the same account, region, and availability zone. Once configured, you can connect to the db01 database on Instance A to access tables in the db02 database on Instance B. You can then run federated queries that join local and remote tables, using the high-speed connectivity between compute nodes.

Connect to your instances using a psql client. For instructions, see Client connection.
Create a database on Instance A and Instance B.
On Instance A, create a database named db01 and switch to it.
```
CREATE DATABASE db01;
\c db01
```
On Instance B, create a database named db02 and switch to it.
```
CREATE DATABASE db02;
\c db02
```
In the db01 database on Instance A and the db02 database on Instance B, create the greenplum_fdw and gp_parallel_retrieve_cursor extensions. For instructions, see Install, update, and uninstall extensions.
Obtain the internal IP addresses of Instance A and add them to the whitelist of Instance B. For instructions on how to configure a whitelist, see Configure an IP address whitelist.
On Instance A, run the following SQL statement to get the internal IP addresses.
```
SELECT dbid, address FROM gp_segment_configuration;
```

In the db02 database on Instance B, prepare the test data.

CREATE SCHEMA s01;

CREATE TABLE s01.t1(a int, b int, c text); 
CREATE TABLE s01.t2(a int, b int, c text); 
CREATE TABLE s01.t3(a int, b int, c text); 

INSERT INTO s01.t1 VALUES(generate_series(1,10),generate_series(11,20),'t1');
INSERT INTO s01.t2 VALUES(generate_series(11,20),generate_series(11,20),'t2');
INSERT INTO s01.t3 VALUES(generate_series(21,30),generate_series(11,20),'t3');

In the db01 database on Instance A, create a server and a user mapping.

Create the server.

CREATE SERVER remote_adbpg FOREIGN DATA WRAPPER greenplum_fdw
    OPTIONS (host 'gp-xxxxxxxx-master.gpdb.zhangbei.rds.aliyuncs.com',
             port '5432',
             dbname 'db02');

The following table describes the parameters.

Parameter	Description
host	The internal endpoint of Instance B. Log on to the AnalyticDB for PostgreSQL console. On the Basic Information page for Instance B, navigate to the Database Connection Information section to find the Internal Endpoint.
port	The port number for the internal endpoint of Instance B. The default value is `5432`.
dbname	The name of the source database. In this example, the value is `db02`.

Create a user mapping. For more information, see CREATE USER MAPPING in the PostgreSQL documentation.

CREATE USER MAPPING FOR PUBLIC SERVER remote_adbpg
    OPTIONS (user 'report', password '******');

The following table describes the parameters.

Parameter

Description

user

The database account for Instance B.

This account must have read permissions on the db02 database. Write permissions are required to perform INSERT operations. For instructions on how to create a database account, see Create and manage accounts.

password

The password for the specified database account.

In the db01 database on Instance A, map the remote tables.
You can map remote tables in one of the following two ways:
- Create a foreign table for each source table.
```
CREATE SCHEMA s01;
CREATE FOREIGN TABLE s01.t1(a int, b int) 
	server remote_adbpg options(schema_name 's01', table_name 't1');
```
  This method has the following advantages and disadvantages:
  - Advantage: You can customize the foreign table's DDL. For example, if the source table t1 in the db02 database has three columns (a, b, and c), but you only need columns a and b, you can specify just those columns when you create the foreign table.
  - Disadvantage: You must know the DDL of each source table. It can be tedious to import multiple tables one by one.
- Import all tables from a schema in the source database.
```
CREATE SCHEMA s01;
IMPORT FOREIGN SCHEMA s01 LIMIT TO (t1, t2, t3) 
	FROM SERVER remote_adbpg INTO s01;
```
  This method has the following advantages and disadvantages:
  - Advantage: You can quickly import multiple foreign tables without knowing the DDL for each one.
  - Disadvantage: This method is less flexible. The names and columns of the foreign tables must match the source tables.
  For more information, see IMPORT FOREIGN SCHEMA in the PostgreSQL documentation.

Query data from Instance B through the db01 database on Instance A.

SELECT * FROM s01.t1;

The following example shows the output.

 a  | b  | c
----+----+----
  2 | 12 | t1
  3 | 13 | t1
  4 | 14 | t1
  7 | 17 | t1
  8 | 18 | t1
  1 | 11 | t1
  5 | 15 | t1
  6 | 16 | t1
  9 | 19 | t1
 10 | 20 | t1
(10 rows)

Performance test

The following figure shows the results of a TPC-H performance test that compares local queries and cross-instance queries. The test was performed on a 1 TB dataset.

For large-scale datasets, such as in a 1 TB TPC-H test, cross-instance query performance is about 50% that of a local query.
Cross-instance queries involve data transfer over the network. To minimize network I/O and improve performance, add filtering conditions to the WHERE clause when you query foreign tables.

References

AnalyticDB for PostgreSQL also supports cross-database queries. For more information, see Query data across databases.