The Complete pgvector Guide

This is part one of three. It covers everything you need to start using pgvector for real work, not a teaser. By the end you will be able to install the extension, create a table that stores embeddings, load data into it, ask "which rows are most similar to this one?", make that question fast with an index, call all of it from Python, and read the errors you will certainly meet along the way. Mid-level and Senior take the same topics further; nothing here is thrown away.

Each section ends with a Try it task. Do them as you go. They take a few minutes each, and these ideas only stick once you have watched a query return the wrong rows and then fixed it yourself. The guide was checked against pgvector 0.8.6, the current release at the time of writing, so every command here is written for that version.

You do not need to know machine learning to follow along. You do need to be comfortable with basic SQL: CREATE TABLE, INSERT, SELECT ... WHERE, and ORDER BY. If those are unfamiliar, spend an hour with a PostgreSQL tutorial first, because pgvector is not a new language. It is a handful of new column types and operators dropped into the SQL you already know.

What pgvector is, and the problem it solves

Modern AI applications constantly need to answer a question of the form "what is like this?" A support bot wants the three help articles closest in meaning to a customer's question. A shop wants products similar to the one you are viewing. A retrieval-augmented generation (RAG) application wants the paragraphs most relevant to a prompt before it calls a language model. None of these can be answered by a normal WHERE clause, because "similar in meaning" is not something you can express as name = 'x' or even name LIKE '%x%'.

The standard solution is the embedding. An embedding model (a neural network such as the ones behind the OpenAI, Cohere, or open-source sentence-transformer families) reads a piece of text, or an image, and outputs a fixed-length list of numbers, for example 384 or 1,536 of them. That list is called a vector. The model is trained so that things with similar meaning produce vectors that sit close together, and unrelated things sit far apart. "How do I reset my password?" and "I forgot my login credentials" produce vectors that are near each other, even though they share almost no words.

Once your data is vectors, "find similar items" turns into a geometry problem: given a query vector, find the stored vectors with the smallest distance to it. This is called nearest-neighbour search, and it needs two things: somewhere to store vectors, and a fast way to search them.

pgvector is an open-source extension for PostgreSQL that gives Postgres both. It adds a vector column type, operators that compute distance between vectors, and two kinds of index that make nearest-neighbour search fast on large tables. Its licence is the PostgreSQL License, it is written in C, and it is maintained as an open-source project at github.com/pgvector/pgvector.

A fair question is why anyone would not just use a dedicated vector database. Several exist, and this series covers some of them, such as Qdrant, Chroma and Pinecone. The honest answer is that for many teams, the vectors are not the whole system. Your documents also have owners, permissions, timestamps, categories, and relations to other tables. When the vectors live in the same database as the rest of that data, a lot of things come free:

• One system to run. You already operate Postgres: backups, monitoring, access control, and on-call runbooks exist. There is no second cluster to learn, secure, or pay for.
• Transactions. Inserting a document and its embedding is one atomic operation. You never have a row in one system and a missing vector in another.
• Joins and filters. "Find the five most similar articles that this user is allowed to see and that were published this year" is a single SQL query, not a vector search followed by a manual filter in application code.
• Everything Postgres already does. Replication, point-in-time recovery, row-level security, and pg_dump all work on your vector columns because they are ordinary columns.

Notice what pgvector does not do. It does not create embeddings. The model that turns text into numbers runs outside the database: you call an API, or run a local model, and then hand the resulting list of numbers to Postgres. pgvector only stores, compares, and indexes the numbers. Beginners often assume the extension "understands" text. It does not; it understands lists of numbers, and the meaning lives in the model that produced them.

A word on naming, because it trips people up on day one. The project, the repository, the Docker image and the Linux packages are all called pgvector. But the extension inside the database is called vector. You type CREATE EXTENSION vector;, not CREATE EXTENSION pgvector;, and the column type is vector(3), not pgvector(3).

The mental model: vectors, distance, and indexes

pgvector rests on a handful of nouns. Learn them well and the rest of the guide is variations on a theme.

A vector is a list of numbers of fixed length. In SQL you write one as text in square brackets: '[1,2,3]'. The length is called its dimension or number of dimensions. A column declared as vector(3) holds vectors of exactly three numbers and rejects any other length. Real embedding models produce many more dimensions: 384, 768, 1,024 and 1,536 are all common. Three dimensions is only for learning, because you can imagine three numbers as a point in a room.

That picture is worth keeping. Think of each vector as a point in space. Two points that are close together mean similar things. A query is just another point, and "search" means "list the stored points nearest to this one."

A distance is a number measuring how far apart two points are. There are several ways to measure it, and pgvector gives each its own operator, which is a short symbol you place between two vectors in SQL:

All of them return a number where smaller means more similar, so "nearest first" is always ORDER BY ... ASC. There are also <~> (Hamming) and <%> (Jaccard) operators for a special kind of vector called a bit vector, which you can ignore for now.

An exact search is the simplest thing that could work. With no index at all, Postgres reads every row, computes the distance from your query to each stored vector, sorts the results, and returns the top few. This is guaranteed to give the true nearest neighbours, which is called perfect recall. It is also perfectly fine for a few thousand or tens of thousands of rows. It only becomes slow when the table is large.

An approximate search is what an index gives you. For large tables, comparing against every row is too slow, so pgvector offers indexes that find very likely nearest neighbours without checking everything. The trade-off is that the answer might occasionally miss a true neighbour. The fraction of true neighbours that the search actually returns is called recall. A recall of 0.95 means that, on average, 95 of the true top 100 came back. This is the one genuinely new idea compared with ordinary SQL indexes. A B-tree index never changes the answer, only the speed. A vector index can change the answer. The official documentation says it directly: unlike typical indexes, you will see different results for queries after adding an approximate index.

pgvector ships two index types:

• HNSW (Hierarchical Navigable Small World) builds a layered graph of connections between vectors, and a search hops through the graph toward the query. It gives the best speed-versus-recall trade-off, needs no training step, and can be created on an empty table. The cost is slower builds and more memory.
• IVFFlat (inverted file with flat lists) divides the vectors into clusters using k-means, then searches only the few clusters nearest to the query. It builds faster and uses less memory but has a worse speed-versus-recall trade-off, and it must be built after the table holds representative data.

Finally, an operator class (usually shortened to "opclass") tells an index which distance it is built for. An index built with vector_cosine_ops speeds up queries that use <=>. It does not speed up queries that use <->. This is the number-one reason a beginner's index "does nothing", and we return to it more than once.

Which metric to choose is simple: use the one your embedding model was trained for, which is usually cosine for text. If the vectors are normalised to length 1, inner product gives the same ranking and is the cheapest to compute. Use the same operator in every query and in the index.

Installing pgvector and checking it works

Installation has two parts, and beginners often forget the second. First the extension's files must exist on the machine that runs Postgres. Then, inside each database where you want to use it, you must run CREATE EXTENSION vector;. Installing the files does not switch anything on.

Pick the route that matches how you run Postgres. Whichever you choose, the destination is the same: a server where CREATE EXTENSION vector; works.

Route 1: Docker (the fastest way to learn). The pgvector project publishes an image that is the official postgres image with the extension already added. It runs exactly like postgres, with the same environment variables. If you have not used containers before, the Docker guide explains the ideas used here.

docker pull pgvector/pgvector:pg18-trixie
docker run -d --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 --shm-size=1g pgvector/pgvector:pg18-trixie

The tag pg18-trixie means Postgres 18 on the Debian "trixie" base. Tags follow the pattern pg13 to pg18, combined with trixie or bookworm, and you can pin the extension version too, as in 0.8.6-pg18-trixie. One detail worth knowing: there is no latest tag, so a bare pgvector/pgvector pull will fail. If you find an old tutorial using ankane/pgvector, that is the legacy name; use pgvector/pgvector. The --shm-size=1g flag raises the container's shared memory, which matters later when you build large indexes in parallel. Docker's default is only 64 MB.

Route 2: a package. On Linux the extension is available from the PostgreSQL project's package repositories, once you have enabled them:

# Debian / Ubuntu (PGDG apt repository enabled)
sudo apt install postgresql-18-pgvector

# Red Hat family (PGDG yum repository enabled)
sudo dnf install pgvector_18

# macOS with Homebrew
brew install pgvector

Replace 18 with the major version of your Postgres server. On macOS, Homebrew adds pgvector only for the postgresql@18 and postgresql@17 formulas. Postgres.app ships with pgvector preinstalled from Postgres 15 onward.

Route 3: from source. This works on Linux and macOS when no package fits. You need the Postgres server development headers (on Debian or Ubuntu, sudo apt install postgresql-server-dev-18).

cd /tmp
git clone --branch v0.8.6 https://github.com/pgvector/pgvector.git
cd pgvector
make
make install   # may need sudo

If you have several Postgres installations on one machine, point the build at the right one with export PG_CONFIG=/path/to/pg_config before running make. Building for the wrong Postgres is a classic cause of "it installed but the database cannot find it". Windows has its own build route with nmake, described in the official README, and Docker also works on Windows.

Route 4: a hosted database. Many managed Postgres services ship pgvector already, including AWS RDS and Aurora, Google Cloud SQL and AlloyDB, Azure Database for PostgreSQL, Supabase and Neon. On most you simply run CREATE EXTENSION vector;. The version you get depends on the provider and on your Postgres minor version, so always check it. If you work with employers in the Gulf or Egypt, this route is common, because it lets you keep the database in a regional cloud location that satisfies data-residency requirements. Details differ per provider, so follow the provider's own documentation. On Azure Database for PostgreSQL flexible server, for example, you must also allow-list VECTOR in the azure.extensions server parameter before the command works.

Now connect to the database with psql and turn the extension on. If you used the Docker command above:

docker exec -it pg psql -U postgres

CREATE EXTENSION IF NOT EXISTS vector;
SELECT extversion FROM pg_extension WHERE extname = 'vector';
SELECT '[1,2,3]'::vector <-> '[4,5,6]'::vector;

The first line enables the extension in the current database. The second reports which version is installed there; you should see 0.8.6 if you used the commands above. The third computes the straight-line distance between two three-dimensional points. The answer is the square root of 27, which Postgres prints as 5.196152422706632. If you get that number, pgvector works.

CREATE EXTENSION needs elevated privileges. The pgvector control file does not mark the extension as "trusted", so on a database you run yourself a superuser has to create it (the default postgres user in Docker is one). On managed services, an admin role does it for you, such as rds_superuser on AWS or azure_pg_admin on Azure. Remember that extensions are enabled per database. If you create a new database later, run the command again there.

In psql, the shortcut \dx vector also lists the installed extension. And if you ever need to see which version the server has available on disk versus installed, query pg_available_extensions:

SELECT default_version, installed_version
FROM pg_available_extensions
WHERE name = 'vector';

Your first project: a tiny similarity search

Let us build something small and complete, using three-dimensional vectors so you can verify every answer in your head. We will make a table of fruits where the three numbers loosely mean "how sweet", "how sour" and "how crunchy" on a scale of 0 to 10. A real project uses an embedding model; the mechanics are identical.

Create the table. A vector(3) column holds three numbers, and an ordinary text column holds the label.

CREATE TABLE fruits (
    id bigserial PRIMARY KEY,
    name text NOT NULL,
    traits vector(3)
);

Insert some rows. The vector is passed as a string literal in square brackets, and Postgres converts it to the vector type for you.

INSERT INTO fruits (name, traits) VALUES
    ('apple',      '[6,4,9]'),
    ('banana',     '[9,1,2]'),
    ('lemon',      '[1,10,3]'),
    ('orange',     '[8,5,3]'),
    ('green apple','[4,7,9]'),
    ('mango',      '[10,2,2]');

Now ask the question this whole extension exists for: which fruits are nearest to "sweet, a little sour, and soft", the point [9,3,2]?

SELECT name, traits <-> '[9,3,2]' AS distance
FROM fruits
ORDER BY traits <-> '[9,3,2]'
LIMIT 3;

You will get something close to the following. Mango is at distance about 1.41 (it differs by 1 in the first number and 1 in the second), banana is exactly 2, and orange is the third at about 2.45.

 name   |      distance
--------+--------------------
 mango  | 1.4142135623730951
 banana |                  2
 orange | 2.449489742783178

Read the query as a sentence. ORDER BY traits <-> '[9,3,2]' means "sort rows by their distance to this point, smallest first." LIMIT 3 means "keep only the nearest three." That pairing, ORDER BY a distance, then LIMIT, is the shape of every nearest-neighbour query you will write, and it is also the shape an index needs in order to help. We will rely on that later.

You can show the distance as a column, as we did, or leave it out. Showing it is useful for debugging because it tells you how much closer the first result is than the fifth. If the top results all have almost the same distance, the vectors are not discriminating well, which usually points at a problem in the embedding step rather than in SQL.

Notice there is no index yet. Postgres is doing an exact search: it computes the distance for all six rows and sorts them. That is the correct choice for a table this small, and you should stay with it until you have a reason not to.

Let us use a different question: find fruits similar to an existing row, say the apple, but excluding the apple itself.

SELECT name
FROM fruits
WHERE id != 1
ORDER BY traits <-> (SELECT traits FROM fruits WHERE id = 1)
LIMIT 2;

The subquery fetches the apple's vector, and the outer query ranks every other fruit by distance to it. This "more like this" pattern, a row used as the query, is how "related articles" features work.

Finally, see why the choice of distance matters. Try cosine distance on the same data:

SELECT name, traits <=> '[9,3,2]' AS cosine_distance
FROM fruits
ORDER BY traits <=> '[9,3,2]'
LIMIT 3;

Cosine distance ignores how long the vectors are and only compares their direction. Two fruits with traits [1,1,1] and [10,10,10] have different L2 distance but a cosine distance of zero, because both point the same way. For text embeddings, direction usually carries the meaning, which is why cosine is the common default.

Everyday commands: insert, update, delete, query

Having built the first project, here are the operations you will use daily, grouped by what you are trying to do. All of them are plain SQL; the vector-specific part is only how you write the vector.

Adding a vector column to a table that already exists. Real projects usually start with a table of documents and add embeddings later:

ALTER TABLE documents ADD COLUMN embedding vector(1536);

Pick the dimension to match your model exactly. Changing models later usually means a new column, because the old vectors came from a different "space" and cannot be compared with the new ones.

Inserting. You can insert one or many rows, as you did above. To insert a vector in a normal application, you pass it as a parameter and the client library formats it (we cover Python shortly). In pure SQL, a text literal with square brackets is enough.

Upserting. If a row may already exist, use ON CONFLICT:

INSERT INTO items (id, embedding) VALUES (1, '[1,2,3]'), (2, '[4,5,6]')
ON CONFLICT (id) DO UPDATE SET embedding = EXCLUDED.embedding;

Updating and deleting. These behave exactly as for any other column:

UPDATE items SET embedding = '[1,2,3]' WHERE id = 1;
DELETE FROM items WHERE id = 1;

Querying by similarity. The workhorse, as you have seen:

SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

Querying within a distance. Sometimes you want "everything closer than this threshold" rather than "the nearest five":

SELECT * FROM items WHERE embedding <-> '[3,1,2]' < 5;

This works, but remember the rule from the mental-model section: to make an index help, a query must have ORDER BY on the distance and a LIMIT. A bare distance filter with no ORDER BY and LIMIT will not use an approximate index, and will scan the whole table.

Combining similarity with ordinary conditions. Because this is SQL, you can add any filter you like:

SELECT id, title
FROM documents
WHERE published_year >= 2025 AND language = 'en'
ORDER BY embedding <=> '[3,1,2]'
LIMIT 5;

Read it as: filter first (conceptually), then rank what remains by closeness. With no vector index, this is simple and exact. With an approximate index there is a subtlety, because the index returns its candidates before your WHERE clause is applied. We deal with that in the section on making indexes behave.

Scores versus ordering. If you want a similarity score for display, compute 1 - (embedding <=> q) in the select list, but keep the ORDER BY as the bare operator. Ordering by 1 - (embedding <=> ...) DESC is mathematically the same ranking, but the planner cannot use an approximate index for it. Always order by the plain operator expression in ascending order.

Averages. pgvector supports AVG on vectors, which computes the element-wise mean. This is handy for "the centre of a group":

SELECT AVG(embedding) FROM items;
SELECT category_id, AVG(embedding) FROM items GROUP BY category_id;

You can use a category's average vector as a rough "topic vector" and compare new items against it. The sum aggregate also exists for vector and halfvec columns.

Handling NULLs. A row whose embedding is NULL is allowed in the table, and sorting by a distance on it behaves like sorting by any NULL. More importantly, NULL vectors are not indexed, so they never appear in results that come through an approximate index. If an embedding job fails halfway, you may end up with such rows; a quick SELECT count(*) FROM items WHERE embedding IS NULL; is a good health check.

Indexes: making search fast

So far every query scanned the whole table. That is fine for the fruit example and genuinely fine for tens of thousands of rows. When searches start taking noticeable time on a larger table, you add an approximate index. Here is how to decide, and how to do it without the usual mistakes.

Do you need an index at all? Check by timing a query with EXPLAIN ANALYZE. If the exact search is fast enough, an index would only add build time, memory use, and the risk of results changing. Many applications under about fifty thousand rows never need one. The right habit is to measure first.

Which index? For nearly everyone starting out, the answer is HNSW. It needs no training data, can be created on an empty table, and has the better recall at a given speed. Choose IVFFlat only when build time or memory is a hard constraint.

Which opclass? It must match the operator you query with:

So for cosine search, the whole job is one line:

CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

Postgres names the index for you, typically items_embedding_idx. The index will accelerate queries of the form ORDER BY embedding <=> '[...]' LIMIT n, and nothing else. A query using <-> will ignore it, and a query using <=> with no LIMIT will ignore it too.

You can set two build options. They have sensible defaults, and a beginner should usually leave them alone:

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

m is the maximum number of connections each point keeps in the graph (default 16). ef_construction is the size of the candidate list used while building (default 64), and it must be at least twice m. Larger values give better recall and cost more time and memory to build. Only raise them after you have measured poor recall.

IVFFlat, if you do need it, has a different recipe. It must be created after you have loaded representative data, because the cluster centres are fixed when the index is built:

CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

lists is the number of clusters (default 100). The documentation suggests starting around rows / 1000 for tables up to a million rows, and the square root of the row count beyond that. At query time, ivfflat.probes controls how many clusters are searched (default 1). A common starting point is the square root of lists. If you build an IVFFlat index on an empty or tiny table, Postgres warns you: NOTICE: ivfflat index created with little data, followed by a detail that this will cause low recall. Believe it: drop the index, load data, and build again.

Build time and memory. For HNSW, the build is much faster if the graph fits in maintenance_work_mem. If it does not, you see:

NOTICE:  hnsw graph no longer fits into maintenance_work_mem after 100000 tuples
DETAIL:  Building will take significantly more time.
HINT:  Increase maintenance_work_mem to speed up builds.

Raise it for your session before the build, within the memory your machine really has:

SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

In Docker, parallel builds use shared memory, so --shm-size must be at least as large as maintenance_work_mem; otherwise the build fails with a "could not resize shared memory segment" error. On a live production table, add CONCURRENTLY to the create statement (CREATE INDEX CONCURRENTLY ...) so that writes are not blocked during the build. Prefer to load data first and create the index afterwards, because bulk-loading into an already-indexed table is far slower.

Confirm the index is used. Put EXPLAIN in front of the query:

EXPLAIN SELECT * FROM items ORDER BY embedding <=> '[3,1,2]' LIMIT 5;

Look for a line such as Index Scan using items_embedding_idx on items. If you see Seq Scan instead, the planner chose to read the whole table. On a tiny table that is correct. On a large one, check the three usual suspects: a missing LIMIT, an operator that does not match the opclass, or an ORDER BY that is not the plain operator in ascending order. To test whether the index can be used at all, run SET enable_seqscan = off; in your session and EXPLAIN again.

Making the index behave: recall, ef_search, and filters

An approximate index has a dial that exchanges speed for accuracy, and a typical beginner problem that is easy to fix once you understand it. This section covers both.

The recall dial for HNSW is hnsw.ef_search. It sets the size of the candidate list the search keeps while walking the graph. The default is 40, and it can range from 1 to 1,000. A higher value looks at more candidates, so it finds more of the true neighbours, at the cost of slower queries. You set it like any Postgres setting:

SET hnsw.ef_search = 100;

That lasts for your session. To change it for a single query, use a transaction and SET LOCAL:

BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT id FROM items ORDER BY embedding <=> '[3,1,2]' LIMIT 5;
COMMIT;

The SET LOCAL form matters more than it looks. If your application connects through a connection pooler that shares connections between clients, a plain session SET may not stick to your next query, whereas SET LOCAL inside the transaction always applies to that transaction's queries. For IVFFlat, the equivalent dial is ivfflat.probes. These names are reserved prefixes, so a typo such as SET hnsw.efsearch = 100; fails with an error rather than silently doing nothing.

There is one further consequence of ef_search that surprises beginners: it also caps the number of rows an index scan can return. With the default of 40, an index scan returns at most about 40 candidates, so asking for LIMIT 100 cannot give you 100 rows. If you need larger result sets, raise ef_search to at least the number you want.

The filtering surprise. Consider this query on an indexed table:

SELECT id FROM items WHERE category_id = 123 ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

With an approximate index, the database first asks the index for its best candidates, then applies WHERE category_id = 123 to them. If only 10 per cent of rows are in category 123 and the index returned 40 candidates, then on average just four survive the filter, and you ask for five and get four, or fewer. No error appears. The index simply handed back too few candidates to start with.

You have three practical remedies, in the order a beginner should try them.

First, add an ordinary index on the filter column, for example CREATE INDEX ON items (category_id);. When the filter is selective, Postgres may then choose to fetch the few matching rows via the B-tree and compute exact distances over them, which is both fast and perfectly accurate.

Second, use iterative scans, added in pgvector 0.8.0. With this on, if the filter discards candidates, the index scan keeps going until it has found enough matching rows or reaches a limit:

SET hnsw.iterative_scan = relaxed_order;

relaxed_order gives the best recall, with the caveat that results can come back slightly out of order, so it fits queries where ordering by distance in the final list is not critical or can be sorted again afterwards. There is also strict_order for HNSW, which keeps exact ordering. IVFFlat has ivfflat.iterative_scan with only relaxed_order. The default for both is off. The cap on how far the scan goes is hnsw.max_scan_tuples (default 20,000).

Third, for a few well-known categories, a partial index (an index with a WHERE clause) gives each category its own small, fast index, and partitioning the table does the same at larger scale. Both belong to the mid-level guide.

