PostgreSQL【应用 01】使用Vector插件实现向量相似度查询（Docker部署的PostgreSQL安装pgvector插件说明）和Milvus向量库对比

@Data
@Builder
public class MilvusRes {
    public float score;
    public String imagePath;
}

@Slf4j
@Component
public class MilvusUtil {
    public MilvusServiceClient milvusServiceClient;
    @PostConstruct
    private void connectToServer() {
        milvusServiceClient = new MilvusServiceClient(
                ConnectParam.newBuilder()
                        .withHost("your service host")
                        .withPort(19530)
                        .build());
        // 加载数据
        LoadCollectionParam faceSearchNewLoad = LoadCollectionParam.newBuilder().withCollectionName("CollectionName").build();
        R<RpcStatus> rpcStatusR = milvusServiceClient.loadCollection(faceSearchNewLoad);
        log.info("Milvus LoadCollection [{}]", rpcStatusR.getStatus());
    }
    public int insertDataToMilvus(String id, String path, float[] feature) {
        List<InsertParam.Field> fields = new ArrayList<>();
        List<Float> featureList = new ArrayList<>(feature.length);
        for (float v : feature) {
            featureList.add(v);
        }
        fields.add(new InsertParam.Field("field1", Collections.singletonList(id)));
        fields.add(new InsertParam.Field("field2", Collections.singletonList(path)));
        fields.add(new InsertParam.Field("field3", Collections.singletonList(featureList)));
        InsertParam insertParam = InsertParam.newBuilder()
                .withCollectionName("CollectionName")
                //.withPartitionName("novel")
                .withFields(fields)
                .build();
        R<MutationResult> insert = milvusServiceClient.insert(insertParam);
        return insert.getStatus();
    }
    public List<MilvusRes> searchImageByFeature(float[] feature) {
        List<Float> featureList = new ArrayList<>(feature.length);
        for (float v : feature) {
            featureList.add(v);
        }
        List<String> queryOutputFields = Arrays.asList("field");
        SearchParam faceSearch = SearchParam.newBuilder()
                .withCollectionName("CollectionName")
                .withMetricType(MetricType.IP)
                .withVectorFieldName("VectorFieldName")
                .withVectors(Collections.singletonList(featureList))
                .withOutFields(queryOutputFields)
                .withTopK(10).build();
        // 执行搜索
        long l = System.currentTimeMillis();
        R<SearchResults> respSearch = milvusServiceClient.search(faceSearch);
        log.info("MilvusServiceClient.search cost [{}]", System.currentTimeMillis() - l);
        // 解析结果数据
        SearchResultData results = respSearch.getData().getResults();
        int scoresCount = results.getScoresCount();
        SearchResultsWrapper wrapperSearch = new SearchResultsWrapper(results);
        List<MilvusRes> milvusResList = new ArrayList<>();
        for (int i = 0; i < scoresCount; i++) {
            float score = wrapperSearch.getIDScore(0).get(i).getScore();
            Object imagePath = wrapperSearch.getFieldData("field1", 0).get(i);
            MilvusRes milvusRes = MilvusRes.builder().score(score).imagePath(imagePath.toString()).build();
            milvusResList.add(milvusRes);
        }
        return milvusResList;
    }
}

MilvusServiceClient.search cost [24]

# 进入容器
docker exec -it CONTAINER ID /bin/bash
# 1.更新 apt-get 
apt-get update
# 未更新直接安装会报错
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
E: Unable to locate package postgresql-12-postgis-3
E: Unable to locate package postgresql-12-postgis-3-dbgsym
E: Unable to locate package postgresql-12-postgis-3-scripts
# 2.安装插件
apt-get install postgresql-12-pgvector

-- 添加 vector 扩展
CREATE EXTENSION vector;
-- 查询可使用的扩展
SELECT * FROM pg_available_extensions;
-- 创建表
CREATE TABLE "public"."test" ( 
  "field1" VARCHAR ( 64 ), 
  "field2" VARCHAR ( 128 ), 
  "field3" vector ( 1024 ), 
  CONSTRAINT "test_pkey" PRIMARY KEY ( "field1" ) 
);

-- 创建索引
CREATE INDEX ON test USING ivfflat ( field3);
CREATE INDEX ON test USING ivfflat ( field3 vector_ip_ops) WITH (lists = 50);
CREATE INDEX ON test USING ivfflat ( field3 vector_ip_ops) WITH (lists = 500);
CREATE INDEX ON test USING ivfflat ( field3 vector_ip_ops) WITH (lists = 1024);

<select id="queryId" resultType="map">
        SELECT
        field1,
        field2,
        field3 <![CDATA[ <#> ]]> CAST ( #{featrue}  AS vector ) AS "score"
        FROM test
        ORDER BY field1 <![CDATA[ <#> ]]> CAST ( #{featrue}  AS vector )
        LIMIT 10;
    </select>

PostgreSQL.vector.search cost [30]