PostgreSQL Vector Search

Performs vector similarity search in a PostgreSQL database.

Overview

Performs vector similarity search in a PostgreSQL database.



Prerequisites

A valid PostgreSQL Account with the required permissions.

Snap views

Type Description Examples of upstream and downstream Snaps
Input
  • Requires an input vector with the same dimension as the selected vector column.
  • Requires a vector input with an array of float/int data types.
Output For each input document, all results are grouped in a single output document.
Learn more about Error handling.

Snap settings

Note: Learn about the common controls in the Snap settings dialog.
Field/Field set Type Description
Label

String

 Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline.

Default value: PostgreSQL - Vector Search

Example: PostgreSQL VS
Schema name

String/Expression/ Suggestion

 Specify the schema name for searching for a vector.

Default value: N/A

Example: VECTOR_DEMO
Table name

String/Expression/ Suggestion

 Required. Specify the table name for searching for a vector.

Default value: N/A

Example: VECTOR_DEMO.BOOKS
Vector column

String/Expression/ Suggestion

 Required. Specify the vector column name to search.

Default value: N/A

Example: INT_VEC
Where clause

String/Expression/ Suggestion

 Specify the where clause to use in the vector search query statement.
Note: Because of the limitation of the SQL standard, you cannot use the _SL_DISTANCE column in the where clause.

Default value: N/A

Example: ID > '001i0000007FVjpAAG'
Limit rows

Integer/Expression

 Specify the number of rows the query must return.

Default value: 4

Example: 3

Min Value: 1
Distance function

Dropdown list

 Required. Choose the similarity function to compare vectors. The available options are:
  • L2: (Euclidean Distance) Measures the straight-line distance between two points. It's useful when you want to calculate the as-the-crow-flies distance.
  • L1: (Manhattan Distance) Measures the distance between two points on the axes at right angles. It's useful in grid-based systems, such as city streets.
  • COSINE: Measures the cosine of the angle between two vectors. It's commonly used in high-dimensional positive spaces to assess similarity regardless of magnitude.
  • Inner Product: (Dot Product) Measures the similarity between two vectors. It's useful in various applications, such as calculating the angle between vectors or finding projections.

Default value: L2

Example: COSINE
Include vector values

Checkbox/Expression

 Select this checkbox to include vector values in the response.
Note: This field does not support input schema from the upstream Snaps.

Default value: Deselected
Include scores

Checkbox/Expression

 Select this checkbox to include similarity scores in the response.
Note:
  • This field does not support input schema from the upstream Snaps.
  • When you select this checkbox, the output preview displays _SL_DISTANCE, which is the distance between the input vector and vectors in the database.

Default value: Selected
Ignore empty result

Checkbox

 Select this checkbox to ignore the empty results and not write a document to the output view when a search operation returns no results.

Default value: Deselected
Number of retries

Integer/Expression

 Specify the maximum number of retry attempts the Snap must make if a network failure occurs.

Default value: 0

Example: 3
Retry interval (seconds)

Integer/Expression

 Specify the time period between two successive retry requests.

Default value: 0

Example: 3
Snap Execution

Dropdown list

Choose one of the three modes in which the Snap executes. Available options are:

  • Validate & Execute: Performs limited execution of the Snap and generates a data preview during pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during pipeline runtime.
  • Execute only: Performs full execution of the Snap during pipeline execution without generating preview data.
  • Disabled: Disables the Snap and all Snaps that are downstream from it.

Default value: Execute only

Example: Validate & Execute