PolarDB:What is a distributed cluster

Technical architecture

PolarDB for PostgreSQL Distributed Editioncontains 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 itself a high-availabilityPolarDB for PostgreSQLcluster with read-write (RW) nodes, read-only (RO) nodes, and distributed storage (PolarStore), ensuring component-level reliability.

Architectural advantages

• Online horizontal scaling: Scale out computing and storage capabilities by adding nodes online, breaking through single-node bottlenecks to support petabyte-scale data and high-concurrency workloads.

• Flexible scaling methods: Nodes use a shared storage architecture that supports scaling by adding nodes (Scale Out) or upgrading node specifications (Scale Up).

• High availability and low cost: The three-replica distributed storage (PolarStore) based on the ParallelRaft replication protocol ensures high data availability, while you are billed only for a single replica. Storage space is billed based on actual usage, eliminating the need for manual capacity adjustments.

• High performance: Deep IO optimizations based on the distributed file system (Polar File System, or PolarFS), such as parallel dirty page flushing, batch read/write operations, and table size caching, deliver ultra-high IOPS.

• Quick backup and recovery: Supports creating backups within seconds and provides multiple data recovery methods, including point-in-time recovery (PITR).

Distributed development patterns

When developing onPolarDB for PostgreSQL Distributed Edition, the key is to plan how data is distributed. You can choose one of the following two patterns based on your business scenarios:

• Horizontal splitting: Rows of a single large table are distributed across multiple DNs based on the hash values of a specific column (distribution column). This pattern is suitable for resolving performance issues caused by excessively large tables, such as user tables and order tables. For optimal performance, queries should include the distribution column as a filter condition whenever possible.

  • Distributed table: Logically a complete table, but its data is physically split and stored across multiple DNs. For example, an application accesses thesensors_datatable, while the data is actually stored in shards such assensors_data_shard1andsensors_data_shard2.

  • Distribution column: The column used to compute hash values to determine data distribution when a distributed table is created, such assensor_id.

  • Replicated table: A special table that stores a complete copy of all its data on each DN. It is typically used to store small dimension tables (such as country and configuration tables) that need to be frequently joined with large distributed tables. This optimizes cross-node JOINs into local JOINs, significantly improving query performance.

  • Distributed transaction: When a single operation (an explicitBEGIN ... COMMIT/ROLLBACKor an implicit transaction) needs to modify data distributed across multiple DNs, the system automatically enables a distributed transaction to ensure data consistency (ACID).

    Note

    Any modification to a replicated table triggers a distributed transaction.

  • Primary/Follower CNs: All CNs can process query requests. However, to ensure metadata consistency, DDL operations (such asCREATE TABLE) can only be executed on the primary CN, and the changes are automatically synchronized to all other nodes (CNs and DNs).

• Vertical splitting: Tables of different business modules are deployed on different DNs. This pattern is suitable for isolating resources by business, such as storing tables of high-frequency transaction workloads and back-end reporting workloads on different nodes to avoid mutual interference. This pattern has minimal impact on applications because CNs shield the underlying details from the upper layer, allowing applications to access all tables as if they were accessing a standalone database.

Development guide

