All chapters
  1. Start hereRetrieval is a query. The model reads only what it returns
  2. Foundations · 1What retrieval means: lookup, keyword search, semantic search
  3. Foundations · 2Traditional SQL first: WHERE, indexes, full-text search and BM25
  4. Foundations · 3Vectors without AI: coordinates, distance and nearest neighbors
  5. Foundations · 4Embeddings: how a model turns text into a vector
  6. Foundations · 5Choosing an embedding model: quality, cost, privacy and fit
  7. Search · 6Schema and storage: where the vectors live
  8. Search · 7Exact vector search: compare the query with every row
  9. Search · 8Approximate nearest neighbors: HNSW, IVF and the recall trade
  10. Search · 9PostgreSQL and pgvector: vectors inside a relational database
  11. Search · 10SQLite vector search: one file on one machine
  12. Pipeline · 11Chunking and ingestion: from a document to retrievable units
  13. Pipeline · 12Retrieval pipelines: from a question to the context the model reads
  14. Pipeline · 13Hybrid search: words and vectors together
  15. Pipeline · 14Reranking: a second, more careful look at the candidates
  16. Pipeline · 15Top-k and scale: ten thousand, a hundred thousand, a million rows
  17. Pipeline · 16Two corpora: similar applicants and underwriter precedents
  18. Pipeline · 17Grounded generation: citations, abstention and conflicts
  19. Pipeline · 18Evaluating retrieval: recall@k, MRR, nDCG and faithfulness
  20. Practice · 19Google Cloud choices: Cloud SQL, AlloyDB, Vector Search, Agent Retrieval, RAG Engine, Agent Search
  21. Practice · 20Other ecosystems: libraries, engines and managed services
  22. Practice · 21Operations: migrations, deletes, permissions and freshness
  23. Practice · 22Advanced retrieval: rewriting, hierarchies, late interaction, graphs, agents
  24. Practice · 23Reference architectures: local-first, dual-corpus, managed, Postgres-native
  25. Practice · 24Framework recipes and the decision guide
  26. ReferenceGlossary
  27. AboutAbout this site and its sources
Search · 9

PostgreSQL and pgvector: vectors inside a relational database

The extension, a vector(384) column, distance operators, HNSW and IVFFlat indexes, filters and iterative scans, transactional updates, EXPLAIN and maintenance, run on a real database.

One extension, one column
pgvector adds a vector(384) column type, distance operators and two index types to PostgreSQL. The vector sits in the row, next to the facts it describes.
Operator and index must agree
<=> cosine, <-> L2, <#> negative inner product. An index built with an operator class serves only its own operator, sorted ascending, with a LIMIT.
Filters change the answer
An HNSW scan filters after it walks the graph. On the throwaway database a selective filter returned 0 rows; an iterative scan returned 10.

The query is ordinary. Find the ten vectors nearest to a query vector among the rows in category 7, which holds 200 of the 20,000 rows of a toy table. It runs on a real PostgreSQL 16.14 with pgvector 0.8.3, in a throwaway container, with an HNSW index on the vector column. It returns zero rows. No error, no warning: an empty result, which the application reads as "nothing similar exists".

The same query with one setting changed, hnsw.iterative_scan = relaxed_order, returns all ten. The plan of that second run reports 1,409 rows removed by the filter while the index was being walked.

Move the scene to the insurance agent. Corpus A asks for applicants similar to SUB-1004 in wind zone C within its own book. If that filter is selective and the index behaves like the first run, the agent is told there are no similar applicants and writes its recommendation without them. That is this site's own case UW-R09, and the previous page showed its mechanism on toy points. This page runs it on a database, explains exactly what the HNSW index holds and what the planner does with a filter, and shows the settings, indexes and checks that keep a filtered query honest. Every SQL output here is from examples/rag-uw/sql/OUTPUT.md: one run on one laptop, toy data; not a benchmark.

What this is not. pgvector is an extension of a general database, not a vector database: the ecosystems page compares it with dedicated engines. Managed PostgreSQL offerings, including the pgvector version Cloud SQL lists, are on Google Cloud. How the HNSW graph works inside is on approximate nearest neighbors.

