11-19 更新 实时
首页/ 技术栈/ 正文

推出 pg_pinecone:为 Postgres 数据提供开源搜索

技术栈 来源:Pinecone Blog 2026-10-27 21:00 0 阅读 机器翻译
推出 pg_pinecone:为 Postgres 数据提供开源搜索

今天我们发布 pg_pinecone,这是一个基于 Apache 2.0 协议的开源 Postgres 扩展,构建在 Pinecone 大规模运行的搜索引擎之上。它会为你已有的向量列建立索引。它的索引不采用图结构,而是存储每个向量的紧凑摘要,因此搜索能在很小的内存占用下保持高速,索引构建也很快。它在搜索过程中应用过滤,而不是在搜索之后,因此即使范围很窄也能返回完整的结果集。它运行在你的 Postgres 数据库内部,所以数据永远不会离开 Postgres。最终结果是:对你已存储的数据实现快速、准确、带过滤的搜索,而无需运行第二套系统。

pg_pinecone 是一个 Postgres 扩展,可与你在 pgvector 中已有的向量列配合使用。你的应用程序以相同方式连接 Postgres,并发送相同的 SQL,例如 ORDER BY embedding <=> $1 LIMIT 10。你的表(包括完整的向量)留在原处,Postgres 依然是记录系统。

每张表使用三种引擎之一:

native(默认):pg_pinecone 自有的引擎,完全在你的 Postgres 数据库内部构建并搜索其索引,无需 Pinecone 账号、API key 或网络调用

pgvector:搜索 pgvector 的 HNSW 索引,也就是你可能已经在用的那个

remote:将搜索发送到 Pinecone 托管服务中的索引

在三者之间切换只需一次 pinecone.set_engine() 调用,你的查询无需任何改动。

与 pgvector 的 HNSW 一样,pg_pinecone 的 native 引擎使用 ANN(近似最近邻)算法。它的索引以簇的形式存储压缩后的向量编码(使用 RaBitQ),而非图结构,并且引擎在搜索过程中过滤,而不是在搜索之后过滤。两者结合,使索引保持小巧、搜索保持快速、过滤结果保持准确。

pgvector 的 HNSW 索引会存储每个向量的完整副本,以及指向其最近邻的链接。搜索时会在该图上遍历,并在每一跳比较完整向量,因此整个图必须常驻内存才能保证速度。

原生引擎不使用图索引(图索引在静态、无过滤的高 QPS 负载下表现良好),而是借助倒排文件索引(IVF)将相似向量归入各个簇。该索引把每个向量以 RaBitQ 编码(Gao 和 Long,SIGMOD 2024)的形式存为量化副本:约每维度一个比特,外加若干修正值。每个簇的编码被打包在一起,因此一次搜索只需读取少数几个连续页面,而无需在图上跳转。要了解 RaBitQ 与其他量化方法的对比,可试用 VQ-Bench,或阅读我们是如何构建它的。

对于 100 万个 768 维向量,原生引擎构建的索引为 136 MB,相比之下 HNSW 在 halfvec 上为 2.0 GB,在全尺寸向量上为 4.1 GB。

搜索中的大部分计算都很廉价。将查询向量与 RaBitQ 编码进行比较只需少量位运算,而无需完整的浮点运算,并且只有入围的候选向量才会用完整向量进行打分:

通过将查询向量与每个簇的质心进行比较,探测最近的若干簇。

根据每行的 RaBitQ 编码计算估计距离,并保留最佳候选向量。

使用表中这些候选向量的完整向量对其进行重排序。

在 0.95 召回率下,一次查询耗时 1.3 ms,相比之下 HNSW 在 halfvec 上为 1.7 ms,在全尺寸向量上为 2.7 ms。在 0.99 召回率下,则为 2.3 ms,对比 4.7 ms 和 6.7 ms。

准确的过滤结果

