Implement a complete RAG system using pgvector in PostgreSQL with IVFFlat indexing, hybrid search, and automatic reindexing.
You are a database and AI engineer. Build a RAG system using pgvector with PostgreSQL. ## Setup - **PostgreSQL Version**: [PG_VERSION e.g. 16] - **pgvector Version**: [PGVEC_VERSION e.g. 0.7.0] - **Language**: [LANGUAGE e.g. Python with asyncpg / Node.js with pg] - **Embedding Dimension**: [DIMENSION e.g. 1536] - **Table Name**: [TABLE e.g. documents] ## Database Schema Create migration scripts for: 1. Enable pgvector extension 2. Create [TABLE] with columns: id (UUID), content (TEXT), embedding (vector([DIMENSION])), metadata (JSONB), created_at, updated_at 3. Create IVFFlat index with lists=[LISTS e.g. 100] for approximate nearest neighbor 4. Create GIN index on metadata JSONB for filtering 5. Create GIN index on content using pg_trgm for keyword search 6. Create ts_vector column with GIN index for full-text search ## Application Layer ### Embedding Manager - Batch embed documents using [EMBED_PROVIDER e.g. OpenAI / Cohere] - Queue-based processing for large document sets - Track embedding status per document ### Hybrid Search Function Create a SQL function `hybrid_search(query_embedding, query_text, match_count, filter_json)` that: - Combines vector similarity (cosine distance) with full-text search (ts_rank) - Applies metadata JSONB filters dynamically - Uses RRF (Reciprocal Rank Fusion) to merge rankings - Returns results with combined score, similarity, and text rank ### Automatic Reindexing - Trigger function to update embeddings when content changes - Background job to rebuild IVFFlat index periodically - Monitor index quality with recall estimation queries ### API Endpoints - POST /documents - Ingest and embed documents - POST /search - Hybrid search with filters - GET /documents/:id/similar - Find similar documents - DELETE /documents/:id - Soft delete with embedding cleanup Provide all SQL migrations, application code, and a performance tuning guide with EXPLAIN ANALYZE examples.
Free to copy and use. Compatible with Claude 4 Opus, GPT-5, Gemini 2.5 Pro, Llama 4 Maverick.
Ensure pgvector extension is installed in your PostgreSQL instance. Run migration scripts in order. Adjust IVFFlat lists parameter based on your data size (sqrt of row count).
Initial release
Sign in and download this prompt to leave a review.