I've been in the vector database space for a while (primary author of txtai). I do think vector indexing in traditional databases with tools like pgvector is a good option.

txtai has long had SQLite + Faiss support to enable metadata filtering with vector search. That pattern can take you farther than you think.

The design decisions I've made is to make it easy to plug different backends in for metadata and vectors. For example, txtai supports storing both in Postgres (w/ pgvector). It also supports sqlite-vec and DuckDB.

I'm not sure there is a one-size-fits-all approach. Flexibility and options seems like a win to me. Different situations warrant different solutions.