The extension, and which version you are reading about#

An extension is a package that adds types, functions, operators and index methods to PostgreSQL without changing the server. pgvector's README, as documented on 2026-11-18, enables it with CREATE EXTENSION vector, once in each database where it is used; the extension's name in SQL is vector. After that, a vector is a column type like text or date, and a vector query is a SELECT. The first primitive of this site holds unchanged: one vector per row, one distance per compared row, rows sorted, the nearest k returned. Retrieval is a query. The model reads only what it returns.

Before AI. A PostgreSQL engineer already relies on this pattern: an extension that brings a new data type with its own operators and index support, so that a domain-specific search becomes an indexed WHERE or ORDER BY inside ordinary SQL, inside ordinary transactions, under ordinary backups. pgvector does the same for vectors. Everything the team knows about running PostgreSQL still applies.

Versions matter on this page because behavior changed between them. The throwaway database ran 0.8.3. The latest release is 0.8.7, dated 2026-10-01 in the changelog; 0.9.0 is listed as unreleased, and its notes include dropping support for PostgreSQL 13 and 14. Cloud SQL for PostgreSQL lists 0.8.5. Iterative index scans, used below, arrived in 0.8.0. Check SELECT extversion FROM pg_extension WHERE extname = 'vector' before trusting any statement about a setting, this page included.

The column: vector(384), and what one row costs#

A vector type is a column type that stores a fixed-length list of numbers. vector(384) stores 384 single-precision floats per row; the README documents 4 bytes per dimension plus 8, so one 384-dimensional vector takes 1,544 bytes, and up to 16,000 dimensions can be stored (2,000 can be indexed with HNSW or IVFFlat). Declaring the dimension makes a wrong-length vector fail at insert, which is the easy kind of failure.

The spec of this site gives the production shape. It is shown here as written; it did not run as is, because the throwaway schema uses vector(16) for toy vectors (the queries are otherwise the same shape). It shows the column next to its facts, the model name next to the vector, the HNSW index with its operator class, and a query with bound parameters.

-- production shape from the site's spec, section 5 (documented shape; the throwaway ran the vector(16) version)
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE applicant_profiles (
  profile_id bigint PRIMARY KEY,
  roof_type text NOT NULL,
  risk_region text,
  profile_summary text NOT NULL,
  embedding_model text NOT NULL,
  embedding vector(384)
);

CREATE INDEX applicant_embedding_hnsw
  ON applicant_profiles USING hnsw (embedding vector_cosine_ops);

-- Bind $1 as a query vector generated by the SAME compatible embedding pipeline.
SELECT profile_id, profile_summary, embedding <=> $1 AS cosine_distance
FROM applicant_profiles
WHERE risk_region = $2
ORDER BY embedding <=> $1
LIMIT 50;

The embedding_model column is not decoration. A dimension of 384 says nothing about which model produced a vector, and two models with the same dimension produce incomparable spaces; the embeddings page shows it. Production queries add AND embedding_model = $3, as the example's Q8 does, and the insurance build versions its text with an embedding_version.

Operators: three distances and a negative#

pgvector writes each distance as an operator between two vectors. The README, as documented on 2026-11-18, lists these:

OperatorDistanceSmaller means closer?Note
<->Euclidean (L2)yes
<=>cosine distanceyescosine similarity = 1 − cosine distance
<#>negative inner productyesmultiply by −1 for the inner product
<+>L1 (taxicab)yes
<~>Hammingyesbit vectors
<%>Jaccardyesbit vectors

The odd one is <#>. The inner product is larger-is-closer, but PostgreSQL index scans only support ascending order, so pgvector returns its negative: the most aligned rows get the smallest values and sort first. For vectors normalized to length 1, the README says inner product gives the best performance; on normalized vectors, cosine, L2 and inner product rank rows the same way, as the vectors page computes by hand.

Operator classes: what an HNSW index actually holds#

This is the section to read twice, because most surprises with pgvector start here.