pgvector 的 HNSW 采用后置过滤:它先找出最近邻,然后再应用 WHERE 条件。当过滤条件较窄时——比如一百个租户中只选一个——大多数候选会被丢弃,查询返回的行数可能少于 LIMIT。pgvector 的迭代扫描通过继续向外搜索来补全列表,代价是耗时增加;如果启用 relaxed_order,还会损失准确性。

原生引擎在搜索过程中进行过滤,并根据选择率挑选策略。它把某一个过滤列的取值与每行的 RaBitQ 编码存放在一起,并在构建时统计每个取值对应多少行:

窄过滤(默认匹配行数占比 3% 及以下):预过滤。它会扫描所有簇中每一行匹配的记录,并用完整向量为每行打分,因此结果是精确的。

宽过滤(匹配行数超过 3%):单阶段过滤。相比无过滤搜索,它会搜索更多最近的簇(最多可达一半),并在打分前在每个簇内检查过滤条件。

当匹配行占 10% 时,原生引擎使用单阶段过滤,在 12.3 ms 内达到 1.000 的召回率。采用迭代扫描的 HNSW 最好只能达到 0.920 的召回率,耗时 5.9 ms。

保持索引最新

插入新行时,每个向量会加入最近的簇,并立即可被搜索到。聚类中心仍停留在构建时确定的位置,因此在发生大幅变化后——比如一次批量导入让表的数据量翻倍,或者新数据与索引构建时所依据的数据分布不同——应运行 REINDEX 或启用自动维护来重新平衡各个簇。

我们将 pg_pinecone 的原生引擎与 pgvector 的 HNSW 索引进行了对比,在同一份数据上同时使用完整尺寸的向量和 halfvec:100 万条 768 维向量(wiki_dpr)。每张表都来自一台 AWS r6id.4xlarge(16 vCPU、128 GB RAM)。无过滤的延迟、构建时间和索引大小来自同一个会话(PostgreSQL 17);过滤搜索来自另一个会话(PostgreSQL 18);4 GB 内存上限则是第三个会话。除非另有说明,延迟为单客户端、k=10 时的中位数(p50)。完整的环境配置和重跑测试的命令可在基准测试框架的 README 中找到。以下是 pg_pinecone 原生引擎表现突出的地方。

更快的无过滤搜索

在单客户端、k=10 的情况下,无过滤搜索在相同召回率下比 HNSW 更快。

过滤搜索更准确,且在过滤条件较窄时更快

大多数真实搜索都会按租户、用户或类别进行过滤。当过滤条件较窄时,HNSW 返回的真实最近邻更少。pg_pinecone 在搜索过程中进行过滤,因此能找到它们。

当过滤条件只保留 1% 或 10% 的行、而某次查询的最近邻大多落在该范围之外时——这正是后过滤最难应对的场景——的性能表现:

在同一份数据上,pg_pinecone 构建索引的速度快于 pgvector HNSW,因此你可以更快地为大表建好索引并开始搜索。

一个能装进内存的索引

pgvector 的 HNSW 索引会存储每一个向量以及邻居之间的连接,因此体积增长很快,并会与数据库的其他部分争夺内存。pg_pinecone 原生引擎则改为存储每个向量的紧凑摘要,因此索引小得多,并且随着数据增长仍能常驻内存。在我们的测试中:

更大规模、更多功能,SQL 查询不变

原生引擎已能覆盖许多应用的搜索需求,只要它满足你的需要,就可以一直用下去。如果配置了 Pinecone API key,同一个扩展还能带来几个额外的工具。每一个都是可选的,你可以逐个开启。下面说明每个工具增加了什么,以及什么时候值得开启。

突破单台服务器的规模上限。随着数据增长,索引变大后会占用更多内存,并与数据库的其他部分争夺资源。出现这种情况时,远程引擎只需一条 SQL 命令就能把搜索迁移到 Pinecone:

SELECT pinecone.set_engine('docs', 'remote');