Measuring recall. How do you know your index is giving good answers? Compare it to the exact answer on a sample query. Run the query normally and note the ids; then repeat it with index scans disabled so Postgres falls back to an exact scan, and compare:

BEGIN;
SET LOCAL enable_indexscan = off;
SELECT id FROM items ORDER BY embedding <=> '[3,1,2]' LIMIT 10;
COMMIT;

If nine of the ten ids match the indexed result, recall for that query is 0.9. Do this for a few dozen representative queries and you have a real number rather than a hunch. If it is too low, raise hnsw.ef_search first. Evaluating the quality of what you retrieve for an LLM application is its own topic, and tools such as RAGAS exist for that.

Silent failures to know about. Beyond filters, two other things hide rows from results without an error. Rows with a NULL embedding are not indexed. And for cosine, rows whose vector is all zeros are not indexed either, because the angle of a zero vector is undefined. If a particular row never shows up, check its embedding first.

Using pgvector from Python

SQL on its own is half the story. Your application creates the embeddings and sends them to the database. Python is the most common language for this, and pgvector has an official client library, pgvector-python, installed with pip install pgvector. At the time of writing it is at version 0.5.0 and requires Python 3.10 or newer.

Two warnings before the code, because this is where old tutorials go wrong. Version 0.5.0 made breaking changes. Importing now happens from the top-level package: from pgvector import Vector. The old pgvector.utils module was removed. And NumPy is no longer a dependency, so you can use plain Python lists. If you copy code from an older article and see ImportError, this is almost certainly why.

The example below uses Psycopg 3, the current Postgres driver for Python. Install both packages:

pip install "psycopg[binary]" pgvector

Then create a script. It connects, makes sure the extension exists, creates a table, inserts a few vectors, and searches.

import psycopg
from pgvector import Vector
from pgvector.psycopg import register_vector

conninfo = "postgresql://postgres:secret@localhost:5432/postgres"

# 1. Create the extension FIRST, on a plain connection.
with psycopg.connect(conninfo, autocommit=True) as setup:
    setup.execute("CREATE EXTENSION IF NOT EXISTS vector")

# 2. Now connect again and register the vector type.
with psycopg.connect(conninfo) as conn:
    register_vector(conn)

    conn.execute("DROP TABLE IF EXISTS fruits")
    conn.execute(
        "CREATE TABLE fruits (id bigserial PRIMARY KEY, name text, traits vector(3))"
    )

    rows = [
        ("apple", [6, 4, 9]),
        ("banana", [9, 1, 2]),
        ("lemon", [1, 10, 3]),
        ("mango", [10, 2, 2]),
    ]
    for name, traits in rows:
        conn.execute(
            "INSERT INTO fruits (name, traits) VALUES (%s, %s)",
            (name, Vector(traits)),
        )

    query = Vector([9, 3, 2])
    result = conn.execute(
        "SELECT name, traits <-> %s AS distance FROM fruits "
        "ORDER BY traits <-> %s LIMIT 3",
        (query, query),
    ).fetchall()

    for name, distance in result:
        print(f"{name:8s} {distance:.3f}")

Run it with python search.py. You should see mango first, then banana, matching the SQL you ran by hand.

Three details in that script are the ones beginners trip over. First, CREATE EXTENSION must run before register_vector, because registering looks up the database's type identifiers for vector, and those do not exist until the extension does; that is why the script uses two connections. Second, register_vector(conn) teaches the driver how to convert between Python and the vector type, and you call it on every new connection (with a pool, in the pool's configure hook). Third, since version 0.5.0, a vector column read through Psycopg comes back as a pgvector.Vector object; call .to_list() to get a plain Python list or .to_numpy() for a NumPy array if you have NumPy installed.

If you skip the registration, or if you pass a bare Python list where a vector is expected, you hit this error:

psycopg.errors.UndefinedFunction: operator does not exist: vector <-> double precision[]

It means the driver sent a Postgres array and the database does not know how to compare an array with a vector. The fix is to register the type with register_vector, or to add an explicit cast in the SQL, %s::vector.

The same library supports other frameworks. With SQLAlchemy 2, you declare a column with VECTOR(3) from pgvector.sqlalchemy, and query with comparator methods such as Item.embedding.l2_distance([3, 1, 2]). With Django, you add VectorExtension() to a migration and use VectorField(dimensions=3) with functions such as L2Distance. Psycopg 2, asyncpg, SQLModel, Peewee and pg8000 are also supported. Official clients exist for more than thirty languages, among them Node.js, Go, Java, Rust, Ruby and PHP, so what you learn here carries over.

Where do the embeddings themselves come from? From a model of your choice. A hosted API, such as the one in the OpenAI API guide, returns a list of floats you can wrap in Vector(...), and a local model from the Hugging Face ecosystem does the same. What matters for pgvector is only that the length of the list matches the column's declared dimension. Frameworks such as LangChain and LlamaIndex offer ready-made pgvector-backed stores, which is convenient once you understand what they generate underneath. Look at the SQL they run, and you will recognise everything in this guide.

Common errors and how to read them

Error messages from pgvector are short and fairly literal. Learning to read them is the quickest way to become independent. Here are the ones beginners meet, grouped by when they appear.

Errors when you set things up.

ERROR: type "vector" does not exist means the extension is not enabled in the database you are connected to (or it is in a schema that is not on your search_path). Run CREATE EXTENSION vector; in this database. If you see it only from your application, check that the application is connecting to the database you think it is.

ERROR: could not open extension control file ".../extension/vector.control": No such file or directory means the files were never installed for this server, often because they were installed against a different Postgres version. Install the package for the correct major version, or rebuild from source with PG_CONFIG pointing at the right server.

ERROR: permission denied to create extension "vector" with the hint Must be superuser to create this extension means your role is not allowed. On a self-managed server, use a superuser. On a managed service, use the provider's admin role, and check whether the provider requires allow-listing (Azure does).

Errors when you store or query vectors.

ERROR: expected 3 dimensions, not 4 means you inserted a vector whose length does not match the column. Check the model's output size, then fix either the data or the column.

ERROR: different vector dimensions 3 and 4 means you compared two vectors of different lengths, almost always because the query embedding came from a different model from the stored ones.

ERROR: invalid input syntax for type vector: "{1,2,3}" with the detail Vector contents must start with "[" means you used curly-brace array syntax. Use '[1,2,3]' or ARRAY[1,2,3]::vector.

ERROR: operator does not exist: vector <-> double precision[] means your client sent the query parameter as an array. Register the vector type in the client library or cast %s::vector.

Errors when you build an index.

ERROR: column cannot have more than 2000 dimensions for hnsw index (the IVFFlat message is the same with ivfflat) means your vector column is too wide to index. A 3,072-dimension embedding hits this. The options are half-precision indexing, binary quantization, a model that can output fewer dimensions, or no index; the mid-level guide covers the first two.

ERROR: ef_construction must be greater than or equal to 2 * m is exactly what it says: raise ef_construction.

NOTICE: ivfflat index created with little data, with the detail This will cause low recall and the hint Drop the index until the table has more data, tells you to drop the index and rebuild after loading data.

ERROR: memory required is X MB, maintenance_work_mem is Y MB is an IVFFlat build that needs more memory than it was given. Raise maintenance_work_mem or lower lists.

ERROR: could not resize shared memory segment ... No space left on device appears in Docker when a parallel HNSW build outgrows the container's small /dev/shm. Start the container with a larger --shm-size.

Problems that give no error at all. These are the hardest, because the database is not complaining.

• Fewer rows than your LIMIT. Usually a filter combined with an approximate index, or ef_search too low, or dead rows. Raise hnsw.ef_search, turn on iterative scans, or add a filter-column index.
• The index is never used. Check for a missing LIMIT, DESC ordering, an expression such as 1 - (a <=> b) in the ORDER BY, an operator that does not match the opclass, or a table small enough that a sequential scan is cheaper. Use EXPLAIN to see the plan.
• Different results after adding an index. Expected: that is approximate search.
• A specific row never appears. Its embedding may be NULL, or an all-zero vector under cosine.

Putting it all together

Time to assemble everything into one small end-to-end project: a semantic search over a handful of help articles, using SQL and Python together. To keep it runnable without an API key, the "embedding" function below is a deliberately crude stand-in, a hashing trick that turns words into a fixed-length vector. It is not a real model, and the quality of the results shows that. In your own project, replace that single function with a call to a real embedding model; nothing else changes.

First, the database. Create a fresh table with room for 64 numbers, and a cosine HNSW index:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE articles (
    id bigserial PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL,
    language text NOT NULL DEFAULT 'en',
    embedding vector(64)
);

CREATE INDEX ON articles USING hnsw (embedding vector_cosine_ops);

Because HNSW needs no training, creating the index before any data exists is fine. (If you loaded a large initial dataset in bulk, you would do the opposite and build the index after.)

Now the Python script that loads articles and searches them:

import hashlib
import math
import psycopg
from pgvector import Vector
from pgvector.psycopg import register_vector

DIMS = 64
conninfo = "postgresql://postgres:secret@localhost:5432/postgres"


def embed(text: str) -> Vector:
    """A crude stand-in for a real embedding model.

    Each word is hashed into one of DIMS buckets. Replace this function
    with a call to a real model; the rest of the script stays the same.
    """
    values = [0.0] * DIMS
    for word in text.lower().split():
        bucket = int(hashlib.sha256(word.encode()).hexdigest(), 16) % DIMS
        values[bucket] += 1.0
    norm = math.sqrt(sum(v * v for v in values)) or 1.0
    return Vector([v / norm for v in values])


ARTICLES = [
    ("Reset your password", "How to reset a forgotten password and log in again"),
    ("Change your email", "Update the email address linked to your account"),
    ("Invoices and billing", "Download invoices and update your payment method"),
    ("Delete your account", "Close your account and remove your data permanently"),
    ("Two factor login", "Set up two factor authentication for safer login"),
]

with psycopg.connect(conninfo) as conn:
    register_vector(conn)
    for title, body in ARTICLES:
        conn.execute(
            "INSERT INTO articles (title, body, embedding) VALUES (%s, %s, %s)",
            (title, body, embed(title + " " + body)),
        )
    conn.commit()

    question = "I forgot my password and cannot log in"
    q = embed(question)
    rows = conn.execute(
        """
        SELECT title, 1 - (embedding <=> %s) AS similarity
        FROM articles
        WHERE language = 'en'
        ORDER BY embedding <=> %s
        LIMIT 3
        """,
        (q, q),
    ).fetchall()

    for title, similarity in rows:
        print(f"{similarity:.2f}  {title}")

Run it and the top result should be "Reset your password", because it shares the words "password" and "log" with the question. Notice what the stand-in embedding can and cannot do: it only matches shared words, so "I lost my credentials" would not find the password article. A real embedding model understands that those mean the same thing, which is the whole reason embeddings exist.

Study the structure, because it is the template for real applications:

1. Ingestion turns each document into text, embeds it, and inserts the row with its vector. In production this runs as a batch job or in response to new content.
2. Search embeds the user's question with the same function, then runs ORDER BY embedding <=> query LIMIT n.
3. The filter language = 'en' shows the Postgres advantage: relational conditions and similarity in one statement.
4. The select list converts distance into a friendly similarity, while the ORDER BY stays as the plain operator so the index remains usable.

What you can now do, and what comes next

You started this guide with the idea that "similar" cannot be expressed in SQL. You now know that it can, once your data is embeddings stored in a vector column. You can install pgvector four different ways and check which version you have, create tables with vector columns and fill them, write nearest-neighbour queries with the right operator, and tell exact search from approximate search. You can add an HNSW index with the matching opclass, confirm with EXPLAIN that it is used, tune hnsw.ef_search, and handle filtered queries with iterative scans. You can call all of it from Python, and you can read the commonest errors without guessing.

Equally important, you know the traps: the extension is named vector, not pgvector; the index only helps queries whose operator matches the opclass and that end in ORDER BY ... LIMIT; <#> is negative; approximate indexes change results; vectors from different models must never be compared; and old tutorials may describe versions that no longer match.

Here is what the next two levels add, so you know what to look for:

• Mid-level goes under the hood of HNSW and IVFFlat so you can predict behaviour, and covers production patterns: half-precision storage and indexing for wide embeddings, partial indexes and partitioning for filters, hybrid search that combines keyword and vector ranking, build tuning, vacuum behaviour, and bulk loading.
• Senior treats pgvector as a platform: capacity planning and memory, replication and upgrades, multi-tenant isolation with row-level security, monitoring recall as a metric, incident playbooks, and, importantly, when pgvector stops being the right tool and a dedicated vector database such as Qdrant or a library like FAISS is.

Sources

• pgvector README (version 0.8.6): https://github.com/pgvector/pgvector/blob/v0.8.6/README.md
• pgvector repository and releases: https://github.com/pgvector/pgvector and https://github.com/pgvector/pgvector/tags
• pgvector changelog: https://github.com/pgvector/pgvector/blob/master/CHANGELOG.md
• Extension SQL definitions (types, operators, operator classes): https://github.com/pgvector/pgvector/blob/v0.8.6/sql/vector.sql
• Extension control file: https://github.com/pgvector/pgvector/blob/v0.8.6/vector.control
• Docker image: https://hub.docker.com/r/pgvector/pgvector
• Hosted providers that ship pgvector: https://github.com/pgvector/pgvector/issues/54
• pgvector-python client library: https://github.com/pgvector/pgvector-python
• pgvector-python changelog: https://github.com/pgvector/pgvector-python/blob/master/CHANGELOG.md
• PostgreSQL documentation on CREATE EXTENSION: https://www.postgresql.org/docs/current/sql-createextension.html
• PostgreSQL documentation on index build progress: https://www.postgresql.org/docs/current/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING

Part one of three. A twenty-minute review of the Beginner Detailed track, then the questions interviewers ask about pgvector. Mid-level reviews this plus its own material; Senior reviews all three. Everything below matches pgvector 0.8.6.

The thirty-second answer

pgvector is an open-source PostgreSQL extension that adds a vector column type, distance operators, and two approximate-nearest-neighbour indexes, HNSW and IVFFlat. It lets you store embeddings next to your normal relational data and find the most similar rows with plain SQL, in the same transaction and with the same backups as everything else.

Then the line that shows you have used it: "the extension is called vector, the index only helps queries that use the matching operator with ORDER BY ... LIMIT, and an approximate index can change which rows come back."

The model

One more fact you should be able to state without notes: pgvector does not create embeddings. A model outside the database does that, and pgvector stores, compares and indexes the numbers.

Commands to have ready

Be able to write these from memory, because interviewers often ask you to type them in a shared editor.

CREATE EXTENSION vector;
CREATE TABLE items (id bigserial PRIMARY KEY, content text, embedding vector(3));
INSERT INTO items (content, embedding) VALUES ('a', '[1,2,3]'), ('b', '[4,5,6]');
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
SELECT extversion FROM pg_extension WHERE extname = 'vector';

And the operator table:

The settings worth remembering are hnsw.ef_search (default 40, range 1 to 1,000), ivfflat.probes (default 1), and, for filtered queries, hnsw.iterative_scan (default off, from 0.8.0). For the build, m defaults to 16 and ef_construction to 64.

The classic traps and why they happen

The extension name. You run CREATE EXTENSION vector;, not pgvector. The project is pgvector; the extension, the type and the schema objects are vector. Interviewers like this because it separates people who have typed it from people who have read about it.

Extensions are per database. Installing the files makes the extension available. You still have to run CREATE EXTENSION vector; in every database that needs it, and you will see type "vector" does not exist if you forget.

The index is ignored. An approximate index is used only for ORDER BY <distance operator> ASC LIMIT n, and only when the operator matches the opclass. A cosine index does nothing for a <-> query. A query without LIMIT, one that orders DESC, or one that orders by 1 - (a <=> b) will not use it either. The reason is that the index can only walk outward from the query point in increasing distance order, so it must be asked for "the nearest n", in exactly that form.

Negative inner product. <#> returns the negative inner product because Postgres index scans only support ascending order on operators. If you want the true value, multiply by -1.

Fewer rows than LIMIT. With an HNSW index, the scan returns about hnsw.ef_search candidates, 40 by default, and your WHERE clause is applied afterwards. A filter that keeps 10 per cent of rows leaves roughly four of 40. The fixes are a higher ef_search, iterative scans (0.8.0 and later), a B-tree on the filter column, a partial index, or partitioning.

Index changes the answers. An approximate index trades accuracy for speed, so results can differ from exact search. Recall is measured by comparing with exact results, which you get by turning index scans off for one query.

IVFFlat on an empty table. The cluster centres are computed at build time. Building on little data gives poor clusters, and Postgres warns ivfflat index created with little data. Load representative data first, then build. HNSW has no such requirement.

Mixing models. Vectors from two different embedding models are not comparable, even if both have 1,536 dimensions. If dimensions differ you get different vector dimensions 3 and 4; if they match you get quietly meaningless results.

The 2,000-dimension limit. A vector column can be stored up to 16,000 dimensions but indexed only up to 2,000. A 3,072-dimension embedding fails with column cannot have more than 2000 dimensions for hnsw index. The usual remedy is a half-precision index, which allows 4,000.

Python registration. With psycopg you must create the extension before calling register_vector, and since pgvector-python 0.5.0 you import from the top-level package, from pgvector import Vector. The old pgvector.utils imports were removed.

Common interview questions

What is pgvector and why would you use it instead of a dedicated vector database?

A strong answer: it is a Postgres extension that stores embeddings in a normal column type and provides distance operators and approximate indexes. You choose it when the vectors belong with other data. You get transactions, joins, row-level security, replication and backups for free, and you run one system instead of two. A filtered query such as "five most similar documents this user may see" is one SQL statement. The honest trade-off is that a dedicated vector database may scale further or offer more tuning for very large collections, and heavy vector workloads can compete with your transactional workload on the same server.

What is an embedding?

A fixed-length list of numbers produced by a machine-learning model so that inputs with similar meaning land close together. A good answer adds that the model lives outside the database, that you must use the same model for stored data and queries, and that the column's declared dimension must equal the model's output size.

What is the difference between exact and approximate search?

Exact search has no index and scans every row, so it returns the true nearest neighbours but cost grows with the table. Approximate search uses an HNSW or IVFFlat index to examine only a fraction of the data, so it is much faster but may miss some true neighbours. Say the word recall, and mention that recall can be measured by comparing the indexed answer with an exact one.

Which distance operator should you use?

Use the metric the embedding model was trained for, which is usually cosine for text. Cosine distance is <=>, and similarity is 1 - (a <=> b). If the vectors are normalised to length 1, inner product <#> gives the same ranking and is the cheapest to compute. L2 <-> is for cases where magnitude matters. Add that the operator in the query and the opclass in the index must match.

HNSW or IVFFlat?

HNSW for most cases: better speed and recall trade-off, no training step, can be created on an empty table, and it keeps up as data grows. The costs are slower builds and higher memory use. IVFFlat builds faster and uses less memory, but its recall is lower at the same speed and it must be built after representative data is loaded, because its cluster centres are fixed at build time. IVFFlat does not support sparse vectors, and has only relaxed_order for iterative scans.

How does HNSW work, in two sentences?

It builds a multilayer graph in which each vector is connected to nearby vectors. A search starts at a top layer, hops greedily toward the query, and descends layer by layer, keeping a candidate list whose size is hnsw.ef_search. You do not need more than that at the beginner level, and saying so honestly is better than bluffing.

What do m, ef_construction and ef_search do?

m is how many connections each node keeps (default 16). ef_construction is the candidate-list size while building (default 64, and at least twice m). Both are fixed when you create the index. ef_search is the candidate-list size at query time (default 40), and you can change it per session or per transaction. Raising any of them improves recall at the cost of time, but the first thing to tune is ef_search.

My query returns fewer rows than the LIMIT. Why?

State the cause first: the index returns a limited candidate list and the filter is applied afterwards. Then list the remedies in order: check ef_search and raise it, add a B-tree on the filter column if the filter is selective, turn on hnsw.iterative_scan = relaxed_order on pgvector 0.8.0 or later, or use partial indexes or partitions when the filter has few distinct values. Also mention that rows with NULL vectors are never in index results.

Why is my index not being used?

Walk through the checklist: is there an ORDER BY on the distance operator with ASC and a LIMIT; does the operator match the opclass; is the ordering expression the bare operator rather than 1 - (a <=> b); is the table small enough that a sequential scan is cheaper; does a query on the base column face an expression index. Debug with EXPLAIN, and test whether the index can be used at all with SET enable_seqscan = off;.

How do you install it and verify the installation?

Use a package, the pgvector/pgvector Docker image, or build from the tagged source with make and make install. Then connect to the database and run CREATE EXTENSION vector;, which needs superuser or a managed-service admin role. Verify with SELECT extversion FROM pg_extension WHERE extname = 'vector'; and a test query such as SELECT '[1,2,3]'::vector <-> '[4,5,6]'::vector;.

How do you upgrade pgvector?

Install the new binaries the same way as the old ones, then run ALTER EXTENSION vector UPDATE; in each database, and verify with the version query. On a managed service the provider controls the version, so check pg_available_extension_versions. A good detail to add: use 0.8.3 or later, because 0.8.3 fixed possible index corruption during HNSW vacuuming and 0.8.2 fixed a parallel HNSW build overflow.

What are the dimension limits?

A vector can be stored up to 16,000 dimensions, but only indexed up to 2,000. halfvec stores up to 16,000 and indexes up to 4,000. bit can be indexed up to 64,000 dimensions. sparsevec can store 16,000 non-zero elements and index 1,000 of them with HNSW.

How do you speed up building an HNSW index?

Load the data first and index afterwards. Increase maintenance_work_mem so the graph fits in memory, then raise max_parallel_maintenance_workers. Use CREATE INDEX CONCURRENTLY on a live table. In Docker, make sure --shm-size is at least as big as maintenance_work_mem, otherwise parallel builds fail with a shared memory error.

How would you use pgvector from an application?

Create the extension first, then use the official client for your language, for example pgvector-python. In Psycopg 3 call register_vector(conn) on each connection, wrap lists with Vector([...]) and pass them as parameters, never concatenated into SQL. Keep connection strings and embedding-API keys in environment variables or a secret manager.

Is the data in a vector column secure?

pgvector adds no security of its own, so Postgres controls apply: roles, grants, row-level security, TLS and pg_hba.conf. Embeddings can leak information about the original text, so give them the same protection as the source data, including backups and replicas.

What happens to my index when I insert, update or delete rows?

An HNSW index is updated as rows change, so new vectors are searchable straight away and you do not need to rebuild after each insert. Inserts into an HNSW index cost more than inserts into a B-tree, so write-heavy tables pay a visible price. Deletes leave dead tuples until vacuum runs, and dead tuples reduce how many HNSW results come back. That is one more reason to run a recent version: 0.8.3 fixed possible index corruption during HNSW vacuuming. IVFFlat is also updated on insert, but its cluster centres were fixed at build time, so after the data changes a lot the clusters no longer fit and you should rebuild it.

