# LLM Book Index: AI-Native Vector Databases with PostgreSQL & pgvector subtitle: From Zero to Production RAG, Search, and Agent Memory author: Muhammad Usman Akbar site: https://muhammadusmanakbar.com/pgvector-book navigation_api: https://muhammadusmanakbar.com/api/pgvector-book-nav flat_chapter_list: https://muhammadusmanakbar.com/api/pgvector-book-nav?format=flat chapter_content_api: https://muhammadusmanakbar.com/api/pgvector-book-content/{slug} published: 2026-07-31 chapters: 64 ## About A zero-prerequisite pgvector tutorial that progresses from PostgreSQL, SQL, vectors, and embeddings through exact and approximate search, ingestion, hybrid retrieval, evaluation, RAG, agent memory, security, production operations, scaling, and three capstones. ## Full book structure ### Start Here - About This Book: Your Zero-to-Expert Path url: https://muhammadusmanakbar.com/pgvector-book/about-this-book content: https://muhammadusmanakbar.com/api/pgvector-book-content/about-this-book description: Learn how to use this beginner-first pgvector book to progress from one similarity query to evaluated, secure, production AI retrieval systems. - What Is an AI-Native Vector Database? url: https://muhammadusmanakbar.com/pgvector-book/ai-native-vector-database content: https://muhammadusmanakbar.com/api/pgvector-book-content/ai-native-vector-database description: Understand vector databases, embeddings, pgvector, and the full AI retrieval lifecycle before choosing an architecture or writing application code. - Relational Databases and PostgreSQL from First Principles url: https://muhammadusmanakbar.com/pgvector-book/postgresql-first-principles content: https://muhammadusmanakbar.com/api/pgvector-book-content/postgresql-first-principles description: Learn tables, rows, keys, constraints, transactions, indexes, schemas, clients, and servers before adding vectors to PostgreSQL. - Install PostgreSQL, pgvector, Python, and Docker url: https://muhammadusmanakbar.com/pgvector-book/install-postgresql-pgvector content: https://muhammadusmanakbar.com/api/pgvector-book-content/install-postgresql-pgvector description: Create a reproducible local pgvector lab with Docker, verify every tool and extension version, and understand native installation alternatives. - Your First Vector Table and Similarity Query url: https://muhammadusmanakbar.com/pgvector-book/first-vector-query content: https://muhammadusmanakbar.com/api/pgvector-book-content/first-vector-query description: Create a pgvector table, insert tiny teaching vectors, run exact cosine and L2 searches, and prove how query direction changes ranking. ### Vectors and Embeddings - Vectors Without Intimidating Mathematics url: https://muhammadusmanakbar.com/pgvector-book/vectors-without-intimidating-math content: https://muhammadusmanakbar.com/api/pgvector-book-content/vectors-without-intimidating-math description: Build an intuitive and practical understanding of vector coordinates, magnitude, direction, dimensions, neighborhoods, and high-dimensional limits. - Embeddings: Models, Tokens, Dimensions, and Meaning url: https://muhammadusmanakbar.com/pgvector-book/embeddings-models-tokens-dimensions content: https://muhammadusmanakbar.com/api/pgvector-book-content/embeddings-models-tokens-dimensions description: Understand how embedding models transform text and other inputs, why preprocessing and versions matter, and what semantic proximity can and cannot mean. - L2, Cosine, Inner Product, L1, Hamming, and Jaccard Distance url: https://muhammadusmanakbar.com/pgvector-book/distance-metrics content: https://muhammadusmanakbar.com/api/pgvector-book-content/distance-metrics description: Choose and implement pgvector distance operators correctly, match metrics to model behavior, and avoid similarity-score and index-operator mistakes. - Normalization, Dimensions, Precision, and Model Compatibility url: https://muhammadusmanakbar.com/pgvector-book/normalization-dimensions-precision content: https://muhammadusmanakbar.com/api/pgvector-book-content/normalization-dimensions-precision description: Control vector shape, numeric precision, normalization, nullability, zero vectors, and embedding-version compatibility in pgvector schemas. - Choose and Evaluate an Embedding Model url: https://muhammadusmanakbar.com/pgvector-book/choose-evaluate-embedding-model content: https://muhammadusmanakbar.com/api/pgvector-book-content/choose-evaluate-embedding-model description: Select embedding models through workload constraints and labeled retrieval evidence instead of leaderboards, popularity, or one impressive demo. ### PostgreSQL and pgvector Essentials - SQL Essentials for AI Engineers url: https://muhammadusmanakbar.com/pgvector-book/sql-essentials-ai-engineers content: https://muhammadusmanakbar.com/api/pgvector-book-content/sql-essentials-ai-engineers description: Learn the SQL needed for vector applications: selection, parameters, joins, grouping, common table expressions, transactions, and safe query composition. - Schema Design for Documents, Chunks, Metadata, and Embeddings url: https://muhammadusmanakbar.com/pgvector-book/schema-documents-chunks-metadata content: https://muhammadusmanakbar.com/api/pgvector-book-content/schema-documents-chunks-metadata description: Design a durable pgvector schema that separates sources, documents, chunks, permissions, embedding versions, and derived retrieval state. - pgvector Data Types: vector, halfvec, bit, and sparsevec url: https://muhammadusmanakbar.com/pgvector-book/pgvector-data-types content: https://muhammadusmanakbar.com/api/pgvector-book-content/pgvector-data-types description: Compare pgvector's dense, half-precision, binary, and sparse representations, their indexed limits, operators, casts, and use cases. - Exact Nearest-Neighbor Search url: https://muhammadusmanakbar.com/pgvector-book/exact-nearest-neighbor-search content: https://muhammadusmanakbar.com/api/pgvector-book-content/exact-nearest-neighbor-search description: Use pgvector's index-free exact search as a correctness baseline, add filters and thresholds safely, and understand the cost of scanning candidates. - Filters, Joins, Transactions, and Referential Integrity url: https://muhammadusmanakbar.com/pgvector-book/filters-joins-transactions content: https://muhammadusmanakbar.com/api/pgvector-book-content/filters-joins-transactions description: Combine vector retrieval with PostgreSQL metadata, joins, authorization predicates, constraints, and atomic writes without confusing filters with security. - Insert, Upsert, Update, Delete, and Bulk COPY url: https://muhammadusmanakbar.com/pgvector-book/write-and-bulk-copy content: https://muhammadusmanakbar.com/api/pgvector-book-content/write-and-bulk-copy description: Write pgvector data safely with idempotent inserts, conflict handling, scoped updates and deletions, binary COPY, staging tables, and verification. - Connect from Python and TypeScript Safely url: https://muhammadusmanakbar.com/pgvector-book/python-typescript-clients content: https://muhammadusmanakbar.com/api/pgvector-book-content/python-typescript-clients description: Use psycopg and node-postgres with registered pgvector types, connection pools, parameters, transactions, timeouts, and explicit application boundaries. ### Build the Ingestion System - Document Extraction, Normalization, and Provenance url: https://muhammadusmanakbar.com/pgvector-book/extraction-normalization-provenance content: https://muhammadusmanakbar.com/api/pgvector-book-content/extraction-normalization-provenance description: Turn files and records into trustworthy normalized documents while preserving source identity, versions, structure, checksums, permissions, and extraction evidence. - Chunking by Structure and Meaning url: https://muhammadusmanakbar.com/pgvector-book/chunking-structure-meaning content: https://muhammadusmanakbar.com/api/pgvector-book-content/chunking-structure-meaning description: Design addressable retrieval chunks using headings, paragraphs, tables, code, overlap, token budgets, parent context, and evaluation evidence. - Idempotent Embedding Pipelines and Job State url: https://muhammadusmanakbar.com/pgvector-book/idempotent-pipelines-job-state content: https://muhammadusmanakbar.com/api/pgvector-book-content/idempotent-pipelines-job-state description: Build retry-safe ingestion with explicit states, leases, deterministic identities, checksums, optimistic concurrency, dead letters, and reconciliation. - Batch APIs, Rate Limits, Retries, and Backpressure url: https://muhammadusmanakbar.com/pgvector-book/batches-rate-limits-backpressure content: https://muhammadusmanakbar.com/api/pgvector-book-content/batches-rate-limits-backpressure description: Control embedding throughput with bounded batches, quotas, adaptive concurrency, retry budgets, circuit breakers, queues, and lag-based backpressure. - Change Detection, Re-embedding, Tombstones, and Deletion url: https://muhammadusmanakbar.com/pgvector-book/change-reembedding-deletion content: https://muhammadusmanakbar.com/api/pgvector-book-content/change-reembedding-deletion description: Keep vector retrieval synchronized with changing sources through checksums, versioned visibility, deletion tombstones, reconciliation, and cache invalidation. - Embedding-Model Migrations Without Downtime url: https://muhammadusmanakbar.com/pgvector-book/model-migrations-zero-downtime content: https://muhammadusmanakbar.com/api/pgvector-book-content/model-migrations-zero-downtime description: Migrate embedding models using parallel columns or tables, backfills, separate ANN indexes, shadow evaluation, staged cutover, and reversible cleanup. - Multilingual, Multimodal, and Multi-Vector Records url: https://muhammadusmanakbar.com/pgvector-book/multilingual-multimodal-multivector content: https://muhammadusmanakbar.com/api/pgvector-book-content/multilingual-multimodal-multivector description: Model language-aware text, image and audio embeddings, and multiple vector representations per record without mixing incompatible semantic spaces. ### Retrieval Engineering - Exact Search as the Quality Baseline url: https://muhammadusmanakbar.com/pgvector-book/exact-search-quality-baseline content: https://muhammadusmanakbar.com/api/pgvector-book-content/exact-search-quality-baseline description: Construct reproducible exact pgvector result sets, separate approximation recall from relevance, and benchmark filtered queries before ANN tuning. - HNSW Indexes from Intuition to Tuning url: https://muhammadusmanakbar.com/pgvector-book/hnsw-indexes content: https://muhammadusmanakbar.com/api/pgvector-book-content/hnsw-indexes description: Build and tune pgvector HNSW indexes with matching operator classes, construction/search parameters, recall experiments, and operational safeguards. - IVFFlat Indexes from Training to Tuning url: https://muhammadusmanakbar.com/pgvector-book/ivfflat-indexes content: https://muhammadusmanakbar.com/api/pgvector-book-content/ivfflat-indexes description: Understand pgvector IVFFlat clustering, build timing, lists and probes, training-data distribution, recall tuning, and operational tradeoffs. - Filtered Approximate Search and Iterative Scans url: https://muhammadusmanakbar.com/pgvector-book/filtered-ann-iterative-scans content: https://muhammadusmanakbar.com/api/pgvector-book-content/filtered-ann-iterative-scans description: Make filtered HNSW and IVFFlat searches return useful rows using selectivity analysis, iterative scans, partial indexes, partitioning, and plan tests. - PostgreSQL Full-Text Search for Lexical Retrieval url: https://muhammadusmanakbar.com/pgvector-book/postgresql-full-text-search content: https://muhammadusmanakbar.com/api/pgvector-book-content/postgresql-full-text-search description: Build language-aware PostgreSQL lexical search with tsvector, tsquery, GIN indexes, ranking, exact identifiers, and safe query parsing. - Hybrid Search with Reciprocal Rank Fusion url: https://muhammadusmanakbar.com/pgvector-book/hybrid-search-rrf content: https://muhammadusmanakbar.com/api/pgvector-book-content/hybrid-search-rrf description: Combine authorized PostgreSQL lexical and pgvector semantic candidates using Reciprocal Rank Fusion, deduplication, diversity, and evaluation. - Reranking, Query Rewriting, and Multi-Query Retrieval url: https://muhammadusmanakbar.com/pgvector-book/reranking-query-rewriting content: https://muhammadusmanakbar.com/api/pgvector-book-content/reranking-query-rewriting description: Improve pgvector candidates with controlled query rewriting, decomposition, expansion, cross-encoder reranking, fusion, and latency-aware fallbacks. - Metadata Filters, Freshness, Diversity, and Business Rules url: https://muhammadusmanakbar.com/pgvector-book/metadata-freshness-diversity-rules content: https://muhammadusmanakbar.com/api/pgvector-book-content/metadata-freshness-diversity-rules description: Blend semantic relevance with typed metadata, freshness, inventory, authority, deduplication, diversity, and explicit product policy. - Build a Golden Dataset and Evaluate Retrieval url: https://muhammadusmanakbar.com/pgvector-book/golden-dataset-evaluation content: https://muhammadusmanakbar.com/api/pgvector-book-content/golden-dataset-evaluation description: Create governed relevance labels and calculate recall, precision, MRR, nDCG, no-result accuracy, latency, and statistical confidence for retrieval. ### RAG and Agentic Applications - The Complete RAG Request Lifecycle url: https://muhammadusmanakbar.com/pgvector-book/complete-rag-lifecycle content: https://muhammadusmanakbar.com/api/pgvector-book-content/complete-rag-lifecycle description: Understand production RAG from identity and retrieval through context, generation, citation validation, observability, feedback, and deletion. - Build a Citation-First RAG API url: https://muhammadusmanakbar.com/pgvector-book/citation-first-rag-api content: https://muhammadusmanakbar.com/api/pgvector-book-content/citation-first-rag-api description: Implement a small provider-neutral RAG API with authorized pgvector retrieval, structured outputs, server-validated citations, timeouts, and tests. - Context Assembly, Token Budgets, and Prompt-Injection Boundaries url: https://muhammadusmanakbar.com/pgvector-book/context-budgets-injection-boundaries content: https://muhammadusmanakbar.com/api/pgvector-book-content/context-budgets-injection-boundaries description: Assemble diverse evidence within token budgets while treating retrieved text as untrusted data and defending tool and policy boundaries. - Conversation State, Summaries, and Semantic Memory url: https://muhammadusmanakbar.com/pgvector-book/conversation-semantic-memory content: https://muhammadusmanakbar.com/api/pgvector-book-content/conversation-semantic-memory description: Separate short-term conversation state from durable semantic memory using explicit consent, salience, versioning, provenance, retrieval, and forgetting. - Agent Memory, Tool Retrieval, and Human Approval url: https://muhammadusmanakbar.com/pgvector-book/agent-memory-tools-approval content: https://muhammadusmanakbar.com/api/pgvector-book-content/agent-memory-tools-approval description: Build policy-bounded agent memory and tool discovery with typed capabilities, least privilege, execution-time authorization, approval, and audit evidence. - Recommendations and Similarity Features Beyond RAG url: https://muhammadusmanakbar.com/pgvector-book/recommendations-similarity-features content: https://muhammadusmanakbar.com/api/pgvector-book-content/recommendations-similarity-features description: Use pgvector for related items, content-based recommendations, duplicate detection, clustering support, and human-reviewed similarity features. - Caching, Streaming, Fallbacks, and Graceful Degradation url: https://muhammadusmanakbar.com/pgvector-book/caching-streaming-fallbacks content: https://muhammadusmanakbar.com/api/pgvector-book-content/caching-streaming-fallbacks description: Design secure cache keys, invalidation, streamed responses, stage deadlines, circuit breakers, and evidence-aware fallbacks for AI retrieval systems. ### Production PostgreSQL - Measure Query Plans, Latency, Recall, and Throughput url: https://muhammadusmanakbar.com/pgvector-book/query-plans-latency-recall-throughput content: https://muhammadusmanakbar.com/api/pgvector-book-content/query-plans-latency-recall-throughput description: Read PostgreSQL plans and benchmark pgvector with EXPLAIN ANALYZE BUFFERS, exact recall, warm and cold latency, concurrency, and end-to-end traces. - Memory, Maintenance, Vacuum, and Index Builds url: https://muhammadusmanakbar.com/pgvector-book/memory-maintenance-vacuum-index-builds content: https://muhammadusmanakbar.com/api/pgvector-book-content/memory-maintenance-vacuum-index-builds description: Operate pgvector tables with memory budgets, autovacuum, analyze, HNSW and IVFFlat builds, concurrent indexing, bloat observation, and safe maintenance. - Connections, Pooling, Transactions, and Concurrency url: https://muhammadusmanakbar.com/pgvector-book/connections-pooling-concurrency content: https://muhammadusmanakbar.com/api/pgvector-book-content/connections-pooling-concurrency description: Control PostgreSQL connection cost, pool sizing, transaction scope, timeouts, cancellation, prepared state, and concurrent vector workloads. - Row-Level Security and Multi-Tenant Isolation url: https://muhammadusmanakbar.com/pgvector-book/row-security-multitenancy content: https://muhammadusmanakbar.com/api/pgvector-book-content/row-security-multitenancy description: Enforce tenant isolation with PostgreSQL roles, row-level security, transaction-local identity, server-derived policy, tests, and operational defense in depth. - Encryption, Secrets, Privacy, Retention, and Audit url: https://muhammadusmanakbar.com/pgvector-book/encryption-secrets-privacy-audit content: https://muhammadusmanakbar.com/api/pgvector-book-content/encryption-secrets-privacy-audit description: Protect vector systems with TLS, storage encryption, least privilege, secret rotation, data minimization, retention, deletion, and useful audit records. - Backups, Point-in-Time Recovery, Replication, and High Availability url: https://muhammadusmanakbar.com/pgvector-book/backup-pitr-replication-ha content: https://muhammadusmanakbar.com/api/pgvector-book-content/backup-pitr-replication-ha description: Protect pgvector data with tested backups, WAL archiving, point-in-time recovery, replicas, failover, recovery objectives, and index-aware drills. - Observability, SLOs, Incident Response, and Quality Drift url: https://muhammadusmanakbar.com/pgvector-book/observability-slos-drift-incidents content: https://muhammadusmanakbar.com/api/pgvector-book-content/observability-slos-drift-incidents description: Observe pgvector across database health, ingestion freshness, retrieval quality, RAG stages, privacy, SLOs, alerts, drift, and incident response. - Zero-Downtime Migrations, Releases, and Rollback url: https://muhammadusmanakbar.com/pgvector-book/zero-downtime-releases-rollback content: https://muhammadusmanakbar.com/api/pgvector-book-content/zero-downtime-releases-rollback description: Release pgvector schema, indexes, ingestion, retrieval, and model changes through expand-migrate-contract, feature flags, canaries, and rehearsed rollback. ### Scale and Advanced Techniques - Partition Vectors by Tenant, Time, Language, or Model url: https://muhammadusmanakbar.com/pgvector-book/partition-vectors content: https://muhammadusmanakbar.com/api/pgvector-book-content/partition-vectors description: Use PostgreSQL partitioning for pruning, lifecycle, isolation, and maintenance only when workload predicates and operational evidence justify complexity. - Half Precision, Binary Quantization, Sparse Vectors, and Subvectors url: https://muhammadusmanakbar.com/pgvector-book/quantization-sparse-subvectors content: https://muhammadusmanakbar.com/api/pgvector-book-content/quantization-sparse-subvectors description: Reduce pgvector search footprint with halfvec, binary signatures, sparse vectors, and subvectors while preserving full-precision reranking and measured recall. - Two-Stage Retrieval and Expression Indexes url: https://muhammadusmanakbar.com/pgvector-book/two-stage-expression-indexes content: https://muhammadusmanakbar.com/api/pgvector-book-content/two-stage-expression-indexes description: Build coarse-to-fine pgvector retrieval with expression indexes, oversampled candidates, exact reranking, materialized ordering, and measurable recall. - Vertical Scaling, Read Replicas, Sharding, and Distributed PostgreSQL url: https://muhammadusmanakbar.com/pgvector-book/scaling-replicas-sharding content: https://muhammadusmanakbar.com/api/pgvector-book-content/scaling-replicas-sharding description: Scale pgvector through measured vertical resources, workload isolation, replicas, partitioning, and sharding while preserving global top-k and consistency. - Capacity Planning and Cost Modeling url: https://muhammadusmanakbar.com/pgvector-book/capacity-cost-modeling content: https://muhammadusmanakbar.com/api/pgvector-book-content/capacity-cost-modeling description: Estimate pgvector rows, vector storage, indexes, WAL, backups, memory, compute, embedding and generation spend, growth, headroom, and unit economics. - When to Choose pgvector Versus a Specialist Vector Database url: https://muhammadusmanakbar.com/pgvector-book/pgvector-vs-specialist-database content: https://muhammadusmanakbar.com/api/pgvector-book-content/pgvector-vs-specialist-database description: Choose pgvector or a specialist vector database through workload evidence, consistency, filters, scale, features, operations, portability, risk, and cost. ### Deployment and Integration - A Reproducible Docker Development Environment url: https://muhammadusmanakbar.com/pgvector-book/docker-development-environment content: https://muhammadusmanakbar.com/api/pgvector-book-content/docker-development-environment description: Build a version-pinned local pgvector stack with health checks, migrations, fixtures, application services, safe secrets, tests, and complete cleanup. - Deploy pgvector on Managed PostgreSQL url: https://muhammadusmanakbar.com/pgvector-book/managed-postgresql content: https://muhammadusmanakbar.com/api/pgvector-book-content/managed-postgresql description: Evaluate and deploy pgvector on managed PostgreSQL with extension support, networking, identity, backups, replicas, upgrades, observability, and cost controls. - ORMs, LangChain, LlamaIndex, and Abstraction Boundaries url: https://muhammadusmanakbar.com/pgvector-book/orms-frameworks-boundaries content: https://muhammadusmanakbar.com/api/pgvector-book-content/orms-frameworks-boundaries description: Use ORMs and AI frameworks without losing pgvector SQL visibility, schema ownership, authorization, migrations, evaluation, or portability. - CI/CD, Migrations, Fixtures, and Retrieval Regression Tests url: https://muhammadusmanakbar.com/pgvector-book/cicd-migrations-regression-tests content: https://muhammadusmanakbar.com/api/pgvector-book-content/cicd-migrations-regression-tests description: Deliver pgvector changes with ephemeral PostgreSQL, migration tests, deterministic fixtures, query-plan checks, golden retrieval gates, canaries, and rollback. ### Capstones and Reference - Beginner Capstone: Semantic Product Search url: https://muhammadusmanakbar.com/pgvector-book/semantic-product-search-capstone content: https://muhammadusmanakbar.com/api/pgvector-book-content/semantic-product-search-capstone description: Build a complete pgvector product search with exact identifiers, semantic retrieval, filters, hybrid fusion, evaluation, API tests, and a launch review. - AI Capstone: Governed Citation-First RAG url: https://muhammadusmanakbar.com/pgvector-book/governed-citation-rag-capstone content: https://muhammadusmanakbar.com/api/pgvector-book-content/governed-citation-rag-capstone description: Build and evaluate a multi-tenant citation-first RAG assistant with governed ingestion, hybrid retrieval, RLS, injection defense, and recovery. - Agent Capstone: Durable Memory with Approval Gates url: https://muhammadusmanakbar.com/pgvector-book/agent-memory-capstone content: https://muhammadusmanakbar.com/api/pgvector-book-content/agent-memory-capstone description: Build a consent-aware agent memory and tool system with pgvector retrieval, typed policy, corrections, expiry, approvals, idempotency, and audit. - Production Launch Checklist and Architecture Review url: https://muhammadusmanakbar.com/pgvector-book/production-launch-checklist content: https://muhammadusmanakbar.com/api/pgvector-book-content/production-launch-checklist description: Review a pgvector system across purpose, data, retrieval, security, reliability, performance, operations, governance, cost, and launch evidence. - Troubleshooting Playbook and SQL Cookbook url: https://muhammadusmanakbar.com/pgvector-book/troubleshooting-sql-cookbook content: https://muhammadusmanakbar.com/api/pgvector-book-content/troubleshooting-sql-cookbook description: Diagnose pgvector installation, dimensions, missing indexes, low recall, filtered results, slow builds, bloat, queues, RLS, and recovery with evidence. - Glossary, Learning Map, and Next Steps url: https://muhammadusmanakbar.com/pgvector-book/glossary-learning-map-next-steps content: https://muhammadusmanakbar.com/api/pgvector-book-content/glossary-learning-map-next-steps description: Review essential pgvector, PostgreSQL, retrieval, RAG, agent, security, and operations terms, then choose a concrete path to continued mastery. ## Recommended query routes - pgvector from zero -> https://muhammadusmanakbar.com/pgvector-book/about-this-book - Install PostgreSQL and pgvector -> https://muhammadusmanakbar.com/pgvector-book/install-postgresql-pgvector - HNSW tuning -> https://muhammadusmanakbar.com/pgvector-book/hnsw-indexes - Hybrid search with RRF -> https://muhammadusmanakbar.com/pgvector-book/hybrid-search-rrf - Build citation-first RAG -> https://muhammadusmanakbar.com/pgvector-book/citation-first-rag-api - Secure multi-tenancy with RLS -> https://muhammadusmanakbar.com/pgvector-book/row-security-multitenancy - Choose pgvector versus another database -> https://muhammadusmanakbar.com/pgvector-book/pgvector-vs-specialist-database