Instances with vector engine optimization enabled

Free trial

Alibaba Cloud offers a free trial for Elastic Storage Mode instances. If you are a new user of AnalyticDB for PostgreSQL, you can apply on the Alibaba Cloud Free Trial page. If you are not eligible for a free trial, create an instance by following the procedure in this topic.

Create an instance

1. Log on to the AnalyticDB for PostgreSQL console.

2. In the upper-right corner of the page, click Create Instance to go to the purchase page.

3. On the purchase page, configure the core parameters for your instance. You can leave the other parameters at their default values. For more information about the parameters, see Create an instance.

   Parameter	Description	Example
   Region and Zone	Subscription: A prepaid billing method. You pay when you create the instance. It is cost-effective for long-term workloads. Longer subscription periods offer larger discounts. Pay-as-you-go: A postpaid billing method where you are billed hourly. It is ideal for short-term workloads. You can release the instance at any time to save costs.	Pay-As-You-Go
   Product Type	Specifies the geographic location of the instance. You cannot change the region after the instance is created. We recommend that you create the instance in the same region as the ECS instance you want to connect to. This allows them to communicate over an internal network.	China (Hangzhou): Zone J
   Instance resource type	Elastic Storage Mode: Supports independent disk scaling and smooth online scaling. ServerlessPro: You specify only the required compute resources without pre-provisioning storage resources.	Storage-elastic Mode
   Engine Version	We recommend that you select 7.0 Standard Edition for richer functionality. 6.0 Standard Edition is also supported.	7.0 Standard Edition
   Instance Edition	High-performance (Basic Edition): Suitable for most business analytics workloads. High-availability Edition: Recommended for core enterprise workloads.	High-performance (Basic Edition)
   Vector engine optimization	Select Enable.	Enable
   VPC	Select the ID of the VPC. To connect to an ECS instance in the same region over an internal network, select the same VPC as the ECS instance. You can select an existing VPC or create a VPC and VSwitch as prompted on the page.	vpc-xxxx
   vSwitch	Select a VSwitch within the VPC. If no VSwitches are listed, the current availability zone has no VSwitch resources. You can switch to another availability zone or create a VSwitch in the current zone as prompted.	vsw-xxxx

4. Click Buy Now. On the Confirm Order page, verify the instance information and click Create Now.

5. After completing the payment, click Console to return to the instance list and view the new instance.

   Note

   An AnalyticDB for PostgreSQL instance takes some time to initialize. You can proceed with subsequent operations only after the instance status changes to Running.

Create an initial account

1. In the left-side navigation pane, click Account Management.

2. Click Create Account. In the Create Account window, enter an account name and set a password. Then, click OK.

   Parameter	Description
   Account	The initial account name must meet the following requirements: Must contain only lowercase letters, digits, and underscores (_). Must start with a lowercase letter and end with a lowercase letter or digit. Cannot start with gp. Must be 2 to 16 characters long.
   New Password and Confirm Password	The password must meet the following requirements: Must contain characters from at least three of the following categories: uppercase letters, lowercase letters, digits, and special characters. The supported special characters are !@#$%^&*()_+-=. Must be 8 to 32 characters long. Important For data security, change your password regularly and do not reuse previous passwords.

Configure an IP whitelist

1. In the left navigation bar of the instance details page, click Security Controls.

2. Click Create Whitelist and configure the following information:

   Parameter	Description
   Whitelist Name	The name of the new whitelist group. The name must meet the following requirements: It must consist of lowercase letters, digits, or underscores (_). It must start with a lowercase letter and end with a lowercase letter or a digit. The name must be 2 to 32 characters in length.
   IP Addresses	The IP addresses to add to the whitelist. Note the following: Separate multiple IP addresses with commas (,). Duplicate IP addresses are not allowed. You can add a maximum of 999 IP addresses. You can use IP addresses in the 10.23.12.24 format and CIDR blocks in the 10.23.12.24/24 format. CIDR stands for Classless Inter-Domain Routing. In CIDR mode, /24 specifies the length of the address prefix. The prefix length can be an integer from 1 to 32. Setting the prefix length to 0, such as in 0.0.0.0/0 or 127.0.0.1/0, allows access from any IP address. This poses a high security risk. Proceed with caution. 127.0.0.1 denies access from all external IP addresses.

3. Click OK.

Connect to the database

This section uses the psql tool as an example to demonstrate how to connect to the database. For information about connecting with other tools, see Client connections.

1. Install psql.

   1. Download the psql tool.

      wget https://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/181125/cn_zh/1598426198114/adbpg_client_package.el7.x86_64.tar.gz

      The preceding download command is applicable only if the client's operating system is RHEL 7 or CentOS 7. For psql versions for other operating systems, see Client connections.

   2. Decompress the psql tool package.

      tar -xzvf adbpg_client_package.el7.x86_64.tar.gz

   3. Navigate to the directory where the psql tool is located.

      cd adbpg_client_package/bin

2. Log on to the database.

   ./psql -h  -p 5432 -d  -U 

   To obtain the connection endpoint:

   • If your client and the AnalyticDB for PostgreSQL instance are deployed on an ECS instance in the same region and with the same network type, you can use a private IP address to connect. Log on to the AnalyticDB for PostgreSQL console. Click the ID of the target instance to go to the instance details page. In the left navigation bar, click Basic Information and find the Database Connection Information section to view the Internal Endpoint.

   • If the client and the AnalyticDB for PostgreSQL instance are deployed in different regions, on ECS instances with different network types, or on systems outside of Alibaba Cloud, you need to apply for a public IP address and use it to connect. For more information, see Manage public IP addresses.

   Example:

   psql -h gp-bp13zq652yy4p****-master.gpdb.rds.aliyuncs.com -p 5432 -d postgres -U testuser

   When you enter the password and press Enter, the postgres=> prompt appears, indicating a successful connection.

Import vector data using SQL

1. Verify that the FastANN vector retrieval extension is installed.

   Run the \dx fastann command to check for the extension. If the command returns information about FastANN, it is installed. Otherwise, submit a ticket to have technical support install it.

2. Download the sample data.

   To facilitate testing, AnalyticDB for PostgreSQL provides test data that you can download from vector_sample_data.csv.

   The sample data has the following structure:

   Column	Type	Description
   id	bigint	The ID.
   market_time	timestamp	The time the vehicle was released to the market.
   color	varchar(10)	The color of the vehicle.
   price	int	The price of the vehicle.
   feature	float4[]	The feature vector of the vehicle image.

   On a Linux server, run the following command to download the sample data:

   wget https://help-static-aliyun-doc.aliyuncs.com/file-manage-files/zh-CN/20230606/uzkx/vector_sample_data.csv

3. Import the data.

   1. Create and switch to a new database.

      -- Create a database named testdb.
      CREATE DATABASE testdb;
      
      -- Switch to the new database.
      \c testdb

   2. Create a table with a vector column based on the sample data.

      CREATE SCHEMA IF NOT EXISTS vector_test;
      CREATE TABLE IF NOT EXISTS vector_test.car_info
      (
        id bigint NOT NULL,
        market_time timestamp,
        color varchar(10),
        price int,
        feature float4[],
        PRIMARY KEY(id)
      ) 
      USING heap -- The heap engine is used by default when vector engine optimization is enabled.
      DISTRIBUTED BY(id);

   3. Create indexes.

      In this tutorial, you create structured indexes for fields such as market time, color, and price, and a vector index for the feature vector from the vehicle image.

      -- Change the storage format of the vector column to PLAIN.
      ALTER TABLE vector_test.car_info ALTER COLUMN feature SET STORAGE PLAIN;
      
      -- Create structured indexes.
      CREATE INDEX ON vector_test.car_info(market_time);
      CREATE INDEX ON vector_test.car_info(color);
      CREATE INDEX ON vector_test.car_info(price);
      
      -- Create a vector index.
      CREATE INDEX ON vector_test.car_info USING ann(feature) WITH (dim='10', pq_enable='0');

   4. Load the sample data into the table.

      \COPY vector_test.car_info FROM '/DATA_PATH/vector_sample_data.csv';

      Replace /DATA_PATH/vector_sample_data.csv with the actual path to your downloaded sample data file. For example, if you downloaded the sample data to the /home directory, the path should be /home/vector_sample_data.csv.

      A successful import returns COPY 10000.

