pgvector, the vector database you already have

pgvector, the vector database you already have

Before adding a separate vector search service to a project, it is worth checking whether an extension to the database you already run will do. pgvector gives PostgreSQL a column type for vectors, distance operators, and approximate indexes, and everything stays inside the same transaction and the same backup.

That last point often decides it. A separate vector database is another service to monitor, another disaster plan, and another place where data can drift out of step with the main database. An extension creates none of those problems.

Installation and a first query

CREATE EXTENSION vector;

CREATE TABLE fragments (
  id bigserial PRIMARY KEY,
  document_id bigint REFERENCES documents(id),
  content text NOT NULL,
  language text NOT NULL,
  embedding vector(1536)
);

The number in brackets is vector dimensionality and must match the model computing your embeddings. Switching to a model of different dimensionality means changing the column type and recomputing every row.

Search comes down to sorting by distance. The operator depends on the metric, and for most current models cosine distance is the right one.

SELECT id, content, 1 - (embedding <=> $1) AS similarity
FROM fragments
WHERE language = 'en'
ORDER BY embedding <=> $1
LIMIT 5;

You compute the question vector bound to that parameter on the application side, with the same model you used for the fragments. That is the one correctness condition the database will not warn you about: vectors from two different models compare fine technically, and the result is worthless.

Indexes, the difference between prototype and production

Without an index every query scans the whole table. At ten thousand rows that is still milliseconds; at a million it is seconds and CPU load you do not want.

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

The HNSW index is the default choice. It builds a graph of links between vectors and delivers high recall at short query times, at the cost of memory and build time. The m parameter sets links per node and ef_construction the thoroughness of graph construction.

The alternative is IVFFlat, which divides the space into lists and searches only the nearest. It builds faster and takes less memory, but requires data already in the table when the index is created and gives lower recall at the same query time. In practice it comes out for genuinely large sets or under memory constraints.

Building an HNSW index uses multiple cores, but only two helper processes by default, so a sixteen core machine builds an index barely faster than a four core one until you change that. max_parallel_maintenance_workers raises the worker count, and at larger values max_parallel_workers, set to eight by default, needs raising too.

The second threshold matters more than core count. The extension builds the graph in the session working memory, and once it stops fitting there it switches to an on disk variant and says so outright, with a hint to raise that memory. The time difference is then a multiple, so before a first indexing of a large set put maintenance_work_mem high enough for the graph to fit, while taking care not to exhaust server memory.

Tuning recall

An approximate index has a parameter governing how widely it searches. Higher means a better chance of finding the genuinely nearest vectors and a longer query.

SET hnsw.ef_search = 100;

The default is forty, meaning a bias toward speed. Raising it to one or two hundred usually improves recall noticeably at a small time cost, but the right number depends on the data set and must be measured. Inside a transaction set it with SET LOCAL, so the changed value does not linger for the whole connection.

Measurement works like this: for a set of a hundred queries compute the exact result without an index, then the result with the index, and check what percentage of results overlaps. It is the only measure a decision can rest on and also the one almost nobody computes.

Know that index recall and search accuracy are two different things, easily confused. The first says whether the index found the same vectors an exact scan would. The second says whether the fragments found actually answer the user's question. An index can work perfectly while the results remain useless, if the embedding model or the document splitting is at fault. When diagnosing poor results, check the second one first, since tuning an index on bad embeddings gains nothing.

Filtering, the most common trap

A query combining vector search with a condition on another column looks innocent and is a source of silent errors. An approximate index returns a set number of candidates and the filter applies afterwards, so with a narrow condition you can end up with zero results despite matching rows in the table.

Newer versions ease this with iterative scanning, which keeps pulling further candidates until it has the requested number of post filter results. The mechanism is off by default, so it has to be enabled deliberately, and it carries a ceiling of its own: the scan stops after twenty thousand visited rows or once its memory allowance runs out, so with a very narrow filter it can still return fewer results than you asked for. Those thresholds are governed by hnsw.max_scan_tuples and hnsw.scan_mem_multiplier.

SET hnsw.iterative_scan = relaxed_order;

The second approach is a partial index, built only for rows meeting a typical condition. It suits a filter that is fixed and predictable, one language or one document type for instance.

The third is partitioning tables by the key the filter concerns. In a multi tenant application where every query restricts to one customer, that structure solves the problem at source and simplifies data deletion along the way.

Saving space

A vector of dimensionality one thousand five hundred thirty six takes over six kilobytes per row at full precision. A million fragments is six gigabytes of embeddings alone, index excluded.

A half precision type halves that at a small recall cost, usually imperceptible in search applications.

ALTER TABLE fragments
ALTER COLUMN embedding TYPE halfvec(1536);

CREATE INDEX ON fragments
USING hnsw (embedding halfvec_cosine_ops);

The second route is shortening the vector itself, if your model supports it. Some models let you request an embedding of fewer dimensions without a quality loss proportional to the reduction, which shrinks both table and index.

The third is the sparse type, useful for embeddings where most positions hold zero. That is typical of lexical models rather than of classic dense embeddings.

Hybrid search without adding services

The database has had full text search for years, so alongside vectors you get a second mechanism with nothing extra to install. That matters more than it looks, because semantic search fails exactly where full text is strong: proper names, error codes, and numbers.

ALTER TABLE fragments ADD COLUMN content_tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;

CREATE INDEX ON fragments USING gin (content_tsv);

Combining both paths comes down to two queries and a merge. A simple approach assigns every result a score inversely proportional to its rank on each list and sums it for fragments present in both.

