Intermediate

PostgreSQL for AI — Intermediate

Production RAG on Postgres: hybrid full-text + vector search, metadata filtering and query planning, RAG schema design, scaling embeddings with partitioning and quantization, and retrieval tuning.

5 lessons ~250 min total
  1. 1

    Hybrid Search: tsvector + pgvector

    Combine Postgres full-text search (tsvector, ts_rank, GIN) with vector similarity, fuse the two rankings with Reciprocal Rank Fusion in SQL, and know when hybrid beats pure vector.

    50 minFull-Text SearchtsvectorHybrid + RRF
  2. 2

    Metadata Filtering & Query Planning

    Combine WHERE filters with vector KNN, read EXPLAIN/EXPLAIN ANALYZE, navigate the filtered-ANN problem (pre- vs post-filter and recall loss), and add B-tree/GIN/partial/composite indexes that support filters.

    50 minMetadata FilteringEXPLAINPartial Indexes
  3. 3

    Schema Design for RAG

    Relational schema design for RAG: documents and chunks tables with foreign keys, parent-document retrieval, stable chunk IDs for idempotent re-embedding, and normalization tradeoffs.

    50 minChunk SchemaParent-ChildNormalization
  4. 4

    Scaling Embeddings: Partitioning & Quantization

    Manage the storage cost of large embedding tables with partitioning and partitioned indexes, and trade memory for a little recall with halfvec, binary, and scalar quantization in pgvector.

    50 minPartitioninghalfvecQuantization
  5. 5

    Tuning & Evaluating Postgres Retrieval

    The intermediate capstone — build a gold set, compute recall@K and MRR in SQL, tune HNSW ef_search and IVFFlat probes against recall and latency, and run the full tuning loop with EXPLAIN ANALYZE.

    50 minRecall@KIndex TuningCost