The constant stream of features getting added to pgvector is impressive. Also, really love some of the benchmarks and explanations that were thrown in here.

Just out of curiosity, is it possible to efficiently get "For all rows in table A, give me the two nearest neighbors in table B" in SQL?

You can do this with a lateral join.

SELECT * FROM A, LATERAL (SELECT * FROM B ORDER BY a.embedding <-> b.embedding LIMIT 2) AS closest_in_b ORDER BY A.id;

It’s going to have so-so performance. So, don’t hose your production server. Create a vector index on b.embedding. HNSW will be nice for this use case.

Thanks! I've implemented this manually with HNSW and I was wondering if pgvector would be an easy replacement.

Congratulations, can anyone give an insight on how this compares to pg_embedding [0] (Postgres and use HNSW). Or the use-cases compared to other victor databases. It’s getting hard to keep up what’s happening in the LLM scene.

[0] https://github.com/neondatabase/pg_embedding

I suspect a lot of the paying use cases for vector search right now have a lot to do with enterprise search functionality. As for the LLM stuff, I do wonder if it's paying bills yet.

If anyone is into embeddings, check out Instructor Large/XL. It's quite good and super fast using L4s. Haven't quite figured out the instructions bits yet, but got it clustering things today and that was cool.

Jonathan did just that in his prior blog post: https://jkatz05.com/post/postgres/pgvector-hnsw-performance/

pgvector_hnsw outperforms pg_embedding using a query per sec / recall measure across various common embedding widths and a range of dataset sizes.

Is there a reason left to use ivfflat compared to hnsw? I am not that deep in the tech. But if hnsw is much much faster thats a big argument. Is hnsw better in every aspect or is the recall accurracy better for ivfflat?