NAME

DBIO::PostgreSQL::ResultSet - ResultSet with pgvector nearest-neighbor search

VERSION

version 0.900007

SYNOPSIS

package MyApp::Schema::Result::Item;
use base 'DBIO::Core';
__PACKAGE__->load_components('PostgreSQL::Result');

__PACKAGE__->table('items');
__PACKAGE__->add_columns(
  id        => { data_type => 'integer', is_auto_increment => 1 },
  name      => { data_type => 'text' },
  embedding => { data_type => 'vector(3)' },   # <- promotes resultset_class
);
__PACKAGE__->set_primary_key('id');

__PACKAGE__->pg_index('idx_items_embedding' => {
  using     => 'hnsw',
  columns   => ['embedding'],
  opclasses => { embedding => 'vector_cosine_ops' },
});

# ... elsewhere

my $rs = $schema->resultset('Item')->nearest_neighbors(
  embedding => [ 0.1, 0.2, 0.3 ],
  distance  => 'cosine',
  rows      => 5,
);
# SELECT me.id, me.name, me.embedding, (me.embedding <=> ?::vector) AS neighbor_distance
#   FROM items me
#  ORDER BY (me.embedding <=> ?::vector)
#  LIMIT ?

DESCRIPTION

A DBIO::ResultSet subclass carrying pgvector query sugar, currently "nearest_neighbors".

A source gets this class automatically: DBIO::PostgreSQL::Result promotes its resultset_class the first time a column with a pgvector data type (vector, halfvec, sparsevec) is registered, so declaring the column is the whole opt-in. The promotion is skipped when the source's resultset_class already inherits from this class, so a custom resultset survives:

package MyApp::Schema::ResultSet::Item;
use base 'DBIO::PostgreSQL::ResultSet';

# ... your own methods, plus nearest_neighbors

Set it by hand for the cases auto-detection cannot see - a bit column queried with Hamming/Jaccard distance, or a result class that does not load the PostgreSQL::Result component:

__PACKAGE__->resultset_class('DBIO::PostgreSQL::ResultSet');

METHODS

nearest_neighbors

my $rs = $schema->resultset('Item')->nearest_neighbors(
  embedding => [ 0.1, 0.2, 0.3 ],
  distance  => 'cosine',
  rows      => 10,
);

while ( my $item = $rs->next ) {
  printf "%s (%.4f)\n", $item->name, $item->get_column('neighbor_distance');
}

K-nearest-neighbor search on a pgvector column: orders by the distance between the column and $query_vector (nearest first) and selects that distance as neighbor_distance.

Arguments are the column, the query vector, and named options:

  • distance - required, the metric. See "DISTANCE KEYWORDS".

  • rows - optional row limit, the k of "k nearest neighbors".

  • cast - optional placeholder cast for the query vector, passed through to "vector" in DBIO::PostgreSQL::Vector. Needed for halfvec and bit columns; a sparsevec query vector (hashref) casts itself.

The column may be given unqualified ('embedding'), in which case it is resolved against this resultset's current source alias, or fully qualified ('me.embedding', 'items.embedding') to name a joined source.

The query vector is passed to DBIO::PostgreSQL::Vector::Op and travels as a bind parameter, so an arrayref, a pgvector text string, or a sparsevec hashref all work.

The return value is an ordinary resultset, so it chains:

$rs->nearest_neighbors( embedding => $v, distance => 'cosine' )
   ->search( { 'me.active' => 1 } )
   ->slice( 0, 9 );

An unknown option, a missing distance, or an unknown distance keyword throws a DBIO::Exception.

This method is deliberately opinionated (it always selects neighbor_distance, it always orders ascending). For a query that needs something else - the distance in WHERE, a descending order, a different alias - use "vector" in DBIO::PostgreSQL::Vector directly; this method is sugar over it, not a replacement.

DISTANCE KEYWORDS

Keyword         Operator   Vector::Op method
--------------------------------------------
euclidean, l2   <->        l2_distance
cosine          <=>        cosine_distance
inner_product   <#>        max_inner_product
taxicab, l1     <+>        l1_distance
hamming         <~>        hamming_distance
jaccard         <%>        jaccard_distance

Keywords are case-insensitive. The set is the Rails neighbor gem's, plus the l2/l1 aliases.

inner_product uses pgvector's <#>, which returns the negative inner product - so ascending order still puts the most similar row first, and neighbor_distance is negative for positively-correlated vectors.

OPERATOR CLASSES

Which distance you ask for decides whether an index can serve the query. An hnsw or ivfflat index only answers for the operator its operator class was built for: vector_l2_ops serves <->, vector_cosine_ops serves <=>, vector_ip_ops serves <#> (with the halfvec_*, sparsevec_* and bit_* families for the other column types). Ordering by an operator no index covers is not an error - PostgreSQL simply falls back to a sequential scan and computes every row's distance.

nearest_neighbors does not and cannot check this: nothing in a resultset knows which indexes the database actually has. Declare the pairing where it belongs, in opclasses on the "pg_index" in DBIO::PostgreSQL::Result that covers the column, and pass the distance that matches it. EXPLAIN on the generated query is the only real confirmation.

SEE ALSO

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.