Distributed

Technical architecture

PolarDB for PostgreSQL Distributed Edition contains two types of core nodes:

Compute Nodes (CNs): Access points for the cluster that parse SQL, generate distributed query plans, and manage metadata.
Data Nodes (DNs): Store the physical data shards of tables.

Each CN or DN is a high-availability (HA) PolarDB for PostgreSQL cluster with a read-write (RW) node, read-only (RO) nodes, and distributed storage (PolarStore) for component-level reliability.

Architectural advantages

Online horizontal scaling: Add nodes online to expand compute and storage capacity, overcoming single-machine bottlenecks for petabyte-scale data and high concurrency.
Flexible scaling methods: Shared storage architecture supports both scale-out (add nodes) and scale-up (increase node specifications).
High availability and low cost: PolarStore uses triplicate replicas with ParallelRaft for high data availability. You pay for only one replica, and storage is billed by actual usage with no manual capacity planning.
High performance: PolarFS-based I/O optimizations deliver high IOPS through parallel dirty page flushing, batch reads/writes, and table size caching.
Quick backup and recovery: Create backups in seconds. Multiple restoration methods are available, including point-in-time restore (PITR).

Distributed development patterns

When developing on PolarDB for PostgreSQL Distributed Edition, choose one of the following two data distribution patterns based on your business scenario:

Horizontal splitting: Distributes rows of a large table across multiple DNs by hashing a distribution column. Suited for large tables such as user or order tables. For optimal performance, include the distribution column in query filters.
- Distributed table: A logically unified table whose data is split across multiple DNs. For example, an application accesses the sensors_data table, but the data is actually stored in shards such as sensors_data_shard1 and sensors_data_shard2.
- Distribution column: The column whose hash value determines data placement across DNs. Example: sensor_id.
- Replicated table: A table fully copied to every DN. Used for small dimension tables, such as country or configuration tables, frequently joined with distributed tables, converting cross-node JOINs into local JOINs and significantly improving query performance.
- Distributed transaction: When an operation (explicit BEGIN ... COMMIT/ROLLBACK or implicit) modifies data across multiple DNs, the system automatically uses a distributed transaction to guarantee ACID compliance.
  Note
  Any modification to a replicated table triggers a distributed transaction.
- Primary/Follower CNs: All CNs process queries, but DDL operations (such as CREATE TABLE) run only on the primary CN. Changes sync automatically to all other nodes (CNs and DNs).
Vertical splitting: Places tables from different business modules on separate DNs for resource isolation. For example, high-frequency transaction tables and backend reporting tables can run on separate nodes. CNs abstract the underlying topology, so applications access all tables as if using a standalone database.

Development guide

Preparations:
1. Create a database account
  Go to the PolarDB console. On the Clusters page, click the target cluster ID. Choose Settings and Management > Accounts. Then, create a database account.
  Note
  Database accounts include Privileged Account and Standard Account, each with different permissions. Create an account based on your business needs.
2. Create a database
  Go to the PolarDB console. On the Clusters page, click the target cluster ID to go to the cluster details page. Choose Settings and Management > Databases, click Create Database, and create a distributed database. For Database Owner, select the Privileged Account that you created.
3. Configure a cluster whitelist
  Go to the PolarDB console. On the Clusters page, click the target cluster ID. Under Settings and Management > Cluster Whitelists, add an IP address whitelist or a security group.
  Note
  If you use an ECS instance to access PolarDB and the ECS instance is in the same VPC as the PolarDB cluster, add the private IP address of the ECS instance to a new IP whitelist group, or add the security group of the ECS instance to the cluster whitelist.
  If you use an ECS instance to access PolarDB but the ECS instance is not in the same VPC as the PolarDB cluster, add the public IP address of the ECS instance to a new IP whitelist group, or add the security group of the ECS instance to the cluster whitelist.
  If you access PolarDB from an on-premises environment, add the public IP address of your on-premises environment to a new IP whitelist group.
  To obtain the public IP address of your local environment on a Linux operating system, open a terminal and run the curl ifconfig.me command.
  Windows operating system: Open the command prompt, enter curl ip.me, and press Enter.
  macOS operating system: Open Terminal, enter curl ifconfig.me, and press Enter.
  If your on-premises network uses a proxy, the retrieved IP address may differ from your actual public IP. Add the IP address range 0.0.0.0/0 to the PolarDB cluster whitelist. After you connect to the cluster, run the SELECT pid,usename,datname,client_addr,state,query FROM pg_stat_activity WHERE state = 'active'; command to obtain your actual public IP address and add it to the cluster whitelist. Then, delete the 0.0.0.0/0 IP address range from the whitelist.
  The IP address range 0.0.0.0/0 allows access from all sources. Adding this range to the cluster whitelist poses a high security risk. Do not add this range to the whitelist unless it is necessary.
4. Obtain the database endpoint and port
  Log on to the PolarDB console. In the cluster list, click a cluster ID to open the Basic Information page. View the endpoint information in the Database Connections section.
  Note
  
  By default, a distributed PolarDB for PostgreSQL cluster has one Primary Endpoint. The default port is 5432.
  
  Use the Private or Public endpoint based on your access environment.
  
  To access the PolarDB cluster from an ECS instance in the same VPC as the PolarDB cluster, use the Private endpoint.
  
  To access the PolarDB cluster from your on-premises environment, use the Public endpoint. No public endpoint exists by default — click Apply to create one.
  
  A PolarDB cluster cannot achieve optimal performance when connected through a public endpoint.
  
  Virtual hosts and lightweight servers cannot connect to a PolarDB cluster through a Private endpoint.

Connect to the distributed cluster

Use DMS to connect to a cluster

