Yeb - we use pgvector in production[0]. Great results. There are some secret sauce stuff, but basically - depending on your source material and your user use-case you have to do more than just call cosine similarity and call it a day. (Most people don't even know what's happening, just using langchain which has different implementations, but all essentially call cosine similarity on raw text).
In my opinion, until LLMs learn to say I don't know, this is the way to do it in accuracy-first tasks.