How chuckhend/pg_vectorize works
Project Type and Purpose
• This is primarily a PostgreSQL extension project (“pg_vectorize”) written in Rust (using the pgrx framework) which automates transforming text into vector embeddings and orchestrates background jobs for real-time or scheduled updates.
• It also ships an optional HTTP embedding service (“vector-serve” in Python/FastAPI) and helper Docker containers (e.g., “ollama-serve”) to host self-managed transformer models.
• The core goal is to let end users add vector search and RAG capabilities into Postgres with two SQL calls, relying on pgvector for similarity search, pgmq (and optionally pg_cron) for queuing, and external LLM providers (OpenAI, HF SentenceTransformers, Ollama).
File-Structure Analysis
• Top-level folders:
• core/ – Rust library + binary (“worker.rs”) implementing the orchestration logic and generic transformer interface
• extension/ – pgrx-based Postgres extension crate, defines SQL functions (vectorize.table, search, rag, generate, encode) and background‐worker hooks
• vector-serve/ – FastAPI microservice for hosting embedding endpoints (used by the extension or standalone)
• ollama-serve/, images/vectorize-pg/ – Docker setups for hosting SentenceTransformers or Ollama models
• docs/, .github/, Makefile, docker-compose.yml, pyproject.toml, etc.
• Look for build scripts (core/Makefile, extension/Makefile, vector-serve/Makefile), configuration (docker-compose, .dockerignore), and test folders.
Key README Insights
• Details on extension API: vectorize.table, search, rag, generate, encode, import_embeddings, table_from
• Dependencies: pgvector, pgmq (or pg_cron), external transformer providers (OpenAI, HuggingFace, self-hosted)
• Usage patterns: triggers for realtime updates vs. cron schedules, background jobs, synchronous SQL search/RAG calls.
a. Main Components
• Postgres Database
– Core extension modules (pgrx) loaded as shared libraries
– Dependencies: pgvector extension, pgmq (or pg_cron)
– SQL API entry points (vectorize.table, search, rag, generate, encode)
• Background Worker (Rust binary)
– Monitors job queues (pgmq tables or cron schedule)
– Applies transformer logic (generic + provider implementations)
– Writes embeddings back to Postgres via extension functions
• Embedding Service(s)
– vector-serve FastAPI container (Python) hosting embeddings endpoint (/v1/encode)
– ollama-serve container for self-hosted LLMs (optional)
– External APIs: OpenAI REST endpoints
• Client / User Interface
– User issues SQL statements (CREATE EXTENSION vectorize, SELECT vectorize.search(..), etc.)
• Supporting Infrastructure
– Docker Compose orchestrates Postgres + optional services
– CI/CD pipelines (.github/actions) for building crates, publishing, testing
b. Relationships & Data Flows
1. User runs SQL “vectorize.table(…)” → extension creates triggers or cron jobs in PG
2. Data changes in user tables → trigger inserts job into pgmq queue (or worker polls on schedule)
3. Background Worker reads job → extracts text columns → calls embedding service(s):
• If configured for HTTP: calls vector-serve or ollama-serve
• If OpenAI: makes HTTPS request to platform.openai.com
4. Receives embedding vector → uses extension API to UPDATE the user table’s embedding column
5. User runs “SELECT vectorize.search(…)” → extension invokes pgvector to compute top-K similarities, returns results
6. User runs “SELECT vectorize.rag(…)” → extension collects nearest docs, calls chat LLM (OpenAI or Ollama), returns chat response
c. Architectural Patterns & Principles
• Event-driven / asynchronous processing (Postgres triggers → pgmq → background worker)
• Modular transformer interface (generic traits + provider plugins)
• Side-car microservices for model hosting (vector-serve, ollama-serve)
• Clear separation between in-DB logic (extension) and out-of-DB compute (worker + external HTTP)
• Dockerized deployments for isolation and reproducibility
d. Key Technologies & Libraries
• Rust (pgrx) for extension & worker
• Postgres extensions: pgvector, pgmq (pg_cron)
• Python / FastAPI for vector-serve
• Docker / Docker-Compose for local setup
• CI: GitHub Actions for building crates & publishing PGXN, pgrx init, vector-serve tests
• LLM providers: OpenAI APIs, HuggingFace Sentence-Transformers (via ollama or hosted)
Essential Elements to Capture
• Postgres extension lifecycle (install, triggers setup, UDFs)
• Two update modes (realtime triggers vs. cron schedule)
• Background orchestration flow (pgmq → worker → embedding providers → DB)
• Query-time flow (search & RAG through extension → pgvector / chat LLM)
• Containerized model services (vector-serve, ollama-serve) and optional OpenAI API