VectorChord Engineering Blog & Releases

• 1: Engineering & Deep Dives
  • 1.1: Scaling Vector Search to 1 Billion on PostgreSQL
  • 1.2: How We Made 100M Vector Indexing in 20 Minutes Possible on PostgreSQL
  • 1.3: Bringing Search‑Engine Ranking to PostgreSQL with VectorChord‑BM25
  • 1.4: Optimize RAG with Contextual Retrieval in PostgreSQL
  • 1.5: VectorChord: Cost-Efficient Upload & Search of 400 Million Vectors on AWS
  • 1.6: All-in-one VectorChord Suite: Building Production-Ready RAG Solutions Directly in PostgreSQL
  • 1.7: Introducing Vechord: Turn PostgreSQL into your search engine in a Pythonic way.
  • 1.8: Beyond Text: Unlock OCR-Free RAG in PostgreSQL with Modal & VectorChord
  • 1.9: 3 Billion Vectors in PostgreSQL to Protect the Earth
  • 1.10: Vector Search Over PostgreSQL: A Comparative Analysis of Memory and Disk Solutions
  • 1.11: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)
  • 1.12: VectorChord-BM25: Introducing pg_tokenizer—A Standalone, Multilingual Tokenizer for Advanced Search
  • 1.13: Hybrid search with Postgres Native BM25 and VectorChord
  • 1.14: Vector Search at 10,000 QPS in PostgreSQL with VectorChord
  • 1.15: VectorChord-BM25: Revolutionize PostgreSQL Search with BM25 Ranking — 3x Faster Than ElasticSearch
  • 1.16: Do we really need a specialized vector database? (2023)
  • 1.17: Supercharge vector search with ColBERT rerank in PostgreSQL
  • 1.18: ARM + VectorChord: Affordable, Fast Vector Search
  • 1.19: Why HNSW is Not the Answer
  • 1.20: Benefits and Steps of External Centroids Building in VectorChord
  • 1.21: VectorChord: Store 400k Vectors for $1 in PostgreSQL
  • 1.22: Introducing PGVecto.rs Cloud - Scalable Vector Search in PostgreSQL, Now in the Cloud
  • 1.23: Introducing Terraform Support for PGVecto.rs Cloud
  • 1.24: Unleash the power of sparse vector
  • 1.25: pg_bestmatch.rs: Elevate Your PostgreSQL Text Queries with BM25
  • 1.26: My binary vector search is better than your FP32 vectors
  • 1.27: pgvector vs. pgvecto.rs in 2024: A Comprehensive Comparison for Vector Search in PostgreSQL
  • 1.28: Building LLM Applications in LangChain using pgvecto.rs
  • 1.29: Building the RAG pipeline with LlamaIndex and pgvecto.rs
  • 1.30: Do we really need a specialized vector database?
• 2: Release Notes
  • 2.1: VectorChord 1.1: Native 4/8 Bit Vector Types and Per-Index Query Defaults
  • 2.2: VectorChord 1.0: Developer-First Vector Search on Postgres, 100x Faster Indexing than pgvector
  • 2.3: VectorChord 0.5: New RaBitQ-empowered DiskANN Index and Continuous Recall Measurement
  • 2.4: VectorChord 0.4: Faster PostgreSQL Vector Search with Advanced I/O and Prefiltering
  • 2.5: VectorChord 0.3: Bringing Efficient Multi-Vector Contextual Late Interaction in PostgreSQL
  • 2.6: VectorChord 0.2: 60K-Dimensional Vectors, 50% Faster Indexing with ARM
  • 2.7: pgvecto.rs 0.2: Unifying Relational Queries and Vector Search in PostgreSQL

1 - Engineering & Deep Dives

1.1 - Scaling Vector Search to 1 Billion on PostgreSQL

For teams trying to run vector search at billion scale themselves, the challenge is often not raw performance, but practicality. Many solutions designed for billion-scale, low-latency vector search come with practical constraints, requiring tradeoffs that affect how easily they can be adopted.

Most existing approaches fall into one of a few categories:

• Operationally complex: Powered by multi-node distributed system that are difficult to manage, operate, and maintain over time.

• Build-time prohibitive: Requiring long index build times, which makes re-indexing costly and easily impacts production workloads.

• Memory heavy: Depending on up to 1 TB of memory on a single machine, making the hardware significantly less affordable for most teams.

These tradeoffs are visible in existing public benchmarks. For example, ScyllaDB reports up to 98% recall with a P99 latency of 12.3 ms on DEEP-1B, but this result depends on multiple large instances. YugabyteDB, on the same dataset, reports significantly higher tail latency, with P99 reaching 0.319 seconds at the same scale.

For single-node deployments, previous work from Scalable Vector Search (SVS) shows that indexing DEEP-1B with HNSWlib typically requires 800 GiB of memory, though SVS or FAISS-IVFPQs can reduce it to around 300 GiB, which is still a substantial hardware requirement.

This makes smooth scaling difficult for teams that want to stay self-hosted. Moving from 1 million to 1 billion vectors often requires rethinking the architectures: new hardware assumptions or a different workflow.

With VectorChord 1.0.0, scaling is far more easy. By taking advantage of the new Hierarchical K-means and other optimizations, indexing 1B vectors follows the same process as indexing 1M vectors. This is exactly how we use it as well: Simply move to a slightly larger machine, for example from an AWS i7i.xlarge to an i7i.4xlarge.

The DEEP-1B Benchmark

To validate VectorChord’s capability at the billion-vector scale, we use the Yandex DEEP-1B dataset from BIGANN. DEEP-1B is a widely adopted benchmark for large-scale vector search, consisting of 1 billion 96-dimensional embeddings generated from deep learning models trained on natural images.

Because of its scale, DEEP-1B is widely used as a benchmark dataset for evaluating large-scale vector search systems. Its broad adoption makes results easy to reproduce and compare, particularly when assessing indexing performance, query latency, and resource efficiency at the billion-vector scale.

For such a huge dataset, building a VectorChord index requires:

• Storage: Approximately 900 GB of high-performance SSD

• Memory (shared_buffers): No less than 60 GB, managed by PostgreSQL

• Memory (extra): At least 60 GB extra memory required during index construction

Based on these requirements, an AWS i7i.4xlarge is the minimum configuration for indexing at this scale. Our tests were run on an AWS i7ie.6xlarge, reflecting practical deployments where additional memory is provisioned to reduce disk access and maintain stable query latency.

All experiments were run with VectorChord 1.0.0 on PostgreSQL 17. The SQL below is the exact command we used to build the index:

CREATE INDEX ON deep USING vchordrq (embedding vector_l2_ops) WITH (options = $$
build.pin = 2
residual_quantization = true
[build.internal]
build_threads = 24
lists = [800, 640000]
kmeans_algorithm.hierarchical = {}
$$);

Here’s what each option does in our build configuration:

• build.pin: Enables build-time pinning, caching the hot portion of the index in shared memory to speed up indexing on large datasets.

• residual_quantization: On DEEP-1B, we find that enabling residual quantization improves query performance, so we keep it on for this benchmark.

• build.internal.build_threads: Uses 24 threads for the K-means build stage, helping saturate available CPU resources on the instance.

• build.internal.lists: Based on our experience, we choose the appropriate list according to the number of rows. Using a two-level list helps significantly at large scale, improving both index build efficiency and query performance.

• build.internal.kmeans_algorithm.hierarchical: Enables the Hierarchical K-means path introduced in VectorChord 1.0, which significantly accelerates index construction at scale.

Our results

Index construction completed in 6,408 seconds (≈ 1.8 hours) when utilizing 24 CPU cores on a single AWS i7ie.6xlarge machine, demonstrating that billion-scale indexing can be completed within a practical window.

The figure shows query throughput versus recall on the Deep1B dataset using a single search thread, evaluated for both Top-10 and Top-100 queries, with all queries run against a warm cache.

For Top-10, throughput ranges from over 117 QPS at ~0.91 recall to around 69 QPS at ~0.95 recall. Top-100 queries follow the same pattern, with throughput decreasing as recall increases.

The table below lists the exact search parameters behind each data point, varying the number of probes while keeping epsilon = 1.9 fixed. Together, the figure and table show that even at the 1B-vector scale, VectorChord provides stable and tunable query performance on a single machine.

These results demonstrate that vector query performance can be practical and predictable on a single machine, even at the billion scale.

Summary

VectorChord 1.0.0 demonstrates that real-time vector search can scale cleanly from 1 million to 1 billion vectors without forcing users to change architectures, workflows, or usages. Whether you’re building image search, AI-powered applications, or RAG pipelines, VectorChord is designed to be a reliable vector engine that runs on your own machine, scaling naturally as your data grows.

Ready to scale up? You can get started today, or reach out to us on GitHub or Discord to learn more and get support from the community.

1.2 - How We Made 100M Vector Indexing in 20 Minutes Possible on PostgreSQL

1. Introduction

In the past few months, we’ve heard consistent feedback from users and partners: while our goal of providing a scalable, high-performance alternative to pgvector is well-received, index build time and memory usage remain major concerns at billion-scale.

Now VectorChord can index 100 million 768-dimensional vectors in 20 minutes on a 16 vCPU machine with just 12 GB of memory. By contrast, indexing the same data with pgvector requires around 200 GB of memory and about 40 hours on a 16-core instance. And pgvector with insufficient memory often suffer from page swapping, making builds even slower.

In short, memory usage and build time have become the key barriers to large-scale deployment of vectors. Through a series of targeted optimizations, we reduced build time to 20 minutes and memory usage by 7×, with only minor accuracy trade-offs.

With these improvements, we can now use far cheaper machines with much less memory, without a GPU, while still hosting 100 million 768-dimensional vectors:

In the following sections, we will introduce how we optimized these phases to make index building faster and more memory-efficient. The optimizations are organized as follows: one targets each phase.

2. Background

The index type used in VectorChord, vchordrq is logically a tree of height \(n+1\). The first \(n\) levels of the tree are immutable which serve purely as the routing structure for search. The \((n+1)\)-th level stores all data.

If \(n=1\), the index is a flat, non-partitioned structure. If \(n=2\), it is an inverted file index. If \(n=3\), it has an additional layer.

The index building can be divided into 3 phases: Initialization, insertion and compaction.

• Initialization Phase
  In this phase, top \(n\) levels of the tree are written to the index. Firstly, the index samples vectors in the table. Then the index builds the tree by clustering the samples, the centroids, the centroids of centroids, and so on for \(n\) levels. Finally the tree is written to the index.

• Insertion Phase
  The index inserts vectors from the table into the bottom level of the tree.

• Compaction Phase

  The index converts all the inserted vectors from non-compact layout to compact layout.

3. Making Clustering Faster and More Memory-efficient

In the past, although we can build index for 100 million vectors on small instances, it typically needs a GPU to accelerate clustering.

The main bottleneck in the initialization phase is clustering, which is time-consuming and memory-intensive. In fact, it decides the minimum memory requirement of index building. If we implement clustering on the CPU in a way that is both fast and memory-efficient, it would be practical to build indexes on small instances without large memory and GPUs.

Let \(n\) be the number of vectors, \(c\) be the number of centroids, \(d\) be the dimension of vectors, and \(l\) be the number of iterations. The time complexity of K-means is \(O(ncdl)\), and the space complexity of it is \(O(nd + cd)\). Let \(f\) be the sampling factor, in other words, \(n=fc\). The time complexity of K-means is \(O(fc^2dl)\), and the space complexity of it is \(O(fcd)\).

In the following sections, we will explain how to reduce the complexity, as well as decrease \(d\) and \(f\) for better performance.

Hierarchical K-means

Constrained by time complexity, K-means cannot be improved beyond linear speedup, regardless of the optimizations applied. Even on a GPU, this would take 30 minutes. So we must reduce time complexity.

A simple idea is to divide the samples to multiple disjoint subsets, run K-means on every subset, and then merge the centroids on every subset. To balance the size of these subsets and the number of them, we choose \(\sqrt{c}\) as the number of subsets. In order to generate \(\sqrt{c}\) subsets, we initially perform a small-scale K-means and then assign the \(n\) vectors to \(\sqrt{c}\) disjoint subsets using \(\sqrt{c}\) centroids.

Assuming the subsets are of uniform size, the time complexity of this step is \(O(f\sqrt{c}\sqrt{c}dl) \times \sqrt{c} = O(fc^{1.5}dl)\). If f = 64 and c = 160,000, the algorithm would be roughly 400 times faster.

There is still a small problem here. How many centroids should be computed for a subset? If we ignore the constraint that it must be an integer, it’s \(\frac{n}{|s|}c\). Considering this constraint, this problem is similar to proportional representation, where Sainte-Laguë method is an algorithm that minimizes the average seats-to-votes ratio deviation. It works as follows.

After all the votes have been tallied, successive quotients are calculated for each party. The formula for the quotient is \(\frac{V}{s_i+0.5}\), where \(V\) is the total number of votes that party received, and \(s_i\) is the number of seats that have been allocated so far to that party, initially \(0\) for all parties.

Now clustering on CPU is practical. However, this algorithm does not reduce memory usage.

Dimensionality Reduction

It’s time to review the 140 GB of memory used for K-means samples. It would definitely result in OOM on a machine with memory of 128 GB. Consider the space complexity \(O(fcd)\), we have two ways to reduce memory usage: reduce \(f\), and reduce \(d\).

Let’s reduce \(d\) now. Although it sounds incredible, we can first reduce the dimension of the vectors and then perform clustering without compromising accuracy. Christos’s results show that running K-means on low-dimensional projections can still maintain good accuracy.

Johnson–Lindenstrauss lemma states that a set of points in a high-dimensional space can be embedded into a space of much lower dimension in such a way that distances between the points are nearly preserved. In the classical proof of the lemma, the embedding is a random orthogonal projection.

According to the theorem, \(n\) vectors can be reduced to \(O(\lg n)\) dimensions. Specifically, we only need to construct a random Gaussian matrix, which allows us to reduce high-dimensional vectors to low-dimensional ones using matrix multiplication. Then we perform K-means on it.

Since we need to reduce memory usage, we apply the Johnson-Lindenstrauss transform directly during sampling. In the end, we obtain low-dimensional centroids. We do not attempt to perform an inverse transform; instead, we sample from the table again, find the nearest cluster in the low-dimensional space after the Johnson-Lindenstrauss transform, and thereby recover the high-dimensional centroids.

With dimensionality reduction from 768 to 100, the resident set size of the instance dropped to 23 GB, allowing us to build the index on an i4i.xlarge instance. Additionally, this also results in clustering being 7 times faster, in theory. With hierarchical K-means and dimensionality reduction, the time of initialization phase fell to 24 minutes.

Reducing \(f\) is trivial. It’s configured as build.internal.sampling_factor so we only need to change the configuration. Let’s set \(f\) to \(64\). The resident set size of the instance dropped to 6 GB, and clustering is roughly 2 times faster.

Sampling

In order to perform clustering, we need to sample vectors from the table. Our previous approach, reservoir sampling was reliable but slow. This method is used because we do not know the number of rows in the table without doing a full table scan. However, it would still perform a full table scan.

