An active-permits chatbot queries Azure Database for PostgreSQL mostly against non-archived rows. How can indexing cut latency?
Select an answer to reveal the explanation.
Short Explanation
Partial indexes are the express lane for ‘active only’—keep archived permits out of the hot index so the chatbot isn’t digging through closed folders every time.
Full Explanation
When an active-permits chatbot mostly queries non-archived rows in Azure Database for PostgreSQL, partial indexes that exclude archived permit rows keep hot-path indexes smaller and more cache-friendly. Queries that predicate on active status can use a leaner index, cutting latency for residents checking live permits without carrying the full historical archive in every index probe.
Indexing only archived historical rows and leaving active permits unindexed optimizes the cold set while punishing the chatbot’s actual workload. Dropping all indexes on permit status forces full scans across history for every active lookup. Duplicating the entire archive into every active query’s SELECT list inflates I/O and result payloads without helping selectivity.
Exam caveat: partial indexes help when the predicate in queries matches the partial index condition (for example status = active / not archived); mismatched predicates will not use the partial index. Operational check: create a partial index excluding archived rows, EXPLAIN the chatbot’s active-permits query to confirm index use, compare buffer hits and latency against a full-table index, and monitor bloat as permits archive over time.