Why pgvector, and when to use it
Learn where vector search helps, why an existing PostgreSQL database can be a sensible starting point, and when ordinary queries are enough. Try a small SQL example before choosing an index or another database.
The question: When should a document application use pgvector rather than ordinary database queries or a separate search service?
You’ll learn: Run a small vector search, separate exact business lookups from meaning search, and check permissions, index accuracy and operating costs.
For: People building or reviewing a document search system; no database background required.
Illustrative worked example · AI-assisted source check . This is not a human expert endorsement.
What does pgvector add?#
PostgreSQL stores records in tables. Your application can ask it for one invoice, join that invoice to its line items, and record a review decision. pgvector is an extension that adds storage and search for vectors: lists of numbers, often produced from text by an embedding model.
For an application that already uses PostgreSQL, this can keep document text, source versions, access records and search data in one system. It is a starting choice, not proof that PostgreSQL is fastest for every workload. A separate search service also needs deployment, monitoring and a reliable copy of the data.
A database transaction groups changes so they succeed or fail together. For example, you can save a policy version and its ready-to-search chunks in one transaction. Create embeddings in a background job first, then publish the complete version. Avoid keeping a database transaction open while waiting for a model service.
Primary explanation of all-or-nothing database changes. The policy publication example is our application design.
The application checks the reader’s access first. Exact identifiers go to ordinary queries; policy wording can use word search and vector search. Both routes return evidence for review.
Select a box to inspect its role. On a small screen, scroll the diagram sideways.The trusted application identifies the reader, organization and permitted records.
Read the complete diagram as text
- A question or invoice: The trusted application identifies the reader, organization and permitted records.
- Exact record lookup: Match a purchase order ID, supplier and company. Check numbers with normal database operations.
- Policy search: Find wording with ordinary word search and, where it helps, embedding similarity.
- Permitted evidence: Return the matching records or policy passages with their source, version and relevant date.
- A question or invoice → known identifier → Exact record lookup
- A question or invoice → worded question → Policy search
- Exact record lookup → record match → Permitted evidence
- Policy search → relevant passage → Permitted evidence
Do not use similarity to match a purchase order#
For “Show purchase order PO-1042,” use an exact query within the correct company. Include other keys required by that ERP, such as supplier or legal entity. A nearby number is not a match. Store amounts as decimal values or agreed integer minor units, and compare them with explicit business rules.
For “What do we do when freight was not agreed?” the policy might say “additional transport charges.” Word search looks for terms. Vector search can help find related wording. Try word search first and measure whether adding vectors finds evidence it misses. PostgreSQL has built-in full-text search for word-based retrieval.
Primary documentation for searching and ranking text by query terms. Vector search is an optional additional route in this design.
Neither search route decides whether an invoice should be paid. Search finds evidence. Your matching rules and authorized reviewer decide the next action. The embeddings guide explains how text becomes vectors and how to compare models.
Try a small search you can check by hand#
This example uses invented three-number vectors. No model produced them. They let us see how the database sorts numbers; they do not demonstrate real language understanding. Run the whole SQL block in one session of a disposable PostgreSQL database with pgvector installed. Have the database administrator enable the extension first if your lesson role lacks that permission. The script creates a temporary table and rolls back the example.
BEGIN;
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TEMP TABLE lesson_policy_chunks (
id integer PRIMARY KEY,
company text NOT NULL,
model_space text NOT NULL,
body text NOT NULL,
embedding vector(3) NOT NULL
) ON COMMIT DROP;
INSERT INTO lesson_policy_chunks VALUES
(1, 'company-a', 'toy-v1', 'Review unagreed freight.', '[1,0,0]'),
(2, 'company-a', 'toy-v1', 'Check extra transport charges.', '[0.8,0.6,0]'),
(3, 'company-a', 'toy-v1', 'Keep the original invoice.', '[0,1,0]'),
(4, 'company-b', 'toy-v1', 'Another company private rule.', '[1,0,0]');
-- Exact scan: no approximate index exists yet.
SELECT id, body,
round((1 - (embedding <=> '[1,0,0]'::vector))::numeric, 3)
AS similarity
FROM lesson_policy_chunks
WHERE company = 'company-a' AND model_space = 'toy-v1'
ORDER BY embedding <=> '[1,0,0]'::vector
LIMIT 3;
-- Optional approximate index, using the same distance as the query.
CREATE INDEX lesson_policy_hnsw ON lesson_policy_chunks
USING hnsw (embedding vector_cosine_ops);
EXPLAIN (COSTS OFF)
SELECT id FROM lesson_policy_chunks
WHERE company = 'company-a' AND model_space = 'toy-v1'
ORDER BY embedding <=> '[1,0,0]'::vector
LIMIT 3;
ROLLBACK;The first query returns IDs 1, 2 and 3, with similarities 1.000, 0.800 and 0.000. It excludes company B. The <=> operator measures cosine distance. Subtract that distance from one to get cosine similarity. A larger similarity means the vectors point in a more similar direction.
Cosine similarity = (q · d) / (length(q) × length(d))
q = [1, 0, 0] d = [0.8, 0.6, 0]
q · d = 0.8 length(q) = 1 length(d) = 1
Similarity = 0.8 Distance = 1 − 0.8 = 0.2A score of 0.8 is not an 80% chance that a rule is correct. It is a geometric comparison in one model’s vector space. A wrong or outdated policy can still score highly. Source checks and effective dates remain necessary.
The SQL shows filtering, not a complete access system. Its fixed company value is teaching data. A real service must derive permitted companies from the authenticated reader and use bound query parameters. Never let an invoice, prompt or browser parameter choose the reader’s permissions.
Speed, missing results and access are separate concerns#
Without an approximate index, pgvector can check every eligible vector. This is exact nearest-neighbor search. An approximate index checks a smaller set of candidates to save time and may miss close matches. The graph-based index in the example is called HNSW, short for Hierarchical Navigable Small World.
For four rows, PostgreSQL may still choose a table scan. EXPLAIN shows its plan; creating an index does not prove it was used. Keep distance ordering and a limit in the query. Use the operator class that matches the distance measure.
An approximate search can find candidates from a shared index and then apply filters. It can return too few permitted results. pgvector 0.8.0 and later offer iterative scans to look further, within limits. This improves the opportunity to find matches; it does not guarantee complete recall.
Primary reference for cosine distance, exact and approximate search, HNSW, iterative scans and supported dimensions. Checked 14 September 2026.
Put organization, document access, model space and date conditions in the retrieval query before choosing the final winners. Apply the same checks to word search, returned parent sections, caches and citations. Do not retrieve unrestricted content and hope to remove it after an answer has been generated.
PostgreSQL row-level security, or RLS, can enforce rules on which rows an application role may read. Use a restricted application role and test it. Table owners normally bypass RLS; superusers and roles with BYPASSRLS bypass it too. FORCE ROW LEVEL SECURITY can cover the owner, but does not remove the other bypasses.
Primary reference for row access policies and the table-owner, superuser and BYPASSRLS exceptions.
Access tests and search-quality tests need separate results. A search can leak nothing and still miss the right permitted passage. Compare approximate results with an exact search under the same filters. Include a small company inside a large shared collection, expired policies and readers with limited document access.
Keep each embedding model in its own search space#
A production column might use 1,536 dimensions for a selected model configuration. That is the length of each vector, not a score for its quality. The current HNSW vector limit is 2,000 dimensions; halfvec supports 4,000 with lower numeric precision. Storage and index limits are different. Check your installed version.
Choose the model first using your own retrieval questions. Keep its name, version or deployment identifier, dimensions and input settings with each batch. Two models producing the same number of dimensions can still use incompatible vector spaces. Do not compare their vectors directly.
When changing models, build a separate table or index namespace. Embed the same source material with the new settings, evaluate it, and switch only when it is ready. Embed queries with the matching configuration. Keep a route back to the old index while checking the change.
When should you use something else?#
| Situation | Reasonable next step |
|---|---|
| You only match invoice IDs and totals | Use ordinary tables, keys and queries. No vector search is needed. |
| A small policy set is already found reliably | Keep word search or exact vector scans until measurement shows a need. |
| PostgreSQL is already operated well | Test pgvector alongside existing data, with realistic query and update loads. |
| Search competes with business transactions | Measure isolation options, including a separate database or search service. |
| You need a feature or scale your setup cannot meet | Compare a dedicated service using the same data, permissions, recall target and cost calculation. |
Measure response time during busy periods, missed relevant passages, update delay, memory, disk and total operating cost. Include backup recovery and the time needed to rebuild an index. A benchmark on public text with no access filters cannot settle your company’s choice.
Plan routine maintenance too. Updated and deleted PostgreSQL rows leave old versions that vacuuming later cleans up for reuse. Monitor that work and index growth; do not assume deleting a file immediately shrinks database storage.
Primary reference for obsolete row versions, space reuse and routine maintenance.
For the invoice case, PostgreSQL is a practical starting point because the workflow already needs exact records and review history. Add pgvector only for a measured improvement in policy search. The chunking guide covers what text should enter that search.