DOCSEARCHbyAlgolia
Submit Docs

pg_vectorize

repository·main·Indexed 21 days ago

https://github.com/chuckhend/pg_vectorize

A VectorDB on Postgres that automates the transformation of text into embeddings for RAG (Retrieval-Augmented Generation) and hybrid search engines. It provides a Postgres extension for self-hosted instances and an HTTP server/SQL proxy for managed databases. Key features include automated embedding orchestration via vectorize.table(), similarity search with vectorize.search(), and LLM integration through vectorize.rag(), vectorize.generate(), and vectorize.encode().

Tokens
38.4K
Snippets
143
Records
172
Agent score
73%

What's inside pg_vectorize

  1. Use the pg_vectorize HTTP server for job management and search

    main

    The pg_vectorize HTTP server provides two primary capabilities: managing vectorization jobs and performing searches on indexed data.

    • Job Management: Use the POST /api/v1/table endpoint to initialize a vectorize job for a specific database table.
    • Search: Use the GET /api/v1/search endpoint to query the indexed vector data.

    By default, the server is assumed to be running at http://localhost:8080. For local setup instructions and high-level examples, refer to the server/README.md file in the repository.

  2. What is the SQL proxy?

    main

    The SQL proxy provides a SQL interface to vectorize.search() without requiring the installation of a Postgres extension. It acts as an intermediary that sits in front of Postgres and intercepts vectorize.search() calls.

    When a query is intercepted, the proxy:

    1. Generates embeddings for the query.
    2. Rewrites the query as a hybrid search (combining semantic and full-text search).
    3. Returns the results transparently using the standard Postgres wire protocol.

    Because it uses the standard protocol, any SQL client compatible with Postgres can be used to interact with the proxy.

  3. How model referencing works in pg_vectorize

    main

    In pg_vectorize, all models (both text-to-embedding and text-generation) are referenced in SQL using a specific URI-like syntax:

    ${provider}/${model-name}

    Examples:

    • openai/text-embedding-ada-002 (OpenAI embedding model)
    • openai/gpt-3.5-turbo-instruct (OpenAI text generation model)
    • ollama/wizardlm2:7b (Ollama hosted model)
    • sentence-transformers/all-MiniLM-L12-v2 (SentenceTransformers embedding model)
    `${provider}/${model-name}`

  4. Configure embedding update scheduling in vectorize.table

    main

    The pg_vectorize extension allows you to control how and when embeddings are updated relative to changes in your source text data using the schedule parameter within the vectorize.table function. There are two primary modes of operation:

    1. Cron-based Scheduling (Default): Sets a background worker to check for changes at specific intervals.

      • Default value: '* * * * *' (checks every minute).
      • Requirement: You must set the updated_at_col parameter to a column in your table that tracks when the input text columns were last modified.
      • Customization: You can provide any valid cron-like string.

    2. Realtime Updates:

      • Value: 'realtime'
      • Behavior: Creates database triggers on the source table. Embeddings are updated immediately whenever a new record is inserted or an existing record is updated.
    -- Example of cron-based scheduling (default behavior)
-- Requires updated_at_col to track changes
SELECT vectorize.table('my_table', schedule => '* * * * *', updated_at_col => 'last_modified');