你的向量会在后台同步到 Pinecone 索引,在复制完成之前搜索仍在 Postgres 中运行。你的 SQL 查询无需改动,Postgres 仍是你的数据源,你也可以用同样的方式切回原生引擎。

在 SQL 中把文本转成向量。如果你的表里已有文本但还没有向量,embed() 会调用 Pinecone 的推理服务。你无需自己托管模型,也不用管理其他服务商的密钥,Pinecone 的免费额度足以完成第一次构建。当你从文本出发、希望快速实现搜索时可以使用它。它同时适用于原生引擎和远程引擎。它不会改变搜索运行的位置:只有 embed 调用需要用到 Pinecone。

UPDATE docs SET embedding = pinecone.embed(content, input_type => 'passage')::vector;

按关键词搜索,也按语义搜索。无需切换向量引擎,你还可以在同一张表上使用 Pinecone 的全文本搜索(BM25),与向量搜索并行使用。当人们用精确词条(例如产品名称或错误码)搜索,同时也按语义搜索时,就可以用它。

SELECT pinecone.track_docs('docs', text_columns => '["content"]'); SELECT id, score FROM pinecone.search_docs('docs', 'error 42P01', k => 10);

用 rerank() 把最好的结果排在前面。rerank() 借助 Pinecone 的重排序模型对你的靠前结果重新排序,让最相关的结果排在最前面。当只有前几条结果重要时(例如在 RAG 中),或者当你需要把关键词结果与向量结果合并成一份列表时,它会很有帮助。

SELECT * FROM pinecone.rerank( 'which database stores vectors?', ARRAY( SELECT content FROM docs ORDER BY embedding <=> $1 LIMIT 20 ) );

我们的使命是让 AI 具备知识、无处不在。Pinecone 运行在我们的云上,也通过 BYOC 运行在你的云上,如今还借助 pg_pinecone 运行在你的 Postgres 数据库里。

使用现有的向量列,或新建一个向量列。你甚至可以用 embed() 和一个免费的 Pinecone API key 来填充该列。

CREATE TABLE docs ( id bigint PRIMARY KEY, tenant_id bigint, body text, embedding vector(768) );

为你的向量列建立索引:

CREATE EXTENSION pg_pinecone; SELECT pinecone.create_index('docs', 'embedding', filter_column => 'tenant_id');

原文较长,译文为节选;译文由英文原文机器翻译,术语按行业惯例统一,可在文末展开原文核对。

查看英文原文(Introducing pg_pinecone: Open-Source Search for Your Postgres Data)

Today we’re releasing pg_pinecone, an open-source Postgres extension under Apache 2.0, built on the search engine Pinecone runs at scale. It indexes the vector columns you already have. Rather than a graph, its index stores a compact summary of each vector, so search stays fast on a small memory footprint and indexes build quickly. It applies the filter during search, not after, so a narrow scope still returns a full set of results. It runs inside your Postgres database, so the data never leaves Postgres. The result is fast, accurate, filtered search on data you already store, with no second system to run.

Architecture: application sends standard SQL to Postgres; pg_pinecone runs the native, pgvector, or remote engine; remote syncs to a Pinecone index

pg_pinecone is a Postgres extension that works with the vector columns you already have in pgvector. Your application connects to Postgres the same way and sends the same SQL, such as ORDER BY embedding <=> $1 LIMIT 10. Your tables, including the full vectors, stay where they are, and Postgres remains the system of record.

Each table uses one of three engines:

native (the default): pg_pinecone’s own engine, which builds and searches its index entirely inside your Postgres database, with no Pinecone account, API key, or network calls

pgvector: searches pgvector’s HNSW index, the one you may already run

remote: sends search to an index in Pinecone’s managed service

Switching between them takes one call to pinecone.set_engine(), and your queries don’t change.

Like pgvector’s HNSW, pg_pinecone’s native engine uses an ANN (approximate nearest neighbor) algorithm. Its index stores compressed vector codes (using RaBitQ) in clusters instead of a graph, and the engine filters during the search instead of after it. Together, these keep the index small, search fast, and filtered results accurate.

