Supabase Vector, the Open Source Vector Toolkit for Postgres | Hacker News Reader

Supabase Vector, the Open Source Vector Toolkit for Postgres

supabase.com

92 pointssamwillis13 comments

for those who jump straight to the comments (like me), this is a python for managing unstructured vectors/embeddings in Postgres using pgvector. It works with any Postgres/pgvector database

The core of the API is this:

    import vecs

    DB_CONNECTION = "postgresql://<user>:<password>@<host>:<port>/<db_name>"

    # create vector store client
    vx = vecs.create_client(DB_CONNECTION)

    # create a collection of vectors with 3 dimensions
    docs = vx.create_collection(name="docs", dimension=3)
    
    # Add embeddings
    docs.upsert(vectors=[("vec0", [0.1, 0.2, 0.3], {"year": 1973})])

    # Query embeddings
    docs.query(query_vector=[0.10,0.21,0.29], limit=1)

This stores all the data in a new `vecs` schema in your database. It took me a while to grasp the nature of structured (eg, manage tables/data with migrations) with unstructured/nosql, but the use cases for vectors are very often geared towards data scientists/engineers. There is some more info about the interop between these approaches here: https://supabase.com/docs/guides/ai/structured-unstructured

Anyone have any experience or knowledge of this VS pinecone?

we're[0] using pgvector via supabase in prod and we've had 0 issues. We store things like the natural language explanation of what a SQL statement does and use pgvector to look up the SQL based on a users new natural language question.

I think the argument against pgvector would be "it doesn't scale", but we're using it against hundreds of thousands of rows of data and the performance is solid (< 100ms). Maybe we'll run into issues with tens of millions of rows, but we'll worry about that when it actually becomes a problem.

0 - https://www.definite.app/

How do you prevent SQL injections or bad queries?

we only execute SELECT statements and the user we connect with has readonly access.

On "bad queries", we rely heavily on the users past SQL history to understand correct JOIN's and how they normally define metrics / use the database in general.

This may depend on if your use case is only vector search in isolation (ANN lookups). In this scenario pgvector is potentially not the best option (https://ann-benchmarks.com/)

That said, using pgvector (or using other SQL databases with vector search support.. many have this capability) will let you do both ANN and your usual SQL filtering and joining (or full text, or.. etc) to produce more hand tuned results to a query. This is something the specialized vector databases don't have much support for.

We embed Postgres and pgvector in Zep[0], a memory store for LLM apps. It’s rock solid, even with very wide vectors, and with the right indexes ANN is performant up to tens of millions of rows. Haven’t tested beyond this, though.

It’s super useful for hybrid search: vector similarity search over unstructured data joined with filtering by structured data.

One caveat: It’s important indexes are added after a fair number of vectors have been persisted, which can make some production use cases challenging to implement.

[0] https://github.com/getzep/zep

When you say you can do vss along with filtering. Does the filtering happen before or after the vss?

Logically at the same time in the sql query. This may not be how the query is executed by the database, though, and is up to the optimizer.

> It’s important indexes are added after a fair number of vectors have been persisted, which can make some production use cases challenging to implement.

managing indexes are the most challenging part of using pgvector right now. There is a bit of a "goldilocks zone" - you can't create the index with zero data, but if you create it too late then it can take a lot of memory. Andrew (and a few others, like AWS) are working hard on improving this, as well as researching other index types.

That’s good to know. I tested it in a docker container and indexed after loading ~1M vectors. Checking EXPLAIN showed it wasn’t using the index at all. I wonder if it’s because it couldn’t load into memory.

Without an index, I found it was too slow for data that size. I didn’t expect issues for data at this “small”, but it’s pretty new to me.

Zep seems really cool! I think hybrid search is a great paradigm and believe it will continue to grow. How do you guys plan on dealing with data deletion requests and other GDPR rules around storage?

Great question. We have a deletion endpoint that allows sessions (think users) to be deleted. All memory artifacts are timestamped, and we run soft-deletes with deleted_at timestamps. This allows enterprises to execute retention strategies (keep all records for X months) and hard deletes / expungement at scale set by policy (all soft deletes must be expunged within 48 hours of a request).

One of the benefits of using Postgres + pgvector, is it is much easier to delete records from a vector index versus using a vector database or Redis. While Redis has ttls for objects, there isn't an easy way to remove data from a vector index.

Reply on news.ycombinator.com