To avoid a full table scan, we take advantage of PostgreSQL table access method’s sampling interface. The interface takes a function that, given the maximum block number, produces an iterator of block numbers. The interface then returns an iterator over the tuples in those blocks. In order to generate such a random iterator, we can generate an ordered sequence and perform Fisher–Yates shuffle on it, but this consumes memory. In fact, we have a more clever approach. In cryptography, a pseudorandom permutation is a function that cannot be distinguished from a random permutation.

Feistel network could be used as a pseudorandom permutation. It defines as \(L_{i + 1} = R_{i}, R_{i + 1} = L_{i} \oplus F(R_i, K_i)\), where \(F\) is a hash function, \(K_i\) is the random seed. The input of the function is \((L_0, R_0)\), and the output of the function is \((L_n, R_n)\). So it’s a function from \([0, 2^n) \times [0, 2^n)\) to \([0, 2^n) \times [0, 2^n)\). Cleverly, because of \(R_{i} = L_{i + 1}, L_{i} = R_{i + 1} \oplus F(L_{i + 1}, K_i)\), this function is reversible. A reversible function is bijective, so this function is bijective. \([0, 2^n) \times [0, 2^n)\) is equivalent to \([0, 4^n)\), and therefore it’s a permutation of \([0, 4^n)\). Now, by filtering out all elements greater than the maximum block number from this permutation, we get the lazy random permutation we need.

Based on the interface and this function, we implement block sampling, which only needs to access the sampled vectors.

With all these optimizations, the initialization phase takes only 8 minutes in total now.

4. Reducing Contention

In earlier experiments, building the index for the LAION-100m dataset on an Amazon i7i.16xlarge (64 vCPU) instance takes approximately 420 minutes during the insertion phase if \(n=2\) is used, and this is entirely computation-bound.

Starting with version 0.1, VectorChord allows \(n\) to be set to a positive integer no greater than \(8\). From our perspective, it is necessary for billion-scale data. However, at that time, we didn’t actually know how much fast it would be.

After trying \(n=3\) on a smaller instance, i7i.4xlarge (16 vCPU), we observed that the insertion phase completed in just 40–60 minutes. At that point, CPU utilization stayed around 40%, and IO throughput fluctuated between 300 MB/s and 800 MB/s, suggesting a large room for optimization.

Reducing Linked-List Contention

The insertion phase took 40–60 minutes. Surprisingly, our tests showed that 8 workers took 40 minutes, while 16 workers took 55 minutes. This suggests the existence of potential contention among workers during insertion.

In the implementation, the index maintains a single linked list to store full-precision vectors aside from the tree, while the tree only stores quantized vectors. This makes the tree nodes much smaller and allows the tree to fit in memory.

Since changing \(n\) from \(2\) to \(3\), the number of computations of insertion phase has decreased. As a result, inserting vectors into this linked list occurs much more frequently. Parallel workers experience contention when inserting into the list. So more workers actually slow down the insertion, making the performance unpredictable.

To address this, we replaced the single linked list with \(1+k\) linked lists. The first linked list stores full-precision vectors for the top \(n\) levels of the tree, while the other \(k\) lists store vectors for the bottom level. During index build, the \(i\)-th worker inserts vectors into the \((i\ \text{mod}\ k)\)-th list. We set \(k=32\) as the default, and consider it sufficient for most cases.

With this change, CPU utilization stabilizes around 54%, and the insertion phase now completes in about 30 minutes.

Reducing Page Extension Lock Contention

The CPU utilization still suggested that more optimizations were potential. But where exactly was the bottleneck? We started our investigation by checking PostgreSQL worker processes using htop.

Many processes showed waiting on their titles, indicating heavy internal contention inside PostgreSQL. Searching in the code, we traced the source that sets waiting to lock.c. To measure off-CPU time, we turned to offcputime from BCC. Then, with stackcollapse.pl and flamegraph.pl from Brendan Gregg’s FlameGraph, we generated a flame graph for the process’s off-CPU time.

The result was surprising: the culprit was LockRelationForExtension, which acquired the lock of the index for extending it.

Why does acquiring this lock become a bottleneck? Searching through the PostgreSQL mailing list led us to this discussion.

In short, PostgreSQL places a lock on each index to prevent this index from being extended concurrently. But the granularity of this lock is too coarse. Thanks to Andres Freund, a patch was introduced that narrows the critical section and fixes the issue, but it requires the new API available since PostgreSQL 16.

Since VectorChord supports PostgreSQL 13 through 18, we took advantage of the old API in the early development. Unfortunately, that meant we overlooked this optimization.

After switching to the new API, the insertion phase dropped to 22 minutes.

Bulk Page Extensions

However, another round of profiling revealed that the bottleneck remains in the same area.

As the critical section of the lock is already narrowed, we need to speed up the page extension to ease the bottleneck. Extending a file using fallocate is fast on the filesystem. If fallocate is used for extending the index, the average time for extending a page will be shorter. So the question becomes: can we use fallocate to extend the index?

The answer is yes. If the index is extended more than 8 pages at a time, PostgreSQL automatically switches from pwrite to fallocate. By requesting 16 pages at once, we significantly increased the speed of page extension.

With this change, the insertion phase dropped to 9 minutes, CPU utilization stabilized at 90%, and write throughput stayed around 1.8 GB/s. iostat reported IO utilization of 0.75–0.85, indicating we were finally making better use of the resources.

There is still room for improvement. But for now, there is no longer any trivial bottleneck in the insertion phase.

5. Parallelize Compaction

On the bottom level of the tree, quantized vectors exist in two layouts:

• Non-compact layout (insert-oriented): Every quantized vector is stored as a tuple, so data can be appended directly without modifying existing tuples.

• Compact layout (search-oriented): Every 32 quantized vectors are stored as a tuple. It’s optimized for SIMD and makes the search fast.

All vectors are initially inserted in the non-compact layout, and will be converted into the compact layout in the final phase. It is worth noting that this phase is serial and takes about 8 minutes during index build.

Since the other phases have become much faster, optimizing this phase has become more important. So we parallelize this phase. If there are \(k\) workers, and \(m\) nodes in the level \(n\) of the tree, the children of the \(i\)-th node will be compacted by the \((i\ \text{mod}\ k)\)-th worker. Benefiting from parallelism, the compaction phase now takes less than 1 minute.

You may notice that an effective index also requires this compaction occasionally to maintain search performance. PostgreSQL has a vacuum mechanism for this purpose. So this phase is also performed for the indexes routinely in vacuum. Unfortunately, we cannot parallelize it in vacuum: PostgreSQL does not allow an index to use nested parallelism. If the vacuum is parallel, the index could not start parallel workers again.

6. Conclusion

Previously, indexing the LAION-100M dataset with VectorChord 0.5.3 on an Amazon i7i.4xlarge instance was infeasible due to out-of-memory (OOM) failures. Offloading clustering to a GPU made the build possible, yielding a recall of 95.6% at 120 QPS for querying the top 10 results, with a build time of 30 minutes on the GPU and 420 minutes on the i7i.4xlarge.

With the optimizations introduced in VectorChord 1.0.0, the index can now be built entirely on the i7i.4xlarge instance in only 18 minutes, achieving a recall of 94.9% under the same QPS setting.

CREATE INDEX ON laion USING vchordrq (embedding vector_ip_ops) WITH (options = $$
build.pin = 2
[build.internal]
lists = [400, 160000]
build_threads = 16
spherical_centroids = true
kmeans_algorithm.hierarchical = {}
kmeans_dimension = 100
sampling_factor = 64
$$);

Our goal is to make VectorChord one of the best ways to do retrieval on PostgreSQL, from the first prototype to billion‑scale datasets. If you’re already using pgvector, we’d love you to try VectorChord 1.0 on your real workloads and tell us where it helps and where it can do better.

1.3 - Bringing Search‑Engine Ranking to PostgreSQL with VectorChord‑BM25

Modern applications rely on PostgreSQL for its fully ACID‑compliant, expressive SQL, and rich ecosystem of extensions. The database handles relational workloads exceptionally well, but many projects also need to search for large text collections—product descriptions, support tickets, documentation—and present the most relevant rows first. PostgreSQL’s native tools offer a foundation for this, yet their default ranking logic can leave ideal matches buried under less useful results. VectorChord‑BM25 changes that equation by introducing the BM25 relevance‑scoring algorithm directly into PostgreSQL.

What is PostgreSQL?

PostgreSQL is an open‑source, enterprise‑class relational database management system (RDBMS) renowned for reliability, data integrity, and standards compliance. It supports advanced features such as window functions, materialized views, JSONB storage, and a robust extension mechanism that allows developers to add capabilities. Learn more about PostgreSQL here.

Full‑Text Search with tsvector

PostgreSQL has its own tool for basic text search called tsvector. Think of it as a special column that stores the important words (lexemes) from each document in a way the database can search quickly. When you add a GIN or GiST index on that column, PostgreSQL can find rows that match a keyword almost instantly. You write searches with the @@ operator, and the full details are in the PostgreSQL manual here.

Where native ranking falls short

While the ts_rank function can sort matches, its scoring method is basic. Long documents that mention a term once may outrank short documents focused entirely on the query, and rare but important words carry little extra weight. In large datasets, the perceived relevance of results often suffers.

What’s New: VectorChord Adds BM25

VectorChord‑BM25 is a lightweight PostgreSQL extension that augments the existing text‑search stack with the industry‑standard BM25 ranking formula. Instead of exporting data to an external search engine, you can keep everything inside a single database, gaining:

• Ranking that rewards distinctive terms and penalises irrelevant verbosity

• In‑index scoring, reducing query latency

• Seamless SQL workflow—documents remain rows, searches remain queries

What is BM25?

BM25 (Best Matching 25) is a probabilistic retrieval model widely adopted by search engines and academic literature. It evaluates how often a query term appears in a document, how rare that term is across the entire corpus, and how long each document is. The algorithm is discussed in detail on its Wikipedia page, but the core idea is straightforward:

• Term Frequency (TF) – More occurrences of a word in a document increase its relevance but with diminishing returns.

• Inverse Document Frequency (IDF) – Rare words are more informative than common ones.

• Document Length Normalisation – Shorter documents aren’t unfairly penalised when they focus on the query topic.

VectorChord implements BM25 through a new index type and operator (<&>), letting PostgreSQL compute scores while scanning the index.

Two Practical Examples

Below are concise demonstrations that illustrate how to adopt VectorChord‑BM25 with a pre‑trained tokenizer and, for specialised domains, with a custom model.

Example 1: Quick Start with a Pre‑Trained Model

# Spin up a pre‑configured PostgreSQL instance
docker run --name vchord-suite \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  -d tensorchord/vchord-suite:pg17-latest

-- Inside psql
CREATE EXTENSION pg_tokenizer CASCADE;
CREATE EXTENSION vchord_bm25  CASCADE;

-- Register the LLMLingua‑2 tokenizer
SELECT create_tokenizer('llm_tok', $$ model = "llmlingua2" $$);

CREATE TABLE articles (
    id   SERIAL PRIMARY KEY,
    body TEXT,
    emb  bm25vector
);

INSERT INTO articles(body) VALUES
('PostgreSQL is a powerful open‑source database system.'),
('BM25 is a ranking function used by search engines.');

-- Tokenise each row
UPDATE articles SET emb = tokenize(body, 'llm_tok');

-- Build the BM25 index
CREATE INDEX articles_emb_bm25 ON articles USING bm25 (emb bm25_ops);

-- Query with relevance ordering
SELECT id,
       body,
       emb <&> to_bm25query('articles_emb_bm25',
                            tokenize('open source database', 'llm_tok')) AS score
FROM   articles
ORDER  BY score         -- lower (more negative) = higher relevance
LIMIT  10;

The query returns rows already ranked by BM25, producing more intuitive results than the default ts_rank.

Example 2: Custom Model for Domain‑Specific Vocabulary

Domain‑specific text—medical notes, legal briefs, technical logs—often includes jargon absent from general models. VectorChord lets you train a custom tokenizer directly in SQL.

-- 1. Create a text analyzer with Unicode segmentation, lowercasing,
--    stop‑word removal, and stemming.
SELECT create_text_analyzer('tech_analyzer', $$
pre_tokenizer = "unicode_segmentation"
[[character_filters]]
to_lowercase = {}
[[token_filters]]
stopwords = "nltk_english"
[[token_filters]]
stemmer = "english_porter2"
$$);

-- 2. Train a model on your own corpus and set up automatic embedding
SELECT create_custom_model_tokenizer_and_trigger(
    tokenizer_name     => 'tech_tok',
    model_name         => 'tech_model',
    text_analyzer_name => 'tech_analyzer',
    table_name         => 'tickets',
    source_column      => 'issue_text',
    target_column      => 'embedding');

-- 3. Insert support tickets; embeddings are generated via trigger
INSERT INTO tickets(issue_text)
VALUES ('Kubernetes pod fails with ExitCode 137 after OOM kill.'),
       ('Network latency spikes to 250ms during peak hours.');

-- 4. Build an index and query as before
CREATE INDEX tickets_emb_bm25 ON tickets USING bm25 (embedding bm25_ops);

SELECT issue_text,
       embedding <&> to_bm25query('tickets_emb_bm25',
                                  tokenize('OOM kill ExitCode 137', 'tech_tok')) AS score
FROM   tickets
ORDER  BY score
LIMIT  5;

With a tailor‑made vocabulary, the database recognises abbreviations such as “OOM” and “Kubernetes,” yielding more precise rankings for technical support scenarios.

Final Thoughts

VectorChord‑BM25 brings search‑engine quality relevance to the PostgreSQL ecosystem without introducing an additional service layer. By combining flexible tokenization, an efficient BM25 index, and familiar SQL, it enables developers to deliver significantly better search experiences while preserving the operational simplicity of a single database system. If your application relies on PostgreSQL and demands accurate text ranking, VectorChord‑BM25 is well worth exploring.

1.4 - Optimize RAG with Contextual Retrieval in PostgreSQL

In the standard approach to Retrieval Augmented Generation (RAG), a common practice is to break down large documents into smaller, more manageable chunks. This is primarily done to improve the efficiency of the retrieval process, leveraging the sentence embedding and keyword bm25 scoring, allowing systems to quickly find and pull relevant pieces of information. While effective in many scenarios, this method can introduce a significant challenge: the potential for individual chunks to lose their surrounding context.

An example could be from a collection of historical documents. If a user asks about a specific event, and a retrieved chunk says: “The treaty was signed, bringing an end to hostilities.”

Without the surrounding text, this chunk lacks vital context such as which treaty was signed, who the involved parties were, and when or where this significant event took place. Relying solely on this decontextualized snippet would provide an incomplete and potentially inaccurate picture to the LLM, hindering its ability to generate a comprehensive and accurate answer about the historical event.

The example highlights that while chunking aids retrieval efficiency, it can inadvertently strip away the necessary context that gives meaning and relevance to the individual pieces of information. This loss of context can significantly impact the quality and accuracy of the responses generated by LLMs in a RAG system.

