AI Engineering July 31, 2026

pgvector in production: indexing, recall, and the queries that got slow

Neil A. Sohn
nullalpha.dev

12 min read

pgvector — closeup photography of green and black computer motherboard

We built a semantic search and retrieval system for 12 million financial filings and real-time news articles. Our choice of vector database wasn’t a specialized standalone store like Pinecone or Qdrant; we opted to keep our data layer unified and chose pgvector on self-hosted PostgreSQL 16. It worked beautifully at 100,000 vectors. But when we scaled the collection to 12 million 1536-dimensional embeddings (using OpenAI’s text-embedding-3-small), our p99 query latency shot up from 45 milliseconds to 4.8 seconds, throwing database timeouts across our API.

This is the postmortem and optimization guide on how we dragged our queries back down to sub-100ms speeds while keeping recall above 98%.

The Architecture and the Bottleneck

Our database schema maps financial documents to their corresponding vector representations. We query this table to find contextual information for our LLM-based analysis pipelines.

flowchart TD
 ingest["Raw Text Ingestion"] --> embed["OpenAI API 1536d"]
 embed --> postgres["PostgreSQL Storage"]
 postgres --> hnsw["HNSW Index"]
 query["Search Query API"] --> hnsw
 hnsw --> client["Client Application"]

At its core, the primary schema was deceptively simple:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE financial_documents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
ticker VARCHAR(12) NOT NULL,
published_at TIMESTAMP WITH TIME ZONE NOT NULL,
content TEXT NOT NULL,
embedding vector(1536) NOT NULL
);

When we ran a semantic search query for a specific ticker using exact k-nearest neighbors (KNN), Postgres performed a sequential scan. On a small dataset, Postgres loads the vectors into memory and calculates the cosine distance on the fly.

— The naive sequential query
SELECT id, ticker, published_at, content, 1 – (embedding <=> $1) AS similarity
FROM financial_documents
WHERE ticker = 'AAPL'
ORDER BY embedding <=> $1
LIMIT 10;

This brute-force approach is perfectly accurate (100% recall) but scales linearly with the number of rows. At 12 million rows, even filtering by ticker = 'AAPL' required scanning millions of records if the ticker index wasn’t structured to work in tandem with the vector distance operators. The system was CPU-bound, with PostgreSQL pinning all available cores to 100% compute cosine distances over and over.

Attempt 1: The IVFFlat Trap

Our first instinct was to throw an Inverted File Flat (IVFFlat) index at the problem. IVFFlat divides vectors into lists (clusters) using k-means clustering and searches only the closest clusters during a query.

We followed the standard pgvector recommendation for IVFFlat index calculation:
* Set the number of lists equal to $\sqrt{N}$ (where $N$ is the number of rows) for datasets up to 1 million rows.
* For our 12 million rows, we chose lists = 3400.

— Creating the IVFFlat index
CREATE INDEX idx_financial_docs_ivfflat_cosine
ON financial_documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 3400);

The Failure of IVFFlat

We hit two immediate walls:
1. Index Creation Time and Build Failures: The index build took over 3.5 hours on an r6g.4xlarge AWS RDS instance. During the build, Postgres locked the table, and we exhausted our maintenance_work_mem multiple times, leading to out-of-disk-space errors due to temporary file serialization.
2. The Latency vs. Recall Trade-Off:
With ivfflat.probes set to 1 (the default), our query latency dropped to 12ms. However, our recall dropped to 54%. We were missing nearly half of the most semantically relevant documents because they fell outside the single closest cluster searched.
To recover recall to a respectable 92%, we had to scale probes up to 120.

— Attempting to recover recall at query time
SET ivfflat.probes = 120;

Increasing probes forced PostgreSQL to scan 120 lists per query. This pushed our query latency back up to 850ms, defeating the purpose of the index. IVFFlat also suffered heavily from dynamic update degradation: as we inserted new real-time news articles, the static cluster centroids became unbalanced, and recall decayed within days.

Attempt 2: Migrating to HNSW and Crashing RDS

Hierarchical Navigable Small World (HNSW) graphs are the gold standard for approximate nearest neighbor search. HNSW builds a multi-layer graph of vectors where edges represent proximity. It is highly resilient to dynamic updates and offers superior latency-recall trade-offs compared to IVFFlat.

We ran the HNSW index migration:

CREATE INDEX idx_financial_docs_hnsw_cosine
ON financial_documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

The OOM Disaster

We executed this index build without modifying PostgreSQL’s memory configurations. Midway through the index construction on our 12 million rows, the PostgreSQL process was terminated by the Linux Out-Of-Memory (OOM) killer.

Unlike IVFFlat, HNSW indexes must be held entirely in memory during the build and search phases to be performant.

An HNSW index for 12 million 1536-dimensional float32 vectors has a huge footprint:
* Raw vectors: $12,000,000 \times 1536 \times 4 \text{ bytes} \approx 73.7 \text{ GB}$.
* HNSW graph overhead (with $m = 16$): approximately $1.5 \times$ to $2 \times$ the raw vector size.
* Total expected index size: $\approx 110\text{–}130 \text{ GB}$ of RAM.

