EXCELLENT question. We tried about 5 different ways of retrieving data, and we found that what works best for us is a search-as-a-service. We use Azure AI Search, but there are lots of other ones out there, including Google Vertex AI Search, Algolia, Amazon CloudSearch, or ElasticSearch.

What tradeoffs? It is fast and accurate, but it does get expensive when you have over 50 million records.