Skip to the yard
Waxsealai

pg_pinecone Brings Compact, Filtered Vector Search to Postgres

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.

pg_pinecone Brings Compact, Filtered Vector Search to Postgres

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.

Harshita Daddala

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.

Anthony Mahanna

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.

Architecture showing an application sending SQL to Postgres, where pg_pinecone runs a native, pgvector, or remote search engine

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.

Unfiltered search latency at 0.95 and 0.99 recall: pg_pinecone 1.27 and 2.28 ms, HNSW halfvec 1.71 and 4.65 ms, HNSW full-size 2.67 and 6.72 ms

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.

Filtered search, recall vs. latency: pg_pinecone 0.981 at 8.3 ms (1% filter) and 1.000 at 12.3 ms (10% filter); pgvector HNSW on halfvec 0.924 at 56 ms and 0.922 at 5.1 ms
HNSW stores vectors and graph links; the native engine stores clusters and compact RaBitQ codesSearch stages: probe nearby clusters, estimate distances with RaBitQ codes, and rerank candidates using full vectors

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.

Index build time: pg_pinecone 45 s, HNSW halfvec 62 s, HNSW full-size 79 s

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.

HNSW applies filters after searching, while the native engine applies filters during search

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 size: pg_pinecone 136 MB, HNSW halfvec 2.0 GB, HNSW full-size 4.1 GB

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.

Source: Richard Williams · www.pinecone.io

Same vesselRelated cargo