The PostgreSQL world really loves operators - this extension uses <#>, <-> and <=> for example

I've been working with PostgreSQL JSON queries a bit recently which is also really operator heavy: https://www.postgresql.org/docs/13/functions-json.html - ->, ->>, #>, #>>

I have a really hard time remember what any of these do - I personally much prefer to use function equivalents like json_extract_path() if they are available.

Nothing stops you. All operators are implemented just as procedure calls.

Minimal working example of storing numpy vectors in sqlite3 using Python:

    import sqlite3
    from scipy.spatial.distance import cdist
    import numpy as np

    class Vector:
        def __init__(self, arg):
            self.arg = arg

        def __str__(self):
            return 'Vector({!r})'.format(self.arg)


    def adapter_func(obj: np.ndarray):
        return obj.tobytes()

    def converter_func(data: bytes):
        return np.frombuffer(data)

    sqlite3.register_adapter(Vector, adapter_func)
    sqlite3.register_converter(f"{Vector.__name__}", converter_func)

    if __name__ == '__main__':
        with sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES) as con:
            cur = con.cursor()
            cur.execute("create table test(v vector)")
            cur.execute("insert into test(v) values (?)", (np.random.random(1280),))
            cur.execute("insert into test(v) values (?)", (np.random.random(1280),))

            cur.execute('select v from test')
            vectors = []
            for v, in cur.fetchall():
                print(v.shape, v)
                assert isinstance(v, np.ndarray)
                vectors.append(v)

            assert len(vectors) == 2
            print(cdist(vectors[:1], vectors[1:], metric='cosine')[0])

            cur.close()

Might be worth checking out :)

Disclosure: I work there.

Also, can you join with existing SQL tables to do hybrid searches in pinecone?

We don't support external SQL joins yet. Depending on what you're trying to do, we have an upcoming feature that might do the job.

At 100M vectors you're well into "volume discount" territory. Even more so with 3B vectors, as you mentioned in another comment. The free trial is capped at 10GB but shoot me an email ([email protected]) to get around it for a proper test and pricing estimate.

related: https://github.com/netrasys/pgANN

It's still loaded from a file, but heavily uses memory-mapping and caching to be speedy and not overload your RAM immediately. And in production scenarios, multiple worker processes can share that memory due to the memory mapping.

Granted it's read-only, so might not be exactly what you are looking for.

Disclaimer: I'm the author.

PS: I work there

What do such vector similarity queries use in production when there's no DB support? Surely having the "real" DB like Postgres and one for vectors alongside it would be cumbersome.

I guess from the 'ivfflat' keyword that this project is serving vectors produced by FAISS[1] somehow?

https://github.com/facebookresearch/faiss

Here is a partial list of possible vector types:

https://github.com/facebookresearch/faiss/wiki/Faiss-indexes

https://github.com/jirutka/smlar