At 2 AM, PagerDuty started firing relentless alerts. Node.js APIs simultaneously threw 504 Gateway Timeouts while our production PostgreSQL cluster maxed out at 100% CPU. A quick check on pg_stat_activity and Grafana made the root cause immediately obvious: a similar product search query was running sequential scans across more than 450,000 unindexed vector rows, driving latency from 45ms to over 8,000ms per request.
If your team is building Semantic Search or RAG (Retrieval-Augmented Generation) and doesn’t want to spend thousands of dollars a month on Pinecone or Milvus, pgvector is the most practical choice. PostgreSQL can easily handle millions of vectors when properly configured. Here is how our team rescued the system that night and standardized our pipeline from the ground up.
1. Quick start: Dựng môi trường test trong 5 phút
During an active incident, don’t waste time compiling extensions from source. The fastest way to spin up a staging cluster or run local tests is using the official Docker image from the pgvector team.
Starting PostgreSQL with pgvector Using Docker
# Run a postgres container with pgvector
docker run -d \
--name postgres-vector \
-e POSTGRES_USER=appuser \
-e POSTGRES_PASSWORD=secretpassword \
-e POSTGRES_DB=vector_db \
-p 5432:5432 \
pgvector/pgvector:pg16Enabling the Extension and Creating the Schema
Connect to the database and enable the extension. Note: the vector(n) data type must strictly match the exact dimensions of your embedding model. For instance, OpenAI’s text-embedding-3-small outputs 1,536 dimensions, while local models like all-MiniLM-L6-v2 use only 384 dimensions.
-- Enable the pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;
-- Create a sample documents table with 1536-dimensional vectors
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT NOT NULL,
embedding vector(1536)
);Inserting Data and Querying Cosine Similarity
-- Insert test data
INSERT INTO documents (content, embedding)
VALUES ('Fix timeout error nodejs postgres', '[0.012, -0.023, 0.045, ...]'); -- 1536-dimensional vector
-- Retrieve top 5 closest documents by Cosine Distance (<=>)
SELECT id, content, 1 - (embedding <=> '[0.010, -0.020, 0.040, ...]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.010, -0.020, 0.040, ...]' ASC
LIMIT 5;2. How pgvector Works Under the Hood
pgvector adds the vector data type to PostgreSQL, along with geometric operators to compute distances between points in high-dimensional space.
3 Core Distance Operators
- L2 distance (Euclidean Distance)
<->: Measures straight-line distance between two vectors. Best suited when the absolute magnitude of vectors carries key information (common in computer vision). - Negative Inner Product
<#>: Negative dot product. Because PostgreSQL sorts by default inASCorder, this operator returns negative values so that vectors with the highest similarity appear first. It provides the fastest computation if vectors are already normalized (L2 norm = 1). - Cosine Distance
<=>: Measures the angle between two vectors, calculated as1 - cosine_similarity. Document length does not skew semantic meaning, making it the standard choice for natural language processing.
Integrating with a Node.js Backend
The standard pg driver in Node.js works seamlessly with pgvector. You simply need to serialize your float array into a PostgreSQL-compatible string format:
import pg from 'pg';
import OpenAI from 'openai';
const { Pool } = pg;
const pool = new Pool({
connectionString: process.env.DATABASE_URL || 'postgresql://appuser:secretpassword@localhost:5432/vector_db',
max: 20,
idleTimeoutMillis: 30000,
});
const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });
async function semanticSearch(userQuery, limit = 5) {
// 1. Generate an embedding from the user query
const embeddingResponse = await openai.embeddings.create({
model: 'text-embedding-3-small',
input: userQuery,
});
const queryVector = embeddingResponse.data[0].embedding;
// 2. Convert array into a string '[x1,x2,x3,...]'
const vectorString = JSON.stringify(queryVector);
// 3. Query top documents by cosine distance
const query = `
SELECT id, content, 1 - (embedding <=> $1::vector) AS similarity
FROM documents
ORDER BY embedding <=> $1::vector ASC
LIMIT $2;
`;
const client = await pool.connect();
try {
const res = await client.query(query, [vectorString, limit]);
return res.rows;
} finally {
client.release();
}
}
// Example run
semanticSearch('how to scale postgresql database')
.then(results => console.log('Search results:', results))
.catch(err => console.error('Query error:', err));3. Indexing: The Key to Saving Production from Meltdown
Why did a 450,000-row table bring our database down? By default without an index, the ORDER BY embedding <=> ... clause forces the database to perform an Exact Nearest Neighbor (KNN) sequential scan. With 50 concurrent requests, PostgreSQL had to compute vector distances 22.5 million times per second.
To slash latency from several seconds down to under 25ms, you must switch to Approximate Nearest Neighbor (ANN) via indexing.
IVFFlat or HNSW?
- IVFFlat (Inverted File Flat): Partitions vector space into clusters (Voronoi cells). Fast to build and memory-efficient. Major drawback: you must wait until the table is sufficiently populated before building an effective index, and recall drops significantly if data distribution is skewed.
- HNSW (Hierarchical Navigable Small World): Builds a multi-layer graph. It takes longer to build and consumes 1.5x to 2x more RAM than IVFFlat. In return, query latency is dramatically lower and recall consistently holds at 95% to 99%. For modern production systems, HNSW is almost always the top recommendation.
Recommended HNSW Index Configuration
-- Temporarily allocate more RAM to speed up index builds in the current session
SET maintenance_work_mem = '2GB';
-- Create an HNSW index for Cosine Distance
CREATE INDEX documents_embedding_hnsw_idx
ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);Parameter breakdown:
m = 16: Maximum number of bidirectional links per graph node. Bump to 24 or 32 if you require higher recall on large datasets.ef_construction = 64: Size of the dynamic candidate list evaluated during index construction. Higher values yield a more accurate graph at the expense of longer build times.hnsw.ef_search: Query-time parameter. Defaults to 40. Lower it to 20 for faster queries, or increase it up to 100 viaSET hnsw.ef_search = 80;for higher recall.
4. 4 Hard-Won Lessons from the Outage
Mitigating an incident is one thing; keeping the system stable long-term is an entirely different challenge. Here are the critical takeaways to keep in mind:
1. Benchmark with Shadow Traffic Before Cutting Over
Never migrate from Elasticsearch or ILIKE queries to pgvector in a single deployment. Configure Node.js to shadow traffic by sending parallel queries to pgvector for at least 3 days. Track p95/p99 latency and measure Mean Reciprocal Rank (MRR) to ensure search relevance does not degrade.
2. Keep Connection Pools and RAM Under Control
An HNSW index must fit entirely in RAM (shared_buffers and OS page cache) to achieve sub-20ms query speeds. An HNSW index for 500,000 1,536-dimensional vectors takes around 1.6GB to 2GB of RAM. Cap Node.js max pool connections between 15-25, or run PgBouncer in transaction pooling mode. Spawning too many connections will exhaust memory and trigger the Linux OOM Killer.
3. Isolate Vectors into a Dedicated Table
A 1,536-dimensional vector occupies roughly 6KB per row. Storing vectors in the same table as large TEXT or JSONB columns bloats the table rapidly, severely diluting PostgreSQL’s buffer cache. Keep vectors in a dedicated table:
-- Standard business entity table
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL
);
-- Dedicated vector table, joined only on search results
CREATE TABLE article_embeddings (
article_id INT PRIMARY KEY REFERENCES articles(id) ON DELETE CASCADE,
embedding vector(1536) NOT NULL
);4. Leverage Hybrid Search with RRF
While Semantic Search excels at contextual understanding, it often underperforms on exact queries like error codes (e.g., ECONNRESET) or product identifiers (SKU-8821). Combine PostgreSQL’s native Full-Text Search (via tsvector) with pgvector, and merge both score sets using Reciprocal Rank Fusion (RRF). The resulting output will deliver both semantic depth and exact keyword precision.