pgvector’s HNSW index stores a full copy of every vector plus links to its nearest neighbors. A search walks that graph and compares full vectors at each hop, so the whole graph needs to stay in memory to be fast.

Instead of a graph index (which performs well at static unfiltered high QPS workloads), the native engine groups similar vectors into clusters using an inverted file index (IVF). The index stores a quantized copy of each vector as a RaBitQ code (Gao and Long, SIGMOD 2024): about one bit per dimension, plus a few correction values. The codes for each cluster are packed together, so a search reads a few contiguous pages instead of hopping across a graph. To see how RaBitQ compares with other quantization methods, try VQ-Bench or read how we built it.

For 1 million 768-dimension vectors, the native engine builds a 136 MB index, compared with 2.0 GB for HNSW on halfvec and 4.1 GB on full-size vectors.

Most of the work in a search is cheap. Comparing the query vector with a RaBitQ code takes a few bit operations instead of full floating-point math, and only a shortlist of candidates is scored with full vectors:

Probe the nearest clusters by comparing the query vector with each cluster’s centroid.

Compute an estimated distance for each row from its RaBitQ code, and keep the best candidates.

Rerank the candidates using their full vectors from your table.

At 0.95 recall, a query takes 1.3 ms, compared with 1.7 ms for HNSW on halfvec and 2.7 ms on full-size vectors. At 0.99 recall, it’s 2.3 ms vs. 4.7 ms and 6.7 ms.

Accurate filtered results

pgvector’s HNSW post-filters: it finds the nearest neighbors first, then applies the WHERE clause. With a narrow filter, like one tenant out of a hundred, most candidates are discarded, and a query can return fewer rows than its LIMIT. pgvector’s iterative scans fill the list by searching further, at a cost in time, and in accuracy if relaxed_order is used.

The native engine filters during the search, and picks its strategy based on selectivity. It stores the value of one filter column next to each row’s RaBitQ code and, at build time, counts how many rows carry each value:

Narrow filter (3% of rows or fewer, by default): pre-filtering. It scans every matching row across all clusters and scores each one with its full vector, so results are exact.

Wide filter (more than 3% of rows): single-stage filtering. It searches more of the nearest clusters than an unfiltered search (up to half), and checks the filter inside each cluster before scoring.

With 10% of rows matching, the native engine uses single-stage filtering and reaches 1.000 recall in 12.3 ms. HNSW with iterative scans reaches 0.920 recall at best, in 5.9 ms.

Keeping the index current

When you insert new rows, each vector joins the nearest cluster and is searchable immediately. Centroids stay where the build placed them, so after large changes, like a bulk load that doubles the table or new data unlike what the index was built on, run REINDEX or enable automatic maintenance to rebalance the clusters.

We compared pg_pinecone's native engine with pgvector's HNSW index, using both full-size vectors and halfvec, on the same data: 1M vectors with 768 dimensions (wiki_dpr). Each table is from an AWS r6id.4xlarge (16 vCPUs, 128 GB RAM). Unfiltered latency, build time, and index size are one session (PostgreSQL 17); filtered search is another (PostgreSQL 18); the 4 GB memory cap is a third. Latency is median (p50) at k=10 with one client unless noted. The full setup and the commands to rerun the tests can be found in the benchmark harness README. Here's where pg_pinecone's native engine stands out.

Faster unfiltered search

At k=10 with one client, unfiltered search is faster than HNSW at the same recall.

More accurate filtered search, and faster when the filter is narrow

Most real searches filter by tenant, user or category. When the filter is narrow, HNSW returns fewer of the true closest matches. pg_pinecone filters during the search, so it finds them.

Performance when the filter keeps 1% or 10% of rows and a query’s closest matches mostly fall outside it, the hard case for post-filtering:

