Specialized DB may be worthwhile
I used pinecone in a proof of concept embeddings search app & it was fine, but that was with a (relatively) small amount of vectors (all other data was held elsewhere in "main" DB, just using pinecone to store vector values and get best match vector results on vectors passed in, then would use returned vector to provide data relating to that vector from "main" DB).
To find similarities between AI embeddings / vectors you generally use cosine similarity.
The maths is not complex but its quite "expensive".
The challenge is not so much implementing the cosine similarity based search but doing it in a fast & efficient way at scale*
Because of that performance issue at scale on a "generalist" database a system specifically designed for efficient cosine similarity searches could well have a massive performance advantage once amount of data stored gets large and that performance advantage could definitely make it a worthwhile option.
* Easy maths - I could write a Cosine similarity select to run on a standard SQL Database, but I would not fancy its performance once it had to process a large number of vectors. But for completeness should reiterate that not tried Pinecone with the sort of massive dataset that would be a nightmare for a "generalist" DB, so just assuming it would perform well (as what's the point in building it otherwise!) it was just PoC, not a proper evaluation of Pinecone, mainly used Pinecone for the learning experience instead of rolling my own cos similarity search on SQL Server.