v6.7.0: PgVector Support

Hint

This article is generated by AI translation.

PostgreSQL's pgvector extension enables vector storage and similarity search, but Java lacked a clean mapping. dbVisitor 6.7.0's PgVectorTypeHandler maps vector columns directly to List<Float> with full Fluent API support for CRUD and KNN queries.

What is pgvector?

pgvector is a PostgreSQL extension that supports:

• Storing high-dimensional vectors (e.g., embeddings)
• L2 distance, cosine similarity, and inner product similarity search
• IVFFLAT and HNSW indexes for acceleration

In AI applications, text embeddings, image feature vectors, and recommendation system user vectors all need to be stored in a database and searched via nearest-neighbor queries.

Using PgVectorTypeHandler

Mapping Definition

@Table("product_vector")
public class ProductVector {
    @Column(primary = true)
    private Integer id;
    private String name;
    
    @Column(typeHandler = PgVectorTypeHandler.class)
    private List<Float> embedding;
    
    // getters/setters...
}

Simply specify typeHandler = PgVectorTypeHandler.class on the @Column annotation to enable automatic conversion between List<Float> and pgvector's vector type.

Basic CRUD

LambdaTemplate lambda = new LambdaTemplate(dataSource);

// Insert vector data
ProductVector product = new ProductVector();
product.setId(1);
product.setName("iPhone");
product.setEmbedding(Arrays.asList(0.1f, 0.2f, 0.3f));

lambda.insert(ProductVector.class)
      .applyEntity(product)
      .executeSumResult();

// Query and retrieve vector
ProductVector loaded = lambda.query(ProductVector.class)
      .eq(ProductVector::getId, 1)
      .queryForObject();

List<Float> embedding = loaded.getEmbedding();
// [0.1, 0.2, 0.3]

KNN Similarity Search

dbVisitor's Fluent API natively supports vector ordering methods:

// Query vector
List<Float> queryVector = Arrays.asList(0.15f, 0.25f, 0.35f);

// L2 distance ordering (Euclidean distance)
List<ProductVector> nearest = lambda.query(ProductVector.class)
      .orderByL2("embedding", queryVector)  // ascending by L2 distance
      .limit(5)
      .queryForList();

// Cosine similarity ordering
List<ProductVector> similar = lambda.query(ProductVector.class)
      .orderByCosine("embedding", queryVector)
      .limit(5)
      .queryForList();

// Inner product ordering
List<ProductVector> ipResults = lambda.query(ProductVector.class)
      .orderByIP("embedding", queryVector)
      .limit(5)
      .queryForList();

// Generic interface — enum-driven
List<ProductVector> results = lambda.query(ProductVector.class)
      .orderByMetric("embedding", queryVector, VectorMetric.L2)
      .limit(10)
      .queryForList();

Combined Vector + Scalar Queries

// Search for the most similar products within a price range
List<ProductVector> results = lambda.query(ProductVector.class)
      .between("price", 100, 500)
      .eq("category", "electronics")
      .orderByL2("embedding", queryVector)
      .limit(10)
      .queryForList();

Implementation Details

PgVectorTypeHandler has a very concise implementation:

• Write: Serializes List<Float> to pgvector's text format [0.1,0.2,0.3] and passes it to PreparedStatement as Types.OTHER
• Read: Parses pgvector's returned string [0.1,0.2,0.3] into a List<Float>

// Write
ps.setObject(i, "[0.1,0.2,0.3]", Types.OTHER);

// Read
String val = rs.getString(columnName);  // "[0.1,0.2,0.3]"
List<Float> vector = parseVector(val);  // [0.1f, 0.2f, 0.3f]

This text-format approach is consistent with pgvector's official protocol and does not depend on any additional Java client libraries.