pgvector: Mapping and Similarity Search

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.

Regression examples pinned to 6.8.0: GitHub / Gitee.

6.8.0 examples

The examples below use 6.8.0: vector-ordering parameters use the field's configured TypeHandler. Pass List<Float> to a field mapped with PgVectorTypeHandler, not the old PGobject ordering argument. See Vector type handlers.

About 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.

Vector Mapping Setup

First create the table in PostgreSQL with pgvector installed:

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE product_vector (
    id integer PRIMARY KEY,
    name text,
    price numeric,
    category text,
    embedding vector(3)
);

Entity Mapping

@Table("product_vector")
public class ProductVector {
    @Column(primary = true)
    private Integer id;
    private String name;
    private BigDecimal price;
    private String category;
    
    @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.setPrice(new BigDecimal("299"));
product.setCategory("electronics");
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 Search

Pass a List<Float> matching the field mapping; the configured PgVectorTypeHandler converts both stored vectors and query vectors.

// Query vector
List<Float> queryVector = List.of(0.15F, 0.25F, 0.35F);

// L2 distance ordering (Euclidean distance)
List<ProductVector> nearest = lambda.query(ProductVector.class)
      .orderByL2(ProductVector::getEmbedding, queryVector)  // ascending by L2 distance
      .initPage(5, 0)
      .queryForList();

// Cosine similarity ordering
List<ProductVector> similar = lambda.query(ProductVector.class)
      .orderByCosine(ProductVector::getEmbedding, queryVector)
      .initPage(5, 0)
      .queryForList();

// Inner product ordering
List<ProductVector> ipResults = lambda.query(ProductVector.class)
      .orderByIP(ProductVector::getEmbedding, queryVector)
      .initPage(5, 0)
      .queryForList();

// Generic interface — enum-driven
List<ProductVector> results = lambda.query(ProductVector.class)
      .orderByMetric(MetricType.L2, ProductVector::getEmbedding, queryVector)
      .initPage(10, 0)
      .queryForList();

Vector and Scalar Filters

// Search for the most similar products within a price range
List<ProductVector> results = lambda.query(ProductVector.class)
      .rangeBetween(ProductVector::getPrice, 100, 500)
      .eq(ProductVector::getCategory, "electronics")
      .orderByL2(ProductVector::getEmbedding, queryVector)
      .initPage(10, 0)
      .queryForList();

How the Handler Works

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.