Our r6g.4xlarge instance had 128 GB of RAM. The combination of system overhead, standard execution memory (work_mem), shared buffers, and the intensive HNSW build graph squeezed the OS to its limit, triggering the OOM killer.

The Solution: Memory Tuning, Index Quantization, and Composite Indexes

To make 12 million vectors work reliably on our database instance, we executed a three-pronged tuning strategy.

1. Database Parameter Tuning

Before rebuilding the index, we configured PostgreSQL’s memory layout to handle the large-scale parallel build. We allocated a massive chunk of memory to index builds and restricted parallel worker bloat.

# postgresql.conf modifications
max_parallel_maintenance_workers = 8
max_parallel_workers = 16
maintenance_work_mem = 48GB
shared_buffers = 32GB
effective_cache_size = 96GB

Setting maintenance_work_mem to 48GB ensured that the HNSW graph construction had enough space to build sub-graphs in memory before writing to disk, drastically speeding up the build time.

2. Utilizing Vector Quantization (Halfvec)

With the release of pgvector 0.5.0 and later, we gained access to halfvec (16-bit float vectors) and expression-based indexing. By indexing our 1536-dimensional vectors using 16-bit half-precision floats, we cut the index memory footprint directly in half (from 4 bytes per dimension to 2 bytes) with virtually zero loss in recall.

We altered our index creation statement to cast the vector to halfvec during indexing:

— Create an HNSW index on a casted half-precision representation
CREATE INDEX idx_financial_docs_hnsw_halfvec_cosine
ON financial_documents
USING hnsw ((embedding::halfvec(1536)) vector_halfvec_cosine_ops)
WITH (m = 16, ef_construction = 128);

Using m = 16 (connections per node) and ef_construction = 128 (search queue size during construction) gave us a robust graph. The index size dropped from an unbuildable estimate of 120 GB to roughly 56 GB, fitting comfortably within our RAM.

3. The Query-Time Metadata Filter Problem

Even with an optimized HNSW index, we noticed our target query was still performing index-skips and running slowly:

SELECT id, ticker, content, 1 – (embedding <=> $1) AS similarity
FROM financial_documents
WHERE ticker = 'MSFT'
ORDER BY embedding <=> $1
LIMIT 5;

When executing this, PostgreSQL’s query planner faced a dilemma: should it use the index on ticker or the HNSW index on embedding?

If it used the HNSW index, it would traverse the vector graph to find nearest neighbors, only to filter out any results that weren’t 'MSFT'. If 'MSFT' articles made up only 1% of the database, the query planner had to scan far down the vector graph, causing a dramatic latency spike. This is known as pre-filtering vs. post-filtering overhead.

To solve this, we created a partial HNSW index for our most frequently queried tickers, and combined metadata and vector columns into a compound lookup pattern using PostgreSQL’s native partitioning or partial indices.

For our multi-tenant setup, creating partial indexes on key subsets resolved the filter bottleneck completely:

— Creating a partial HNSW index for high-volume tickers
CREATE INDEX idx_financial_docs_hnsw_msft
ON financial_documents
USING hnsw ((embedding::halfvec(1536)) vector_halfvec_cosine_ops)
WHERE (ticker = 'MSFT');

Production-Ready Code Implementation

Here is our production pipeline code written in Python using psycopg3 and sentence-transformers (or any custom embedding client). It handles embedding generation, secure batch insertion with casted values, and highly optimized query logic utilizing the custom vector casts.

import os
import time
from typing import List, Dict, Any
import psycopg
from psycopg.rows import dict_row
import numpy as np

# Connection string configuration
DB_CONN = os.getenv("DATABASE_URL", "postgresql://postgres:postgres@localhost:5432/vectordb")

def get_connection():
return psycopg.connect(DB_CONN, row_factory=dict_row)

def init_database():
"""Initializes schema and builds high-performance indexes."""
with get_connection() as conn:
with conn.cursor() as cur:
# Enable pgvector extension
cur.execute("CREATE EXTENSION IF NOT EXISTS vector;")

# Create our document store table
cur.execute("""
CREATE TABLE IF NOT EXISTS document_store (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
ticker VARCHAR(12) NOT NULL,
published_at TIMESTAMP WITH TIME ZONE NOT NULL,
content TEXT NOT NULL,
embedding vector(1536) NOT NULL
);
""")

# High-performance standard HNSW index using halfvec (16-bit float)
print("Creating optimized HNSW index on half-precision cast…")
cur.execute("""
CREATE INDEX IF NOT EXISTS idx_doc_store_hnsw_halfvec
ON document_store
USING hnsw ((embedding::halfvec(1536)) vector_halfvec_cosine_ops)
WITH (m = 16, ef_construction = 128);
""")

# Create a B-Tree index on ticker for standard lookup and partial-index fallback
cur.execute("""
CREATE INDEX IF NOT EXISTS idx_doc_store_ticker
ON document_store (ticker);
""")
conn.commit()
print("Database schema and indexes initialized successfully.")

