In case it wasn't clear, I'm the main author of txtai.
I use txtai in my everyday work. While txtai is open source, I also support a number of consulting projects that are txtai focused. In most cases, I've used SQLite + Faiss for the vector database config paired with an LLM for RAG support.
This setup has worked well and it's what I would start with until it didn't scale.
In this case, you get the robustness of Postgres (which has been around a while) and Faiss (which is one of the most mature vector indexes).
https://neuml.hashnode.dev/external-database-integration