To tackle this problem, Anthropic explores a method called Contextual Retrieval.

Basically, it could be treated as the chunk augmentation with the document context.

This article will walk through the steps to improve the retrieval accuracy with the help of contextual information. We use the same prompt, dataset, and metrics described in the anthropic tutorial. Note that we use a different embedding model and LLM in this experiment, so the results will differ. However, this does not reflect the model’s accuracy, as the dataset consists of code rather than natural language.

We mainly use Gemini models to generate the chunk context and text embedding, other models also work. The embedding quality affects the vector retrieval metrics heavily, we recommend checking the leaderboard to find the one that suits your use case. For the keyword tokenization, we use the default BERT tokenizer, you can also try other solutions.

The rerank part is not the main point of this article, if you want to know more about the rerank methods, check our article about the cross-encoder rerank and token-level late interaction rerank. BTW, since we support multivector MaxSim index, the token-level late interaction rerank can be done more efficiently.

Prepare the environment

We provide both vector search and keyword search features in our VectorChord-suite docker image.

docker run --rm -d --name vdb -e POSTGRES_PASSWORD=postgres -p 5432:5432 ghcr.io/tensorchord/vchord-suite:pg17-20250414

We will use the vechord Python library to reduce the boilerplate code and simplify the process.

pip install 'vechord[gemini]'

Define the RAG-related tables

Let’s get started with the basic RAG process.

from vechord.spec import (
    ForeignKey,
    Keyword,
    PrimaryKeyAutoIncrease,
    Table,
    UniqueIndex,
    Vector,
)

DenseVector = Vector[768]

class Document(Table, kw_only=True):
    uid: Optional[PrimaryKeyAutoIncrease] = None
    uuid: Annotated[str, UniqueIndex()]
    content: str

class Chunk(Table, kw_only=True):
    uid: Optional[PrimaryKeyAutoIncrease] = None
    doc_uuid: Annotated[str, ForeignKey[Document.uuid]]
    index: int
    content: str
    vector: DenseVector
    keyword: Keyword

class Query(Table, kw_only=True):
    uid: Optional[PrimaryKeyAutoIncrease] = None
    content: str
    answer: str
    doc_uuids: list[str]
    chunk_index: list[int]
    vector: DenseVector

This includes the vector embedding index and keyword index.

To load the datasets:

from vechord.embedding import GeminiDenseEmbedding
from vechord.registry import VechordRegistry

emb = GeminiDenseEmbedding()
vr = VechordRegistry("anthropic", "postgresql://postgres:postgres@172.17.0.1:5432/")

vr.register([Document, Chunk, Query])

def load_data(filepath: str):
    with open(filepath, "r", encoding="utf-8") as f:
        docs = json.load(f)
        for doc in docs:
            vr.insert(
                Document(
                    uuid=doc["original_uuid"],
                    content=doc["content"],
                )
            )
            for chunk in doc["chunks"]:
                vr.insert(
                    Chunk(
                        doc_uuid=doc["original_uuid"],
                        index=chunk["original_index"],
                        content=chunk["content"],
                        vector=emb.vectorize_chunk(chunk["content"]),
                        keyword=Keyword(chunk["content"]),
                    )
                )

def load_query(filepath: str):
    queries = []
    with open(filepath, "r", encoding="utf-8") as f:
        for line in f:
            query = json.loads(line)
            queries.append(
                Query(
                    content=query["query"],
                    answer=query["answer"],
                    doc_uuids=[x[0] for x in query["golden_chunk_uuids"]],
                    chunk_index=[x[1] for x in query["golden_chunk_uuids"]],
                    vector=emb.vectorize_query(query["query"]),
                )
            )
    vr.copy_bulk(queries)

Evaluation

Now we have everything for the basic RAG process. Let’s define the evaluation metric Pass@k , which means that retrieved top-k chunks contain how many of the groundtruth chunks.

def evaluate(topk=5, search_func=vector_search):
    print(f"TopK={topk}, search by: {search_func.__name__}")
    queries: list[Query] = vr.select_by(Query.partial_init())
    total_score = 0
    start = perf_counter()
    for query in queries:
        chunks: list[Chunk] = search_func(query, topk)
        count = 0
        for doc_uuid, chunk_index in zip(
            query.doc_uuids, query.chunk_index, strict=True
        ):
            for chunk in chunks:
                if chunk.doc_uuid == doc_uuid and chunk.index == chunk_index:
                    count += 1
                    break
        score = count / len(query.doc_uuids)
        total_score += score

    print(
        f"Pass@{topk}: {total_score / len(queries):.4f}, total queries: {len(queries)}, QPS: {len(queries) / (perf_counter() - start):.3f}"
    )

We can try different retrieval strategies like vector search, keyword search, hybrid search with fusion or rerank.

Those strategies can be defined as:

from vechord.rerank import CohereReranker, ReciprocalRankFusion

def vector_search(query: Query, topk: int) -> list[Chunk]:
    return vr.search_by_vector(Chunk, query.vector, topk=topk)

def keyword_search(query: Query, topk: int) -> list[Chunk]:
    return vr.search_by_keyword(Chunk, query.content, topk=topk)

def hybrid_search_fuse(query: Query, topk: int) -> list[Chunk]:
    rrf = ReciprocalRankFusion()
    return rrf.fuse([vector_search(query, topk), keyword_search(query, topk)])[:topk]

def hybrid_search_rerank(query: Query, topk: int, boost=3) -> list[Chunk]:
    ranker = CohereReranker()
    vecs = vector_search(query, topk * boost)
    keys = keyword_search(query, topk * boost)
    chunks = list({chunk.uid: chunk for chunk in vecs + keys}.values())
    indices = ranker.rerank(query.content, [chunk.content for chunk in chunks])
    return [chunks[i] for i in indices[:topk]]

Contextual retrieval

The LLM generates contextual information with the prompt like:

prompt = (
    "<document>\n{whole_document}\n</document>"
    "Here is the chunk we want to situate within the whole document \n"
    "<chunk>\n{chunk}\n</chunk>\n"
    "Please give a short succinct context to situate this chunk within "
    "the overall document for the purposes of improving search retrieval "
    "of the chunk. Answer only with the succinct context and nothing else."
)

The prompt above is a general one designed for retrieval tasks. You may find that a more specialized prompt offers better performance for your specific use case.

This feature is already included in the vechord library.

GeminiAugmenter also supports the prompt caching like Claude, but it requires the cached content to be at least 32768 tokens, which is not the case for this dataset.

from vechord.augment import GeminiAugmenter

class ContextualChunk(Table, kw_only=True):
    uid: Optional[PrimaryKeyAutoIncrease] = None
    doc_uuid: Annotated[str, ForeignKey[Document.uuid]]
    index: int
    content: str
    context: str
    vector: DenseVector
    keyword: Keyword

def load_contextual_chunks(filepath: str):
    augmenter = GeminiAugmenter()

    with open(filepath, "r", encoding="utf-8") as f:
        docs = json.load(f)
        for doc in docs:
            augmenter.reset(doc["content"])
            chunks = doc["chunks"]
            augments = augmenter.augment_context([chunk["content"] for chunk in chunks])
            if len(augments) != len(chunks):
                print(f"augments length not match for uuid: {doc['original_uuid']}, {len(augments)} != {len(chunks)}")
            for chunk, context in zip(chunks, augments, strict=False):
                contextual_content = f"{chunk['content']}\n\n{context}"
                vr.insert(
                    ContextualChunk(
                        doc_uuid=doc["original_uuid"],
                        index=chunk["original_index"],
                        content=chunk["content"],
                        context=context,
                        vector=emb.vectorize_chunk(contextual_content),
                        keyword=Keyword(contextual_content),
                    )
                )

Then, we can expand the strategies like:

def vector_contextual_search(query: Query, topk: int) -> list[ContextualChunk]:
    return vr.search_by_vector(ContextualChunk, query.vector, topk=topk)

def keyword_contextual_search(query: Query, topk: int) -> list[ContextualChunk]:
    return vr.search_by_keyword(ContextualChunk, query.content, topk=topk)

def hybrid_contextual_search_fuse(query: Query, topk: int) -> list[ContextualChunk]:
    rrf = ReciprocalRankFusion()
    return rrf.fuse(
        [vector_contextual_search(query, topk), keyword_contextual_search(query, topk)]
    )[:topk]

def hybrid_contextual_search_rerank(
    query: Query, topk: int, boost=3
) -> list[ContextualChunk]:
    ranker = CohereReranker()
    vecs = vector_contextual_search(query, topk * boost)
    keys = keyword_contextual_search(query, topk * boost)
    chunks = list({chunk.uid: chunk for chunk in vecs + keys}.values())
    indices = ranker.rerank(
        query.content, [f"{chunk.content}\n{chunk.context}" for chunk in chunks]
    )
    return [chunks[i] for i in indices[:topk]]

Benchmark

• topk=5

• topk=10

All the code can be found in our vechord repository.

1.5 - VectorChord: Cost-Efficient Upload & Search of 400 Million Vectors on AWS

In this article, we will describe one method for uploading, indexing, and searching a big dataset of vector data in a cost-efficient manner using the real-world LAION-400M dataset and the VectorChord extension in PostgreSQL.

We’re attempting to show you what kind of hardware setup you need to allow you to search through this vast dataset using VectorChord. We want to make sure that you have a good experience with a search that is both accurate and fast.

VectorChord is a PostgreSQL extension that may bring high-performance vector database capabilities directly into your PostgreSQL database with high compatibility with the popular pgvector extension’s interface where possible.

The version of VectorChord to be used for this tutorial must be v0.3.0 or later. While VectorChord is compatible with most of the recent pgvector versions, pgvector 0.8.0 was extensively tested.

Dataset

The dataset used for this experiment is LAION-400M, which is a dataset with approximately 400 million vectors derived from images. Each vector is 512-dimensional and was generated from a CLIP model.

The total vector data is approximately 400 GB. The vectors have been normalized, so it is feasible to use multiple distance measures (L2, cosine, dot product). To be consistent with other benchmarks on this dataset and following the nature of CLIP embeddings, we employed the cosine distance measure in this experiment.

LAION-400M is divided into 409 chunks, each containing around 1 million vectors. We will process these chunks sequentially for uploading.

Algorithm

Searching through 400 million vectors is a huge challenge, especially as the data size means much of it must live on disk rather than in memory. We need algorithms built for this scale! A popular and effective method for handling large, disk-based vector data is the graph-based DiskANN, known for its Low memory and resource cost.

However, being derived from HNSW, DiskANN also has some inherited disadvantages:

• Slow index building: For large scale datasets, it’s well known that building indexes with DiskANN can take a long time.

• Poor insert performance: For streaming inserted vectors, updating a graph-based index requires traversing the partial graph. This is even more costly because DiskANN’s on-disk graph must be loaded into memory.

VectorChord addresses these problems with its VChordRQ index, which combines a cluster-based index (ivf) with a clever data compression scheme called RabitQ. In addition to better precision and latency in both memory and disk, RabitQ’s strength lies in its strong theoretical guarantee of accuracy, which is different from other compression techniques. This helps us achieve an excellent trade-off between accuracy and efficiency!

Hardware

Based on our experiments, we determined the following cloud instance type to be sufficient to index and query the dataset with acceptable latency:

• Instance Type: AWS EC2 i8g.2xlarge ($501.0720 / month)

• CPU: 8 vCPUs (ARM64)

• RAM: 64GB

• Storage: 1875 GB NVMe SSD

This configuration provides the necessary compute and ample fast local NVMe storage. The 64GB of RAM serves as cache to accelerate search queries.

For the PostgreSQL setup within this instance, we adjusted the shared_buffers parameter:

• Set to 48GB during the index building process.

• Increased to 54GB for search evaluation to allow more data caching for search performance.

Uploading and Indexing

The VectorChord VChordRQ index uses an Index-based Vector File (IVF) structuring. While VChordRQ can compute the required centroids during the index building process, the building of an index of this size (400M vectors) pre-calculating the centroids out of the system significantly accelerates the process of building an index.

For LAION-400M, we performed an external k-means clustering step to determine these centroids. This was done on an instance with an A10 GPU for faster computation.

• The clustering was configured for nlist = 160000 clusters.

• The clustering took approximately 220 seconds per iteration for 25 iterations, totaling about 1.5 hours.

Once we have computed the centroids, we set up the VectorChord-enabled PostgreSQL database. We used a pre-built Docker image for convenience:

-- Mount PGDATA path to the NVMe SSD
-- sudo mount /dev/nvme1n1 /data
docker run --name vchord-pg17 \
-e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -v /data/pg:/var/lib/postgresql/data \
-d tensorchord/vchord-postgres:pg17-v0.3.0

This Docker image contains PostgreSQL 17 with pgvector 0.8.0 and VectorChord 0.3.0 installed.

The core of the upload process involves inserting the pre-computed centroids into a dedicated table and then inserting the vectors from the LAION dataset chunks into the main data table. This can be done efficiently using a client library like psycopg in Python with batched inserts.

First, insert the centroids into a table (e.g., laion_centroids):

-- The centroids table we created is:
-- CREATE TABLE laion_centroids (id SERIAL PRIMARY KEY, vector vector(512));
INSERT INTO laion_centroids (vector) VALUES ('[...centroid vector...]'); -- Insert all 160000 centroids

Then, insert the vectors from the dataset into the data table (e.g., laion):

-- The data table we created is:
-- CREATE TABLE laion (id BIGINT PRIMARY KEY, embedding vector(512));
-- Note: VectorChord is compatible with pgvector's vector insert syntax.
INSERT INTO laion (id, embedding) VALUES (0, '[...vector data...]'); -- Batch inserts for chunks

After inserting all data, we build the VectorChord index (vchordrq). vchordrq stands for RabitQ, which is VectorChord’s highly optimized index type based on the IVF structure combined with quantization and efficient reranking.

-- Note: The metric operator vector_cosine_ops is specified here, similar to pgvector. 
-- The $$ syntax is used to pass build options as a text block.
CREATE INDEX laion_embedding_idx ON laion USING vchordrq (embedding vector_cosine_ops) WITH (options = $$
[build.external]
table = 'public.laion_centroids' -- Link to the pre-computed centroids
$$);

The image below shows the system resource utilization (CPU, memory, and disk usage) on the instance during the upload and indexing process. The disk usage graph shows the data being written (initial steep increase) and the subsequent index building (steady increase), resulting in a final size over 1TB. The CPU and memory graphs indicate the resources consumed throughout this process.

Ground Truth Data

To correctly measure search performance and accuracy, comparing to ground truth is vital. The LAION data set comes without inherent ground truth. We are in debt to the Qdrant team for having published the ground truth data they had prepared for their tutorial on this dataset.

Their methodology involved performing a full-scan nearest neighbor search for the first 100 vectors in the dataset to find the top 50 true nearest neighbors for each query. We used this same exact ground truth file to evaluate the VectorChord search accuracy.

Search Query

VectorChord search employs two main parameters to control the performance-accuracy trade-off: nprobe and epsilon.

