PostgreSQL with pgvector

Overview

Vector similarity search using PostgreSQL pgvector extension. This guide provides practical, production-ready implementations.

Category: ai-infrastructure Primary Tool: postgresql Tags: infrastructure, devops, postgresql, production

Prerequisites

bash
pip install openai anthropic postgresql python-dotenv
export OPENAI_API_KEY="sk-..."

Core Implementation

python
import os
from openai import OpenAI
from typing import Optional, Any
import json
client = OpenAI()
class PostgreSQL_with_pgvector:
    """PostgreSQL with pgvector
    
    Vector similarity search using PostgreSQL pgvector extension
    """
    
    def __init__(self, model: str = "gpt-4o", temperature: float = 0.3):
        self.client = OpenAI()
        self.model = model
        self.temperature = temperature
        self.system = """You are an AI expert in ai-infrastructure. 
        Provide accurate, practical, production-ready assistance.
        Be clear, concise, and well-structured."""
    
    def run(self, query: str, context: Optional[dict] = None) -> dict:
        """Execute the main workflow."""
        
        messages = [{"role": "system", "content": self.system}]
        
        if context:
            messages.append({
                "role": "user",
                "content": f"Context: {json.dumps(context, indent=2)}"
            })
        
        messages.append({"role": "user", "content": query})
        
        response = self.client.chat.completions.create(
            model=self.model,
            messages=messages,
            temperature=self.temperature,
            max_tokens=2000
        )
        
        return {
            "output": response.choices[0].message.content,
            "model": self.model,
            "tokens": response.usage.total_tokens,
            "category": "ai-infrastructure"
        }
    
    def batch_run(self, queries: list[str]) -> list[dict]:
        """Process multiple queries."""
        return [self.run(q) for q in queries]
Usage
tool_instance = PostgreSQL_with_pgvector()
result = tool_instance.run("How do I implement postgresql with pgvector?")
print(result["output"])

Advanced Usage

python
from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
app = FastAPI(title="PostgreSQL with pgvector API")
tool_instance = PostgreSQL_with_pgvector()
class Request(BaseModel):
    query: str
    context: dict = {}
@app.post("/run")
async def run_endpoint(req: Request):
    try:
        result = tool_instance.run(req.query, req.context)
        return result
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))
@app.get("/health")
async def health():
    return {"status": "ok", "tool": "PostgreSQL with pgvector"}

Best Practices

Input validation — always validate and sanitize inputs

Error handling — handle API failures gracefully with retries

Rate limiting — respect API rate limits with backoff

Caching — cache responses to reduce costs

Monitoring — track usage, costs, and quality metrics

Testing

python
import pytest
@pytest.fixture
def tool():
    return PostgreSQL_with_pgvector(model="gpt-4o-mini")
def test_basic_functionality(tool):
    result = tool.run("Test query for PostgreSQL with pgvector")
    assert "output" in result
    assert len(result["output"]) > 10
    assert result["category"] == "ai-infrastructure"
def test_batch_processing(tool):
    queries = ["Query 1", "Query 2", "Query 3"]
    results = tool.batch_run(queries)
    assert len(results) == 3
    assert all("output" in r for r in results)

Resources

OpenAI API: https://platform.openai.com/docs

postgresql documentation

Related tutorials on infrastructure, devops, postgresql, production