06. Vector Databases: PostgreSQL + pgvector

Android Architecture Mental Model: Specialized standalone vector databases (like Pinecone or Chroma) introduce the distributed dual-write problem: you store relational user metadata in one database and vector embeddings in another, requiring fragile two-phase commit logic. pgvector is analogous to adding an official Room type converter or SQLite C-extension directly inside your existing relational database. Your operational relational tables (Users, Tenants, Auth IDs) sit in the exact same schema and transaction boundary as your dense vector arrays.

1. The pgvector Data Architecture

pgvector registers a first-class vector(N) data type inside PostgreSQL. Embeddings are stored contiguously as IEEE 754 floating-point numbers on disk, allowing standard SQL queries to perform trigonometric scans alongside relational WHERE clauses.

flowchart TD subgraph PostgresEngine["PostgreSQL Runtime Engine"] ROW["Row Record: id, tenant_id, created_at, content"] VEC["vector(1536): [0.012, -0.043, 0.921, ...]"] INDEX["HNSW Index (Layered Geometric Graph)"] FILTER["Relational B-Tree (WHERE tenant_id = 'tenant_1')"] end CLIENT["SQL Query: SELECT content FROM docs WHERE tenant_id = 'tenant_1' ORDER BY embedding <=> '[...]' LIMIT 5"] CLIENT --> FILTER FILTER --> INDEX INDEX --> RESULT["Top-K Candidate Rows"]

2. Vector Distance Operators in PostgreSQL

pgvector introduces three primary geometric distance operators:

Operator	Distance Type	SQL Calculation	Optimal Index Setting
`<=>`	Cosine Distance	$1 - \text{Cosine Similarity}$	`vector_cosine_ops`
`<#>`	Negative Dot Product	$-\mathbf{u} \cdot \mathbf{v}$ (multiplied by -1 for ascending sort)	`vector_ip_ops` (Unit-normalized vectors)
`<->`	Euclidean Distance ($L_2$)	$\sqrt{\sum (u_i - v_i)^2}$	`vector_l2_ops`

3. Index Tuning: HNSW vs. IVFFlat

Without an index, Postgres performs an exact sequential scan ($O(N)$), comparing the query vector against every record. For production tables ($>100k$ rows), an Approximate Nearest Neighbor (ANN) index is required.

Index Parameter	IVFFlat (Inverted File Flat)	HNSW (Hierarchical Navigable Small World)
Mechanism	Partitions vectors into $K$ Voronoi centroid clusters; searches only vectors in the nearest centroids.	Builds a multi-layer geometric graph structure (skip-list concept in N-dimensional space).
Build Time & RAM	Very fast to construct; lowest memory footprint.	Slower index build time; higher RAM requirements.
Recall & Latency	Decent recall, but requires retraining centroids when distribution shifts.	Highest recall (>98%) and fast millisecond query throughput. Production standard.
DDL Definition	`CREATE INDEX ON docs USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);`	`CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);`

4. Dual-Stack Implementations: Schema & Vector Query

-- 1. Enable extension
CREATE EXTENSION IF NOT EXISTS vector;

-- 2. Define relational table with dense vector column
CREATE TABLE document_embeddings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id VARCHAR(64) NOT NULL,
    content TEXT NOT NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    embedding vector(1536) NOT NULL
);

-- 3. Construct HNSW ANN Index for Cosine Distance
CREATE INDEX idx_docs_hnsw_cosine 
ON document_embeddings 
USING hnsw (embedding vector_cosine_ops) 
WITH (m = 16, ef_construction = 64);

-- 4. Compound index for filtered queries
CREATE INDEX idx_docs_tenant ON document_embeddings (tenant_id);

import psycopg
from pgvector.psycopg import register_vector

DATABASE_URL = "postgresql://postgres:postgres@localhost:5432/ai_db"

def search_documents(query_vector: list[float], tenant_id: str, limit: int = 5):
    with psycopg.connect(DATABASE_URL) as conn:
        # Register pgvector type codec
        register_vector(conn)
        with conn.cursor() as cur:
            query = """
                SELECT id, content, embedding <=> %s AS distance
                FROM document_embeddings
                WHERE tenant_id = %s
                ORDER BY distance ASC
                LIMIT %s;
            """
            cur.execute(query, (query_vector, tenant_id, limit))
            results = cur.fetchall()
            return [{"id": r[0], "content": r[1], "score": 1 - r[2]} for r in results]

import java.sql.Connection
import java.sql.DriverManager
import com.pgvector.PGvector

data class SearchResult(val id: String, val content: String, val score: Double)

class PgVectorRepository(private val dbUrl: String) {

    fun searchSimilar(queryVector: FloatArray, tenantId: String, limit: Int): List<SearchResult> {
        val sql = """
            SELECT id, content, embedding <=> ? AS distance
            FROM document_embeddings
            WHERE tenant_id = ?
            ORDER BY distance ASC
            LIMIT ?;
        """.trimIndent()

        val results = mutableListOf<SearchResult>()

        DriverManager.getConnection(dbUrl).use { conn ->
            // Register PGvector type codec on JDBC connection
            PGvector.addVectorType(conn)
            
            conn.prepareStatement(sql).use { stmt ->
                stmt.setObject(1, PGvector(queryVector))
                stmt.setString(2, tenantId)
                stmt.setInt(3, limit)

                stmt.executeQuery().use { rs ->
                    while (rs.next()) {
                        val distance = rs.getDouble("distance")
                        results.add(
                            SearchResult(
                                id = rs.getString("id"),
                                content = rs.getString("content"),
                                score = 1.0 - distance
                            )
                        )
                    }
                }
            }
        }
        return results
    }
}

5. Progressive Glossary

Term	Technical Definition	Mobile / Systems Analogy
HNSW	Hierarchical Navigable Small World: a graph-based data structure executing logarithmic ANN searches.	Skip-lists or hierarchical spatial quad-trees used for GIS rendering in Google Maps.
$m$ Parameter	Maximum number of bi-directional connection links per node in an HNSW graph (typically 16–64).	Connection pool bounds or graph adjacency list capacity limits.
$ef\_construction$	Size of dynamic candidate list examined during HNSW index construction.	Compiler optimization flags (e.g., `-O3` compilation effort vs. build duration).
Iterative Pre-Filtering	Executing hard metadata SQL constraints (`WHERE tenant_id = X`) before vector distance traversal.	Applying Room `@Query` filters prior to sorting elements in memory.

Sources & Reference Standards

pgvector Open Source Project: Vector Similarity Search for PostgreSQL
PostgreSQL Official Documentation: Index Types & Extension Development
Pinecone / pgvector Benchmarking: Performance and Trade-offs of In-Engine Vector Search

← Module 05: Production RAG Proceed to Module 07: Tool Calling →