-- 創(chuàng)建384維向量列（適合BERT-base模型）
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding vector(384) NOT NULL,
    metadata JSONB
);

-- 余弦相似度查詢（值越接近0越相似）
SELECT id, content, embedding <=> '[0.1, 0.2, ..., 0.9]' AS similarity
FROM documents
ORDER BY similarity
LIMIT 5;

CREATE INDEX idx_hnsw_embedding ON documents 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

CREATE INDEX idx_ivfflat_embedding ON documents
USING ivfflat (embedding vector_l2_ops)
WITH (lists = 100); -- 聚類數(shù)量，建議設為數(shù)據(jù)量的平方根

# 啟動包含pgvector的PostgreSQL 16容器
docker run -d -p 5432:5432 \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=vectordb \
  --name pgvector-db \
  pgvector/pgvector:pg16

-- 在目標數(shù)據(jù)庫中啟用擴展
CREATE EXTENSION vector;

-- 驗證安裝
SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';
-- 應返回 (vector, 0.8.0)

# 增加共享內存（至少25%系統(tǒng)內存）
shared_buffers = 8GB
# 索引構建內存（建議4-16GB）
maintenance_work_mem = 8GB
# 并行索引構建（pg13+）
max_parallel_maintenance_workers = 4
# 連接池大小
max_connections = 100

-- 會話級臨時調整
SET maintenance_work_mem = '8GB';

<dependencies>
    <!-- Spring Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <!-- PostgreSQL驅動 -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.7.2</version>
    </dependency>
    <!-- pgvector Java客戶端 -->
    <dependency>
        <groupId>com.pgvector</groupId>
        <artifactId>pgvector</artifactId>
        <version>0.1.6</version>
    </dependency>
    <!-- Spring AI向量存儲支持 -->
    <dependency>
        <groupId>org.springframework.ai</groupId>
        <artifactId>spring-ai-starter-vector-store-pgvector</artifactId>
        <version>1.0.0</version>
    </dependency>
</dependencies>

public class VectorTypeHandler extends BaseTypeHandler<float[]> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, 
                                  float[] parameter, JdbcType jdbcType) throws SQLException {
        ps.setObject(i, new PGvector(parameter));
    }

    @Override
    public float[] getNullableResult(ResultSet rs, String columnName) throws SQLException {
        PGvector vector = (PGvector) rs.getObject(columnName);
        return vector != null ? vector.toArray() : null;
    }

    // 實現(xiàn)其他必要方法...
}

mybatis:
  type-handlers-package: com.example.handler

@Entity
@Table(name = "product_embeddings")
public class ProductEmbedding {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(nullable = false)
    private String productName;
    
    @Column(columnDefinition = "vector(1536)")
    private float[] embedding; // OpenAI嵌入向量（1536維）
    
    @Column(columnDefinition = "jsonb")
    private Map<String, Object> metadata;
    
    // Getters and setters
}

public interface ProductRepository extends JpaRepository<ProductEmbedding, Long> {

    // 原生SQL實現(xiàn)余弦相似度查詢
    @Query(value = "SELECT * FROM product_embeddings " +
                   "ORDER BY embedding <=> CAST(:embedding AS vector) " +
                   "LIMIT :limit", nativeQuery = true)
    List<ProductEmbedding> findSimilarProducts(
        @Param("embedding") String embedding, // 向量字符串如"[0.1,0.2,...]"
        @Param("limit") int limit);
}

@Configuration
public class VectorStoreConfig {

    @Bean
    public VectorStore vectorStore(JdbcTemplate jdbcTemplate, EmbeddingClient embeddingClient) {
        return PgVectorStore.builder()
            .withJdbcTemplate(jdbcTemplate)
            .withEmbeddingClient(embeddingClient)
            .withTableName("document_vectors")
            .withDistanceType(DistanceType.COSINE)
            .withDimensions(1536)
            .build();
    }
}

@Service
public class DocumentService {
    private final VectorStore vectorStore;
    
    // 注入VectorStore
    public DocumentService(VectorStore vectorStore) {
        this.vectorStore = vectorStore;
    }
    
    // 存儲文檔向量
    public void storeDocument(String content, Map<String, Object> metadata) {
        Document document = new Document(content, metadata);
        vectorStore.add(List.of(document));
    }
    
