pg_similarity VS pgvector

For Postgres, there is an extension that provides that.

Note that pgvector isn't supported on any of the large cloud providers' hosted Postgres offerings, other than Supabase. https://github.com/pgvector/pgvector#hosted-postgres has instructions on how to add your voice to request it to be added!

(It does seem that the ancient https://github.com/eulerto/pg_similarity is supported by RDS and Google Cloud - but it's hard to tell whether attention was paid to its performance characteristics with nearly the rigor that pgvector seems to have been designed.)

While I don't doubt that you know your usecase and weighed/tried the option.

> Postgres search is essentially an easier to use regex engine.

I'm not sure exactly what you meant to convey here, but if you're searching with LIKE or `~` you're not doing Postgres's proper Full Text Search. You should be dealing with tsvectors[0]

> As soon as you need multiple languages

Postgres FTS supports multiple languages and you can create your own configurations[1]

> advanced autocomplete

I'm not sure what "advanced" autocomplete is but you can get pretty fast trigram searches going[2] (back to LIKE/ILIKE here but obviously this is an isolated usecase). In the end I'd expect auto complete results to actually not hit your DB most of the time (maybe I'm naive but that feels like a caching > cache invalidation > cache pushdown problem to me)

> misspelling detection

pg_similarity_extension[3] might be of some help here, but it may require some wrangling.

> large documents, large datasets,

PG has TOAST[4], and obviously can scale (maybe not necessarily great at it) -- see pg_partman/Timescale/Citus/etc.

> custom scoring

Postgres only has basic ranking features[5], but you can write your own functions and extend it of course.

Solr/ES are definitely the right tools for the job (tm) when the job is search, but you can get surprisingly far with Postgres. I'd argue that many usecases actually don't want/need a perfect full text search solution -- it's often minor features that turn into overkill fests and ops people learning/figuring out how to properly manage and scale an ES cluster and falling into pitfalls along the way.

[0]: https://www.postgresql.org/docs/current/textsearch-intro.htm...

[1]: https://www.postgresql.org/docs/current/textsearch-intro.htm...

[2]: https://about.gitlab.com/blog/2016/03/18/fast-search-using-p...

[3]: https://github.com/eulerto/pg_similarity

[4]: https://www.postgresql.org/docs/current/storage-toast.html

[5]: https://www.postgresql.org/docs/9.5/textsearch-controls.html...

# Install Postgres and pgvector !apt-get update && apt install postgresql postgresql-server-dev-14 !git clone --branch v0.6.2 https://github.com/pgvector/pgvector.git !cd pgvector && make && make install # Start database !service postgresql start !sudo -u postgres psql -U postgres -c "ALTER USER postgres PASSWORD 'pass';"

When talking about Vector Databases, in the market we can find the specialized ones and multi-model, most of the major database providers like Oracle, PostgreSQL or MongoDB, for mention some of them, have integrated a specific solution to retrieve vector data.

For this example, I wanted to focus on how pgvector  – an open-source vector similarity search for Postgres – can be used to identify data similarities that exist in enterprise data.

pgvector supports dense vector search well, but it does not have plan to support sparse vector.

There's an issue in the pgvector repo about someone having several ~10-20million row tables and getting acceptable performance with the right hardware and some performance tuning: https://github.com/pgvector/pgvector/issues/455

I'm in the early stages of evaluating pgvector myself. but having used pinecone I currently am liking pgvector better because of it being open source. The indexing algorithm is clear, one can understand and modify the parameters. Furthermore the database is postgresql, not a proprietary document store. When the other data in the problem is stored relationally, it is very convenient to have the vectors stored like this as well. And postgresql has good observability and metrics. I think when it comes to flexibility for specialized applications, pgvector seems like the clear winner. But I can definitely see pinecone's appeal if vector search is not a core component of the problem/business, as it is very easy to use and scales very easily

The Spring AI project aims to streamline the development of applications that incorporate artificial intelligence functionality without unnecessary complexity. On this example we use features like: Embedding, Prompts, ETL and save all embedding on PGvector(Postgres Vector database)

Official GitHub repository of the pgvector extension

pgvector 0.6.0 was just released and will be available on Supabase projects soon. Again, a special shout out to Andrew Kane and everyone else who worked on parallel index builds.

The pgvector extension adds vector similarity search capabilities to your PostgreSQL database. To use the extension, you have to first create it in your database. You can install the extension, by connecting to your database and running the CREATE EXTENSION command from the psql command prompt: