MVCC, WAL & pgvector — Series 2

Preview — 3 of 10 questions

For an HNSW index on a vector column, what do m and ef_construction control, and what is ef_search for?

javascript

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 64);
SET hnsw.ef_search = 40;

Am controls the maximum number of connections per node in the HNSW graph (higher = better recall, more memory/build time); ef_construction controls how thorough the search is while building the graph (higher = better index quality, slower build); ef_search (a runtime/session setting, not an index option) controls how thorough the search is at query time (higher = better recall, slower queries) — it can be tuned per-query without rebuilding the index

Bm and ef_construction are two names for the exact same parameter; only one needs to be set

Cef_search must be set at index creation time and cannot be changed afterward per query

DHigher m always makes queries faster with no trade-off in memory or build time

An index was created with vector_l2_ops (Euclidean distance), but the query orders by cosine distance (<=>). What happens?

javascript

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

SELECT * FROM items ORDER BY embedding <=> '[0.1,0.2,0.3]' LIMIT 5;

AThe index is used automatically regardless of operator class, since PostgreSQL converts between distance metrics on the fly

BThe index cannot be used for this query — it was built for L2/Euclidean distance (<->), not cosine distance (<=>); the planner falls back to a full sequential scan computing cosine distance for every row and sorting, silently losing the performance benefit the index was meant to provide; a separate index built WITH vector_cosine_ops is needed for <=> queries

CPostgreSQL raises a hard error and refuses to run the query at all

DThe query returns results ordered by L2 distance instead, silently ignoring the requested cosine distance

What does marking a function IMMUTABLE (vs the default VOLATILE) tell the PostgreSQL planner?

javascript

CREATE FUNCTION full_name(first text, last text) RETURNS text
AS $$ SELECT first || ' ' || last $$
LANGUAGE sql IMMUTABLE;

AIMMUTABLE functions run faster because they're automatically compiled to native machine code

BIMMUTABLE functions cannot accept any TEXT arguments, only numeric types

CIMMUTABLE promises the function will always return the same result for the same arguments, with no dependency on database state or side effects — this allows the planner to safely cache/reuse results, use the function in an expression index, and fold constant expressions at plan time; VOLATILE (the default) makes none of these promises and must be re-evaluated for every row every time

DIMMUTABLE is purely documentation with zero effect on how the planner treats the function

Answer all 10 questions (7 more), see explanations for every answer, and track your score.