What would you say if asked whether pgvector can replace a vector database?

Answer with a condition rather than a slogan. For a few million vectors that belong with relational data, and a team that already runs Postgres, it very often can. It stops being comfortable when vector traffic competes with your transactional workload, when the index no longer fits in memory, or when you need features the extension does not provide. A strong answer names the measurements you would take first: query latency, recall against exact search, index size against available RAM, and build time.

Why do normalised vectors matter?

When every vector has length 1, cosine distance, L2 distance and inner product all rank neighbours identically, so you can choose inner product, which is the cheapest to compute. If your model does not return normalised vectors, l2_normalize can scale them before you store them. Normalisation is also why a zero vector is a problem for cosine: it has no direction, and such rows are not indexed for cosine.

Sixty-second self-test

Cover the answers and say them aloud.

1. What is the name you pass to CREATE EXTENSION? vector.
2. Which operator is cosine distance, and which is negative inner product? <=> and <#>.
3. What shape must a query have for an approximate index to be used? ORDER BY <matching operator> ASC LIMIT n.
4. Which opclass serves a cosine query? vector_cosine_ops.
5. Why might an HNSW query with a WHERE filter return fewer rows than asked? The filter runs after the index returns about ef_search candidates; use iterative scans, a higher ef_search or other filtering strategies.
6. When must IVFFlat be built? After the table has representative data.
7. What is the highest dimension count you can index in a vector column? 2,000.
8. What does pgvector not do? Create embeddings.
9. How do you check the installed version? SELECT extversion FROM pg_extension WHERE extname = 'vector';.
10. Which Docker image and what trap? pgvector/pgvector, and there is no latest tag.

If you hesitated on more than two of these, reread the sections on indexes and on making the index behave in the Beginner Detailed track, then try again tomorrow. For related tools to compare with, see the Qdrant, Chroma and FAISS guides, which interviewers often ask you to contrast with pgvector.

Part one of three. Most beginner problems with pgvector come from four root causes: the index is not being used, the vectors are not comparable, an approximate index quietly returns fewer or different rows, or the setup is incomplete. Start with the table, then the habits, then the practice cards and the checklist. Everything is accurate for pgvector 0.8.6.

Common errors at this level

The rest of this guide takes the problems that waste the most hours and walks through each one as symptom, cause, fix.

Mistake 1: the index exists but queries stay slow

Symptom. You created an HNSW index, and a nearest-neighbour query on a large table still takes seconds. EXPLAIN shows Seq Scan.

Cause. The planner only uses an approximate index when the query is shaped exactly the way the index expects, and the usual culprit is a mismatch. The most common version is an index built with vector_cosine_ops and a query using <->. The second most common is a missing LIMIT. The third is ordering by a computed expression such as 1 - (embedding <=> q) DESC, which looks equivalent to a human but is not the operator expression the index serves.

Fix. Make the operator in the query match the opclass in the index, write ORDER BY embedding <=> '[...]' LIMIT n, and keep the ordering in ascending form. If you want a similarity score, compute it in the select list, not in the ORDER BY. Then re-run EXPLAIN and look for Index Scan using ..._embedding_idx. If you still see a sequential scan on a big table, SET enable_seqscan = off; for your session and run EXPLAIN again. If the index is used now, the planner thought a sequential scan was cheaper, and the mismatch is elsewhere; if it still is not used, the query shape or opclass is still wrong.

Mistake 2: results are missing after you add a filter

Symptom. WHERE category_id = 7 ORDER BY embedding <=> q LIMIT 10 returns three rows although hundreds of matching rows exist. There is no error.

Cause. An HNSW index returns about hnsw.ef_search candidates, 40 by default, and the WHERE clause is applied to those candidates afterwards. If only a small share of rows match the filter, little survives.

Fix. Try these in order. First, SET hnsw.iterative_scan = relaxed_order;, available from pgvector 0.8.0, which lets the scan continue until it has enough matching rows. Second, raise hnsw.ef_search. Third, if the filter is very selective, put an ordinary B-tree index on the filter column, which can let Postgres fetch the few matching rows and compute exact distances. Fourth, for a handful of categories use partial indexes, and for many categories consider partitioning; those are covered at the mid level. Remember that relaxed_order can return results slightly out of order.

Mistake 3: comparing vectors from different models

Symptom. Search results look random, or you see different vector dimensions 1536 and 384.

Cause. You embedded the documents with one model and the query with another, or you changed models halfway through a project. When the dimensions differ you get an error. When they happen to match, there is no error and the answers are quietly meaningless, because each model arranges meaning differently.

Fix. Use the same model, with the same settings, for stored vectors and queries. Record the model name in a column, such as embedding_model text, so you can tell what produced a row. When you change models, create a new column or table and re-embed everything, rather than overwriting some of the rows.

Mistake 4: building an IVFFlat index too early

Symptom. Right after creating the index you see NOTICE: ivfflat index created with little data, and queries return poor results.

Cause. IVFFlat runs k-means at build time to choose cluster centres, and those centres stay fixed afterwards. A table that was empty or tiny at build time produces bad clusters.

Fix. DROP INDEX it, load representative data, then build again. If you do not have a reason to prefer IVFFlat, use HNSW, which has no training step and is fine on an empty table. Also note that IVFFlat does not adapt when your data later drifts, so rebuild it after large changes.

Mistake 5: forgetting register_vector, or running it too early

Symptom. Python raises operator does not exist: vector <-> double precision[], or register_vector itself fails saying the type cannot be found.

Cause. The client library needs to know about the vector type. Registration looks up the type's identifier in the database, so the extension has to exist first. If you pass bare Python lists without registration, the driver sends them as arrays.

Fix. Run CREATE EXTENSION IF NOT EXISTS vector on a connection before calling register_vector(conn) on the connection you use for queries, and wrap values with Vector([...]). With connection pools, register in the pool's configure hook so every connection gets it. As a fallback, add a cast in the SQL: %s::vector.

Mistake 6: copying code from an old pgvector-python tutorial

Symptom. ImportError for pgvector.utils, or np.ndarray expected but a list or Vector returned.

Cause. pgvector-python 0.5.0 removed the pgvector.utils package and the re-exported classes. NumPy is no longer a dependency. Columns of vector type now come back as Python lists in Django, SQLAlchemy, SQLModel and Peewee, and as a pgvector.Vector object in Psycopg, asyncpg and pg8000.

Fix. Import from the top-level package, from pgvector import Vector, HalfVector, Bit, SparseVector. Call .to_list() or .to_numpy() on a returned Vector. In SQLAlchemy the type is spelled VECTOR, and Vector remains an alias. The client needs Python 3.10 or newer and SQLAlchemy 2 or newer.

Mistake 7: using the wrong Docker image or tag

Symptom. docker pull pgvector/pgvector says the manifest is unknown, or a tutorial's ankane/pgvector image is old and stuck on an obsolete version.

Cause. The image moved from ankane/pgvector to pgvector/pgvector in version 0.6.0, and the new image has per-Postgres-version tags and no latest tag.

Fix. Use an explicit tag like pgvector/pgvector:pg18-trixie, or pin the full version with 0.8.6-pg18-trixie. The short tags pg18 and 0.8.6-pg18 point to the bookworm base. Set --shm-size large enough if you raise maintenance_work_mem.

Mistake 8: a 3,072-dimension embedding that will not index

Symptom. ERROR: column cannot have more than 2000 dimensions for hnsw index.

Cause. The vector type can be stored up to 16,000 dimensions but indexed only to 2,000. Large embedding models can exceed that.

Fix. Store at full size and index a half-precision cast, which allows up to 4,000 dimensions:

CREATE INDEX ON items USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);
SELECT id FROM items ORDER BY embedding::halfvec(3072) <=> '[...]' LIMIT 5;

The query must repeat the same cast expression, otherwise the index is not used. Other options are a model that lets you request fewer dimensions, or no index at all if the table is small. You will meet more of these techniques at the mid level.

Mistake 9: the extension is installed, but not for this server

Symptom. You ran make install, yet Postgres says it cannot find vector.control.

Cause. You have several Postgres installations, and the build targeted the wrong one. Package managers behave the same way when you install postgresql-17-pgvector but run Postgres 18.

Fix. Check which pg_config the build used, and set export PG_CONFIG=/path/to/pg_config before make clean && make, then install with sudo --preserve-env=PG_CONFIG make install. For packages, match the major version in the package name to the server.

Mistake 10: reading <#> as a positive similarity

Symptom. Inner-product results are all negative, and you conclude the model is broken.

Cause. <#> returns the negative inner product, so that ascending order puts the most similar rows first.

Fix. Multiply by -1 to display the real inner product: (embedding <#> q) * -1. Order by the raw operator, not the multiplied value.

Habits worth adopting on day one

Learn with small vectors you can verify. Before you use a real embedding model, build a table with vector(3) and hand-made points, as in the Beginner Detailed track. When the operators feel natural, nothing about them changes for 1,536 dimensions. This habit makes later debugging far faster, because you can always reduce a confusing problem to a toy you can predict.

Start with exact search, then measure. An index is an optimisation. Many tables under fifty thousand rows are fast enough without one, and an exact search gives you perfect recall and a baseline. Add an index only when EXPLAIN ANALYZE shows you need it, and keep the exact query available so you can compare.

Match the operator to the opclass, always. Decide on a metric once, write it in a short note for the team, and use the same operator in every query and the same opclass in the index. If in doubt, cosine with vector_cosine_ops is a safe default for text.

Order by the bare operator, ascending, with a limit. Compute friendly scores in the select list. This one habit prevents most "my index is ignored" tickets.

Create the extension in a migration, not by hand. Put CREATE EXTENSION IF NOT EXISTS vector; at the top of your first migration. Then a fresh database, a test database and a staging database all behave the same way, and you cannot forget it. In Django, the VectorExtension() operation does this.

Pin versions. Pin the Docker tag, such as pgvector/pgvector:0.8.6-pg18-trixie, and the Python library version in your requirements file. For anything real, use pgvector 0.8.3 or later, since 0.8.3 fixed possible index corruption during HNSW vacuuming and 0.8.2 fixed a buffer overflow in parallel HNSW builds. Check the installed version with SELECT extversion FROM pg_extension WHERE extname = 'vector';.

Keep a "model" column. Store which embedding model and dimension produced each vector. It costs a few bytes and saves you from the worst class of silent bug, which is comparing vectors that were never meant to be compared.

Load first, index after. Bulk-loading into an indexed table is much slower than loading and then building. For HNSW, give the build maintenance_work_mem and parallel workers. On a live table, use CREATE INDEX CONCURRENTLY.

Use SET LOCAL for per-query tuning. When you want a higher hnsw.ef_search for one query, open a transaction and use SET LOCAL hnsw.ef_search = 100;. It applies to that transaction only, and it works correctly through connection poolers that would not carry a plain session setting across requests.

Measure recall on real queries. Run a few dozen representative queries with the index, and again with SET LOCAL enable_indexscan = off; inside a transaction, and compare the ids. A number such as 0.97 beats a feeling. Re-measure after any change to the index options or the data. If you later evaluate the quality of retrieval inside an LLM application, tools such as the RAGAS guide describes are the next layer.

Pass vectors as parameters. Never build SQL by pasting numbers into a string. Use the client library's parameter binding, and read the database password and embedding-API key from environment variables or a secret manager.

Treat embeddings as sensitive. Embeddings can leak information about the text behind them. Give them the same access controls and backup protection as the source data. Region matters too: if your employer must keep data in the Gulf or in Egypt, check that the managed database and the embedding API both run in permitted regions, because sending text to a model endpoint abroad is also a data transfer.

Read the plan, not your intuition. EXPLAIN and EXPLAIN (ANALYZE, BUFFERS) tell you whether the index was used and how long each step took. When a query surprises you, ask the planner before changing settings.

Practice cards

A short checklist

Run through this before you call a pgvector setup finished.

• The extension is created in every database that needs it, preferably in a migration.
• SELECT extversion ... shows 0.8.3 or later, and the Docker tag or package version is pinned.
• Every vector column has a declared dimension that equals the model's output size.
• Stored vectors and query vectors come from the same model, which is recorded in a column.
• One distance metric is chosen, and the index opclass matches the operator used in queries.
• Queries use ORDER BY <bare operator> ASC LIMIT n, and EXPLAIN confirms the index is used on large tables.
• Indexes were built after the bulk load, with enough maintenance_work_mem, and CONCURRENTLY on live tables.
• Filtered queries return the expected number of rows, thanks to iterative scans or a suitable index.
• Recall was measured against exact search on realistic queries.
• No passwords or API keys are in code, and embeddings are protected like the source data.

If every box is ticked, you are past the beginner stage. Mid-level builds on this with production patterns, and when your search starts to need more than one database can offer, look at how the Qdrant and Pinecone guides approach the same problem.

This is part two of three. It picks up where Beginner ended and takes every topic from there further, then adds the machinery you have not met yet. It is written against pgvector 0.8.6 and assumes you already have a table with an embedding column and a working similarity query somewhere at work.

Where this picks up

Each section starts with the problem it solves and ends with a Try it you can run against a copy of your own data in a few minutes. Never run the experiments against a production primary during peak load. An index build is a heavy operation, and several of the exercises build one.

What a vector costs, and the limits that follow

Every decision later in this guide starts from the size of a row, so we start there. The problem it solves is the surprise most teams get around their first million rows: the table is fine, the index is fine, and then the memory graph does not look the way anyone expected.

A vector stores single-precision floats, four bytes per element, plus an eight-byte header. So a vector costs 4 * dims + 8 bytes. A halfvec stores two-byte floats and costs 2 * dims + 8. A bit vector costs dims / 8 + 8. A sparsevec costs 8 * nnz + 16, where nnz is the number of non-zero elements. Those four formulas let you size a workload on the back of an envelope. A 1,536-dimension vector is 6,152 bytes. Ten million of them is roughly 61 GB of heap before you build any index.

A value that size does not fit comfortably in an 8 KB Postgres page, so Postgres moves it out of line into a TOAST table. Since 0.6.0 the vector type uses external storage, which means TOASTed but not compressed, because float data compresses poorly. You rarely need to think about this, with one exception covered in the performance section: the planner does not cost out-of-line storage well when deciding whether to run a sequential scan in parallel.

Two sets of limits matter, and confusing them is a classic source of errors.

If you try to index a 3,072-dimension column, such as the output of a large embedding model, you get ERROR: column cannot have more than 2000 dimensions for hnsw index. The error is not a bug and no setting raises the ceiling. It is a signal to choose one of the techniques in the quantization section: index at half precision, quantize to bits, index a prefix, or ask the model for fewer dimensions.

Elements must be finite. NaN and infinity are rejected on input with NaN not allowed in vector or infinite value not allowed in vector. The usual cause is an embedding pipeline that normalises a zero vector by dividing by its length. Catch it in the pipeline, not in a failed batch insert at 3 a.m.

A column declared as plain vector, with no dimension, accepts any length. That flexibility has a price: you cannot index it directly, and you get ERROR: column does not have dimensions. Mixed-dimension columns are possible, but only with expression indexes, which we return to in the quantization section.

Exact search, and when the planner picks the index

Before you tune anything, you need to know what you are tuning against. Without an approximate index, Postgres scans every row, computes the distance and sorts. This is exact nearest-neighbour search. It has perfect recall, it can run in parallel, and for tables of modest size it is often the right answer. An approximate index is an optimisation you add when exact search is too slow, not a default.

The planner uses a pgvector index under one condition: the query has ORDER BY <distance operator expression> ASC LIMIT n. Everything that breaks that shape quietly sends you back to a sequential scan.

-- Uses the index (given a matching opclass)
SELECT id FROM items ORDER BY embedding <=> '[3,1,2]' LIMIT 5;

-- Does NOT use the index: the ordering is a computed expression, and descending
SELECT id FROM items ORDER BY 1 - (embedding <=> '[3,1,2]') DESC LIMIT 5;

The second query is the mistake teams make most often, because it reads naturally: "order by similarity, highest first". The index can only serve ascending order on the raw distance operator, so you compute similarity in the select list and order by the distance:

SELECT id, 1 - (embedding <=> '[3,1,2]') AS similarity
FROM items
ORDER BY embedding <=> '[3,1,2]'
LIMIT 5;

A WHERE embedding <-> '[3,1,2]' < 5 clause on its own does not use the index either. To get an index scan you need the ORDER BY and LIMIT as well, and a distance threshold is then best applied outside the ordered query, which the iterative-scan section shows.

The list of reasons an index is not used is short enough to memorise:

1. There is no LIMIT.
2. The ordering is DESC, or a computed expression instead of the bare operator.
3. The operator does not match the index's opclass, for example a <-> query against an index built with vector_cosine_ops.
4. The table is small enough that a sequential scan is cheaper.
5. You query the base column but only an expression index exists, or the reverse.

To find out which, use EXPLAIN, and to force the question use a session setting:

BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM items ORDER BY embedding <=> '[3,1,2]' LIMIT 5;
ROLLBACK;

If the plan shows Index Scan using items_embedding_idx, the index is usable and the planner simply judged a sequential scan cheaper. If it still shows a sequential scan with enable_seqscan off, the query shape or opclass is wrong. This distinction saves hours.

Choosing operators and operator classes

An operator class, or opclass, binds an index to one distance. An index accelerates only the operator that matches its opclass. If you query with two distances, you need two indexes, and each costs memory and write time. Most teams should query with one.

The operators are <-> for L2 distance, <#> for negative inner product, <=> for cosine distance, <+> for L1 distance, <~> for Hamming distance and <%> for Jaccard distance. The last two work on bit vectors. The inner-product operator returns the negative value because Postgres index scans only support ascending order on operators. To recover the real inner product, multiply by minus one.

Read the table for what is absent. IVFFlat has no L1 opclass, no Jaccard opclass and no sparse support. If your design needs sparse vectors, you are committed to HNSW.

Which distance should you pick? Use the one your embedding model was trained for, which the model card states. Cosine is the common default for text embeddings. If your embeddings are normalised to length one, as OpenAI's are, inner product produces the same ranking as cosine and is the cheapest to compute, so vector_ip_ops with <#> is a reasonable choice there. If you are not sure whether your vectors are normalised, compute vector_norm(embedding) on a sample before relying on that equivalence.

A subtle point follows from the opclass binding. Indexes are not used for rows whose distance is undefined. NULL vectors are never indexed, and zero vectors are not indexed under cosine, because the cosine of a zero vector is undefined. Those rows will never appear in approximate results, and no error tells you so. If you store placeholders for items not yet embedded, store NULL and expect them to be invisible to ANN queries, or filter them out of your indexing job on purpose.

HNSW from the inside

HNSW stands for Hierarchical Navigable Small World. It is a multilayer proximity graph. Each vector is a node, connected to a limited number of near neighbours, and the upper layers are sparser, so a search can take long hops at the top and short precise steps at the bottom. It gives a better speed and recall trade-off than IVFFlat, at the cost of slower builds and more memory. It has no training step, so you can create it on an empty table and it stays good as the data grows.

There are three knobs and they act at different moments.

m and ef_construction are index options, fixed in the WITH clause, and changing them means rebuilding the index. A higher ef_construction gives a better graph and better recall at the same query settings, at the cost of build and insert time. Setting ef_construction below twice m is rejected with ERROR: ef_construction must be greater than or equal to 2 * m. The defaults of 16 and 64 are a sound starting point. Change them only when you have measured low recall that raising ef_search cannot fix, because both change build time substantially.

hnsw.ef_search is the query-time dial, and it is the one you will actually turn. It sets the size of the dynamic candidate list the search maintains, so a bigger value explores more of the graph, finds better neighbours and takes longer. It has a second, less obvious role: it caps the number of rows an index scan can return. With the default of 40, an index scan returns at most 40 candidates, so LIMIT 100 cannot give you 100 rows. Beginners see "fewer rows than I asked for" and assume corruption. It is arithmetic.

-- Per session
SET hnsw.ef_search = 100;

-- Per query, safe with connection poolers
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT id FROM items ORDER BY embedding <=> '[3,1,2]' LIMIT 20;
COMMIT;

The hnsw and ivfflat setting prefixes are reserved, so a typo such as SET hnsw.efsearch = 100 is an error, not a silently created placeholder. All of these settings can be applied per session, per transaction with SET LOCAL, per role or database with ALTER ROLE ... SET or ALTER DATABASE ... SET, or in postgresql.conf. Values are validated, so hnsw.ef_search = 5000 fails with an out-of-range message.

Because HNSW is an approximate index, results differ after you add it. That is expected, not a defect. Adding an index to an exact query changes which rows you get back, which is unlike every other index type you have used. This is why recall measurement, covered later, belongs in your toolbox from the day you add the index.

IVFFlat from the inside

IVFFlat is the older of the two index types. It runs k-means clustering over your data to split the vectors into lists clusters, remembers the centre of each, and at query time searches only the ivfflat.probes clusters whose centres are closest to the query. It builds faster and uses less memory than HNSW but has a worse speed and recall trade-off.

The property that defines how you operate it is that the cluster centres are fixed at build time. The index is trained on whatever data exists when you run CREATE INDEX. If you build it on an empty or tiny table, the centres are meaningless and recall is poor, and pgvector tells you so:

NOTICE:  ivfflat index created with little data
DETAIL:  This will cause low recall.
HINT:  Drop the index until the table has more data.

The cure is the order of operations: load a representative amount of data first, then build the index. Later inserts are assigned to the existing nearest cluster, so if your data distribution drifts, the centres go stale and recall quietly degrades. A periodic REINDEX after a large change in the data re-runs the clustering.

CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
SET ivfflat.probes = 10;

lists defaults to 100 and accepts 1 to 32,768. A starting point is rows divided by 1,000 for tables up to about a million rows, and the square root of the row count above that. ivfflat.probes defaults to 1, which is rarely what you want: a single probe searches only the closest cluster and misses neighbours that sit just across a boundary. A starting point for probes is the square root of lists. More lists means faster queries but lower recall at a given probe count, and probes is the dial you turn per query. Setting probes equal to lists makes the search exact, at which point the planner will not bother with the index.

Building IVFFlat needs memory proportional to lists times dimensions for the k-means step. When that exceeds maintenance_work_mem you get ERROR: memory required is X MB, maintenance_work_mem is Y MB. Raise the setting or lower lists.

Filters: why WHERE shrinks your results

This is the section that explains the most production incidents. With an approximate index, the planner asks the index for its nearest candidates and applies the WHERE clause afterwards. This is post-filtering. If the filter removes most candidates, you get fewer rows than you asked for.

Work through the arithmetic. Your query filters to a category that matches 10% of the table. The index returns hnsw.ef_search candidates, 40 by default. Ten percent of 40 is four. Your LIMIT 10 returns about four rows, with no error and no warning. The more selective the filter, the worse it gets. With 0.8.0 and later you have a proper cure, iterative scans, but you also have four older strategies, and the README's advice is to consider them in this order.

1. A B-tree on the filter column. CREATE INDEX ON items (category_id); lets Postgres find the matching rows through the B-tree and compute exact distances over that subset. When the filter is selective, this is both exact and fast, and it is the first thing to try.
2. An ANN index with iterative scans. When the filter matches a large share of rows, the cost of an exact scan over the subset is too high and the index does the heavy lifting. Iterative scans make the index keep searching until the LIMIT is satisfied.
3. A partial index. When there are few distinct values of the filter, build an ANN index for just that slice: CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WHERE (category_id = 123);. The query must include the same predicate for the planner to use it.
4. Partitioning. When there are many values, PARTITION BY LIST (category_id) gives each value its own table, and each partition gets its own index. A query that filters on the partition key touches only one partition.

The strategies are not mutually exclusive. A multi-tenant system might partition by tenant for the large customers and rely on a B-tree plus exact search for the long tail of tiny ones.

CREATE TABLE items (
  id bigserial,
  customer_id int NOT NULL,
  embedding vector(1536),
  PRIMARY KEY (customer_id, id)
) PARTITION BY LIST (customer_id);

CREATE TABLE items_c42 PARTITION OF items FOR VALUES IN (42);
CREATE INDEX ON items_c42 USING hnsw (embedding vector_cosine_ops);

Notice that the primary key includes the partition key, which Postgres requires on partitioned tables. Notice also that indexes on the parent do not substitute for a design decision: decide which tenants deserve their own ANN index.

Iterative scans in practice

Before pgvector 0.8.0, a filtered ANN query that came up short had one remedy: raise ef_search or probes until enough candidates survived, and hope. Version 0.8.0 added iterative index scans. When a scan finishes and the filter has left fewer rows than the LIMIT, the index continues, fetching more candidates until the limit is met or a cap is hit. You turn it on per session:

SET hnsw.iterative_scan = relaxed_order;
SELECT id FROM items WHERE category_id = 123 ORDER BY embedding <-> '[3,1,2]' LIMIT 10;

The settings are small in number but easy to confuse:

The difference between the two HNSW modes is about ordering. strict_order guarantees results come back in exact distance order. relaxed_order gives better recall but results can be slightly out of order. For most search and retrieval features the order among near-ties is irrelevant, so relaxed_order is the practical default, and the README's own guidance leans that way. If you need strict order from a relaxed scan, restore it with a materialized common table expression:

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 + 0 is required on Postgres 17 and later so that the outer sort is not optimised away. For a distance threshold, such as "only results closer than 5", put the threshold outside a materialized CTE and keep the other filters inside, so the threshold does not defeat the index:

WITH nearest_results AS MATERIALIZED (
    SELECT id, embedding <-> '[1,2,3]' AS distance
    FROM items ORDER BY distance LIMIT 5
)
SELECT * FROM nearest_results WHERE distance < 5 ORDER BY distance;

Two cautions. Iterative scans are bounded: if a filter is so selective that fewer than LIMIT rows exist within hnsw.max_scan_tuples tuples, you still get a short result. For a filter matching a tiny fraction of the table, the B-tree strategy or a partial index remains the better tool. And an iterative scan does more work, so a query that previously stopped early may now run longer. Measure latency after turning it on, not only recall.

Turn it on where it is needed. A role-level or database-level default through ALTER ROLE ... SET hnsw.iterative_scan = 'relaxed_order' is cleaner than every query remembering a SET, particularly with a pooler in transaction mode, where a session-level setting does not reliably follow your queries.

Building indexes in production

A build on a large table is the most disruptive routine operation you will run. The problem it solves is doing it without blocking writes, without running out of memory, and without taking hours when it could take minutes.

Order of operations first. Load the data, then build the index. Building before a bulk load means every insert pays the index maintenance cost, which is far more expensive than building once at the end. For IVFFlat, loading first is mandatory for quality. For HNSW it is a performance choice, but a large one.

CREATE INDEX CONCURRENTLY items_embedding_hnsw
  ON items USING hnsw (embedding vector_cosine_ops);

CONCURRENTLY takes longer but does not lock out inserts and updates. It cannot run inside a transaction block, and if it fails it leaves an invalid index behind that you must drop, so check \d items for an INVALID marker after a failed attempt.

Then make it fast. HNSW builds are much quicker when the graph fits in maintenance_work_mem, and they parallelise:

SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 7;
SET max_parallel_workers = 8;

max_parallel_maintenance_workers defaults to 2 and counts workers in addition to the leader. It is capped by max_parallel_workers, which defaults to 8, so raise both when you want more than a few workers. Do not set maintenance_work_mem higher than your server can actually spare, because several maintenance operations, autovacuum workers among them, can each claim that much. When the graph no longer fits, Postgres tells you:

NOTICE:  hnsw graph no longer fits into maintenance_work_mem after 100000 tuples
DETAIL:  Building will take significantly more time.
HINT:  Increase maintenance_work_mem to speed up builds.

When you see that notice, the build has dropped into a much slower mode, and the fix is memory, not patience.

You can watch a build through pg_stat_progress_create_index:

SELECT phase,
       round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;

For HNSW the phases are initializing and loading tuples, and progress is measured in blocks. For IVFFlat the phases are initializing, performing k-means, assigning tuples and loading tuples, and progress is measured in tuples, but a percentage appears only in the last phase. A build that sits in "performing k-means" with no percentage is not stuck.

One more production point: upgrade to 0.8.2 or later before running parallel HNSW builds, because 0.8.2 fixed a buffer overflow in parallel builds. The version section lists every fix that matters.

Shrinking the working set: halfvec, bits and prefixes

Memory is the bill that arrives with success. The graph of an HNSW index wants to live in RAM, and the vectors it points at make it larger. The tools for shrinking this are all variations on one idea: index a smaller representation, then use the full-precision vector to correct the result.

Half precision. The cheapest change with the least risk is storing or indexing at half precision. A halfvec column costs half the bytes of a vector column and indexes up to 4,000 dimensions instead of 2,000. If you cannot change the table, index an expression and keep the original column:

CREATE INDEX ON items USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops);

