Support for pgvector

[sarvesh-deserve]

Is there a support for pgvector?

[sinisaos]

@sarvesh-deserve No, it is not currently supported, but you can try the raw sql workaround to get the desired result. Pgvector supports asyncpg and you can try Piccolo with raw sql to add vector columns and indexes. For select results you can use raw sql queries and there are also OrderbyRaw helper that allow Piccolo code to be mixed with raw sql for eg. get the nearest neighbors to a vector. Something similar to this. I hope you find this useful. Good luck.

[JoeGermuska]

Thanks for the pointer to #876 but I'm not clear on how to insert vectors.

I tried something like Table.raw('update table_name set embedding = {} where id = {}', embedding, id) but the query builder is complaining:

DataError: invalid input for query argument $1: [-0.022150227800011635, -0.0352758131921... (expected str, got list)

embedding is a vector column, so not defined in the table definition -- I can't find anything about type hinting so that the query builder would accept a list in that position.

[sinisaos]

@JoeGermuska I actually never tried pgvector until now. First I had to install vector extension, then add vector extension to Piccolo Postgres engine like this

# in piccolo/engine/postgres.py
def __init__(
      self,
      config: t.Dict[str, t.Any],
      extensions: t.Sequence[str] = ("uuid-ossp","vector",),
      ...

Then I alter table to add vector column like this

await Table.raw("ALTER TABLE table_name ADD COLUMN embedding vector(3);")

To insert vector data into the table, I used the Postgres cast operator (::) like this {}::float[]

embedding = [-0.022150227800011635, -0.0352758131921, -0.013192581323527]
await Table.raw("INSERT INTO table_name (some_column, embedding) VALUES ({}, {}::float[])", "some_column_data", embedding")

and I was able to insert data into the table. You can try that.

[JoeGermuska]

yes, that seems to work -- thanks for trying it out!

I had pressed ahead and used psycopg to do the work. For some reason, I didn't need to do a cast with that, but I do see that your code works for the inserting. I'll see if I can adapt the query side to work with Piccolo

[JoeGermuska]

FWIW, the query seems to be a hitch. Casting the parameter to float[] as in

…
SELECT ..., 1 - (embedding <=> {}::float[]) AS cosine_similarity FROM...
…

errors with

UndefinedFunctionError: operator does not exist: vector <=> double precision[]
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

For psycopg2, i had to cast to vector as in 1 - (embedding <=> %s::vector) but for Piccolo, that yields (expected str, got list)

This is just prototyping, so I'm not going to get hung up, but leaving this for anyone else who googles their way here.

[sinisaos]

@JoeGermuska I don't know if it's a good thing to do and I'm sorry if it's not, but you can try something like this.

results = []
embeddings = Table.raw("SELECT embedding from table_name;").run_sync()
for item in embeddings:
    results.extend(
        Table.raw(
            "SELECT 1 - (embedding <=> {}) AS cosine_similarity from table_name;",
            item["embedding"],
        ).run_sync()
    )
print(results)

Are the query results the same as in psycopg2?