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

How IVFFlat indexes work

IVFFlat divides vectors into clusters during index construction. At query time, the database searches only a subset of clusters rather than all vectors.

The two parameters have opposite effects on recall and throughput.

Test environment

The RDS instance and ECS instance must be in the same virtual private cloud (VPC) and vSwitch to avoid network-related variance in test results.

Prerequisites

Before you begin, ensure that you have:

• A privileged account named testuser and a database named testdb. See Create a database and an account

• The pgvector extension installed in testdb. The extension is named vector in the system. See Manage extensions

• A connection to the RDS instance. See Connect to an ApsaraDB RDS for PostgreSQL instance

Set up test data

1. Connect to testdb and create a helper function that generates random vectors of a given length:

   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;

2. Create a table for 1536-dimensional vectors:

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

3. Insert 100,000 rows of test data:

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

4. Create an IVFFlat index using cosine distance with 100 lists:

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

Run the benchmark

Use the internal endpoint of the RDS instance to eliminate network latency as a variable.

1. Create a SQL file named test.sql with the following query. The query generates a random 1536-dimensional vector and retrieves the most similar records from vtest using cosine distance:

   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. Run pgbench from the ECS instance. Make sure the PostgreSQL client is installed. See the pgbench documentation for reference.

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

   Parameter	Description
   -f ./test.sql	Path to the test SQL file. Replace with the actual path
   -c6	Number of concurrent client connections (6 in this test)
   -T60	Test duration in seconds (60 seconds in this test)
   -P5	Progress report interval in seconds (every 5 seconds)
   -U testuser	Database username. Replace with your username
   -h pgm-bp****.pg.rds.aliyuncs.com	Internal endpoint of the RDS instance
   -p 5432	Internal port of the RDS instance
   -d testdb	Target database

Tune lists and probes for production

The following chart shows the combined trade-off between recall and throughput as both parameters vary.

Use these formulas as a starting point based on the number of rows in your table:

Up to 1,000,000 rows:

lists  = row_count / 1,000
probes = lists / 10

Over 1,000,000 rows:

lists  = sqrt(row_count)
probes = sqrt(lists)

Related topics

• Manage extensions

• Connect to an ApsaraDB RDS for PostgreSQL instance