SELECT id FROM items
ORDER BY embedding::halfvec(1536) <=> '[3,1,2]'::halfvec(1536)
LIMIT 5;

The query must repeat the exact expression, cast included, or the planner will not match it to the index. This is the same expression-index rule that governs every technique in this section, and forgetting it is the usual cause of "I built the index and it is not used". For a 3,072-dimension model, this is the direct fix for the 2,000-dimension indexing error.

Binary quantization. The binary_quantize function sets each element to 1 if it is positive and 0 otherwise, producing a bit string you index with Hamming distance. The index is up to 32 times smaller than a float one, and the distance is very fast. The cost is accuracy, which you recover by re-ranking: fetch a generous candidate list from the bit index, then sort that list by the true distance.

CREATE INDEX ON items
  USING hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops);

SELECT * FROM (
  SELECT * FROM items
  ORDER BY binary_quantize(embedding)::bit(1536) <~> binary_quantize('[1,-2,3]')
  LIMIT 100
) candidates
ORDER BY embedding <=> '[1,-2,3]'
LIMIT 10;

The inner LIMIT is a recall dial. Fetch more candidates for better recall and pay in re-rank time. Bit indexes accept up to 64,000 dimensions. Test on your own embeddings, because how well binary quantization preserves ranking depends on the model.

Subvector indexing. Some embedding models are trained so that a prefix of the vector is itself a usable, lower-resolution embedding. For those models you can index a slice with subvector(embedding, 1, 256) and re-rank with the whole vector, using the same two-stage pattern. This only makes sense for models designed for it. Check the model's documentation before trying it.

Mixed dimensions. If one table holds embeddings from several models, use a column of type plain vector with expression and partial indexes, one per model:

CREATE TABLE embeddings (
  model_id bigint, item_id bigint, embedding vector,
  PRIMARY KEY (model_id, item_id)
);
CREATE INDEX ON embeddings USING hnsw ((embedding::vector(3)) vector_l2_ops)
  WHERE (model_id = 123);
SELECT * FROM embeddings WHERE model_id = 123
ORDER BY embedding::vector(3) <-> '[3,1,2]' LIMIT 5;

Compare these options on what they cost you:

Vacuum, updates and keeping an index healthy

An index that is correct on day one can degrade by day ninety. This section is about how that happens and what to watch.

HNSW vacuuming is slower than vacuuming a B-tree because removing a node means repairing the graph around it. The README recommends that when vacuum is slow on a large table, you reindex first and vacuum afterwards:

REINDEX INDEX CONCURRENTLY items_embedding_hnsw;
VACUUM items;

Dead tuples have a second cost: they take up room in the candidate list. Until a deleted or updated row is vacuumed, it can occupy one of the ef_search slots and then be discarded by visibility checks, so a table with heavy churn returns fewer rows than a clean one. If you notice recall or result counts falling on a table with many updates, check autovacuum health before blaming the index.

Versions matter here more than anywhere else. Version 0.8.3 fixed possible index corruption with HNSW vacuuming, and 0.8.4 fixed an hnsw graph not repaired error during HNSW vacuuming and a possible error with inserts during vacuum. If you run anything older than 0.8.3 on a table that sees deletes or updates, upgrading is a correctness fix, not a nicety.

For IVFFlat, the maintenance concern is staleness. The centres were computed once. If the table doubles and the data's character shifts, for example you add a new language or product line, recall drops without any error. Schedule a REINDEX after major data changes, or track recall and trigger one when it falls.

Bulk loads deserve their own mention. Inserting row by row through an ORM is slow. The binary COPY protocol is the fast path:

COPY items (embedding) FROM STDIN WITH (FORMAT BINARY);

With psycopg 3, use the copy.write_row method with the registered vector type. The pgvector-python repository includes a loading example under examples/loading. Load, then build the index, then run ANALYZE items; so the planner has fresh statistics.

Measuring recall and latency

You cannot tune what you do not measure, and an approximate index gives you no error when it is wrong. Recall is the fraction of the true top-k that your query returns. Measure it by running the same query twice, once with the index and once with the index disabled, and comparing the id sets.

-- Approximate result
SELECT id FROM items ORDER BY embedding <=> $1 LIMIT 10;

-- Exact result, forced by disabling index scans in this transaction
BEGIN;
SET LOCAL enable_indexscan = off;
SELECT id FROM items ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

Recall at 10 is the size of the intersection divided by ten. Do it in a script over a few hundred real queries sampled from production traffic. A random vector is a poor test query, because real queries cluster in parts of the space your data also occupies. A short Python harness is enough:

def recall_at_k(conn, queries, k=10):
    total = 0.0
    for q in queries:
        approx = {r[0] for r in conn.execute(
            "SELECT id FROM items ORDER BY embedding <=> %s LIMIT %s", (q, k))}
        with conn.transaction():
            conn.execute("SET LOCAL enable_indexscan = off")
            exact = {r[0] for r in conn.execute(
                "SELECT id FROM items ORDER BY embedding <=> %s LIMIT %s", (q, k))}
        total += len(approx & exact) / k
    return total / len(queries)

Note that disabling index scans can leave the planner choosing a bitmap scan or a sequential scan, which is what you want for ground truth. Run this on a replica or staging copy if the table is large, since exact scans are expensive.

Then sweep one parameter at a time and record both recall and latency. A table of results makes the trade-off visible and gives you a defensible setting:

Pick the smallest value that meets your recall target, not the largest the budget allows. Record the target and the measurement in the repository so the next engineer knows why the number is what it is.

For latency itself, rely on the standard Postgres tools. EXPLAIN (ANALYZE, BUFFERS) shows whether you hit the index, how many buffers came from cache and how many from disk. Since 0.8.2 the Index Searches line is correct on Postgres 18. The pg_stat_statements extension gives you per-query totals and averages across the fleet, and pg_stat_user_indexes shows whether an index is being scanned at all. Add one gauge for recall on a sample and alert when it drops, because recall is the only metric that tells you the index is returning the right answers.

For exact search, parallelism helps. Increase max_parallel_workers_per_gather, and if the planner declines a parallel scan because out-of-line storage is not costed well, you can nudge it for one transaction:

SET LOCAL min_parallel_table_scan_size = 1;
SET LOCAL parallel_setup_cost = 1;

Using pgvector from Python without surprises

Most applications reach pgvector through Python, and the client library changed in a way that breaks older code. The current companion library is pgvector-python 0.5.0, installed with pip install pgvector.

The breaking changes in 0.5.0 are worth knowing because tutorials from before it are everywhere. Imports now come from the top-level package: from pgvector import Vector, HalfVector, Bit, SparseVector. The old pgvector.utils package and the classes re-exported from driver modules, such as pgvector.psycopg.Vector, were removed. NumPy is no longer a dependency, so code that relied on getting arrays back must call .to_numpy() itself. With Psycopg 3, Psycopg 2, asyncpg and pg8000, a vector column now comes back as a pgvector.Vector object, and you call .to_list() or .to_numpy() on it. With the ORMs, vector and halfvec columns return a plain Python list. The library requires Python 3.10 or newer and SQLAlchemy 2 or newer.

With Psycopg 3, register the type on each connection after the extension exists, since registration looks up the type's OIDs:

import psycopg
from pgvector import Vector
from pgvector.psycopg import register_vector

with psycopg.connect("postgresql://app@db/app", autocommit=True) as conn:
    conn.execute("CREATE EXTENSION IF NOT EXISTS vector")
    register_vector(conn)
    conn.execute("INSERT INTO items (embedding) VALUES (%s)", (Vector([1, 2, 3]),))
    rows = conn.execute(
        "SELECT id FROM items ORDER BY embedding <-> %s LIMIT 5", (Vector([1, 2, 3]),)
    ).fetchall()

With a connection pool, registering on one connection is not enough, because the pool hands you others. Use the pool's configure hook so every connection registers the type: ConnectionPool(conninfo, configure=lambda c: register_vector(c)). With asyncpg, pass init= to the pool, and with async Psycopg use register_vector_async.

A characteristic error comes from skipping registration: operator does not exist: vector <-> double precision[]. The driver sent your Python list as a Postgres array. The fix is to register the type, or cast the parameter in SQL as %s::vector.

With SQLAlchemy 2, the types are VECTOR, HALFVEC, BIT and SPARSEVEC, and Vector remains an alias of VECTOR:

from pgvector.sqlalchemy import VECTOR
from sqlalchemy import Index, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase): pass

class Item(Base):
    __tablename__ = "items"
    id: Mapped[int] = mapped_column(primary_key=True)
    embedding: Mapped[list[float]] = mapped_column(VECTOR(1536))
    __table_args__ = (
        Index("items_embedding_idx", "embedding",
              postgresql_using="hnsw",
              postgresql_with={"m": 16, "ef_construction": 64},
              postgresql_ops={"embedding": "vector_cosine_ops"}),
    )

stmt = select(Item).order_by(Item.embedding.cosine_distance([3, 1, 2])).limit(5)

Django offers VectorField, HnswIndex and IvfflatIndex, plus a VectorExtension() migration operation that creates the extension so your migrations are self-contained. Include VectorExtension() before any migration that adds a vector field.

For an API service, the natural place for all this is a dependency that provides a pooled connection, as covered in the FastAPI guide. If you build retrieval with a framework, both LangChain and LlamaIndex ship pgvector-backed stores. Those are third-party integrations, not part of pgvector, so check their documentation for which pgvector features, such as iterative scans and index options, they expose. When the framework's store does not expose a setting you need, such as hnsw.ef_search, set it at the role or database level instead.

Hybrid search with full-text search

Embeddings find meaning and miss exact tokens: part numbers, error codes, names. Keyword search has the opposite profile. Combining them is called hybrid search, and since pgvector lives inside Postgres, you can do it in one database with no second service.

The keyword side uses Postgres full-text search. You store a tsvector for each document, index it with GIN, and query with plainto_tsquery:

ALTER TABLE items ADD COLUMN textsearch tsvector
  GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;
CREATE INDEX ON items USING gin (textsearch);

SELECT id, content
FROM items, plainto_tsquery('hello search') query
WHERE textsearch @@ query
ORDER BY ts_rank_cd(textsearch, query) DESC
LIMIT 5;

You now have two ranked lists with incomparable scores: a distance and a text rank. Do not try to add them. The standard fix is Reciprocal Rank Fusion, which uses only each item's position in each list. An item's fused score is the sum, over the lists, of 1 / (k + rank), with k commonly 60. An item that ranks high in both lists wins, and no score normalisation is needed.

WITH semantic AS (
  SELECT id, row_number() OVER (ORDER BY embedding <=> $1) AS rank
  FROM items ORDER BY embedding <=> $1 LIMIT 20
),
keyword AS (
  SELECT id, row_number() OVER (ORDER BY ts_rank_cd(textsearch, query) DESC) AS rank
  FROM items, plainto_tsquery('english', $2) query
  WHERE textsearch @@ query
  ORDER BY ts_rank_cd(textsearch, query) DESC LIMIT 20
)
SELECT coalesce(s.id, k.id) AS id,
       coalesce(1.0 / (60 + s.rank), 0) + coalesce(1.0 / (60 + k.rank), 0) AS score
FROM semantic s FULL OUTER JOIN keyword k ON s.id = k.id
ORDER BY score DESC
LIMIT 10;

Running it: poolers, replicas, containers and cost

pgvector adds no process, no port and no credentials. It runs inside Postgres, so you operate it with the same tools as Postgres. That is its main operational advantage, and the details below are where the exceptions hide.

Poolers. In PgBouncer's transaction mode, a session-level SET hnsw.ef_search = 100 may not apply to your next query, because your next transaction can land on a different server connection. Two fixes work: wrap the query in a transaction and use SET LOCAL, or set the value at the role or database level with ALTER ROLE app SET hnsw.ef_search = 100. The second is simpler and survives every pooling mode.

Replicas. Indexes are WAL-logged relations, so a hot standby has the same indexes as the primary and can serve read queries. The extension binary must be installed on the replica too, or the replica fails when it tries to use the index type. Send heavy ANN traffic and recall-measurement jobs to a replica, and keep index builds on the primary.

Containers. Pin the exact tag. The official image is pgvector/pgvector, and its tags follow pg18-trixie, pg18-bookworm and the pinned form 0.8.6-pg18-trixie. There is no latest tag, and the short tags pg18 and 0.8.6-pg18 point to bookworm. Pinning means a rebuild next month gives you the same pgvector. If you build binaries yourself, use make OPTFLAGS="" for anything that will run on more than one CPU model, because the default can compile for the build machine's CPU and crash elsewhere with Illegal instruction.

Kubernetes. Run Postgres through an operator or a managed service rather than a plain Deployment, give the pod a memory limit with room for shared_buffers, the OS cache and maintenance memory, and mount memory-backed /dev/shm for parallel builds. Package the configuration with Helm so maintenance_work_mem and the shared-memory volume change together.

Hosted Postgres. Many providers ship pgvector, including AWS RDS and Aurora, Google Cloud SQL and AlloyDB, Azure Database for PostgreSQL, Supabase and Neon. Which version you get depends on the provider and its Postgres minor version, so check it rather than assuming:

SELECT * FROM pg_available_extension_versions WHERE name = 'vector';

On Azure Flexible Server you must also allow-list VECTOR in the azure.extensions server parameter before CREATE EXTENSION works. For teams in the Gulf and Egypt, the practical point is regional: choose a provider region that satisfies your data-residency requirements first, then check that its pgvector version meets the 0.8.3 floor. Embeddings can leak information about the text they came from, so they carry the same sensitivity as the source data, including in backups and replicas.

Tenants, permissions and security

Because pgvector has no authentication or network surface of its own, every security property comes from Postgres, and that is good news: the controls you already know apply.

Start with who can create the extension. The control file does not mark it as trusted, so on self-managed Postgres a superuser must run CREATE EXTENSION vector, and a normal role gets permission denied to create extension "vector" with the hint "Must be superuser to create this extension". Managed services give you a pseudo-superuser role, such as rds_superuser or azure_pg_admin, for the same step. The extension is relocatable, so you can install it into a dedicated schema with CREATE EXTENSION vector SCHEMA extensions; and control which schemas are on each role's search_path. This is standard hygiene against search-path hijacking. The side effect is that type "vector" does not exist can mean the type is in a schema your session cannot see, so qualify it as extensions.vector or fix the path.

For multi-tenant data, the key fact is that a shared ANN index mixes every tenant's vectors, so one tenant's data affects another's recall and latency. Your options, from most to least isolation:

1. Separate tables or databases per tenant. Strongest isolation, highest operational cost.
2. List partitioning by tenant. One index per partition, so a tenant's queries touch only their data. Good for a moderate number of sizeable tenants.
3. Partial indexes for a few very large tenants, with WHERE (customer_id = 42).
4. A shared index plus a B-tree on customer_id. For many small tenants, exact search over the tenant's rows is cheap, or a shared HNSW with iterative scans handles the filter.

Whichever you choose, enforce who can see what with row-level security, not with application code alone. A policy on the table filters rows at the database, so a bug in the query layer cannot return another tenant's embeddings. Note that RLS adds predicates to queries, so test the plan: a policy is one more filter that interacts with the post-filtering behaviour described earlier.

Finally, keep the extension patched. Version 0.8.2 fixed a buffer overflow in parallel HNSW builds, and 0.8.6 fixed an overflow in IVFFlat builds on 32-bit systems. You do not need to track every release, but you do need a process that notices when yours is behind.

Versions, upgrades and what changed

Many blog posts and chat answers describe pgvector as it was in 0.5 or 0.7. The current release is 0.8.6, from July 2026, and the details have moved. Here is the history that affects decisions at work.

Nothing in the SQL surface has been removed since 0.5. The removals were Postgres major versions, 11 and 12, and client-library APIs. The recommendation that follows is simple: run 0.8.3 or later in production, and 0.8.6 if your provider offers it.

Upgrading is a two-step process, and the second step is the one people forget. First install the new binaries the same way you installed the old ones: a package upgrade, a new image tag, or a rebuild from source. Then, in each database that uses the extension, run:

ALTER EXTENSION vector UPDATE;
SELECT extversion FROM pg_extension WHERE extname = 'vector';

Until you run the ALTER, the binary is new but the SQL objects are old, which is how you end up with the 0.5-era access method does not exist symptom on a server that "has the new version". You can see the gap directly:

