Your database can search vectors

Imagine correcting a published article while its search index still describes the old version. A reader searches for the new guidance, but search still ranks the article using its superseded text. Now imagine unpublishing that article and waiting for a second database to notice.

Those are useful reasons to consider pgvector. It adds vector similarity search to PostgreSQL, so an article, its publication status, and its searchable representation can live together. For an application already using Postgres, the interesting question is how much search it can handle while preserving the data rules you already depend on.

Keep the document and its representation together

An embedding is a numerical representation of text. It lets a search compare related wording: a query about "answers grounded in documents" may find an article about retrieval-augmented generation without requiring that exact phrase.

The embedding belongs to a particular version of that text. Once the application has generated the new embedding, it can save the content and vector in one transaction. PostgreSQL makes those changes visible together, or rolls them back together if the transaction fails. Publication status can participate in the same operation. PostgreSQL's transaction documentation explains that all-or-nothing behavior.

There is still application work here. If embedding generation runs in a background job, a transaction cannot make an old vector describe new content. I would track the document revision used by that job, reject stale results, and decide whether an article awaiting its new embedding should remain searchable. Keeping everything in one database makes that policy easier to enforce.

It also means SQL joins can connect search results to the rest of the application. Backups, connection pooling, monitoring, and recovery stay within the PostgreSQL operations your team already maintains. That is a practical advantage when a separate search service would otherwise need its own update pipeline.

Ask for the right articles before asking for faster search

For our illustrative article catalog, a useful query is small:

SELECT id, title, slug
FROM articles
WHERE status = 'published'
  AND embedding IS NOT NULL
ORDER BY embedding <=> $1::vector
LIMIT 10;

Here $1 is a query embedding supplied by the application. It must use the same model and dimensionality as the stored embeddings. The <=> operator orders by cosine distance: smaller means closer in direction.

With no approximate vector index, this is an exact nearest-neighbor search over eligible vectors. "Exact" describes the distance calculation and ordering. It does not promise that the embedding model understands the reader's intent perfectly.

Suppose the catalog has a large archive but only a small published collection. A normal index on the status filter may leave little work for the distance calculation. I would measure that query before adding a more complicated search index. The number of rows in the whole table tells only part of the story.

An approximate index searches a limited candidate set. With pgvector, filters apply after that index scan, so too few eligible rows may survive. Iterative scans, available since 0.8.0, can search further within configured limits. pgvector filtering guidance.

Picture a librarian bringing ten promising books to the desk, then discovering that eight cannot be borrowed. There may be plenty of available books elsewhere. Returning two tells you something about the search process, not necessarily the collection. For the article query, publication status stays mandatory; the remedy is to improve candidate retrieval.

Choose the index by the work it saves

IVFFlat groups vectors into lists and searches selected lists near the query. HNSW builds a graph of neighboring vectors and navigates through it. Both exchange some recall, the fraction of exact neighbors recovered, for less search work.

The SQL examples below are alternative index configurations to evaluate, rather than a requirement to install both.

IVFFlat

-- Create an IVFFlat index
CREATE INDEX ON articles
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- Set probes at query time (default is 1)
SET ivfflat.probes = 10;  -- Search 10 of 100 lists

More probes search more lists. Build the index after loading representative data. The documented starting points are rows / 1000 lists up to one million rows, sqrt(rows) above that, and sqrt(lists) probes. These are tuning starting points, not capacity limits. pgvector IVFFlat guidance.

IVFFlat accepts new rows without rebuilding the index. Its insertion implementation assigns incoming vectors to an existing list. If the collection changes substantially, reassess whether those lists still serve the workload well.

HNSW

-- Create an HNSW index
CREATE INDEX ON articles
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

m controls graph connectivity; ef_construction controls the candidate list used while building it. Increasing them spends more resources on the index. At query time, a separate setting controls search effort:

-- Set search quality at query time
SET hnsw.ef_search = 100;  -- Default is 40. Higher = better recall, slower.

I would test this against the exact results for the published collection. A faster query that misses the articles readers need is a regression, even if the database monitoring screen looks happier.

Aspect	IVFFlat	HNSW
Build time	Generally faster	Generally slower
Insert without rebuild	Supported; new rows join existing lists	Supported; new rows join the graph
Memory	Generally lower	Generally higher
Speed and recall	Search more lists to recover more neighbors	Search a larger graph candidate set to recover more neighbors
Decision to test	Can lower build and memory cost meet the recall target?	Does better search performance justify the build and memory cost?