• nprobe: Configured using the vchord.probes setting. This parameter determines how many clusters (partitions) of the IVF index are searched in the initial phase. A higher nprobe increases the search scope, potentially finding better results but also increasing latency.

• epsilon: Configured using the vchord.epsilon setting. This parameter controls the reranking precision. After fetching the initial candidates from the nprobed clusters, VectorChord performs a reranking step using more precise vector data. A larger epsilon means more candidates are considered and potentially reranked for higher recall, impacting latency.

These parameters are set as PostgreSQL session variables before executing the search query:

-- Example settings
SET vchord.probes = 100; -- Configure nprobe
SET vchord.epsilon = 0.8; -- Configure epsilon

-- The search query itself uses the standard pgvector distance operator
SELECT id FROM laion ORDER BY embedding <=> '[...query_vector...]' LIMIT 50;

The ORDER BY embedding <=> '[...]' clause leverages the index to find the approximate nearest neighbors based on the specified distance metric (cosine distance, like CLIP). The LIMIT 50 retrieves the top 50 results.

Running Search Requests

After the index built is finished, we ran queries over the 100 ground truth vectors with varying nprobe and epsilon settings to try out performance versus precision trade-offs.

We expressed search latency in milliseconds (ms). “Cold” latency refers to instances where vector data of interest may need to be read from disk, while “warm” latency implies that data will be in memory.

Here are the average latencies and corresponding Precision@50 scores across the 100 queries:

The table clearly illustrates the trade-off:

• Increasing nprobe and epsilon generally leads to higher Precision@50.

• However, this comes at the cost of increased search latency, especially noticeable in cold scenarios where more disk I/O is required to fetch data from more clusters and perform more extensive reranking.

• Warm cache performance is significantly faster, highlighting the importance of sufficient RAM for caching frequently accessed data.

• It is possible to have over 90% precision through warm latencies less than 50ms, and increasing precision above this yields hundreds of milliseconds or even more than a second latency for best recall, depending on the parameters selected.

The graph below shows the trade-off between Queries Per Second (QPS) and Precision@50 for VectorChord (cold and warm cache) alongside Qdrant, with Qdrant’s metrics taken from their published benchmarks on a comparable 8 vCPU, 64 GB machine rather than run on the same hardware.

Conclusion

In this tutorial, we illustrated how VectorChord as an extension to PostgreSQL can be utilized to upload, index, and search a gigantic 400 million vector dataset cost-efficiently on relatively small hardware with fast NVMe SSD.

By using the VectorChord VChordRQ index with external centroids, we were able to handle the scale. Having carefully set nprobe and epsilon at query time provides precise control over the critical trade-off between search speed and accuracy, allowing users to tune the system to their specific needs.

VectorChord’s compatibility with the pgvector interface simplifies adoption for current pgvector users, and its vchordrq index provides powerful capabilities for large-scale vector search directly within the robust and familiar PostgreSQL environment.

1.6 - All-in-one VectorChord Suite: Building Production-Ready RAG Solutions Directly in PostgreSQL

Retrieval-Augmented Generation (RAG) is revolutionizing our interaction with vast datasets and language models. RAG systems offer more precise, contextually aware, and current answers by retrieving pertinent information before generating responses. Nonetheless, constructing robust, scalable, and efficient RAG pipelines poses considerable challenges, particularly for production use.

Many solutions feature complex architectures, using distinct databases for vector search, keyword search, and primary data storage. This commonly results in data synchronization challenges, greater infrastructure complexity, and higher operational costs.

What if you could build a powerful, production-ready RAG system directly within your trusted PostgreSQL database?

Enter the VectorChord Suite, a collection of PostgreSQL extensions designed to bring high-performance vector search, native BM25 ranking, and flexible tokenization capabilities right into Postgres. Let’s explore the components and how they enable next-generation RAG solutions.

What is the VectorChord Suite?

The suite comprises three key PostgreSQL extensions working in concert:

1. VectorChord: The core vector search engine. It’s specifically designed for scalable, high-performance, and disk-efficient vector similarity search within PostgreSQL.

2. VectorChord-bm25: This extension implements the sophisticated BM25 ranking algorithm directly inside PostgreSQL, leveraging efficient Block-WeakAnd algorithms. BM25 is a standard for relevance ranking based on keyword frequency and document characteristics.

3. pg_tokenizer.rs: Provides essential text tokenization capabilities needed for effective full-text search, enabling fine-grained control over how text is processed for full-text search.

By combining these extensions, you unlock powerful capabilities for building advanced RAG systems entirely within PostgreSQL.

How to Use the VectorChord Suite

You can use the tensorchord/vchord-suite image to run multiple extensions which are provided by TensorChord. The image is based on the official Postgres image and includes the following extensions:

docker run   \           
  --name vchord-suite  \
  -e POSTGRES_PASSWORD=postgres  \
  -p 5432:5432 \
  -d tensorchord/vchord-suite:pg17-latest
  # If you want to use ghcr image, you can change the image to `ghcr.io/tensorchord/vchord-suite:pg17-latest`.
  # if you want to use the specific version, you can use the tag `pg17-20250414`, supported version can be found in the support matrix.

CREATE EXTENSION IF NOT EXISTS vchord CASCADE;
CREATE EXTENSION IF NOT EXISTS pg_tokenizer CASCADE;
CREATE EXTENSION IF NOT EXISTS vchord_bm25 CASCADE;
\dx
pg_tokenizer | 0.1.0   | tokenizer_catalog | pg_tokenizer
vchord       | 0.3.0   | public            | vchord: Vector database plugin for Postgres, written in Rust, specifically designed for LLM
vchord_bm25  | 0.2.0   | bm25_catalog      | vchord_bm25: A postgresql extension for bm25 ranking algorithm
vector       | 0.8.0   | public            | vector data type and ivfflat and hnsw access methods

Use Cases for the VectorChord Suite

Use Case 1: Powerful Hybrid Search with Native BM25 and VectorChord

In the RAG era, effective retrieval is paramount. Neither keyword search nor vector search alone is perfect:

• Keyword Search (like BM25): Excels at precision, finding documents with exact keyword matches. It’s great for structured queries and term-specific searches. However, it struggles with synonyms, paraphrasing, and understanding the underlying meaning or semantic intent. (Leverages VectorChord-bm25 and pg_tokenizer).

• Vector Search: Captures deep semantic meaning and relationships between concepts, allowing it to find relevant information even if the exact keywords aren’t present. However, it can sometimes lack precision for queries demanding specific term matches. (Leverages VectorChord).

The Solution: Hybrid Search. By combining the strengths of both approaches within Postgres, the VectorChord Suite bridges this gap. You can run a query that leverages:

• VectorChord-bm25 (powered by pg_tokenizer) for keyword precision.

• VectorChord for semantic understanding.

The results from both can be intelligently combined (e.g., using reciprocal rank fusion - RRF or Model-based rerank) to produce a final ranking that is more accurate, contextually relevant, and semantically aware than either method could achieve alone. This delivers significantly better retrieval performance for your RAG applications.

Use Case 2: Beyond Text - OCR-Free RAG with ColQwen2 & VectorChord

Building RAG systems for documents like PDFs or scanned images often involves cumbersome pre-processing pipelines. Traditional methods rely heavily on Optical Character Recognition (OCR) and layout analysis to extract text. This process can be:

• Slow: OCR can be computationally expensive.

• Error-Prone: OCR accuracy varies significantly depending on document quality.

• Lossy: Crucial visual contexts like tables, figures, formatting, and relative positioning is often lost during text extraction.

The Solution: OCR-Free, Visually-Aware RAG. What if you could query documents based on how they look and their content, without explicit OCR?

This is now achievable by combining:

1. Multi-Modal Vision Language Models (VLMs): Models like ColQwen2 can process images (document pages) and generate embeddings that capture both textual content and visual layout information.

2. VectorChord’s Multi-Vector Capabilities: VectorChord can efficiently store and search multiple vectors per document within Postgres – allowing you to store embeddings representing different aspects (e.g., text content, visual layout).

With this setup, you can query your document database using prompts that reference visual elements (“Find documents with a bar chart comparing sales figures”) or combined textual and visual cues, directly within PostgreSQL. This simplifies your RAG stack, potentially boosts retrieval accuracy by preserving visual context, and eliminates the bottlenecks associated with traditional OCR pipelines.

Why Build RAG in PostgreSQL?

Leveraging the VectorChord Suite within Postgres offers significant advantages:

• Unified Data: Keep your source data, text, metadata, and vector embeddings all in one place.

• Reduced Complexity: Eliminate the need for separate vector databases and synchronization pipelines.

• Leverage Existing Infrastructure: Utilize your existing Postgres expertise, tooling, and operational practices.

• Transactional Integrity: Benefit from PostgreSQL’s robust ACID compliance.

• Rich Ecosystem: Access the wide array of tools and features available within the Postgres ecosystem.

Conclusion

The VectorChord Suite transforms PostgreSQL into a powerful, production-ready platform for building advanced RAG solutions. By integrating high-performance vector search (VectorChord), sophisticated keyword ranking (VectorChord-bm25), and flexible text processing (pg_tokenizer), you can implement cutting-edge techniques like hybrid search and OCR-free multi-modal retrieval directly within your database. Simplify your architecture, enhance retrieval accuracy, and unlock the full potential of RAG with VectorChord in PostgreSQL.

Previous Blog Post

In our previous blog, we shared some user cases where all images can be replaced with VectorChord Suite image.

• Supercharge vector search with ColBERT rerank in PostgreSQL

• Hybrid search with Postgres Native BM25 and VectorChord

• Beyond Text: Unlock OCR-Free RAG in PostgreSQL with Modal & VectorChord

References

• https://vchord.pgsty.com/blog/post/vectorchord-store-400k-vectors-for-1-in-postgresql/

• https://vchord.pgsty.com/blog/post/vectorchord-bm25-revolutionize-postgresql-search-with-bm25-ranking-3x-faster-than-elasticsearch/

• https://vchord.pgsty.com/blog/post/vectorchord-bm25-introducing-pgtokenizera-standalone-multilingual-tokenizer-for-advanced-search/

1.7 - Introducing Vechord: Turn PostgreSQL into your search engine in a Pythonic way.

Today, we’re thrilled to announce the release of Vechord, a new Python library designed to dramatically simplify building robust search infrastructure directly on top of the PostgreSQL database.

In the rapidly evolving world of AI and large language models (LLMs), Retrieval-Augmented Generation (RAG) and semantic search have become crucial components. However, setting up the necessary vector search infrastructure often involves learning new database technologies, managing complex integrations, or wrestling with intricate frameworks. This adds friction and slows down development, especially for teams already comfortable with PostgreSQL.

The Challenge: Hybrid Search Complexity

Building search capabilities often means:

1. Choosing & Managing a Vector Database: Evaluating, deploying, and maintaining specialized vector databases (Pinecone, Weaviate, Milvus, etc.) and text search frameworks (ElasticSearch, Solr, etc.) adds operational overhead.

2. Complex Data Handling: Managing the synchronization between the source data and the vector representations.

3. Steep Learning Curves: Understanding the APIs and abstractions of comprehensive frameworks for a wide range of LLM tasks.

Vechord: The Simple, Pythonic Solution Built on Top of PostgreSQL

Vechord tackles these challenges head-on by leveraging the power and extensibility of PostgreSQL, enhanced with the powerful VectorChord and VectorChord-bm25 extensions. Our core philosophy is simplicity and focus.

Vechord provides a clean, Pythonic interface to:

• Initialize: Easily configure the table schema with Python struct and annotations.

• Ingest Data: Effortlessly add documents, PDFs, or any other type of data with transformation tools.

• Perform Hybrid Search: Efficiently execute the vector similarity search and keyword search, and rerank the retrieval results with the user-friendly API.

• Evaluate Metrics: Evaluate metrics seamlessly, either against ground truth or with LLM-based scoring.

• Makes Simple Tasks Simple: offer an ORM-like interface to select, insert, and delete records from the PostgreSQL database.

How is Vechord Different?

1. Laser Focus on PostgreSQL Vector + Keyword Search: Vechord concentrates specifically on making the PostgreSQL + VectorChord-suite combination easy to use for search. If your primary goal is streamlined vector search and keyword search within your existing PostgreSQL ecosystem, Vechord offers a leaner, more direct path.

2. Library, Not a Full Platform: Vechord is designed as a library – a focused building block that you can integrate into your application code. It gives you the core storage and hybrid search capability on PostgreSQL, leaving the broader application architecture and workflow design entirely up to you.

3. Leveraging Existing Infrastructure: The core premise of Vechord is to empower teams already using PostgreSQL. You don’t need to introduce and manage a separate, dedicated vector database or document database if your scale and requirements are well-served by the VectorChord suite. This reduces operational complexity and cost.

4. Simplicity as a Feature: Vechord prioritizes a minimal API surface and ease of use for its specific task. Vechord aims to get you performing hybrid search on Postgres with minimal boilerplate and cognitive load.

Get Started with Vechord

• Define the table schema

from typing import Annotated, Optional
from vechord.spec import Table, Vector, PrimaryKeyAutoIncrease, ForeignKey, Keyword

# use 768 dimension vector
DenseVector = Vector[768]

class Document(Table, kw_only=True):
    uid: Optional[PrimaryKeyAutoIncrease] = None  # auto-increase id, no need to set
    link: str = ""
    text: str

class Chunk(Table, kw_only=True)
    uid: Optional[PrimaryKeyAutoIncrease] = None
    doc_id: Annotated[int, ForeignKey[Document.uid]]  # reference to `Document.uid` on DELETE CASCADE
    vec: DenseVector  # this comes with a default vector index
    keyword: Keyword  # this comes with a default tokenizer and text index
    text: str

• Inject the data with a Python decorator

import httpx
from vechord.registry import VechordRegistry
from vechord.extract import SimpleExtractor
from vechord.embedding import GeminiDenseEmbedding

vr = VechordRegistry(namespace="test", url="postgresql://postgres:postgres@127.0.0.1:5432/")
# ensure the table and index are created if not exists
vr.register([Document, Chunk])
extractor = SimpleExtractor()
emb = GeminiDenseEmbedding()

@vr.inject(output=Document)  # dump to the `Document` table
# function parameters are free to define since `inject(input=...)` is not set
def add_document(url: str) -> Document:  # the return type is `Document`
    with httpx.Client() as client:
        resp = client.get(url)
        text = extractor.extract_html(resp.text)
        return Document(link=url, text=text)

@vr.inject(input=Document, output=Chunk)  # load from the `Document` table and dump to the `Chunk` table
# function parameters are the attributes of the `Document` table, only defined attributes
# will be loaded from the `Document` table
def add_chunk(uid: int, text: str) -> list[Chunk]:  # the return type is `list[Chunk]`
    chunks = text.split("\n")
    return [Chunk(doc_id=uid, vec=emb.vectorize_chunk(t), keyword=Keyword(t), text=t) for t in chunks]

if __name__ == "__main__":
    add_document("https://paulgraham.com/best.html")  # add arguments as usual
    add_chunk()  # omit the arguments since the `input` is will be loaded from the `Document` table
    vr.insert(Document(text="hello world"))  # insert manually
    print(vr.select_by(Document.partial_init()))  # select all the columns from table `Document`