SELECT default_version, installed_version
FROM pg_available_extensions WHERE name = 'vector';

If the two columns differ, the binary on disk is ahead of what the database has installed. A major Postgres upgrade, whether through pg_upgrade or a provider's major-version upgrade, needs pgvector installed for the new major version first. Managed services upgrade the extension with their own Postgres minor releases, so the version you get is theirs to schedule.

Putting it all together

Here is a realistic path for a retrieval table in a service that already runs on Postgres, using what this level covered. A support-article search stores 1,536-dimension embeddings for about five million articles, filtered by product and language, and the team wants fast, high-recall results.

First, size it: 5,000,000 rows at 6,152 bytes is about 31 GB of heap, so a replica with enough RAM for the hot set is justified. Second, choose the representation. Half-precision indexing roughly halves the index, so the team indexes the halfvec expression and keeps the full vector column for re-ranking and for the day they want a different index.

CREATE INDEX CONCURRENTLY articles_embedding_hnsw
  ON articles USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops);
CREATE INDEX ON articles (product_id, language);
ALTER ROLE search_api SET hnsw.iterative_scan = 'relaxed_order';
ALTER ROLE search_api SET hnsw.ef_search = 100;

Third, the query. It repeats the index expression, orders by the bare operator, filters on product and language, and returns only the top ten:

SELECT id, title
FROM articles
WHERE product_id = $2 AND language = $3
ORDER BY embedding::halfvec(1536) <=> $1::halfvec(1536)
LIMIT 10;

Fourth, verify before trusting it. The team samples 300 real queries, runs the recall harness against exact search, and records recall at 10 for ef_search of 40, 100 and 200. They choose 100 because it meets their target at acceptable p95 latency, and write the number and the reason in the repository. Fifth, operate it: autovacuum settings reviewed for the table, the extension at 0.8.6 pinned in the container tag, a nightly job that reports recall on a sample and index size, and an alert when recall falls below the target.

Where you are now

You can now predict what pgvector will do instead of discovering it: what a row costs, whether the planner will use an index, what each HNSW and IVFFlat setting changes, why filters shrink results and how iterative scans fix that, and how to build, shrink, measure and upgrade safely. The versions that matter are 0.8.0 for iterative scans, 0.8.2 for the parallel build fix and 0.8.3 for the vacuum corruption fix.

What this level did not cover is running pgvector as a shared platform: failure modes at large scale, capacity planning across teams, governance, and the point at which another system is the better choice. That is the senior material. Before you move on, put the recall harness in your repository and run it on your most important table. It is the one tool in this guide that turns every other setting from an opinion into a measurement.

Sources

• pgvector README for v0.8.6: https://github.com/pgvector/pgvector/blob/v0.8.6/README.md
• pgvector repository: https://github.com/pgvector/pgvector
• Changelog: https://github.com/pgvector/pgvector/blob/master/CHANGELOG.md
• Extension SQL with types, operator classes and casts: https://github.com/pgvector/pgvector/blob/v0.8.6/sql/vector.sql
• Control file: https://github.com/pgvector/pgvector/blob/v0.8.6/vector.control
• HNSW settings and limits: https://github.com/pgvector/pgvector/blob/v0.8.6/src/hnsw.c
• IVFFlat settings and limits: https://github.com/pgvector/pgvector/blob/v0.8.6/src/ivfflat.c
• Docker image: https://hub.docker.com/r/pgvector/pgvector
• Parallel HNSW build overflow, issue 959: https://github.com/pgvector/pgvector/issues/959
• Hosted providers, issue 54: https://github.com/pgvector/pgvector/issues/54
• pgvector-python: https://github.com/pgvector/pgvector-python
• pgvector-python changelog: https://github.com/pgvector/pgvector-python/blob/master/CHANGELOG.md
• Postgres index build progress reporting: https://www.postgresql.org/docs/current/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING
• Postgres partitioning: https://www.postgresql.org/docs/current/ddl-partitioning.html
• Postgres partial indexes: https://www.postgresql.org/docs/current/indexes-partial.html
• Postgres CTE materialization: https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION
• Postgres full-text search: https://www.postgresql.org/docs/current/textsearch-intro.html
• Postgres pg_stat_statements: https://www.postgresql.org/docs/current/pgstatstatements.html

Part two of three. At this level an interviewer is no longer asking what a vector is. They describe a system that misbehaves and watch how you narrow it down. This file is organised as scenarios first, then trade-offs, then a guide to what separates a strong answer from a weak one. It assumes pgvector 0.8.6, and where an answer depends on a version, the version is named.

Where this picks up

The thirty-second answer, at this level

pgvector is a Postgres extension that adds vector types, distance operators, two approximate index types and a few settings. Because vectors live in ordinary columns, you keep transactions, joins, replication, backups and row-level security. The cost of that convenience is that you share a database with everything else, and you must understand the index behaviour yourself, because an approximate index will not raise an error when it returns the wrong answer.

If you can say that, and then say which three things you check when a query is slow or short (the plan, the filter's selectivity, and recall against exact search), you have framed the rest of the interview.

Scenario: the query is slow after we added an index

The question. "We added an HNSW index and the query is not faster. What do you check first?"

A strong answer starts with whether the index is being used at all, because that is the cheapest question. Run the query under EXPLAIN (ANALYZE, BUFFERS) and look for an index scan on the HNSW index. If the plan shows a sequential scan, there are five usual causes: no LIMIT; ordering descending or by a computed expression such as 1 - (embedding <=> q); an operator that does not match the opclass, such as <-> against vector_cosine_ops; a table small enough that a scan is genuinely cheaper; or a query on the base column when only an expression index exists.

To separate "cannot use" from "chose not to", wrap the query in a transaction, run SET LOCAL enable_seqscan = off, and explain again. If the index scan appears, the index is usable and the planner estimated that a scan was cheaper, which might be correct for a small table. If it still shows a sequential scan, the query shape or opclass is wrong.

If the index is used and the query is still slow, move on to buffers. A large number of shared reads rather than hits means the index does not fit in memory, which points at shared_buffers, the OS cache, or shrinking the index with halfvec. Only after those do you touch hnsw.ef_search, because lowering it trades recall for speed and you must know your recall first.

What a weak answer does. It jumps to "increase ef_search" or "increase m" without having looked at the plan. It also never asks what "slow" means in numbers.

Scenario: results got worse after adding the index

The question. "Before the index the search was right. Now users say the results are worse, and sometimes there are only a handful."

Two separate effects are being described, and a strong answer names both.

First, an approximate index returns different results from exact search by design. Recall below one hundred percent is the price of speed, and the right response is to measure it. Run a sample of real queries with the index, run them again with SET LOCAL enable_indexscan = off in a transaction for ground truth, and compute the overlap of the top ten. That gives you recall at ten. Then raise hnsw.ef_search in steps and plot recall against latency to choose a value.

Second, "only a handful of rows" is a different problem. hnsw.ef_search defaults to 40 and caps the rows one index scan returns, so LIMIT 100 cannot return 100 rows at the default. If there is also a WHERE clause, the filter is applied after the index returns its candidates. A filter matching ten percent of the table leaves about four of forty. The remedy depends on selectivity, covered in the next scenario.

A candidate who also mentions dead tuples shows depth. Updated and deleted rows that have not been vacuumed still occupy candidate slots and are then discarded, so a high-churn table returns fewer rows than a clean one. Check autovacuum, and check the extension version. Versions before 0.8.3 had a possible index corruption problem with HNSW vacuuming, which is a reason to upgrade on its own.

Scenario: filtered queries return too few rows

The question. "Our search filters by tenant and language. For small tenants we get two results instead of ten. What are the options?"

This is the most common design question about pgvector, and the answer is a ladder, ordered by the README's advice.

1. A B-tree on the filter columns. If the filter is selective, Postgres can find the few matching rows through the B-tree and compute exact distances over them. It is exact, fast for small subsets and needs no tuning. For small tenants this is often the whole answer.
2. Iterative scans (0.8.0 and later). Set hnsw.iterative_scan = relaxed_order, and when the filter leaves too few rows the scan continues until the LIMIT is met or hnsw.max_scan_tuples is reached. It suits filters matching a moderate share of the table. It is bounded, so a very selective filter can still come up short.
3. A partial index for a handful of large, distinct values, for example WHERE (customer_id = 42). The query must include the same predicate.
4. Partitioning by the filter column for many values, so each partition has its own index and a query touches only one.

A strong answer picks per tenant size rather than one global strategy: partitions or partial indexes for the few large tenants, a B-tree and exact search for the long tail, and iterative scans as a safety net. It also notes that row-level security adds its own predicate, which interacts with post-filtering, so the plan has to be tested with RLS on.

It should mention the mode choice too. relaxed_order gives better recall but can return results slightly out of order. strict_order exists for HNSW only, not IVFFlat. If strict order matters, you can wrap the query in a materialized CTE and re-sort the output with ORDER BY distance + 0.

Scenario: the index build is taking forever, or failing

The question. "The HNSW build on a fifty-million-row table has run for hours. What do you look at?"

Start with the progress view: pg_stat_progress_create_index. For HNSW it reports blocks done against blocks total during the loading phase. Then check the server log for the notice hnsw graph no longer fits into maintenance_work_mem after N tuples. If it appeared, the build fell into a much slower mode because the graph outgrew memory. The fix is a larger maintenance_work_mem, within what the machine can really spare, since other maintenance operations may claim the same amount.

Next, parallelism. max_parallel_maintenance_workers defaults to 2 and is limited by max_parallel_workers, default 8, so both may need raising. Then ask about the environment. If the database runs in a container and the build fails with could not resize shared memory segment ... No space left on device, the shared memory is too small. Docker defaults /dev/shm to 64 MB, so run with --shm-size at least as large as maintenance_work_mem; in Kubernetes mount an emptyDir with medium: Memory at /dev/shm.

Also ask what ran before the build. Building before a bulk load makes every insert pay for index maintenance. Load first, build second. In production use CREATE INDEX CONCURRENTLY so writers are not blocked, and know its failure mode: a failed concurrent build leaves an invalid index that must be dropped.

For a version angle, mention that 0.8.2 fixed a buffer overflow in parallel HNSW builds. Anyone running parallel builds on an older version should upgrade before the next large build.

Scenario: we need to index 3,072-dimension embeddings

The question. "Our new model produces 3,072 dimensions and the index creation fails. What now?"

The error is column cannot have more than 2000 dimensions for hnsw index, and the limit is real: a vector can be stored up to 16,000 dimensions but indexed only to 2,000. A candidate who understands the whole set of options is distinguished from one who only knows one.

• Index at half precision. CREATE INDEX ... USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops). A halfvec indexes up to 4,000 dimensions and halves the memory. The query must repeat the same cast expression or the planner will not use the index. This is the first thing to try.
• Store as halfvec directly, if you do not need single precision at all.
• Binary quantization with re-ranking. A bit index takes up to 64,000 dimensions and is far smaller. Retrieve a generous candidate list by Hamming distance, then re-sort by the true distance. Accuracy loss depends on the model, so it must be measured.
• Prefix indexing with subvector, only for models trained so that a prefix is a usable embedding.
• Ask the model for fewer dimensions, if the provider supports it. This is a model choice, not a database one, but it is often the cleanest answer.

The point a strong answer adds is that every one of these needs a recall measurement before and after, since each changes what the index returns.

Scenario: the setting does not stick behind PgBouncer

The question. "We set hnsw.ef_search = 200 in the application but queries behave as if it were 40."

In transaction pooling mode, consecutive transactions from one client can use different server connections, so a session-level SET made in one transaction is not reliably present in the next. There are two correct fixes. Use SET LOCAL inside an explicit transaction around the query, which scopes the value to that transaction. Or set it once at role or database level, ALTER ROLE app SET hnsw.ef_search = 200, which every connection for that role picks up. The role-level setting is simpler and also covers iterative scan settings.

Mention that a mistyped name is an error, because the hnsw. and ivfflat. prefixes are reserved. That detail shows you have used these settings, not only read about them. Also mention that the values are validated, so hnsw.ef_search outside 1 to 1000 is rejected.

Trade-off: HNSW versus IVFFlat

The question. "Which do you pick and why?"

HNSW is the default choice. It has a better speed and recall trade-off, needs no training step, can be created on an empty table and stays good as data arrives. Its costs are slower builds and more memory. IVFFlat builds faster and uses less memory, but its cluster centres are fixed when it is built, so it must be created after loading representative data and can go stale as the distribution drifts. It does not support sparse vectors, has no L1 or Jaccard opclass, and its iterative scans have no strict mode.

A strong answer gives a condition for choosing IVFFlat: a very large table that is rebuilt often, or a memory budget where the HNSW graph cannot fit. It adds the tuning starting points: lists as rows over a thousand up to a million rows and the square root of the row count beyond that, probes around the square root of lists, and the fact that setting probes to lists makes a search exact.

Trade-off: pgvector inside Postgres versus a dedicated vector database

The question. "Why not use Qdrant, or Pinecone, or Milvus?"

Be balanced. The case for pgvector is operational and transactional. You already run Postgres, so you already have backups, point-in-time recovery, replication, monitoring and access control. A vector can be updated in the same transaction as the row it describes, joined against relational data and filtered with SQL. There is no second system to keep in sync. For many workloads, up to tens of millions of vectors with sensible hardware, that is enough.

The case against appears with scale and isolation. Vector workloads compete for memory and CPU with your transactional load. Large index builds are heavy. Sharding is more work than with a system built for it, though Citus and application-level sharding exist. If you need a very large collection, extreme query rates, or features such as built-in multi-tenancy, a dedicated system may be better. See the Qdrant guide and the FAISS guide for what those alternatives look like.

A strong answer does not claim a winner. It states the decision criteria, and says it would test with real data and a recall target before committing.

Common interview questions with model answers

What does <#> return? The negative inner product, because Postgres index scans only support ascending order on operators. Multiply by minus one to get the inner product.

Why does ORDER BY 1 - (embedding <=> q) DESC not use my index? The index serves only ascending order on the raw distance operator. Select the similarity but order by embedding <=> q.

Does an index change results? An approximate index can, which is why you measure recall against exact search.

What does ef_search do besides controlling quality? It caps how many rows a scan returns, with a default of 40.

What is the difference between ef_construction and ef_search? The first is a build option fixed in the index, and must be at least twice m. The second is a query-time setting you can change per transaction.

Why build IVFFlat after loading data? The k-means centres are computed at build time. On an empty table you get the notice ivfflat index created with little data and poor recall.

Which versions matter? 0.5.0 added HNSW, 0.7.0 added halfvec, sparsevec and quantization functions, 0.8.0 added iterative scans, and 0.8.3 fixed possible HNSW vacuum corruption. Recommend 0.8.3 or later in production.

How do you upgrade the extension? Install the new binaries, then run ALTER EXTENSION vector UPDATE in each database, then verify with SELECT extversion FROM pg_extension WHERE extname = 'vector'.

What is the Docker image? pgvector/pgvector, with tags like pg18-trixie and pinned 0.8.6-pg18-trixie. There is no latest tag.

Do NULL vectors appear in ANN results? No. NULL vectors are not indexed, and zero vectors are not indexed for cosine.

How do you do hybrid search? Run a full-text query and a vector query, then fuse the two ranked lists with Reciprocal Rank Fusion, summing 1 / (k + rank) with k often 60, or re-rank with a cross-encoder.

What a strong answer covers that a weak one misses

Interviewers at this level listen for a few habits.

It starts with the cheapest diagnostic. A strong candidate reaches for EXPLAIN before changing any setting. A weak one starts turning dials.

It names the version. "Iterative scans, from 0.8.0" and "HNSW vacuum fix in 0.8.3" are the kind of specifics that show current knowledge. Many answers online are stuck on 0.5 to 0.7 behaviour, and an interviewer who knows this will probe for it.

It treats recall as a number. A weak answer says "it's approximate". A strong one says how it would measure recall, on which queries, and what target it would defend.

It separates the symptoms. Slow, wrong and short are three different problems with three different causes, and a strong candidate does not blur them.

It states costs. Every option has one: memory, build time, recall, latency or write speed. Naming the cost shows you have run it.

It knows the boundaries. It can say when pgvector is the wrong tool, without defensiveness.

For the surrounding stack, the Docker guide and the Kubernetes guide cover the container and shared-memory points that came up above.

Sixty-second self-test

Answer each aloud without notes.

1. Name the five reasons a vector index is not used.
2. Which opclass pairs with <=>, and what happens if you query with <-> instead?
3. What does hnsw.ef_search do, and what is its default?
4. Why does a filtered query return fewer rows, and what are the four fixes?
5. Which version added iterative scans, and which modes does IVFFlat support?
6. How do you measure recall?
7. What fails when you index a 3,072-dimension vector, and what are two fixes?
8. Why does SET hnsw.ef_search misbehave behind a transaction-mode pooler?
9. What are the two steps of an extension upgrade?
10. Which minimum version would you run in production, and why?

Part two of three. At this level the index exists and the queries return something, so the problems change character. They stop being "why does this error" and become "why is recall low", "why did the build take nine hours" and "why does it behave differently in staging". Start with the error table, then the practices in order of leverage, then the practice cards and operational recipes. Everything here is written for pgvector 0.8.6.

Common errors at this level

These are cumulative: the beginner errors still apply, and these are the ones that appear once things work.

Three of these deserve more than a table row, because they fail silently.

Rows that never appear. NULL vectors are not indexed, and zero vectors are not indexed under cosine. If an item is not showing up in approximate results even though it exists, check for a NULL or all-zero embedding first. The exact query, with index scans off, will show whether the row is reachable.

Results that quietly decay. An IVFFlat index built once has fixed cluster centres. If the data changes character, recall drops with no error. An HNSW index on a table with heavy churn loses candidate slots to dead tuples until vacuum catches up. Neither produces a message. Only a recall metric does.

A good index with the wrong query. The index is only used for ORDER BY <operator expression> ASC LIMIT n. The most common way to lose it is to write ORDER BY 1 - (embedding <=> q) DESC, which reads naturally and falls back to a sequential scan.

Practices that pay off most, in order of leverage

These are ordered by how much they save for how little effort. If you only adopt the first three, you have most of the benefit.

1. Measure recall against exact search

Nothing else on this list matters as much, because without a recall number every setting is a guess. Take a few hundred real queries, run each with the index and again with SET LOCAL enable_indexscan = off inside a transaction, and compute the overlap of the top ten. Record the result next to the index definition. When you change ef_search, m, a quantization scheme or the pgvector version, run it again.

Use real queries, not random vectors. Real queries cluster in the regions where your data lives, and random vectors do not, so they overstate how easy the job is.

2. Read the plan before changing a setting

EXPLAIN (ANALYZE, BUFFERS) answers whether the index was used, how much came from cache, and where the time went. Ninety percent of slow-query reports are a query shape the index cannot serve. Fixing the shape costs nothing, while tuning around a sequential scan costs a lot.

3. Match the opclass to the operator, and index one distance

An index serves only the operator it was built for. Pick the distance your embedding model was trained for, build one index for it, and use the same operator everywhere in the codebase. Two indexes for two distances double the memory and write cost for a feature you probably do not need. For normalised vectors, inner product gives the same ranking as cosine and is the cheapest to compute, so confirm normalisation with vector_norm before relying on it.

4. Load first, build second, build concurrently

Bulk load the table, then create the index. Run ANALYZE afterwards. In production use CREATE INDEX CONCURRENTLY so writers are not blocked, and know that a failed concurrent build leaves an invalid index you must drop.

5. Give the build enough memory

HNSW builds are far faster when the graph fits in maintenance_work_mem. Raise it for the build session, raise max_parallel_maintenance_workers and, if needed, max_parallel_workers, and watch the log for the notice that the graph no longer fits. In containers, raise shared memory to match.

6. Put settings at the role level

ALTER ROLE app SET hnsw.ef_search = 100 and ALTER ROLE app SET hnsw.iterative_scan = 'relaxed_order' survive poolers, apply to every connection and keep tuning out of application code. Use SET LOCAL in a transaction for the occasional query that needs something different.

7. Decide the filtering strategy deliberately

Look at the selectivity of every filter in your hot queries. Selective filters want a B-tree and exact search. Moderate ones want iterative scans. A few large values want partial indexes, and many values want partitions. Do not leave this to chance, because the default behaviour is the silent short result.

8. Run 0.8.3 or later

The reasons are concrete: 0.8.2 fixed a buffer overflow in parallel HNSW builds, and 0.8.3 fixed possible index corruption with HNSW vacuuming. Pin the version in your container tag, for example pgvector/pgvector:0.8.6-pg18-trixie, and remember there is no latest tag.

9. Shrink before you scale

If memory is the constraint, try half precision first. It halves the size and usually needs no re-ranking. Only then consider binary quantization with a re-rank step, and measure recall at each stage.

10. Keep the vacuum healthy

Dead tuples reduce HNSW results. Watch n_dead_tup and last_autovacuum for the embedding table, and for a large table with heavy deletes use REINDEX INDEX CONCURRENTLY followed by VACUUM.

Practice cards

Each card is a small exercise you can run on a staging copy. They are ordered from easiest to most involved.

Card 1: Prove the index is used

1. Pick your most common query and run it under EXPLAIN (ANALYZE, BUFFERS).
2. Note whether the plan shows an index scan on your HNSW or IVFFlat index.
3. Rewrite the order clause to ORDER BY 1 - (embedding <=> q) DESC and run it again. Compare plans.

What you should learn: the shape of the ORDER BY decides everything. Keep the plan output in your notes as the reference for what "good" looks like.

Card 2: Break and fix a filter

1. Choose a filter that matches about ten percent of your rows.
2. Run the query with LIMIT 20 and count the rows returned.
3. Set hnsw.iterative_scan = relaxed_order and count again.
4. Add a B-tree on the filter column and compare the plan.

What you should learn: the arithmetic of post-filtering, and that the cure depends on selectivity.

Card 3: Sweep ef_search

1. Sample 200 real queries.
2. For hnsw.ef_search of 40, 100 and 200, record recall at ten and p95 latency.
3. Choose the smallest value that meets your target.

What you should learn: how steeply latency grows against recall for your data. Write the decision down with the numbers.

Card 4: Watch a build

1. In one session, start an HNSW build on a copy of a large table with default maintenance_work_mem.
2. In another, poll pg_stat_progress_create_index once a minute.
3. Look in the log for the notice that the graph no longer fits.
4. Repeat with a larger maintenance_work_mem and more workers.

What you should learn: how much of build time is memory, and what the slow path looks like in the log.

Card 5: Shrink the index