WITH vector_hits AS (
  SELECT id, row_number() OVER (ORDER BY embedding <=> $1) AS pos
  FROM fragments ORDER BY embedding <=> $1 LIMIT 30
), text_hits AS (
  SELECT id, row_number() OVER (ORDER BY ts_rank(content_tsv, $2) DESC) AS pos
  FROM fragments WHERE content_tsv @@ $2 LIMIT 30
)
SELECT id, sum(1.0 / (60 + pos)) AS score
FROM (SELECT * FROM vector_hits UNION ALL SELECT * FROM text_hits) merged
GROUP BY id ORDER BY score DESC LIMIT 5;

That construction lifts accuracy more than swapping the embedding model for a larger one, particularly in technical documentation. A separate vector database would need a second system for this; here it all happens in one query.

Maintenance and operational cost

A vector index can occupy more space than the data itself, so measure it before running out of disk.

SELECT pg_size_pretty(pg_relation_size('fragments')) AS table_size,
       pg_size_pretty(pg_indexes_size('fragments')) AS index_size;

Two operational problems appear at large volume. The first is memory: an HNSW index is fast while it fits in the database cache, and once it starts being read from disk query times jump. The second is build time when restoring from a backup, which at millions of vectors runs into hours.

A sensible practice is keeping vectors in a separate table linked by a foreign key rather than as a column on the main table. That way queries unrelated to search do not drag six kilobytes per row along, and index operations do not block work on the core data.

Ordinary hygiene matters too: statistics after a bulk insert, autovacuum under frequent updates, and watching the query plan. These are the same things as for any other table, only the consequences of neglect are felt more sharply here.

pgvector against a dedicated vector database

The extension's biggest advantage is something no separate database offers: joining search results with relational data in one query. Fetching fragments together with author, document status, and permissions is one query rather than a search in one system and a lookup in another.

The boundary sits at scale and write frequency. A few million vectors under moderate traffic is territory the extension handles well. Tens of millions of vectors with continuous inserts is where a specialised database starts winning.

The second boundary concerns who maintains it. The extension hands you every knob, and with it the whole duty of setting them: index parameter choice, memory, watching the query plan. A managed service takes that on in exchange for a subscription and less control. A team with a database administrator, or somebody who enjoys reading query plans, gains more from the extension than a team that would rather not think about the database at all.

The third thing is cost. The extension is free, so you pay only for the machine the database already runs on. At collections of hundreds of thousands of fragments the difference against a service billed per vector is often severalfold, and that frequently settles the choice early in a project.

The full flow in an application

It helps to see this from the code side, since that is where it becomes clear the extension needs no separate access layer.

import { sql } from './db'

export async function findFragments(question: string, language: string) {
  const vector = await computeEmbedding(question)

  return sql`
    SELECT f.content, d.title, d.url
    FROM fragments f
    JOIN documents d ON d.id = f.document_id
    WHERE f.language = ${language} AND d.published = true
    ORDER BY f.embedding <=> ${JSON.stringify(vector)}::vector
    LIMIT 5
  `
}

Note the join and the published condition. A separate vector database would return identifiers, after which you would fetch titles and check status in a second system, at the risk that a document was withdrawn and the index does not know. Here one query answers everything at once and sees the same state of the data.

Indexing a new document also fits in a transaction. You store the document, split it into fragments, compute embeddings, and insert them in one statement. If anything fails, you are left with neither half the fragments without a document nor a document without fragments.

That consistency is the main practical argument for the extension. In a setup with a separate vector database you must keep both systems describing the same state yourself, and any failure mid operation leaves them out of step, to be repaired later with a script.

Common mistakes

The first is no index in production. The query works, so nobody checks the plan, and the database quietly scans the whole table on every question.

The second is comparing vectors from different models. Nothing detects it and results are random. Store the model name in a column beside the embedding, and the mistake surfaces immediately.

The third is a mismatched operator class on the index. An index built for Euclidean distance will not be used by a cosine query, and the database quietly falls back to a scan.

The fourth is too little working memory during index build. The operation completes correctly, only many times slower and producing a worse graph.

The fifth is ignoring autovacuum. A table with frequent embedding updates bloats with dead rows, which grows both size and query time.

The sixth is keeping fragment text only in another service. Since the database stores the vector anyway, keeping content and metadata beside it simplifies everything and removes the risk of two sources drifting apart.

FAQ

Is pgvector enough instead of a dedicated vector database?

In most projects, yes. Up to a few million fragments under moderate traffic the extension delivers comparable query times and saves an entire separate service. A dedicated database starts paying off at tens of millions of vectors or very high write frequency.

HNSW or IVFFlat?

HNSW by default, since it gives higher recall at the same query time and needs no data in the table before creation. Choose IVFFlat under memory constraints or when index build time is critical.

How large should fragments be?

As with any vector database: five hundred to a thousand characters with slight overlap, cut along headings and paragraphs rather than every fixed number of characters. The rule does not depend on where you keep vectors, only on how a model reads context.

Does it work with Supabase?

Yes, the extension is available in Supabase and enables with one statement. That is in fact the most common way teams meet pgvector, since the database is already there and no service needs adding.

Where do embeddings come from?

From a cloud vendor's model or from a local model served through Ollama. The extension does not compute them, only stores and compares them, so model choice is independent of the database and can change, provided you recompute every row.

Documentation and code sit in the GitHub repository, and changes across successive extension releases appear in the changelog.