Lesson 8. What a pgvector Index Costs to Keep

Mission link: This is stage 4's capstone: llm/rag derives why HNSW behaves the way it does and how to tune it for retrieval quality; this workspace's job is naming what that structure actually costs the Postgres instance operating it, day to day, not just at query time.
Primary source: Repo: pgvector, pgvector
Prerequisites: Lesson 7, Write-ahead log (WAL)

Warm-up

1. ▢ Why does adding an index to a table make every write to it more expensive, regardless of the index's type?

2. ▢ What trade-off does GIN's fastupdate/pending-list mechanism make, and what cleans up the pending list?

Know this

The index itself can be larger than the vectors it stores

An HNSW index doesn't just store a copy of the vectors it indexes; it stores the graph structure connecting them, edges linking each vector to a bounded number of neighbors across multiple layers. Unlike a B-tree index, which is typically much smaller than the table it indexes, an HNSW index's graph overhead can make the index substantially larger than the raw vector data alone, since every vector carries connections to potentially dozens of neighbors, all of which need to be stored and kept updated.

Build time is a real planning concern, not an afterthought

Building an HNSW index scales with the size of the data and grows further with the parameters chosen for it (larger m or ef_construction mean more, and more expensive, work per vector during the build). For a large table, this build can take a long time and requires a meaningful amount of maintenance_work_mem; too little configured for the build can force a much slower fallback path. Sizing this correctly, and planning for how long a rebuild will actually take, is an operational decision the same way lesson 7's index types each demanded their own maintenance planning.

It still interacts with vacuum, and the details have genuinely changed across versions

Deleted or updated rows still leave index entries that need reclaiming, the same underlying concern as every other index type this workspace has covered. How gracefully pgvector's HNSW implementation handles this has changed across its own version history; earlier versions handled update-heavy workloads on HNSW less gracefully than more recent ones. This is a case where checking the specific pgvector version's current release notes matters directly, rather than assuming behavior documented once stays fixed: the mechanics an operator needs to plan around here are exactly the kind of thing that changes between releases of an actively developed extension.

The cost that matters most in production: staying resident in memory

An HNSW index has to be resident in memory (Postgres's shared buffers, or read from disk otherwise, far slower) to answer queries at the speed it's built for. For a large vector table, the index alone can require a substantial fraction of the instance's available memory, competing directly with everything else that same Postgres instance is running: other tables' data, other indexes, WAL buffers, and any other workload sharing the server. This is the operational question the mission's success criterion is actually asking: not just what it costs to build a vector index once, but what it costs to keep it fast, continuously, alongside everything else the instance has to do.

Practice

1. ▢ Why can an HNSW index be substantially larger on disk than the raw vector data it indexes, unlike a typical B-tree index relative to its table?

2. ▢ What operational concern does HNSW's build time interact with, and why does maintenance_work_mem matter for it?

3. ▢ Why does a large HNSW index's need to stay memory-resident for fast queries create a real trade-off with everything else the same Postgres instance is running?

4. ▢ Why should an operator check the specific pgvector version's release notes rather than assume update and delete behavior on an HNSW index is fixed and documented once?

5. ▢ Which claim is true of what a pgvector HNSW index costs to keep?

   • a) Its disk footprint is always smaller than the raw vectors it indexes, the same as a typical B-tree
   • b) It costs disk space beyond the raw vectors (graph structure), a real build-time resource requirement, and a standing memory cost that competes with everything else the instance runs
   • c) Once built, an HNSW index requires no further interaction with vacuum
   • d) pgvector's update and delete handling for HNSW has been identical across every version of the extension

Real-world reps

• [ ] For a pgvector table you have access to, or a hypothetical one sized to a real workload, estimate the HNSW index's disk footprint relative to the raw vector data, using pgvector's documented parameters.
• [ ] Check what maintenance_work_mem is currently configured to on an instance you operate, and whether it's sized for building or rebuilding a vector index of the size you expect.
• [ ] Tomorrow: check the release notes for the pgvector version you're running (or planning to run) for anything about HNSW update or delete handling that's changed recently.

Going further

• Repo: pgvector, pgvector
• RAG: llm/rag's workspace, which derives why HNSW behaves the way it does and how to tune it for retrieval quality
• Resources

Not landing? Reread the primary source at the top, since this lesson compresses it and compression is where understanding leaks. Check the glossary for any term that felt slippery.

If the lesson itself is unclear rather than the material, that is a defect: open an issue.