1. Build a half-precision expression index on a copy.
2. Compare pg_relation_size against the full-precision index.
3. Run the recall script against both.
4. Confirm with EXPLAIN that the query repeats the exact cast expression.

What you should learn: the saving is real, the recall cost is usually small, and an expression mismatch silently disables the index.

Card 6: Simulate a short deployment failure

1. Run Postgres in a container with the default shared memory.
2. Raise maintenance_work_mem to 1 GB and try a parallel HNSW build.
3. Read the error, then rerun with --shm-size=1g.

What you should learn: the exact message could not resize shared memory segment, so that you recognise it in a pipeline log. The Docker guide explains why the default is 64 MB.

Card 7: Rehearse an upgrade

1. On a copy, install an older pgvector, create the extension and an index.
2. Install the new binaries.
3. Compare default_version and installed_version in pg_available_extensions.
4. Run ALTER EXTENSION vector UPDATE and confirm with SELECT extversion FROM pg_extension WHERE extname = 'vector'.

What you should learn: the binary and the catalogue are upgraded separately, and the second step is the one that gets forgotten.

Operational recipes

Copy these into a runbook and adapt the names.

Recipe: measure recall for a sample of queries

-- Approximate result for one query
SELECT id FROM items ORDER BY embedding <=> $1 LIMIT 10;

-- Exact result for the same query
BEGIN;
SET LOCAL enable_indexscan = off;
SELECT id FROM items ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

Compare the two id sets in a script and average the overlap divided by ten. Run it on a replica or staging copy when the table is large, because exact scans are expensive. The Python version lives in the detailed guide.

Recipe: build an HNSW index safely

SET maintenance_work_mem = '4GB';
SET max_parallel_maintenance_workers = 6;
CREATE INDEX CONCURRENTLY items_embedding_hnsw
  ON items USING hnsw (embedding vector_cosine_ops);

In another session, watch progress:

SELECT phase,
       round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;

Adjust the memory to what the server can spare. Afterwards, check for an invalid index with \d items and run ANALYZE items;.

Recipe: make filtered queries complete

ALTER ROLE search_api SET hnsw.iterative_scan = 'relaxed_order';
ALTER ROLE search_api SET hnsw.max_scan_tuples = 50000;
CREATE INDEX ON items (category_id);

New connections for that role pick the settings up. Test latency before and after, because iterative scans trade time for completeness. If a filter is very selective, rely on the B-tree and exact search instead.

Recipe: restore strict order from a relaxed scan

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 + 0 is needed on Postgres 17 and later.

Recipe: index a 3,072-dimension column

CREATE INDEX ON items USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);

SELECT id FROM items
ORDER BY embedding::halfvec(3072) <=> $1::halfvec(3072)
LIMIT 10;

The query repeats the expression exactly. Measure recall against the exact query before shipping.

Recipe: reduce index bloat on a churning table

REINDEX INDEX CONCURRENTLY items_embedding_hnsw;
VACUUM items;

Do this off-peak, with enough free disk for a second copy of the index while the swap happens.

Recipe: check what is installed and what is available

SELECT default_version, installed_version
FROM pg_available_extensions WHERE name = 'vector';

SELECT * FROM pg_available_extension_versions WHERE name = 'vector';

Run these on the primary and every replica. On managed services, this is how you find out which version your provider actually gives you.

Recipe: run the right container

docker run -d --name pg \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  --shm-size=1g \
  pgvector/pgvector:0.8.6-pg18-trixie

The tag is pinned to a pgvector and Postgres version, and --shm-size is at least the maintenance_work_mem you plan to use. For Kubernetes, mount a memory-backed emptyDir at /dev/shm instead, and keep the configuration in a chart, as in the Helm guide.

Recipe: set up an extension schema

CREATE SCHEMA extensions;
CREATE EXTENSION vector SCHEMA extensions;
ALTER DATABASE app SET search_path = "$user", public, extensions;

This keeps extension objects out of the public schema. The cost is that anything which cannot see the schema reports type "vector" does not exist, so qualify as extensions.vector there.

Recipe: a Python connection pool that registers the type

from psycopg_pool import ConnectionPool
from pgvector.psycopg import register_vector

pool = ConnectionPool(
    "postgresql://app@db/app",
    configure=lambda conn: register_vector(conn),
)

The extension must already exist, because registration looks up the type's identifiers. Run the migration before the pool starts.

Habits that prevent most of the above

These are small, repeatable habits, none of which requires new tooling.

Write the recall number next to the index. A comment in the migration or a line in the repository is enough: "recall at 10 was 0.97 at ef_search 100 on the March sample". When someone asks why the value is 100, you have an answer.

Keep an exact path. Even when the index serves all production traffic, keep the exact query available for measurement and for tiny tenants.

Use one model per column. Do not mix embeddings from different models in one column, and never compare vectors across models. If you must keep several, use separate columns or a table keyed by model with partial expression indexes.

Treat a model change as a migration. A new model means new dimensions, a new column, a new index and a new recall measurement. Run old and new side by side, then switch.

Pin versions. Pin the pgvector version in the container tag and the Python library in your lockfile. Read the changelog before upgrading either.

Test with a realistic row count. An index on a thousand rows behaves nothing like an index on ten million, and the planner may legitimately prefer a sequential scan on the small one. Test with data at the scale you intend to run.

Keep embeddings sensitive. They can leak information about the source text. Apply the same access controls, encryption and retention rules as the source data, including backups and replicas, and put tenant isolation in row-level security instead of application code alone.

Alert on the right things. Index size, dead tuples, build duration, the extension version drifting between primary and replica, and a sampled recall figure. Latency alone will not tell you when results get worse.

A checklist before you ship a vector feature

• The query orders by the bare operator, ascending, with a LIMIT, and the plan shows an index scan.
• The opclass matches the operator, and only one distance is indexed.
• Recall at ten has been measured on real queries and recorded.
• Every filter in the hot path has a strategy: B-tree, iterative scan, partial index or partition.
• Settings are applied at role level, or with SET LOCAL, and verified behind the pooler.
• The index was built after loading, concurrently, with enough memory and shared memory.
• The extension is 0.8.3 or later, pinned in the image, and the same on every replica.
• Dimensions above 2,000 have a plan: half precision, bits or fewer dimensions.
• NULL and zero embeddings are handled on purpose.
• Vacuum is healthy, and there is an alert on recall.
• Embedding columns are covered by the same access and retention rules as the source text.

For the next step, the senior material covers running pgvector as a shared platform. Before then, work through the practice cards on your own data. The Kubernetes guide is the natural companion if your database runs in a cluster.

This is part three of three. It closes the series by taking every topic from Beginner and Mid one level further and adds the work you own when pgvector is a platform that other teams build on, rather than a tool you use yourself.

Up to now the questions were about making similarity search work and making it fast. From here they change: which component fails first as the corpus grows, who is allowed to read whose embeddings, what an upgrade does to indexes that took a day to build, how you notice that recall has quietly dropped, and when the honest answer is that Postgres is no longer the right place for this workload. The version this guide is checked against is pgvector 0.8.6, released on 29 July 2026.

Where this picks up

I am starting with the architecture, because every later decision depends on being exact about what pgvector is and what it borrows from Postgres.

What the architecture gives you, and what it costs

pgvector is a Postgres extension written in C. It adds four things: vector data types, distance operators and functions, two index access methods (hnsw and ivfflat), and a set of settings (GUCs) that tune those indexes. It adds no process, no network listener, no authentication system and no storage engine of its own. An embedding is a value in a row, and an ANN index is an ordinary Postgres relation made of pages in the shared buffer cache.

That design is the reason to choose it, and it deserves to be stated as a list of inherited properties, because each one is something you do not have to build or operate:

• Transactions. An embedding and the row it describes commit or roll back together. There is no window where the document exists and its vector does not.
• WAL and replication. Index changes are written to the write-ahead log, so streaming replicas receive them and have the indexes too. Crash recovery and point-in-time recovery cover vectors and indexes with no extra work.
• Backups. pg_dump, pg_basebackup and your managed provider's snapshots all include vector data. Nobody has to design a second backup system.
• Security primitives. Roles, GRANT, row-level security, TLS and pg_hba.conf apply unchanged.
• Joins. A similarity query can join to the permissions table, the tenant table or the product catalogue in the same statement.

The cost of the same design is shared fate. Vector search runs in the same buffer cache, on the same CPUs, under the same autovacuum, as your transactional workload. A large HNSW index competes with the hot pages of the orders table. A parallel index build consumes max_parallel_maintenance_workers workers and a large allocation of maintenance_work_mem. A bulk re-embedding job generates WAL that every replica must replay. None of these are bugs; they are the price of not running a second system, and your job as platform owner is to make the price visible before an incident makes it visible for you.

Failure modes follow from the architecture. Because the extension is a shared library loaded into every backend, a defect in it is a defect in Postgres: a bug in index build or scan code can crash a backend, and a crash of one backend makes the postmaster restart all of them. This is why the release notes matter more than they would for a client library. Version 0.8.2 fixed a buffer overflow in parallel HNSW index builds, and 0.8.3 fixed possible index corruption during HNSW vacuuming. Those are not performance tweaks; they are reasons for a patched floor across the whole fleet, and I come back to that in the governance section.

A second consequence is that the binary must exist on every server that might serve or promote. Replicas replay WAL records for an index whose access method is implemented by the shared library. If a replica lacks the library you will find out at the worst moment, during a failover. Treat "pgvector installed at the same version on every node" as an invariant that your provisioning code enforces, not a step someone remembers.

How the two indexes behave at scale

You know the parameters. This section is about behaviour: what each index does as data and traffic grow, and which resource runs out first.

HNSW is a multilayer proximity graph. A query enters at the top layer and greedily descends toward the query vector, then widens the search at the bottom layer using a candidate list whose size is hnsw.ef_search (default 40, range 1 to 1000). Two properties follow directly. First, ef_search is not just a quality knob, it is also the ceiling on how many rows one index scan can return, which is the source of the classic "I asked for 100 rows and got 40" report. Second, the graph is traversed by random access. When the index fits in cache, traversal is fast; when it does not, each hop can be a disk read, and latency degrades sharply rather than gradually.

HNSW has no training step, so it can be created on an empty table and keeps working as data arrives. The price is paid elsewhere: builds are slower and use more memory than IVFFlat, and each insert must find neighbours and update the graph, so an HNSW insert costs more than a B-tree insert. For a write-heavy platform that is the number to measure, not the query latency.

IVFFlat runs k-means at build time to partition vectors into lists clusters, then at query time searches the ivfflat.probes closest clusters. It builds faster, uses less memory, and has a worse speed and recall tradeoff. Its defining weakness is that the centroids are fixed when the index is built. If you build on an empty or tiny table, you get the notice ivfflat index created with little data with the detail This will cause low recall. If you build on representative data and the distribution later drifts, say because a team switches to a new embedding model or a new document source arrives, recall decays without any error. The only remedy is REINDEX.

Which breaks first depends on the axis you scale along:

The row that surprises teams most is the first. Everything works in staging with a million vectors, and production with fifty million passes through the point where the graph no longer fits in memory. The correct response is rarely to tune ef_search. It is to shrink the working set (the sizing section covers how), or to move ANN traffic to a replica with more memory than the primary, or to partition so each index is smaller.

Exact search remains a legitimate platform option, and a senior engineer should say so. With no index, Postgres computes every distance and sorts, giving perfect recall, and the scan can run in parallel (raise max_parallel_workers_per_gather). For a table of a few hundred thousand rows behind a selective filter, exact search over the filtered subset is often fast enough, needs no maintenance, and has no recall to monitor. Many platform incidents are caused by an ANN index added to a workload that never needed one.

Filtering, iterative scans and recall as a service level

A vector index answers "what is nearest", and a WHERE clause asks a different question. The two interact in a way that produces the most common silent defect on a shared platform. With an approximate index, the filter is applied after the index returns its candidates (post-filtering). If a filter matches 10% of rows and hnsw.ef_search is 40, the scan returns about 40 candidates and keeps about 4. The query succeeds, returns fewer rows than LIMIT, and raises no error.

Since 0.8.0 there are three tools, and the README's order of preference is the order I would teach teams:

1. A B-tree or multicolumn index on the filter column, so that a selective filter yields exact search over a small subset.
2. Iterative index scans, when the filter matches a large share of rows.
3. Partial indexes when there are few distinct filter values, or partitioning when there are many.

Iterative scans change the scan so that, when the filter removes candidates, it continues through more of the index until LIMIT is satisfied or a cap is reached. The settings are hnsw.iterative_scan (off, relaxed_order, strict_order), ivfflat.iterative_scan (off, relaxed_order; there is no strict order for IVFFlat), hnsw.max_scan_tuples (default 20,000), hnsw.scan_mem_multiplier (default 1, a multiple of work_mem) and ivfflat.max_probes. Two details matter for a platform owner. The cap hnsw.max_scan_tuples is approximate and does not affect the initial scan, so it bounds the worst case but is not a precise budget. And if raising the cap does not improve recall, the scan may be running out of memory, which is what hnsw.scan_mem_multiplier is for.

relaxed_order gives better recall but can return results slightly out of order. If a consumer needs strict ordering and you are on IVFFlat, or you want the extra recall of relaxed mode, wrap the query in a materialized CTE and sort the outer result:

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 + 0 is needed on Postgres 17 and later, as the README notes. Distance thresholds follow a similar rule: keep the distance filter outside the materialized CTE and the other filters inside it, otherwise the threshold is applied to a truncated candidate set.

The senior move is to stop treating these as per-query tuning and make them platform policy. All of these GUCs are user-settable, so you can set them per role or per database:

ALTER ROLE search_service SET hnsw.iterative_scan = 'relaxed_order';
ALTER ROLE search_service SET hnsw.ef_search = 100;
ALTER DATABASE catalog SET hnsw.max_scan_tuples = 50000;

The hnsw and ivfflat prefixes are reserved, so a typo such as SET hnsw.efsearch raises an error instead of silently creating a placeholder setting. That is a useful safety property, and it also means a configuration management tool that pushes a misspelled name will fail loudly.

Connection poolers deserve a specific warning. A session-level SET hnsw.ef_search does not follow a client through a transaction-mode PgBouncer, because consecutive transactions can land on different server connections. Use SET LOCAL inside an explicit transaction, or set the value per role with ALTER ROLE, which applies on every connection that role opens. Teams that discover this in production usually do so as an unexplained recall difference between two nominally identical environments.

Now the discipline that separates owning the platform from using it: recall is a service-level objective and must be measured. The way to measure it is to compare approximate results with exact ones on a sample of real queries. Run the exact version by disabling index scans inside a transaction:

BEGIN;
SET LOCAL enable_indexscan = off;
SELECT id FROM items ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

Recall at k is the size of the overlap between the approximate top k and the exact top k, divided by k. Run this on a schedule against a fixed set of sampled production queries, export the number as a metric (see the Prometheus guide at /student-guides/prometheus), and alert on a drop. The events that move it are mundane: an index rebuild with different parameters, a vacuum that leaves dead tuples, a data distribution change under IVFFlat, a new filter pattern from a new team. Without the metric, each of these is invisible.

Sizing the working set

Capacity planning for pgvector is mostly arithmetic about bytes, because the index's performance depends on how much of it stays in memory. The storage formulas are exact. A vector costs 4 * dims + 8 bytes, a halfvec costs 2 * dims + 8, a bit vector costs dims / 8 + 8, and a sparsevec costs 8 * nnz + 16. A 1,536-dimension vector is therefore about 6 KB, which is large enough that Postgres stores it out of line in TOAST; since 0.6.0 the type's storage is external, meaning it is stored out of line without compression. A hundred million such rows is roughly 600 GB of heap before any index exists.

The index is separate and depends on m, the corpus and the opclass, so measure it rather than predicting it:

SELECT pg_size_pretty(pg_relation_size('items_embedding_idx'));

Indexes do not have to fit in memory to work, but performance is far better when they do. The practical target is that shared_buffers plus the operating system page cache can hold the HNSW index and the hot parts of everything else on the same instance. That second clause is where shared platforms fail: the vector index fits on its own, then a reporting team's queries evict it.

There are four levers for shrinking the working set, and they differ in how much recall risk they carry.

Half-precision storage. A halfvec column is half the size of a vector column. If you would rather keep full precision in the table and shrink only the index, use an expression index and repeat the expression in queries:

CREATE INDEX ON items USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops);

SELECT id FROM items
ORDER BY embedding::halfvec(1536) <=> $1::halfvec(1536)
LIMIT 10;

The planner only uses an expression index when the query contains the same expression. This is the most common reason a team reports that "the new index is not used", and it is why a platform should publish query templates rather than let each team write its own. The same trick lifts a hard limit: vector can be indexed to 2,000 dimensions, halfvec to 4,000, and the error column cannot have more than 2000 dimensions for hnsw index is what a 3,072-dimension model produces without it.

Binary quantization. binary_quantize turns each positive element into 1 and everything else into 0, giving an index 32 times smaller than float4 and a Hamming-distance search. Recall is lower, so the pattern is to retrieve generous candidates from the binary index and re-rank them with the full-precision vector:

CREATE INDEX ON items USING hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops);

SELECT * FROM (
    SELECT * FROM items
    ORDER BY binary_quantize(embedding)::bit(1536) <~> binary_quantize($1::vector)
    LIMIT 100
) candidates
ORDER BY embedding <=> $1 LIMIT 10;

The inner LIMIT is a tuning parameter with a direct recall and latency effect, so it belongs in your recall measurement.

Subvector indexing. For models trained so that a prefix of the vector is itself useful (Matryoshka-style embeddings), index subvector(embedding, 1, N) and re-rank with the full vector. It works the same way as binary quantization: a cheap first stage, an accurate second stage.

Partitioning and partial indexes. These do not shrink data but shrink each index, so a query touches a smaller graph that is more likely to be cached.

Sparse vectors have their own ceiling. A sparsevec can hold up to 16,000 non-zero elements but an HNSW index accepts at most 1,000, and only HNSW supports it; IVFFlat does not. Vectors that are too dense raise sparsevec cannot have more than 1000 non-zero elements for hnsw index, and the options are pruning to the top weights or not indexing.

There is also a quiet limit in the other direction. NULL vectors are not indexed, and zero vectors are not indexed for cosine distance, so those rows never appear in ANN results. A pipeline that normalises a zero vector can also produce NaN, which is rejected outright with NaN not allowed in vector. Both belong in your ingestion validation, not in a support ticket.

Building, vacuuming and maintaining indexes

Index builds are the operation most likely to cause an incident on a pgvector platform, because they are long, memory-hungry and invisible to application teams. Treat them as change events with a runbook.

The ordering rule is simple: load data first, index afterwards. For IVFFlat it is mandatory, because the centroids come from the data. For HNSW it is a speed rule, since building over existing data in one pass is faster than inserting row by row into a growing graph. In production, build with CREATE INDEX CONCURRENTLY so that writes are not blocked, accepting that it takes longer and cannot run inside a transaction block.

Three settings govern build speed, and they should be raised in that order:

SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 7;
-- may also need max_parallel_workers (default 8)
CREATE INDEX CONCURRENTLY ON items USING hnsw (embedding vector_cosine_ops);

The reason maintenance_work_mem comes first is the graph. HNSW builds are much faster if the graph fits in it; when it stops fitting, Postgres emits NOTICE: hnsw graph no longer fits into maintenance_work_mem after 100000 tuples, with the hint that building will take significantly more time. The count in the message is how many tuples were loaded when it spilled, so a notice at 100,000 tuples on a ten-million-row table tells you the build will be slow almost from the start. Do not set the value so high that it exhausts server memory; remember that several maintenance operations, and autovacuum workers, can each claim their own allocation.

Parallel builds use shared memory, and this is where containerised platforms break. In Docker the default /dev/shm is 64 MB, and a parallel HNSW build fails with could not resize shared memory segment ... No space left on device. The fix is to give the container a --shm-size at least as large as maintenance_work_mem. In Kubernetes the equivalent is an emptyDir volume with medium: Memory mounted at /dev/shm. See /student-guides/kubernetes for the volume mechanics and /student-guides/docker for the flag.

Watch builds instead of guessing. The progress view differs by index type:

-- HNSW: phases are initializing, loading tuples
SELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;

