Do I need a dedicated vector database?

Not always. For moderate sizes, a vector column in PostgreSQL keeps one system to run. Very large or specialised workloads may justify a separate one.

Can I use pgvector with SQLite?

Not through pgvector, which is a PostgreSQL extension. SQLite has separate extensions for vectors.

Why did my filtered query return too few rows?

The index scan happens before the filter. Use an ordinary index, a partial index, partitions or iterative scans, as described above.

Do I need an index at all?

Not for small tables. Exact search is correct and simple. Add an approximate index when exact search is too slow, and measure the recall you give up.

Can I change embedding models later?

Yes, but you must re-embed everything you want to compare. Keep the original text and a model name column so it is a batch job.

AI apps

Search by meaning with the Postgres you already run

AI search needs embeddings, and embeddings need somewhere to live. For many apps that place is a table, with the same backups and permissions as everything else.

Published October 202613 min read

Jump to the similarity query

A t-SNE scatter plot in which thousands of colored points form clusters, a common way to picture embeddings

Photo: Climate data analysis using tSNE method, Orlovma, CC BY 4.0, via Wikimedia Commons.

Many AI applications share one trick. They turn text, images or other items into lists of numbers called embeddings, then find stored items whose numbers are close to the numbers for a question. That is how a search for "can I return this?" finds a page titled "refund policy". It is also the retrieval half of retrieval-augmented generation, where a model is handed relevant stored passages before it answers.

The pgvector extension adds a vector column type and nearest-neighbour search to PostgreSQL. This article follows its documentation, version 0.8.7 at the time of writing, which supports PostgreSQL 13 and later. Your embeddings sit next to the rows they describe, with the same backups, permissions and transactions.

What an embedding is

An embedding model reads an input and returns a fixed-length list of numbers, for example 768 or 1,536 of them. The model is trained so that inputs with similar meaning produce lists that are near each other. "Near" is measured with a distance function. The model is what gives the numbers meaning; the database only stores and compares them.

Two consequences follow. First, you must use the same embedding model for stored items and for queries, because lists from different models are not comparable. Second, the lists have a fixed length, which you declare in the column type.

Create a table with a vector column

The example uses three dimensions so it is readable. Real models use hundreds or thousands, so set the number to match yours. Run CREATE EXTENSION once in each database that will use it.

SQL

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE docs (
  id BIGSERIAL PRIMARY KEY,
  body TEXT NOT NULL,
  embedding vector(3) NOT NULL
);

INSERT INTO docs (body, embedding) VALUES
  ('refund policy',   '[1, 0, 0]'),
  ('shipping times',  '[0, 1, 0]'),
  ('returns process', '[0.9, 0.1, 0]');

The documentation recommends bulk loading with COPY ... FROM STDIN WITH (FORMAT BINARY) for large amounts of data, which is much faster than many single inserts.

The types, and what they cost to store

Type	Storage per value (from the README)	Index limit
vector	4 × dimensions + 8 bytes	2,000 dimensions
halfvec	2 × dimensions + 8 bytes	4,000 dimensions
sparsevec	8 × non-zero elements + 16 bytes	1,000 non-zero elements
bit	dimensions / 8 + 8 bytes	64,000 dimensions

A single vector column can hold up to 16,000 dimensions, but an index on a plain vector column is limited to 2,000. At 1,536 dimensions, one stored vector takes about 6.1 kilobytes, so a million rows is about 6 gigabytes before indexes. Estimate this before you pick a model. The documentation also describes indexing at half precision and using binary quantization with re-ranking to keep indexes smaller.

Find the nearest items

Distance is chosen by operator. Ordering by the operator and limiting the result gives the nearest neighbours.

Operator	Distance	Notes
<->	Euclidean (L2)	The straight-line distance.
<=>	Cosine distance	Use 1 − (<=>) for cosine similarity.
<#>	Negative inner product	Multiply by −1 for the true inner product. Postgres only supports ascending index scans on operators, hence the negative.
<+>	L1 (taxicab)
<~> and <%>	Hamming and Jaccard	For bit vectors only.

SQL

SELECT body, embedding <-> '[1, 0, 0]' AS distance
FROM docs
ORDER BY embedding <-> '[1, 0, 0]'
LIMIT 2;
-- refund policy (0), returns process (about 0.14)

To find items similar to an existing row, use its own embedding. This pattern comes straight from the documentation:

SQL

SELECT * FROM docs
WHERE id != 1
ORDER BY embedding <-> (SELECT embedding FROM docs WHERE id = 1)
LIMIT 5;

Without an index, this scans every row and returns exact results. That is fine for thousands of rows and slow for millions. It is also the baseline against which to judge any index.

Which distance should I use?

Use the one your embedding model was trained for. Many text embedding models are designed for cosine similarity, and if the vectors are normalised to length one, cosine and inner product give the same ranking. The model's documentation will say. If it does not, cosine distance is a common default. Whatever you choose, build the index with the matching operator class, or the index will not be used.

Indexes: trading accuracy for speed

Approximate indexes find very close neighbours quickly, but may miss the true nearest one. pgvector offers two kinds.

	HNSW	IVFFlat
How it works	A multilayer graph of connections between vectors.	Splits vectors into lists; a query searches the closest lists.
Speed versus recall	Generally better, per the README.	Good, but needs tuning.
Build	Slower, uses more memory. Can be created on an empty table.	Faster. Create it after the table has data, or results may be missing.
Key settings	m (default 16), ef_construction (default 64), hnsw.ef_search (default 40)	lists, ivfflat.probes (default 1)

SQL

-- HNSW with defaults
CREATE INDEX ON docs USING hnsw (embedding vector_l2_ops);

-- HNSW with explicit build settings
CREATE INDEX ON docs USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 64);

-- Query-time accuracy for this session
SET hnsw.ef_search = 100;

-- IVFFlat: build after loading data
CREATE INDEX ON docs USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);
SET ivfflat.probes = 10;

Operator classes must match the operator you order by: vector_l2_ops for <->, vector_cosine_ops for <=> and vector_ip_ops for <#>. Half-precision and sparse types have their own classes with a halfvec_ or sparsevec_ prefix.

For IVFFlat, the README suggests lists of rows divided by 1,000 up to a million rows, and the square root of the row count beyond that. For probes, it suggests starting around the square root of lists. Setting probes equal to lists gives exact search, which defeats the point of the index. HNSW builds much faster when the graph fits in maintenance_work_mem, but do not set that so high that you exhaust server memory.

Measure what you lose

An index that is fast and wrong is worse than a slow scan. Check recall by running a sample of real queries both ways and comparing the results. The README gives a setting for exact search inside a transaction:

SQL

BEGIN;
SET LOCAL enable_indexscan = off;  -- use exact search
SELECT id FROM docs ORDER BY embedding <-> '[1, 0, 0]' LIMIT 10;
COMMIT;

Compare those ten ids to what the indexed query returns. If you get nine of ten, recall is 90%. Raise hnsw.ef_search or ivfflat.probes until recall is acceptable, and note the speed cost. Do this again whenever you change the model, the data size or the settings.

Filtering is where people get surprised

Real applications filter: only this customer's documents, only this language, only items in stock. With an approximate index, the filter is applied after the index scan. The README gives a worked example. With HNSW's default ef_search of 40, a condition that matches 10% of rows returns about four rows on average, not the ten you asked for.

SQL

SELECT body
FROM chunks
WHERE tenant_id = 42
ORDER BY embedding <-> $1
LIMIT 10;   -- may return fewer than 10 rows with an approximate index

The documented remedies are:

Index the filter column with an ordinary index. If the filter is selective, exact search over the matching rows works well.
Use a partial index when there are only a few distinct filter values.
Partition the table when there are many values, such as one per tenant.
Turn on iterative index scans, available from pgvector 0.8.0, so the index keeps scanning until it has enough rows.

SQL

SET hnsw.iterative_scan = strict_order;    -- exact distance order
-- or
SET hnsw.iterative_scan = relaxed_order;   -- better recall, may be slightly out of order

SET ivfflat.iterative_scan = relaxed_order;

Iterative scans stop at a limit: hnsw.max_scan_tuples, which defaults to 20,000, or ivfflat.max_probes. With relaxed ordering you can combine a materialised CTE with an outer sort to restore strict order. Test filtered queries as carefully as unfiltered ones.

Design the table around the retrieval, not only the vector

A model reads a passage, not a whole book. Long documents are split into chunks, and each chunk gets its own embedding. A simple, extensible layout:

SQL

CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  title TEXT NOT NULL,
  source_url TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE chunks (
  id BIGSERIAL PRIMARY KEY,
  document_id BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  chunk_index INT NOT NULL,
  body TEXT NOT NULL,
  embedding vector(1536) NOT NULL,
  embedding_model TEXT NOT NULL,
  tenant_id BIGINT NOT NULL,
  UNIQUE (document_id, chunk_index)
);