An HNSW index in pgvector is a graph over the values of one vector column, built for one distance. The distance is chosen by the operator class named in CREATE INDEX: an operator class is the piece that tells PostgreSQL which operator an index can serve. The README lists vector_l2_ops, vector_ip_ops, vector_cosine_ops and vector_l1_ops for vector, with halfvec_* and sparsevec_* equivalents and bit_hamming_ops and bit_jaccard_ops for bits. The example's schema builds USING hnsw (embedding vector_cosine_ops), so its graph links rows by cosine distance and serves ORDER BY embedding <=> ....

What the index does not hold matters as much. It holds no other column: not wind_zone, not tenant, not status, not the text. It knows nothing about the model that made the vectors. It does not index NULL vectors, and for cosine it does not index zero vectors, as the README notes. Each entry points back to its table row, and the row supplies everything else.

Diagram: What a pgvector HNSW index holds, and what the row supplies

Read left to right. CREATE INDEX names one vector column and one operator class. The index is a graph over that column's vectors for that distance, with pointers to table rows. Filter columns, text and the model name live only in the row, so the planner checks a filter on each row the walk returns, and only a query ordered by the matching operator, ascending, with a LIMIT, can use the index.

  1. Definition
    1. CREATE INDEX: One column, one distance: the operator class picks the operator the index serves.
  2. Inside the index
    1. Graph of vectors: Each entry is one row's vector, linked to near vectors by that distance.
  3. Only in the row
    1. Row columns: The index points back to the row; every filter column and fact lives there.

Three consequences follow. A query sorted by <-> cannot use a vector_cosine_ops index; it runs exact, or uses another index built for L2. A filter cannot be checked inside the graph walk, because the filter's columns are not in the graph; it is checked on each row the walk returns. And the README's troubleshooting section states the form a query must take before the planner will use the index at all: ORDER BY a distance operator (not an expression), ascending, with a LIMIT.

-- from pgvector's README, troubleshooting (as documented on 2026-11-18; not run here)
-- index
ORDER BY embedding <=> '[3,1,2]' LIMIT 5;

-- no index
ORDER BY 1 - (embedding <=> '[3,1,2]') DESC LIMIT 5;

The second line computes cosine similarity and sorts it descending: the same ranking, and an expression the index cannot serve. Sort by the operator, ascending, and compute the similarity in the SELECT list if a reader needs it.

Index DDL: HNSW and IVFFlat with their documented defaults#

The example's schema creates its HNSW indexes with explicit settings. These lines ran on the throwaway database:

-- from examples/rag-uw/sql/schema.sql (ran on the throwaway database, 2026-11-18)
CREATE INDEX guidance_embedding_hnsw   ON guidance_passages  USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);
CREATE INDEX profiles_embedding_hnsw   ON applicant_profiles USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);
CREATE INDEX precedents_embedding_hnsw ON precedents         USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

m = 16 and ef_construction = 64 are pgvector's documented defaults, written out so that a reader of the schema sees them. The query-time setting is hnsw.ef_search, default 40, and the README recommends SET LOCAL inside a transaction to change it for one query. IVFFlat takes a number of lists at build and ivfflat.probes at query time:

-- from pgvector's README (as documented on 2026-11-18; not run here)
CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);
SET ivfflat.probes = 10;

The README's starting points: lists = rows / 1000 up to a million rows and sqrt(rows) above; probes defaults to 1 with sqrt(lists) as a starting point; build IVFFlat after the data is loaded, since its lists are learned from the data; HNSW can be built on an empty table. In production it suggests CREATE INDEX CONCURRENTLY so that the build does not block writes. The meaning of each setting is on the ANN page; the two facts to keep from here are the defaults and that hnsw.ef_search also caps how many rows an HNSW scan can return.

Reading plans: EXPLAIN ANALYZE says which search ran#

EXPLAIN ANALYZE runs a statement and prints its plan with the rows each step actually produced. The PostgreSQL documentation, read on 2026-11-18, notes that it really executes the statement, so a write under EXPLAIN ANALYZE must sit inside BEGIN ... ROLLBACK; in PostgreSQL 18 the documentation adds that BUFFERS is on automatically with ANALYZE. The example ran PostgreSQL 16 with COSTS OFF, TIMING OFF, SUMMARY OFF, so only plan shapes and row counts appear.

