The pgvector Playbook
Production-Grade Vector Search in PostgreSQL
Pavan Krishna Rampalli
Postgres Bangalore Meetup
Google Cloud Platform
Google Cloud
TALK HIGHLIGHTS
pgvector - The Vector Database
Market Position & Enterprise Advantages
Market Position & Popularity
Widespread Developer Adoption
pgvector is widely adopted alongside specialized vector databases like Pinecone, Milvus, Weaviate, Quadrant, etc.
SQL & Cloud Native
Drives massive adoption through standard SQL familiarity and native managed support across Google Cloud, AWS, and Azure.
Completely eliminates the operational overhead of maintaining separate, isolated AI database infrastructure.
Architectural Advantages
Full ACID Integrity
Vector updates share transaction boundaries with business data, guaranteeing consistency via standard PostgreSQL WAL and MVCC.
Zero-Lag Synchronization
Integrates updates inline to eliminate fragile ETL or CDC pipelines and completely prevent dual-write drift.
Unified Integration
Easily query structured tables and vector similarity representations together in a single statement.
Google Cloud
Semantic Search Baseline
Understanding Search by Intent
Semantic search matches meaning rather than brittle word-for-word patterns.
For example, an e-commerce query for "Sustainable running shoes for wet weather" would fail with traditional keyword searches if the literal text isn't in the metadata.
SQL QUERY SNIPPET
SELECT id, title, price
FROM products
ORDER BY embedding <=> $query_embedding
LIMIT 5;
Sample Results
1. Eco-Trail GORE-TEX Runner
Similarity: 0.94
2. Recycled Mesh All-Weather Sneaker
Similarity: 0.91
3. Bio-Based Waterproof Trainer
Similarity: 0.89
4. Plant-Foam Storm Shield Jogger
Similarity: 0.85
5. Recycled Ripstop Rain Trainer
Similarity: 0.82
Google Cloud
Vector Embedding
Transforming Unstructured Text into 768-Dimensional Geometric Meaning
Google Cloud
INPUT DATA
"Sustainable running shoes for wet weather"
TEXT PROMPT
Embed β
EMBEDDING MODEL
text-embedding-005
Vectors β
VECTOR EMBEDDING
768 Floating-Point Dimensions
HIGH-DIMENSIONAL ARRAY ANATOMY
DIM 1
0.32423
DIM 2
-0.01323
DIM 3
0.89104
...
...
DIM 768
-0.45012
β’ 4 Bytes per Float (32-bit floating point precision)
β’ Total Storage: 3,072 Bytes per Row
β’ PostgreSQL Data Type: pgvector: vector(768)
GEOMETRIC PROXIMITY = SEMANTIC SIMILARITY
Cosine Space (<=>)
β Query Target
dist = 0.04
(Near)
β "Eco-Trail Shoes"
dist = 0.89
(Far)
β "Gold Ring"
Concepts with similar meanings cluster together geometrically in multi-dimensional vector space.
Google Cloud Platform
Overview
Exact kNN: The Scaling Wall
Why Brute-Force Computations Decline at Scale
Distance Metrics: Similarity is evaluated using vector distance metrics: Cosine Distance (<=>), L2/Euclidean (<->), and Inner Product (<#>).
Full Row Scans: To calculate exact nearest neighbors (Flat or exact kNN search), Postgres must scan every row, calculating 768 floating-point multiplications and additions per row.
Complexity: O(N * D)
N = Dataset Size | D = Dimensions (e.g. 768)
The Scale Limit: At scale, brute-force exact search pins the CPU and tanks throughput, necessitating ANN (Approximate Nearest Neighbor) indexes.
Google Cloud
Exact Search Latency vs. Dataset Size
Dataset Size
Avg. Latency
SLA Status
10,000 rows
~15 β 30 ms
SLA Pass
50,000 rows
~60 β 120 ms
SLA Warn
100,000 rows
~200 β 280 ms
SLA Fail
500,000 rows
~1.0 β 1.4 sec
SLA Fail
1,000,000 rows
~2.2 β 3.0 sec
SLA Fail
Google Cloud
ANN & Query Recall: The Accuracy Trade-off
Trading Absolute Precision for Sub-Millisecond Speed: Measuring Quality via Recall@k
2. ANN Search Results
π Query: "Sustainable running shoes for wet weather"
1. Eco-Trail GORE-TEX Runner β
2. Recycled Mesh All-Weather Sneaker β
3. Bio-Based Waterproof Trainer β
4. Plant-Foam Storm Shield Jogger β
5. Synthetic Trekker β οΈ (True #14)
β οΈ ACCURACY TRADE-OFF:
β’ Missed true item #5 ("Recycled Ripstop Rain Trainer") due to early graph termination.
β’ Substituted lower-rank item (rank #14).
3. Query Recall@k Calculation
Recall@k = |ANN β© Truth| / k
80.0%
Recall@5 = 4 / 5
β’ Requested Top k: 5 items
β’ True Neighbors Found: 4 items
β’ Resulting Quality: 0.80 Recall
1. Why Vector Search Requires ANNs
Dynamic Distance Computation
Distance depends on the runtime query vector, requiring explicit geometric math against stored vectors.
No Precomputable Exact Indexes
Unlike B-Trees or inverted indexes that pre-sort exact keys, continuous high-dimensional space cannot precalculate exact distances.
π― Why We Must Trade Off Accuracy
To avoid full O(NΓD) linear scans, ANNs intentionally accept ~1β5% accuracy trade-offs to achieve sub-millisecond latency.
Choose the right Index Type
We will take the example of HNSW vs IVFFlat β two index types offered in pgvector
Google Cloud
β‘
Query Speed
HNSW is faster
β’ Sub-millisecond query latencies via hierarchical multi-layer proximity graphs.
β’ O(log N) search routing delivers substantially higher QPS under load.
β’ Best for interactive user SLAs.
π―
Recall
HNSW typically higher
β’ Superior recall typically achieved for the same target QPS.
Caution: Be cognizant of factors affecting recall:
β’ Query selectivity
β’ Sparse vs dense vectors
β±οΈ
Index Building
IVFFlat is faster
β’ Faster build times leveraging simple k-means centroid clustering.
β’ Minimal CPU overhead during upfront index generation.
β’ Ideal when rapid initial bulk load is needed.
πΎ
Memory Usage
IVFFlat is lower
β’ Lower RAM footprint storing compact inverted centroid lists.
β’ Zero graph edge overhead (avoids heavy link tables).
β’ Best for RAM-constrained database instances.
π
Incremental Data Changes
HNSW handles well
β’ HNSW handles updates seamlessly with zero offline re-training.
β’ IVFFlat is sensitive to data drift and may need frequent re-building.
β’ Built-in graph repair maintains recall.
Ref: pgvector docs - https://github.com/pgvector/pgvector/pull/422/files , Google Cloud blog on getting started with pgvector indexes.
HNSW Deconstruction: Skip Lists
Multi-Layer 1D Express Highways
Understanding the 1D Skip List
To understand HNSW, we first deconstruct the 1D Skip List using the analogy of a multi-story highway:
Level 2 (Express): Highway exits are spaced 100 miles apart.
Level 1 (Medium): Highway exits are spaced 10 miles apart.
Level 0 (Local): Standard road exits are spaced every single mile.
To search a specific point, you travel as far as possible on the top level, and then drop down levels as you get closer to the destination, skipping large swaths of irrelevant data.
Level 2 (Express)
Level 1 (Medium)
Level 0 (Local)
H
30
60
H
10
30
50
60
H
10
20
30
40
50
60
β Blue Path: Step-by-Step Search Traversal (Target: 50)
1. Level 2 (Express): Start at H β Hop to 30. Next node 60 overshoots (60 > 50) β Stop at 30.
2. Vertical Drop: Drop down to Level 1 at anchor node 30.
3. Level 1 (Medium): Advance from 30 β 50 (Target Match!). Drop to Level 0 to access record.
β‘ Key Benefit: Skips nodes 10, 20, 40 entirely, slashing linear O(N) scan to O(log N) hops
Google Cloud
HNSW Deconstruction: Proximity Graphs
Spatial Connection of Near Neighbors
VISUALIZATION: HNSW BASE LAYER (SPATIAL HOPS)
Fast Path Routing
3 hops to target cluster
Neighborhood Connectivity (M)
A proximity graph connects each vector node to its "M" nearest spatial neighbors.
This forms a small-world network where any node can be reached from another within a small number of hops.
This spatial equivalent of "six degrees of separation" allows fast routing through geographic clusters of vectors.
M-factor range: 8 to 64 (Default = 16)
Google Cloud
2. Greedy Traversal & Drop Down
At each layer, the algorithm hops until reaching a local minimum, then "drops down" vertically to the next denser layer to refine search.
3. Dense Layer 0 Convergence (Full Mesh)
The path terminates at Layer 0, where the full, dense 2M graph mesh resides. Only a tiny fraction (~0.1%) of total nodes is ever evaluated.
1. Sparse Upper Layers (Layer 2 - Express)
Traversal begins on highly sparse proximity graphs where search jumps greedily across massive distances toward the closest candidate node.
Multi-Layer Geometric Routing
β’ Top express highway rapidly guides search toward target proximity cluster.
β’ Dotted vertical lines link identical physical vectors maintained across layers.
β’ Layer 0 provides dense connectivity (2M edges) for high-recall nearest neighbor clustering.
HNSW: Multi-Layer Search
High-Dimensional Traversal in Action: Combining Skip Lists with Proximity Graphs
1. Top Layer Greedy Routing (Layer 2 - Express)
β’ Start at global Entry Point E; evaluate distance to query target Q.
β’ Step greedily to neighbor A because dist(A, Q) < dist(E, Q).
β’ No closer neighbors exist at Layer 2 β Drop down vertically to Layer 1.
2. Intermediate Refinement (Layer 1 - Regional)
β’ Resume traversal from node A at Layer 1 with denser connectivity.
β’ Discover neighbor B closer to Q; advance greedily A β B.
β’ Local minimum reached on Layer 1 β Drop down vertically to Layer 0.
3. Base Layer Convergence (Layer 0 - Dense Mesh)
β’ Switch from greedy (ef=1) to candidate queue exploration (ef_search=40).
β’ Maintain pairing heaps C (candidates) and W (dynamic nearest pool).
β’ Explore 2M dense local edges around B to return exact top-k neighbors.
β‘ Traversal Efficiency: In a 10M vector index, multi-layer routing evaluates only ~0.005% of nodes (~200 distance checks vs 10,000,000 brute-force calculations), delivering sub-5ms latency.
Google Cloud
HNSW Search Path: Step-by-Step Traversal
Visualizing Greedy Multi-Layer Routing from Entry Point to Nearest Neighbors
HNSW Storage: The Logical Tuple Split
Disk Serialization of pgvector Graph Structures
HnswElementTupleData
Core Vector & Metadata
β’ Type ID: 1-byte identifier (type = 1)
β’ Max Layer: 1-byte level indicator
β’ Deleted Flag: 1-byte VACUUM state tracker
β’ Version: 1-byte, 4-bit concurrent ver
β’ heaptids Array: 60-byte reference array blocks duplicate graph bloat
β’ neighbortid: 6-byte direct disk pointer to associated adjacency list
β’ Payload: Actual vector (varlena + float4)
Physical Data Model Connection
Relating the Core Node to the Graph Edge Adjacency List on Disk
HnswNeighborTupleData
Graph Edges (Adjacency)
β’ Type ID: 1-byte identifier (type = 2)
β’ Version: 1-byte (strictly matches element)
β’ Edge Count: 2-byte count
β’ indextids Array: Sequentially packs the adjacency list descending Layer L to 0
β’ Layers L to 1: Allocated M slots each
β’ Layer 0 (Base): Allocated 2*M slots for maximum graph connectivity on disk
Google Cloud
HNSW Storage: The Physical 8 KB Slotted Page Layout
Co-Located Tuple Anatomy & 8 KB PostgreSQL Slotted Page Layout
HnswElementTupleData (Element Tuple)
β’ Type ID: 1-byte identifier (type = 1 for Element)
β’ Max Layer / Deleted / Version: State metadata tracks HNSW level & VACUUM
β’ heaptids Array: 10-slot reference array blocks duplicate graph bloat
β’ neighbortid: 6-byte disk pointer targeting the co-located adjacency list
β’ Payload: actual vector float array (varlena metadata + float4 coordinates)
HnswNeighborTupleData (Neighbor Tuple)
β’ Type ID: 1-byte identifier (type = 2 for Neighbor)
β’ Version / Edge Count: Matches associated element page verification
β’ indextids Array: Sequentially maps adjacency list descending from Layer L to 0
β’ Upper Layers L to 1: Allocated M slots each for fast routing expressways
β’ Base Layer 0: Allocated 2*M slots for high-recall dense graph connectivity
neighbortid ptr
PostgreSQL Slotted 8 KB Page (BLCKSZ = 8192)
PageHeaderData (24 bytes) [pd_lsn, pd_checksum, pd_lower, pd_upper]
ItemId[0]
ItemId[1] (Element)
ItemId[2] (Neighbor)
pd_lower (grows down)
FREE SPACE
Accommodates additional elements / adjacency growth
pd_upper (grows up)
Tuple 2: HnswNeighborTupleData
Graph Edge list (adjacency list representation packed sequentially)
Tuple 1: HnswElementTupleData
Node metadata (type ID, level, deleted states, heaptids array, payload)
HnswPageOpaqueData (8 bytes) [pd_special space]
nextblkno linked list block pointer (0xFF90 index verifier)
Google Cloud
The Storage Dilemma: B-Tree vs. HNSW on Disk
Why traditional indexes degrade gently on disk, while vector graphs collapse by 100x
π Analogy: Like reading an alphabetized dictionary
B-Tree: Predictable & Forgiving on Disk
β’ Sorted & Organized: Data sits in a clean, predictable line. Related items share the same page.
β’ Shallow Tree: Finding any record requires only 1 to 2 disk reads, even across 100M rows.
β’ Gentle Slowdown: If RAM runs out, queries slow down gently from 1 ms β 5 ms (barely noticeable).
β’ Disk Friendly: Operating systems easily prefetch adjacent pages from disk in advance.
πΊοΈ Analogy: Like a scavenger hunt across random buildings
HNSW: Fast in RAM, Fragile on Disk
β’ Scattered Everywhere: High-dimensional vectors have no order. Neighbors live on random disk pages.
β’ Massive Random Reads: A single search hops across 50 to 100+ random pages scattered across disk.
β’ The Latency Cliff: If RAM runs out, queries collapse from 2 ms β 250+ ms (100x slower!).
β’ Freezes the Database: 20 queries/sec demand thousands of disk reads, saturating disk IOPS.
π‘ THE GOLDEN RULE FOR PRODUCTION PGVECTOR:
β’ In RAM: Ultra-fast (2β5 ms). Graph hops take microseconds.
β’ On Disk: Catastrophic 100x slowdown (250β800+ ms) due to dozens of random SSD stalls.
β’ Key Takeaway: B-Trees tolerate slow disks. HNSW strictly requires enough RAM to hold the entire index.
Google Cloud
Major Production Scaling Challenges
Hurdles of Deploying pgvector HNSW at Scale
Google Cloud
1. Memory Pressure
2. Index Build Bottlenecks
3. Recall Decay
4. Filtering Trap
Google Cloud
Playbook: RAM Sizing & Capacity Planning
Sizing Formulas & Memory Architecture: Build-Time vs. Query-Time Split
HNSW Working Set Sizing Formula
(D Γ 4B + M Γ 3 Γ 4B) Γ Total_Rows
β’ Vector Payload: D Γ 4 Bytes (raw float4 array)
β’ Graph Pointers: M Γ 12 Bytes (avg ItemPointer per edge)
β’ Overhead Factor: 1.25x multiplier for page headers & bloat
β’ Goal: Index MUST fit entirely in shared_buffers + OS cache!
Worked Example: 1M Vectors (768D, M=32)
β’ Vector Data: 1M Γ (768 Γ 4B) = 3.07 GB
β’ Edge Pointers: 1M Γ (32 Γ 12B) = 0.38 GB
β’ Base Raw Total: 3.45 GB
Allocated Footprint (8 KB framing): ~4.30 GB
Query Time: RAM Residency & Degradation
Requirement & Root Cause: The entire HNSW index must fit inside memory; otherwise queries suffer a catastrophic 50x-100x latency collapse (2-5 ms in RAM β 250-800+ ms on SSD).
Because each graph hop forces random 8 KB SSD page reads across unordered high-dimensional clusters, quickly saturating disk IOPS.
Build Time: Two-Stage Build Mechanism
Stage 1 (In-Memory): Constructs graph in RAM using fast pointer swaps (~1,500-2,500 vecs/sec) within maintenance_work_mem.
Stage 2 (On-Disk): If memory exceeded, FlushPages() writes to disk via HnswInsertTupleOnDisk with buffer locks & random I/O.
β Scale up maintenance_work_mem & max_parallel_maintenance_workers.
π¨ 15xβ30x BUILD SLOWDOWN TRAP:
In-memory: ~8β12 mins. Spilling to disk: 3 to 5 hours! Temporarily raise maintenance_work_mem for CREATE INDEX.
OPERATIONAL PLAYBOOK β’ SLIDE 16
Playbook: Vector Memory Optimization Levers
Slashing RAM Footprint and Accelerating Builds with halfvec and bitvec Quantization
LEVER 1: HALFVEC
β Standard
β’ 50% Coordinate Compression: 32-bit float4 β 16-bit half-float. 768D vector shrinks from 3,072 B to 1,536 B.
β’ ~40% Total Index Sizing: 1M index drops from ~4.30 GB to ~2.50 GB (edge pointers remain 12B ItemPointers).
β’ 3x Faster Builds & Fidelity: Relieves memory bandwidth; builds 3x faster with >99.9% recall parity.
CREATE TABLE items ( id BIGSERIAL PRIMARY KEY, embedding halfvec(768) );
LEVER 2: BITVEC (1-BIT QUANTIZATION)
β‘ Extreme Scale
β’ 32x Raw Vector Compression: 768 dimensions compress to just 96 Bytes (1M payload = 96 MB; total index ~0.65 GB).
β’ Bit Conversion Rule: Negative values (-ve) mapped to 0, positive values (+ve) mapped to 1.
β’ 2-Stage Retrieval Pipeline: Coarse filter on bit index (<~>) for top 100 in <1 ms, then re-rank with heap float4 vectors.
CREATE INDEX ON items USING hnsw ((binary_quantize(embedding)) bit_hamming_ops);
ARCHITECTURAL METRIC
Standard (vector float4)
Optimized (halfvec FP16)
Quantized (bitvec BQ + Re-rank)
Vector Payload (768D)
3,072 Bytes (1.0x)
1,536 Bytes (0.5x, -50%)
96 Bytes (0.03x, 32x smaller)
1M Total Index RAM
~4.30 GB (Baseline)
~2.50 GB (-40% RAM)
~0.65 GB (-85% RAM)
1M Build Duration
~10β15 mins
~3.5β5 mins (~3x faster)
~1β2 mins (~8x faster)
Recall Fidelity
100% (Ground Truth)
>99.9% (Practically Identical)
90β95% raw / 99%+ with Re-rank
Google Cloud Platform
16 of 18
Tuning HNSW Index Parameters
Parameter | Applicable during | Impact on Increasing the parameter value | |
Benefit | Cost | ||
m | Index Build | Give higher recall and/or Support higher dimensionality | Increased memory usage and Query time |
ef_construction | Index Build | Improved recall (up to an extent) | Increased index build time |
ef_search | Search Query | Higher recall | Lower QPS |
OPERATIONAL PLAYBOOK β’ ANN INDEX ARCHITECTURE
Make Conscious Tradeoffs in ANN Indexes
In B-Tree indexes it is a binary "Yes/No" decision. In ANN indexes it is a spectrum with multi-axis tradeoffs.
THE ANN TRADEOFF SPECTRUM
β’ Spectrum vs. Binary: B-Tree indexes offer fixed guarantees. ANN indexes trade full accuracy for massive performance gains at scale.
β’ Recall vs. Throughput (QPS): Increasing search parameters yields higher recall but decreases queries per second.
β’ Memory Footprint: Higher graph connectivity (m) improves navigation quality but inflates RAM usage.
β’ Build Time Cost: Higher ef_construction generates better graph structures at the expense of longer indexing time.
Key Takeaway: Tune parameters dynamically based on whether your SLA prioritizes latency, memory budget, or recall fidelity.
Google Cloud Platform
Playbook: Solving the Filter Trap
Overcoming Native Post-Filtering Limitations with Modern Scan & Partitioning Strategies
THE CORE CHALLENGE: NATIVE POST-FILTERING LIMITATION
pgvector natively supports post-filtering only (evaluating WHERE clauses after fetching top-K vector neighbors) rather than true pre-filtering during graph traversal. This causes severe filter starvation or forces slow brute-force B-tree scans.
THE EFFECT
1. The Zero-Results Trap
Post-filtering limits results globally before pruning rows, causing empty queries even if matching rows exist.
SELECT * FROM orders WHERE price < 3000 ORDER BY embedding <=> $query LIMIT 5;
B-tree pre-filtering crashes throughput with brute-force scans.
SOLUTION 1
2. Iterative Index Scans
Introduced in pgvector 0.8.0+, dynamically continues traversing the HNSW graph until target rows passing filters are fully satisfied.
SET hnsw.iterative_scan = 'relaxed_order';
Ensures correct limits without brute forcing the search.
Google Cloud
SOLUTION 2
3. Declarative Partitioning
Upfront physical separation isolates HNSW indexes on individual partitions, eliminating cross-category pollution completely.
PARTITION BY LIST (category)
Postgres confines graph traversal strictly inside target partition.
OPERATIONAL PLAYBOOK β’ CONCLUSION
Thank You!
Questions & Discussion | Production-Grade Vector Search in PostgreSQL
Google Cloud Platform