pgvector's entire value proposition is not needing a separate vector database at all: it's a Postgres extension that adds a vector column type and similarity search operators directly into a database most teams are already running for everything else. For an application that doesn't need Pinecone or Qdrant's scale, that means one less piece of infrastructure to run, monitor, and keep in sync with the rest of the data.
It supports both exact and approximate nearest-neighbor search, with indexing methods that trade off accuracy against speed, and because it's just Postgres, it inherits all of Postgres's existing tooling: backups, replication, transactions, and the ability to join vector search results against ordinary relational data in a single query, something a dedicated vector database generally can't do as naturally.
Being free, open source, and usable anywhere Postgres already runs, including managed services like Supabase and most major cloud providers' Postgres offerings, it's become a very common default for teams adding RAG to an existing product without wanting to introduce and operate an entirely new category of database.