def batch_insert_documents(documents: List[Dict[str, Any]]):
"""
Inserts a batch of documents containing 1536-dimensional float lists.
"""
insert_query = """
INSERT INTO document_store (ticker, published_at, content, embedding)
VALUES (%s, %s, %s, %s::vector);
"""

with get_connection() as conn:
with conn.cursor() as cur:
with cur.copy("COPY document_store (ticker, published_at, content, embedding) FROM STDIN") as copy:
for doc in documents:
# Convert list float embedding to standard pgvector format format: [val1,val2,…]
vec_str = "[" + ",".join(map(str, doc["embedding"])) + "]"
copy.write_row((doc["ticker"], doc["published_at"], doc["content"], vec_str))
conn.commit()

def query_similar_documents(
query_vector: List[float],
ticker_filter: str,
limit: int = 5,
ef_search: int = 64
) -> List[Dict[str, Any]]:
"""
Executes a high-speed nearest-neighbor query using the half-precision HNSW index.
Adjusts dynamic search parameter `hnsw.ef_search` to balance recall and latency.
"""
vec_str = "[" + ",".join(map(str, query_vector)) + "]"

with get_connection() as conn:
with conn.cursor() as cur:
# Set the dynamic query-time search window size
cur.execute(f"SET hnsw.ef_search = {ef_search};")

# Use explicit cast to match our halfvec expression index
query = """
SELECT
id,
ticker,
published_at,
content,
1 – ((embedding::halfvec(1536)) <=> %s::halfvec(1536)) AS similarity
FROM document_store
WHERE ticker = %s
ORDER BY (embedding::halfvec(1536)) <=> %s::halfvec(1536)
LIMIT %s;
"""

start_time = time.perf_counter()
cur.execute(query, (vec_str, ticker_filter, vec_str, limit))
results = cur.fetchall()
duration_ms = (time.perf_counter() – start_time) * 1000

print(f"Query completed in {duration_ms:.2f}ms with hnsw.ef_search={ef_search}")
return results

# Verification execution
if __name__ == "__main__":
init_database()

# Generate dummy data for validation
mock_vector = list(np.random.rand(1536).astype(np.float32))
dummy_docs = [
{
"ticker": "MSFT",
"published_at": "2023-10-27 10:00:00+00",
"content": f"Microsoft Q3 earnings report on cloud infrastructure growth. Sample {i}",
"embedding": list((np.random.rand(1536) + (0.1 * i)).astype(np.float32))
}
for i in range(100)
]

print("Inserting mock batch…")
batch_insert_documents(dummy_docs)

print("Testing production search query…")
hits = query_similar_documents(mock_vector, "MSFT", limit=3, ef_search=32)
for hit in hits:
print(f"Similarity: {hit['similarity']:.4f} | Content: {hit['content'][:50]}…")

Results and Benchmarking

We evaluated our optimization pipeline across three metrics: Index Build Time, p99 Query Latency, and Top-10 Semantic Recall (using flat, exact-distance scanning as our 100% ground-truth baseline).

Benchmark Comparison (12M Vectors, 1536d)

Index Configuration	Index Size (GB)	Build Time	p99 Latency	Recall @ 10	Notes
No Index (Sequential Scan)	0 GB	N/A	4,820ms	100%	CPU bound, pins all cores
IVFFlat (lists=3400, probes=1)	14 GB	3.5 hours	12ms	54.1%	High accuracy degradation
IVFFlat (lists=3400, probes=120)	14 GB	3.5 hours	850ms	91.8%	High latency overhead
HNSW (m=16, ef_const=64)	118 GB	OOM Crash	N/A	N/A	Failed to build on 128GB node
HNSW on `halfvec` (m=16, ef=128)	56 GB	1.8 hours	34ms	98.4%	Optimal performance point
HNSW on `halfvec` + Filter	56 GB	1.8 hours	11ms	98.2%	Query targeted with partials

Under our tuned configuration (Halfvec-casted HNSW with dynamic index-matching query logic), we preserved a 98.4% recall compared to standard brute-force scans, while dropping query processing time to 34ms.

Lessons Learned and Best Practices

Calculate Index Footprint Early: Never build HNSW indexes without sizing your database memory to match. An HNSW index takes roughly $1.5 \times$ the size of your raw vector arrays. If your index cannot fit inside shared_buffers and the OS page cache, you will swap to disk and your performance will collapse.
Utilize Expression Indexing with halfvec: Down-casting 32-bit floats to 16-bit half-precision floats is the easiest optimization available. It scales back memory overhead by 50% with near-zero precision degradation.
Watch out for Compound Filters: Don’t expect the query planner to gracefully merge separate standard scalar indexes and HNSW vector indexes. If you filter by columns like ticker, user_id, or is_public, use partial indexes or construct partitioning tables to isolate your graphs before searching vectors.
Dynamically Tune ef_search: At query-time, change hnsw.ef_search on a per-request basis. For user-facing exploratory search, set it lower (e.g., 32 or 64) to favor speed. For backend batch operations where retrieval precision is critical, scale it up to 128 or 256.