    // 相似度搜索
    public List<Document> searchSimilar(String query, int topK) {
        return vectorStore.similaritySearch(query, topK);
    }
}

-- 設置并行工作線程數(shù)
SET max_parallel_maintenance_workers = 8;

-- 增加維護內存（僅會話級生效）
SET maintenance_work_mem = '16GB';

-- 創(chuàng)建并行HNSW索引
CREATE INDEX idx_hnsw_parallel ON large_documents
USING hnsw (embedding vector_cosine_ops);

-- 結合元數(shù)據(jù)過濾與向量搜索
SELECT * FROM products
WHERE metadata->>'category' = 'electronics'
ORDER BY embedding <=> '[0.3, 0.1, ..., 0.7]'
LIMIT 10;

-- 批量插入向量（使用pgvector的向量數(shù)組構造器）
INSERT INTO documents (content, embedding)
SELECT 
    text, 
    vector_agg(value)::vector(3)  -- 聚合為3維向量
FROM unnest(ARRAY['text1', 'text2'], ARRAY[[0.1,0.2,0.3], [0.4,0.5,0.6]]) AS t(text, value)
GROUP BY text;

-- 只返回相似度高于閾值的結果
SELECT * FROM articles
WHERE embedding <=> '[0.5, 0.3, ..., 0.2]' < 0.8  -- 余弦距離閾值
ORDER BY embedding <=> '[0.5, 0.3, ..., 0.2]'
LIMIT 20;

@Service
public class ProductSearchService {
    private final EmbeddingClient embeddingClient;
    private final ProductRepository productRepository;
    
    // 注入依賴...
    
    public List<ProductDTO> searchSimilarProducts(String query, int limit) {
        // 1. 生成查詢向量
        EmbeddingResponse response = embeddingClient.embed(query);
        float[] queryEmbedding = response.getEmbedding().getValues();
        
        // 2. 轉換為PGvector字符串
        String vectorStr = "[" + Arrays.stream(queryEmbedding)
            .mapToObj(Float::toString)
            .collect(Collectors.joining(",")) + "]";
            
        // 3. 執(zhí)行相似度查詢
        List<ProductEmbedding> similarProducts = productRepository
            .findSimilarProducts(vectorStr, limit);
            
        // 4. 轉換為DTO返回
        return similarProducts.stream()
            .map(this::toProductDTO)
            .collect(Collectors.toList());
    }
}

@Service
public class KnowledgeBaseService {
    private final VectorStore vectorStore;
    private final TextSplitter textSplitter;
    
    @Value("${app.embedding.dimensions:768}")
    private int dimensions;
    
    public void ingestDocument(MultipartFile file) throws IOException {
        // 1. 讀取文檔內容
        DocumentReader reader = new TikaDocumentReader(file.getInputStream());
        List<Document> documents = reader.read();
        
        // 2. 文本分塊（遞歸字符分割器）
        List<Document> splitDocuments = textSplitter.split(documents);
        
        // 3. 存儲到向量庫
        vectorStore.add(splitDocuments);
    }
    
    public String answerQuestion(String question) {
        // 1. 檢索相關文檔片段
        List<Document> relevantDocs = vectorStore.similaritySearch(question, 3);
        
        // 2. 構建提示詞
        String context = relevantDocs.stream()
            .map(Document::getContent)
            .collect(Collectors.joining("\n\n"));
            
        String prompt = String.format("基于以下信息回答問題：\n%s\n\n問題：%s", context, question);
        
        // 3. 調用大模型生成回答
        return chatClient.call(prompt);
    }
}

-- 檢查pgvector版本
SELECT * FROM pg_extension WHERE extname = 'vector';

-- 驗證向量運算
SELECT '[1,2,3]'::vector <-> '[4,5,6]' AS l2_distance;

-- 查看查詢計劃
EXPLAIN ANALYZE
SELECT * FROM documents
ORDER BY embedding <=> '[0.1,0.2,...0.9]'
LIMIT 10;

-- 檢查索引使用情況
SELECT idx_scan FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_hnsw_embedding';

VACUUM ANALYZE documents; -- 更新統(tǒng)計信息，幫助優(yōu)化器選擇正確索引