Keep the model name in a column. When you change models, you need to know which rows to re-embed, and you must never mix models in one comparison.
Store the original text with the vector, so you can show the passage, and rebuild embeddings later.
Cascade deletes from document to chunks, so removing a document removes its embeddings. That matters for privacy requests.
Record when a chunk was embedded, so stale content is easy to find.

Combine meaning with keywords

Vector search is good at meaning and weak at exact terms such as part numbers and names. PostgreSQL full-text search is the opposite. The README suggests using them together, and combining the two ranked lists with Reciprocal Rank Fusion or a cross-encoder. Reciprocal Rank Fusion is simple: each list gives an item a score of one over its rank plus a constant, and the scores are added.

SQL

WITH semantic AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS rnk
  FROM chunks ORDER BY embedding <=> $1 LIMIT 20
),
keyword AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank_cd(to_tsvector('english', body), plainto_tsquery('english', $2)) DESC) AS rnk
  FROM chunks
  WHERE to_tsvector('english', body) @@ plainto_tsquery('english', $2)
  LIMIT 20
)
SELECT COALESCE(s.id, k.id) AS id,
       COALESCE(1.0 / (60 + s.rnk), 0) + COALESCE(1.0 / (60 + k.rnk), 0) AS score
FROM semantic s FULL OUTER JOIN keyword k ON s.id = k.id
ORDER BY score DESC
LIMIT 10;

The constant 60 is a common default, not a law. Tune it, and the list lengths, against queries your users actually run.

Embeddings need access control too

OWASP's 2025 list includes LLM08: Vector and Embedding Weaknesses. It names unauthorized access and data leakage when access controls on embeddings are inadequate or misaligned, embedding inversion, where attackers recover significant amounts of source information from embeddings, and data poisoning. Its mitigations include permission-aware vector stores, partitioning datasets so users and groups cannot reach each other's data, and detailed immutable logs of retrieval activity.

PostgreSQL gives you a native tool for this: row-level security. A policy can restrict each query to one tenant's rows, even when the application forgets a WHERE clause.

SQL

ALTER TABLE chunks ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON chunks
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

-- Per request, in the same transaction:
SET LOCAL app.tenant_id = '42';

Row-level security does not apply to table owners or superusers by default, so the application should connect as a role that is neither. Combine it with a limited role for any model-driven queries.

Running it in practice

01Pick the model firstIt sets the dimension count, the cost and the distance function.
02Start without an indexLoad data and measure exact search. If it is fast enough, you may not need an index yet.
03Add an index and measure recallCompare against the exact results on real queries.
04Test filters and tenantsCheck that filtered queries still return enough rows.
05Plan the re-embedKeep the model name and source text so changing models is a batch job, not a rebuild.
06Back up and restoreVector tables are large. Time a restore, as in the backup restore test.

When a separate vector database is worth it

A vector column in PostgreSQL is a good fit when your data is already there, when you want transactions and joins with ordinary tables, and when the collection is moderate in size. Very large collections, strict latency targets at high query rates or specialised features can justify a dedicated system. The cost is another store to secure, back up and keep in sync. Decide with measurements from your own data, not with a default.

Chunking: what goes in each vector

A single embedding of a long document blurs everything in it together. Splitting text into chunks lets a search find the passage that answers the question. There is no single right size. Short chunks are precise but lose context; long chunks keep context but dilute meaning. Typical choices are a few hundred words, often with a small overlap between neighbours, and splitting at headings or paragraphs where you can.

Split on natural boundaries, such as headings and paragraphs, before falling back to a fixed length.
Keep a chunk index and a link to the source, so an answer can cite where it came from.
Store the title or section heading with each chunk and consider including it in the text you embed.
Use the model's documented limits on input length, and keep chunks well under them.
Re-chunk and re-embed when your splitting rule changes. Store the rule's version alongside the model name.

Test chunking with real questions. Take twenty questions your users ask, find the passage that answers each, and check whether search returns it. If it does not, change the chunking before you tune the index.

Evaluate retrieval with a small labelled set

You cannot improve what you do not measure. Build a table of test questions with the chunk or document that should be returned, run the search, and compute how many expected results appear. This is often called recall at k. The SQL below was run on sample data in SQLite; the idea is the same in PostgreSQL.

SQL

CREATE TABLE expected (q INTEGER, doc INTEGER);  -- what should be found
CREATE TABLE returned (q INTEGER, doc INTEGER);  -- what the search returned (top k)

INSERT INTO expected VALUES (1, 10), (1, 11), (1, 12), (2, 20);
INSERT INTO returned VALUES (1, 10), (1, 11), (1, 99), (2, 20);

SELECT e.q,
       1.0 * SUM(r.doc IS NOT NULL) / COUNT(*) AS recall_at_k