For vector queries the plan answers the most important question: was this search exact or approximate? Q10 forces each plan in turn on the 20,000-row toy table (the vector literal is shortened to '[...]'):

-- with enable_indexscan = off
 Limit (actual rows=10 loops=1)
   ->  Sort (actual rows=10 loops=1)
         Sort Key: ((embedding <=> '[...]'::vector(16)))
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on scale_points (actual rows=20000 loops=1)

-- with enable_seqscan = off
 Limit (actual rows=10 loops=1)
   ->  Index Scan using scale_points_hnsw on scale_points (actual rows=10 loops=1)
         Order By: (embedding <=> '[...]'::vector(16))

The first reads every row and sorts: exact. The second walks the HNSW graph and returns ten rows in distance order: approximate. Because the query forced each plan, the output does not say which one the planner would pick by itself. The insurance build's documentation records that choice for its own data: unfiltered at 1,140 rows the planner preferred a sequential scan, and the HNSW walk appeared only with enable_seqscan = off (in that build, not a benchmark). On a small table, an exact scan is often what the planner chooses, and that is correct.

Filters: why an approximate scan can return fewer rows#

Now the opening query. Here is what happens inside, step by step, with the README's own wording as the source.

With approximate indexes, the README says, filtering is applied after the index is scanned. The HNSW scan walks the graph and produces rows in order of distance, up to hnsw.ef_search of them (40 here). Each row then meets WHERE category = 7. In this table one row in a hundred passes, so of the 40 rows the walk produced, on average fewer than one is in category 7. The README's own example gives the same arithmetic: 10% matching with ef_search 40 leaves about 4 rows. Here it left none. Q12 runs the query with iterative scan off, then with relaxed_order, then prints the plan of the second:

-- Q12 from examples/rag-uw/sql/queries.sql (ran on the throwaway database, 2026-11-18)
SET enable_seqscan = off;
SET hnsw.ef_search = 40;
SET hnsw.iterative_scan = off;
SELECT count(*) AS rows_returned_iterative_off FROM (
  SELECT id FROM scale_points WHERE category = 7 ORDER BY embedding <=> :'sq'::vector(16) LIMIT 10) AS t;
SET hnsw.iterative_scan = relaxed_order;
SELECT count(*) AS rows_returned_relaxed_order FROM (
  SELECT id FROM scale_points WHERE category = 7 ORDER BY embedding <=> :'sq'::vector(16) LIMIT 10) AS t;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
  SELECT id FROM scale_points WHERE category = 7 ORDER BY embedding <=> :'sq'::vector(16) LIMIT 10;
 rows_returned_iterative_off 
-----------------------------
                           0

 rows_returned_relaxed_order 
-----------------------------
                          10

 Limit (actual rows=10 loops=1)
   ->  Index Scan using scale_points_hnsw on scale_points (actual rows=10 loops=1)
         Order By: (embedding <=> '[...]'::vector(16))
         Filter: (category = 7)
         Rows Removed by Filter: 1409

An iterative scan is a setting that lets the index scan continue past its first batch when the filter removed too many rows, until enough rows pass or a limit is reached. Added in 0.8.0 and off by default, hnsw.iterative_scan takes strict_order, which keeps results in exact distance order, or relaxed_order, which allows slightly out-of-order results for better recall; ivfflat.iterative_scan supports relaxed_order. hnsw.max_scan_tuples, default 20,000, bounds how far the scan goes. In the plan, Rows Removed by Filter: 1409 is the work the first run never did: the walk had to visit well over a thousand rows outside category 7 to find ten inside it.

Diagram: One filtered query, three plans: post-filtered HNSW, iterative scan, btree plus exact sort

