Pg_vectorsearch: An index that is faster than pgvector's IVFFlat and HNSW (github.com)

Like, I would love to learn more about the design process that led to a whole new thing. I am sure the decision was not taken lightly, but the README is very thin on the details for why anyone should go through the troubles above, at least in the making of it.

For example: (taken from a project I'm currently working on)

```

SELECT *

FROM report_embeddings a

INNER JOIN report_embeddings b

    ON (

        subvector(a.embedding, 1, 768)::vector(768) <-> 

    ) < $1

    AND b.id != a.id

    AND a.embedding <-> b.embedding < $1

In this example, I truncate the embedding to 768 dimensions for the index and the lookup, (see the INNER JOIN) but do an additional full vector comparison as well. (in the WHERE clause)

This performs a fast but less accurate lookup into the index, and then only does the non-indexed full vector check for the matched results.

But how are people doing recency biasing with vector databases?

All of our approaches are either expensive or super hacky.

We address that directly in the blog post: https://www.tigerdata.com/blog/announcing-pg_vectorsearch-sc...

Summary: We weren't happy with the unpredictable recall performance of pgvectorscale (and couldn't find a way to fix that in the literature) and decided that moving away from graph-algorithms to partition-based ones was a better long-term approach.

Project zaxonlite: https://github.com/insanai/zaxonlite

Project sqlodin: https://github.com/insanai/sqlodin

Just you need to remember sqlite support single writer at a time so both needs to work with that limitation. PostgreSQL uses MVCC, so performance for concurrent writes from multiple clients might be better. Did not do benchmark against it so cannot say.

I sent the thread to Erik (the main contributor) though, so if you have feedback or questions, good chance that he'll see it.

This isn’t the first vector index we’ve built and open-sourced for PostgreSQL. In 2024, Tiger Data released pgvectorscale, which used a graph-based indexing algorithm inspired by Microsoft’s DiskANN research. We deliberately chose a disk-optimized design so that indexes could scale beyond available RAM. Our initial benchmarks were promising, but as adoption grew and we tested against a wider variety of datasets, particularly low-dimensional ones, we encountered a troubling problem: portions of the graph could become unreachable, causing recall to degrade. We searched the literature for a principled solution but couldn’t find one, and the heuristics we tried worked on some datasets but not others.

That’s a frustrating failure mode for a search index. When queries are slow, you can measure the problem and tune accordingly. But when recall degrades, you might not even notice until users start questioning the quality of their search results. And telling them to experiment with yet another set of tuning parameters isn’t the experience we want to provide.

Meanwhile, partitioned-list approaches reminiscent of IVFFlat were getting a second life, as advances in compression and optimization addressed many of their old limitations. More importantly, they offered stronger guarantees than we could achieve by patching a graph-based index. We came to believe they were a better foundation, and decided to start over.

With the introduction of pg_vectorsearch, we are shifting the majority of our vector-search development efforts to this new architecture. The existing pgvectorscale extension, which is stable and handling production workloads currently, will enter maintenance mode.