FROM expected e
LEFT JOIN returned r ON r.q = e.q AND r.doc = e.doc
GROUP BY e.q;
-- 1 | 0.667     2 | 1.0

Run the same set whenever you change the embedding model, the chunking, the index or its settings. Keep the results in a table with the date and the settings, so you can see whether a change helped.

Read the query plan

To see whether an index is used, put EXPLAIN ANALYZE before the query. For a nearest-neighbour search, the plan should show an index scan on the vector index rather than a sequential scan over the whole table. If you see a sequential scan, check three things:

01Operator and index agreeThe operator class on the index must match the distance operator in the query.
02The query has ORDER BY and LIMITApproximate indexes serve queries that order by distance and limit the result.
03The table is large enoughOn a small table the planner may reasonably prefer a scan.

SQL

EXPLAIN ANALYZE
SELECT id FROM chunks ORDER BY embedding <=> $1 LIMIT 10;

Changing models and dimensions

The number in vector(1536) is part of the column type. A model that produces a different number of dimensions needs a different column, and its vectors cannot be compared with the old ones. The safest approach is to add a new column, fill it in the background, switch queries to it, and drop the old one later.

SQL

ALTER TABLE chunks ADD COLUMN embedding_v2 vector(3072);
-- fill embedding_v2 for every row with the new model, in batches
CREATE INDEX ON chunks USING hnsw (embedding_v2 vector_cosine_ops);
-- switch the application to embedding_v2, then later:
ALTER TABLE chunks DROP COLUMN embedding;

Check the maximum index dimensions for your vector type first. A plain vector column can be indexed only up to 2,000 dimensions, so a 3,072-dimension model needs another approach, such as half-precision storage, which the README says can be indexed to 4,000 dimensions. Verify against the version you run.

Backups, restores and the extension

Vector data is ordinary table data, so pg_dump backs it up with the rest of the database. The extension must be available on the server where you restore it, at a compatible version, or the restore will fail on the vector type. Add that to your restore test: restore into a clean server with the same extension installed, and run a similarity query against it.

pg_dump -Fc app -f app.dump
createdb app_restore_test
pg_restore -d app_restore_test app.dump
psql app_restore_test -c "SELECT count(*) FROM chunks;"

Large vector tables also take time to re-index after a restore, so time the whole procedure and include it in your recovery estimate.

A sizing example

Using the storage formula from the README, a table of 2 million chunks with 1,536-dimension vectors takes about 4 × 1,536 + 8 = 6,152 bytes per vector, so roughly 12.3 gigabytes for the vectors alone. An HNSW index adds more, and PostgreSQL adds its own per-row overhead. Half-precision vectors at 2 × 1,536 + 8 = 3,080 bytes would halve the vector storage. Run the arithmetic for your own model before you commit to a design, and check actual sizes with pg_total_relation_size once data is loaded.

Chunks	Dimensions	vector (4 bytes)	halfvec (2 bytes)
100,000	1,536	about 0.6 GB	about 0.3 GB
1,000,000	1,536	about 6.2 GB	about 3.1 GB
10,000,000	1,536	about 61.5 GB	about 30.8 GB
1,000,000	768	about 3.1 GB	about 1.5 GB

These figures cover the vectors only. Add the text, the indexes and your other columns. They are estimates from the documented formulas, not measurements.

Deleting and updating

When a source document is removed or edited, its embeddings are stale. Cascade deletes from the document to its chunks, as in the schema above. For edits, replace the document's chunks inside a transaction so a reader never sees a half-updated document.

SQL

BEGIN;
DELETE FROM chunks WHERE document_id = $1;
-- insert the new chunks and their embeddings here
COMMIT;

If privacy rules require you to delete a person's data, the vectors derived from it must go too. OWASP warns that source information can sometimes be recovered from embeddings, which is a reason to treat deletion of vectors as seriously as deletion of text.

Questions that come up

Do I need a dedicated vector database?: Not always. For moderate sizes, a vector column in PostgreSQL keeps one system to run. Very large or specialised workloads may justify a separate one.
Can I use pgvector with SQLite?: Not through pgvector, which is a PostgreSQL extension. SQLite has separate extensions for vectors.
Why did my filtered query return too few rows?: The index scan happens before the filter. Use an ordinary index, a partial index, partitions or iterative scans, as described above.
Do I need an index at all?: Not for small tables. Exact search is correct and simple. Add an approximate index when exact search is too slow, and measure the recall you give up.
Can I change embedding models later?: Yes, but you must re-embed everything you want to compare. Keep the original text and a model name column so it is a batch job.