SQL 122 🛢️ Vector Search and Embeddings in SQL (pgvector, HNSW indexes)

Vector search brings the core operation of modern AI applications—finding items that are semantically similar—into the relational database. An embedding is a high-dimensional vector of floating-point numbers that represents the meaning of text, an image, or any other data. Two embeddings that are close in vector space represent items with similar meaning. pgvector is the PostgreSQL extension that adds a vector data type, distance operators, and approximate nearest-neighbor indexes to the database . The result is that a RAG pipeline, a recommendation system, or a semantic search feature can store its embeddings alongside its relational data and query them with SQL, without introducing a separate vector database.

This chapter covers the vector type, the distance operators that drive similarity search, the HNSW index that makes the search fast, and the parameter tuning that balances recall against latency and memory.

Key point: The HNSW index is an approximate index. It trades a small amount of recall for a large amount of speed. The parameters m and ef_construction control the index build; ef_search controls the query. Raising ef_search improves recall at the cost of latency, and that is the knob to turn first when a query returns fewer relevant results than expected .

---

Why vector search in SQL exists

The separate-database problem. A common architecture for semantic search uses an external vector database alongside PostgreSQL: relational data in Postgres, embeddings in Pinecone or Qdrant, and an application layer that joins the two. This works, but it introduces a second system to operate, a second consistency model to reason about, and a network hop between the two. pgvector exists to collapse that architecture into one system. The embeddings live in a table column, the similarity query is a SQL ORDER BY, and the join between the embedding and its metadata is a normal join .

The competitive performance problem. The assumption that a dedicated vector database is always faster does not hold at moderate scale. A 2026 benchmark on 10,000 Amazon product embeddings found that pgvector with a tuned HNSW index achieved 5.71 ms p50 semantic search latency and 1,212 queries per second under 10 concurrent users, outperforming Elasticsearch on every local category . At 100 million vectors the picture changes—dedicated systems like Qdrant and Weaviate pull ahead—but for many applications the dataset is small enough that the operational simplicity of pgvector outweighs the raw throughput of a specialized system .

The approximate-index problem. Scanning every row to compute the distance to the query vector is exact but slow. An approximate nearest-neighbor index like HNSW (Hierarchical Navigable Small World) builds a graph over the vectors and navigates it to find likely neighbors without visiting every row. The trade-off is that the result may miss some true neighbors, but the recall is typically above 95% with tuned parameters, and the speed improvement is orders of magnitude .

---

a. The vector Type and Distance Operators

The extension is enabled with CREATE EXTENSION vector. The extension’s binary name is vector, not pgvector, which is the name the community uses . The vector type is declared with a fixed dimension, which must match the dimension of the embedding model that produced the vectors.

-- Enable the extension
CREATE EXTENSION vector;

-- A table with a 3-dimensional vector column
CREATE TABLE items (
    id bigserial PRIMARY KEY,
    name text,
    embedding vector(3)
);

The dimension is part of the type definition. A vector(3) column accepts only 3-element vectors. The dimension should match the embedding model’s output: all-MiniLM-L6-v2 produces 384 dimensions, text-embedding-ada-002 produces 1536, and so on .

Similarity search uses distance operators in the ORDER BY clause. The operator determines the distance metric, and the ordering is ascending because smaller distance means greater similarity.

Operator	Metric	Use When
<->	Euclidean (L2)	General-purpose distance
<#>	Negative inner product	Vectors are normalized
<=>	Cosine distance	Vectors are not normalized

-- Find the 5 most similar items by Euclidean distance
SELECT id, name, embedding <-> '[3,1,2]' AS distance
FROM items
ORDER BY embedding <-> '[3,1,2]'
LIMIT 5;

The query computes the distance for every row, sorts by it, and returns the closest five. Without an index, this is a sequential scan that computes the distance for every row in the table. The index exists to avoid that scan .

The choice of metric depends on the embedding model. For normalized vectors, inner product and cosine distance produce the same ranking. For unnormalized vectors, cosine distance is the safer choice because it is invariant to magnitude. Supabase’s documentation recommends inner product for normalized vectors and cosine for unnormalized ones .

---

b. HNSW Indexes and Build Parameters

pgvector supports two index types: IVFFlat and HNSW. The recommendation from Supabase, and the consensus in the community, is HNSW because of its performance and robustness against changing data . IVFFlat requires a training step and performs poorly when the data distribution shifts; HNSW builds incrementally and maintains its performance as rows are added.

The HNSW index is created with USING hnsw and an operator class that matches the distance metric.

-- HNSW index for Euclidean distance
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

-- HNSW index for cosine distance
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