1. Preparations:

   1. Create a database account

      Go to the PolarDB console. On the Clusters page, click the ID of the target cluster to go to the cluster details page. In the left-side navigation pane, choose Settings and Management > Accounts to create a database account.

      Note

      Database accounts are classified into Privileged Account and Standard Account. These two types of accounts have different permissions. You can create a database account based on your business requirements.

   2. Create a database

      Go to the PolarDB console. On the Clusters page, click the ID of the target cluster to go to the cluster details page. In the left-side navigation pane, choose Settings and Management > Databases. Click Create Database to create a distributed database. For Database Owner, select the Privileged Account that you created.

      Note

      Do not execute the DROP DATABASE or RENAME statement on a database of PolarDB for PostgreSQL Distributed Edition from the psql command line.

   3. Configure a cluster whitelist

      Go to the PolarDB console. On the Clusters page, click the ID of the target cluster to go to the cluster details page. In the left-side navigation pane, choose Settings and Management > Cluster Whitelists, add an IP address whitelist or a security group.

      Note

      • If you access PolarDB from an ECS instance and the ECS instance and PolarDB are in the same VPC, you can add the private IP address of the ECS instance to a new IP address whitelist group, or add the security group of the ECS instance to the cluster whitelist.

      • If you access PolarDB from an ECS instance but the ECS instance and PolarDB are not in the same VPC, you can add the public IP address of the ECS instance to a new IP address 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 address whitelist group.

        To obtain the public IP address of your on-premises environment, perform the following steps: For Linux, open a terminal and run the curl ifconfig.me command.

        • For Windows, open Command Prompt and run the curl ip.me command.

        • For macOS, open a terminal and run the curl ifconfig.me command.

        If your local network uses a proxy or similar configurations, the IP address obtained by using the preceding methods may not be your actual public IP address. You can add the CIDR block 0.0.0.0/0 to the whitelist of the PolarDB cluster. 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 the actual public IP address, and then add it to the cluster whitelist. Then, remove the CIDR block 0.0.0.0/0 from the whitelist.

        

      • The CIDR block 0.0.0.0/0 allows access from all sources. Adding it to the cluster whitelist poses significant security risks. Do not add it to the whitelist unless necessary.

   4. Obtain the database endpoint and port

      After you create a PolarDB for PostgreSQL distributed cluster, the system creates a default primary endpoint that is associated only with the primary CN and a distributed proxy endpoint that is associated with all CNs. The distributed proxy endpoint performs load balancing across the associated CNs based on the number of connections, providing a unified access point for your business. You can also create custom endpoints and specify the associated CNs as needed to achieve more flexible traffic distribution and business isolation.

      Procedure

      Go to the PolarDB console. On the Clusters page, click the ID of the target cluster to go to the cluster details page. In the Database Connections section, obtain the database endpoint.

      Note

      • A PolarDB for PostgreSQL Distributed Edition cluster contains the Primary Endpoint and a distributed proxy endpoint that is associated with all CNs by default, and the default port number is 5432.

      • Select the Private Endpoint or Public Endpoint based on your access environment.

        • If you access PolarDB from an ECS instance and the ECS instance and PolarDB are in the same VPC, select the Private Endpoint.

        • If you access PolarDB from an on-premises environment, select the Public Endpoint. Click Apply to apply for a public endpoint.

      • The Public Endpoint is accessible over the Internet. Accessing through the Public Endpoint cannot achieve the optimal performance of the PolarDB cluster.

      • Cloud Web Hosting and lightweight application servers are not supported for connecting to the PolarDB cluster by using the Private Endpoint.

