Connect PostgreSQL to AI Pipeline on GPU

What You’ll Connect

After this guide, your PostgreSQL database will trigger AI inference on your GPU server whenever new data arrives — automatically enriching rows with AI-generated summaries, classifications, embeddings, and extracted entities. Using pgvector and your vLLM endpoint on dedicated GPU hardware, you get semantic search directly in SQL queries without external search infrastructure.

The integration uses PostgreSQL triggers and a worker process that calls your OpenAI-compatible API. When rows are inserted or updated, the trigger queues them for AI processing. The worker generates embeddings, classifications, or summaries and writes results back to the database. pgvector stores embeddings as a native column type, enabling similarity search with standard SQL.

Prerequisites

• A GigaGPU server running a self-hosted LLM (setup guide)
• PostgreSQL 15+ with the pgvector extension installed
• Python 3.10+ with psycopg2, requests, and pgvector
• Network access between your PostgreSQL server and GPU endpoint

Integration Steps

Install the pgvector extension in PostgreSQL: CREATE EXTENSION vector;. Add an embedding column to your table using the vector type: ALTER TABLE articles ADD COLUMN embedding vector(1024);. Create an index for fast similarity search: CREATE INDEX ON articles USING ivfflat (embedding vector_cosine_ops);

Build a worker process that watches a processing queue table. When new rows need AI enrichment, the worker reads the source text, calls your GPU endpoint for embeddings, classifications, or summaries, and writes the results back. A PostgreSQL trigger or LISTEN/NOTIFY channel signals the worker when new data arrives.

Add semantic search functions that query pgvector using cosine similarity. Your application passes a search query to the embedding API, then uses the resulting vector in a SQL query to find the most similar rows. This gives you AI-powered search with a standard SQL interface.

Code Example

PostgreSQL integration with AI enrichment from your self-hosted models:

import psycopg2, requests, json
from pgvector.psycopg2 import register_vector

EMBEDDING_URL = "http://localhost:8001/v1/embeddings"
VLLM_URL = "http://localhost:8000/v1/chat/completions"
GPU_KEY = "your-api-key"

conn = psycopg2.connect("dbname=myapp user=app")
register_vector(conn)

def enrich_row(row_id, text):
    """Generate embedding and classification for a database row."""
    # Generate embedding
    resp = requests.post(EMBEDDING_URL, json={
        "input": [text], "model": "bge-large"
    }, headers={"Authorization": f"Bearer {GPU_KEY}"})
    embedding = resp.json()["data"][0]["embedding"]

    # Generate classification
    resp = requests.post(VLLM_URL, json={
        "model": "meta-llama/Llama-3-8b-chat-hf",
        "messages": [{"role": "user",
            "content": f"Classify this text into one category: "
                       f"tech, business, science, health, other.\n{text}"}],
        "max_tokens": 10, "temperature": 0.1
    }, headers={"Authorization": f"Bearer {GPU_KEY}"})
    category = resp.json()["choices"][0]["message"]["content"].strip()

    # Write back to database
    with conn.cursor() as cur:
        cur.execute(
            "UPDATE articles SET embedding = %s, category = %s, "
            "enriched_at = NOW() WHERE id = %s",
            (embedding, category, row_id)
        )
    conn.commit()

def semantic_search(query_text, limit=10):
    """Search articles by semantic similarity."""
    resp = requests.post(EMBEDDING_URL, json={
        "input": [query_text], "model": "bge-large"
    }, headers={"Authorization": f"Bearer {GPU_KEY}"})
    query_vec = resp.json()["data"][0]["embedding"]

    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, title, 1 - (embedding <=> %s::vector) AS similarity "
            "FROM articles ORDER BY embedding <=> %s::vector LIMIT %s",
            (query_vec, query_vec, limit)
        )
        return cur.fetchall()

Testing Your Integration

Insert a test row into your articles table and run the enrichment function. Verify the embedding column is populated with a vector of the correct dimension and the category column contains a valid classification. Run a semantic search query and verify results are ranked by relevance rather than keyword matching.

Test with bulk enrichment by inserting 100 rows and processing them in a batch. Measure throughput to estimate how long a full table backfill will take. Verify the pgvector index improves query speed by comparing search performance with and without the index on a larger dataset.

Production Tips

Process the enrichment backfill in batches of 100-500 rows to avoid overwhelming the GPU endpoint. Use PostgreSQL’s LISTEN/NOTIFY for real-time processing of new rows without polling. Tune the ivfflat index parameters (lists) based on your table size — more lists for larger tables, fewer for smaller ones.

For high-volume tables, run enrichment asynchronously with a task queue rather than synchronous triggers. Monitor embedding freshness — if source text updates, the embedding should regenerate. Build an AI chatbot that queries your enriched PostgreSQL database for grounded answers. Explore more tutorials or get started with GigaGPU to power your data pipeline.