The index build is controlled by two parameters. m is the number of bi-directional links created for each new element. Higher m produces a better graph and higher recall, but the index consumes more memory. The default is 16, and the useful range is 12 to 48 . ef_construction is the size of the dynamic candidate list used during construction. Higher values produce a better index at the cost of longer build time. The default is 64, and it must be at least twice m .

-- Tuned HNSW index for a larger dataset
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops)
WITH (m = 24, ef_construction = 128);

A community tuning report on a 100K+ vector dataset found that raising m from 16 to 24 and ef_construction from 64 to 128 doubled QPS while improving recall from 0.95 to 0.998 . The trade-off is a larger index and a longer build time. For a dataset with high dimensionality or high accuracy requirements, the higher values are worth the cost.

---

c. Query-Time Tuning with ef_search

ef_search is the query-time parameter. It controls the size of the dynamic candidate list during the search. Higher values explore more of the graph and find more true neighbors, but they increase latency. The default is 40 .

-- Set the search parameter for the session
SET hnsw.ef_search = 100;

The effect is direct: a higher ef_search improves recall, a lower value improves speed. The tuning process is to set a recall target, measure recall at different ef_search values, and choose the smallest value that meets the target. The same 100K-vector tuning report set ef_search to 100 and achieved a recall of 0.998 with roughly 40 QPS, compared to the default configuration’s 0.95 recall and 20 QPS .

A subtle interaction exists between filtering and HNSW. When a query filters on a metadata column alongside the vector search, the index may return fewer rows than requested because the filter removes some of the approximate results.

-- This may return fewer than 5 rows if the filter removes matches
SELECT * FROM items
WHERE category_id = 123
ORDER BY embedding <-> '[3,1,2]'
LIMIT 5;

Supabase’s documentation explains the cause: the HNSW index returns the approximate nearest neighbors, and the filter is applied afterward. If some of those neighbors do not match the filter, the result set is smaller than the limit. The fix is iterative search, which continues scanning the index until enough filtered results are found . PostgreSQL 0.8.0 and later supports this through hnsw.iterative_scan.

---

Complete Example Session

-- ============================================
-- PART 1: ENABLE THE EXTENSION
-- ============================================
CREATE EXTENSION vector;


-- ============================================
-- PART 2: CREATE A TABLE WITH AN EMBEDDING COLUMN
-- ============================================
-- 384 dimensions matches all-MiniLM-L6-v2
CREATE TABLE documents (
    id bigserial PRIMARY KEY,
    content text,
    embedding vector(384)
);


-- ============================================
-- PART 3: INSERT EMBEDDINGS
-- ============================================
-- The vectors are generated by an embedding model
-- and inserted as array literals
INSERT INTO documents (content, embedding) VALUES
('PostgreSQL is a relational database', '[0.1, 0.2, ...]'),
('Vector search finds similar items', '[0.3, 0.1, ...]');


-- ============================================
-- PART 4: SIMILARITY SEARCH WITHOUT INDEX
-- ============================================
-- Exact search: scans every row
SELECT id, content, embedding <=> '[...]' AS distance
FROM documents
ORDER BY embedding <=> '[...]'
LIMIT 5;


-- ============================================
-- PART 5: CREATE AN HNSW INDEX
-- ============================================
-- Use cosine distance for unnormalized embeddings
CREATE INDEX documents_embedding_idx
ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 24, ef_construction = 128);


-- ============================================
-- PART 6: TUNE THE SEARCH PARAMETER
-- ============================================
-- Higher ef_search improves recall at the cost of latency
SET hnsw.ef_search = 100;


-- ============================================
-- PART 7: INDEXED SEARCH
-- ============================================
-- The same query now uses the HNSW index
SELECT id, content, embedding <=> '[...]' AS distance
FROM documents
ORDER BY embedding <=> '[...]'
LIMIT 5;


-- ============================================
-- PART 8: FILTERED SEARCH (CAVEAT)
-- ============================================
-- The filter is applied after the index returns candidates
-- The result may contain fewer rows than LIMIT
SELECT id, content
FROM documents
WHERE content ILIKE '%database%'
ORDER BY embedding <=> '[...]'
LIMIT 5;


-- ============================================
-- PART 9: ITERATIVE SCAN FOR FILTERED SEARCH
-- ============================================
-- Continue scanning until enough filtered rows are found
SET hnsw.iterative_scan = relaxed_order;


-- ============================================
-- PART 10: VERIFY INDEX USAGE
-- ============================================
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM documents
ORDER BY embedding <=> '[...]'
LIMIT 5;
-- Look for "Index Scan using documents_embedding_idx"