The query asks for the 10 nearest rows in category 7 (200 of 20,000 toy rows). Top: the HNSW walk stops at its beam of ef_search 40 and the filter removes them all, so 0 rows return. Middle: with hnsw.iterative_scan = relaxed_order the walk continues, 1,409 rows are removed by the filter and 10 return. Bottom: a btree fetches the 200 matching rows and an exact sort returns the true 10. Numbers from Q12 and Q13, one run on toy data.

  1. HNSW, iterative scan off
    1. Graph walk: The index returns up to 40 nearest rows of any category.
    2. Filter afterwards: The filter runs on the returned rows and removes them all; no error is raised.
  2. HNSW, iterative scan on
    1. Walk continues: The scan keeps going until enough rows pass the filter or a limit is reached.
    2. 10 rows: Enough rows return; relaxed order may be slightly off, so re-sort.
  3. Btree on the filter column
    1. Fetch the matches: The btree finds exactly the 200 rows in category 7.
    2. Exact sort: All 200 are ranked by distance: the true top 10, chosen by the planner.

The diagram follows one query down both paths. With iterative scan off, the walk stops at its beam and the filter empties it. With iterative scan on, the walk continues until ten rows pass. The third path, on the right, never touches the graph.

With relaxed_order the README suggests re-sorting the rows in a materialized common table expression, so the final order is exact again:

-- from pgvector's README (as documented on 2026-11-18; not run here)
WITH relaxed_results AS MATERIALIZED (
    SELECT id, embedding <-> '[1,2,3]' AS distance FROM items WHERE category_id = 123 ORDER BY distance LIMIT 5
) SELECT * FROM relaxed_results ORDER BY distance + 0;

The README notes that + 0 is needed on PostgreSQL 17 and later. The insurance build does exactly this pairing: hnsw.iterative_scan = relaxed_order when pgvector is 0.8 or later, then a re-sort.

The other fix: a btree and an exact sort#

The README suggests a different first move for filtered queries: index the filter column with an ordinary index, because exact search suits conditions that match a small share of rows. Q13 adds a btree on category and asks the planner, without forcing anything:

== Q13 with a btree on the filter column the planner may prefer an exact sort over ~200 rows
CREATE INDEX
ANALYZE
 Limit (actual rows=10 loops=1)
   ->  Sort (actual rows=10 loops=1)
         Sort Key: ((embedding <=> '[...]'::vector(16)))
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Bitmap Heap Scan on scale_points (actual rows=200 loops=1)
               Recheck Cond: (category = 7)
               Heap Blocks: exact=200
               ->  Bitmap Index Scan on scale_points_category (actual rows=200 loops=1)
                     Index Cond: (category = 7)

The planner used the btree to fetch the 200 rows of category 7 and sorted them exactly by distance. Ten rows, perfect recall, no graph walk and no iterative setting. This is exact search over the filtered rows, the plan the exact search page describes, chosen by the planner because the filter made it cheap.

