AI-200 practice: Implement indexing strategies, including optimizing query latency and reducing pgvector compute overhead

Question 1

A team is choosing a vector index for an embeddings table in Azure Database for PostgreSQL with pgvector. The table receives continuous inserts, and query-time recall and speed matter more than index build time or memory footprint.

Which index type and consideration is most appropriate?

Accepted Answer

An HNSW index, which typically offers better query recall and speed than IVFFlat, accepts incremental inserts, and avoids periodic full re-clustering as data grows

Answer

An IVFFlat index with a small lists parameter, because it rebuilds quickly after each insert batch and keeps memory usage lower than graph indexes

Answer

No vector index, relying on sequential scans of the embedding column, which remain efficient for any table size when the server has sufficient RAM

Answer

A B-tree index on the embedding column, because PostgreSQL default indexes support arbitrary column types and nearest-neighbor lookups efficiently

Question 2

You provision pgvector on Azure Database for PostgreSQL Flexible Server and create a table for embedding storage: ```sql CREATE EXTENSION IF NOT EXISTS vector; CREATE TABLE chunks ( id BIGSERIAL PRIMARY KEY, text TEXT, embedding VECTOR(1536) ); INSERT INTO chunks (text, embedding) VALUES ('AKS scaling guide', '[0.01, 0.02, ...]'); SELECT id, text, embedding <=> '[0.05, 0.01, ...]' AS distance FROM chunks ORDER BY embedding <=> '[0.05, 0.01, ...]' LIMIT 10; ``` Similarity queries work correctly but are slow as the table grows past 1 million rows. What is the most effective fix to reduce query latency at this scale?

Accepted Answer

Create an approximate nearest-neighbor index on the embedding column, e.g., CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops); (or ivfflat with an appropriate lists parameter), so the planner can use an index scan instead of a full sequential scan with exact distance computation on every row.

Answer

Add a B-tree index on embedding: CREATE INDEX ON chunks USING btree (embedding);.

Answer

Partition the table by id range into 10 partitions; pgvector automatically parallelizes similarity search across partitions with no index needed.

Answer

Change VECTOR(1536) to VECTOR (unbounded dimension) to allow the planner to use a more efficient generic comparison operator.

Question 3

You choose between hnsw and ivfflat index types for a pgvector table with 5 million embeddings that receives frequent new inserts throughout the day:
```sql
CREATE INDEX ON chunks USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);
```
After a large batch insert of 500,000 new rows, query recall noticeably degrades until the index is manually rebuilt with REINDEX. Which statement best explains this and the recommended fix?

Accepted Answer

ivfflat indexes cluster vectors into a fixed number of lists (partitions) determined at index build time; as new data is inserted, the cluster centroids no longer represent the updated data distribution well, degrading recall until a REINDEX. Switching to an hnsw index avoids this issue because HNSW graphs are incrementally updated with each insert and don't require periodic rebuilding to maintain recall.

Answer

This is expected and unavoidable with pgvector regardless of index type; all pgvector indexes require nightly REINDEX for correctness.

Answer

The fix is to increase lists to 100,000 so every future insert gets its own cluster, eliminating the need for REINDEX permanently.

Answer

Switching to a B-tree index resolves the recall degradation since B-tree indexes update incrementally with every insert by design.

Question 4

A vector similarity search on a pgvector column in Azure Database for PostgreSQL is slow at scale (millions of rows) despite low compute overhead concerns. What is the most effective optimization?

Accepted Answer

HNSW or IVFFlat index on the vector column

Answer

B-tree index created on the vector column

Answer

Raise fill factor on the table to one hundred

Answer

Convert the vector column into JSONB storage

Question 5

You must reduce pgvector compute overhead for a write-heavy PostgreSQL table where embeddings are inserted in large nightly batches, but query latency during the day is not critical. What indexing strategy minimizes overhead?

Accepted Answer

IVFFlat with modest lists; REINDEX after batches

Answer

HNSW at max ef_construction and m regardless

Answer

Skip vector indexes; always full sequential scans

Answer

Separate B-tree indexes on every table column

Question 6

You are implementing vector search in Azure Database for PostgreSQL. Which index type is best suited for approximate nearest neighbor (ANN) search on large datasets where low memory footprint during indexing is required?

Accepted Answer

ivfflat vector index

Answer

hnsw vector index

Answer

btree scalar index

Answer

gist spatial index

Question 7

You created `CREATE INDEX ON chunks USING hnsw (embedding vector_l2_ops);`, but the application ranks results with `ORDER BY embedding <=> $1 LIMIT 10`. `EXPLAIN` shows a sequential scan with a sort, and the HNSW index is never used. What is the fix?

Accepted Answer

Recreate the index with vector_cosine_ops so that it matches the <=> cosine distance operator

Answer

Run ANALYZE chunks so the planner discovers the index

Answer

SET enable_seqscan = off for the session

Answer

Increase hnsw.ef_search to 200

Question 8

An HNSW index (m = 16, ef_construction = 64) returns results quickly, but offline evaluation shows recall@10 of 0.89, and the target is 0.95. You want to raise recall without rebuilding the index. What should you change?

Accepted Answer

Raise hnsw.ef_search (default 40), for example SET hnsw.ef_search = 100, accepting somewhat higher latency

Answer

Increase m to 48

Answer

SET ivfflat.probes = 20

Answer

Increase maintenance_work_mem

Question 9

Building an HNSW index on 10 million embeddings takes many hours on a 16-vCore Memory Optimized flexible server, and a NOTICE says the graph no longer fits into maintenance_work_mem. You want a faster build without lowering index quality. Which two settings should you raise for the build session? Select two.

Accepted Answer

maintenance_work_mem, so that the graph fits in memory during the build

Accepted Answer

max_parallel_maintenance_workers, so that pgvector can build the index with parallel workers

Answer

hnsw.ef_search

Answer

effective_cache_size

Answer

work_mem

Question 10

A query runs `WHERE category = 'contracts' ORDER BY embedding <=> $1 LIMIT 10` against an HNSW index. Contracts are only 2% of rows, and the query often returns just 1 or 2 rows instead of 10. The server runs pgvector 0.8. What should you enable?

Accepted Answer

SET hnsw.iterative_scan = relaxed_order (or strict_order) so the index scan keeps searching until enough rows pass the filter

Answer

SET ivfflat.probes = 100

Answer

Raise LIMIT to 500 and keep the first 10 contract rows in the application

Answer

Drop the HNSW index so that PostgreSQL always runs an exact scan

11 questions