-- Example of realtime scheduling
SELECT vectorize.table('my_table', schedule => 'realtime');

  5. Relationship between Vector Search and RAG APIs

    main

    In pg_vectorize, APIs are divided into two categories: Vector Search and Retrieval Augmented Generation (RAG).

    • Vector Search APIs: These are lower-level APIs used for finding similar vectors.
    • RAG APIs: These are higher-level APIs built on top of the vector search APIs to facilitate retrieval-augmented generation tasks.

    Both sets of APIs are considered high-level relative to standard Postgres APIs.

  6. Choose between the HTTP server and Postgres extension

    main

    pg_vectorize offers two deployment modes depending on your database environment:

    • HTTP server (Recommended for managed DBs): Use this if you are using a managed service like AWS RDS or Google Cloud SQL, or if you cannot install custom extensions. It runs as a standalone service that connects to Postgres and exposes a REST API. It only requires that pgvector is already available in your database.
    • Postgres extension (SQL): Use this if you self-host Postgres and have filesystem access to the database server. This provides an in-database experience using direct SQL functions like vectorize.table() and vectorize.search().

  7. Use filters in Hybrid Search

    main

    Filters allow you to restrict search results based on specific column values. The server validates these against the job's schema.

    Syntax and Operators

    Filters use an operator prefix to define the comparison logic. If no operator is provided, the server defaults to eq (Equal).

    Supported Operators:

    • eq: Equal
    • gt: Greater Than
    • gte: Greater Than or Equal
    • lt: Less Than
    • lte: Less Than or Equal

    Example: price=gt.10 or price=gte.10.

    Method-Specific Implementation

    • GET: Filters are supplied as individual URL query parameters.
      • Example: ?product_category=outdoor&price=lt.10
    • POST: Filters are supplied as a JSON object within the filters field in the request body.
      • Example: {"filters": {"product_category": "outdoor", "price": "lt.10"}}
    curl -G "http://localhost:8080/api/v1/search" \
  --data-urlencode "job_name=my_job" \
  --data-urlencode "query=camping gear" \
  --data-urlencode "limit=2" \
  --data-urlencode "product_category=outdoor" \
  --data-urlencode "price=gt.10"

  8. Configure and use Ollama generative models

    main

    Ollama allows you to run self-hosted text generation models.

    1. Run the Ollama server

    Start the ollama-serve service via Docker Compose:

    docker compose up ollama-serve -d

    2. Configure Postgres

    Set the vectorize.ollama_service_url to point to your Ollama instance:

    ALTER SYSTEM set vectorize.ollama_service_url TO 'http://localhost:3001';
SELECT pg_reload_conf();

    3. Use in RAG API

    Pass the model name (prefixed with ollama/) to the chat_model parameter in the vectorize.rag function:

    SELECT vectorize.rag(
    job_name    => 'product_chat',
    query       => 'What is a pencil?',
    chat_model  => 'ollama/wizardlm2:7b'
);

    4. Loading new models

    You can pull new models from the Ollama library using a curl command to the /api/pull endpoint:

    curl http://localhost:3001/api/pull -d '{"name": "llama3"}'

    Then use it in SQL:

    SELECT vectorize.rag(
    job_name    => 'product_chat',
    query       => 'What is a pencil?',
    chat_model  => 'ollama/llama3'
);

  11. Configure the embedding service URL

    main

    The pg_vectorize extension requires the vectorize.embedding_svc_url configuration parameter to be set to the URL of your embedding server (e.g., a vector-serve container).

    If you are running in a Docker environment using the provided docker-compose.yaml, the URL is typically http://vector-serve:3000/v1/embeddings.

    If running locally outside of Docker, you must manually update the setting to point to your local instance (e.g., http://localhost:3000/v1/embeddings) and reload the PostgreSQL configuration.

    -- Check current setting
SHOW vectorize.embedding_service_url;

-- Set to local address if not using Docker
ALTER SYSTEM SET vectorize.embedding_svc_url TO 'http://localhost:3000/v1/embeddings';

-- Reload configuration to apply changes
SELECT pg_reload_conf();

  12. Configure vectorize to run on a custom database

    main

    By default, vectorize runs on the postgres database. To change this to a different database, you must update both the vectorize.database_name and the cron.database_name settings (to ensure pg_cron background workers connect to the correct database).

    Steps to change the target database:

    1. Create the new database.
    2. Update the system settings for cron.database_name and vectorize.database_name using ALTER SYSTEM.
    3. Restart PostgreSQL to apply the configuration changes.
    4. Connect to the new database and enable the extension using CREATE EXTENSION vectorize CASCADE;.
    CREATE DATABASE my_new_db;

ALTER SYSTEM SET cron.database_name TO 'my_new_db';
ALTER SYSTEM SET vectorize.database_name TO 'my_new_db';

-- After restarting postgres:
\c my_new_db
CREATE EXTENSION vectorize CASCADE;