The ten parts trace the setup, the exact search, the index creation, the tuning, and the filtered-search caveat. Part 8 shows the query that can return fewer rows than requested; Part 9 shows the setting that fixes it.

---

Quick Reference

pgvector Distance Operators

Operator	Metric	Index Opclass
<->	Euclidean (L2)	vector_l2_ops
<#>	Negative inner product	vector_ip_ops
<=>	Cosine distance	vector_cosine_ops

HNSW Parameters

Parameter	Default	Effect of Increasing
m	16	Better recall, more memory
ef_construction	64	Better index, longer build
ef_search	40	Better recall, higher latency

Index Type Comparison

Index	Build	Updates	Recommendation
HNSW	Incremental	Handles well	Recommended
IVFFlat	Requires training	Degrades with change	Avoid for dynamic data

Common Commands

Command	Purpose
CREATE EXTENSION vector	Enable the extension
CREATE INDEX ... USING hnsw	Create the index
SET hnsw.ef_search = N	Tune query recall
SET hnsw.iterative_scan = relaxed_order	Fix filtered search

---

Best Practices

✅ Do This:

-- Match the vector dimension to the embedding model
embedding vector(384)  -- all-MiniLM-L6-v2                            -- ✅

-- Use cosine distance for unnormalized embeddings
ORDER BY embedding <=> '[...]'                                        -- ✅

-- Tune ef_search to the recall target
SET hnsw.ef_search = 100;                                             -- ✅

-- Use HNSW for dynamic data
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);       -- ✅

❌ Don’t Do This:

-- Use IVFFlat for frequently updated data
-- Requires retraining and degrades                                -- ❌

-- Set ef_search to a very high value without measuring latency
SET hnsw.ef_search = 1000;  -- may be slower than exact scan          -- ❌

-- Assume filtered vector search returns LIMIT rows
-- The filter is applied after the index scan                      -- ❌

-- Use the wrong dimension for the embedding model
embedding vector(1536)  -- with 384-dim model                         -- ❌

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Index not used	Query uses a different operator than the index	Match opclass to distance operator
Fewer rows than LIMIT with filter	Filter applied after index scan	SET hnsw.iterative_scan
Slow index build	Low maintenance_work_mem	Increase for build session
High memory usage	Large m or many dimensions	Reduce m, use halfvec
Recall below target	ef_search too low	Increase ef_search

---

Real-World Examples

1. Enable Extension

CREATE EXTENSION vector;

2. Create Vector Column

CREATE TABLE items (embedding vector(384));

3. Insert Embedding

INSERT INTO items (embedding) VALUES ('[0.1, 0.2, ...]');

4. Exact Search

SELECT * FROM items ORDER BY embedding <-> '[...]' LIMIT 5;

5. Create HNSW Index

CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

6. Tuned Index

CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops) WITH (m = 24, ef_construction = 128);

7. Set ef_search

SET hnsw.ef_search = 100;

8. Filtered Search Fix

SET hnsw.iterative_scan = relaxed_order;

9. Check Index Usage

EXPLAIN SELECT * FROM items ORDER BY embedding <-> '[...]' LIMIT 5;

10. Half-Precision Vector

-- Reduces memory at the cost of a small recall loss
embedding halfvec(384)

---

Visual

The Vector Search Query

┌──────────────────────────────────────────────────────────────┐
│  QUERY VECTOR: [0.3, 0.1, ...]                               │
│                                                              │
│  ┌─────────────────────────────────────────────────────────┐│
│  │ TABLE                                                   ││
│  │ ┌─────────┬─────────────────────────────┬─────────────┐ ││
│  │ │ id      │ embedding                   │ distance    │ ││
│  │ ├─────────┼─────────────────────────────┼─────────────┤ ││
│  │ │ 1       │ [0.1, 0.2, ...]             │ 0.12        │ ││
│  │ │ 2       │ [0.3, 0.1, ...]             │ 0.03        │ ││
│  │ │ 3       │ [0.9, 0.8, ...]             │ 0.87        │ ││
│  │ └─────────┴─────────────────────────────┴─────────────┘ ││
│  └─────────────────────────────────────────────────────────┘│
│                                                              │
│  ORDER BY embedding <=> query                                │
│  LIMIT 5                                                     │
│                                                              │
│  Smallest distance = most similar.                           │
│  The index makes the scan approximate and fast.              │
│                                                              │
└──────────────────────────────────────────────────────────────┘

HNSW Graph Structure