-- IVFFlat: phases include performing k-means, assigning tuples, loading tuples
SELECT phase, round(100.0 * tuples_done / nullif(tuples_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;

For IVFFlat, the percentage is only meaningful in the loading phase; k-means shows no progress, which looks like a hang to an anxious operator. An IVFFlat build that needs more memory than allowed fails with memory required is X MB, maintenance_work_mem is Y MB, driven by lists and dimensions; raise the setting or lower lists.

Vacuum is the other long-term cost. HNSW vacuuming can be slow, and dead tuples reduce the number of results a scan returns. The README recommends reindexing and then vacuuming:

REINDEX INDEX CONCURRENTLY items_embedding_idx;
VACUUM items;

That approach is a deliberate platform pattern: a table with heavy update or delete traffic gets a scheduled concurrent reindex rather than relying on vacuum alone. It also needs disk space for a second copy of the index during the operation, which should be in your capacity plan. The vacuum fixes in 0.8.3 (possible index corruption) and 0.8.4 (the hnsw graph not repaired error and a possible error with inserts during vacuum) are the best argument for the version floor: running anything older on a table with deletes is running a known defect.

A note on update-heavy tables. Updating a non-vector column of a row creates a new row version, and the new version needs an index entry. Teams that store frequently changing metadata in the same table as the embedding pay HNSW insert costs for changes that did not touch the vector at all. Where the access pattern allows, keep volatile attributes in a separate table joined by key, and keep the embedding table append-mostly.

Security: the trust model, secrets and the supply chain

pgvector introduces no authentication, no network surface and no secrets. That is good news for the attack surface and a trap for the threat model, because it is easy to conclude that there is nothing to secure. There is: everything you protect in Postgres now also protects embeddings, and a few new risks exist.

The trust model. Anyone who can run SQL as a role that may read the table can read the embeddings and can run arbitrary distance queries against them. Authorisation therefore lives where it always lived: roles and GRANT (including column privileges), pg_hba.conf, TLS with sslmode=verify-full, and row-level security for per-tenant row filtering. The senior point is to put these in the platform, not in application code. If the only thing separating tenant A from tenant B is a WHERE tenant_id = ... in a service, a missing clause in one query leaks the corpus.

Creating the extension needs privilege. vector.control does not mark the extension as trusted, so on self-managed Postgres a superuser must run CREATE EXTENSION vector;. Managed services wrap this in an admin role such as rds_superuser or azure_pg_admin, and on Azure Flexible Server you must also allow-list VECTOR in the azure.extensions server parameter. The consequence for a platform is that extension creation should be part of database provisioning, performed by your automation, not a privilege handed to application teams. The extension is relocatable, so install it into a dedicated schema and control the search path:

CREATE SCHEMA extensions;
CREATE EXTENSION vector SCHEMA extensions;
ALTER DATABASE catalog SET search_path = public, extensions;

Without the schema on the search path, applications see type "vector" does not exist, which is a configuration error that looks like a missing install. With it, the standard extension hygiene against search-path hijacking applies.

Embeddings are sensitive data. Embeddings can leak information about the source text, and inversion attacks that reconstruct approximate text from vectors are a studied area. The defensible position is to classify embedding columns at the same sensitivity as the source documents, and to carry that classification to everything that copies them: backups, replicas, analytics exports, and the logs and query-sample stores from the recall monitoring above. For employers in the Gulf and Egypt this connects directly to data-residency commitments. If the documents must stay in-region, so must their vectors, their snapshots and any cross-region replica.

Secrets. pgvector has none of its own. The secrets in the picture are the database credentials and the embedding provider's API keys. Keep them out of code and images, and use environment injection from a secret manager or IAM database authentication where the provider supports it; see /student-guides/vault for the pattern. Embedding API keys belong to the application or ingestion pipeline, never to the database, which should have no reason to call out to a model provider.

The supply chain. The extension is compiled C code that runs inside the Postgres process with the privileges of the Postgres user, so the provenance of the binary matters. Know which route you use and pin it: the PGDG packages, a managed provider's built-in version, or the pgvector/pgvector Docker image. For Docker, tags follow pg{13..18}-{trixie|bookworm} and pinned forms such as 0.8.6-pg18-trixie; the short tags pg18 and 0.8.6-pg18 point to bookworm, and there is no latest tag. The old ankane/pgvector image is the legacy name, and any manifest still referencing it is a finding. Pin by exact tag, ideally by digest, scan images with the approach in /student-guides/trivy, and update on a schedule driven by the changelog rather than by chance. Building from source with the default flags can use -march=native, which produces an Illegal instruction crash on a different CPU; for a fleet, build with make OPTFLAGS="", as the official Dockerfile does.

Keep patched: 0.8.2 fixed a buffer overflow with parallel HNSW builds and 0.8.6 fixed an IVFFlat build overflow on 32-bit systems. State a minimum version in policy, and I would set it at 0.8.3 or later because of the vacuum corruption fix.

Multi-tenancy

When several teams or customers share a pgvector platform, the question is what they share and what they must not. An ANN index is one structure. In a shared HNSW graph, one tenant's vectors become candidates for another tenant's queries, so a very large tenant dilutes the candidate lists of small ones, and a filter that is selective for a small tenant returns few rows under post-filtering.

The options, from weakest to strongest isolation:

1. Shared table and index, filtered by a tenant column. Cheapest. It needs iterative scans to avoid thin results for small tenants, gives no performance isolation, and relies on filter correctness. Pair it with row-level security so the database enforces the filter.
2. Shared table with a B-tree on the tenant column and exact search. For many small tenants this is often right: each slice is small enough to scan exactly, recall is perfect, and there is no ANN index to maintain.
3. Partial indexes per large tenant. CREATE INDEX ... USING hnsw (...) WHERE (customer_id = 123) gives each big tenant its own graph in one table. Queries must include the matching predicate for the planner to use it.
4. List partitioning by tenant. PARTITION BY LIST(customer_id) gives each partition its own index and lets the planner prune. The README notes a partitioned table can have thousands of partitions, though planning and maintenance overhead grows with the count.
5. Separate tables, databases or instances. The strongest isolation at the highest operating cost, for contractual isolation, residency or noisy workloads.

CREATE TABLE items (
    customer_id int NOT NULL,
    id bigint NOT NULL,
    embedding vector(1536),
    PRIMARY KEY (customer_id, id)
) PARTITION BY LIST (customer_id);

CREATE TABLE items_c123 PARTITION OF items FOR VALUES IN (123);
CREATE INDEX ON items_c123 USING hnsw (embedding vector_cosine_ops);

ALTER TABLE items ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON items
    USING (customer_id = current_setting('app.customer_id')::int);

Row-level security enforces the boundary in the database, which is its value, but policies are extra filters and interact with approximate scans in the same post-filtering way as any predicate. Test that ANN queries still use the index and return enough rows under the policy, and test with a non-owner role, because table owners bypass policies unless the table forces them.

A platform with five very large tenants and a long tail of tiny ones usually ends with a hybrid: partitions or partial indexes for the large, B-tree plus exact search for the tail, and a written, automated rule for promoting a tenant between tiers. Promotion is a build plus a cutover, not a setting.

Noisy neighbours need a mechanism the extension does not provide. Use what Postgres has: statement_timeout per role, connection limits, and a dedicated replica for the heaviest tenant's ANN traffic. A lower per-role hnsw.ef_search is also a coarse throttle, since each query does less work.

Cost, replicas and consolidation

pgvector is free under the PostgreSQL License. You pay for Postgres compute, memory and storage, and memory is dominated by the HNSW working set, so frame the leadership conversation around that.

Consolidation has a real case: no second system to run, secure and back up, and transactional consistency between vectors and relational data. It has a real counter-case: vector workloads can starve OLTP, and because both share an instance the failure is hard to attribute. Consolidation is the default for moderate scale and write rates; separation should be justified by measurement.

Cost levers, roughly by savings per unit of risk:

• Index only the metric you query. An index serves only the operator matching its opclass, so three indexes for three distances means three graphs for one in use. Check pg_stat_user_indexes and drop the idle ones.
• Half-precision storage or index expressions, after measuring recall.
• Binary quantization with re-ranking for very large corpora.
• Hot-standby replicas for heavy ANN reads. Replicas carry the indexes because pgvector is WAL-based. Size them for the index, and install the binary there too.
• Builds off peak, and where possible off the primary.

Replicas have two catches. Long read queries on a standby can be cancelled by recovery conflicts when replay must remove rows they depend on, so set conflict handling deliberately and keep ANN queries short. And bulk re-embedding or a build on the primary produces WAL every replica must replay, so replica lag is the metric to watch; a lagging search replica returns stale results.

Beyond one machine, the documented options are vertical scaling, read replicas, and sharding with Citus, PgDog or application-level sharding. Sharding changes ANN queries: each shard returns its own top k and a coordinator merges and re-ranks, adding latency and work. Do it after measurement shows a large replica and a smaller working set are exhausted. A non-partitioned table is limited to 32 TB by default, rarely the first limit you meet.

Upgrades and migrations

Four different upgrades hide under the word, each with its own risk.

Extension upgrade. Install new binaries by the same method as the original install, run ALTER EXTENSION vector UPDATE; in each database that uses the extension, and verify with SELECT extversion FROM pg_extension WHERE extname='vector';. Upgrade scripts are chained (for example vector--0.8.5--0.8.6.sql), so multi-version jumps work. Two traps: binary and SQL objects update separately, so a database can run new binaries against old SQL until you run the update; and the update is per database, so twenty databases need twenty updates. Automate both and report version per database. The documentation records that nothing in SQL has been deprecated or removed since 0.5.0, so the SQL surface is low risk; the risk lives in the build and operational changes in the release notes.

Postgres major upgrade. pg_upgrade and provider major upgrades need pgvector installed for the new major first. Support has been dropped before: Postgres 12 in 0.8.0 and 11 in 0.6.0, while Postgres 18 support arrived in 0.8.1. Confirm the release supports the target, install it, upgrade, then run the extension update. Rehearse on a restored copy, since moving large indexes takes time.

Managed-service upgrades. The version depends on the provider and your Postgres minor version. Check SELECT * FROM pg_available_extension_versions WHERE name = 'vector'; and treat the provider's schedule as a constraint on your version floor. If a provider cannot offer 0.8.3 or later, record it on the risk register. Provider steps are outside pgvector's own documentation.

Embedding-model migration. The one platform owners underestimate most. A vector(n) column rejects other lengths with expected 3 dimensions, not 4, and mixing lengths in a distance fails with different vector dimensions 3 and 4. Vectors from different models are not comparable even when lengths match, so they cannot share an index. The pattern:

1. Add a new column or table for the new model with its own dimension.
2. Backfill in batches (binary COPY for volume), then build the index after the load.
3. Dual-write new rows to both.
4. Compare old and new recall on sampled queries and shadow-read.
5. Switch reads, keep the old column for a rollback window, then drop it and its index.

For many models, the README documents an untyped vector column with expression and partial indexes keyed by model_id. It is flexible and easy to get wrong, so ship it as a tested template.

Client library migration. pgvector-python 0.5.0 (July 2026) is breaking. Vector columns return a Python list in Django, SQLAlchemy, SQLModel and Peewee, and a pgvector.Vector object in Psycopg 3, Psycopg 2, asyncpg and pg8000, so code expecting NumPy arrays must call .to_numpy() or .to_list(). The pgvector.utils package and re-exported classes are gone, so import from the top level (from pgvector import Vector). NumPy is no longer a dependency, and Python 3.10 or later and SQLAlchemy 2 or later are required. A shared client wrapper is the place to absorb this once.

Governance and incident playbooks

Governance for an extension is a small set of automatically enforced rules.

• A minimum extension version, checked in provisioning and in CI (the pgvector/setup-pgvector GitHub Action installs it in CI).
• Every embedding column declares its dimension, except in a documented multi-model design.
• Every ANN index is created CONCURRENTLY through the migration tool, with a named owner and a recall check.
• Filtered-search templates carry iterative-scan settings or a documented reason not to.
• Embedding tables are classified for sensitivity, including backups and replicas.
• No indexes for distance metrics nobody queries.

The incident format that works is symptom, first check, likely causes, action. These are the ones I would write first.

Fewer rows than requested, no error. Check selectivity of the filter and whether hnsw.iterative_scan is set for the role. Causes: post-filtering against ef_search, dead tuples in HNSW, or an IVFFlat index built on too little data. Actions: enable iterative scans, raise ef_search, reindex, or rebuild IVFFlat after loading.

Slow after a deploy. Run EXPLAIN (ANALYZE, BUFFERS). The index serves only ORDER BY <distance expression> ASC LIMIT n, so a change to ORDER BY 1 - (a <=> b) DESC silently becomes a sequential scan. Other causes: an operator that no longer matches the opclass, a missing LIMIT, or a base-column query when only an expression index exists. SET LOCAL enable_seqscan = off shows whether an index plan is possible.

Latency spike, no query change. Compare index size with cache. The working set outgrew memory or another workload evicted it. Move reads to a larger replica, shrink the index, or add memory.

Build failed or stalled. Read the notices. hnsw graph no longer fits into maintenance_work_mem explains slowness; could not resize shared memory segment in a container means /dev/shm is too small; memory required is X MB, maintenance_work_mem is Y MB is an IVFFlat memory limit. Check pg_stat_progress_create_index, then look for an invalid index.

Recall dropped. Ask what changed: reindex parameters, IVFFlat drift, a new filter pattern, dead tuples. Confirm against exact search on sampled queries.

Crash during or after an index operation. Read the Postgres log and the extension version. If it is below the patched floor, the known fixes are the first hypothesis: parallel HNSW build overflow (0.8.2), vacuum corruption (0.8.3). Plan an upgrade and reindex.

Each incident ends with a lint, template or default change. An incident that ends with "be careful" is not fixed.

Where pgvector stops

Knowing the limits matters as much as knowing the features. pgvector is a poor answer when:

• The vector workload dominates the instance. Hundreds of millions of vectors under heavy traffic push the working set past what one node and its replicas serve comfortably, and shared fate with OLTP becomes unacceptable.
• Write throughput into the index is very high. HNSW inserts cost more than B-tree inserts.
• You need capabilities outside the extension, such as managed horizontal scaling with automatic rebalancing. Dedicated stores such as /student-guides/qdrant, /student-guides/pinecone, /student-guides/weaviate and /student-guides/milvus target that.
• Dimensions or sparsity exceed index limits and half precision, binary quantization and subvector indexing lose too much recall.
• The need is offline or in-process. A library such as /student-guides/faiss carries no database overhead.

Practitioners also compare pgvector with pgvectorscale and VectorChord, separate projects with their own maintainers and trade-offs. Evaluate them on your data and do not assume pgvector's guarantees carry over.

Equally, do not leave because a blog post said to. The triggers should be measured: recall below target after tuning and quantization, p99 out of budget with the index cached, transactional latency harmed even on a replica, or an instance cost above a dedicated service plus its operations. If the answers are no, the simplest system wins, and that is usually the Postgres you already run.

When you migrate, use the embedding-model pattern: dual-write, shadow-read, compare recall, cut over. Keep a thin retrieval interface so the store can change. Frameworks such as /student-guides/langchain and /student-guides/llamaindex have pgvector integrations, but they hide the filter, iterative-scan and index details discussed here, so keep the platform's own interface explicit.

Running pgvector as a platform for other teams

Everything above becomes a platform when packaged so teams get good defaults without learning the failure modes.

Offer a paved path. A new team receives a database with the extension in a dedicated schema at the pinned version, least-privilege roles, recommended per-role GUCs, TLS enforced, backups configured and dashboards wired, in minutes and without a ticket thread.

Publish templates, not only documents. For each storage profile give the table DDL, index DDL, query SQL, a client snippet and measured recall and latency on a reference dataset. Templates stop expression-index mismatches and ordering mistakes at the source. A Python template in the 0.5.0 style:

from pgvector import Vector
from pgvector.psycopg import register_vector
import psycopg

with psycopg.connect(DSN, autocommit=True) as conn:
    conn.execute("CREATE EXTENSION IF NOT EXISTS vector")
    register_vector(conn)
    with conn.transaction():
        conn.execute("SET LOCAL hnsw.ef_search = 100")
        rows = conn.execute(
            "SELECT id FROM items ORDER BY embedding <=> %s LIMIT 10",
            (Vector(query_embedding),),
        ).fetchall()

CREATE EXTENSION must run before register_vector, which looks up type OIDs. For pools, register in the pool's configure hook.

Instrument by default. Latency percentiles from pg_stat_statements, index size, index scan counts, build progress, replica lag and the sampled recall metric. Dashboards in /student-guides/grafana and traces in /student-guides/opentelemetry show retrieval inside an application trace, where teams first notice slow retrieval. Answer-quality tools such as /student-guides/ragas complement index recall monitoring and do not replace it.

Define the contract. Say what you promise (version floor, backup and restore, patch turnaround, supported profiles) and what teams own (schema, queries, model choice, re-embedding cost). State limits as limits, including no ad hoc index creation on production tables outside the migration path.

Manage change openly. Follow the pgvector releases, triage each for security and operational impact, and set an adoption deadline for fixes that matter. Test upgrades at production index sizes.

The review checklist

Use this on design documents and pull requests that touch vector data.

Schema and indexing

• Is the dimension declared and does it match the model?
• Does the opclass match the operator in the queries, with one index per distance actually used?
• Is the query ORDER BY <distance expression> ASC LIMIT n, with the same expression as any expression index?
• Are dimensions within limits (2,000 for vector, 4,000 for halfvec, 1,000 non-zero for HNSW sparsevec), or is a reduction documented?
• Are NULL and zero vectors handled, given they are not indexed?

Search quality

• Is there a recall target measured against exact search on sampled queries?
• How do filters interact with the index: B-tree, iterative scan, partial index or partition?
• Are ef_search and iterative-scan settings applied per role or with SET LOCAL, given any pooler?

Operations

• Are builds done after load, concurrently, with enough maintenance_work_mem and shared memory?
• Is there a reindex schedule for tables with deletes and updates?
• Is the version at or above the floor on every node, replicas included?
• Does a restore test cover indexes and the extension?

Security and cost

• Who can read the embeddings, and is row-level security or partitioning used for tenants?
• Is the extension in a dedicated schema with pinned image tags?
• Do backups, replicas and exports carry the source data's classification?
• What is the memory cost of the working set, and is it budgeted?

If a design cannot answer these in a sentence each, it is not finished.

The complete picture

The three levels tell one story at three depths. At the beginner level pgvector is a column type and an operator. At the mid level it is an index choice and tuning knobs, with the lesson that approximate search trades recall for speed and that filters interact with it. At the senior level it is a shared piece of a database that others depend on, so its failures are architectural: working sets that outgrow memory, builds that starve production, filters that silently shrink results, versions behind security fixes, and tenants that affect each other.

The last skill is restraint. The best platform has the fewest moving parts that meet the targets, and you leave it only when the numbers say so.

Where the series leaves you

You can now own pgvector for a team: choose between exact search, HNSW and IVFFlat with reasons; size and shrink the working set; build, vacuum and upgrade indexes without incidents; set filters and iterative scans as policy; isolate tenants at the right level; patch the extension as part of the database supply chain; and recognise when a workload has outgrown Postgres.

Practical next step: write the recall monitor and run it for a month before trusting any tuning, then rehearse an extension upgrade and an embedding-model migration on a copy of production data. To compare designs, read /student-guides/qdrant; for the infrastructure under the database, /student-guides/kubernetes and /student-guides/terraform.

Sources

• pgvector README at v0.8.6
• pgvector repository
• pgvector CHANGELOG
• pgvector releases and tags
• Extension SQL: types, opclasses and casts
• Control file
• HNSW settings and limits (hnsw.c)
• IVFFlat settings and limits (ivfflat.c)
• Official Dockerfile
• pgvector image on Docker Hub
• Hosted providers list (issue 54)
• Parallel HNSW build overflow (issue 959)
• IVFFlat 32-bit overflow (issue 1006)
• setup-pgvector GitHub Action
• pgvector-python README
• pgvector-python CHANGELOG
• PostgreSQL: CREATE INDEX progress reporting
• PostgreSQL: table partitioning
• PostgreSQL: partial indexes
• PostgreSQL: CTE materialization
• PostgreSQL: pg_stat_statements
• PostgreSQL: TOAST storage

Senior pgvector interviews rarely ask you to recite syntax. They hand you a platform, a failure or a budget conversation and watch how you reason. This file is organised the way those interviews run: a short orientation, system-design prompts, incident questions, trade-off arguments for leadership, and a closing self-test. Facts are checked against pgvector 0.8.6 (released 29 July 2026).

How to use this file

For each prompt, say your answer out loud before reading the model answer. Interviewers at this level score reasoning, so what matters is the order in which you ask questions and what you refuse to assume. A strong answer states the numbers it needs (corpus size, write rate, filter selectivity, recall target, latency budget), commits to a design, names the failure modes of that design, and says how you would measure that it works. A weak answer names an index type and stops.

Three habits separate strong from weak answers throughout. First, anchor on the version: many popular answers describe 0.5 to 0.7 behaviour, and iterative scans (0.8.0) and the corruption and overflow fixes (0.8.2, 0.8.3) change what is good advice. Second, treat recall as something you measure against exact search, never something you assume. Third, say what you would not use pgvector for.

The thirty-second answer at senior level

"pgvector is a Postgres extension that adds vector types, distance operators and two approximate index types, HNSW and IVFFlat. Its strength is that vectors live in ordinary rows, so transactions, WAL replication, backups, joins and row-level security all apply. Its cost is shared fate: the index competes for the same memory, CPU and vacuum as everything else, so as a platform owner I manage the working set, the build and upgrade operations, filter behaviour, tenancy and the version floor, and I measure recall against exact search."

If you can say that and then defend each clause, you have the shape of every answer below.

System design: a multi-tenant retrieval platform

Prompt. "Design retrieval storage for a SaaS product with 2,000 customers. Most have fewer than 50,000 documents; five have more than 20 million. Queries are always scoped to one customer. Embeddings are 1,536 dimensions. How do you build it on pgvector?"

Model answer. Start by asking about recall target, latency budget, write rate and whether customers have isolation or residency requirements, since those decide the tier. Then separate the population. For the long tail, a shared table with a B-tree on customer_id and exact search is likely best: each customer's slice is small enough to scan exactly, recall is perfect and there is no ANN index to maintain. For the five large customers, give each its own index, either as a partial index (WHERE customer_id = ...) or as a list partition, because a shared graph lets a large tenant dilute the candidate lists of the small ones. I would pick partitions if the large tenants number in the tens, since each partition has its own index and the planner prunes. I would enforce the boundary with row-level security rather than relying on every query to include the filter, and test it with a non-owner role.

Next, the vector storage. 1,536 dimensions is under the 2,000 limit for indexing vector, so full precision works. At 20 million rows the heap is roughly 120 GB per large tenant (about 6 KB per vector), so I would evaluate half-precision storage or a half-precision expression index to halve the index working set, measuring recall first. The query template must repeat the expression exactly, or the planner will not use the index.

Then operations. Builds use CREATE INDEX CONCURRENTLY after the initial load, with raised maintenance_work_mem and parallel workers; in containers --shm-size (or an in-memory emptyDir at /dev/shm in Kubernetes) must be at least maintenance_work_mem. Tier promotion, moving a growing customer from the shared table to its own partition, is a documented, automated procedure. For search traffic I would send heavy ANN reads to a hot standby sized for the index. Monitoring covers recall against exact search on sampled queries, p99 latency, index size versus cache, replica lag and build progress.

What a strong answer covers that a weak one misses: a different design for different tenant sizes, the post-filtering problem for small tenants in a shared index, enforcement of isolation in the database, the expression-index query rule, and a promotion path between tiers. A weak answer proposes one HNSW index on one table and filters by customer_id, then is surprised when small customers see thin results.

System design: a RAG platform that other teams adopt

Prompt. "Your company wants every team building RAG applications to use a shared Postgres platform. What do you build so the first team is productive in a day and the fiftieth does not cause an outage?"

Model answer. I would treat it as a product with a paved path and a contract. The paved path is a provisioning workflow that creates a database with the extension installed by automation (it is not a trusted extension, so application teams should not need superuser) into a dedicated schema, at a pinned version, with roles following least privilege, TLS enforced, backups on, and recommended per-role settings such as hnsw.iterative_scan = relaxed_order and a sensible hnsw.ef_search. Because poolers in transaction mode do not carry session settings, defaults go in ALTER ROLE, and per-query overrides use SET LOCAL.

On top of that, I publish storage profiles. Each profile has table DDL, index DDL, a query template, a client snippet and measured recall and latency on a reference dataset. Typical profiles are full precision with HNSW cosine, half-precision index for larger or higher-dimension models, and binary quantization with re-rank for very large corpora. Templates prevent the common mistakes: mismatched operator and opclass, sorting by a computed similarity so the index is not used, and a missing LIMIT.

For safety rails, a CI check enforces the version floor (I would set it at 0.8.3 or later) and that ANN indexes are created concurrently. Quotas and role limits (statement_timeout, connection limits) contain noisy neighbours. Observability is on by default: statement statistics, index sizes, scan counts, replica lag and a sampled recall metric per team. The contract states what the platform promises (patching, backup, restore) and what teams own (their schema, queries, embedding model and re-embedding cost).

Finally, I would keep an exit in mind: a thin retrieval interface in the shared client library so a team that outgrows the platform can migrate with dual-write and shadow-read rather than a rewrite.

System design: moving to a new embedding model

Prompt. "A team wants to switch embedding models. The new one has 3,072 dimensions. The table has 80 million rows and serves production traffic. Walk me through it."

Model answer. First, the dimension problem. A vector can be stored up to 16,000 dimensions but indexed only to 2,000, so 3,072 dimensions will fail with column cannot have more than 2000 dimensions for hnsw index. Options: ask whether the model supports reduced dimensions, index at half precision through an expression (halfvec indexes up to 4,000 dimensions), use binary quantization with re-ranking, or use a subvector index if the model is trained for it. I would test the candidates against a recall target on sampled queries before choosing.

Second, the migration. Vectors from different models are not comparable even if lengths match, and vector(n) rejects other lengths. So: add a new column or table with the new dimension, backfill in batches (binary COPY for volume), build the index after the load with a raised maintenance_work_mem, dual-write new rows, compare old and new recall and latency in shadow reads, switch reads, hold the old column through a rollback window, then drop it. Plan disk for two copies of data and two indexes, and watch replica lag during the backfill because the WAL volume is large. Tell the application owners that re-embedding has an API cost, since the pipeline calls the embedding provider, not the database.

Follow-up an interviewer may add: "What if you cannot afford two copies?" A strong answer partitions the table so migration proceeds partition by partition, and accepts a longer window with a mixed state where each partition knows which model it uses (the documented untyped column with model_id and partial indexes supports this).

Incident: the search returns too few results

Prompt. "After enabling HNSW, a search endpoint that asks for 20 results returns 6 for some users. There are no errors. What do you do?"

Model answer. I would suspect post-filtering. With an approximate index, the WHERE filter is applied after the index returns candidates, and the candidate count is capped by hnsw.ef_search (default 40). If the filter matches 10% of rows, about 4 of 40 survive. So I would check the filter's selectivity for those users, run the query with EXPLAIN (ANALYZE, BUFFERS), and confirm whether iterative scans are on. The fix is, in order of preference: a B-tree on the filter column if the filter is selective, hnsw.iterative_scan = 'relaxed_order' if the filter matches a large share, a partial index if few distinct values, or partitioning if many. Raising ef_search is a blunt remedy. I would also check for dead tuples, which reduce HNSW results and call for a concurrent reindex and vacuum, and check the version, since pre-0.8.0 builds have no iterative scans.

If order matters, relaxed_order can return slightly out of order, so I would use the materialized CTE pattern with an outer sort. And I would add a recall and result-count check so the next regression is detected by monitoring, not by users.

Incident: latency doubled overnight

Prompt. "p99 for vector search doubled with no deploy. What do you check?"

Model answer. I look at what changed in the environment, since the code did not. First, the plan: did the query stop using the index? Run EXPLAIN (ANALYZE, BUFFERS). Second, the cache: compare the index size (pg_relation_size) with available memory, and look at buffer reads versus hits. A graph that stopped fitting in cache shows up as a latency jump rather than a gradual slope, which matches a sudden doubling. Third, competing load: another team's reporting query, a build, or a bulk load evicting pages or consuming CPU. Fourth, data change: a large insert or delete batch leaving dead tuples, or a distribution shift. Fifth, replica lag if reads go to a standby.

Mitigation depends on the cause: move ANN reads to a replica with more memory, shrink the index with a half-precision or quantized profile, throttle the competing job, or reindex concurrently. The follow-up is structural: alert on index-size-to-memory ratio so you see it coming.

Incident: the index build will not finish

Prompt. "An HNSW build on 40 million rows has run for ten hours. How do you diagnose it, and can you save it?"

Model answer. Check progress with pg_stat_progress_create_index, which shows blocks_done against blocks_total for HNSW. Read the server log for NOTICE: hnsw graph no longer fits into maintenance_work_mem after N tuples; if it appears early, the build has spilled and will be slow. The remedy is to stop and restart with a larger maintenance_work_mem, more max_parallel_maintenance_workers and, if needed, a higher max_parallel_workers, within the memory the instance actually has, remembering that other operations also claim memory. In a container, a parallel build can fail with could not resize shared memory segment ... No space left on device; the fix is --shm-size at least as large as maintenance_work_mem. I would also ask whether the build was done after the data load and whether m and ef_construction were raised above defaults, since both strongly affect build time. After a cancelled concurrent build, check for an invalid index and drop it.

Trade-offs: how to argue them to leadership

Leadership rarely wants index internals. They want a decision, a cost and a risk. The structure that works is: state the decision, give the number that justifies it, name the risk you are accepting, and say what would make you change your mind.

"Why not buy a dedicated vector database?" The honest answer is conditional. Today our corpus is X million vectors with Y writes per second, and a Postgres instance with the HNSW index cached meets the recall and latency target. Staying consolidated avoids a second system to secure, back up, monitor and staff, and keeps vectors transactionally consistent with the rest of our data. The risk is shared fate: heavy vector traffic can hurt transactional workloads, which we mitigate with a replica for ANN reads. We would revisit if recall falls below target after tuning, p99 exceeds budget with the index cached, or the instance needed to hold the index costs more than a dedicated service plus its operations. Put those thresholds on a slide so the trigger is objective.

"Can we skip recall monitoring?" No, and the argument is about silent failure. Approximate search degrades without errors: dead tuples, drift under IVFFlat and new filter patterns all reduce recall quietly. The monitor is a scheduled comparison against exact search on sampled queries, cheap to run, and it converts an invisible risk into an alert.

"Why pin the version and patch on a schedule?" The extension is C code in the Postgres process. Real fixes exist for a buffer overflow in parallel HNSW builds (0.8.2) and for index corruption in HNSW vacuuming (0.8.3). The cost of the policy is a few engineer-days per quarter; the cost of an unpatched corruption bug is a rebuild measured in hours of downtime.

"Why not let every team pick its own settings?" Because the failure modes are subtle and shared. Storage profiles and templates give teams speed with guardrails, and the platform team stays accountable for what runs on shared hardware.

HNSW versus IVFFlat, defended in one minute. HNSW has the better speed and recall tradeoff and needs no training step, so it works on an empty table and as data grows, at the cost of slower builds and more memory. IVFFlat builds faster and uses less memory, but its centroids are fixed at build time, so it must be built on representative data and rebuilt when the distribution drifts. I default to HNSW and choose IVFFlat when build time or memory is the binding constraint and I can manage the rebuild schedule.

Rapid-fire senior questions

What does <#> return, and why? The negative inner product, because Postgres index scans only support ascending order on operators. Multiply by -1 for the inner product.

When does the planner not use the ANN index? With no LIMIT, with descending order or a computed ordering expression such as 1 - (a <=> b), when the operator does not match the opclass, on small tables, and when the query uses the base column but only an expression index exists.

What are the indexing limits? vector 2,000 dimensions, halfvec 4,000, bit 64,000, and sparsevec 1,000 non-zero elements with HNSW only.

Why can a pooled connection lose hnsw.ef_search? Session settings do not follow clients through transaction-mode pooling. Use SET LOCAL in a transaction or ALTER ROLE ... SET.

What changed in pgvector-python 0.5.0? Vector columns return a Python list in Django, SQLAlchemy, SQLModel and Peewee and a pgvector.Vector in the psycopg and asyncpg drivers, pgvector.utils was removed, NumPy is no longer a dependency, and Python 3.10 or later is required.

How do you run exact search for a recall check? Inside a transaction, SET LOCAL enable_indexscan = off, run the query, and compare the result sets.

Where do embeddings sit in your data classification? At the same level as the source text, including in backups and replicas, because embeddings can leak information about it.

Sixty-second self-test

Answer each without looking back.

1. Name three things a pgvector index inherits from Postgres and one cost of that.
2. Why does a filtered HNSW query return fewer rows than LIMIT, and what are the three fixes in order?
3. Which setting caps rows returned per HNSW scan, and what is its default?
4. What does the notice hnsw graph no longer fits into maintenance_work_mem tell you?
5. Give the two steps of an extension upgrade, and say how often the second runs.
6. How would you index 3,072-dimension embeddings?
7. Why are vectors from two models not comparable, and what is the migration pattern?
8. Which versions fixed the parallel HNSW build overflow and the HNSW vacuum corruption?
9. How do you measure recall, and what do you alert on?
10. Name three signals that would make you leave pgvector, and say how you would migrate.

If any answer needed a lookup, return to the matching section of the detailed guide, and then rehearse it aloud once more.

These are the lessons that only appear once pgvector is shared, large and long-lived. Each trap is written as symptom, cause and fix, followed by habits for owning the platform and checklists you can paste into pull requests and design documents. Everything is checked against pgvector 0.8.6, released on 29 July 2026.

Production traps that only appear at scale

1. Everything is fast in staging, then latency falls off a cliff. Symptom: p99 doubles after a growth milestone with no code change. Cause: the HNSW graph stopped fitting in cache. Graph traversal is random access, so once hops become disk reads, latency jumps rather than creeping. Fix: compare pg_relation_size of the index with memory, shrink the working set (half-precision expression index, binary quantization with re-rank, partitioning), or move ANN reads to a replica sized for the index. Alert on the ratio of index size to memory so you see it coming.

2. A filtered query returns three rows instead of ten. Symptom: no error, short result sets, worst for small tenants. Cause: approximate indexes apply the WHERE filter after retrieving candidates, and the candidate count is capped by hnsw.ef_search, default 40. A filter matching 10% of rows leaves about four. Fix: a B-tree on the filter column when the filter is selective, hnsw.iterative_scan = 'relaxed_order' when it matches a large share, a partial index for few distinct values, partitioning for many.

3. The new index is never used. Symptom: a sequential scan after a deploy. Cause: the planner uses an ANN index only for ORDER BY <distance expression> ASC LIMIT n. ORDER BY 1 - (embedding <=> $1) DESC does not qualify, nor does a missing LIMIT, nor an operator that does not match the opclass (a <-> query against vector_cosine_ops), nor a query on the base column when only an expression index exists. Fix: sort by the distance expression ascending, compute similarity in the select list, and use EXPLAIN with SET LOCAL enable_seqscan = off to prove the index plan is possible.

4. The expression index and the query disagree by one character. Symptom: a half-precision or binary-quantized index exists but is ignored. Cause: the query must repeat the indexed expression exactly, including the cast and the dimension: embedding::halfvec(1536), not embedding::halfvec. Fix: ship query templates from the platform and test them with EXPLAIN in CI.

5. Settings vanish behind the pooler. Symptom: recall differs between two environments that look identical. Cause: a session-level SET hnsw.ef_search does not follow a client through a transaction-mode pooler, because consecutive transactions can land on different server connections. Fix: SET LOCAL inside an explicit transaction, or ALTER ROLE ... SET so every connection of that role gets the value.

6. An IVFFlat index decays quietly. Symptom: recall slowly worsens after a new data source or a model change. Cause: centroids are fixed at build time and do not adapt. A build on a nearly empty table is worse and prints ivfflat index created with little data with the detail that this will cause low recall. Fix: build after loading representative data, reindex after large distribution shifts, and monitor recall.

7. A build that should take an hour takes a day. Symptom: the notice hnsw graph no longer fits into maintenance_work_mem after N tuples. Cause: the graph spilled out of build memory. Fix: raise maintenance_work_mem within real RAM, then max_parallel_maintenance_workers (default 2), then max_parallel_workers (default 8). Leave m and ef_construction at defaults unless recall demands otherwise, since both strongly affect build time.

8. A parallel build dies in a container. Symptom: could not resize shared memory segment ... No space left on device. Cause: Docker's default /dev/shm is 64 MB. Fix: --shm-size at least as large as maintenance_work_mem; in Kubernetes mount an emptyDir with medium: Memory at /dev/shm.

9. Dead tuples erode HNSW results and vacuum is slow. Symptom: fewer rows over time on a table with deletes and updates. Cause: dead tuples stay in the graph until vacuumed, and HNSW vacuuming can be slow. Fix: a scheduled REINDEX INDEX CONCURRENTLY followed by VACUUM, with disk headroom for a second copy of the index. Running anything older than 0.8.3 here is running a known corruption bug.

10. Frequently updated metadata sits beside the embedding. Symptom: high insert cost and WAL volume with few vector changes. Cause: updating any column creates a new row version that needs an index entry, and HNSW inserts are more expensive than B-tree inserts. Fix: keep volatile attributes in a separate table joined by key, and keep the embedding table append-mostly.

11. Rows vanish from search for no visible reason. Symptom: some documents are never returned. Cause: NULL vectors are not indexed, and zero vectors are not indexed for cosine distance. Fix: validate embeddings at ingestion, reject NaN and infinite values, and alert on rows with a NULL embedding.

12. A replica cannot be promoted cleanly. Symptom: errors around a missing extension after failover. Cause: pgvector must be installed on every server, replicas included. Fix: provisioning enforces the same version on every node, and the check runs continuously.

13. A model change breaks a distance query. Symptom: different vector dimensions 3 and 4, or worse, no error because the new model has the same length. Cause: vectors from different models are not comparable, and a typed column only catches the length. Fix: record the model in the schema (a model_id column or a table per model) and migrate with dual-write and shadow-read.

14. A client upgrade breaks every service. Symptom: attribute errors on arrays, import errors for pgvector.utils. Cause: pgvector-python 0.5.0 returns list or pgvector.Vector instead of NumPy arrays, removed pgvector.utils and dropped the NumPy dependency. Fix: import from the top-level pgvector package, call .to_numpy() or .to_list() where needed, and absorb it once in the shared client wrapper.

15. A binary that works on your laptop crashes on the fleet. Symptom: Illegal instruction. Cause: a source build with -march=native run on a different CPU. Fix: make OPTFLAGS="", or use the published image.

Platform-ownership habits

Own a version floor and enforce it in code. Pick the minimum release (I would use 0.8.3 or later, because of the HNSW vacuum corruption fix and the earlier parallel build overflow fix) and make provisioning and CI fail below it. A recommendation in a wiki decays; a failing check does not. Report extversion per database, because the extension update runs per database and clusters drift.

Pin everything that can drift. Use exact image tags such as 0.8.6-pg18-trixie, not pg18 (which points to bookworm) and not a mutable tag. There is no latest tag, and the legacy ankane/pgvector name should not appear in any manifest. Scan the images, and read the changelog before each bump.

Install the extension through automation. The extension is not marked trusted, so creation needs a superuser or the managed provider's admin role, and on Azure Flexible Server VECTOR must be allow-listed in azure.extensions. Put it in a dedicated schema, set the search_path deliberately, and never hand extension creation to application teams.

Make recall a number on a dashboard. Compare approximate and exact results on sampled real queries, on a schedule, and alert on drops. Treat a change to index parameters, a reindex, or a new filter pattern as an event that triggers a re-measurement. If you cannot say what your recall is, you do not know whether the platform works.

Prefer exact search wherever it is fast enough. For tables of modest size behind a selective B-tree filter, an exact scan has perfect recall, no build, no vacuum cost and no drift. Add an ANN index only when measurement shows you need one.

Publish templates, not paragraphs. For each storage profile, ship DDL, query SQL and a client snippet with measured numbers, and test the templates in CI. Most expression-index and ordering mistakes disappear when teams copy a tested query.

Put policy in roles. Set hnsw.iterative_scan, hnsw.ef_search and statement timeouts per role with ALTER ROLE ... SET. The reserved hnsw and ivfflat prefixes mean a typo raises an error, which is a free guardrail.

Treat builds and migrations as change events. Each has an owner, a window, a rollback and a communication to the replica owners. Check disk, replica lag and invalid indexes before and after.

Classify embeddings with their source. They can leak information about the original text, so backups, replicas, analytics exports and sampled-query stores carry the same classification. For employers with data-residency commitments in the Gulf or Egypt, this includes the region of every copy.

Keep an exit. Hide the store behind a thin retrieval interface. When a workload outgrows Postgres, the migration pattern is dual-write, shadow-read, compare recall, cut over. You will be glad of the interface when the day comes, and you will have a calmer conversation with leadership.

Read the release notes as an operator. For each release ask three questions: does it fix something we have, does it change build or memory behaviour, and does it change Postgres version support (12 and 11 were dropped in 0.8.0 and 0.6.0; 18 arrived in 0.8.1)?

Reviewing a pull request that touches vector data

Paste this list into your review template and answer each line from the diff, not from the description.

Schema changes

• The new vector column declares its dimension, and the dimension matches the model that produces the data.
• A column storing more than 2,000 dimensions that must be indexed uses a half-precision or quantized strategy, with the expression written out.
• If an untyped vector column is used, partial indexes and expression casts are present and tested.
• sparsevec data stays within 1,000 non-zero elements if it will be indexed by HNSW.

Index changes

• CREATE INDEX CONCURRENTLY through the migration tool, never inside a transaction block that forbids it.
• The opclass matches the operator the queries use. No second index exists for a distance nobody queries.
• m and ef_construction are defaults, or a measured reason is recorded. ef_construction is at least twice m; otherwise Postgres raises ef_construction must be greater than or equal to 2 * m.
• The index is created after the bulk load, not before.
• The migration sets maintenance_work_mem and parallel workers deliberately, and the container has a matching --shm-size.

Query changes

• ORDER BY <distance expression> ASC LIMIT n, with the same expression as any expression index.
• Filters have a plan: B-tree, iterative scan, partial index or partition. The author can say what happens when the filter is unselective.
• Per-query overrides use SET LOCAL inside a transaction, given the pooler.
• Distance thresholds follow the materialized CTE pattern, with the threshold outside.
• Any relaxed_order consumer either tolerates slight disorder or re-sorts with the documented CTE.

Client code

• Imports come from the top-level pgvector package (from pgvector import Vector).
• register_vector runs after CREATE EXTENSION, and pools register the type in their configure hook.
• No code assumes NumPy arrays are returned.
• Secrets and connection strings come from the secret manager, not the repository.

Safety

• No new role has more access to embedding tables than it needs, and row-level security is tested with a non-owner role.
• The change does not introduce a new copy of embeddings (an export, a cache, a log) without the matching classification.

Reviewing a design document

Design documents deserve slower scrutiny, because the expensive mistakes are cheap to fix on paper.

Requirements. Does the document state corpus size now and in two years, write rate, query rate, latency budget, recall target, filter patterns and tenancy model? A design with no recall target cannot be judged.

Fit. Does it justify pgvector against exact search and against a dedicated store with numbers? Does it name the measurements that would trigger a change?

Capacity. Is the working set computed (bytes per vector, index size, memory needed to cache it) and compared with the proposed instance? Is there a plan for growth: a smaller profile, a replica, partitioning?

Quality. How is recall measured, on what queries, how often, and what threshold pages someone? How do filters interact with the index?

Operations. Who builds and rebuilds indexes, and when? How is vacuum handled for tables with deletes? What is the upgrade path for the extension, for Postgres, and for the embedding model? Is the version floor stated?

Failure. What happens when the index is dropped or invalid, when a replica lags, when the pooler resets settings, when an embedding provider is down during ingestion? Is the recall of the degraded mode acceptable?

Security and compliance. Who reads embeddings, how is tenant isolation enforced in the database, where do copies live, and does that satisfy residency requirements?

Cost. Is the cost expressed per million vectors and per query, and does it include replicas and build windows?

Exit. Is there a retrieval interface, and is there a stated migration pattern if the workload outgrows Postgres?

A document that answers these in a short paragraph each is ready. A document that is silent on recall, working set or upgrades is not.

Operational recipes

Check what is installed and available.

SELECT extversion FROM pg_extension WHERE extname = 'vector';
SELECT default_version, installed_version
FROM pg_available_extensions WHERE name = 'vector';
SELECT * FROM pg_available_extension_versions WHERE name = 'vector';

Upgrade the extension. Install the new binaries by the original method on every node, then run this in each database and verify:

ALTER EXTENSION vector UPDATE;
SELECT extversion FROM pg_extension WHERE extname = 'vector';

Measure recall for one query. Run both versions and compare the identifiers returned.

-- approximate (uses the index)
SELECT id FROM items ORDER BY embedding <=> $1 LIMIT 10;

-- exact
BEGIN;
SET LOCAL enable_indexscan = off;
SELECT id FROM items ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

Recall at 10 is the number of shared identifiers divided by ten. Average it over a sample.

Find out why the index is not used.

BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM items ORDER BY embedding <=> $1 LIMIT 10;
ROLLBACK;

If an index scan appears only with the sequential scan disabled, the planner judged it cheaper, usually on a small table. If none appears even then, look for the operator, ordering or expression mismatch.

Watch a build. Query pg_stat_progress_create_index, using blocks_done and blocks_total for HNSW, and tuples_done and tuples_total for IVFFlat, where the percentage is meaningful only in the loading phase.

Refresh a bloated HNSW index.

REINDEX INDEX CONCURRENTLY items_embedding_idx;
VACUUM items;

Check free disk first and check replica lag afterwards.

Set per-role policy.

ALTER ROLE search_service SET hnsw.iterative_scan = 'relaxed_order';
ALTER ROLE search_service SET hnsw.ef_search = 100;

Install into a dedicated schema.

CREATE SCHEMA extensions;
CREATE EXTENSION vector SCHEMA extensions;

Then set the search_path for the roles that need the types, or applications will report type "vector" does not exist.

Docker for a local or CI instance.

docker run -d --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 --shm-size=1g pgvector/pgvector:0.8.6-pg18-trixie

For related topics, see /student-guides/docker for container flags, /student-guides/kubernetes for volumes and probes, /student-guides/prometheus and /student-guides/grafana for monitoring, and /student-guides/vault for secrets.

A closing checklist for platform owners

• A version floor of 0.8.3 or later is enforced, and extversion is reported per database.
• Image tags are pinned, and no manifest refers to ankane/pgvector or a mutable tag.
• Extension creation is automated, in a dedicated schema.
• A recall monitor compares approximate and exact search on sampled queries, and it alerts.
• Index size versus memory is on a dashboard with an alert.
• Builds are concurrent, scheduled, and have runbooks, with matching shared memory in containers.
• Tables with deletes have a scheduled concurrent reindex.
• Filter strategy is documented per workload, and iterative-scan settings live in roles.
• Tenancy has defined tiers with an automated promotion rule.
• Embeddings are classified with their source, including backups and replicas.
• A thin retrieval interface and a rehearsed migration pattern exist.
• Every incident ends in a new check, template or default.