In the insurance build. In that build, the planner made the same choice on real-shaped data: with the selective filter, it used the btree on (state, zip3, dwelling_type) and an exact sort over about 190 rows (reported in that build's docs; in that build, not a benchmark). Its precedent corpus takes this design on purpose, with no ANN index at all.

For a few distinct filter values, the README suggests partial indexes, one HNSW index per value (... USING hnsw (embedding vector_l2_ops) WHERE (category_id = 123)); for many values, partitioning. The choice among iterative scan, btree plus exact sort, partial index and partition depends on how selective the filter is and how many distinct values it has, which the scale page turns into a benchmark.

The labs: operators, plans and a starving filter#

The SQL lab with the pgvector preset starts on the filter-plus-vector template, adds the operator choice (<->, <=>, <#>) and an HNSW checkbox that changes the plan sketch and the note on what is exact. The toy vectors are normalized, so all three operators give the same order. The plan sketch is a sketch, not EXPLAIN output. Toy encoders and synthetic data show how the mechanism works, not how good any model or product is.

Without JavaScript (query "roof replaced recently but aerial imagery shows an older roof", cosine distance, k = 5; the same order with <-> and <#> on these normalized vectors; in this example):

#No filterdistancepolicy_version = '2026.2' AND trusteddistance
1GD-NOTE-010.2293GD-NOTE-010.2293
2GD-2025.4-R-ROOF-03 (superseded)0.3035GD-2026.2-R-ROOF-030.3035
3GD-2026.2-R-ROOF-030.3035GD-2026.2-R-ROOF-020.3792
4GD-2025.4-R-ROOF-02 (superseded)0.3792GD-2026.2-R-ROOF-010.4007
5GD-2026.2-R-ROOF-020.3792GD-NOTE-060.4636

The HNSW lab with the filter preset starts at 5% selectivity, the toy version of Q12. Raise ef_search, switch on the iterative scan, or pick the exact method, and compare the rows returned, the index recall and the distance computations (work, not latency).

Without JavaScript (query 1, k = 10, toy HNSW with M 8 over 400 points, 17 of which pass the 5% filter; in this example):

Method and settingRows returnedIndex recall@10Distance computations
HNSW, ef_search 10101.059
HNSW, ef_search 40101.0121
HNSW, ef_search 160101.0228
IVF, nprobe 1101.056
IVF, nprobe 4101.0144
5% filter, HNSW ef_search 40, post-filter30.3121
5% filter, HNSW ef_search 160, post-filter70.7228
5% filter, HNSW ef_search 40, iterative scan101.0817
5% filter, exact scan of the filtered rows101.017

The toy and the database agree in shape: post-filtering starves, the iterative scan repairs it with extra work, and an exact scan of the filtered rows is the cheapest plan when the filter is selective.

Comparing recall: EXPLAIN ANALYZE plus the exact answer#

Rows returned is not recall. The relaxed-order run returned ten rows; whether they are the true ten is a separate question, and the only way to answer it is to compute the true ten. The README suggests the method: disable index scans in a transaction to get the exact result, then compare with the indexed result. Q11 on the ANN page does this with temporary tables, for one query at three ef_search values.

EXPLAIN ANALYZE has one job in that procedure, and skipping it is a classic trap: confirm which plan each half ran. On a small table the planner may choose a sequential scan for the "indexed" query too, and then the comparison measures exact search against exact search and reports a perfect, meaningless recall. Check for Index Scan using ..._hnsw in the indexed run and for a scan feeding a Sort in the exact run. Then compare ids, average index recall over a realistic query set (filters included, since filters are where recall drops), and keep the number with the pgvector version and settings that produced it.

Transactional updates: the row and its vector change together#

A policy revision is a write: rule R-ROOF-01 of version 2026.2 might be superseded by a newer version. Because the vector sits in the same row as status, policy_version and the text, there is no second store to keep in step: one UPDATE changes the row, and a rollback undoes it. Q14 shows it, rolled back on purpose:

== Q14 a policy revision is transactional: rows and their vectors change together (rolled back)
BEGIN
UPDATE 1
     passage_id      |   status   
---------------------+------------
 GD-2025.4-R-ROOF-01 | superseded
 GD-2026.2-R-ROOF-01 | superseded

ROLLBACK
     passage_id      |   status   
---------------------+------------
 GD-2025.4-R-ROOF-01 | superseded
 GD-2026.2-R-ROOF-01 | current

Inside the transaction both versions are superseded; after the rollback the current one is current again, and no query outside ever saw the half state. With vectors in a separate system, the same revision is two writes in two places, and a reader between them can see a current-status row with an old vector or the reverse; the storage page compares the layouts.

Maintenance: build memory, vacuum and reindex#

pgvector inherits PostgreSQL's maintenance model, and its README adds vector-specific advice, as documented on 2026-11-18.

Builds. HNSW builds are much faster when the graph fits in maintenance_work_mem; PostgreSQL raises a notice when it does not, and the README warns against setting it so high that the server runs out of memory. Parallel builds use max_parallel_maintenance_workers. Progress shows in pg_stat_progress_create_index. Load with COPY and create indexes after the initial data.

Vacuum and reindex. Vacuuming an HNSW index can take a while; the README suggests REINDEX INDEX CONCURRENTLY first, then VACUUM. The PostgreSQL documentation, read on 2026-11-18, describes REINDEX CONCURRENTLY as rebuilding an index without locks that prevent concurrent inserts, updates or deletes, at the cost of two table scans; it cannot run inside a transaction block, and a failed run leaves an invalid index behind that must be dropped. Plain REINDEX blocks writes and most queries for its duration. The changelog records HNSW vacuum fixes in 0.8.3 and 0.8.4, one more reason to know your version.

Reindex is not re-embed. A reindex rebuilds the graph from the same vectors. A model change needs new vectors: the insurance build finds stale rows by embedding_version and re-embeds them in batches, and plans a model change as a new column, a backfill and a switch of reads. Both are on the operations page.

halfvec: half the bytes per dimension#

halfvec is pgvector's half-precision vector type, added in 0.7.0: 2 bytes per dimension plus 8, up to 16,000 dimensions, and indexable up to 4,000. A 384-dimensional halfvec takes 776 bytes against 1,544 for vector (arithmetic from the documented sizes). It can be the column type (embedding halfvec(384)), or only the index can use it through an expression that casts the column, which the README shows as USING hnsw ((embedding::halfvec(3)) halfvec_l2_ops) for smaller indexes. Fewer bits per number make distances slightly less precise, so measure index recall again after switching; the ANN page covers quantization in general.

In a coding agent. A coding agent that keeps its code-chunk vectors in PostgreSQL gets the same benefits and the same traps. A query restricted to one repository among hundreds is a selective filter: with an HNSW index and no iterative scan it can return fewer chunks than asked, and the agent concludes the code does not exist. A btree on the repository column, an iterative scan, or a partial index per large repository keeps the search complete, and the chunk's file path and commit sit in the same row as its vector.

How pgvector queries fail#

A selective filter with iterative scan off. Zero rows in Q12. Turn on hnsw.iterative_scan, index the filter column, or partition.

Similarity sorted descending. The index is ignored; sort by the operator, ascending.

An operator that does not match the index's operator class. A vector_cosine_ops index does not serve <->.

LIMIT above hnsw.ef_search. Asking for 50 candidates with the default 40 returns at most 40.

Recall compared without checking plans. Exact versus exact reports a perfect recall that means nothing.

Common confusion. "The planner did not use my HNSW index, so something is broken." Often nothing is broken: on a small table, or with a selective filter and a btree, an exact plan is cheaper and returns perfect recall, as Q13 and the insurance build's planner finding show. The index is worth forcing only to measure it.

A checklist for Monday#

QuestionWhy it matters
Which pgvector version runs in each environment?Iterative scans need 0.8.0; vacuum fixes landed in 0.8.3 and 0.8.4
Does every vector index name the operator class of the operator your queries use?Otherwise the index is never used
Do queries ORDER BY the operator ascending, with a LIMIT no larger than hnsw.ef_search?The documented form for index use; ef_search caps rows
For filtered queries: iterative scan on, a btree on the filter, or a partial index?A post-filtered HNSW scan can return zero rows (UW-R09, Q12)
Is index recall measured against an exact run, with both plans confirmed by EXPLAIN ANALYZE?Rows returned is not recall
Are row, status and vector updated in one transaction?Readers never see a half-revised policy
Is REINDEX INDEX CONCURRENTLY planned for heavy churn, outside a transaction block?It rebuilds without blocking writes
Partial indexes, partitions or iterative scan for tenancyThree designs for one selective filter, and when each fits

A tenant column is the most common selective filter, and pgvector offers three designs for it, each documented in its README.

An iterative scan keeps one HNSW index over all tenants and lets the scan continue until enough rows pass. It needs no schema change and adapts to any filter. Its cost grows as the filter gets more selective, because the walk visits many rows of other tenants before finding enough of this one: in Q12, 1,409 rows were removed by the filter to return 10. hnsw.max_scan_tuples bounds that work, which also means a very selective filter can still come back short.

A partial index per tenant (CREATE INDEX ... USING hnsw (...) WHERE (tenant = 'book-east')) builds a graph that contains only that tenant's rows, so the walk never meets another tenant. It suits a few large tenants. With many tenants it becomes many indexes to build, vacuum and monitor, and a new tenant needs new DDL.

Partitioning by tenant puts each tenant's rows, and its own indexes, in a separate partition, which the planner can choose by the filter value. The README suggests it for many distinct values. It also gives retention and deletion per tenant for free, which matters for the operations of a multi-tenant insurer. In all three designs the rule of the exact search page stays: the tenant condition is part of the query, never a post-processing step.

What EXPLAIN ANALYZE can and cannot tell you about recallRow counts, removed rows and the missing number

EXPLAIN ANALYZE reports how many rows each step produced and, for a filtered index scan, how many it removed. Those numbers are evidence about work and about starvation: actual rows=10 under the Limit means the query was satisfied, and a high Rows Removed by Filter means the walk spent most of its effort on rows that could never be returned. A run that returns fewer rows than its LIMIT is a starvation signal worth an alert.

What it cannot report is recall. The plan does not know which rows the exact search would have returned, so actual rows=10 is equally true of the perfect ten and of ten rows that skip a true neighbor. Recall needs the second, exact run and an id comparison. A practical setup keeps both: plan shapes and short-result counts from production queries as continuous signals, and a scheduled recall job that replays a sample of real queries exactly and through the index. Traces and logs as a discipline belong to observe.sammuthu.com.

Versions, managed services and behavior driftWhy the same SQL behaves differently in two environments

The throwaway database ran 0.8.3, the latest release is 0.8.7, Cloud SQL lists 0.8.5, and AlloyDB lists a customized 0.8.5.google-1, as documented on 2026-11-18. A laptop container, a staging instance and a managed production database can easily run three versions. The SQL is the same in all of them; the behavior is not always: iterative scans exist only from 0.8.0, and HNSW vacuum fixes landed in 0.8.3 and 0.8.4. The unreleased 0.9.0 notes list faster IVFFlat scans, vacuum progress reporting for indexes and dropped support for PostgreSQL 13 and 14.

PostgreSQL itself moves too. The example ran PostgreSQL 16; the current documentation is PostgreSQL 18, where EXPLAIN ANALYZE includes BUFFERS automatically, and the README's re-sort example needs + 0 from PostgreSQL 17. A team that copies a plan or a setting from a blog post copies its versions with it.

The defense is boring and effective: record version() and extversion with every benchmark and every recall measurement, pin the extension version in infrastructure code where the platform allows it, and rerun the recall job after any upgrade.

Dead tuples, updates and why results can shrink after churnMVCC meets a graph index

PostgreSQL updates a row by writing a new version and leaving the old one as a dead tuple until vacuum removes it. In PostgreSQL's general design, index entries for dead versions stay until vacuum cleans them, so a scan can find them and then discard them as invisible: fewer usable rows from the same beam. That is the general mechanism, stated here as an explanation, not read from pgvector's code. The README's troubleshooting note on fewer results names this directly: results are limited by hnsw.ef_search, and dead tuples and filters can reduce them further.

For the insurance example this is not hypothetical. Precedents move from pending to active and sometimes to quarantined or revoked, and every status change is an update of a row whose vector sits in the HNSW index. A table with heavy churn and a selective status filter is the case where short results appear first. Watch short-result counts, keep vacuum healthy, and follow the README's advice to reindex concurrently before vacuuming a large HNSW index. None of this changes the vectors; it is a reindex, never a re-embedding.

In one breath#

pgvector makes a vector an ordinary PostgreSQL column with distance operators and two approximate index types, so retrieval stays a SELECT inside the same transactions, joins and backups as the facts it describes. An HNSW index is a graph over one column's vectors for one operator class: it holds no filter columns, so a filter runs on the rows the walk returns, and a selective filter can starve the result, as Q12 did with zero rows. An iterative scan, a btree with an exact sort, a partial index or a partition fixes that, and only an exact run compared by id, with both plans confirmed by EXPLAIN ANALYZE, says what the index really returned.

The insurance agent's three corpora now live in PostgreSQL with indexes, filters and transactional updates that a team already knows how to run, and the starving filter of UW-R09 has measured fixes. Not every retrieval system runs next to a database server, though: Mnemos keeps private documents in one file on one laptop. The next page, SQLite, moves the same primitive into SQLite, with an extension, a virtual table, or a function you register yourself.