6 ms·

Supabase Vector, the Open Source Vector Toolkit for Postgres

92 points by samwillis 3y ago

jonplackett 3y ago
Anyone have any experience or knowledge of this VS pinecone?
- mritchie712 3y ago
  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/ https://www.definite.app/
  - aligajani 3y ago
    How do you prevent SQL injections or bad queries?
    - mritchie712 3y ago
      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.
- AdamProut 3y ago
  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/ 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.
- roseway4 3y ago
  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 https://github.com/getzep/zep
  - brigadier132 3y ago
    When you say you can do vss along with filtering. Does the filtering happen before or after the vss?
    - roseway4 3y ago
      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.
  - kiwicopple 3y ago
    > 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.
    - deepsquirrelnet 3y ago
      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.
kiwicopple 3y ago
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 https://supabase.com/docs/guides/ai/structured-unstructured

92 points by samwillis 3y ago

jonplackett 3y ago

Anyone have any experience or knowledge of this VS pinecone?

mritchie712 3y ago
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/ https://www.definite.app/
- aligajani 3y ago
  How do you prevent SQL injections or bad queries?
  - mritchie712 3y ago
    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.
AdamProut 3y ago
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/ 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.
roseway4 3y ago
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 https://github.com/getzep/zep
- brigadier132 3y ago
  When you say you can do vss along with filtering. Does the filtering happen before or after the vss?
  - roseway4 3y ago
    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.
- kiwicopple 3y ago
  > 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.
  - deepsquirrelnet 3y ago
    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.

kiwicopple 3y ago

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 https://supabase.com/docs/guides/ai/structured-unstructured