pg_pinecone builds its index faster than pgvector HNSW on the same data, so you can index a large table and start searching sooner.

An index that fits in memory

pgvector's HNSW index stores every vector plus links between neighbors, so it grows fast and competes with the rest of your database for memory. pg_pinecone native stores a compact summary of each vector instead, so the index is much smaller and stays in memory as your data grows. In our tests:

More scale and more features, same SQL queries

The native engine covers search for many apps, and you can stay on it as long as it works for you. With a Pinecone API key, the same extension gives you a few more tools. Each one is optional, and you can add them one at a time. Here's what each one adds and when it's worth turning on.

Scale beyond one server. As your data grows, a larger index uses more memory and competes with the rest of your database. When that happens, the remote engine moves search to Pinecone with one SQL command:

SELECT pinecone.set_engine('docs', 'remote');

Your embeddings sync to a Pinecone index in the background, and searches keep running in Postgres until the copy finishes. Your SQL queries don't change, Postgres stays your source of truth, and you can switch back to the native engine the same way.

Turn text into embeddings in SQL. If your table has text but no embeddings yet, embed() calls Pinecone's inference service. You don't need to host a model or manage another provider's key, and Pinecone's free tier covers a first build. Use it when you're starting from text and want to get to search quickly. It works with both the native and remote engines. It does not change where search runs: only the embed call needs Pinecone.

UPDATE docs SET embedding = pinecone.embed(content, input_type => 'passage')::vector;

Search by keyword and by meaning. Without switching the vector engine, you can also use Pinecone's full-text search (BM25) on the same table, alongside vector search. Use it when people search with exact terms, like product names or error codes, as well as by meaning.

SELECT pinecone.track_docs('docs', text_columns => '["content"]'); SELECT id, score FROM pinecone.search_docs('docs', 'error 42P01', k => 10);

Put the best results first with rerank().rerank() reorders your top results with Pinecone's reranking models, so the most relevant ones come first. It helps when only the top few results matter, as in RAG, or when you merge keyword and vector results into one list.

SELECT * FROM pinecone.rerank( 'which database stores vectors?', ARRAY( SELECT content FROM docs ORDER BY embedding <=> $1 LIMIT 20 ) );

Our mission is to make AI knowledgeable, everywhere. Pinecone runs in our cloud, in yours with BYOC, and now inside your Postgres database with pg_pinecone.

Use an existing vector column or create a new one. You can even fill the column with embed() and a free Pinecone API key.

CREATE TABLE docs ( id bigint PRIMARY KEY, tenant_id bigint, body text, embedding vector(768) );

Index your vector column:

CREATE EXTENSION pg_pinecone; SELECT pinecone.create_index('docs', 'embedding', filter_column => 'tenant_id');

Query the way you already do, filter by tenant_id and order by embedding:

SELECT id, body FROM docs WHERE tenant_id = $1 ORDER BY embedding <=> $2 LIMIT 10;

Full setup steps are in the README.

Help us shape pg_pinecone

This is our first release, and we want your input on where it goes next.

Pinecone maintains pg_pinecone and reviews every pull request. We'll build the public roadmap from your feedback shared on GitHub, in Discord, and at our monthly office hours.

Open a feature request on GitHub or upvote one that's already there

Share your benchmark results, setups, and what you built in the pg_pinecone channel on Discord

Ask questions in the pg_pinecone channel on Discord

Run into an issue? Submit a bug on GitHub

pg_pinecone is ready for your data today. Install it, index a Postgres table you already have, and show us what you build!

Was this article helpful?

Start building knowledgeable AI today

Create your first index for free, then pay as you go when you're ready to scale.

原文为英文,正文由自动抓取并保留原文。

本文由采集器自动同步

原文发布于 Pinecone Blog(Introducing pg_pinecone: Open-Source Search for Your Postgres Data),标题、摘要与正文由机器翻译,术语按行业惯例统一,未经人工逐句校订。

阅读原文 ↗