Data Management (DMS) provides database administration, security auditing, and performance optimization. Manage your PolarDB cluster directly in DMS.

Log on to the PolarDB console. In the cluster list, click a cluster ID to open the Basic Information page. In the upper-right corner, click Log On To Database.
In the dialog box, enter the database account and password, then click Login.
After you log on, choose Database Instances > Instances Connected in the left-side navigation pane to manage the cluster.

Use a client to connect to a cluster

This procedure uses pgAdmin 4 v9.0 to connect to a PolarDB cluster.

Download and install the pgAdmin 4 client.
Open the pgAdmin 4 client, right-click Servers, and select Register > Server....

On the General tab, set the connection name. On the Connection tab, configure the connection parameters, then click Save.

Parameter	Description
Host name/address	The endpoint and port of the PolarDB cluster. To access the PolarDB cluster from an ECS instance, and the ECS instance is in the same VPC as the PolarDB cluster, specify the Private endpoint and port. To access the PolarDB cluster from your on-premises environment, specify the Public endpoint and port. The default port number is 5432.
Port
Username	The database account and password of the PolarDB cluster.
Password	The database account and password of the PolarDB cluster.

A successful connection displays the following page.

Note
postgres is the default system database. Do not perform any operations on this database.

Use psql to connect to a cluster

Download psql from PostgreSQL Downloads to connect to a PolarDB cluster. You can also use psql in the PolarDB-Tools to connect to a PolarDB cluster.

Note

The connection method is the same for Windows and Linux.
The official psql documentation covers detailed usage.

Syntax

psql -h <host> -p <port> -U <username> -d <dbname>

Parameter	Description
`host`	The cluster endpoint and port of the PolarDB cluster. To access the PolarDB cluster from an ECS instance, and the ECS instance is in the same VPC as the PolarDB cluster, specify the Private endpoint and port. To access the PolarDB cluster from your on-premises environment, specify the Public endpoint and port. The default port number is 5432
`port`
`username`	The database account of the PolarDB cluster.
`dbname`	The database name.

Example

psql -h pc-xxx.rwlb.rds.aliyuncs.com -p 5432 -U testusername -d postgres

Connect to a cluster in a programming language

Connecting to a PolarDB for PostgreSQL cluster is similar to connecting to a regular PostgreSQL database — just update the endpoint, port, account, and password.

Java

Connect to a PolarDB for PostgreSQL cluster using the PostgreSQL JDBC driver in a Maven-based Java project.

Add the PostgreSQL JDBC driver dependency to your pom.xml file. Sample code:

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.2.18</version>
</dependency>

Connect to the cluster. Replace the <HOST>, <PORT>, <USER>, <PASSWORD>, <DATABASE>, <YOUR_TABLE_NAME>, and <YOUR_TABLE_COLUMN_NAME> placeholders with the actual cluster connection parameters.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class PolarDBConnection {
    public static void main(String[] args) {
        // Database URL, username, and password.
        String url = "jdbc:postgresql://<HOST>:<PORT>/<DATABASE>";
        String user = "<USER>";
        String password = "<PASSWORD>";

        try {
            // Load the PostgreSQL JDBC driver.
            Class.forName("org.postgresql.Driver");
            
            // Establish the connection.
            Connection conn = DriverManager.getConnection(url, user, password);
            
            // Create a Statement object.
            Statement stmt = conn.createStatement();
            
            // Execute an SQL query.
            ResultSet rs = stmt.executeQuery("SELECT * FROM <YOUR_TABLE_NAME>");
            
            // Process the result set.
            while (rs.next()) {
                System.out.println(rs.getString("<YOUR_TABLE_COLUMN_NAME>"));
            }
            
            // Close resources.
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Python

Connect to a PolarDB for PostgreSQL cluster using the psycopg2 library in Python 3.

Install the psycopg2 library.
```
pip3 install psycopg2-binary
```

Connect to the cluster. Replace the <HOST>, <PORT>, <USER>, <PASSWORD>, <DATABASE>, and <YOUR_TABLE_NAME> placeholders with the actual cluster connection parameters.

import psycopg2

try:
    # Connection parameters
    conn = psycopg2.connect(
        host="<HOST>",  # The cluster endpoint.
        database="<DATABASE>",  # The database name.
        user="<USER>",  # The username.
        password="<PASSWORD>",  # The password.
        port="<PORT>"  # The port number.
    )

    # Create a cursor object.
    cursor = conn.cursor()

    # Execute a query.
    cursor.execute("SELECT * FROM <YOUR_TABLE_NAME>")

    # Get all results.
    records = cursor.fetchall()
    for record in records:
        print(record)
        
except Exception as e:
    print("Error:", e)
finally:
    # Close the connection.
    if 'cursor' in locals():
        cursor.close()
    if 'conn' in locals():
        conn.close()

Go

Connect to a PolarDB for PostgreSQL cluster using the database/sql package and the lib/pq driver in Go 1.23.0.

Install the lib/pq driver.
```
go get -u github.com/lib/pq
```

Connect to the cluster. Replace the <HOST>, <PORT>, <USER>, <PASSWORD>, <DATABASE>, and <YOUR_TABLE_NAME> placeholders with the actual cluster connection parameters.

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq" // Initialize the PostgreSQL driver.
)

func main() {
    // The connection string format.
    connStr := "user=<USER> password=<PASSWORD> dbname=<DATABASE> host=<HOST> port=<PORT> sslmode=disable"

    // Open a database connection.
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close() // Close the connection when the program exits.

    // Test the connection.
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("Connected to PostgreSQL!")

    // Execute a query.
    rows, err := db.Query("SELECT * FROM <YOUR_TABLE_NAME>")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
}

Create and manage distributed and replicated tables
DML operations on distributed tables
Additional topics: