ApsaraDB RDS:Use the pgvector extension to test performance based on IVF indexes

Test environment

An RDS instance and an Elastic Compute Service (ECS) instance must reside in the same virtual private cloud (VPC) and belong to the same vSwitch to prevent errors caused by network fluctuations.

Preparations

1. Create a privileged account named testuser and a database named testdb. For more information, see Create a database and an account.

2. Install the pgvector extension in the testdb database. The pgvector extension is named vector in the system. For more information, see Manage extensions.

3. Connect to the RDS instance and generate random vectors with a fixed length in the testdb database as the test data. For more information, see Connect to an ApsaraDB RDS for PostgreSQL instance.

   CREATE OR REPLACE FUNCTION random_array(dim integer) 
       RETURNS DOUBLE PRECISION[] 
   AS $$ 
       SELECT array_agg(random()) 
       FROM generate_series(1, dim); 
   $$ 
   LANGUAGE SQL 
   VOLATILE 
   COST 1;

4. Create a table to store 1536-dimensional vectors.

   CREATE TABLE vtest(id BIGINT, v VECTOR(1536));

5. Insert data into the table.

   INSERT INTO vtest SELECT i, random_array(1536)::VECTOR(1536) FROM generate_series(1, 100000) AS i;

6. Create an index.

   CREATE INDEX ON vtest USING ivfflat(v vector_cosine_ops) WITH(lists = 100);

Test procedure

To prevent the impacts on the test data caused by factors such as network latency, we recommend that you use the internal endpoint of the RDS instance. In this example, the test is performed on an ECS instance that resides in the same region and VPC as the RDS instance.

1. Use a random vector to calculate the similarities between the data in the vtest table and obtain the most similar 50 records.

   You must create an SQL file and write the following content to the file for subsequent stress testing:

   WITH tmp AS (
       SELECT random_array(1536)::VECTOR(1536) AS vec
   )
   SELECT id
   FROM vtest
   ORDER BY v <=> (SELECT vec FROM tmp)
   LIMIT FLOOR(RANDOM() * 50);

2. Use pgbench to perform stress testing.

   pgbench is a program that runs benchmark tests in PostgreSQL. You must run the following commands in the CLI. In this case, make sure that the PostgreSQL client is installed. In this example, PostgreSQL 15.1 is used. For more information, see PostgreSQL official documentation.

   pgbench -f ./test.sql -c6 -T60 -P5 -U testuser -h pgm-bp****.pg.rds.aliyuncs.com -p 5432 -d testdb

   The following table describes the parameters.

   Parameter/Value	Description
   -f ./test.sql	The path and file name of the test script file. ./test.sql is an example. You must modify the parameter based on your business requirements.
   -c6	The number of concurrent client connections. -c specifies the number of concurrent client connections. In this example, 6 indicates that six concurrent client connections is used for the test.
   -T60	The testing time. -T specifies the time that is required to perform the test. In this example, 60 specifies that the test lasts for 60 seconds.
   -P5	The script parameter. In this example, the progress report is displayed every 5 seconds.
   -U testuser	The username of the database. You must replace testuser with your database username.
   -h pgm-bp****.pg.rds.aliyuncs.com	The internal endpoint of the RDS instance.
   -p 5432	The internal port of the RDS instance.
   -d testdb	The database to which you want to connect. In this example, testdb is connected.

Test results

Conclusions

• The value of the lists parameter has a slight impact on the storage that is occupied by indexes. The occupied storage is affected by the data volume in the table.

• The values of the lists and probes parameters have opposite impacts on the query efficiency and the recall rate. You must specify appropriate values for the parameters to make a trade-off between the query efficiency and the recall rate.

  

  We recommend that you configure the lists and probes parameters based on the number of data rows in the table. The following section provides sample configuration settings.

  • If the number of data rows in the table is less than or equal to 1 million, you can calculate the values of the lists and probes parameters by using the following formulas: lists = Number of data rows/1,000 and probes = Value of the lists parameter/10

  • If the number of data rows in the table is greater than 1 million, you can calculate the values of the lists and probes parameters by using the following formulas: lists = sqrt(Number of data rows) and probes = sqrt(Value of the lists parameter).

    Note

    sqrt indicates the square root function.