Dedicated vector databases have become such a standard part of the RAG conversation that teams often add one before they have proved they need it. According to studies, over 70% of companies using LLMs are using vector databases and RAG to customize their models. That shows how quickly the pattern has become normal. However, it does not mean every RAG application needs a separate retrieval system.
If your application already runs on PostgreSQL, pgvector may be enough. It stores embeddings alongside the documents, permissions, metadata, and business records they relate to, then retrieves relevant content with SQL. Your team can keep using the access controls, backups, monitoring, and deployment process it already trusts.
The next question is usually scale. How far can PostgreSQL go before a separate vector database makes more sense? There is no published vector count that answers it. Ten million short embeddings with modest traffic may be manageable. A much smaller collection can become difficult if the application needs very fast responses, runs many searches at once, or applies restrictive filters. The index, embedding dimensions, hardware, query volume, and recall target all matter.
So the decision should not come down to a headline number of vectors. It should come down to whether pgvector meets your latency, quality, and operational requirements. If it does, adding another system may just create more work.
What is pgvector and how does it work with RAG?
What is pgvector? It is an open-source PostgreSQL extension that adds vector data types, distance operators, and exact and approximate nearest-neighbor search. The SQL extension is named vector, so it is enabled with CREATE EXTENSION vector;.
If you are also asking “what is a vector database,” it is a system designed to store embeddings and find vectors that are close to a query vector. pgvector adds that capability to PostgreSQL. It does not generate embeddings or call an LLM. Your application creates the embeddings, while PostgreSQL stores and searches them.
How do vector databases work in RAG? The basic flow is:
- Split source documents into smaller chunks.
- Generate an embedding for each chunk.
- Store the chunk, its metadata, and its embedding.
- Generate an embedding for the user’s question.
- Retrieve the closest chunks.
- Send those chunks to the language model as context.
The main pgvector operators are documented below. For cosine similarity, subtract cosine distance from 1.
| Operator | Metric | Typical use |
|---|---|---|
| <-> | Euclidean distance | Models configured for L2 distance |
| <=> | Cosine distance | Common text-embedding search |
| <#> | Negative inner product | Models optimized for inner product |
When pgvector is enough for RAG
pgvector is usually a strong starting point when vector retrieval is one part of a PostgreSQL-backed application. It is especially useful when results need SQL filters, joins, transactions, or the same operational controls as the rest of the product.
| Requirement | Why pgvector fits |
| Thousands or millions of vectors | PostgreSQL can support many moderate-scale RAG workloads with suitable indexes and hardware |
| Moderate query volume | A separate distributed platform may add more complexity than value |
| Application data already lives in PostgreSQL | Embeddings and business data remain in one system |
| Search needs metadata filters | SQL can filter by tenant, language, status, category, date, or permissions |
| Results must join application tables | Vector results and relational records can be combined in one query |
| The team already manages PostgreSQL | Existing backup, monitoring, security, and deployment processes can be reused |
| Infrastructure consolidation matters | There is no separate retrieval store to deploy and synchronize |
This is what makes a Postgres vector database attractive for SaaS search, support assistants, internal knowledge tools, and product documentation. A team can enforce tenant access in the same query that ranks chunks. It also avoids a synchronization problem in which an updated or deleted business record remains searchable in another system.
That doesn’t make performance automatic. Measure index size, recall, p50 and p95 latency, write rate, filter selectivity and impact of retrieval queries on transactional work. pgvector suggests comparing approximate results with exact search to monitor recall.
When a dedicated vector database may be better
If the primary workload is vector retrieval or PostgreSQL cannot meet the measured scale and latency targets, a dedicated platform may be the better option. The decision should be based on load testing, not the label “AI application.”
| Scenario | Why a dedicated vector database may help |
|---|---|
| Tens or hundreds of millions of vectors | Distributed storage and index management may be easier to operate |
| Very high query throughput | Vector-native platforms may offer simpler horizontal scaling |
| Strict tail-latency targets | Specialized infrastructure may provide more predictable retrieval latency |
| Vector search is the product’s main workload | A dedicated system may offer deeper search and operational features |
| Cross-region retrieval is required | Some platforms include replication and request routing |
| Retrieval affects transactional queries | Separating the workloads can isolate CPU, memory, and I/O use |
That separation comes at a price. Application records and embeddings now live in different systems, so you need to keep updates, deletions, permissions, and disaster recovery in sync. Metadata filtering and joins are also dependent on the support of the chosen platform. Benchmark those operational requirements and raw query speed.
How to build a RAG search with pgvector
A good pgvector architecture distinguishes source documents from the chunks used for retrieval. Each chunk should carry sufficient metadata to trace it back to its source, apply access rules, and regenerate embeddings at some later time.
The steps below form a compact pgvector tutorial. They cover storage and retrieval, but the application still needs to generate embeddings and pass the query vector to PostgreSQL.
Store documents, chunks, and embeddings
Keep a record for the source document and a record for each searchable chunk.
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE documents (
id bigserial PRIMARY KEY,
tenant_id bigint NOT NULL,
title text NOT NULL,
product_id bigint,
language text NOT NULL,
status text NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE document_chunks (
id bigserial PRIMARY KEY,
document_id bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
chunk_position integer NOT NULL,
chunk_text text NOT NULL,
embedding vector(1536) NOT NULL,
embedding_model text NOT NULL,
embedding_model_version text,
created_at timestamptz NOT NULL DEFAULT now()
);The number in vector(n) must match the output dimension of the selected embedding model. In other words, a PostgreSQL vector embedding with 1,536 values belongs in vector(1536). Changing models may require a new column or a controlled re-embedding migration. The pgvector documentation also notes that indexed vector values support up to 2,000 dimensions, while other types and indexing approaches support different limits.
In a typical vector database Python workflow, Python generates the embedding and inserts it through a PostgreSQL driver. The official Python pgvector package supports Psycopg, SQLAlchemy, Django, asyncpg, and other common libraries.
The important distinction is that the embedding model creates the vector; the Postgres vector extension stores and searches it.
Search embeddings and join application tables
The query should rank semantically similar chunks while enforcing the same filters used by the application. This pgvector example limits results to the current tenant, language, and published content, then joins product data in the same statement.
SELECT
dc.id,
dc.chunk_text,
d.title,
p.product_name,
1 - (dc.embedding <=> :query_embedding) AS similarity
FROM document_chunks dc
JOIN documents d
ON d.id = dc.document_id
JOIN products p
ON p.id = d.product_id
WHERE d.tenant_id = :tenant_id
AND d.status = 'published'
AND d.language = :language
ORDER BY dc.embedding <=> :query_embedding
LIMIT 10;This is a key advantage of PostgreSQL vector search. Semantic ranking, permissions, metadata filters, and application joins remain in one SQL query. Notice that the raw distance operator is used in ORDER BY; pgvector requires that form, in ascending order with LIMIT, for an approximate index to be considered. The calculated similarity is only for display.
Exact search, HNSW, and IVFFlat
pgvector supports exact search by default and approximate nearest-neighbor search through HNSW and IVFFlat indexes. Exact search gives perfect recall. Approximate indexes trade some recall for faster retrieval, so production tuning should compare their results with an exact baseline.
| Option | Best for | Main limitation |
|---|---|---|
| Exact search | Prototypes, smaller datasets, and recall testing | Slows as the number of scanned vectors grows |
| HNSW | Fast queries with a strong recall-to-speed balance | Uses more memory and builds more slowly |
| IVFFlat | Faster index creation and explicit lists/probes tuning | Needs enough data and careful configuration |
The official documentation says HNSW generally has a better speed-to-recall tradeoff than IVFFlat, but its index takes longer to build and uses more memory. IVFFlat divides vectors into lists and searches selected lists. Its recall depends heavily on the number of lists and probes.
For pgvector cosine similarity, create the index with the cosine operator class.
CREATE INDEX document_chunks_embedding_hnsw
ON document_chunks
USING hnsw (embedding vector_cosine_ops);Start with exact search to establish quality. Add HNSW or IVFFlat when measurements show it is needed, then test both speed and recall with representative filters.
Filtering and hybrid search in PostgreSQL
Production RAG search rarely ranks every chunk without restrictions. It usually filters by tenant, permissions, language, category, date, or publication status before returning context to the model.
With approximate indexes, pgvector applies ordinary filters after scanning the vector index. Selective filters can therefore return fewer rows than requested. Starting with pgvector 0.8.0, iterative index scans can continue through an HNSW or IVFFlat index until enough matching rows are found or a configured limit is reached.
Vector similarity is also not the best match for every query. Product names, error codes, function names, version numbers, technical identifiers, and exact phrases often need lexical search. PostgreSQL already includes full-text search, and the pgvector project recommends combining it with vector retrieval for hybrid search. Results can then be merged with a method such as reciprocal rank fusion or reranked with a cross-encoder.
| Search type | Strongest use case |
|---|---|
| Vector search | Meaning, intent, and semantically related content |
| Keyword or full-text search | Exact terminology, names, phrases, and identifiers |
| Hybrid search | Questions that require both meaning and exact matching |
Working with pgvector in dbForge Studio for PostgreSQL
dbForge Studio can support pgvector development by giving teams one place to write DDL, inspect stored data, run similarity queries, and review execution plans. It does not generate the embeddings. Those still come from the model or embedding service selected by the application.
| Task | How dbForge supports it |
|---|---|
| Create vector tables | Write and execute DDL in the SQL editor |
| Inspect schemas and columns | Browse database objects and their definitions |
| Test similarity queries | Run vector operators, filters, and joins |
| Review chunks and metadata | View and filter records in Data Editor |
| Analyze index use | Review query profiles and execution plans |
| Compare query variants | Test exact search, HNSW, IVFFlat, and filtered queries |
This PostgreSQL GUI includes SQL editing, data viewing, query profiling, and execution-plan tools. That makes it useful for checking whether an index is used, comparing query variants, and inspecting the rows returned to the RAG pipeline. Treat AI-generated optimization suggestions as ideas to test, not measured performance results.
pgvector vs dedicated vector database
pgvector is often the right first choice when application data already lives in PostgreSQL and retrieval depends on relational filters or joins. A dedicated vector database becomes easier to justify when real benchmarks show that PostgreSQL cannot meet the required scale, throughput, isolation, or latency.
| Criterion | pgvector | Dedicated vector database |
|---|---|---|
| Infrastructure | Runs inside PostgreSQL | Separate service or cluster |
| SQL joins | Native | Usually needs extra queries or synchronization |
| Relational filters | Full PostgreSQL filtering | Depends on the platform |
| Transactions | PostgreSQL transactions | Platform-specific |
| Operational complexity | Lower for existing PostgreSQL teams | Additional deployment and monitoring |
| Horizontal vector scaling | Uses PostgreSQL replicas, sharding, or distributed options | Often built into the platform |
| Best fit | RAG inside a relational application | Large-scale or vector-first products |
Over to you
A PostgreSQL pgvector implementation can go a long way before another system is necessary. It keeps Postgres vector embeddings close to the records and rules that give them meaning. But it is not a universal answer. Test with realistic data, filters, concurrency, and recall targets. Introduce a dedicated platform when the evidence shows that it solves a problem PostgreSQL cannot meet economically or predictably.
Frequently asked questions
What is pgvector, and how does it work with RAG?
pgvector is a PostgreSQL extension for vector storage and search. In RAG, the application creates embeddings for both document chunks and user questions, and pgvector finds the closest chunks to pass as context to the language model.
Can PostgreSQL replace a dedicated vector database?
Yes, for many RAG apps. PostgreSQL with pgvector is a solid choice if the data is already in PostgreSQL, query volume is moderate and retrieval requires SQL filters, joins, transactions, or existing PostgreSQL controls.
Is pgvector suitable for production RAG applications?
Yes, if testing shows that it meets the application’s latency, throughput, recall, and availability requirements. Production use also needs suitable indexes, monitoring, backups, access controls, and a plan for model or embedding changes.
Which pgvector index is better for RAG: HNSW or IVFFlat?
HNSW is often a better starting point in terms of query speed and recall but takes longer to build and consumes more memory. IVFFlat is faster and uses less memory but its lists and probes need to be tuned carefully.
Can pgvector combine vector search with SQL joins?
Yes. You can rank chunks by vector distance and join them to regular PostgreSQL tables in the same query using pgvector. It can also apply filters of tenant, language, status, permission or category.
Does pgvector generate embeddings?
No. An embedding model or external service generates the embeddings. pgvector provides PostgreSQL data types, operators, and indexes for storing and searching them.