2. Connect to a distributed cluster

   Use DMS to connect to a cluster

   DMS is a graphical data management tool provided by Alibaba Cloud. It is a data management service that integrates data management, schema management, user authorization, security auditing, data trends, data tracking, BI charts, performance and optimization, and server management. You can manage your PolarDB cluster directly in DMS without the need for additional tools.

   1. Go to the PolarDB console. On the Clusters page, click the ID of the target cluster to go to the cluster details page. In the upper-right corner of the page, click Log on to Database.

   2. In the dialog box that appears, enter the database account and database password created in the cluster, and then click Login.

   3. After you log on, you can view the logged-on PolarDB cluster in the Database Instances > Connected Instances list in the left-side navigation pane and perform management operations.

   Use a client to connect to a cluster

   You can use any common client to connect to a PolarDB cluster. This example uses pgAdmin 4 v9.0. The procedure for other clients is similar.

   1. Download and install the pgAdmin 4 client.

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

   3. On the General tab, set the connection name. On the Connection tab, set the cluster connection information, and then click Save.

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

   4. Check the connection result. If the connection information is correct, the following page appears, indicating that the connection is successful.

   Use a CLI to connect to a cluster

   Go to the PostgreSQL official website to download and use the psql tool to connect to the PolarDB database cluster. You can also use the PolarDB provided PolarDB-Toolspsql tool in the toolkit to connect to the PolarDB database cluster.

   Note

   • The psql connection method is the same for Windows and Linux.

   • For more information about psql usage, see PostgreSQL official psql documentation.

   Syntax

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

   Parameter	Description
   host	The database endpoint and port of the PolarDB cluster. If you access PolarDB from an ECS instance and the ECS instance and PolarDB are in the same VPC, select the private endpoint and port. If you access PolarDB from an on-premises environment, select the public endpoint and port. The default port number is 5432.
   port
   username	The database account of the PolarDB cluster.
   dbname	The database to manage and maintain.

   Example

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

   Use an application to connect to a cluster

   Connecting to a PolarDB for PostgreSQL Distributed Edition cluster is the same as connecting to any other PostgreSQL database. You only need to replace the database endpoint, port, account, and password. The following examples show how to access a PolarDB database by using applications in various programming languages:

   Java

   This example uses a Maven project to connect to a PostgreSQL JDBC driver to a PolarDB for PostgreSQL Distributed Edition cluster.

   1. First, add the PostgreSQL JDBC driver dependency to the pom.xml file. Sample code:

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

   2. Connect to the cluster. Replace the <HOST>, <PORT>, <USER>, <PASSWORD>, <DATABASE>, <YOUR_TABLE_NAME>, and <YOUR_TABLE_COLUMN_NAME> 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, password
              String url = "jdbc:postgresql://<HOST>:<PORT>/<DATABASE>";
              String user = "<USER>";
              String password = "<PASSWORD>";
      
              try {
                  // Load the JDBC driver
                  Class.forName("org.postgresql.Driver");
                  
                  // Establish a 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

   This example uses Python 3 with the psycopg2 library to connect to a PolarDB for PostgreSQL Distributed Edition cluster.

   1. First, install the psycopg2 library. If you have not installed it, run the following command:

      pip3 install psycopg2-binary

   2. Connect to the cluster. Replace the <HOST>, <PORT>, <USER>, <PASSWORD>, <DATABASE>, and <YOUR_TABLE_NAME> parameters.

      import psycopg2
      
      try:
          # Connection parameters
          conn = psycopg2.connect(
              host="<HOST>",  # Database host address
              database="<DATABASE>",  # Database name
              user="<USER>",  # Username
              password="<PASSWORD>",  # Password
              port="<PORT>"  # Port
          )
      
          # Create a cursor object
          cursor = conn.cursor()
      
          # Execute a query
          cursor.execute("SELECT * FROM <YOUR_TABLE_NAME>")
      
          # Fetch 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

   This example uses Go 1.23.0 with the database/sql package and the lib/pq driver to connect to a PolarDB for PostgreSQL Distributed Edition cluster.

   1. First, install the lib/pq driver. Run the following command:

      go get -u github.com/lib/pq

   2. Connect to the cluster. Replace the <HOST>, <PORT>, <USER>, <PASSWORD>, <DATABASE>, and <YOUR_TABLE_NAME> parameters.

      package main
      
      import (
          "database/sql"
          "fmt"
          "log"
      
          _ "github.com/lib/pq" // Initialize the driver
      )
      
      func main() {
          // 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 before the program exits
      
          // Test whether the connection is successful
          err = db.Ping()
          if err != nil {
              log.Fatal(err)
          }
          fmt.Println("Connected to PostgreSQL!")
      
          // Execute a sample query
          rows, err := db.Query("SELECT * FROM <YOUR_TABLE_NAME>")
          if err != nil {
              log.Fatal(err)
          }
          defer rows.Close()
      }
      

   Note

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

3. Create and manage distributed tables

4. DML operations on distributed tables

5. Additional topics:

   • Optimize queries on distributed tables

   • Usage and Limitations of Distributed Sequence

   • Tenant isolation

   • Multi-tenant management

   • Distributed columnar index

   • Configure CDC to synchronize data changes