Vector analysis

This section provides examples of how to perform vector analysis to calculate the squared Euclidean distance, dot product distance, and cosine similarity.

SELECT id, l2_squared_distance(feature, array[0.495181661387,0.108697291209,0.181728549067,0.109680543346,0.19713082404,0.0197809514512,0.534227452778,0.442411970815,0.409909873031,0.0975687394505]::float4[]) AS distance 
  FROM vector_test.car_info 
  ORDER BY feature <-> array[0.495181661387,0.108697291209,0.181728549067,0.109680543346,0.19713082404,0.0197809514512,0.534227452778,0.442411970815,0.409909873031,0.0975687394505]::float4[] 
  LIMIT 10;

 id | distance
------+--------------------
    2 | 0
 1331 | 0.0677967891097069
 1543 | 0.079616591334343
 5606 | 0.0892329216003418
 6423 | 0.0894578248262405
 1667 | 0.0903968289494514
 8215 | 0.0936210229992867
 7801 | 0.0952572822570801
 2581 | 0.0965127795934677
 2645 | 0.0987173467874527
(10 rows)

SELECT id, dp_distance(feature, array[0.495181661387,0.108697291209,0.181728549067,0.109680543346,0.19713082404,0.0197809514512,0.534227452778,0.442411970815,0.409909873031,0.0975687394505]::float4[]) AS similarity 
  FROM vector_test.car_info 
  ORDER BY feature <-> array[0.495181661387,0.108697291209,0.181728549067,0.109680543346,0.19713082404,0.0197809514512,0.534227452778,0.442411970815,0.409909873031,0.0975687394505]::float4[] 
  LIMIT 10;

 id | similarity
------+-------------------
    2 | 1
 1331 | 0.966101586818695
 1543 | 0.960191607475281
 5606 | 0.955383539199829
 6423 | 0.955271065235138
 1667 | 0.954801559448242
 8215 | 0.953189492225647
 7801 | 0.95237135887146
 2581 | 0.951743602752686
 2645 | 0.950641334056854
(10 rows)

SELECT id, dp_distance(feature, array[0.495181661387,0.108697291209,0.181728549067,0.109680543346,0.19713082404,0.0197809514512,0.534227452778,0.442411970815,0.409909873031,0.0975687394505]::float4[]) AS similarity 
  FROM vector_test.car_info 
  WHERE market_time >= '2020-10-30 00:00:00' 
  AND market_time < '2021-01-01 00:00:00' 
  AND color in ('red', 'white', 'blue') 
  AND price < 100 
  ORDER BY feature <-> array[0.495181661387,0.108697291209,0.181728549067,0.109680543346,0.19713082404,0.0197809514512,0.534227452778,0.442411970815,0.409909873031,0.0975687394505]::float4[] 
  LIMIT 10;

 id | similarity
------+-------------------
 7645 | 0.922723233699799
 8956 | 0.920517802238464
 8219 | 0.91210675239563
 8503 | 0.895939946174622
 5113 | 0.895431876182556
 7680 | 0.893448948860168
 8433 | 0.893425941467285
 3604 | 0.89293098449707
 3945 | 0.891274154186249
 7153 | 0.891128540039062
(10 rows)