pgai

The pgai Vectorizer provides an automated way to generate and manage LLM embeddings for PostgreSQL data. Key features include:

• Automated embedding generation & synchronization: Creates triggers on source tables to automatically update embeddings when data changes.
• Background processing: Runs asynchronously to minimize impact on standard INSERT, UPDATE, and DELETE operations.
• Scalability: Processes data in batches and supports concurrent execution.
• Configurability: Allows fine-grained control over embedding models (e.g., OpenAI, Ollama), chunking strategies, formatting templates, and indexing options.
• Automated Views: Automatically creates views that join original data with its embeddings for easy querying.
• Management: Supports scheduling, monitoring via queues, and fine-grained access control.

The pgai vectorizer uses a configurable pipeline consisting of five stages:

1. Loading: Defines the source (a table column or a URI to a file/S3 bucket).
2. Parsing: Handles non-text documents (PDF, HTML, Markdown).
3. Chunking: Splits text into manageable segments.
4. Formatting: Formats chunks before embedding (e.g., prepending titles).
5. Embedding: Specifies the LLM provider, model, and parameters.

The pgai semantic-catalog command group manages the lifecycle of semantic catalogs used for natural language to SQL functionality. The workflow follows these steps:

1. Describe: Generate natural language descriptions of database objects.
2. Create: Create a new semantic catalog with embedding configuration.
3. Import: Import descriptions into the semantic catalog.
4. Vectorize: Generate embeddings for semantic search capabilities.
5. Search: Perform semantic searches to find relevant database objects.
6. Generate SQL: Generate SQL statements from natural language prompts.

For self-hosted PostgreSQL instances, you can provide API keys by setting an environment variable available to the PostgreSQL process. After setting the variable, reference its name in pgai functions using the api_key_name parameter.

Systemd

Add the variable to the [Service] stanza of your unit file: Environment=MY_API_KEY=<api key here>

Docker

Use the -e flag during docker run: docker run -e MY_API_KEY=<api key here> ... timescale/timescaledb-ha:pg17

Docker Compose

Add the variable to the environment section of your database service:

services:
  db:
    image: timescale/timescaledb-ha:pg17
    environment:
      MY_API_KEY: <api key here>

SELECT * FROM ai.openai_list_models(api_key_name => 'MY_API_KEY');

To ensure secure AI operations within your database, follow these best practices:

• User Privileges: Grant specific permissions to users or roles to control who can access pgai functionality.
• API Key Management: Follow established patterns for securely handling and storing LLM API keys within your database environment.

To use pgai with Ollama, ensure Ollama is running and network-accessible to your database. You can specify the Ollama network address using the host parameter in function calls or by setting the ai.ollama_host configuration parameter. If neither is provided, pgai defaults to http://localhost:11434 and logs a warning.

If pgai is running in a Docker container and Ollama is on the same host machine, use http://host.docker.internal:11434 as the host address.

A vectorizer is a pgai concept that automatically processes data in a table to create and sync embeddings.

To use a vectorizer:

1. Enable the ai extension: CREATE EXTENSION IF NOT EXISTS ai CASCADE;.
2. Ensure your source table exists (e.g., a blog table with a contents column).
3. Call ai.create_vectorizer specifying the source table, the column to load, the embedding model (via ai.embedding_ollama), and the destination table.

Example configuration for an Ollama-based vectorizer using the nomic-embed-text model with 768 dimensions:

SELECT ai.create_vectorizer(
     'blog'::regclass,
     loading => ai.loading_column('contents'),
     embedding => ai.embedding_ollama('nomic-embed-text', 768),
     destination => ai.destination_table('blog_contents_embeddings')
);