pg_pinecone Brings Compact, Filtered Vector Search to Postgres
- Cargo
- Machine Learning
- Shipped
- Shipper
- Richard Williams
- Readers
- 840
The Apache 2.0 extension adds compact vector indexing and search inside Postgres, with optional Pinecone services and unchanged SQL queries.

For teams whose application data already lives in Postgres, pg_pinecone adds another way to search its vectors without moving the database’s source data. The Apache 2.0 extension uses a compact index and applies filters during search, targeting two practical limits of HNSW in pgvector: memory use and the loss of relevant results under narrow filters.

That design is aimed at applications such as support assistants, recommendations and systems that retrieve records using user or tenant constraints. pg_pinecone works with existing pgvector columns and standard SQL. Its native engine runs within Postgres, and doesn’t require a Pinecone account, API key or network connection. Teams can also select pgvector’s HNSW search or route queries to a managed Pinecone index.
Three engines, one query interface
An application connects to Postgres as usual and can keep using a query such as ORDER BY embedding <=> $1 LIMIT 10. Tables and full vectors remain in Postgres. The extension lets each table use one of three engines: native, the default; pgvector, which searches an existing HNSW index; or remote, which searches a Pinecone index. Switching engines takes one call to pinecone.set_engine(), without changing the query.

The native engine is an approximate nearest neighbor (ANN) search method. Instead of building a graph of links between vectors, it groups similar vectors into clusters using an inverted file index. It stores a compressed RaBitQ code for each vector, about one bit per dimension plus correction values. At query time, it compares the query with cluster centroids, estimates distances from the codes, then reranks a shortlist using the full vectors in the table.

Compact storage and measured search times
HNSW stores vectors and graph links. This can perform well for static, unfiltered workloads with high query volume, but its index grows with the dataset and can require substantial memory to maintain performance. The native engine’s clustered codes are packed into contiguous pages, reducing index size and avoiding graph traversal.

In tests on 1 million vectors with 768 dimensions, the native index measured 136 MB. The comparison HNSW indexes measured 2.0 GB with half precision vectors (halfvec) and 4.1 GB with full-size vectors. The source data remains in the table; the compact index supplies estimates, while the final ranking uses full vectors.
At 0.95 recall, reported median query latency was 1.3 ms for the native engine, versus 1.7 ms for HNSW on halfvec and 2.7 ms on full-size vectors. At 0.99 recall, the respective figures were 2.3 ms, 4.7 ms and 6.7 ms. These results describe one benchmark setup, rather than a guarantee across database sizes or workloads.



Filtering happens during search
Filtered vector search is a common weak spot for graph indexes. HNSW can find nearby vectors first and apply a WHERE condition afterward. If a query is limited to one tenant, many candidates may be discarded, leaving fewer rows than the requested limit. Iterative scans can search further to fill the list, but that costs time; relaxed ordering can also reduce accuracy.
The native engine incorporates a filter column into its index and counts the values during index construction. It chooses a search strategy based on how many rows match. For a narrow filter—3% of rows or fewer by default—it scans every matching row across clusters and scores each with its full vector, producing exact results. For a wider filter, it searches more of the closest clusters than an unfiltered query, up to half, and checks the filter within each cluster.

In the reported case where 10% of rows match, native search reached 1.000 recall in 12.3 ms. HNSW with iterative scans reached 0.920 recall in 5.9 ms. A separate hard case, where nearest matches mostly fall outside the filter, reported 0.981 recall at 8.3 ms for pg_pinecone with a 1% filter, and 1.000 recall at 12.3 ms with a 10% filter. HNSW on halfvec reached 0.924 recall at 56 ms for the 1% filter and 0.922 at 5.1 ms for the 10% filter. The figures show a tradeoff: the native engine took longer in the 10% comparison while returning more complete results.

Benchmark scope and index maintenance
The comparisons used 1 million 768-dimensional wiki_dpr vectors on an AWS r6id.4xlarge machine with 16 vCPUs and 128 GB of RAM. Unfiltered latency, build time and index size were measured in one PostgreSQL 17 session; filtered search used PostgreSQL 18, and the 4 GB memory cap was tested separately. Latency figures are median (p50) at k=10 with one client unless otherwise specified. The benchmark harness and commands are available in the project’s README.

Index construction took 45 seconds for pg_pinecone, compared with 62 seconds for HNSW on halfvec and 79 seconds for HNSW on full-size vectors. These build-time and storage results matter for teams weighing indexing delays and database memory pressure alongside query speed.
New rows join their nearest cluster and become searchable immediately. Cluster centroids stay fixed after the index is built, though, so a large bulk load or a shift in the incoming data may call for REINDEX or automatic maintenance to rebalance the clusters.
Optional Pinecone services
The native engine can cover applications that fit within one Postgres server. If that server’s index becomes a constraint, the remote engine syncs embeddings to a Pinecone index in the background. Searches continue in Postgres until the copy is ready; the SQL interface and Postgres’s role as the source of truth remain the same. Switching back to native search also uses the engine setting.
With a Pinecone API key, the extension offers additional services. The embed() function can call Pinecone’s inference service to create vectors from text, without hosting a model. Pinecone’s free tier covers a first build, and this feature works with both native and remote search. Only the embedding call requires Pinecone; search remains in the selected engine.
For exact-term retrieval alongside vector search, the extension can use Pinecone full-text search based on BM25. That can help with queries involving product names or error codes. A rerank() function can then reorder candidate results using Pinecone reranking models, which may be useful when the top few results carry most of the value or when combining keyword and vector results.
Getting started
pg_pinecone is available under the Apache 2.0 license from GitHub releases and as the pineconeteam/pg_pinecone Docker image. After installing the extension, a team can index an existing vector column, optionally naming a filter column such as tenant_id, then continue querying it with ordinary SQL.
CREATE EXTENSION pg_pinecone;
SELECT pinecone.create_index('docs', 'embedding', filter_column => 'tenant_id');
SELECT id, body
FROM docs
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 10;The main adoption question is whether the native engine’s memory footprint and filtered-search behavior match a team’s own data and latency needs. Its benchmarks offer a useful comparison point, while the option to change engines without rewriting queries leaves room to test those tradeoffs against a live Postgres workload. The project is maintained by Pinecone, with feedback and bug reports accepted through its GitHub issue tracker and community Discord channel.