The index is a write-time cost: choosing HNSW or IVFFlat when rows arrive daily

A retrieval index is usually chosen on query speed: which structure answers fastest at acceptable recall. That question is incomplete as soon as the corpus stops being static. On one platform we publish eight SEO articles every day with zero manual intervention; on another, articles publish autonomously every day. Rows arrive on a schedule nobody attends, and the index has to absorb them on that same schedule.

The default has no build step

By default, pgvector performs exact nearest neighbour search, which provides perfect recall. There is no graph to construct and no structure that degrades as rows accumulate. Adding an index is precisely what trades some recall for speed. Starting without one gives you two things worth keeping: a correctness baseline to compare every later configuration against, and a write path whose cost does not change when the corpus grows.

HNSW buys query speed with build time and memory

An HNSW index creates a multilayer graph. It has better query performance than IVFFlat in terms of the speed-recall trade-off, but it has slower build times and uses more memory. On a corpus that grows daily, neither cost is one-off. The structure that wins on queries is the one that loses on construction, so which side dominates depends on how often you build, not on how fast you read.

Postgres already treats search as write-time work

Full-text search makes the same bargain explicit. Full-text indexing allows documents to be preprocessed and an index saved for later rapid searching, with tsvector storing the preprocessed document and tsquery representing the processed query. The operators that need no preparation, LIKE and ILIKE and the regex operators, lack many essential properties required by modern information systems. Preparation is the price of usable search, in either index family.

The largest retrieval gains are also upstream

Contextual retrieval reduces the number of failed retrievals by 49%, and by 67% when combined with reranking. The first figure comes from how chunks are prepared before anything is embedded or queried. That ordering matters for budgeting. The cheapest place to improve retrieval is often the document preparation step already running on every new row, not the index parameter tuned after complaints arrive.

What an unsupervised pipeline has to own

Zero manual intervention, and roughly zero hours of weekly content operations, are measured results, and they constrain the design. If index maintenance needs a person to notice slow queries, rebuild a graph or reclaim memory after a bulk insert, that number stops being zero. Either the build fits inside the same job that writes the rows, or exact search stays and its query cost is accepted.

Keep one lever on the read side

pgvector lets you specify the size of the dynamic candidate list for search, 40 by default. That is a read-side lever: it moves recall per query without changing what the writer paid. Keep it explicit in configuration rather than inherited silently, so a recall complaint has a cheap first response that does not require rebuilding an index at the moment someone notices the problem.

Takeaway

Price the index twice, once at query time and once at write time, then check which of the two numbers your schedule multiplies. For a corpus gaining rows every day with no supervision, exact search with perfect recall is a defensible production choice until measurement says otherwise. HNSW is a commitment to pay slower builds and more memory on every cycle, so make that commitment deliberately.

Sources

pgvector (GitHub) — Open-source vector similarity search for Postgres — https://github.com/pgvector/pgvector

Anthropic — Introducing Contextual Retrieval — https://www.anthropic.com/engineering/contextual-retrieval

PostgreSQL 18 Documentation — Full Text Search: 12.1. Introduction — https://www.postgresql.org/docs/current/textsearch-intro.html

Neurolinks case study — Four markets, one codebase — https://neurolinks.be/work/tatano-energy

Neurolinks case study — A personal brand that publishes itself — https://neurolinks.be/work/matthieu-pesesse-media