• Run several steps in a transaction to guarantee data consistency

pipeline = vr.create_pipeline([add_document, add_chunk])
pipeline.run("https://paulgraham.com/best.html")  # only accept the arguments for the first function

• Search by the vector and keyword, rerank with the cross-encoder model

from vechord.rerank import CohereReranker

reranker = CohereReranker()
text = vr.search_by_vector(Chunk, emb.vectorize_query("startup"))
vec = vr.search_by_keyword(Chunk, "startup")
chunks = list({chunk.uid: chunk for chunk in text_retrieves + vec_retrievse}.values())
indices = reranker.rerank(query, [chunk.text for chunk in chunks])
print([chunks[i] for i in indices[:topk]])

Join the Community!

Vechord is open-source and community-driven. We believe it fills a vital gap for developers wanting powerful search capabilities without unnecessary complexity.

• Check out the code on GitHub: https://github.com/tensorchord/vechord

• Read the documentation: https://tensorchord.github.io/vechord/

• Communicate with us on Discord: https://discord.gg/KqswhpVgdU

%[https://github.com/tensorchord/vechord]

1.8 - Beyond Text: Unlock OCR-Free RAG in PostgreSQL with Modal & VectorChord

Building effective Retrieval-Augmented Generation (RAG) systems for documents often feels like wrestling with messy, complex pipelines. Especially when dealing with PDFs or scanned images, traditional methods rely heavily on Optical Character Recognition (OCR) and layout analysis. These steps can be slow, error-prone, and often lose crucial visual context like tables, figures, and formatting. But what if you could query documents based on how they look, not just the extracted text?

This post is your guide to building exactly that: an OCR-free RAG system directly within your familiar PostgreSQL database. We’ll leverage the power of the ColQwen2 Vision Language Model, the efficiency of VectorChord for multi-vector search in Postgres, and the scalability of Modal for GPU-powered embedding generation. Get ready to simplify your RAG stack and potentially boost your retrieval accuracy, all without complex pre-processing.

We’ll cover:

• What ColQwen2 is and why it’s a game-changer.

• How VectorChord makes advanced vector search possible in Postgres.

• A step-by-step tutorial to build and evaluate the system.

What is ColQwen2? The Power of Visual Understanding

To grasp ColQwen2, let’s first look at its foundation: ColPali. As introduced in the paper “ColPali: Efficient Document Retrieval with Vision Language Models”, ColPali represents a novel approach using Vision Language Models (VLMs). Instead of relying on imperfect OCR, it directly indexes documents using their rich visual features – text, images, tables, layout, everything the eye can see.

Think about the limitations of traditional OCR: complex layouts get mangled, tables become gibberish, and images are often ignored entirely. It’s like trying to understand a book by only reading a flawed transcript. ColPali avoids this by using a powerful VLM (originally PaliGemma) to create embeddings that capture the document’s holistic visual nature. Two key concepts make it shine:

1. Contextualized Vision Embeddings: Generating rich embeddings directly from the document image using a VLM.

2. Late Interaction: This clever technique allows the query’s textual meaning to directly interact with the document’s detailed visual features at search time. It’s not just matching text summaries; it’s comparing the query concept against the visual evidence within the document page.

The ColPali architecture (Image from the ColPali paper)

ColQwen2 builds upon this powerful ColPali architecture but swaps the underlying VLM for the more recent Qwen2-VL-2B. It generates ColBERT-style multi-vector representations, capturing fine-grained details from both text and images. As seen on the vidro-leaderboard, ColQwen2 delivers impressive performance with practical model size.

How Does VectorChord Enable ColQwen2 in Postgres?

This is where VectorChord becomes the crucial piece of the puzzle, bringing this cutting-edge VLM capability into your PostgreSQL database. ColQwen2 (and ColPali) relies heavily on those multi-vector representations and the Late Interaction mechanism, specifically requiring an efficient MaxSim (Maximum Similarity) operation.

Calculating MaxSim – finding the highest similarity score between any vector in the query set and any vector in the document set – can be computationally brutal, especially across millions of document vectors. VectorChord tackles this head-on:

• Native Multi-Vector Support: It’s designed from the ground up to handle multi-vector data efficiently within Postgres.

• Optimized MaxSim: Drawing inspiration from the WARP paper, VectorChord uses techniques like dynamic similarity imputation to dramatically speed up MaxSim calculations, making large-scale visual document retrieval feasible.

• Hybrid Search Ready: Beyond multi-vector, it also supports dense, sparse, and hybrid search (check out our previous post!).

• Scalable & Disk-Friendly: Designed for performance without demanding excessive resources.

In short, VectorChord transforms PostgreSQL into a powerhouse capable of handling the advanced vector search techniques required by models like ColQwen2 or ColPali.

Tutorial: Building Your OCR-Free RAG System

Alright, theory’s great, but let’s roll up our sleeves and build this thing! We’ll walk through setting up the environment, processing data using Modal for scalable embedding generation, indexing into VectorChord within Postgres, and finally, evaluating our shiny new OCR-free RAG system.

Prerequisites

Before we start, ensure you have:

• A PostgreSQL instance (Docker recommended) with the VectorChord extension installed OR a VectorChord Cloud cluster.

• A Modal account (free tier available). Modal’s fast GPU provisioning and scaling are perfect for the embedding generation step. To efficiently process a large volume of documents using the ColQwen2 model, it’s crucial to leverage Modal’s rapid startup and GPU expansion features. This approach will significantly reduce the time required for local processing.

If you want to reproduce the tutorial quickly, you can use the tensorchord/vchord-suite image to run multiple extensions that TensorChord provides.

You can run the following command to build and start Postgres with VectorChord-BM25 and VectorChord.

docker run   \           
  --name vchord-suite  \
  -e POSTGRES_PASSWORD=postgres  \
  -p 5432:5432 \
  -d tensorchord/vchord-suite:pg17-latest

CREATE EXTENSION IF NOT EXISTS vchord CASCADE;
CREATE EXTENSION IF NOT EXISTS pg_tokenizer CASCADE;
CREATE EXTENSION IF NOT EXISTS vchord_bm25 CASCADE;
\dx
pg_tokenizer | 0.1.0   | tokenizer_catalog | pg_tokenizer
vchord       | 0.3.0   | public            | vchord: Vector database plugin for Postgres, written in Rust, specifically designed for LLM
vchord_bm25  | 0.2.0   | bm25_catalog      | vchord_bm25: A postgresql extension for bm25 ranking algorithm
vector       | 0.8.0   | public            | vector data type and ivfflat and hnsw access methods

Set up Modal:

$ pip install modal
$ python3 -m modal setup  # click the link to authorize

Step 1: Load the Data (Using Modal Volumes)

We’ll use the ViDoRe Benchmark dataset. To handle this data efficiently across potentially distributed Modal functions, we’ll download it to a Modal Volume. Volumes provide persistent, shared storage ideal for this ‘download once, process many times’ scenario.

image = modal.Image.debian_slim().pip_install("datasets","huggingface_hub","Pillow")
DATASET_DIR = "/data"
DATASET_VOLUME  = modal.Volume.from_name(
    "colpali-dataset", create_if_missing=True
)
app = modal.App(image=image)

@app.function(volumes={DATASET_DIR: DATASET_VOLUME}, timeout=3000)
def download_dataset(cache=False) -> None:
    from datasets import load_dataset
    from tqdm import tqdm
    
    collection_dataset_names = get_collection_dataset_names("vidore/vidore-benchmark-667173f98e70a1c0fa4db00d") 
    for dataset_name in tqdm(collection_dataset_names, desc="vidore benchmark dataset(s)"):
        dataset = load_dataset(dataset_name, split="test",num_proc=10)
        unique_indices = dataset.to_pandas().drop_duplicates(subset="image_filename", keep="first").index #to remove repeating PDF pages with different queries
        dataset = dataset.select(unique_indices)
        dataset.save_to_disk(f"{DATASET_DIR}/{dataset_name}")

Modal offers a straightforward Python API for interacting with its platform. With modal.Image, you can set the base image for the Modal App, and the function decorator helps define the function to be executed within the Modal application. To download the dataset to the Modal Volume, execute the following command. Modal will then automatically build the Docker image and promptly launch the container to run your function.

Run the download function:

$ modal run dataset.py::download_dataset

Modal handles building the environment and running the script in the cloud.

Step 2: Process Data & Generate Embeddings (With Modal & ColQwen2)

This is the heavy lifting: converting document images into ColQwen2 multi-vector embeddings. Doing this locally for many documents would be slow. Modal shines here:

• Easy GPU Access & Autoscaling: Launch a ColQwen2 model service on Modal and use it to generate the image embeddings and query embeddings for the dataset. The decision to implement an HTTP service instead of using the SDK directly was made to ensure seamless auto-scaling during large-scale embedding operations. If you want to deploy the embedding service as a persistent web endpoint, you can directly change modal.cls to modal.web_server for ColPaliServer and running modal deploy.

• Recovery: We checkpoint progress to a separate Modal Volume, allowing us to resume embedding generation if interrupted.

# embedding.py (Illustrative - keep original code)
# ... imports, volumes setup ...
modal_app = modal.App() # Define app

# Function to coordinate embedding generation
@modal_app.function(...)
def embed_dataset(down_scale: float = 1.0, batch_size: int = BATCH_SIZE):
    # ... (logic for loading dataset names, handling checkpoints) ...
    colpali_server = ColPaliServer() # Get handle to our GPU class
    # ... (loop through datasets, batch items, call server for embeddings) ...
    # ... (save embeddings and update checkpoint) ...
    print("Embedding generation complete.")

# server.py (Illustrative - keep original code)
# Class running on GPU to serve embedding requests
@modal_app.cls(gpu=GPU_CONFIG, ...)
class ColPaliServer:
    @modal.enter()
    def load_model_and_start_server(self):
        # ... (Load ColQwen2 model, start internal FastAPI server) ...
        self.client = httpx.AsyncClient(...) # Client to talk to internal server

    @modal.exit()
    def shutdown_server(self):
        # ... (Cleanup) ...

    # Method called by embed_dataset function
    @modal.method()
    async def embed_images(self, images: List[str]) -> np.ndarray:
        # ... (Prepare batch, send request to internal server via self.client) ...
        # ... (Decode response using msgspec for speed) ...
        return embeddings_numpy_array

    # ... (Potentially add embed_queries method too) ...

# colpali.py
class ColPaliModel:
    def __init__(self, model_name: str = "vidore/colqwen2-v1.0", cache_dir: str="/model"):
        # ...
        if self.model_name == "vidore/colqwen2-v1.0":
            from colpali_engine.models import ColQwen2, ColQwen2Processor
            from transformers.utils.import_utils import is_flash_attn_2_available
            
            # load model
            model = ColQwen2.from_pretrained(
                self.model_name,
                torch_dtype=torch.bfloat16,
                device_map="cuda:0", 
                attn_implementation="flash_attention_2" if is_flash_attn_2_available() else None,
                cache_dir=self.cache_dir,
            ).eval()

            colpali_processor = ColQwen2Processor.from_pretrained(
                self.model_name,
                cache_dir=self.cache_dir,
            )
    # ... functions for embedding images and queries
    # @modal.method()
    # async def batch_embed_images(self, images_base64: List[str]) -> np.ndarray:
    # @modal.method()
    # async def batch_embed_queries(self, queries: List[str]) -> np.ndarray:

Generate the embeddings:

$ modal run embedding.py::embed_dataset

Step 3: Create Index in VectorChord (Using Vechord SDK)

With embeddings generated and saved in a Modal Volume, we first need to bring them local.

$ modal volume get colpali-embedding-checkpoint /path/to/local/vidore_embeddings

Now, we’ll use the vechord SDK to load these embeddings into our PostgreSQL database and create the necessary multi-vector index. vechord provides a Pythonic, ORM-like way to interact with VectorChord in Postgres.

%[https://github.com/tensorchord/vechord]

MultiVector = List[Vector[128]] # Assuming 128 dimensions for ColQwen2

lists = 2500 # lists is the number of the cluster
# Define the database table schema using vechord
class Image(Table, kw_only=True):
    uid: Optional[PrimaryKeyAutoIncrease] = None
    image_embedding: Annotated[MultiVector, MultiVectorIndex(lists=lists)] # Stores the document's visual embeddings 
    query_embedding: Annotated[MultiVector, MultiVectorIndex(lists=lists)]  # Stores the query's embeddings 
    query: str = None
    dataset: Optional[str] = None
    dataset_id: Optional[int] = None

# Connect to your PostgreSQL database
# Ensure the URL points to your local Docker or VectorChord Cloud instance
DB_URL = "postgresql://postgres:postgres@127.0.0.1:5432/postgres" # Default DB
vr = VechordRegistry("colpali", DB_URL)
vr.register([Image]) # Creates the table and multi-vector index if they don't exist

# Function to load embeddings from disk and yield Image objects
@vr.inject(output=Image)
def load_image_embeddings(path: str) -> Iterator[Image]:
    # ... (logic to load numpy arrays from disk, convert to List[Vector]) ...
    # ... (yield Image(...) instances) ...
    print(f"Loaded embeddings from {path}")

if __name__ == "__main__":
    # Path where you downloaded the embeddings from Modal
    embedding_dir = "/path/to/local/vidore_embeddings"
    load_image_embeddings(embedding_dir) # This triggers vechord to insert data
    print("Data loaded and indexed into VectorChord.")

Run the indexer script, vechord handles table creation, index creation, and data insertion.

Step 4: Evaluation - Does it Work?

The final step is crucial: evaluating retrieval performance. Does this OCR-free system deliver accurate results, and can it be fast? We’ll use vechord to run queries against our indexed data and look at NDCG@10 and Recall@10. We’ll also test the impact of VectorChord’s WARP optimization, which accelerates MaxSim calculations. In this tutorial, we will use vidore/arxivqa_test_subsampled dataset for evaluation queries, the data are shown below.

TOP_K = 10

# Define structure for results (optional but good practice)
class Evaluation(msgspec.Struct):
    map: float
    ndcg: float
    recall: float

TOP_K = 10
def evaluate(queries: list[Image], probes: int, max_maxsim_tuples: int) -> list[Evaluation]:
    result  = []
    for query in queries:
        vector = query.query_embedding
        docs: list[Image] = vr.search_by_multivec(
            Image, vector, topk=TOP_K, probe=probes, max_maxsim_tuples=max_maxsim_tuples
        )
        score = BaseEvaluator.evaluate_one(query.uid, [doc.uid for doc in docs])
        result.append(Evaluation(
            map=score.get("map"),
            ndcg=score.get("ndcg"),
            recall=score.get(f"recall_{TOP_K}"),
        ))
    return result

if __name__ == "__main__":
    # Select some queries from the benchmark dataset stored in the DB
    # Example: Get 100 queries from a specific subset
    test_queries: list[Image] = vr.select_by(
        Image.partial_init(dataset="vidore/arxivqa_test_subsampled"), limit=100
    )

    # ... optimize the param ...
    res: list[Evaluation] = evaluate(queries, probes=probes, maxsim_refine=maxsim_refine)
    print("ndcg@10", sum(r.ndcg for r in res) / len(res))
    print("recall@10", sum(r.recall for r in res) / len(res))
    print(f"Total execution time: {total_time:.4f} seconds")

Running the evaluation script yields impressive results:

# Disable WARP
ndcg@10 0.8615
recall@10 0.92
Total execution time: 810 seconds # Baseline accuracy and time

# Enable WARP
# There is no need to focus on specific times, 
# only relative times need to be taken into account, 
# as the tests were performed on a local model with poor performance.
ndcg@10 0.8353
recall@10 0.90
Total execution time: 41 seconds # Dramatic WARP Speed Boost

Analysis:

• High Baseline Accuracy: First, let’s look at the baseline performance without WARP optimization. The system achieves an excellent NDCG@10 of 0.8615 and Recall@10 of 0.92. This confirms the fundamental effectiveness of ColQwen2 embeddings paired with VectorChord’s precise MaxSim search, delivering state-of-the-art results for visual document retrieval even without speed optimizations.

• Dramatic WARP Speed Boost: Now, observe the impact of enabling VectorChord’s WARP optimization. The total execution time plummets from a substantial 810 seconds down to just 41 seconds! This represents a massive ~18.7x speedup for the evaluation query set. This clearly demonstrates WARP’s power in dramatically accelerating the computationally intensive MaxSim operations required for late-interaction models like ColQwen2.

• Minimal Accuracy Trade-off: Impressively, this significant speed gain comes at the cost of only a very minor dip in retrieval accuracy. The NDCG@10 slightly decreases to 0.8353 (a difference of less than 0.3), and Recall@10 reduces minimally to 0.9 (a difference of only 0.02).

Conclusion: Visual RAG Made Simpler in Postgres

In this tutorial, we successfully built a high-performance, OCR-free RAG system by bringing together the visual understanding capabilities of ColQwen2, the scalable multi-vector search of VectorChord within PostgreSQL, and the efficient GPU processing of Modal. We saw how this stack allows us to directly query documents based on their visual content, bypassing the pitfalls of traditional OCR pipelines.

The ability to seamlessly integrate this visual dimension into your RAG system directly within Postgres opens up exciting possibilities for applications dealing with visually rich documents like scientific papers, invoices, product manuals, historical archives, and much more.

Ready to try it yourself?

• Explore the Code: https://github.com/xieydd/vectorchord-colqwen2

• Dive Deeper into VectorChord: Check out the VectorChord documentation or try the hassle-free VectorChord Cloud.

• Experiment: Try different VLMs or datasets.

• Share Your Thoughts: Let us know your experiences or questions in the comments below!

This approach represents a significant step towards simpler, more robust, and visually-aware document retrieval systems.

References

• https://huggingface.co/vidore/colqwen2-v1.0

• https://huggingface.co/blog/manu/colpali

• https://blog.vespa.ai/scaling-colpali-to-billions/

1.9 - 3 Billion Vectors in PostgreSQL to Protect the Earth

Monitoring the health of our planet is one of the most critical challenges of our time. Our planet faces immense environmental pressures, and understanding these changes requires sifting through an unimaginable amount of satellite data – petabytes of pixels captured constantly. From tracking deforestation in the Amazon to identifying illegal mining operations in remote regions or monitoring agricultural impacts on sensitive ecosystems, turning this data into actionable intelligence is paramount.

Earth Genome’s Earth Index platform is tackling this challenge head-on. It now offers global coverage, making the entire land surface of our planet searchable using the power of AI. This isn’t just a technical achievement; it’s a new tool designed to empower environmental stewardship by providing accessible, actionable intelligence from satellite data.

%[https://www.earthgenome.org/]

We at VectorChord are incredibly proud that our PostgreSQL vector search extension is playing a crucial role in powering this ambitious and vital mission.

What is Earth Index Doing? Turning Pixels into Planetary Insights

Imagine finding a specific type of small-scale environmental impact, like an unreported quarry or a new patch of deforestation, somewhere within satellite imagery covering millions of square kilometers. Traditionally, analyzing this data required significant expertise and resources, making tracking specific threats across vast, remote areas difficult, especially for conservation groups, journalists, and regulators with limited budgets. The animation below illustrates how efficiently Earth Index pinpoints the illegal narcotrafficking airstrips in the Amazon, akin to finding a toothpick on a soccer field.

But how does Earth Index make finding environmental features of interest possible on a global scale? The magic lies in a cutting-edge approach using AI foundation models trained via contrastive learning and the resulting vector embeddings.

Here’s a glimpse into their innovative process:

1. Tiling the Globe: Earth Index divides the Earth’s entire land surface into over 3.2 billion overlapping tiles, each roughly 10 hectares.

2. Learning the “Planetary DNA” with Contrastive Self-Supervision: Earth Index leverages breakthroughs in AI, specifically foundation models trained for Earth Observation (EO) using self-supervised, contrastive learning methods (like the DINO objective). The foundation model (e.g., an SSL4EO Vision Transformer) learns the fundamental visual language of satellite imagery by distinguishing between pairs of images from the same location (positive pairs) and pairs from different locations (negative pairs). This happens across diverse global data (like Harmonized Landsat Sentinel-2, Sentinel-1 radar) without needing explicit human labels for features like “forest” or “city.”

3. Generating Vector Embeddings: Once pre-trained, this model acts as an encoder. Imagery for each tile is fed into it, producing a vector embedding – a rich numerical representation or fingerprint capturing the tile’s essential visual and structural characteristics in a highly compressed format (>10,000x compression). This high-dimensional vector acts like a unique “Planetary DNA” for that piece of land.

4. Creating a Searchable Planet: These 3.2 billion vector embeddings form the core of Earth Index. They need to be stored in a specialized system optimized for rapidly finding vectors that are numerically similar to each other. Users can provide an example of what they’re looking for (e.g., by clicking on a known illegal mine or a specific type of agricultural infrastructure on the map), and Earth Index searches this massive dataset to find other locations across the globe (or within a specific region) with similar vector fingerprints.

Real-World Impact: Earth Index in Action for Environmental Protection

This technological foundation translates directly into powerful tools for understanding and protecting our environment. It empowers journalists (like those at Mongabay and Radio Free Europe), indigenous communities, conservation organizations (like the World Bank), and researchers to uncover hidden environmental changes, monitor industrial footprints, and gather evidence for conservation efforts with unprecedented ease and speed. The figure shows the shrimp farms in the Gulf of Fonseca.

Here are some examples of how Earth Index is being used:

• Combating Deforestation and Illegal Activities: In collaboration with Mongabay, Earth Index was used to pierce through dense canopy cover and identify hidden, illegal narcotrafficking airstrips in the Amazon. Similarly, the platform can rapidly locate illegal gold mining scars, like those devastating parts of the Yanomami Indigenous Territory, helping to quantify the scale of the problem and guide enforcement efforts.

• Monitoring Resource Extraction: Radio Free Europe utilized Earth Index to identify over 1300 quarries across the Balkans, revealing that roughly half lacked proper permits, highlighting potential environmental damage and unregulated extraction. This approach can monitor fracking wellpads, rubber processing plants, or other industrial facilities.

• Protecting Water Quality and Ecosystems: Earth Index can locate Concentrated Animal Feeding Operations (CAFOs) near sensitive waterways to assess runoff risks or identify expansions of shrimp farming into vital mangrove ecosystems.

• Tracking Agricultural Expansion: The tool helps monitor the conversion of natural habitats into agricultural land by searching for patterns associated with new clearings or specific crop types.

The Infrastructure Challenge: Searching Billions of Vectors Affordably

Generating 3.2 billion high-dimensional vectors is one challenge; hosting and searching them efficiently presents another significant hurdle. Earth Index needed a solution that could:

• Handle Massive Scales: Searching billions of vectors requires specialized indexing and querying capabilities.

• Integrate Seamlessly with Geospatial Data: Earth Index heavily relies on geospatial operations (like filtering results within specific administrative boundaries or proximity to rivers). This made PostgreSQL with PostGIS a natural and essential choice for their core database. Standalone vector databases often lack the rich geospatial features required.

• Maintain Performance: Users need results relatively quickly for the platform to be effective for investigation and monitoring.

• Control Costs: Storing and searching billions of vectors, especially if requiring vast amounts of RAM for purely in-memory indexes, can become prohibitively expensive for many organizations, particularly non-profits.

Earth Index needed a vector search solution that lived within their chosen PostgreSQL environment, could scale massively, perform well, and wouldn’t break the bank on hardware costs.

Why PostgreSQL Rocks for Planetary-Scale Vectors

While specialized vector databases exist, Earth Index faced a critical need: seamlessly integrating vector similarity search with rich geospatial data and operations. This is where PostgreSQL, combined with extensions, truly shines:

• Unified Data Platform: PostgreSQL, especially with the powerful PostGIS extension, allowed Earth Index to keep their vector embeddings alongside their crucial geospatial metadata (tile locations, administrative boundaries, proximity to features like rivers). This avoids the complexity and potential synchronization issues of managing separate databases for vector search and relational/geospatial data.

• Leveraging the Power of SQL & PostGIS: Earth Index could combine vector similarity searches with standard SQL filters and complex PostGIS spatial queries (e.g., “find vectors similar to this example within this specific national park boundary” or “near this river system”) all within a single query interface. Standalone vector databases often lack this mature geospatial query capability.

• Maturity and Ecosystem: Building on PostgreSQL means inheriting its decades of development, renowned stability, robust tooling, and wide community support – essential for a mission-critical platform like Earth Index.

However, searching 3.2 billion vectors efficiently requires more than just standard PostgreSQL capabilities.

Why Earth Index Chose VectorChord for Performance and Affordability

While the PostgreSQL ecosystem provided the ideal foundation, achieving the required performance and affordability at the scale of 3.2 billion vectors presented a significant challenge. Earth Index needed a solution that could deliver speed without breaking the bank.

Finding the right balance of performance, cost, and integration was challenging. Hutch Ingold, CTO of Earth Genome, explains their considerations:

When we evaluated solutions for our 3.2 billion vectors – recognizing the rapidly evolving tech landscape – standard pgvector didn’t meet our performance requirements at that scale. Dedicated options like Qdrant posed difficulties: hosted versions were too expensive, and self-hosting clustered deployments on our Kubernetes infrastructure seemed insufficiently mature at the time. We also examined cloud services, such as Google’s Vertex AI Vector Search, but the list price of $237,000 per month for our dataset was simply prohibitive.

This is where VectorChord became the enabling technology:

• Performance Beyond Vanilla PGVector: VectorChord delivers the necessary speed enhancements – up to 5x faster queries, 16x higher insert throughput, and 16x quicker index building – addressing the performance limitations encountered with standard pgvector at this massive scale.

• Affordable Scale via Disk-Based Indexing: Critically, VectorChord’s optimized disk-based indexing (leveraging cost-effective SSDs) directly counters the prohibitive costs highlighted by the CTO. It avoids the massive RAM requirements of purely in-memory approaches and the exorbitant monthly fees of managed cloud vector services, making the 3.2 billion vector index financially feasible.

• Native Integration & Scalability: As a PostgreSQL extension, VectorChord provided these benefits within their chosen database, requiring no infrastructure changes. It’s built to scale efficiently, handling the billions of vectors needed for Earth Index’s global monitoring platform while maintaining interactive performance.

VectorChord provided the crucial combination of high performance, massive scalability, and cost-effectiveness directly within the familiar and powerful PostgreSQL environment that Earth Index needed.

Earth Index: Technical Architecture and Setup

To manage its planetary dataset effectively and affordably, Earth Index built its backend on a cluster of three powerful AWS EC2 i8g.16xlarge instances, powered by the latest Graviton4 ARM processor and Nitro SSD. Each provides 512 GB RAM, 64 vCPUs, and 15 TB of NVMe storage, offering a solid foundation for both computation and disk I/O. This setup, costing around $12,000 per month, delivers massive cost savings compared to the estimated $237,000 monthly fee for a comparable managed cloud vector service, making the project economically viable. These instances run PostgreSQL enhanced with VectorChord and PostGIS, efficiently hosting both the vector data and other application components.

The core data resides in a partitioned table structure across the three nodes. The primary table, globe24, stores each tile’s unique ID, its geographic point geometry (geometry(Point, 4326)), a partition key (partkey), and the crucial 384-dimensional AI-generated embedding stored efficiently as halfvec(384). This sharding distributes the 3.2 billion rows across the cluster, allowing for parallel processing.

A key advantage of this architecture is the ability to perform complex, integrated queries. Earth Index commonly executes SQL statements that seamlessly combine VectorChord’s fast vector search (embedding <=> '{vector}'::vector) with PostGIS geospatial filtering (st_within(geom, '{geom}')) and partition pruning (partkey IN (...)). This allows users to find visually similar locations within specific geographic boundaries efficiently. Even with billions of vectors, this integrated approach yields practical performance, achieving a median query latency (p50) of approximately 761 ms for typical searches returning the top 500 results.

Enabling Environmental Action at Scale

By providing a cost-effective, scalable, and tightly integrated vector search solution within PostgreSQL, VectorChord helps enable Earth Index’s groundbreaking work. It allows them to manage their enormous “Planetary DNA” database affordably, ensuring that this powerful tool for environmental monitoring can reach the organizations and communities who need it most.

Join the Earth Index waitlist today to explore the environmental challenges in your neighborhood, understand your ecological footprint, and be part of a global effort to protect the planet.

We are thrilled to see Earth Index leverage VectorChord to turn satellite data into actionable environmental intelligence. Their success demonstrates the power of combining cutting-edge AI, robust database technology, and efficient vector search to address critical global challenges.

Interested in bringing large-scale vector search to your PostgreSQL database? Learn more about VectorChord or get started today!

%[https://github.com/tensorchord/VectorChord]

1.10 - Vector Search Over PostgreSQL: A Comparative Analysis of Memory and Disk Solutions

Introduction: real-world challenges

Specialized vector databases aren’t always needed. With PostgreSQL extensions like pgvector, pgvectorscale, VectorChord (which evolved from pgvecto.rs)****, you get vector search plus relational power—no extra infrastructure. But adoption isn’t seamless. Here’s what real users say:

• The query takes 30 seconds for the first time and 100ms if it’s a repeat query. The problem is very slow IO.

• Is it normal to take 3 hours to build the index for 12 million rows with 75 dimension vector?

• After 13 hours of building index for 200 million vectors of 75 dimensions each, I have noticed a heavy memory usage at 100GB/124GB at 30% of building index, and server will eventually crash.

• We’re hitting some challenges, particularly around need for frequent index updates, throughput constraints, lack of parallel index scans and no built-in quantization.

In this article, we’ll take a deep dive into these questions and help you make informed decisions by understanding not only the “what” but also the “why” behind these tools. Let’s explore how to navigate these complexities and choose the right solution for your needs.

What do we value most in a vector database?

In PostgreSQL, shared_buffers is a crucial memory area acting as the primary cache for data pages, including table data and indexes. By storing frequently accessed vector index blocks in this memory buffer, PostgreSQL significantly reduces slow disk I/O. When a query needs data, PostgreSQL first checks shared_buffers. If the data is present, it’s retrieved quickly without disk access. If not, the data is fetched from disk and loaded into shared_buffers for future queries. To configure this, use ALTER SYSTEM SET shared_buffers = 'xGB' in psql and restart the server.

Let’s say you have some vectors in a table, after the index is built, it’s time to start a query. Even though you have set enough shared_buffers for PostgreSQL, it still needs to read the index from disk for the first query. However, for the repeated query, since the vector can be retrieved directly from shared_buffers, it can be much faster.

While the first query after a cold start or index change matters, sustained performance often depends on how quickly queries execute once the ‘hot’ or frequently accessed parts of the index are cached in memory (shared_buffers), mimicking the repeated query scenario. Therefore, optimizing for this cached performance is crucial for many real-world applications.

In this blog, we will compare the performance of vector similarity search extensions in these situations:

• Query performance when memory is sufficient

• Query performance when memory is low and disk becomes a bottleneck

We will also discuss other aspects related to index building, such as:

• Index build speed: How long does it take to build the index? If I have multiple cores in the instance, can it use them all to speed things up?

• Memory usage: How much memory should be reserved for index creation? This is the most important consideration when choosing an instance.

• Disk usage: How many vectors can be hosted on a given amount of disk space? This is also a consideration when choosing an instance that uses non-expandable NVMe SSD (a type of high-performance solid state drive) storage.

• Ease of use: Can I use a simple CREATE INDEX command to create an index? How difficult is it to tune for query performance?

Finally, we will discuss the insertion performance after index building is complete. This is important if the user has streamed data that will trigger frequent index updates.

Experimental setup

Here we present information about the hardware and datasets used in all of the following experiments.

In this table, we show the version of all the extensions we use in these experiments:

Vectors in memory: for small scale data

For a vector search system, if you want the best performance, it’s important to make sure that the entire index is cached in shared_buffers, which is more appropriate on a memory-optimized instance. In this situation, the size of shared_buffers should ideal be large enough to hold the entire index.

For our experiment, we choose a medium-sized dataset, LAION-5m, which consists of 5 million vectors with 768 dimensions. To host LAION-5m in shared_buffers, we can use the AWS instance I4I Extra Large, with a 937GB NVMe SSD and 32GB of memory.

On this machine, we set the shared_buffers to 24GB and measure the performance of the first query and then the repeated query. The following is the result of the repeated query performance over pgvector, pgvectorscale and VectorChord.

The following graphs illustrate the trade-off between query speed (QPS) and search quality (Recall@10/100). Recall@10 measures the percentage of true nearest neighbors found within the top 10 results. In general, higher QPS can be achieved at the cost of lower recall, and vice versa. A better system may achieve higher recall at the same QPS, or higher QPS at the same recall level. Our goal is typically to maximize QPS while maintaining a high recall target (e.g., 95%).

To measure first query performance, we cleared shared_buffers before running the 10,000 test queries sequentially. Although the buffer starts empty, index/data pages loaded into the cache by earlier queries within the same test run can sometimes be reused by subsequent queries. This can cause the instantaneous QPS to increase during the run. We report the average QPS across all 10,000 queries to provide a representative measure of performance with a cold cache.

In conclusion, VectorChord consistently achieves higher QPS at high recall levels (e.g., >95% Recall@10) compared to the other extensions. When it comes to the other extensions, pgvectorscale performs better for first query, but once data is cached, pgvector will outperform it.

Vectors in disk: for large scale data

There are times when things are different. For billions of vectors, it is not possible to use memory to store the entire index on a single machine. When the size of the vectors to be read exceeds the size of memory, the speedup effect of shared_buffers is greatly reduced, and most of the data is fetched from disk.

While shared_buffers becomes less effective, the operating system’s page cache can still provide some caching benefits, although its performance is generally lower than shared_buffers.

To illustrate this situation, we can host LAION-5m on a much smaller machine, C6ID Large, with 4GB of memory and set shared_buffers to 2GB. This configuration ensures that memory will be insufficient and most queries will be forced to go to disk.

In these cases, the difference in performance between the first and repeated queries will be much smaller due to insufficient memory. Therefore, it’s acceptable to evaluate only the repeated query performance of each extension.

As always, VectorChord consistently achieves higher QPS at high recall levels (e.g., >95% Recall@10) compared to the other extensions, followed by pgvector and pgvectorscale with similar QPS.

Experience in index building

As we discussed earlier, users also care about some index building metrics: Index build time, memory usage, disk usage and usability. We measured these metrics during the CREATE INDEX step of the above experiment on the Laion-5m dataset:

• Core utilization: Percentage of available CPU cores effectively used during index creation. Higher values indicate better parallelism.

• Index build time: The time taken by the SQL command to build the index. If the index build requires an additional step (VectorChord external build), this should also be included.

• Memory usage: Peak memory usage during the index build, including the 1G shared_buffers allocated for PostgreSQL.

• Disk usage: The size of PGDATA on disk after index building is finished, including vectors and the index.

All query performance is measured after an external build, on a standalone machine with A10 GPU. If you insist on using the same I4I Extra Large instance for external builds, the index build time will increase to 50 minutes, which is still competitive.

In this case, we set shared_buffers to 1GB and logged the peak memory usage of each extension. Both VectorChord's internal build and VectorChord's external build are set to 25 iters for K-means clustering. For ease of use, pgvector, pgvectorscale and VectorChord's internal build have similar syntax with a single SQL to build the index, while VectorChord's external build is more complicated.

For most aspects, VectorChord's external build gained a leading position, but this partly sacrificed the ease of use, as developers need to implement an additional K-means clustering step outside of PostgreSQL and manage the resulting centroids.

If you want to start with a simple CREATE INDEX command for a straightforward approach, then VectorChord's external build may not be the best choice , you can turn to other extensions / methods:

• Choose VectorChord's internal build for a faster index build

• Choose pgvector if you really don’t have enough memory

• Choose pgvectorscale to reduce disk requirements or get better capacity

Insertion performance: for stream-generated data

With streaming vector data, insertion speed can become a new concern. In real-time data analysis systems, the number of new vectors written per second can be very high. Each time new vectors are added to an index, the index itself must be updated. This capability is usually referred to as insertion performance.

So, we set up an experiment to see how well the insertion worked. After building the index for 5 million vectors, we insert another 100,000 random generated vectors into the table one after the other and calculate the insertion time of all.

As you can see, VectorChord (1565 Insert/Sec) performs much better than pgvector (246 Insert/Sec) and pgvectorscale (107 Insert/Sec) when inserting data. For most workloads, 100 Insert/Sec is more than enough. However, if you find that a lot of vectors are continuously inserted in your production (for example, 500 Insert/Sec), this could be a problem.

Summary

Based on the results above, this table shows the differences between the three extensions in various aspects. We can see that the main advantage of VectorChord is concentrated in performance, while pgvector and pgvectorscale have their own advantages in terms of usability and capacity.

If you want to see more detailed differences, this table shows them between the three extensions in various aspects. We can see that the main advantage of VectorChord is concentrated in performance, while pgvector and pgvectorscale have their own advantages in terms of usability and capacity.

Want to know more about how VectorChord compares to other extensions? In future blogs, we will explore the differences of PostgreSQL vector extensions in more scenarios, such as bm25 hybrid search (combining traditional keyword search with vector similarity search) and filtered vector search. Ready to try VectorChord for your vector search needs? Download the extension and feel free to join our Discord community or contact us at support@tensorchord.ai.

%[https://github.com/tensorchord/VectorChord]

1.11 - PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

You might have come across discussions or blog posts suggesting that PostgreSQL’s built-in full-text search (FTS) struggles with performance compared to dedicated search engines or specialized extensions. A notable recent example comes from Neon’s blog post, “Performance Benchmark: pg_search on Neon” (link).

In their benchmark, Neon compared query performance on their database platform with their pg_search extension (based on Rust’s Tantivy library via pgrx) against the Postgres built-in fulltext search setting with tsvector and GIN index. They commendably stated they optimized this standard setup by adding GIN indexes where appropriate (benchmark code available here).

However, while adding GIN indexes is a necessary first step, their results showing significantly slower performance for the “standard” setup suggest crucial additional optimization steps for PostgreSQL FTS were likely missed. The conclusion that standard FTS is inherently much slower than pg_search might be based on an unintentionally handicapped baseline.

Let’s dive into how to correctly set up and use standard PostgreSQL FTS for optimal performance, addressing the specific configuration flaws likely present in the baseline used in the Neon benchmark, and demonstrating the true speed of built-in FTS. We’ll show concrete numbers demonstrating a ~50x performance increase just by applying these standard optimizations to the baseline configuration.

The Benchmark Setup (Recap from Neon’s Analysis)

The analysis used a table structure similar to this with 10 million log entries:

CREATE TABLE benchmark_logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    country VARCHAR(255),
    severity INTEGER,
    timestamp TIMESTAMP,
    metadata JSONB
);

They tested various query types. Many involved searching the message text field, using queries structured like this in their “standard Postgres” examples:

-- Create index on to_tsvector('english', message)
CREATE INDEX message_gin ON benchmark_logs USING gin (to_tsvector('english', message));
-- Example problematic query structure (likely used in Neon's baseline)
SELECT country, COUNT(*)
FROM benchmark_logs
WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')
GROUP BY country
ORDER BY country;

Even with a GIN index present on message, this query structure and the likely default GIN index settings are where the performance issues for the standard FTS baseline begin.

Our Test Environment Details (Replicating the Baseline Scenario)

To demonstrate the impact of proper optimization on standard FTS, we used the following setup:

• Instance: AWS EC2 i7ie.xlarge with local NVMe SSD (minimizing I/O impact).

• CPU: 4 vCPUs.

• PostgreSQL Configuration: PostgreSQL 16 via Docker, configured for the hardware:

  docker run -d \
    --name my-postgres \
    --network=host \
    -v /mnt/localssd/pgdata:/var/lib/postgresql/data \
    -e POSTGRES_PASSWORD=mysecretpassword \
    postgres:latest \
    -c shared_buffers=8GB \
    -c maintenance_work_mem=8GB \
    -c max_parallel_workers=4 \
    -c max_worker_processes=4

• Parallelism Note: This setup provides max_parallel_workers_per_gather = 2. The Neon post mentioned their test environment used 8 parallel workers due to larger instances, implying potentially higher parallelism (max_parallel_workers_per_gather up to 8) than our setup. Our optimized results for standard FTS were achieved with less query parallelism.

Mistake #1: Calculating tsvector On-the-Fly (Major issue)

The sample queries shown in the Neon blog (and common in basic FTS examples) calculate the tsvector within the WHERE clause:

WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')

This forces PostgreSQL to:

1. Perform Expensive Computation: Run to_tsvector() (parsing, stemming, etc.) repeatedly for many rows during query execution.

2. Limit Index Efficiency: Prevent the most direct and efficient use of the GIN index, even if one exists on the base message column.

The Fix (For a Proper Standard FTS Baseline): Pre-calculate and store the tsvector.

1. Add a tsvector column:

   ALTER TABLE benchmark_logs ADD COLUMN message_tsvector tsvector;

2. Populate the column:

   UPDATE benchmark_logs SET message_tsvector = to_tsvector('english', message);

3. Index the tsvector column (with fastupdate=off): (As shown in Fix #1)

4. Rewrite queries:

   -- Optimized standard FTS query
   SELECT country, COUNT(*)
   FROM benchmark_logs
   WHERE message_tsvector @@ to_tsquery('english', 'research') -- Use the indexed column!
   GROUP BY country
   ORDER BY country;

Mistake #2 : Ignoring GIN Index fastupdate (Minor issue)

While Neon’s benchmark correctly identified GIN as the index type for standard FTS, it likely used the default setting: fastupdate=on.

• fastupdate=on (Default): Prioritizes index update speed by using pending lists. This significantly slows down searches over time, especially on large, static datasets used for benchmarks, as both the main index and growing pending lists must be scanned. It also contributes to index bloat and less efficient page structure.

• fastupdate=off: Prioritizes search speed. Index updates are slower, but the resulting index is more compact and significantly faster to query as there are no pending lists to scan. Essential for read-heavy workloads or benchmarking search performance.

For a fair comparison focused on search speed, the standard FTS baseline should have used fastupdate=off.

The Fix (For a Proper Standard FTS Baseline):

-- Create the GIN index correctly for search speed
CREATE INDEX idx_gin_logs_message_tsvector
ON benchmark_logs USING GIN (message_tsvector) -- Index the dedicated tsvector column
WITH (fastupdate = off);

The Performance Impact: A ~50x Speedup for Standard FTS

What happens when we apply these standard optimizations to the standard PostgreSQL FTS setup? We tested a query in Neon’s examples on the 10-million-row dataset:

• Unoptimized Standard FTS (Neon’s Baseline): ~41301 ms (41.3 seconds)

• Optimized Standard FTS (Our Fixes): ~877 ms (0.88 seconds)

This demonstrates a ~50x speed improvement for standard PostgreSQL FTS achieved simply by applying well-established best practices. This transforms the baseline performance dramatically, suggesting the comparison point used in the Neon benchmark may not have represented the true potential of built-in FTS. This speedup was achieved even with potentially less query parallelism available than in Neon’s tests.

The Grain of Truth: Ranking (ts_rank) Performance

Neon’s analysis did likely highlight a valid point regarding ranking speed. Functions like ts_rank or ts_rank_cd can be relatively slow in standard PostgreSQL compared to just finding matches. This is because ranking requires fetching and processing data for all matching rows before limiting, which can be I/O and CPU intensive.

Beyond Standard FTS: Advanced Ranking with VectorChord-BM25

While standard FTS excels at finding matches quickly, if sophisticated, high-performance ranking is critical, specialized tools are often required. Instead of relying solely on built-in functions, consider VectorChord-BM25.

VectorChord-BM25 is a PostgreSQL extension specifically designed for fast, relevance-based ranking using the well-regarded BM25 algorithm. BM25 goes beyond simple term matching, estimating relevance based on term frequency within a document and inverse document frequency across the entire collection.

Key advantages reported for VectorChord-BM25 include:

• High Performance: Designed for speed, reportedly outperforming other solutions like Elasticsearch (up to 3x faster) and potentially pg_search for ranking tasks.

• BM25 Algorithm: Implements a standard, effective relevance ranking model.

• Specialized Indexing: Uses a dedicated bm25 index type (leveraging optimizations like Block WeakAnd) which is crucial for both performance and calculating the global statistics needed for BM25 scoring.

• Dedicated Data Type: Introduces a bm25vector type to store tokenized representations suitable for BM25.

If your application heavily relies on the quality and speed of relevance ranking, and standard ts_rank proves insufficient, VectorChord-BM25 presents a powerful, integrated alternative within PostgreSQL.

Conclusion: Standard FTS is Faster Than You Might Think

Standard PostgreSQL FTS is a highly capable and performant feature for finding documents when correctly optimized using tsvector columns and properly configured GIN indexes (with fastupdate=off for reads). Benchmarks suggesting otherwise may be comparing against unoptimized baselines.

For advanced relevance ranking where standard ts_rank falls short, specialized extensions like VectorChord-BM25 offer significant performance improvements by implementing algorithms like BM25 with dedicated data types and optimized indexing strategies.

Choose the right tool for the job: optimize standard FTS first, and if high-performance relevance ranking is paramount, explore dedicated extensions built for that purpose.

%[https://github.com/tensorchord/VectorChord-bm25]

1.12 - VectorChord-BM25: Introducing pg_tokenizer—A Standalone, Multilingual Tokenizer for Advanced Search

We’re excited to announce the release of VectorChord-BM25 version 0.2, our PostgreSQL extension designed to bring advanced BM25-based full-text search ranking capabilities directly into your database!

VectorChord-BM25 allows you to leverage the power of the BM25 algorithm, a standard in information retrieval, without needing external search engines. This release marks a significant step forward, focusing heavily on enhancing the core text processing component: tokenization, unlocking greater flexibility and significantly improved multilingual support.

The Big News: Introducing pg_tokenizer.rs

The cornerstone of VectorChord-BM25 0.2 is a completely refactored and decoupled tokenizer extension: pg_tokenizer.rs.

Why the change? We realized that tokenization – the process of breaking down text into meaningful units (tokens) – is incredibly complex and highly dependent on the specific language and use case. Supporting multiple languages with their unique rules, custom dictionaries, stemming, stop words, and different tokenization strategies within a single monolithic extension was becoming cumbersome.

By moving the tokenizer into its own dedicated project (pg_tokenizer.rs) under the permissive Apache License, we achieve several key benefits:

1. Modularity & Flexibility: Developers can now use or customize the tokenizer independently of the core BM25 ranking logic.

2. Easier Contribution: The focused nature of pg_tokenizer.rs makes it simpler for the community to contribute new language support, tokenization techniques, or custom filters.

3. Faster Iteration: We can now develop and release improvements to the tokenizer more rapidly without needing a full VectorChord-BM25 release cycle.

4. Enhanced Customization: Users gain significantly more control over how their text, regardless of language, is processed before ranking.

What’s New in Tokenization (Thanks to pg_tokenizer.rs)

This new architecture enables several powerful features in v0.2:

• Expanded Language Support: Directly handle diverse linguistic needs with dedicated tokenizers like Jieba (Chinese) and Lindera (Japanese), alongside powerful multilingual LLM-based tokenizers (like Gemma2 and LLMLingua2) trained on vast datasets covering a wide array of languages.

• Richer Tokenization Features: You now have more granular control over the tokenization pipeline:

  • Custom Stop Words: Define your own lists of words to ignore during indexing and search.

  • Custom Stemmers: Apply stemming rules for various supported languages or even define custom ones.

  • Custom Synonyms: Define synonym lists to treat different words as equivalent (e.g., “postgres”, “postgresql”, “pgsql”).

  • Language-Specific Options: Leverage fine-grained controls available within specific tokenizers (like Lindera or Jieba) when needed.

Show Me the Code!

Let’s see how easy it is to use the new tokenizer features.

1. Using a Pre-trained Multilingual LLM Tokenizer (LLMLingua2)

LLM-based tokenizers are great for handling text from many different languages.

-- Enable the extensions (if not already done)
CREATE EXTENSION IF NOT EXISTS vchord_bm25;
CREATE EXTENSION IF NOT EXISTS pg_tokenizer;

-- Update search_path for the first time
ALTER SYSTEM SET search_path TO "$user", public, tokenizer_catalog, bm25_catalog;
SELECT pg_reload_conf();

-- Create a tokenizer configuration using the LLMLingua2 model
SELECT create_tokenizer('llm_tokenizer', $$
model = "llmlingua2"
$$);

-- Tokenize some English text
SELECT tokenize('PostgreSQL is a powerful, open source database.', 'llm_tokenizer');
-- Output: {2795,7134,158897,83,10,113138,4,9803,31344,63399,5} -- Example token IDs

-- Tokenize some Spanish text (LLMLingua2 handles multiple languages)
SELECT tokenize('PostgreSQL es una potente base de datos de código abierto.', 'llm_tokenizer');
-- Output: {2795,7134,158897,198,220,105889,3647,8,13084,8,55845,118754,5} -- Example token IDs

-- Integrate with a table
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    passage TEXT,
    embedding bm25vector
);

INSERT INTO documents (passage) VALUES ('PostgreSQL is a powerful, open source database.');

UPDATE documents
SET embedding = tokenize(passage, 'llm_tokenizer')
WHERE id = 1; -- Or process the whole table

2. Creating a Custom Tokenizer with Filters (Example: English)

This example defines a custom pipeline, including lowercasing, Unicode normalization, skipping non-alphanumeric tokens, using NLTK English stop words, and the Porter2 stemmer. It then automatically trains a model and sets up a trigger to tokenize text on insert/update.

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding bm25vector
);

-- Define a custom text analysis pipeline
SELECT create_text_analyzer('english_analyzer', $$
pre_tokenizer = "unicode_segmentation"  # Basic word splitting
[[character_filters]]
to_lowercase = {}                       # Lowercase everything
[[character_filters]]
unicode_normalization = "nfkd"          # Normalize Unicode
[[token_filters]]
skip_non_alphanumeric = {}              # Remove punctuation-only tokens
[[token_filters]]
stopwords = "nltk_english"              # Use built-in English stopwords
[[token_filters]]
stemmer = "english_porter2"             # Apply Porter2 stemming
$$);

-- Create tokenizer, custom model based on 'articles.content', and trigger
SELECT create_custom_model_tokenizer_and_trigger(
    tokenizer_name => 'custom_english_tokenizer',
    model_name => 'article_model',
    text_analyzer_name => 'english_analyzer',
    table_name => 'articles',
    source_column => 'content',
    target_column => 'embedding'
);

-- Now, inserts automatically generate tokens
INSERT INTO articles (content) VALUES
('VectorChord-BM25 provides advanced ranking features for PostgreSQL users.');

SELECT embedding FROM articles WHERE id = 1;
-- Output: {1:1, 2:1, 3:1, 4:1, 5:1, 6:1, 7:1, 8:1}
-- Bm25vector based on the custom model and pipeline

3. Using Jieba for Chinese Text

CREATE TABLE chinese_docs (
    id SERIAL PRIMARY KEY,
    passage TEXT,
    embedding bm25vector
);

-- Define a text analyzer using the Jieba pre-tokenizer
SELECT create_text_analyzer('jieba_analyzer', $$
[pre_tokenizer.jieba]
# Optional Jieba configurations can go here
$$);

-- Create tokenizer, custom model, and trigger for Chinese text
SELECT create_custom_model_tokenizer_and_trigger(
    tokenizer_name => 'chinese_tokenizer',
    model_name => 'chinese_model',
    text_analyzer_name => 'jieba_analyzer',
    table_name => 'chinese_docs',
    source_column => 'passage',
    target_column => 'embedding'
);

-- Insert Chinese text
INSERT INTO chinese_docs (passage) VALUES
('红海早过了，船在印度洋面上开驶着。'); -- Example sentence

SELECT embedding FROM chinese_docs WHERE id = 1;
-- Output: {1:1, 2:1, 3:1, 4:1, 5:1, 6:1, 7:1, 8:1, 9:1, 10:1, 11:1, 12:1}
-- Bm25vector based on Jieba segmentation

(For full examples, including custom stop words and synonyms, please refer to the pg_tokenizer.rs documentation.)

Understanding the Tokenizer Configuration

The new tokenizer system revolves around two core concepts:

1. Text Analyzer: Defines how raw text is processed into a sequence of tokens. It consists of:

   • character_filters: Modify text before splitting (e.g., lowercasing, Unicode normalization).

   • pre_tokenizer: Splits the text into initial tokens (e.g., based on Unicode rules, Jieba, Lindera).

   • token_filters: Modify or filter tokens after splitting (e.g., stop word removal, stemming, synonym replacement).

2. Model: Defines the mapping from the processed tokens to the final integer token IDs used by BM25. Models can be:

   • pre-trained: Use established vocabularies and rules (like bert-base-uncased, llmlingua2). Great for general purpose and multilingual use.

   • custom: Build a vocabulary dynamically from your own data, tailored specifically to your corpus and language(s).

You can define these components separately or inline them when creating a tokenizer using a simple TOML configuration format passed as a string in SQL.

Get Started with VectorChord-BM25 0.2!

This release significantly boosts the flexibility and power of VectorChord-BM25, especially for users dealing with multiple languages or needing fine-grained control over text processing.

• GitHub Repository: https://github.com/tensorchord/VectorChord-bm25

• Tokenizer Documentation: https://vchord.pgsty.com/bm25/tokenizer/

We encourage you to try out version 0.2 and explore the new tokenization capabilities. Your feedback is invaluable – please report any issues or suggest features on our GitHub repository.

Upgrade your PostgreSQL full-text search today with the enhanced multilingual flexibility of VectorChord-BM25 0.2 and pg_tokenizer.rs!

1.13 - Hybrid search with Postgres Native BM25 and VectorChord

In the era of RAG (Retrieval-Augmented Generation), efficiently retrieving relevant data from vast datasets is crucial for businesses and developers. Traditional keyword-based retrieval methods, such as those utilizing BM25 as a scoring mechanism, excel in ranking documents based on term frequency and exact keyword matches. This makes them highly effective for structured queries and keyword-heavy content. However, they struggle with understanding synonyms, contextual nuances, and semantic intent. Conversely, vector-based retrieval captures deep semantic meaning, enabling better generalization across varied queries but sometimes sacrificing precision in exact keyword matches. Hybrid Search bridges this gap by combining BM25’s precision with vector search’s contextual understanding, delivering faster, more accurate, and semantically aware results.

This article explores how to implement Hybrid Search in Postgres using VectorChord-bm25 for keyword-based retrieval and VectorChord for semantic search. By leveraging VectorChord-bm25’s BM25 ranking with the Block-WeakAnd algorithm and VectorChord’s advanced vector similarity capabilities, you can build a robust search system that seamlessly integrates keyword precision with semantic understanding. Whether you’re building a recommendation engine, a document retrieval system, or an enterprise search solution, this guide will walk you through the steps to unlock the full potential of hybrid search.

All related benchmark codes can be found here.

Hybrid Search Explained

Hybrid Search merges the results from vector search and keyword search. Vector search is based on the semantic similarity between the query and the documents, while keyword search relies on the BM25 ranking algorithm. However, it is important to note that BM25 itself is only responsible for scoring documents, while the broader keyword search process also includes steps such as tokenization, indexing, and query parsing. Hybrid Search utilizes reranker or fuses the results from both methods. Fuses include RRF (Reciprocal Rank Fusion) or weighted scoring for multiple recall strategies. Model-based rerank includes cross encoder models like bge-reranker-v2-m3 and multi-vector representation model ColBERT.

%[https://github.com/tensorchord/VectorChord]

What is the VectorChord?

You may be familiar with pgvector, an open-source vector similarity search extension for Postgres. However, scalable or performance-critical scenarios may require a more advanced vector search solution. VectorChord is an excellent choice.

Check out the blog post “VectorChord: Store 400k Vectors for $1 in PostgreSQL” to learn more about its motivation and design.

%[https://github.com/tensorchord/VectorChord-bm25]

What is VectorChord-BM25

VectorChord-BM25 is a PostgreSQL extension for keyword search. It not only implements BM25 ranking but also includes a tokenizer and a Block-WeakAnd index to improve speed.

What is BM25?

BM25 (Best Matching 25) is a probabilistic ranking function used in information retrieval to assess how well a document matches a query. It calculates relevance scores based on term frequency (TF) and inverse document frequency (IDF), while also applying document length normalization. The formula ensures that terms appearing frequently within a document (TF) and those rare across the corpus (IDF) are appropriately weighted, improving search accuracy and relevance. IDF measures how often a word appears across the document collection. The fewer the occurrences, the higher its value. TF represents the frequency of a specific word from the query appearing in the given document. A higher TF indicates a stronger relevance between the query and the document.

Why a Postgres native BM25 Ranking implementation is needed?

In our previous blog post, VectorChord-BM25: Revolutionizing PostgreSQL Search with BM25 Ranking — 3x Faster Than Elasticsearch, my colleague Allen has already provided a detailed and sufficient explanation, which can be referred to.

Tutorial: Use VectorChord-BM25 and VectorChord implement Hybrid Search

In this tutorial, we will guide you through the steps to implement Hybrid Search using VectorChord-BM25 and VectorChord in PostgreSQL. We will cover the following topics:

• Semantic Search with VectorChord

• Keyword Search with VectorChord-BM25

• Rerank

Prerequisites

1. Postgres with VectorChord-BM25 and VectorChord

If you want to reproduce the tutorial quickly, you can use the tensorchord/vchord-suite image to run multiple extensions that TensorChord provides.

You can run the following command to build and start Postgres with VectorChord-BM25 and VectorChord.

docker run   \           
  --name vchord-suite  \
  -e POSTGRES_PASSWORD=postgres  \
  -p 5432:5432 \
  -d tensorchord/vchord-suite:pg17-latest

CREATE EXTENSION IF NOT EXISTS vchord CASCADE;
CREATE EXTENSION IF NOT EXISTS pg_tokenizer CASCADE;
CREATE EXTENSION IF NOT EXISTS vchord_bm25 CASCADE;
\dx
pg_tokenizer | 0.1.0   | tokenizer_catalog | pg_tokenizer
vchord       | 0.3.0   | public            | vchord: Vector database plugin for Postgres, written in Rust, specifically designed for LLM
vchord_bm25  | 0.2.0   | bm25_catalog      | vchord_bm25: A postgresql extension for bm25 ranking algorithm
vector       | 0.8.0   | public            | vector data type and ivfflat and hnsw access methods

2. Prepare the Embedding Model and Data

For embedding, we can use a pre-trained embedding model like BGE-M3 to generate embeddings for the documents. BGE-M3 is a high-quality embedding model known for its versatility in multi-functionality, multi-linguality, and multi-granularity.

For validation, we use the BEIR dataset, a heterogeneous benchmark for information retrieval. It is easy to use and allows you to evaluate your models across 15+ diverse IR datasets.

First, you need to load your data into PostgreSQL.Then you can use the following SQL query to generate embeddings for the documents.

with self.conn.cursor() as cursor:
    cursor.execute(
        f"CREATE TABLE IF NOT EXISTS {self.dataset}_corpus (id TEXT, text TEXT, emb vector({self.vector_dim}), bm25 bm25vector);"
    )

    for did, doc in tqdm(zip(doc_ids, docs), desc="insert corpus"):
        emb = self.sentence_encoder.encode_doc(doc)
        cursor.execute(
            f"INSERT INTO {self.dataset}_corpus (id, text, emb) VALUES (%s, %s, %s)",
            (did, doc, emb),
        )