You probably don't need a dedicated vector database (Use pgvector instead)

Before you sign up for another $400/month hosted vector database, stop and ask yourself: how many vectors do you actually have?

If you're storing under 10 million vectors, you almost certainly don't need Pinecone, Milvus, or Qdrant. You just need the Postgres instance you're already running, supercharged with the pgvector extension.

With pgvector and HNSW indexes, you get sub-10ms nearest-neighbor queries, ACID transactions, and the ability to join your vector results directly with your relational tables (like WHERE user_id = $1 or AND organization_id = $2) without writing awkward ETL sync pipelines between two different databases.

Here is the practical setup: the schema, the HNSW index tuning, and the query patterns to make Postgres handle production vector workloads without breaking a sweat.

The Critical Role of Vector Databases in LLM RAG Pipelines

LLMs are phenomenally powerful, but they natively suffer from hallucinations and lack awareness of your proprietary, private, or real-time data. Retrieval-Augmented Generation (RAG) solves this by providing the model with relevant context retrieved from an external database before generating an answer.

But how do we reliably find the "most relevant" context in an ocean of unstructured data?

When you pass text through a specialized embedding model (such as OpenAI's text-embedding-3-small, Cohere, or open-source alternatives like BGE), the model translates the semantic meaning of that text into a high-dimensional vector - essentially, an array of floating-point numbers. In this multi-dimensional mathematical space, texts with similar meanings or concepts are located close to each other.

A vector database is absolutely essential for RAG because it is optimized to store these high-dimensional arrays and quickly calculate the mathematical distance between a user's query vector and millions of stored document vectors. Without vector search algorithms, finding semantically relevant documents at scale would be computationally unfeasible.

By retrieving the nearest neighbors to a query, RAG pipelines can inject highly relevant factual information directly into the LLM's context window. This significantly reduces hallucinations and ensures the generated response is accurate and context-aware. Furthermore, this approach circumvents the need for continuous, expensive fine-tuning of the LLM. It saves immense computational resources and allows developers to update their AI's knowledge base simply by adding, updating, or deleting rows in a database.

Why Choose PostgreSQL and pgvector?

While purpose-built vector databases (like Pinecone, Milvus, Qdrant, or Weaviate) have gained immense popularity, introducing a brand-new database technology into your tech stack introduces significant operational overhead. You must manage data synchronization, handle distributed transactions, navigate complex integrations, and maintain an entirely separate infrastructure.

pgvector elegantly transforms PostgreSQL into a fully-fledged vector database. By keeping your vector data alongside your traditional relational data, you inherit decades of Postgres reliability, robust ACID compliance, proven backup strategies, and comprehensive role-based access control. Most importantly, you gain the ability to perform hybrid searches - filtering by standard SQL columns (like tenant ID, publication date, or category) while simultaneously ordering by vector similarity - without ever moving data across networks.

Setting Up pgvector

Getting started with pgvector is highly straightforward. If you are using a modern managed PostgreSQL provider (such as AWS RDS, Supabase, Google Cloud SQL, or Neon), pgvector is almost certainly already supported and merely needs to be enabled.

If you are running PostgreSQL locally or on a custom server, you can compile and install it from the source. Once the binary is installed on your server, enable the extension in your database by running the following SQL command:

-- Enable the pgvector extension in your database
CREATE EXTENSION IF NOT EXISTS vector;

With the extension successfully enabled, you can now utilize the new vector data type. Let's create a table to store our text document chunks and their corresponding embeddings. For example, if we are using OpenAI's standard embeddings, the dimensions typically equal 1536.

-- Create a table to store documents, metadata, and their vector embeddings
CREATE TABLE documents (
    id bigserial PRIMARY KEY,
    content text NOT NULL,
    metadata jsonb,
    -- Store a vector array with precisely 1536 dimensions
    embedding vector(1536)
);

Inserting data into this table is just as simple as inserting into any other Postgres table. You simply provide the vector as a formatted string or a standard array from your application code:

-- Insert a sample document and its semantic embedding
INSERT INTO documents (content, metadata, embedding)
VALUES (
    'Vector search enables semantic matching based on meaning, rather than keywords.',
    '{"author": "Jane Doe", "category": "AI", "tenant_id": 101}',
    '[0.012, -0.045, 0.088, ..., 0.011]'
);

Performing Cosine Similarity Search

To find the most relevant documents for a given query, we must first convert the user's plain-text query into an embedding using the exact same embedding model, and then search the database for the closest vectors. pgvector supports several distance metrics natively, including Euclidean distance (<->), inner product (<#>), and cosine distance (<=>).

For most modern LLM embeddings (which are often normalized by the provider), cosine distance is the standard and recommended metric. Here is how you can perform a K-Nearest Neighbors (KNN) search to rapidly find the top 5 most semantically similar documents:

-- Find the 5 most semantically similar documents to a user's query vector
SELECT 
    id, 
    content, 
    -- Calculate cosine similarity by subtracting distance from 1
    1 - (embedding <=> '[0.015, -0.042, 0.091, ..., 0.021]') AS similarity_score
FROM documents
ORDER BY embedding <=> '[0.015, -0.042, 0.091, ..., 0.021]'
LIMIT 5;

Notice that the custom operator <=> computes the cosine distance. Because cosine similarity is mathematically defined as 1 - cosine_distance, we simply subtract the distance from 1 in our SELECT clause to retrieve an intuitive similarity score.

Scaling with HNSW Indexes

A standard KNN query as shown above performs a sequential scan, examining every single row in the table to calculate the exact distance. While this Exact Nearest Neighbor (ENN) approach guarantees perfect accuracy, it becomes incredibly slow as your dataset grows into the hundreds of thousands or millions of rows.

To scale vector search to enterprise levels, we must use Approximate Nearest Neighbor (ANN) algorithms. These algorithms trade a tiny, often imperceptible bit of accuracy (recall) for massive, logarithmic performance gains. Starting in version 0.5.0, pgvector introduced robust support for HNSW (Hierarchical Navigable Small World) indexes - widely considered the gold standard algorithm for vector search today.

HNSW builds a multi-layered graph where each node represents a vector. Searches start at the highest, sparsest layer, making large jumps across the vector space to quickly narrow down the neighborhood, and progressively drill down to lower, denser layers for fine-grained navigation.

Here is how you create an HNSW index in pgvector, explicitly optimized for cosine distance:

-- Create an HNSW index optimized for cosine distance calculations
CREATE INDEX documents_embedding_hnsw_idx 
ON documents 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Performance Tuning: m and ef_construction

The HNSW index creation command accepts two critical parameters that allow you to precisely tune the tradeoff between build time, memory footprint, and search recall:

• m: The maximum number of bidirectional links created for each element during graph construction. A higher m (e.g., 32, 64, or even 96) improves recall for high-dimensional data (like 1536-dimensional vectors) but significantly increases the index size on disk and RAM, as well as the build time. The default is 16, but 64 is often recommended for heavy production workloads.
• ef_construction: The size of the dynamic candidate list used when building the index. Increasing this value (e.g., to 128, 256, or 512) results in a meticulously constructed, higher-quality graph and better recall, at the explicit cost of significantly longer index creation times. It only impacts index build time, not query time.

Additionally, during query execution, you can dynamically tune ef_search for the current transaction or session to control the number of candidates considered during the search phase. Higher values increase recall but slightly reduce search speed.

-- Adjust ef_search for the current session to prioritize recall (default is 40)
SET hnsw.ef_search = 100;

Hybrid Search: The Ultimate Postgres Advantage

One of the most compelling reasons to use pgvector over a standalone vector database is the ability to perform complex hybrid searches. You can seamlessly and transactionally combine vector similarity with traditional SQL filters and joins. For instance, you can effortlessly filter documents by a specific author, tenant ID, or a strict date range before ranking the remaining subset by semantic relevance.

SELECT 
    content,
    metadata->>'author' AS author,
    1 - (embedding <=> '[0.015, -0.042, 0.091, ..., 0.021]') AS similarity
FROM documents
WHERE metadata->>'category' = 'AI'
  AND (metadata->>'tenant_id')::int = 101
ORDER BY embedding <=> '[0.015, -0.042, 0.091, ..., 0.021]'
LIMIT 5;

If your standard columns are properly indexed (e.g., using B-Tree or GIN indexes on the metadata JSONB column), PostgreSQL's sophisticated query planner can aggressively filter the dataset first, applying the expensive vector search only to the relevant, highly targeted subset. This is notoriously difficult, heavily latent, and error-prone to achieve efficiently in split architectures where relational metadata lives in Postgres and vectors live completely isolated in a separate database system.

Conclusion

Vector databases are the fundamental engine powering the next generation of AI applications, making RAG pipelines responsive, context-aware, and highly accurate. With pgvector, you do not need to reinvent your infrastructure or adopt unproven tech stacks. You can add state-of-the-art, HNSW-indexed vector search directly to PostgreSQL - the database you likely already know and trust.

By keeping your relational data and dense embeddings together, you radically simplify your system architecture, maintain rigorous transactional integrity, and empower your LLMs with fast, contextually relevant knowledge. Whether you are building a specialized semantic search engine, an autonomous intelligent chatbot, or an enterprise-grade recommendation system, pgvector provides the performance, flexibility, and massive scalability necessary to bring your AI visions into reality.

You Might Also Like

• LangChain vs LlamaIndex: Production RAG Pipeline Guide
• Claude API Function Calling: JSON Schema Optimization Guide
• vLLM vs Ollama: Local LLM Throughput & GPU Benchmarks
• Semantic Caching with Redis and Qdrant for LLM Cost Reduc...
• Fine-Tuning Llama 3 with LoRA and Unsloth: Developer Guide

---

Originally published at https://www.locionic.com on Locionic.

Comments

[Raknaos]

Agreed on the sub-10M vector regime, though the part I'd argue for more strongly is the join itself. Once embeddings sit next to the rows they describe, permission filtering (WHERE org_id = $1) becomes a plain predicate instead of a pre-filter dance against an external index. That's where the operational win actually is, not the latency.

What was your HNSW tuning story on a shared OLTP instance? Ours suffered from ef_search drift under load until we pinned the per-connection GUCs.

[locionic]

Totally agree on the joins. Having WHERE org_id = $1 right beside the embedding avoids the whole pre-filter mess with external vector DBs.
For ef_search drift with connection pooling, I pinned it per-transaction with SET LOCAL hnsw.ef_search = 80; (or directly on the search function) so it can't leak across connections. Once write volume picked up, I just offloaded search to a read replica to protect shared_buffers.
How did you end up pinning the GUCs on your end?