HNSW generally offers the better speed/recall tradeoff, with higher build and memory costs. Measure both on your queries. pgvector index comparison.

Meaning and wording can contribute to the same search

A reader typing "RAG" may care about that specific term. Another may describe the same problem without knowing the acronym. Vector search and full-text search give us two ways to find the article.

Postgres lets us combine them beside the article metadata in SQL. Hybrid retrieval also exists in dedicated systems, including Pinecone. The advantage here is keeping the query within the relational database already holding the content.

This weighted query illustrates the mechanics. Its 0.7 and 0.3 coefficients are deliberately unvalidated example values:

-- Hybrid search: combine semantic similarity with keyword matching
WITH semantic AS (
    SELECT id, 1 - (embedding <=> $1::vector) AS semantic_score
    FROM articles
    WHERE status = 'published'
    ORDER BY embedding <=> $1::vector
    LIMIT 50
),
keyword AS (
    SELECT id, ts_rank(search_vector, websearch_to_tsquery('english', $2)) AS text_score
    FROM articles
    WHERE status = 'published'
      AND search_vector @@ websearch_to_tsquery('english', $2)
)
SELECT
    a.id, a.title, a.slug, a.summary, a.tags,
    COALESCE(s.semantic_score, 0) * 0.7 +
    COALESCE(k.text_score, 0) * 0.3 AS combined_score
FROM articles a
LEFT JOIN semantic s ON a.id = s.id
LEFT JOIN keyword k ON a.id = k.id
WHERE s.id IS NOT NULL OR k.id IS NOT NULL
ORDER BY combined_score DESC
LIMIT 10;

The semantic branch takes up to 50 neighbors; the keyword branch finds matching lexemes, the normalized words used by PostgreSQL full-text search. The final query joins those candidates to their article records and combines scores.

The arithmetic is easy. Deciding whether it produces a useful ranking takes work. Cosine similarity and ts_rank have different meanings and scales, so multiplying them by weights that add to one does not make their contributions comparable. PostgreSQL's ranking documentation describes the factors and normalization options behind its text scores.

I would use judged queries to compare this weighted approach with rank fusion, which combines positions in result lists instead of raw scores. Pinecone's hybrid search guide describes that alternative. Neither method can rescue a relevant article excluded from both candidate lists. Check candidate coverage before spending an afternoon adjusting decimal places.

SQL reference for the catalog

The following setup keeps the complete examples together. Choose one schema and one vector index for an experiment; the two CREATE TABLE articles definitions are alternatives.

Install and enable the extension

Use your provider's supported extension procedure on managed PostgreSQL. For self-hosted installations, the build and PostgreSQL 16 package examples are:

# Build from source
git clone https://github.com/pgvector/pgvector.git
cd pgvector
make
make install

# Or via package manager (Debian/Ubuntu)
apt install postgresql-16-pgvector

The package must match the installed PostgreSQL major version; source builds require PostgreSQL development files and a compiler. Follow the installation instructions for the environment, then enable the extension in the database:

CREATE EXTENSION IF NOT EXISTS vector;

Match the column to the embedding

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    content TEXT NOT NULL,
    summary TEXT,
    tags TEXT[],
    status TEXT DEFAULT 'draft',
    published_at TIMESTAMPTZ,
    embedding vector(1536),          -- OpenAI ada-002 dimension
    search_vector tsvector,          -- Full-text search
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

vector(1536) fixes the length of each stored vector. For orientation, OpenAI's ada-002 produces 1536 dimensions, while text-embedding-3-small and text-embedding-3-large default to 1536 and 3072 respectively; the newer models also support shorter outputs. OpenAI embedding guide.

Other models have different sizes: all-MiniLM-L6-v2 produces 384 dimensions, and Cohere's embed-english-v3.0 produces 1024. The alternative schema below assumes a model configured for 768 dimensions. Confirm the actual output before creating the column. Equal dimensions alone do not make embeddings from different models comparable.

A 3072-dimensional vector also needs an indexing plan: the ordinary vector approximate indexes support up to 2000 dimensions; half-precision indexing supports up to 4000. pgvector dimension limits.

Supply the complete embedding as a parameter, rather than an abbreviated vector containing an ellipsis:

INSERT INTO articles (title, slug, content, embedding)
VALUES (
    'Building a RAG Pipeline',
    'building-rag-pipeline',
    'Full article content here...',
    $1::vector(1536)
);