┌──────────────────────────────────────────────────────────────┐
│  HNSW GRAPH (conceptual, simplified)                         │
│                                                              │
│  Layer 2 (sparse):                                           │
│  ┌─────────────────────────────────────────────────────────┐│
│  │     A ──────────────── B                                ││
│  └─────────────────────────────────────────────────────────┘│
│                                                              │
│  Layer 1:                                                    │
│  ┌─────────────────────────────────────────────────────────┐│
│  │     A ───── C ───── B                                   ││
│  │     │       │       │                                   ││
│  │     D ───── E ───── F                                   ││
│  └─────────────────────────────────────────────────────────┘│
│                                                              │
│  Layer 0 (dense):                                            │
│  ┌─────────────────────────────────────────────────────────┐│
│  │  All vectors, each connected to m nearest neighbors     ││
│  └─────────────────────────────────────────────────────────┘│
│                                                              │
│  Search starts at the top layer and descends.                │
│  m = number of connections per node.                         │
│  ef_search = number of candidates explored per layer.        │
│                                                              │
└──────────────────────────────────────────────────────────────┘

Recall vs. Latency Trade-off

┌──────────────────────────────────────────────────────────────┐
│  ef_search                                                   │
│                                                              │
│  ┌─────────────────────────────────────────────────────────┐│
│  │ LOW (10-20)     HIGH (100-200)                          ││
│  │                                                         ││
│  │ Fast            Slow                                    ││
│  │ Low recall      High recall                             ││
│  │ (misses some    (finds most                             ││
│  │  true neighbors) true neighbors)                        ││
│  └─────────────────────────────────────────────────────────┘│
│                                                              │
│  Tuning process:                                             │
│  1. Set recall target (e.g., 0.95)                           │
│  2. Measure recall at different ef_search values             │
│  3. Choose smallest value that meets target                  │
│                                                              │
│  Default 40 is a starting point, not an optimum.             │
│                                                              │
└──────────────────────────────────────────────────────────────┘

Filtered Search Caveat

┌──────────────────────────────────────────────────────────────┐
│  QUERY WITH FILTER                                           │
│  WHERE category_id = 123                                     │
│  ORDER BY embedding <-> query                                │
│  LIMIT 5                                                     │
│                                                              │
│  ┌─────────────────────────────────────────────────────────┐│
│  │ 1. HNSW returns top 5 approximate neighbors             ││
│  │ 2. Filter is applied to those 5                         ││
│  │ 3. Only 2 match the filter                              ││
│  │ 4. Result: 2 rows, not 5                                ││
│  └─────────────────────────────────────────────────────────┘│
│                                                              │
│  FIX: SET hnsw.iterative_scan = relaxed_order;               │
│  The scan continues until enough filtered rows are found.    │
│                                                              │
└──────────────────────────────────────────────────────────────┘

---

Summary

Item	Value
Extension	pgvector (binary name vector)
Enable	CREATE EXTENSION vector
Vector type	vector(dimensions)
Euclidean operator	<->
Inner product operator	<#>
Cosine operator	<=>
Index type	HNSW (recommended)
HNSW m default	16
HNSW ef_construction default	64
HNSW ef_search default	40
Filtered search fix	SET hnsw.iterative_scan

Key takeaways:

• pgvector brings vector similarity into SQL. The embedding is a column, the similarity is an ORDER BY, and the metadata is a normal join. This collapses the separate vector database architecture into a single system for datasets at moderate scale .
• HNSW is an approximate index with tunable recall. The m and ef_construction parameters control the index build. Higher values produce a better graph at the cost of memory and build time. The ef_search parameter controls the query. Higher values improve recall at the cost of latency .
• The distance operator must match the index opclass. An index created with vector_cosine_ops is used by queries that order by <=>. A query that orders by <-> will not use that index. The metric should match the embedding model’s training .
• Filtered vector search can return fewer rows than requested. The HNSW index returns approximate neighbors first, and the filter is applied afterward. If some neighbors do not match, the result is smaller than LIMIT. The hnsw.iterative_scan setting fixes this by continuing the scan until enough filtered rows are found .
• pgvector is competitive at moderate scale but not at massive scale. A 10K-vector benchmark showed it outperforming Elasticsearch on local metrics. At 100M vectors, dedicated systems like Qdrant and Weaviate pull ahead on throughput and latency. The choice depends on the dataset size and the operational cost of a second system .

Remember: Vector search in SQL is not a separate query language. It is a distance operator in an ORDER BY clause and an index that makes the ordering fast without scanning every row. The embedding is just another column, and the similarity is just another sort key. The complexity is in the tuning: the HNSW parameters determine how much recall the index sacrifices for speed, and the filtered-search caveat determines whether the query returns the number of rows it promises. For applications that already run PostgreSQL and have embeddings in the hundreds of thousands or low millions, pgvector removes the need for a second database without giving up the query performance that semantic search requires.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!