A municipal code-search service on Azure Database for PostgreSQL filters by foreign keys and ranks by vector similarity. How should indexes be designed?
Select an answer to reveal the explanation.
Short Explanation
Think two indexes for two jobs: B-tree gets you to the right code shelf by key, and the vector index finds the nearest meaning on that shelf for municipal code search.
Full Explanation
Municipal code-search on Azure Database for PostgreSQL that filters by foreign keys and ranks by vector similarity needs both B-tree indexes on relational foreign keys and vector indexes (such as IVFFlat or HNSW-style indexes via pgvector). B-tree indexes accelerate join and filter predicates; vector indexes accelerate approximate nearest-neighbor ranking. Together they support the filter-plus-similarity access pattern city legal and code assistants require.
Creating no indexes and always sequential-scanning every code table collapses under corpus growth. Indexing only vector columns and never indexing relational foreign keys used in filters leaves the predicate path expensive, so filtered semantic queries still scan heavily. Replacing vector indexes with full table locks during every resident query serializes load and destroys concurrency.
Exam caveat: associate items often test recognizing that hybrid queries need hybrid indexing—relational indexes plus vector indexes—not one or the other alone. Operational check: EXPLAIN a representative filter-plus-similarity query, confirm foreign-key predicates can use B-tree indexes and the similarity operator can use the vector index, then benchmark p95 latency on production-like code volumes.