Common performance optimization and monitoring operations - PolarDB

This topic describes the common performance optimization and monitoring operations for vector databases.

Loading

COPY items (embedding) FROM STDIN WITH (FORMAT BINARY);

To ensure optimal performance, add indexes after loading the initial data.

Indexing

Optimize HNSW index creation and reduce index building time.
In production environments, create indexes concurrently to prevent write operations from being blocked.
```
CREATE INDEX CONCURRENTLY ...
```

Querying

Use EXPLAIN ANALYZE to debug performance.

EXPLAIN ANALYZE SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

Exact search

To accelerate queries that cannot use indexes, modify the max_parallel_workers_per_gather parameter to control the parallel query capability in the execution plans. Appropriately increasing the parameter can significantly improve query performance when aggregating and sorting large amounts of data.
```
SET max_parallel_workers_per_gather = 4;
```
If vectors are normalized to length 1, use inner product for best performance.
```
SELECT * FROM items ORDER BY embedding <#> '[3,1,2]' LIMIT 5;
```

Approximate search

To accelerate queries that use IVFFlat indexes, increase the number of inverted lists (at the cost of reduced recall).

CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 1000);

Vacuuming

Vacuuming HNSW indexes may take a long time. To accelerate th process, rebuild the indexes first.

REINDEX INDEX CONCURRENTLY index_name;
VACUUM table_name;

Monitoring

Create the pg_stat_statements extension to monitor query performance.

Note

Before creating the pg_stat_statements extension, make sure that you add it to shared_preload_libraries in the PolarDB console. You can add it by modifying the shared_preload_libraries parameter. Modifying the shared_preload_libraries parameter causes the cluster to restart. Carefully plan and execute to minimize disruption to your business operations.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Find the top 20 time-consuming queries.

Note

In PolarDB for PostgreSQL 11, replace total_plan_time + total_exec_time with total_time in the following statement.

SELECT query, calls, ROUND((total_plan_time + total_exec_time) / calls) AS avg_time_ms,
    ROUND((total_plan_time + total_exec_time) / 60000) AS total_time_min
    FROM pg_stat_statements ORDER BY total_plan_time + total_exec_time DESC LIMIT 20;

Monitor recall by comparing the approximate and exact search results.

BEGIN;
SET LOCAL enable_indexscan = off; -- Use exact search.
SELECT ...
COMMIT;