Pick a distance that matches the model

The three operators used in these examples are:

-- Cosine distance (most common for text embeddings)
SELECT * FROM articles
ORDER BY embedding <=> $1::vector  -- cosine distance
LIMIT 10;

-- L2 (Euclidean) distance
SELECT * FROM articles
ORDER BY embedding <-> $1::vector  -- L2 distance
LIMIT 10;

-- Inner product (negative, for max inner product search)
SELECT * FROM articles
ORDER BY embedding <#> $1::vector  -- negative inner product
LIMIT 10;

Cosine distance compares direction:

\text{cosine\_distance}(\mathbf{a}, \mathbf{b}) = 1 - \frac{\mathbf{a} \cdot \mathbf{b}}{\lVert \mathbf{a} \rVert \, \lVert \mathbf{b} \rVert}

For nonzero vectors, identical direction gives distance 0; opposite direction gives 2. L2 measures straight-line distance and includes magnitude. Inner product includes magnitude too, so its suitability depends on how the model was trained. With unit-normalized vectors, these metrics produce equivalent neighbor orderings. The negative sign in <#> lets the largest inner products sort first in ascending order. Match the index's operator class to the operator used by the query.

Keep the text index current

-- Add tsvector column with GIN index
CREATE INDEX articles_search_idx ON articles USING gin(search_vector);

-- Auto-update search_vector on insert/update
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.summary, '')), 'B') ||
        setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_update
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION update_search_vector();

The trigger assigns different weights to title, summary, and content. It runs for future inserts and updates; existing rows need a backfill if the trigger is added later. Full-text search normalizes words, so code identifiers or exact punctuation may require a different search configuration. PostgreSQL text parsing and weights.

A fuller schema

This alternative uses 768-dimensional embeddings and adds ordinary indexes for the catalog's other access patterns. It is an illustrative schema, not a declaration of this site's deployed configuration.

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    content TEXT NOT NULL,
    summary TEXT,
    tags TEXT[],
    status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    reading_time TEXT,
    published_at TIMESTAMPTZ,
    embedding vector(768),           -- Embedding model output
    search_vector tsvector,          -- Full-text search
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- Indexes
CREATE INDEX articles_embedding_idx ON articles
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

CREATE INDEX articles_search_idx ON articles USING gin(search_vector);
CREATE INDEX articles_status_idx ON articles (status);
CREATE INDEX articles_tags_idx ON articles USING gin(tags);
CREATE INDEX articles_published_idx ON articles (published_at DESC);

The GIN index on tags supports array queries such as tags @> ARRAY['rag']; the date index supports chronological browsing. These familiar relational operations are part of the reason to consider pgvector in the first place.

Measure the cost of sharing the database

Search competes with application traffic for CPU, memory, and storage bandwidth. Test it while updates and ordinary queries are running. A connection pool such as PgBouncer can limit concurrent database work, but it cannot create extra capacity.

For index builds, an example memory setting is:

SET maintenance_work_mem = '2GB';  -- Before creating HNSW index

Treat 2 GB as an example allocation. PostgreSQL's memory guidance distinguishes maintenance memory from shared buffers and the operating system cache. Leave room for concurrent work. Measure index size and memory pressure instead of assuming a fixed multiplier of raw vector storage.

Embedding generation and any model-based reranker still need an application or service to run them. A separate vector platform may package those functions or offer operational isolation that your workload needs. Moving later involves more than copying vectors: filters, score semantics, synchronization, and result quality must survive the move.

When I would keep search in Postgres

For this catalog, I would start with the filtered exact query, record its latency under realistic load, and use its neighbors as the recall reference. Then I would test whether an approximate index meets the latency target while retaining enough relevant, eligible articles. I would also test publication changes and delayed embedding jobs, because freshness is part of correctness.

There is no useful universal row count at which those requirements stop being achievable. Vector dimensions, filter selectivity, traffic, hardware, and update patterns all change the answer. Set the acceptance targets before measuring: result quality, tail latency, freshness, and the effect on ordinary database work.

If PostgreSQL meets those targets, keeping the article and its search representation together removes a synchronization problem and leaves the team with one system to operate. If it cannot, the failed target tells you what another system must improve. That is a much better basis for choosing a vector database than discovering, too late, that your very fast search still recommends an article you unpublished yesterday.