Vector Search

The vector-search flow is two part; first initialize a table using vectorize.table(), then search the table with vectorize.search().

Initialize a table

Initialize a table for vector search. Generates embeddings and index. Creates triggers to keep embeddings up-to-date.

vectorize."table"(
    "table" TEXT,
    "columns" TEXT[],
    "job_name" TEXT,
    "primary_key" TEXT,
    "args" json DEFAULT '{}',
    "schema" TEXT DEFAULT 'public',
    "update_col" TEXT DEFAULT 'last_updated_at',
    "transformer" TEXT DEFAULT 'openai/text-embedding-ada-002',
    "index_dist_type" vectorize.IndexDist DEFAULT 'pgv_hnsw_cosine',
    "table_method" vectorize.TableMethod DEFAULT 'join',
    "schedule" TEXT DEFAULT '* * * * *'
) RETURNS TEXT

Parameter	Type	Description
table	text	The name of the table to be initialized.
columns	text	The name of the columns that contains the content that is used for context for RAG. Multiple columns are concatenated.
job_name	text	A unique name for the project.
primary_key	text	The name of the column that contains the unique record id.
args	json	Additional arguments for the transformer. Defaults to '{}'.
schema	text	The name of the schema where the table is located. Defaults to 'public'.
update_col	text	Column specifying the last time the record was updated. Required for cron-like schedule. Defaults to last_updated_at
transformer	text	The name of the transformer to use for the embeddings. Defaults to 'text-embedding-ada-002'.
index_dist_type	IndexDist	The name of index type to build. Defaults to 'pgv_hnsw_cosine'.
table_method	TableMethod	join to store embeddings in a new table in the vectorize schema. append to create columns for embeddings on the source table. Defaults to join.
schedule	text	Accepts a cron-like input for a cron based updates. Or realtime to set up a trigger.

Sentence-Transformer Examples

OpenAI Examples

To use embedding model provided by OpenAI's public embedding endpoints, provide the model name into the transformer parameter, and provide the OpenAI API key.

Pass the API key into the function call via args.

select vectorize.table(
    job_name    => 'product_search',
    "table"     => 'products',
    primary_key => 'product_id',
    columns     => ARRAY['product_name', 'description'],
    transformer =>  'openai/text-embedding-ada-002',
    args        => '{"api_key": "my-openai-key"}'
);

The API key can also be set via GUC.

ALTER SYSTEM SET vectorize.openai_key TO 'my-openai-key';
SELECT pg_reload_conf();

Then call vectorize.table() without providing the API key.

select vectorize.table(
    job_name    => 'product_search',
    "table"     => 'products',
    primary_key => 'product_id',
    columns     => ARRAY['product_name', 'description'],
    transformer =>  'openai/text-embedding-ada-002'
);

Search a table

Search a table initialized with vectorize.table. The search results are sorted in descending order according to similarity. The query is transformed to embeddings using the same transformer configured during vectorize.table.

vectorize."search"(
    "job_name" TEXT,
    "query" TEXT,
    "api_key" TEXT DEFAULT NULL,
    "return_columns" TEXT[] DEFAULT ARRAY['*']::text[],
    "num_results" INT DEFAULT 10
) RETURNS TABLE (
    "search_results" jsonb
)

Parameters:

Parameter	Type	Description
job_name	text	A unique name for the project.
query	text	The user provided query or command provided to the chat completion model.
api_key	text	API key for the specified chat model. If OpenAI, this value overrides the config vectorize.openai_key
return_columns	text[]	The columns to return in the search results. Defaults to all columns.
num_results	int	The number of results to return. Sorted in descending order according to similarity. Defaults to 10.

Example

SELECT * FROM vectorize.search(
    job_name        => 'product_search',
    query           => 'mobile electronic devices',
    return_columns  => ARRAY['product_id', 'product_name'],
    num_results     => 3
);

                                         search_results                                     

--------------------------------------------------------------------------------------------
----
 {"product_id": 13, "product_name": "Phone Charger", "similarity_score": 0.8564681325237845}
 {"product_id": 24, "product_name": "Tablet Holder", "similarity_score": 0.8295988934993099}
 {"product_id": 4, "product_name": "Bluetooth Speaker", "similarity_score": 0.8250355616233103}
(3 rows)