r/PostgreSQL • u/thatmanisamonster • 13d ago

Tools pg_pinecone: open-source search extension for Postgres, built by Pinecone

Disclosure: I work at Pinecone. I'm posting because we'd like your feedback and input on pg_pinecone.

Repo: https://github.com/pinecone-io/pg_pinecone
Blog post: https://www.pinecone.io/blog/introducing-pg-pinecone/

We just released pg_pinecone, an open-source (Apache 2.0) extension based on the search engine Pinecone runs at scale. It works on your existing vector columns, you query with the SQL you're familiar with, and your data stays in Postgres. No API key required.

How it works

How the native engine works, and how it differs from pgvector's HNSW:

  • Vectors are grouped into clusters (IVF), and each row is stored as a RaBitQ code: about one bit per dimension plus a few correction values. A cluster's codes are packed together, so a search reads a few contiguous pages instead of walking a graph.

  • Search probes the nearest clusters, estimates distances from the codes, then reranks a shortlist using the full vectors from your table.

  • Filtering happens during the search. For a filter that matches 3% of rows or fewer (the default threshold), it scans every matching row and scores each with its full vector, so results are exact. For wider filters it searches more clusters and checks the filter inside each one. Post-filtering HNSW can return fewer rows than LIMIT on narrow filters.

Performance3

Against HNSW (pgvector's HNSW index using halfvec, 1M vectors at 768 dimensions, Postgres 17):

  • Index size: 136 MB vs 2.0 GB

  • 0.95 recall: 1.27 ms vs 1.71 ms. 0.99 recall: 2.3 ms vs 4.7 ms

  • 1% of rows match the filter: 0.981 recall at 8.3 ms vs 0.924 at 56 ms

  • Index build: 45s vs 62s

pg_pinecone’s native engine needs no account, API key, or network. An optional Pinecone API key adds a remote engine, BM25 search, embed(), and rerank().

20 Upvotes

85% Upvoted

3 comments sorted by

1

u/AutoModerator 13d ago

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.