a curated list of database news from authoritative sources

September 21, 2025

Franck Pachot

MongoDB Search Index Internals With Luke (Lucene Toolbox GUI Tool)

Previously, I demonstrated MongoDB text search scoring with a simple example, creating a dynamic index without specifying fields explicitly. You might be curious about what data is actually stored in such an index. Let's delve into the specifics. Unlike regular MongoDB collections and indexes, which use WiredTiger for storage, search indexes leverage Lucene technology. We can inspect these indexes using Luke, the Lucene Toolbox GUI tool.

Set up a lab

I started a Atlas local deployment to get a container for my lab:

# download Atlas CLI if you don't have it. Here it is for my Mac:
wget https://www.mongodb.com/try/download/atlascli
unzip mongodb-atlas-cli_1.43.0_macos_arm64.zip

# start a container
bin/atlas deployments setup  atlas --type local --port 27017 --force

Sample data

I connected with mongosh and created a collection, and a search index, like in the previous post:

mongosh --eval '

db.articles.deleteMany({});

db.articles.insertMany([
 { description : "🍏 🍌 🍊" },                // short, 1 🍏
 { description : "🍎 🍌 🍊" },                // short, 1 🍎
 { description : "🍎 🍌 🍊 🍎" },             // larger, 2 🍎
 { description : "🍎 🍌 🍊 🍊 🍊" },          // larger, 1 🍎
 { description : "🍎 🍌 🍊 🌴 🫐 🍈 🍇 🌰" },  // large, 1 🍎
 { description : "🍎 🍎 🍎 🍎 🍎 🍎" },       // large, 6 🍎
 { description : "🍎 🍌" },                 // very short, 1 🍎
 { description : "🍌 🍊 🌴 🫐 🍈 🍇 🌰 🍎" },  // large, 1 🍎
 { description : "🍎 🍎 🍌 🍌 🍌" },          // shorter, 2 🍎
]);

db.articles.createSearchIndex("default",
  { mappings: { dynamic: true } }
);

'

Get Lucene indexes

While MongoDB collections and secondary indexes are stored by the WiredTiger storage engine (by default, in the /data/db directory), the text search indexes use Lucene in a mongot process (with files stored by default in /data/mongot). I copied it to my laptop:

docker cp atlas:/data/mongot ./mongot_copy
cd mongot_copy

One file is easy to read, as it is in JSON format, and it is the metadata listing the search indexes, with their MongoDB configuration:

cat configJournal.json | jq

{
  "version": 1,
  "stagedIndexes": [],
  "indexes": [
    {
      "index": {
        "indexID": "68d0588abf7ab96dd26277b1",
        "name": "default",
        "database": "test",
        "lastObservedCollectionName": "articles",
        "collectionUUID": "a18b587d-a380-4067-95aa-d0e9d4871b64",
        "numPartitions": 1,
        "mappings": {
          "dynamic": true,
          "fields": {}
        },
        "indexFeatureVersion": 4
      },
      "analyzers": [],
      "generation": {
        "userVersion": 0,
        "formatVersion": 6
      }
    }
  ],
  "deletedIndexes": [],
  "stagedVectorIndexes": [],
  "vectorIndexes": [],
  "deletedVectorIndexes": []
}

The directory where Lucene files are stored has the IndexID in their names:

ls 68d0588abf7ab96dd26277b1*

_0.cfe _0.cfs _0.si
_1.cfe _1.cfs _1.si
_2.cfe _2.cfs _2.si
_3.cfe _3.cfs _3.si
_4.cfe _4.cfs _4.si
_5.cfe _5.cfs _5.si
_6.cfe _6.cfs _6.si
segments_2
write.lock

In a Lucene index, each .cfs/.cfe/.si set represents one immutable segment containing a snapshot of indexed data (with .cfs holding the actual data, .cfe its table of contents, and .si the segment’s metadata), and the segments_2 file is the global manifest that tracks all active segments so Lucene can search across them as one index.

Install and use Luke

I installed the Lucene binaries and started Luke:

wget https://dlcdn.apache.org/lucene/java/9.12.2/lucene-9.12.2.tgz
tar -zxvf lucene-9.12.2.tgz
lucene-9.12.2/bin/luke.sh

This starts the GUI asking for the index directory:

The "Overview" tab shows lots of information:

The field names are prefixed with the type. My description field was indexed as a string and named $type:string/description. There are nine documents and nine different terms:

The Lucene index keeps the overall frequency in order to apply inverse document frequency (IDF). Here, 🍎 is present in eight documents and 🍏 in one.

The "Document" tab lets us browse the documents and see what is indexed. For example, 🍏 is present in one document with { description: "🍏 🍌 🍊" }:

The flags IdfpoN-S mean that it is a fully indexed text field with docs, frequencies, positions, and offsets, with norms and stored values.

The "Search" tab allows us to run queries. For example, { $search: { text: { query: ["🍎", "🍏"], path: "description" }, index: "default" } } from my previous post is:

This is exactly what I got from MongoDB:

db.articles.aggregate([
  { $search: { text: { query: ["🍎", "🍏"], path: "description" }, index: "default" } },
  { $project: { _id: 0, score: { $meta: "searchScore" }, description: 1 } },
  { $sort: { score: -1 } }
]).forEach( i=> print(i.score.toFixed(3).padStart(5, " "),i.description) )

1.024 🍏 🍌 🍊
0.132 🍎 🍎 🍎 🍎 🍎 🍎
0.107 🍎 🍌 🍊 🍎
0.101 🍎 🍎 🍌 🍌 🍌
0.097 🍎 🍌
0.088 🍎 🍌 🍊
0.073 🍎 🍌 🍊 🍊 🍊
0.059 🍎 🍌 🍊 🌴 🫐 🍈 🍇 🌰
0.059 🍌 🍊 🌴 🫐 🍈 🍇 🌰 🍎

If you double-click on a document in the result, you can get the explanation of the score:

For example, the score of 🍏 🍌 🍊 is explained by:

1.0242119 sum of:
  1.0242119 weight($type:string/description:🍏 in 0) [BM25Similarity], result of:
    1.0242119 score(freq=1.0), computed as boost * idf * tf from:
      1.89712 idf, computed as log(1 + (N - n + 0.5) / (n + 0.5)) from:
        1 n, number of documents containing term
        9 N, total number of documents with field
      0.5398773 tf, computed as freq / (freq + k1 * (1 - b + b * dl / avgdl)) from:
        1.0 freq, occurrences of term within document
        1.2 k1, term saturation parameter
        0.75 b, length normalization parameter
        3.0 dl, length of field
        4.888889 avgdl, average length of field

The BM25 core formula is score = boost × idf × tf.

IDF is idf = log(1 + (N - n + 0.5) / (n + 0.5)) where n is the number of documents containing 🍏 , so 1, and N is the total documents in the index for this field, so 9.

TF is tf = freq / ( freq + k1 × (1 - b + b × (dl / avgdl)) ) where freq is the term occurrences in this doc’s field, so 1, k1 is the term saturation parameter, which defaults to 1.2 in Lucene, b is the length normalization, which defaults to 0.75 in Lucene, dl is the document length, which is three tokens here, and avgdl is the average document length for this field in the segment—here, 4.888889.

Daily usage in MongoDB search also allows boosting via the query, which multiplies into the BM25 scoring formula (boost).

The "Analysis" tab helps explain how strings are tokenized and processed. For example, the standard analyzer explicitly recognized emojis:

Finally, I inserted 500 documents with other fruits, like in the previous post, and the collection-wide term frequency has been updated:

The scores reflect the change:

The explanation of the new rank for 🍏 🍌 🍊 is:

3.2850468 sum of:
  3.2850468 weight($type:string/description:🍏 in 205) [BM25Similarity], result of:
    3.2850468 score(freq=1.0), computed as boost * idf * tf from:
      5.8289456 idf, computed as log(1 + (N - n + 0.5) / (n + 0.5)) from:
        1 n, number of documents containing term
        509 N, total number of documents with field
      0.5635748 tf, computed as freq / (freq + k1 * (1 - b + b * dl / avgdl)) from:
        1.0 freq, occurrences of term within document
        1.2 k1, term saturation parameter
        0.75 b, length normalization parameter
        3.0 dl, length of field
        5.691552 avgdl, average length of field

After adding 500 documents without 🍏, BM25 recalculates IDF for 🍏 with a much larger N, making it appear far rarer in the corpus, so its score contribution more than triples.

Notice that when I queried for 🍎🍏, no documents contained both terms, so the scoring explanation included only one weight. If I modify the query to include 🍎🍏🍊, the document 🍏 🍌 🍊 scores highest, as it combines the weights for both matching terms, 🍏 and 🍊:

4.3254924 sum of:
  3.2850468 weight($type:string/description:🍏 in 205) [BM25Similarity], result of:
    3.2850468 score(freq=1.0), computed as boost * idf * tf from:
      5.8289456 idf, computed as log(1 + (N - n + 0.5) / (n + 0.5)) from:
        1 n, number of documents containing term
        509 N, total number of documents with field
      0.5635748 tf, computed as freq / (freq + k1 * (1 - b + b * dl / avgdl)) from:
        1.0 freq, occurrences of term within document
        1.2 k1, term saturation parameter
        0.75 b, length normalization parameter
        3.0 dl, length of field
        5.691552 avgdl, average length of field
  1.0404456 weight($type:string/description:🍊 in 205) [BM25Similarity], result of:
    1.0404456 score(freq=1.0), computed as boost * idf * tf from:
      1.8461535 idf, computed as log(1 + (N - n + 0.5) / (n + 0.5)) from:
        80 n, number of documents containing term
        509 N, total number of documents with field
      0.5635748 tf, computed as freq / (freq + k1 * (1 - b + b * dl / avgdl)) from:
        1.0 freq, occurrences of term within document
        1.2 k1, term saturation parameter
        0.75 b, length normalization parameter
        3.0 dl, length of field
        5.691552 avgdl, average length of field

Here is the same query in MongoDB:

db.articles.aggregate([
  { $search: { text: { query: ["🍏", "🍊"], path: "description" }, index: "default" } },
  { $project: { _id: 0, score: { $meta: "searchScore" }, description: 1 } },
  { $sort: { score: -1 } },
  { $limit: 15  }
]).forEach( i=> print(i.score.toFixed(3).padStart(5, " "),i.description) )

4.325 🍏 🍌 🍊
1.354 🍎 🍌 🍊 🍊 🍊
1.259 🫐 🍊 🥑 🍊
1.137 🥥 🍊 🍊 🍅 🍈 🍈
1.137 🍍 🍓 🍊 🍊 🥑 🍉
1.137 🥥 🍆 🍊 🍊 🍍 🍉
1.084 🍊 🍑 🍊 🥥 🍌 🍍 🫐
1.084 🍊 🫐 🥝 🍋 🥑 🍇 🍊
1.084 🥭 🍍 🥑 🍋 🍈 🍊 🍊
1.040 🍊 🫐 🥭
1.040 🍊 🍉 🍍
1.040 🍎 🍌 🍊
1.040 🍊 🍋 🍋
1.040 🍐 🍌 🍊
1.036 🍐 🥥 🍍 🍈 🍐 🍊 🍆 🍊

While the scores may feel intuitively correct when you look at the data, it's important to remember there's no magic—everything is based on well‑known mathematics and formulas. Lucene’s scoring algorithms are used in many systems, including Elasticsearch, Apache Solr, and the search indexes built into MongoDB.

Conclusion

MongoDB search indexes are designed to work optimally out of the box. In my earlier post, I relied entirely on default settings, dynamic mapping, and even replaced words with emojis—yet still got relevant, well-ranked results without extra tuning. If you want to go deeper and fine-tune your search behavior, or simply learn more about how it works, inspecting the underlying Lucene index can provide great insights. Since MongoDB Atlas Search indexes are Lucene-compatible, tools like Luke allow you to see exactly how your text is tokenized, stored, and scored—giving you full transparency into how queries match your documents.

by Franck Pachot

September 19, 2025

Franck Pachot

Text Search with MongoDB and PostgreSQL

MongoDB Search Indexes provide full‑text search capabilities directly within MongoDB, allowing complex queries to be run without copying data to a separate search system. Initially deployed in Atlas, MongoDB’s managed service, Search Indexes are now also part of the community edition. This post compares the default full‑text search behaviour between MongoDB and PostgreSQL, using a simple example to illustrate the ranking algorithm.

Setup: a small dataset

I’ve inserted nine small documents, each consisting of different fruits, using emojis to make it more visual. The 🍎 and 🍏 emojis represent our primary search terms. They appear at varying frequencies in documents of different lengths.

db.articles.deleteMany({});

db.articles.insertMany([
 { description : "🍏 🍌 🍊" },                // short, 1 🍏
 { description : "🍎 🍌 🍊" },                // short, 1 🍎
 { description : "🍎 🍌 🍊 🍎" },             // larger, 2 🍎
 { description : "🍎 🍌 🍊 🍊 🍊" },          // larger, 1 🍎
 { description : "🍎 🍌 🍊 🌴 🫐 🍈 🍇 🌰" },  // large, 1 🍎
 { description : "🍎 🍎 🍎 🍎 🍎 🍎" },       // large, 6 🍎
 { description : "🍎 🍌" },                 // very short, 1 🍎
 { description : "🍌 🍊 🌴 🫐 🍈 🍇 🌰 🍎" },  // large, 1 🍎
 { description : "🍎 🍎 🍌 🍌 🍌" },          // shorter, 2 🍎
]);

To enable dynamic indexing, I created a MongoDB Search Index without specifying any particular field names:

db.articles.createSearchIndex("default",
  { mappings: { dynamic: true } }
);

I created the equivalent on PostgreSQL:

DROP TABLE IF EXISTS articles;
CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    description TEXT
);

INSERT INTO articles(description) VALUES
('🍏 🍌 🍊'),
('🍎 🍌 🍊'),
('🍎 🍌 🍊 🍎'),
('🍎 🍌 🍊 🍊 🍊'),
('🍎 🍌 🍊 🌴 🫐 🍈 🍇 🌰'),
('🍎 🍎 🍎 🍎 🍎 🍎'),
('🍎 🍌'),
('🍌 🍊 🌴 🫐 🍈 🍇 🌰 🍎'),
('🍎 🍎 🍌 🍌 🍌');

Since text search needs multiple index entries for each row, I set up a GIN (Generalized Inverted Index) and use tsvector to extract and index the relevant tokens.

CREATE INDEX articles_fts_idx
  ON articles USING GIN (to_tsvector('simple', description))
;

MongoDB Text Search (Lucene BM25):

I use my custom search index to find articles containing either 🍎 or 🍏 in their descriptions. The results are sorted by relevance score and displayed as follows:

db.articles.aggregate([
  { $search: { text: { query: ["🍎", "🍏"], path: "description" }, index: "default" } },
  { $project: { _id: 0, score: { $meta: "searchScore" }, description: 1 } },
  { $sort: { score: -1 } }
]).forEach( i=> print(i.score.toFixed(3).padStart(5, " "),i.description) )

Here are the results, presented in order of best to worst match:

1.024 🍏 🍌 🍊
0.132 🍎 🍎 🍎 🍎 🍎 🍎
0.107 🍎 🍌 🍊 🍎
0.101 🍎 🍎 🍌 🍌 🍌
0.097 🍎 🍌
0.088 🍎 🍌 🍊
0.073 🍎 🍌 🍊 🍊 🍊
0.059 🍎 🍌 🍊 🌴 🫐 🍈 🍇 🌰
0.059 🍌 🍊 🌴 🫐 🍈 🍇 🌰 🍎

All documents were retrieved by this search since each contains a red or green apple. However, they are assigned different scores:

  • Multiple appearances boost the score: When a document contains the search term more than once, its ranking increases compared to those with only a single appearance. That's why documents featuring several 🍎 are ranked higher than those containing only one.
  • Rarity outweighs quantity: When a term like 🍎 appears in every document, it has less impact than a rare term, such as 🍏. Therefore, even if 🍏 only appears once, the document containing it ranks higher than others with multiple 🍎. In this model, rarity carries more weight than mere frequency.
  • Diminishing returns on term frequency: Each extra occurrence of a term adds less to the relevance score. For instance, increasing 🍎 from one to six times (from 🍎 🍌 to 🍎 🍎 🍎 🍎 🍎 🍎) boosts the score, but not by a factor of six. The effect of term repetition diminishes as the count rises.
  • Document length matters: A term that appears only once is scored higher in a short document than in a long one. That's why 🍎 🍌 ranks higher than 🍎 🍌 🍊, which itself ranks higher than 🍎 🍌 🍊 🍊 🍊.

MongoDB Atlas Search indexes are powered by Lucene’s BM25 algorithm, a refinement of the classic TF‑IDF model:

  • Term Frequency (TF): More occurrences of a term in a document increase its relevance score, but with diminishing returns.
  • Inverse Document Frequency (IDF): Terms that appear in fewer documents receive higher weighting.
  • Length Normalization: Matches in shorter documents contribute more to relevance than the same matches in longer documents.

To demonstrate the impact of IDF, I added several documents that do not contain any of the apples I'm searching for.

const fruits = [ "🍐","🍊","🍋","🍌","🍉","🍇","🍓","🫐",         
                 "🥝","🥭","🍍","🥥","🍈","🍅","🥑","🍆",  
                 "🍋","🍐","🍓","🍇","🍈","🥭","🍍","🍑",  
                 "🥝","🫐","🍌","🍉","🥥","🥑","🥥","🍍" ];
function randomFruitSentence(min=3, max=8) {
  const len = Math.floor(Math.random() * (max - min + 1)) + min;
  return Array.from({length: len}, () => fruits[Math.floor(Math.random()*fruits.length)]).join(" ");
}
db.articles.insertMany(
  Array.from({length: 500}, () => ({ description: randomFruitSentence() }))
);

db.articles.aggregate([
  { $search: { text: { query: ["🍎", "🍏"], path: "description" }, index: "default" } },
  { $project: { _id: 0, score: { $meta: "searchScore" }, description: 1 } },
  { $sort: { score: -1 } }
]).forEach( i=> print(i.score.toFixed(3).padStart(5, " "),i.description) )

3.365 🍎 🍎 🍎 🍎 🍎 🍎
3.238 🍏 🍌 🍊
2.760 🍎 🍌 🍊 🍎
2.613 🍎 🍎 🍌 🍌 🍌
2.506 🍎 🍌
2.274 🍎 🍌 🍊
1.919 🍎 🍌 🍊 🍊 🍊
1.554 🍎 🍌 🍊 🌴 🫐 🍈 🍇 🌰
1.554 🍌 🍊 🌴 🫐 🍈 🍇 🌰 🍎

Although the result set is unchanged, the score has increased and the frequency gap between 🍎 and 🍏 has narrowed. As a result, 🍎 🍎 🍎 🍎 🍎 🍎 now ranks higher than 🍏 🍌 🍊, since the inverse document frequency (IDF) of 🍏 does not fully offset its term frequency (TF) within a single document. Crucially, changes made in other documents can influence the score of any given document, unlike in traditional indexes where changes in one document do not impact others' index entries.

PostgreSQL Text Search (TF only):

Here is the result in PostgreSQL:

SELECT ts_rank_cd(  

        to_tsvector('simple', description)
     ,  
        to_tsquery('simple', '🍎 | 🍏')  

       ) AS score, description  
FROM articles  
WHERE
       to_tsvector('simple', description) 
    @@ 
       to_tsquery('simple', '🍎 | 🍏')  

ORDER BY score DESC;

It retrieves the same documents, but with many having the same score, even with different patterns:

 score |       description
-------+-------------------------
   0.6 | 🍎 🍎 🍎 🍎 🍎 🍎
   0.2 | 🍎 🍌 🍊 🍎
   0.2 | 🍎 🍎 🍌 🍌 🍌
   0.1 | 🍏 🍌 🍊
   0.1 | 🍎 🍌
   0.1 | 🍌 🍊 🌴 🫐 🍈 🍇 🌰 🍎
   0.1 | 🍎 🍌 🍊 🌴 🫐 🍈 🍇 🌰
   0.1 | 🍎 🍌 🍊
   0.1 | 🍎 🍌 🍊 🍊 🍊
(9 rows)

With PostgreSQL text search, only the term frequency (TF) matters, and is a direct multiplicator of the score: 6 apples ranks 3x higher than two, and 6x than one.

There's some possible normalization available with additiona flags:

SELECT ts_rank_cd(
         to_tsvector('simple', description),
         to_tsquery('simple', '🍎 | 🍏')  ,
            0 -- (the default) ignores the document length
         |  1 -- divides the rank by 1 + the logarithm of the document length
    --   |  2 -- divides the rank by the document length
    --   |  4 -- divides the rank by the mean harmonic distance between extents (this is implemented only by ts_rank_cd)
         |  8 -- divides the rank by the number of unique words in document
    --   | 16 -- divides the rank by 1 + the logarithm of the number of unique words in document
    --   | 32 -- divides the rank by itself + 1
       ) AS score,
       description
FROM articles
WHERE to_tsvector('simple', description) @@ to_tsquery('simple', '🍎 | 🍏')
ORDER BY score DESC
;
    score    |       description
-------------+-------------------------
    0.308339 | 🍎 🍎 🍎 🍎 🍎 🍎
 0.055811062 | 🍎 🍎 🍌 🍌 🍌
  0.04551196 | 🍎 🍌
  0.04142233 | 🍎 🍌 🍊 🍎
 0.024044918 | 🍏 🍌 🍊
 0.024044918 | 🍎 🍌 🍊
 0.018603688 | 🍎 🍌 🍊 🍊 🍊
 0.005688995 | 🍎 🍌 🍊 🌴 🫐 🍈 🍇 🌰
 0.005688995 | 🍌 🍊 🌴 🫐 🍈 🍇 🌰 🍎
(9 rows)

This penalizes longer documents and those with more unique terms. Still, it doesn't consider other documents like IDF.

PostgreSQL Full Text Search scoring with ts_rank_cd is based on term frequency and proximity. It does not compute inverse document frequency, so scores do not change as the corpus changes. Normalization flags can penalize long documents or those with many unique terms, but they are length-based adjustments, not true IDF, like we have in TF‑IDF or BM25‑style search engine.

ParadeDB with pg_search (Tantivy BM25)

PostgreSQL popularity is not only due to its features but also its extensibility and ecosystem. The pg_search extension adds functions and operators that use BM25 indexes (Tantivy, a Rust-based search library inspired by Lucene). It is easy to test with ParadeDB:

docker run --rm -it paradedb/paradedb bash

POSTGRES_PASSWORD=x docker-entrypoint.sh postgres &

psql -U postgres

The extension is installed in version 0.18.4:

postgres=# \dx
                                        List of installed extensions
          Name          | Version |   Schema   |                        Description
------------------------+---------+------------+------------------------------------------------------------
 fuzzystrmatch          | 1.2     | public     | determine similarities and distance between strings
 pg_cron                | 1.6     | pg_catalog | Job scheduler for PostgreSQL
 pg_ivm                 | 1.9     | pg_catalog | incremental view maintenance on PostgreSQL
 pg_search              | 0.18.4  | paradedb   | pg_search: Full text search for PostgreSQL using BM25
 plpgsql                | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis                | 3.6.0   | public     | PostGIS geometry and geography spatial types and functions
 postgis_tiger_geocoder | 3.6.0   | tiger      | PostGIS tiger geocoder and reverse geocoder
 postgis_topology       | 3.6.0   | topology   | PostGIS topology spatial types and functions
 vector                 | 0.8.0   | public     | vector data type and ivfflat and hnsw access methods
(9 rows)

I created and inserted the same as I did above on PostgreSQL and created the BM25 index:

CREATE INDEX search_idx ON articles
       USING bm25 (id, description)
       WITH (key_field='id')
;

We can query using the @@@ operator and rank with paradedb.score(id). Unlike PostgreSQL’s built‑in @@, which uses query‑local statistics, @@@ computes scores using global IDF and Lucene’s BM25 length normalization — so adding unrelated documents can still change the scores.

SELECT description, paradedb.score(id) AS score
FROM articles
WHERE description @@@ '🍎' OR description @@@ '🍏'
ORDER BY score DESC, description;

 description | score
-------------+-------
(0 rows)

The result is empty. Using emoji as terms can lead to inconsistent tokenization results, so I replaced them with text labels instead:

UPDATE articles SET description 
 = replace(description, '🍎', 'Gala');
UPDATE articles SET description 
 = replace(description, '🍏', 'Granny Smith');
UPDATE articles SET description 
 = replace(description, '🍊', 'Orange');

This time, the scoring is more precise and takes into account the term frequency within the document (TF), the term’s rarity across the entire indexed corpus (IDF), along with a length normalization factor to prevent longer documents from having an unfair advantage:

SELECT description, paradedb.score(id) AS score
FROM articles
WHERE description @@@ 'Gala' OR description @@@ 'Granny Smith'
ORDER BY score DESC, description;

          description          |   score
-------------------------------+------------
 Granny Smith 🍌 Orange        |  3.1043208
 Gala Gala Gala Gala Gala Gala | 0.79529095
 Gala Gala 🍌 🍌 🍌            |  0.7512194
 Gala 🍌                       | 0.69356775
 Gala 🍌 Orange Gala           | 0.63589364
 Gala 🍌 Orange                |  0.5195716
 Gala 🍌 Orange 🌴 🫐 🍈 🍇   |  0.5195716
 🍌 Orange 🌴 🫐 🍈 🍇   Gala |  0.5195716
 Gala 🍌 Orange Orange Orange  | 0.34597924
(9 rows)

PostgreSQL’s built-in search only provides basic, local term frequency scoring. To get a full-feature text search that can be used in application's search boxes, it can be extended with third-party tools like ParadeDB's pg_search.

Conclusion

Relevance scoring in text search can differ widely between systems because each uses its own ranking algorithms and analyzers. To better visualize my results in these tests, I used emojis and opted for the simplest definitions. I selected PostgreSQL's to_tsvector('simple') configuration to prevent language-specific processing, while for MongoDB Atlas Search, I used the default dynamic mapping.

MongoDB Atlas Search (and now in MongoDB Community Edition) uses Lucene’s BM25 algorithm, combining:

  • Term Frequency (TF): Frequent terms in a document boost scores, but with diminishing returns
  • Inverse Document Frequency (IDF): Rare terms across the corpus get higher weight
  • Length normalization: Matches in shorter documents are weighted more than the same matches in longer ones

PostgreSQL’s full-text search (ts_rank_cd()) evaluates only term frequency and position, overlooking other metrics like IDF. For more advanced features such as BM25, extensions like ParadeDB’s pg_search are needed, which require extra configuration and are not always available on managed platforms. PostgreSQL offers a modular approach, where extensions can add advanced ranking algorithms like BM25. MongoDB provides built‑in BM25‑based full‑text search in both Atlas and the Community Edition.

by Franck Pachot
Franck Pachot

Text Search With MongoDB (BM25 TF-IDF) and PostgreSQL

MongoDB search indexes provide full‑text search capabilities directly within MongoDB, allowing complex queries to be run without copying data to a separate search system. Initially deployed in Atlas, MongoDB’s managed service, search indexes are now also part of the community edition. This post compares the default full‑text search behaviour between MongoDB and PostgreSQL, using a simple example to illustrate the ranking algorithm.

Setup: a small dataset

I’ve inserted nine small documents, each consisting of different fruits, using emojis to make it more visual. The 🍎 and 🍏 emojis represent our primary search terms. They appear at varying frequencies in documents of different lengths.

db.articles.deleteMany({});

db.articles.insertMany([
 { description : "🍏 🍌 🍊" },                // short, 1 🍏
 { description : "🍎 🍌 🍊" },                // short, 1 🍎
 { description : "🍎 🍌 🍊 🍎" },             // larger, 2 🍎
 { description : "🍎 🍌 🍊 🍊 🍊" },          // larger, 1 🍎
 { description : "🍎 🍌 🍊 🌴 🫐 🍈 🍇 🌰" },  // large, 1 🍎
 { description : "🍎 🍎 🍎 🍎 🍎 🍎" },       // large, 6 🍎
 { description : "🍎 🍌" },                 // very short, 1 🍎
 { description : "🍌 🍊 🌴 🫐 🍈 🍇 🌰 🍎" },  // large, 1 🍎
 { description : "🍎 🍎 🍌 🍌 🍌" },          // shorter, 2 🍎
]);

To enable dynamic indexing, I created a MongoDB search index without specifying any particular field names:

db.articles.createSearchIndex("default",
  { mappings: { dynamic: true } }
);

I created the equivalent on PostgreSQL:

DROP TABLE IF EXISTS articles;
CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    description TEXT
);

INSERT INTO articles(description) VALUES
('🍏 🍌 🍊'),
('🍎 🍌 🍊'),
('🍎 🍌 🍊 🍎'),
('🍎 🍌 🍊 🍊 🍊'),
('🍎 🍌 🍊 🌴 🫐 🍈 🍇 🌰'),
('🍎 🍎 🍎 🍎 🍎 🍎'),
('🍎 🍌'),
('🍌 🍊 🌴 🫐 🍈 🍇 🌰 🍎'),
('🍎 🍎 🍌 🍌 🍌');

Since text search needs multiple index entries for each row, I set up a Generalized Inverted Index (GIN) and use tsvector to extract and index the relevant tokens.

CREATE INDEX articles_fts_idx
  ON articles USING GIN (to_tsvector('simple', description))
;

MongoDB text search (Lucene BM25):

I use my custom search index to find articles containing either 🍎 or 🍏 in their descriptions. The results are sorted by relevance score and displayed as follows:

db.articles.aggregate([
  { $search: { text: { query: ["🍎", "🍏"], path: "description" }, index: "default" } },
  { $project: { _id: 0, score: { $meta: "searchScore" }, description: 1 } },
  { $sort: { score: -1 } }
]).forEach( i=> print(i.score.toFixed(3).padStart(5, " "),i.description) )

Here are the results, presented in order of best to worst match:

1.024 🍏 🍌 🍊
0.132 🍎 🍎 🍎 🍎 🍎 🍎
0.107 🍎 🍌 🍊 🍎
0.101 🍎 🍎 🍌 🍌 🍌
0.097 🍎 🍌
0.088 🍎 🍌 🍊
0.073 🍎 🍌 🍊 🍊 🍊
0.059 🍎 🍌 🍊 🌴 🫐 🍈 🍇 🌰
0.059 🍌 🍊 🌴 🫐 🍈 🍇 🌰 🍎

All documents were retrieved by this search since each contains a red or green apple. However, they are assigned different scores:

  • Multiple appearances boost the score: When a document contains the search term more than once, its ranking increases compared to those with only a single appearance. That's why documents featuring several 🍎 are ranked higher than those containing only one.
  • Rarity outweighs quantity: When a term like 🍎 appears in every document, it has less impact than a rare term, such as 🍏. Therefore, even if 🍏 only appears once, the document containing it ranks higher than others with multiple 🍎. In this model, rarity carries more weight than mere frequency.
  • Diminishing returns on term frequency: Each extra occurrence of a term adds less to the relevance score. For instance, increasing 🍎 from one to six times (from 🍎 🍌 to 🍎 🍎 🍎 🍎 🍎 🍎) boosts the score, but not by a factor of six. The effect of term repetition diminishes as the count rises.
  • Document length matters: A term that appears only once is scored higher in a short document than in a long one. That's why 🍎 🍌 ranks higher than 🍎 🍌 🍊, which itself ranks higher than 🍎 🍌 🍊 🍊 🍊.

MongoDB Atlas Search indexes are powered by Lucene’s BM25 algorithm, a refinement of the classic TF‑IDF model:

  • Term frequency (TF): More occurrences of a term in a document increase its relevance score, but with diminishing returns.
  • Inverse document frequency (IDF): Terms that appear in fewer documents receive higher weighting.
  • Length normalization: Matches in shorter documents contribute more to relevance than the same matches in longer documents.

To demonstrate the impact of IDF, I added several documents that do not contain any of the apples I'm searching for.

const fruits = [ "🍐","🍊","🍋","🍌","🍉","🍇","🍓","🫐",         
                 "🥝","🥭","🍍","🥥","🍈","🍅","🥑","🍆",  
                 "🍋","🍐","🍓","🍇","🍈","🥭","🍍","🍑",  
                 "🥝","🫐","🍌","🍉","🥥","🥑","🥥","🍍" ];
function randomFruitSentence(min=3, max=8) {
  const len = Math.floor(Math.random() * (max - min + 1)) + min;
  return Array.from({length: len}, () => fruits[Math.floor(Math.random()*fruits.length)]).join(" ");
}
db.articles.insertMany(
  Array.from({length: 500}, () => ({ description: randomFruitSentence() }))
);

db.articles.aggregate([
  { $search: { text: { query: ["🍎", "🍏"], path: "description" }, index: "default" } },
  { $project: { _id: 0, score: { $meta: "searchScore" }, description: 1 } },
  { $sort: { score: -1 } }
]).forEach( i=> print(i.score.toFixed(3).padStart(5, " "),i.description) )

3.365 🍎 🍎 🍎 🍎 🍎 🍎
3.238 🍏 🍌 🍊
2.760 🍎 🍌 🍊 🍎
2.613 🍎 🍎 🍌 🍌 🍌
2.506 🍎 🍌
2.274 🍎 🍌 🍊
1.919 🍎 🍌 🍊 🍊 🍊
1.554 🍎 🍌 🍊 🌴 🫐 🍈 🍇 🌰
1.554 🍌 🍊 🌴 🫐 🍈 🍇 🌰 🍎

Although the result set is unchanged, the score has increased and the frequency gap between 🍎 and 🍏 has narrowed. As a result, 🍎 🍎 🍎 🍎 🍎 🍎 now ranks higher than 🍏 🍌 🍊, since the inverse document frequency (IDF) of 🍏 does not fully offset its term frequency (TF) within a single document. Crucially, changes made in other documents can influence the score of any given document, unlike in traditional indexes, where changes in one document do not impact others' index entries.

PostgreSQL text search (TF only):

Here is the result in PostgreSQL:

SELECT ts_rank_cd(  

        to_tsvector('simple', description)
     ,  
        to_tsquery('simple', '🍎 | 🍏')  

       ) AS score, description  
FROM articles  
WHERE
       to_tsvector('simple', description) 
    @@ 
       to_tsquery('simple', '🍎 | 🍏')  

ORDER BY score DESC;

It retrieves the same documents, but with many having the same score, even with different patterns:

 score |       description
-------+-------------------------
   0.6 | 🍎 🍎 🍎 🍎 🍎 🍎
   0.2 | 🍎 🍌 🍊 🍎
   0.2 | 🍎 🍎 🍌 🍌 🍌
   0.1 | 🍏 🍌 🍊
   0.1 | 🍎 🍌
   0.1 | 🍌 🍊 🌴 🫐 🍈 🍇 🌰 🍎
   0.1 | 🍎 🍌 🍊 🌴 🫐 🍈 🍇 🌰
   0.1 | 🍎 🍌 🍊
   0.1 | 🍎 🍌 🍊 🍊 🍊
(9 rows)

With PostgreSQL text search, only the term frequency (TF) matters, and is a direct multiplicator of the score: six apples rank three times higher than two, and six times higher than one.

There's some possible normalization available with additiona flags:

SELECT ts_rank_cd(
         to_tsvector('simple', description),
         to_tsquery('simple', '🍎 | 🍏')  ,
            0 -- (the default) ignores the document length
         |  1 -- divides the rank by 1 + the logarithm of the document length
    --   |  2 -- divides the rank by the document length
    --   |  4 -- divides the rank by the mean harmonic distance between extents (this is implemented only by ts_rank_cd)
         |  8 -- divides the rank by the number of unique words in document
    --   | 16 -- divides the rank by 1 + the logarithm of the number of unique words in document
    --   | 32 -- divides the rank by itself + 1
       ) AS score,
       description
FROM articles
WHERE to_tsvector('simple', description) @@ to_tsquery('simple', '🍎 | 🍏')
ORDER BY score DESC
;
    score    |       description
-------------+-------------------------
    0.308339 | 🍎 🍎 🍎 🍎 🍎 🍎
 0.055811062 | 🍎 🍎 🍌 🍌 🍌
  0.04551196 | 🍎 🍌
  0.04142233 | 🍎 🍌 🍊 🍎
 0.024044918 | 🍏 🍌 🍊
 0.024044918 | 🍎 🍌 🍊
 0.018603688 | 🍎 🍌 🍊 🍊 🍊
 0.005688995 | 🍎 🍌 🍊 🌴 🫐 🍈 🍇 🌰
 0.005688995 | 🍌 🍊 🌴 🫐 🍈 🍇 🌰 🍎
(9 rows)

This penalizes longer documents and those with more unique terms. Still, it doesn't consider other documents like IDF.

PostgreSQL fFull text search scoring with ts_rank_cd is based on term frequency and proximity. It does not compute inverse document frequency, so scores do not change as the corpus changes. Normalization flags can penalize long documents or those with many unique terms, but they are length-based adjustments, not true IDF, like we have in TF‑IDF or BM25‑style search engine.

ParadeDB with pg_search (Tantivy BM25)

PostgreSQL popularity is not only due to its features but also its extensibility and ecosystem. The pg_search extension adds functions and operators that use BM25 indexes (Tantivy, a Rust-based search library inspired by Lucene). It is easy to test with ParadeDB:

docker run --rm -it paradedb/paradedb bash

POSTGRES_PASSWORD=x docker-entrypoint.sh postgres &

psql -U postgres

The extension is installed in version 0.18.4:

postgres=# \dx
                                        List of installed extensions
          Name          | Version |   Schema   |                        Description
------------------------+---------+------------+------------------------------------------------------------
 fuzzystrmatch          | 1.2     | public     | determine similarities and distance between strings
 pg_cron                | 1.6     | pg_catalog | Job scheduler for PostgreSQL
 pg_ivm                 | 1.9     | pg_catalog | incremental view maintenance on PostgreSQL
 pg_search              | 0.18.4  | paradedb   | pg_search: Full text search for PostgreSQL using BM25
 plpgsql                | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis                | 3.6.0   | public     | PostGIS geometry and geography spatial types and functions
 postgis_tiger_geocoder | 3.6.0   | tiger      | PostGIS tiger geocoder and reverse geocoder
 postgis_topology       | 3.6.0   | topology   | PostGIS topology spatial types and functions
 vector                 | 0.8.0   | public     | vector data type and ivfflat and hnsw access methods
(9 rows)

I created and inserted the same as I did above on PostgreSQL and created the BM25 index:

CREATE INDEX search_idx ON articles
       USING bm25 (id, description)
       WITH (key_field='id')
;

We can query using the @@@ operator and rank with paradedb.score(id). Unlike PostgreSQL’s built‑in @@, which uses query‑local statistics, @@@ computes scores using global IDF and Lucene’s BM25 length normalization—so adding unrelated documents can still change the scores.

SELECT description, paradedb.score(id) AS score
FROM articles
WHERE description @@@ '🍎' OR description @@@ '🍏'
ORDER BY score DESC, description;

 description | score
-------------+-------
(0 rows)

The result is empty. Using emoji as terms can lead to inconsistent tokenization results, so I replaced them with text labels instead:

UPDATE articles SET description 
 = replace(description, '🍎', 'Gala');
UPDATE articles SET description 
 = replace(description, '🍏', 'Granny Smith');
UPDATE articles SET description 
 = replace(description, '🍊', 'Orange');

This time, the scoring is more precise and takes into account the term frequency within the document (TF), the term’s rarity across the entire indexed corpus (IDF), along with a length normalization factor to prevent longer documents from having an unfair advantage:

SELECT description, paradedb.score(id) AS score
FROM articles
WHERE description @@@ 'Gala' OR description @@@ 'Granny Smith'
ORDER BY score DESC, description;

          description          |   score
-------------------------------+------------
 Granny Smith 🍌 Orange        |  3.1043208
 Gala Gala Gala Gala Gala Gala | 0.79529095
 Gala Gala 🍌 🍌 🍌            |  0.7512194
 Gala 🍌                       | 0.69356775
 Gala 🍌 Orange Gala           | 0.63589364
 Gala 🍌 Orange                |  0.5195716
 Gala 🍌 Orange 🌴 🫐 🍈 🍇   |  0.5195716
 🍌 Orange 🌴 🫐 🍈 🍇   Gala |  0.5195716
 Gala 🍌 Orange Orange Orange  | 0.34597924
(9 rows)

It looks very similar to the MongoDB result. Lucene may give a slight edge to terms that appear more frequently (🍎 🍌 🍊 🍎), even if the document length penalty is higher. Tantivy might apply length normalization in a slightly different way, so the shorter (🍎 🍌) gets a bigger boost.

Here is the execution plan in ParadeDB:

EXPLAIN(ANALYZE, BUFFERS, VERBOSE)
SELECT description, paradedb.score(id) AS score
FROM articles
WHERE description @@@ 'Gala' OR description @@@ 'Granny Smith'
ORDER BY score DESC, description
;

 Gather Merge  (cost=1010.06..1010.68 rows=5 width=31) (actual time=5.893..8.237 rows=8 loops=1)
   Output: description, (score(id))
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=333
   ->  Sort  (cost=10.04..10.05 rows=3 width=31) (actual time=0.529..0.540 rows=3 loops=3)
         Output: description, (score(id))
         Sort Key: (score(articles.id)) DESC, articles.description
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=306
         Worker 0:  actual time=0.548..0.558 rows=0 loops=1
           Sort Method: quicksort  Memory: 25kB
           Buffers: shared hit=64
         Worker 1:  actual time=0.596..0.607 rows=0 loops=1
           Sort Method: quicksort  Memory: 25kB
           Buffers: shared hit=64
         ->  Parallel Custom Scan (ParadeDB Scan) on public.articles  (cost=10.00..10.02 rows=3 width=31) (actual time=0.367..0.444 rows=3 loops=3)
               Output: description, score(id)
               Table: articles
               Index: search_idx
               Segment Count: 5
               Heap Fetches: 8
               Virtual Tuples: 0
               Invisible Tuples: 0
               Parallel Workers: {"-1":{"query_count":0,"claimed_segments":[{"id":"a17b19a2","deleted_docs":0,"max_doc":9},{"id":"3fa71653","deleted_docs":6,"max_doc":6},{"id":"3c243f8e","deleted_docs":1,"max_doc":1},{"id":"badbcd7e","deleted_docs":8,"max_doc":8},{"id":"add79d5d","deleted_docs":9,"max_doc":9}]}}
               Exec Method: NormalScanExecState
               Scores: true
               Tantivy Query: {"boolean":{"should":[{"with_index":{"query":{"parse_with_field":{"field":"description","query_string":"Gala","lenient":null,"conjunction_mode":null}}}},{"with_index":{"query":{"parse_with_field":{"field":"description","query_string":"Granny Smith","lenient":null,"conjunction_mode":null}}}}]}}
               Buffers: shared hit=216
               Worker 0:  actual time=0.431..0.441 rows=0 loops=1
                 Buffers: shared hit=19
               Worker 1:  actual time=0.447..0.457 rows=0 loops=1
                 Buffers: shared hit=19

This PostgreSQL plan shows ParadeDB executing a parallel full-text search with Tantivy. The Parallel Custom Scan node issues a BM25 query (Gala OR "Granny Smith") to the segmented Tantivy index. Each worker searches its segments, scores, fetches matching descriptions, and sorts locally. The Gather Merge then combines these into a single ranked list. Since search and scoring are done within Tantivy across CPU cores and results are fetched from shared memory, the query is quick and efficient.

In the execution plan, the Tantivy query closely resembles a MongoDB search query. Specifically, "boolean" in Tantivy is equivalent to "compound" in MongoDB, "should" matches "should", "parse_with_field.field" is similar to "path".

PostgreSQL’s built-in search only provides basic, local term frequency scoring. To get a full-featured text search that can be used in an applica... (truncated)

by Franck Pachot

September 18, 2025

AWS Database Blog - Amazon Aurora

Dynamic view-based data masking in Amazon RDS and Amazon Aurora MySQL

Data masking is an important technique in cybersecurity, allowing organizations to safeguard personally identifiable information (PII) and other confidential data, while maintaining its utility for development, testing, and analytics purposes. Data masking involves replacing original sensitive data with false, yet realistic information. This process helps ensure that the masked version preserves the format and characteristics […]
by Cade Kettner
Percona Database Performance Blog

Help Shape the Future of Vector Search in MySQL

AI and machine learning are seemingly everywhere, and that’s forcing every database company to think about vector search. Companies want to build things like smart search that actually understands what you mean, recommendation systems that know what you’ll like, and tools that can spot when something’s off. To make all of this work at the […]
by David Quilty
Franck Pachot

Combine Two JSON Collections with Nested Arrays: MongoDB and PostgreSQL Aggregations

Suppose you need to merge two sources of data—both JSON documents containing nested arrays. This was a question on StackOverflow, with a simple example, easy to reproduce. Let's examine how to accomplish this in PostgreSQL and MongoDB, and compare the approaches.

Description of the problem

I have two tables. One is stored on one server, and the other on another. And I need to combine their data on daily statistics once in a while. The tables are identical in fields and structure. But I don't know how to combine the jsonb fields into one array by grouping them by some fields and calculating the total number.

So, we have sales transactions stored in two sources, each containing an array of cash registers, each cash register containing an array of products sold that day.
We want to merge both sources, and aggregate the counts by product and register in nested arrays.

They provided an example on db<>fiddle. To make it simpler, I've put the sample data in a table, with the two sources ("server_table" and "my_temp") and the expected result in bold:

date cash register product name count source
2025-09-01 2 name1 2 server_table
2
2025-09-01 2 name2 4 server_table
4
2025-09-01 3 name1 2 my_temp
2
2025-09-01 3 name2 4 my_temp
4
2025-09-01 4 name2 4 my_temp
2025-09-01 4 name2 8 server_table
12
2025-09-01 4 name8 12 my_temp
2025-09-01 4 name8 6 server_table
18
2025-09-02 1 name1 2 my_temp
2025-09-02 1 name1 2 server_table
4
2025-09-02 1 name2 4 my_temp
2025-09-02 1 name2 4 server_table
8
2025-09-02 3 name2 4 my_temp
4
2025-09-02 3 name8 12 my_temp
12
2025-09-02 4 name2 4 server_table
4
2025-09-02 4 name4 5 server_table
5
2025-09-03 2 name1 2 my_temp
2025-09-03 2 name1 2 server_table
4
2025-09-03 2 name2 4 my_temp
2025-09-03 2 name2 4 server_table
8
2025-09-03 4 name2 4 my_temp
2025-09-03 4 name2 4 server_table
8
2025-09-03 4 name8 12 my_temp
2025-09-03 4 name8 12 server_table
24
2025-09-04 1 name1 2 my_temp
2025-09-04 1 name1 2 server_table
4
2025-09-04 1 name2 4 my_temp
2025-09-04 1 name2 4 server_table
8
2025-09-04 4 name2 4 my_temp
2025-09-04 4 name2 4 server_table
8
2025-09-04 4 name8 12 my_temp
2025-09-04 4 name8 12 server_table
24

Sample data in PostgreSQL

Here is the example provided in the post, as a db<>fiddle link:


-- Create first table  
CREATE TABLE my_temp (  
    employee_id TEXT,  
    date DATE,  
    info JSONB  
);  

-- Insert sample data into my_temp  
INSERT INTO my_temp (employee_id, date, info)  
VALUES  
(  
    '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',  
    '2025-09-01',  
    '[  
        { "cash_register": 3,  
          "products": [  
            { "productName": "name1", "count": 2 },  
            { "productName": "name2", "count": 4 }  
          ]  
        },  
        { "cash_register": 4,  
          "products": [  
            { "productName": "name8", "count": 12 },  
            { "productName": "name2", "count": 4 }  
          ]  
        }  
     ]'  
),  
(  
    '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',  
    '2025-09-02',  
    '[  
        { "cash_register": 1,  
          "products": [  
            { "productName": "name1", "count": 2 },  
            { "productName": "name2", "count": 4 }  
          ]  
        },  
        { "cash_register": 3,  
          "products": [  
            { "productName": "name8", "count": 12 },  
            { "productName": "name2", "count": 4 }  
          ]  
        }  
     ]'  
),  
(  
    '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',  
    '2025-09-03',  
    '[  
        { "cash_register": 2,  
          "products": [  
            { "productName": "name1", "count": 2 },  
            { "productName": "name2", "count": 4 }  
          ]  
        },  
        { "cash_register": 4,  
          "products": [  
            { "productName": "name8", "count": 12 },  
            { "productName": "name2", "count": 4 }  
          ]  
        }  
     ]'  
),  
(  
    '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',  
    '2025-09-04',  
    '[  
        { "cash_register": 1,  
          "products": [  
            { "productName": "name1", "count": 2 },  
            { "productName": "name2", "count": 4 }  
          ]  
        },  
        { "cash_register": 4,  
          "products": [  
            { "productName": "name8", "count": 12 },  
            { "productName": "name2", "count": 4 }  
          ]  
        }  
     ]'  
);  

-- Create second table  
CREATE TABLE server_table (  
    employee_id TEXT,  
    date DATE,  
    info JSONB  
);  

-- Insert sample data into server_table  
INSERT INTO server_table (employee_id, date, info)  
VALUES  
(  
    '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',  
    '2025-09-01',  
    '[  
        { "cash_register": 2,  
          "products": [  
            { "productName": "name1", "count": 2 },  
            { "productName": "name2", "count": 4 }  
          ]  
        },  
        { "cash_register": 4,  
          "products": [  
            { "productName": "name8", "count": 6 },  
            { "productName": "name2", "count": 8 }  
          ]  
        }  
     ]'  
),  
(  
    '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',  
    '2025-09-02',  
    '[  
        { "cash_register": 1,  
          "products": [  
            { "productName": "name1", "count": 2 },  
            { "productName": "name2", "count": 4 }  
          ]  
        },  
        { "cash_register": 4,  
          "products": [  
            { "productName": "name4", "count": 5 },  
            { "productName": "name2", "count": 4 }  
          ]  
        }  
     ]'  
),  
(  
    '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',  
    '2025-09-03',  
    '[  
        { "cash_register": 2,  
          "products": [  
            { "productName": "name1", "count": 2 },  
            { "productName": "name2", "count": 4 }  
          ]  
        },  
        { "cash_register": 4,  
          "products": [  
            { "productName": "name8", "count": 12 },  
            { "productName": "name2", "count": 4 }  
          ]  
        }  
     ]'  
),  
(  
    '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',  
    '2025-09-04',  
    '[  
        { "cash_register": 1,  
          "products": [  
            { "productName": "name1", "count": 2 },  
            { "productName": "name2", "count": 4 }  
          ]  
        },  
        { "cash_register": 4,  
          "products": [  
            { "productName": "name8", "count": 12 },  
            { "productName": "name2", "count": 4 }  
          ]  
        }  
     ]'  
);

Our goal is to aggregate data from two tables and calculate their total counts. Although I have 30 years of experience working with relational databases and am generally stronger in SQL, I find MongoDB to be more intuitive when working with JSON documents. Let's begin there.

Sample data in MongoDB

I create two collections with the same data as the PostgreSQL example:

db.my_temp.insertMany([  
  {  
    employee_id: "3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9",  
    date: ISODate("2025-09-01"),  
    info: [  
      {  
        cash_register: 3,  
        products: [  
          { productName: "name1", count: 2 },  
          { productName: "name2", count: 4 }  
        ]  
      },  
      {  
        cash_register: 4,  
        products: [  
          { productName: "name8", count: 12 },  
          { productName: "name2", count: 4 }  
        ]  
      }  
    ]  
  },  
  {  
    employee_id: "3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9",  
    date: ISODate("2025-09-02"),  
    info: [  
      {  
        cash_register: 1,  
        products: [  
          { productName: "name1", count: 2 },  
          { productName: "name2", count: 4 }  
        ]  
      },  
      {  
        cash_register: 3,  
        products: [  
          { productName: "name8", count: 12 },  
          { productName: "name2", count: 4 }  
        ]  
      }  
    ]  
  },  
  {  
    employee_id: "3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9",  
    date: ISODate("2025-09-03"),  
    info: [  
      {  
        cash_register: 2,  
        products: [  
          { productName: "name1", count: 2 },  
          { productName: "name2", count: 4 }  
        ]  
      },  
      {  
        cash_register: 4,  
        products: [  
          { productName: "name8", count: 12 },  
          { productName: "name2", count: 4 }  
        ]  
      }  
    ]  
  },  
  {  
    employee_id: "3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9",  
    date: ISODate("2025-09-04"),  
    info: [  
      {  
        cash_register: 1,  
        products: [  
          { productName: "name1", count: 2 },  
          { productName: "name2", count: 4 }  
        ]  
      },  
      {  
        cash_register: 4,  
        products: [  
          { productName: "name8", count: 12 },  
          { productName: "name2", count: 4 }  
        ]  
      }  
    ]  
  }  
]);  


db.server_table.insertMany([  
  {  
    employee_id: "3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9",  
    date: ISODate("2025-09-01"),  
    info: [  
      {  
        cash_register: 2,  
        products: [  
          { productName: "name1", count: 2 },  
          { productName: "name2", count: 4 }  
        ]  
      },  
      {  
        cash_register: 4,  
        products: [  
          { productName: "name8", count: 6 },  
          { productName: "name2", count: 8 }  
        ]  
      }  
    ]  
  },  
  {  
    employee_id: "3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9",  
    date: ISODate("2025-09-02"),  
    info: [  
      {  
        cash_register: 1,  
        products: [  
          { productName: "name1", count: 2 },  
          { productName: "name2", count: 4 }  
        ]  
      },  
      {  
        cash_register: 4,  
        products: [  
          { productName: "name4", count: 5 },  
          { productName: "name2", count: 4 }  
        ]  
      }  
    ]  
  },  
  {  
    employee_id: "3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9",  
    date: ISODate("2025-09-03"),  
    info: [  
      {  
        cash_register: 2,  
        products: [  
          { productName: "name1", count: 2 },  
          { productName: "name2", count: 4 }  
        ]  
      },  
      {  
        cash_register: 4,  
        products: [  
          { productName: "name8", count: 12 },  
          { productName: "name2", count: 4 }  
        ]  
      }  
    ]  
  },  
  {  
    employee_id: "3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9",  
    date: ISODate("2025-09-04"),  
    info: [  
      {  
        cash_register: 1,  
        products: [  
          { productName: "name1", count: 2 },  
          { productName: "name2", count: 4 }  
        ]  
      },  
      {  
        cash_register: 4,  
        products: [  
          { productName: "name8", count: 12 },  
          { productName: "name2", count: 4 }  
        ]  
      }  
    ]  
  }  
]);

While PostgreSQL stores the employee ID and date in separate columns—since JSONB doesn’t support every BSON data type, a document database stores all related data within a single document. Despite these structural differences, the JSON representation appears similar, whether it is stored as JSONB in PostgreSQL or BSON in MongoDB.

Solution in MongoDB

The aggregation framework helps to decompose a problem as successive stages in a pipeline, making it easier to code, read, and debug. I'll need the following stages:

  1. $unionWith to concatenate from "server_table" with those read from "my_temp"
  2. $unwind to flatten the array items to multiple documents
  3. $group and $sum to aggregate
  4. $group to get back the multiple documents into arrays

Here is my query:

db.my_temp.aggregate([
  // concatenate with the other source
  { $unionWith: { coll: "server_table" } },
  // flatten the info to apply aggregation
  { $unwind: "$info" },
  { $unwind: "$info.products" },
  { // sum and group by employee/date/register/product
    $group: {
      _id: {
        employee_id: "$employee_id",
        date: "$date",
        cash_register: "$info.cash_register",
        productName: "$info.products.productName"
      },
      total_count: { $sum: "$info.products.count" }
    }
  },
  { // Regroup by register (inverse of unwind)
    $group: {
      _id: {
        employee_id: "$_id.employee_id",
        date: "$_id.date",
        cash_register: "$_id.cash_register"
      },
      products: {
        $push: {
          productName: "$_id.productName",
          count: "$total_count"
        }
      }
    }
  },
  { // Regroup by employee/date  (inverse of first unwind)
    $group: {
      _id: {
        employee_id: "$_id.employee_id",
        date: "$_id.date"
      },
      info: {
        $push: {
          cash_register: "$_id.cash_register",
          products: "$products"
        }
      }
    }
  },
  { $project: { _id: 0, employee_id: "$_id.employee_id", date: "$_id.date", info: 1 } },
  { $sort: { date: 1 } }
]);

Here is the result:

[
  {
    info: [
      { cash_register: 2, products: [ { productName: 'name1', count: 2 }, { productName: 'name2', count: 4 } ] },
      { cash_register: 4, products: [ { productName: 'name8', count: 18 }, { productName: 'name2', count: 12 } ] },
      { cash_register: 3, products: [ { productName: 'name2', count: 4 }, { productName: 'name1', count: 2 } ] }
    ],
    employee_id: '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',
    date: ISODate('2025-09-01T00:00:00.000Z')
  },
  {
    info: [
      { cash_register: 1, products: [ { productName: 'name2', count: 8 }, { productName: 'name1', count: 4 } ] },
      { cash_register: 4, products: [ { productName: 'name4', count: 5 }, { productName: 'name2', count: 4 } ] },
      { cash_register: 3, products: [ { productName: 'name8', count: 12 }, { productName: 'name2', count: 4 } ] }
    ],
    employee_id: '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',
    date: ISODate('2025-09-02T00:00:00.000Z')
  },
  {
    info: [
      { cash_register: 2, products: [ { productName: 'name2', count: 8 }, { productName: 'name1', count: 4 } ] },
      { cash_register: 4, products: [ { productName: 'name8', count: 24 }, { productName: 'name2', count: 8 } ] }
    ],
    employee_id: '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',
    date: ISODate('2025-09-03T00:00:00.000Z')
  },
  {
    info: [
      { cash_register: 4, products: [ { productName: 'name8', count: 24 }, { productName: 'name2', count: 8 } ] },
      { cash_register: 1, products: [ { productName: 'name1', count: 4 }, { productName: 'name2', count: 8 } ] }
    ],
    employee_id: '3dd280f2-e4d3-4568-9d97-1cc3a9dff1e9',
    date: ISODate('2025-09-04T00:00:00.000Z')
  }
]

Solution in PostgreSQL

In SQL, you can emulate an aggregation pipeline by using the WITH clause, where each stage corresponds to a separate common table expression:

WITH
all_data AS ( -- Union to concatenate the two tables
    SELECT employee_id, "date", info FROM my_temp
    UNION ALL
    SELECT employee_id, "date", info FROM server_table
),
unwound AS ( -- Unwind cash registers and products
    SELECT
        ad.employee_id,
        ad.date,
        (reg_elem->>'cash_register')::int AS cash_register,
        prod_elem->>'productName' AS product_name,
        (prod_elem->>'count')::int AS product_count
    FROM all_data ad
    CROSS JOIN LATERAL jsonb_array_elements(ad.info) AS reg_elem
    CROSS JOIN LATERAL jsonb_array_elements(reg_elem->'products') AS prod_elem
),
product_totals AS ( -- Sum and group by employee, date, register, product
    SELECT
        employee_id,
        date,
        cash_register,
        product_name,
        SUM(product_count) AS total_count
    FROM unwound
    GROUP BY employee_id, date, cash_register, product_name
),
register_group AS ( -- Regroup by register
    SELECT
        employee_id,
        date,
        cash_register,
        jsonb_agg(
            jsonb_build_object(
                'productName', product_name,
                'count', total_count
            )
            ORDER BY product_name
        ) AS products
    FROM product_totals
    GROUP BY employee_id, date, cash_register
),
employee_group AS ( -- Regroup by employee, date
    SELECT
        employee_id,
        date,
        jsonb_agg(
            jsonb_build_object(
                'cash_register', cash_register,
                'products', products
            )
            ORDER BY cash_register
        ) AS info
    FROM register_group
    GROUP BY employee_id, date
)
SELECT *
FROM employee_group
ORDER BY date;

Beyond the classic SQL operations, like UNION, JOIN, GROUP BY, we had to use JSON operators such as jsonb_array_elements(), ->>, jsonb_build_object(), jsonb_agg() to unwind and aggregate.

PostgreSQL follows the standard SQL/JSON since PostgreSQL 17 and the query can be written with JSON_TABLE(), JSON_OBJECT() and JSON_ARRAYAGG()

WITH all_data AS (  
    SELECT employee_id, date, info FROM my_temp  
    UNION ALL  
    SELECT employee_id, date, info FROM server_table  
),  
-- Flatten registers and products in one pass  
unwound AS (  
    SELECT  
        t.employee_id,  
        t.date,  
        jt.
                                    
                                    
                                    

                                        by Franck Pachot
ParadeDB Blog

Elasticsearch Was Never a Database

Elasticsearch is a search engine, not a database. Here’s why it falls short as a system of record.
by James Blackwood-Sewell
ParadeDB Blog

Elasticsearch Was Never a Database

Elasticsearch is a search engine, not a database. Here's why it falls short as a system of record.
by James Blackwood-Sewell

September 17, 2025

Murat Demirbas

Supporting our AI overlords: Redesigning data systems to be Agent-first

This Berkeley systems group paper opens with the thesis that LLM agents will soon dominate data system workloads. These agents, acting on behalf of users, do not query like human analysts or even like the applications written by them. Instead, the LLM agents bombard databases with a storm of exploratory requests: schema inspections, partial aggregates, speculative joins, rollback-heavy what-if updates. The authors calls this behavior agentic speculation.

Agentic speculation is positioned as both the problem and the opportunity. The problem is that traditional DBMSs are built for exact intermittent workloads and cannot handle the high-throughput redundant and inefficient querying of LLM agents. The opportunity also lies here. Agentic speculation has recognizable properties and features that invite new designs. Databases should adapt by offering approximate answers, sharing computation across repeated subplans, caching grounding information in an agentic memory store, and even steering agents with cost estimates or semantic hints.

The paper argues the database must bend to the agent's style. But why don't we also consider the other way around? Why shouldn't agents be trained to issue smarter, fewer, more schema-aware queries? The authors take agent inefficiency as a given, I think, in order to preserve the blackbox nature of general LLM agents. After a walkthrough of the paper, I'll revisit this question as well as other directions that occur to me.  


Case studies

The authors provide experiments to ground their claims about agentic speculation. The first study uses the BIRD benchmark (BIg Bench for LaRge-scale Database Grounded Text-to-SQL Evaluation) with DuckDB as the backend. The goal here is to evaluate how well LLMs can convert natural language questions into SQL queries. I, for one, welcome our SQL wielding AI overlords! Here, agents are instantiated as GPT-4o-mini and Qwen2.5-Coder-7B.

The central finding from Figure 1 is that  accuracy improves with more attempts for both sequential (one agent issuing multiple turns) and parallel setups (many agents in charge at once). The success rate climbs by 14–70% as the number of queries increases. Brute forcing helps, but it also means flooding the database with redundant queries.

Figure 2 drives that point home. Across 50 independent attempts at a single task, fewer than 10–20% of query subplans are unique. Most work is repeated, often  in a trivial manner. Result caching looks like an obvious win here.

The second case study moves beyond single queries into multi-database integration tasks that combine Postgres, MongoDB, DuckDB, and SQLite. Figure 3 plots how OpenAI's o3 model proceeds. Agents begin with metadata exploration (tables, columns), move to partial queries, and eventually to full attempts. But the phases overlap in a messy and uncertain way. The paper then explains that injecting grounding hints into the prompts (such as which column contains information pertinent to the task) reduced the number of queries by more than 20%, which shows how steerability helps. So, the agent is like a loudmouth politician who spews less bullshit when his handlers give him some direction.

The case studies illustrate the four features of agentic speculation: Scale (more attempts do improve success), Redundancy (most attempts repeat prior work), Heterogeneity (workloads mix metadata exploration with partial and complete solutions), and Steerability (agents can be nudged toward efficiency). 


Architecture

The proposed architecture aims to redesign the database stack for agent workloads. The key idea is that LLM agents send probes instead of bare SQL. These probes include not just queries but also "briefs" (natural language descriptions of intent, tolerance for approximation, and hints about the phase of work). Communication is key, folks! The database, in turn, parses these probes through an agentic interpreter, optimizes them via a probe optimizer that satisfices rather than guarantees exact results. It then executes these queries against a storage layer augmented with an agentic memory store and a shared transaction manager designed for speculative branching and rollback. Alongside answers, the system may return proactive feedback (hints, cost estimates, schema nudges) to steer agents.

The architecture is maybe too tidy. It shows a single agent swarm funneling probes into a single database engine, which responds in kind. So, this looks very much like a single-client, single-node system. There is no real discussion of multi-tenancy: what happens when two clients, with different goals and different access privileges, hit the same backend? Does one client's agentic memory contaminate another's? Are cached probes and approximations shared across tenants, and if so, who arbitrates correctness and privacy? These questions are briefly mentioned in privacy concerns in Section 6, but the architecture itself is silent. Whether this single-client abstraction can scale to the real, distributed, multi-tenant world remains as the important question.


Query Interfaces

Section 4 focuses on the interface between agents and databases. Probes extend SQL into a dialogue by bundling multiple queries together with a natural-language "brief" describing goals, priorities, and tolerance for error. This allows the system to understand not just what is being asked, but why. For example, is the agent is in metadata exploration or solution formulation mode? The database can then prioritize accordingly and provide a rough sample for schema discovery, and a more exact computation for validation.

Two directions stand out for me. First, on the agent-to-system side, probes may request things SQL cannot express, like "find tables semantically related to electronics". This would require embedding-based similarity operators built into the DBMS.

Second, on the system-to-agent side, the database is now encouraged to become proactive, returning not just answers but feedback. These "sleeper agents" inside the DB can explain why a query returned empty results, suggest alternative tables, or give cost estimates so the agent can rethink a probe before execution. 


Processing and Optimizing Probes

Section 5 focuses on how to process probes at scale, and what does it mean to optimize them? The key shift is that the database no longer aims for exact answers to each query. Instead, it seeks to satisfice: provide results that are good enough for the agent to decide its next step.

The paper calls this the approximation technique, and presents it as two folds. First, it provides exploratory scaffolding: quick samples, coarse aggregates, and partial results that help the agent discover which tables, filters, and joins matter. Second, it can be decision-making approximation: estimates with bounded error that may themselves be the final answer, because the human behind the agent cares more about trends than exact counts.

Let's consider the task of finding reasons for why profits in coffee bean sales in Berkeley was low this year relative to last. A human analyst would cut to the chase: they would join sales with stores, compare 2024 vs. 2025, then check returns or closures. A schema-blind LLM agent would issue a flood of redundant queries, many of them dead ends. The proposed system here splits the difference: it prunes irrelevant exploration, offers approximate aggregates up front (coffee sales down ~15%), and caches this in memory so later probes can build from it.

To achieve this, the probe optimizer adapts familiar techniques. Multi-query optimization collapses redundant subplans, approximate query processing provides fast sketches instead of full scans, and incremental evaluation streams partial results with early stopping when the trend is clear. The optimizer works both within a batch of probes (intra-probe) and across turns (inter-probe). It caches results and materializes common joins so that the agent's next attempts don't repeat the same work. The optimization goal is not minimizing per-query latency but minimizing the total interaction time between agent and database, a subtle but important shift.


Indexing, Storage, and Transactions

Section 6 addresses the lower layers of the stack: indexing, storage, and transactions. In order to deal with the  dynamic, overlapping, and branch-heavy nature of agentic speculation, it proposes an agentic memory store for semantic grounding, and a new transactional model for branched updates.

The agentic memory store is essentially a semantic cache. It stores results of prior probes, metadata, column encodings, and even embeddings to support similarity search. This way, when the agent inevitably repeats itself, the system can serve cached or related results. The open problem is staleness: if schemas or data evolve, cached grounding may mislead future probes. Traditional DBs handle this through strict invalidation (drop and recompute indexes, refresh materialized views). The paper hints that agentic memory may simply be good enough until corrected, a looser consistency model that may suit LLM's temperament. 

For branched updates, the paper proposes "a new transactions framework that is centered on state sharing across probes, each of which may be independently attempting to complete a user-defined sequence of updates". The paper argues for multi-world isolation: each branch must be logically isolated, but may physically overlap to exploit shared state. Supporting thousands of concurrent speculative branches requires something beyond Postgres-style MVCC or Aurora's copy-on-write snapshots. 


Discussion

The paper offers an ambitious rethinking of how databases should respond to the arrival of LLM agents. This naturally leaves several open questions for discussion.

In my view, the paper frames the problem asymmetrically: agents are messy, exploratory, redundant, so databases must bend to accommodate them. But is that the only path forward? Alternatively, agents could be fine-tuned to issue smarter probes that are more schema-aware, less redundant, more considerate of cost. A protocol of mutual compromise seems more sustainable than a one-sided redesign. Otherwise we risk ossifying the data systems around today's inefficient LLM habits.

Multi-client operation remains an open issue. The architecture is sketched as though one user's army of agents owns the system. Real deployments will have many clients, with different goals and different access rights, colliding on the same backend. What does agentic memory mean in this context? Similarly, how does load management work? How do we allocate resources fairly among tenants when each may field thousands of speculative queries per second? Traditional databases long ago developed notions of connection pooling, admission control, and multi-tenant isolation; agent-first systems will need new equivalents attuned to speculation.

Finally, there is the question of distribution. The architecture as presented looks like a single-node system: one interpreter, one optimizer, one agentic memory, one transaction manager. Yet the workloads described are precisely the heavy workloads that drove databases toward distributed execution. How should agentic memory be partitioned or replicated across nodes? How would speculative branching work here? How can bandwidth limits be managed when repeated scans, approximate sampling, and multi-query optimization saturate storage I/O? How can cross-shard communication be kept from overwhelming the system when speculative branches and rollbacks trigger network communication at scale?


Future Directions: A Neurosymbolic Angle

If we squint, there is a neurosymbolic flavor to this entire setup. LLM agents represent the neural side: fuzzy reasoning, associative/semantic search, and speculative exploration. Databases constitute the symbolic side with schemas, relational algebra, logical operators, and transactional semantics. The paper is then all about creating an interface where the neural can collaborate with the symbolic by combining the flexibility of learned models with the structure and rigor of symbolic systems.

Probes are already halfway to symbolic logic queries: part SQL fragments, part logical forms, and part neural briefs encoding intent and constraints. If databases learn to proactively steer agents with rules and constraints, and if agents learn to ask more structured probes, the result would look even more like a neurosymbolic reasoning system, where neural components generate hypotheses and symbolic databases test, prune, and ground them. If that happens, we can talk about building a new kind of reasoning stack where the two halves ground and reinforce each other.


MongoDB as an AI-First Platform

Document databases offer an interesting angle on the AI–database melding problem. Their schema flexibility and tolerance for semistructured data make them well-suited for the exploratory phase of agent workloads, when LLMs are still feeling out what fields and joins matter. The looseness of document stores may align naturally with the fuzziness of LLM probes, especially when embeddings are brought into play for semantic search.

MongoDB's acquisition of Voyage AI points at this convergence. With built-in embeddings and vector search, MongoDB aims to support probes that ask for semantically similar documents and provide approximate retrieval early in the exploration phase.


How the 2018 Berkeley AI-Systems Vision Fared

Back in 2018, Berkeley systems group presented a broad vision of the systems challenges for AI. Continuing our tradition of checking in on influential Berkeley AI-systems papers, let's give a brief evaluation. Many of its predictions were directionally correct: specialized hardware, privacy and federated learning, and explainability. Others remain underdeveloped, like cloud–edge integration and  continual learning in dynamic environments. What it clearly missed was the rise and dominance of LLMs as the interface to data and applications. As I said back then, plans are useless, but planning is indispensable.

Compared with that blue sky agenda, this new Agent-First Data Systems paper is more technical, grounded, and focused. It does not try to map a decade of AI systems research, but rather focuses on a single pressing problem and proposes mechanisms to cope.

by Murat (noreply@blogger.com)
Percona Database Performance Blog

MySQL with Diagrams Part Three: The Life Story of the Writing Process

When you run a simple write, …it may look simple, but under the hood, MySQL’s InnoDB engine kicks off a pretty complex sequence to ensure your data stays safe, consistent, and crash-recoverable. In the top-left corner of the diagram, we see exactly where this begins — the moment the query is executed: [crayon-68cab7e2c68be431462678/] The log […]
by Yunus Uyanik

September 16, 2025

Percona Database Performance Blog

What is Percona’s Transparent Data Encryption Extension for PostgreSQL (pg_tde)?

If you’re running PostgreSQL in a regulated industry, you know the frustration: your compliance auditor demands data-at-rest encryption, but PostgreSQL doesn’t offer it natively. Your only options in the past? Pay premium prices for proprietary forks or accept compliance gaps that keep you awake at night. Percona has changed that. With Percona for PostgreSQL, you […]
by David Quilty
Franck Pachot

MongoDB Multikey Indexes and Index Bound Optimization

Previously, I discussed how MongoDB keeps track of whether indexed fields contain arrays. This matters because if the database knows a filter is operating on scalar values, it can optimize index range scans.

As an example, let's begin with a collection that has an index on two fields, both containing only scalar values:

db.demo.createIndex( { field1:1, field2:1 } );

db.demo.insertOne(
 { _id: 0, field1: 2           , field2: "y"               },
);

With arrays, each combination is stored as a separate entry in the index. Instead of diving into the internals (as in the previous post), you can visualize this with an aggregation pipeline by unwinding every field:

db.demo.aggregate([ 
 { $unwind: "$field1" }, 
 { $unwind: "$field2" }, 
 { $project: { field1: 1, field2: 1, "document _id": "$_id", _id: 0 } },
 { $sort: { "document _id": 1 } } 
]);

[ { field1: 2, field2: 'y', 'document _id': 0 } ]

Note that this is simply an example to illustrate how index entries are created, ordered, and how they belong to a document. In a real index, the internal key is used. However, since showRecordId() can be used only on cursors and not in aggregation pipelines, I displayed "_id" instead.

Single-key index with bound intersection

As I have documents with only scalars, there's only one entry for this document, and the index is not multikey and this is visible in the execution plan as isMultiKey: false and multiKeyPaths: { field1: [], field2: [] } with empty markers:

db.demo.find(
 { field1: { $gt: 1, $lt: 3 } }
).explain("executionStats").executionStats;

{
  executionSuccess: true,
  nReturned: 1,
  executionTimeMillis: 0,
  totalKeysExamined: 1,
  totalDocsExamined: 1,
  executionStages: {
    isCached: false,
    stage: 'FETCH',
    nReturned: 1,
    executionTimeMillisEstimate: 0,
    works: 2,
    advanced: 1,
    needTime: 0,
    needYield: 0,
    saveState: 0,
    restoreState: 0,
    isEOF: 1,
    docsExamined: 1,
    alreadyHasObj: 0,
    inputStage: {
      stage: 'IXSCAN',
      nReturned: 1,
      executionTimeMillisEstimate: 0,
      works: 2,
      advanced: 1,
      needTime: 0,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      keyPattern: { field1: 1, field2: 1 },
      indexName: 'field1_1_field2_1',
      isMultiKey: false,
      multiKeyPaths: { field1: [], field2: [] },
      isUnique: false,
      isSparse: false,
      isPartial: false,
      indexVersion: 2,
      direction: 'forward',
      indexBounds: { field1: [ '(1, 3)' ], field2: [ '[MinKey, MaxKey]' ] },
      keysExamined: 1,
      seeks: 1,
      dupsTested: 0,
      dupsDropped: 0
    }
  }
}

As the query planner knows that there's only one index entry per document, the filter { field1: {$gt: 1, $lt: 3} } can be applied as one index range indexBounds: { field1: [ '(1, 3)' ], field2: [ '[MinKey, MaxKey]' ] }, reading only the index entry (keysExamined: 1) required to get the result (nReturned: 1).

Multikey index with bound intersection

I add a document with an array in field2:

db.demo.insertOne(
 { _id:1,  field1: 2           , field2: [ "x", "y", "z" ] },
);

My visualization of the index entries show multiple keys per document:

db.demo.aggregate([ 
 { $unwind: "$field1" }, 
 { $unwind: "$field2" }, 
 { $project: { field1: 1, field2: 1, "document _id": "$_id", _id: 0 } } ,
 { $sort: { "document _id": 1 } }
]);

[
  { field1: 2, field2: 'y', 'document _id': 0 },
  { field1: 2, field2: 'x', 'document _id': 1 },
  { field1: 2, field2: 'y', 'document _id': 1 },
  { field1: 2, field2: 'z', 'document _id': 1 }
]

The index is marked as multikey (isMultiKey: true), but only for field2 (multiKeyPaths: { field1: [], field2: [ 'field2' ] }):

db.demo.find(
 { field1: { $gt: 1, $lt: 3 } }
).explain("executionStats").executionStats;

{
  executionSuccess: true,
  nReturned: 2,
  executionTimeMillis: 0,
  totalKeysExamined: 4,
  totalDocsExamined: 2,
  executionStages: {
    isCached: false,
    stage: 'FETCH',
    nReturned: 2,
    executionTimeMillisEstimate: 0,
    works: 5,
    advanced: 2,
    needTime: 2,
    needYield: 0,
    saveState: 0,
    restoreState: 0,
    isEOF: 1,
    docsExamined: 2,
    alreadyHasObj: 0,
    inputStage: {
      stage: 'IXSCAN',
      nReturned: 2,
      executionTimeMillisEstimate: 0,
      works: 5,
      advanced: 2,
      needTime: 2,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      keyPattern: { field1: 1, field2: 1 },
      indexName: 'field1_1_field2_1',
      isMultiKey: true,
      multiKeyPaths: { field1: [], field2: [ 'field2' ] },
      isUnique: false,
      isSparse: false,
      isPartial: false,
      indexVersion: 2,
      direction: 'forward',
      indexBounds: { field1: [ '(1, 3)' ], field2: [ '[MinKey, MaxKey]' ] },
      keysExamined: 4,
      seeks: 1,
      dupsTested: 4,
      dupsDropped: 2
    }
  }
}

As the query planner knows that there's only one value per document for field1, the multiple keys have all the same value for this field. The filter can apply on it with tight bounds ((1, 3)) and the multiple keys are deduplicated (dupsTested: 4, dupsDropped: 2).

Multikey index with larger bound

I add a document with an array in field1:

db.demo.insertOne(
 { _id:2, field1: [ 0,5 ] , field2: "x"               },
);

My visualization of the index entries show the combinations:

db.demo.aggregate([ 
 { $unwind: "$field1" }, 
 { $unwind: "$field2" }, 
 { $project: { field1: 1, field2: 1, "document _id": "$_id", _id: 0 } } ,
 { $sort: { "document _id": 1 } }
]);

[
  { field1: 2, field2: 'y', 'document _id': 0 },
  { field1: 2, field2: 'x', 'document _id': 1 },
  { field1: 2, field2: 'y', 'document _id': 1 },
  { field1: 2, field2: 'z', 'document _id': 1 },
  { field1: 0, field2: 'x', 'document _id': 2 },
  { field1: 5, field2: 'x', 'document _id': 2 }
]

If I apply the filter to the collection, the document with {_id: 2} matches because it has values of field1 greater than 1 and values of field1 lower than 3 (I didn't use $elemMatch to apply to the same element):

db.demo.aggregate([ 
 { $match: { field1: { $gt: 1, $lt: 3 } } },
 { $unwind: "$field1" }, 
 { $unwind: "$field2" }, 
 { $project: { field1: 1, field2: 1, "document _id": "$_id", _id: 0 } } ,
 { $sort: { "document _id": 1 } }
]);

[
  { field1: 2, field2: 'y', 'document _id': 0 },
  { field1: 2, field2: 'x', 'document _id': 1 },
  { field1: 2, field2: 'y', 'document _id': 1 },
  { field1: 2, field2: 'z', 'document _id': 1 },
  { field1: 0, field2: 'x', 'document _id': 2 },
  { field1: 5, field2: 'x', 'document _id': 2 }
]

However, if I apply the same filter after the $unwind, that simulates the index entries, there's no entry for {_id: 2} that verifies { field1: { $gt: 1, $lt: 3 } }:

db.demo.aggregate([ 
 { $unwind: "$field1" }, 
 { $match: { field1: { $gt: 1, $lt: 3 } } },
 { $unwind: "$field2" }, 
 { $project: { field1: 1, field2: 1, "document _id": "$_id", _id: 0 } } ,
 { $sort: { "document _id": 1 } }
]);

[
  { field1: 2, field2: 'y', 'document _id': 0 },
  { field1: 2, field2: 'x', 'document _id': 1 },
  { field1: 2, field2: 'y', 'document _id': 1 },
  { field1: 2, field2: 'z', 'document _id': 1 }
]

This shows that the query planner cannot utilize the tight range field1: [ '(1, 3)' ] anymore. Because field1 is multikey (multiKeyPaths: { field1: [ 'field1' ] }), the planner can only apply the bound to the leading index field during the index scan. The other predicate must be evaluated after fetching the document that contains the whole array (filter: { field1: { '$gt': 1 } }):

db.demo.find(
 { field1: { $gt: 1, $lt: 3 } }
).explain("executionStats").executionStats;

{
  executionSuccess: true,
  nReturned: 3,
  executionTimeMillis: 0,
  totalKeysExamined: 5,
  totalDocsExamined: 3,
  executionStages: {
    isCached: false,
    stage: 'FETCH',
    filter: { field1: { '$gt': 1 } },
    nReturned: 3,
    executionTimeMillisEstimate: 0,
    works: 7,
    advanced: 3,
    needTime: 2,
    needYield: 0,
    saveState: 0,
    restoreState: 0,
    isEOF: 1,
    docsExamined: 3,
    alreadyHasObj: 0,
    inputStage: {
      stage: 'IXSCAN',
      nReturned: 3,
      executionTimeMillisEstimate: 0,
      works: 6,
      advanced: 3,
      needTime: 2,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      keyPattern: { field1: 1, field2: 1 },
      indexName: 'field1_1_field2_1',
      isMultiKey: true,
      multiKeyPaths: { field1: [ 'field1' ], field2: [ 'field2' ] },
      isUnique: false,
      isSparse: false,
      isPartial: false,
      indexVersion: 2,
      direction: 'forward',
      indexBounds: { field1: [ '[-inf.0, 3)' ], field2: [ '[MinKey, MaxKey]' ] },
      keysExamined: 5,
      seeks: 1,
      dupsTested: 5,
      dupsDropped: 2
    }
  }
}

This execution plan is logically equivalent to the following:

db.demo.aggregate([  
  // simulate index entries as combinations
  { $unwind: "$field1" },  
  { $unwind: "$field2" },  
  // simulate index range scan bounds
  { $match: { field1: { $lt: 3 } } },  
  // simulate deduplication
  { $group: { _id: "$_id" } },   
  // simulate fetching the full document
  { $lookup: { from: "demo", localField: "_id", foreignField: "_id", as: "fetch" } },   
  { $unwind: "$fetch" },  
  { $replaceRoot: { newRoot: "$fetch" } },
  // apply the remaining filter
  { $match: { field1: { $gt: 1 } } }, 
]).explain();

I show this to make it easier to understand why MongoDB cannot intersect the index bounds in this case, resulting in a wider scan range ([MinKey, MaxKey]), especially when the filter must apply to multiple keys (this behavior would be different if the query used $elemMatch). MongoDB allows flexible schema where a field can be an array, but keeps track of it to optimize the index range scan when it is known that there are only scalars in a field.

Final notes

MongoDB’s flexible schema lets you embed many‑to‑many relationships within a document, improving data locality and often eliminating the need for joins. Despite this, most queries traverse one-to-many relationships, building hierarchical views for particular use cases. When optimizing access through secondary indexes, it’s important not to combine too many multikey fields. If you attempt to do so, MongoDB will block both the creation of such indexes and insertion into collections containing them:

db.demo.insertOne(
 { _id:3, field1: [ 0,5 ] , field2: [ "x", "y", "z" ] },
);

MongoServerError: cannot index parallel arrays [field2] [field1]

MongoDB doesn't allow compound indexes on two array fields from the same document, known as "parallel arrays." Indexing such fields would generate a massive number of index entries due to all possible element combinations, making maintenance and semantics unmanageable. If you attempt this, MongoDB rejects it with a "cannot index parallel arrays" error. This rule ensures index entries remain well-defined. This is per-document, so partial indexes may be created as long a same index doesn't have entries for multiple arrays from the same document.

While the execution plan may display isMultiKey and multiKeyPaths, the multiKey status is managed on a per-index and per-field path basis. This state is updated automatically as documents are inserted, updated, or deleted, and stored in the index metadata, as illustrated in the previous post, rather than recalculated dynamically for each query.

I used top-level fields in this demonstration but arrays can be nested — which is why the list is called "path". multiKeyPaths is the list of dot-separated paths within a field that cause the index to be multikey (i.e., where MongoDB encountered arrays).

MongoDB determines precise index bounds on compound indexes by tracking which fields are multikey using multiKeyPaths metadata. This allows optimized range scans on scalar fields, even if other indexed fields contain arrays. What the query planner can do depends on the path and the presence of array in a field within the path. There are examples documented in Compound Bounds of Multiple Fields from the Same Array. To show a quick example, I add a field with a nested array and an index on its fields:

db.demo.createIndex( { "obj.sub1":1, "obj.sub2":1 } )
;
db.demo.insertOne(
 { _id:4, obj: { sub1: [ 1, 2, 3 ] , sub2:  "x" } },
);
db.demo.find(
 { "obj.sub1": { $gt:1 } , "obj.sub2": { $lt: 3 }  }
).explain("executionStats").executionStats
;

The plan shows that the multikey status comes from only one array field (obj.sub1) and all filters are pushed down to the index bounds:

      keyPattern: { 'obj.sub1': 1, 'obj.sub2': 1 },
      isMultiKey: true,
      multiKeyPaths: { 'obj.sub1': [ 'obj.sub1' ], 'obj.sub2': [] },
      indexBounds: {
        'obj.sub1': [ '(1, inf.0]' ],
        'obj.sub2': [ '[-inf.0, 3)' ]
      }

I insert another document with an array at higher level:

db.demo.insertOne(
 { _id:5, obj: [ { sub1: [ 1, 2, 
                                    
                                    
                                    

                                        by Franck Pachot
Supabase Blog

Defense in Depth for MCP Servers

Learn about the security risks of connecting AI agents to databases and how to implement defense in depth strategies to protect your data from prompt injection attacks.

September 15, 2025

Percona Database Performance Blog

Deploying Percona Operator for MongoDB Across GKE Clusters with MCS

Ever needed a robust, highly available MongoDB setup that spans multiple Kubernetes clusters on GCP? This step-by-step guide shows you how to deploy the Percona Operator for MongoDB in two GKE clusters, linking them using Multi-Cluster Services (MCS) for seamless cross-cluster discovery and connectivity. Step 1: Prepare your GKE clusters & enable MCS 1. Prepare […]
by Ivan Groenewold
Phil Eaton

In response to a developer asking about systems

Sometimes I get asked questions that would be more fun to answer in public. All letters are treated as anonymous unless permission is otherwise granted.

Hey [Redacted]! It's great to hear from you. I'm very glad you joined the coffee club and met some good folks. :)

You asked how to learn about systems. A great question! I think I need to start first with what I mean when I say systems.

My definition of systems is all of the underlying software we developers use but are taught not to think about because they are so solid: our compilers and interpreters, our databases, our operating system, our browser, and so on. We think of them as basically not having bugs, we just count on them to be correct and fast enough so we can build the applications that really matter to users.

But 1) some developers do actually have to work on these fundamental blocks (compilers, databases, operating systems, browsers, etc.) and 2) it's not thaaaat hard to get into this development professionally and 3) even if you don't get into it professionally, having a better understanding of these fundamental blocks will make you a better application developer. At least I think so.

To get into systems I think it starts by you just questioning how each layer you build on works. Try building that layer yourself. For example you've probably used a web framework like Rails or Next.js. But you can just go and write that layer yourself too (for education).

And you've probably used Postgres or SQLite or DynamoDB. But you can also just go and write that layer yourself (for education). It's this habit of thinking and digging into the next lower layer that will get you into systems. Basically, not being satisfied with the black box.

I do not think there are many good books on programming in general, and very very few must-read ones, but one that I recommend to everybody is Designing Data Intensive Applications. I think it's best if you read it with a group of people. (My book club will read it in December when the 2nd edition comes out, you should join.) But this book is specific to data obviously and not interested in the fundamentals of other systems things like compilers or operating systems or browsers or so on.

Also, I see getting into this as a long-term thing. Throughout my whole career (almost 11 years now) I definitely always tried to dig into compilers and interpreters, I wrote and blogged about toy implementations a lot. And then 5 years ago I started digging into databases and saw that there was more career potential there. But it still took 4 years until I got my first job as a developer working on a database (the job I currently have).

Things take time to learn and that's ok! You have a long career to look forward to. And if you end up not wanting to dig into this stuff that's totally fine too. I think very few developers actually do. And they still have fine careers.

Anyway, I hope this is at least mildly useful. I hope you join nycsystems.xyz as well and look forward to seeing you at future coffee clubs!

Cheers,
Phil

September 14, 2025

Franck Pachot

MongoDB Internals: How Collections and Indexes Are Stored in WiredTiger

WiredTiger is MongoDB’s default storage engine, but what really occurs behind the scenes when collections and indexes are saved to disk? In this short deep dive, we’ll explore the internals of WiredTiger data files, covering everything from _mdb_catalog metadata and B-Tree page layouts to BSON storage, primary and secondary indexes, and multi-key array handling. The goal is to introduce useful low-level tools like wt and other utilities.

I ran this experiment in a Docker container, set up as described in a previous blog post:

docker run --rm -it --cap-add=SYS_PTRACE mongo bash

# install required packages
apt-get update && apt-get install -y git xxd strace curl jq python3 python3-dev python3-pip python3-venv python3-pymongo python3-bson build-essential cmake gcc g++ libstdc++-12-dev libtool autoconf automake swig liblz4-dev zlib1g-dev libmemkind-dev libsnappy-dev libsodium-dev libzstd-dev

# get WiredTiger main branch
curl -L $(curl -s https://api.github.com/repos/wiredtiger/wiredtiger/releases/latest | jq -r '.tarball_url') -o wiredtiger.tar.gz

git clone https://github.com/wiredtiger/wiredtiger.git
cd wiredtiger

# Compile
mkdir build && cmake -S /wiredtiger -B /wiredtiger/build \
        -DCMAKE_C_FLAGS="-O0 -Wno-error -Wno-format-overflow -Wno-error=array-bounds -Wno-error=format-overflow -Wno-error=nonnull" \
        -DHAVE_BUILTIN_EXTENSION_SNAPPY=1 \
        -DCMAKE_BUILD_TYPE=Release
cmake --build /wiredtiger/build

# add `wt` binaries and other tools in the PATH
export PATH=$PATH:/wiredtiger/build:/wiredtiger/tools

# Start mongodb
mongod &

I use the mongo image, add the WiredTiger sources from the main branch, compile it to get wt, and start mongod.

I create a small collection with three documents, and an index, and stop mongod:

mongosh <<'JS'
db.franck.insertMany([
 {_id:"aaa",val1:"xxx",val2:"yyy",val3:"zzz",msg:"hello world"},
 {_id:"bbb",val1:"xxx",val2:"yyy",val3:"zzz",msg:["hello","world"]},
 {_id:"ccc",val1:"xxx",val2:"yyy",val3:"zzz",msg:["hello","world","hello","again"]}
]);
db.franck.createIndex({_id:1,val1:1,val2:1,val3:1,msg:1});
db.franck.find().showRecordId();
use admin;
db.shutdownServer();
JS

I stop MongoDB so that I can access the WiredTiger files with wt without them being opened and locked by another program. Before stopping, I displayed the documents:

[
  {
    _id: 'aaa',
    val1: 'xxx',
    val2: 'yyy',
    val3: 'zzz',
    msg: 'hello world',
    '$recordId': Long('1')
  },
  {
    _id: 'bbb',
    val1: 'xxx',
    val2: 'yyy',
    val3: 'zzz',
    msg: [ 'hello', 'world' ],
    '$recordId': Long('2')
  },
  {
    _id: 'ccc',
    val1: 'xxx',
    val2: 'yyy',
    val3: 'zzz',
    msg: [ 'hello', 'world', 'hello', 'again' ],
    '$recordId': Long('3')
  }
]

The files are stored in the default WiredTiger directory /data/db

MongoDB catalog, which maps the MongoDB collections to their storage attributes, is stored in a WiredTiger table _mdb_catalog. The default WiredTiger directory is /data/db:

root@72cf410c04cb:/wiredtiger# ls -altU /data/db

drwxr-xr-x. 4 root    root       32 Sep  1 23:10 ..
-rw-------. 1 root    root        0 Sep 13 20:33 mongod.lock
drwx------. 2 root    root       74 Sep 13 20:29 journal
-rw-------. 1 root    root       21 Sep 12 22:47 WiredTiger.lock
-rw-------. 1 root    root       50 Sep 12 22:47 WiredTiger
-rw-------. 1 root    root    73728 Sep 13 20:33 WiredTiger.wt
-rw-r--r--. 1 root    root     1504 Sep 13 20:33 WiredTiger.turtle
-rw-------. 1 root    root     4096 Sep 13 20:33 WiredTigerHS.wt
-rw-------. 1 root    root    36864 Sep 13 20:33 sizeStorer.wt
-rw-------. 1 root    root    36864 Sep 13 20:33 _mdb_catalog.wt
-rw-------. 1 root    root      114 Sep 12 22:47 storage.bson
-rw-------. 1 root    root    20480 Sep 13 20:33 collection-0-3767590060964183367.wt
-rw-------. 1 root    root    20480 Sep 13 20:33 index-1-3767590060964183367.wt
-rw-------. 1 root    root    36864 Sep 13 20:33 collection-2-3767590060964183367.wt
-rw-------. 1 root    root    36864 Sep 13 20:33 index-3-3767590060964183367.wt
-rw-------. 1 root    root    20480 Sep 13 20:20 collection-4-3767590060964183367.wt
-rw-------. 1 root    root    20480 Sep 13 20:20 index-5-3767590060964183367.wt
-rw-------. 1 root    root    20480 Sep 13 20:33 index-6-3767590060964183367.wt
drwx------. 2 root    root     4096 Sep 13 20:33 diagnostic.data
drwx------. 3 root    root       21 Sep 13 20:17 .mongodb
-rw-------. 1 root    root    20480 Sep 13 20:33 collection-0-6917019827977430149.wt
-rw-------. 1 root    root    20480 Sep 13 20:23 index-1-6917019827977430149.wt
-rw-------. 1 root    root    20480 Sep 13 20:25 index-2-6917019827977430149.wt

Catalog

_mdb_catalog maps MongoDB names to WiredTiger table names. wt lists the key (recordId) and value (BSON):

root@72cf410c04cb:~# wt -h /data/db dump table:_mdb_catalog

WiredTiger Dump (WiredTiger Version 12.0.0)
Format=print
Header
table:_mdb_catalog
access_pattern_hint=none,allocation_size=4KB,app_metadata=(formatVersion=1),assert=(commit_timestamp=none,durable_timestamp=none,read_timestamp=none,write_timestamp=off),block_allocation=best,block_compressor=snappy,block_manager=default,cache_resident=false,checksum=on,colgroups=,collator=,columns=,dictionary=0,disaggregated=(page_log=),encryption=(keyid=,name=),exclusive=false,extractor=,format=btree,huffman_key=,huffman_value=,ignore_in_memory_cache_size=false,immutable=false,import=(compare_timestamp=oldest_timestamp,enabled=false,file_metadata=,metadata_file=,panic_corrupt=true,repair=false),in_memory=false,ingest=,internal_item_max=0,internal_key_max=0,internal_key_truncate=true,internal_page_max=4KB,key_format=q,key_gap=10,leaf_item_max=0,leaf_key_max=0,leaf_page_max=32KB,leaf_value_max=64MB,log=(enabled=true),lsm=(auto_throttle=,bloom=,bloom_bit_count=,bloom_config=,bloom_hash_count=,bloom_oldest=,chunk_count_limit=,chunk_max=,chunk_size=,merge_max=,merge_min=),memory_page_image_max=0,memory_page_max=10m,os_cache_dirty_max=0,os_cache_max=0,prefix_compression=false,prefix_compression_min=4,source="file:_mdb_catalog.wt",split_deepen_min_child=0,split_deepen_per_child=0,split_pct=90,stable=,tiered_storage=(auth_token=,bucket=,bucket_prefix=,cache_directory=,local_retention=300,name=,object_target_size=0),type=file,value_format=u,verbose=[],write_timestamp_usage=none
Data
\81
r\01\00\00\03md\00\eb\00\00\00\02ns\00\15\00\00\00admin.system.version\00\03options\00 \00\00\00\05uuid\00\10\00\00\00\04\ba\fc\c2\a9;EC\94\9d\a1\df(\c9\87\eaW\00\04indexes\00\97\00\00\00\030\00\8f\00\00\00\03spec\00.\00\00\00\10v\00\02\00\00\00\03key\00\0e\00\00\00\10_id\00\01\00\00\00\00\02name\00\05\00\00\00_id_\00\00\08ready\00\01\08multikey\00\00\03multikeyPaths\00\10\00\00\00\05_id\00\01\00\00\00\00\00\00\12head\00\00\00\00\00\00\00\00\00\08backgroundSecondary\00\00\00\00\00\03idxIdent\00+\00\00\00\02_id_\00\1c\00\00\00index-1-3767590060964183367\00\00\02ns\00\15\00\00\00admin.system.version\00\02ident\00!\00\00\00collection-0-3767590060964183367\00\00
\82
\7f\01\00\00\03md\00\fb\00\00\00\02ns\00\12\00\00\00local.startup_log\00\03options\003\00\00\00\05uuid\00\10\00\00\00\042}_\a9\16,L\13\aa*\09\b5<\ea\aa\d6\08capped\00\01\10size\00\00\00\a0\00\00\04indexes\00\97\00\00\00\030\00\8f\00\00\00\03spec\00.\00\00\00\10v\00\02\00\00\00\03key\00\0e\00\00\00\10_id\00\01\00\00\00\00\02name\00\05\00\00\00_id_\00\00\08ready\00\01\08multikey\00\00\03multikeyPaths\00\10\00\00\00\05_id\00\01\00\00\00\00\00\00\12head\00\00\00\00\00\00\00\00\00\08backgroundSecondary\00\00\00\00\00\03idxIdent\00+\00\00\00\02_id_\00\1c\00\00\00index-3-3767590060964183367\00\00\02ns\00\12\00\00\00local.startup_log\00\02ident\00!\00\00\00collection-2-3767590060964183367\00\00
\83
^\02\00\00\03md\00\a7\01\00\00\02ns\00\17\00\00\00config.system.sessions\00\03options\00 \00\00\00\05uuid\00\10\00\00\00\04D\09],\c6\15FG\b6\e2m!\ba\c4j<\00\04indexes\00Q\01\00\00\030\00\8f\00\00\00\03spec\00.\00\00\00\10v\00\02\00\00\00\03key\00\0e\00\00\00\10_id\00\01\00\00\00\00\02name\00\05\00\00\00_id_\00\00\08ready\00\01\08multikey\00\00\03multikeyPaths\00\10\00\00\00\05_id\00\01\00\00\00\00\00\00\12head\00\00\00\00\00\00\00\00\00\08backgroundSecondary\00\00\00\031\00\b7\00\00\00\03spec\00R\00\00\00\10v\00\02\00\00\00\03key\00\12\00\00\00\10lastUse\00\01\00\00\00\00\02name\00\0d\00\00\00lsidTTLIndex\00\10expireAfterSeconds\00\08\07\00\00\00\08ready\00\01\08multikey\00\00\03multikeyPaths\00\14\00\00\00\05lastUse\00\01\00\00\00\00\00\00\12head\00\00\00\00\00\00\00\00\00\08backgroundSecondary\00\00\00\00\00\03idxIdent\00Y\00\00\00\02_id_\00\1c\00\00\00index-5-3767590060964183367\00\02lsidTTLIndex\00\1c\00\00\00index-6-3767590060964183367\00\00\02ns\00\17\00\00\00config.system.sessions\00\02ident\00!\00\00\00collection-4-3767590060964183367\00\00
\84
\a6\02\00\00\03md\00\e6\01\00\00\02ns\00\0c\00\00\00test.franck\00\03options\00 \00\00\00\05uuid\00\10\00\00\00\04>\04\ec\e2SUK\ca\98\e8\bf\fe\0eu\81L\00\04indexes\00\9b\01\00\00\030\00\8f\00\00\00\03spec\00.\00\00\00\10v\00\02\00\00\00\03key\00\0e\00\00\00\10_id\00\01\00\00\00\00\02name\00\05\00\00\00_id_\00\00\08ready\00\01\08multikey\00\00\03multikeyPaths\00\10\00\00\00\05_id\00\01\00\00\00\00\00\00\12head\00\00\00\00\00\00\00\00\00\08backgroundSecondary\00\00\00\031\00\01\01\00\00\03spec\00q\00\00\00\10v\00\02\00\00\00\03key\005\00\00\00\10_id\00\01\00\00\00\10val1\00\01\00\00\00\10val2\00\01\00\00\00\10val3\00\01\00\00\00\10msg\00\01\00\00\00\00\02name\00!\00\00\00_id_1_val1_1_val2_1_val3_1_msg_1\00\00\08ready\00\01\08multikey\00\01\03multikeyPaths\00?\00\00\00\05_id\00\01\00\00\00\00\00\05val1\00\01\00\00\00\00\00\05val2\00\01\00\00\00\00\00\05val3\00\01\00\00\00\00\00\05msg\00\01\00\00\00\00\01\00\12head\00\00\00\00\00\00\00\00\00\08backgroundSecondary\00\00\00\00\00\03idxIdent\00m\00\00\00\02_id_\00\1c\00\00\00index-1-6917019827977430149\00\02_id_1_val1_1_val2_1_val3_1_msg_1\00\1c\00\00\00index-2-6917019827977430149\00\00\02ns\00\0c\00\00\00test.franck\00\02ident\00!\00\00\00collection-0-6917019827977430149\00\00

I can decode the BSON value with wt_to_mdb_bson.py to display it as JSON, and use jq to filter the file information about the collection I've created:

wt -h /data/db dump -x table:_mdb_catalog |
 wt_to_mdb_bson.py -m dump -j |
 jq 'select(.value.ns == "test.franck") |
     {ns: .value.ns, ident: .value.ident, idxIdent: .value.idxIdent}
'

{
  "ns": "test.franck",
  "ident": "collection-0-6917019827977430149",
  "idxIdent": {
    "_id_": "index-1-6917019827977430149",
    "_id_1_val1_1_val2_1_val3_1_msg_1": "index-2-6917019827977430149"
  }
}

ident is the WiredTiger table name (collection-...) for the collection documents. All collections have a primary key index on "_id" and additional secondary indexes, stored in WiredTiger tables (index-...). These indexes are stored as .wt files in the data directory.

Collection

Using the WiredTiger table name for the collection, I dump the content, keys, and values, and decode it as JSON:

wt -h /data/db dump -x table:collection-0-6917019827977430149 |
 wt_to_mdb_bson.py -m dump -j 

{"key": "81", "value": {"_id": "aaa", "val1": "xxx", "val2": "yyy", "val3": "zzz", "msg": "hello world"}}
{"key": "82", "value": {"_id": "bbb", "val1": "xxx", "val2": "yyy", "val3": "zzz", "msg": ["hello", "world"]}}
{"key": "83", "value": {"_id": "ccc", "val1": "xxx", "val2": "yyy", "val3": "zzz", "msg": ["hello", "world", "hello", "again"]}}

The "key" here is the recordId — an internal, unsigned 64-bit integer MongoDB uses (when not using clustered collections) to order documents in the collection table. The 0x80 offset is because the storage key is stored as a signed 8‑bit integer, but encoded in an order-preserving way.

I can also use wt_binary_decode.py to look at the file blocks. Here is the leaf page (page type: 7 (WT_PAGE_ROW_LEAF)) that contains my three documents as six key and value cells (cells (oflow len): 6) :

wt_binary_decode.py --offset 4096 --page 1 --verbose --split --bson /data/db/collection-0-6917019827977430149.wt

/data/db/collection-0-6917019827977430149.wt, position 0x1000/0x5000, pagelimit 1
Decode at 4096 (0x1000)
                                               0: 00 00 00 00 00 00 00 00 1f 0f 00 00 00 00 00 00 5f 01 00 00
                                                  06 00 00 00 07 04 00 01 00 10 00 00 64 0a ec 4b 01 00 00 00
Page Header:
  recno: 0
  writegen: 3871
  memsize: 351
  ncells (oflow len): 6
  page type: 7 (WT_PAGE_ROW_LEAF)
  page flags: 0x4
  version: 1
Block Header:
  disk_size: 4096
  checksum: 0x4bec0a64
  block flags: 0x1
0:                                            28: 05 81
  desc: 0x5 short key 1 bytes:
  <packed 1 (0x1)>
1:                                            2a: 80 91 51 00 00 00 02 5f 69 64 00 04 00 00 00 61 61 61 00 02
                                                  76 61 6c 31 00 04 00 00 00 78 78 78 00 02 76 61 6c 32 00 04
                                                  00 00 00 79 79 79 00 02 76 61 6c 33 00 04 00 00 00 7a 7a 7a
                                                  00 02 6d 73 67 00 0c 00 00 00 68 65 6c 6c 6f 20 77 6f 72 6c
                                                  64 00 00
  cell is valid BSON
  { '_id': 'aaa',
  'msg': 'hello world',
  'val1': 'xxx',
  'val2': 'yyy',
  'val3': 'zzz'}
2:                                            7d: 05 82
  desc: 0x5 short key 1 bytes:
  <packed 2 (0x2)>
3:                                            7f: 80 a0 60 00 00 00 02 5f 69 64 00 04 00 00 00 62 62 62 00 02
                                                  76 61 6c 31 00 04 00 00 00 78 78 78 00 02 76 61 6c 32 00 04
                                                  00 00 00 79 79 79 00 02 76 61 6c 33 00 04 00 00 00 7a 7a 7a
                                                  00 04 6d 73 67 00 1f 00 00 00 02 30 00 06 00 00 00 68 65 6c
                                                  6c 6f 00 02 31 00 06 00 00 00 77 6f 72 6c 64 00 00 00
  cell is valid BSON
  { '_id': 'bbb',
  'msg': ['hello', 'world'],
  'val1': 'xxx',
  'val2': 'yyy',
  'val3': 'zzz'}
4:                                            e1: 05 83
  desc: 0x5 short key 1 bytes:
  <packed 3 (0x3)>
5:                                            e3: 80 ba 7a 00 00 00 02 5f 69 64 00 04 00 00 00 63 63 63 00 02
                                                  76 61 6c 31 00 04 00 00 00 78 78 78 00 02 76 61 6c 32 00 04
                                                  00 00 00 79 79 79 00 02 76 61 6c 33 00 04 00 00 00 7a 7a 7a
                                                  00 04 6d 73 67 00 39 00 00 00 02 30 00 06 00 00 00 68 65 6c
                                                  6c 6f 00 02 31 00 06 00 00 00 77 6f 72 6c 64 00 02 32 00 06
                                                  00 00 00 68 65 6c 6c 6f 00 02 33 00 06 00 00 00 61 67 61 69
                                                  6e 00 00 00
  cell is valid BSON
  { '_id': 'ccc',
  'msg': ['hello', 'world', 'hello', 'again'],
  'val1': 'xxx',
  'val2': 'yyy',
  'val3': 'zzz'}

The script shows the raw hexadecimal bytes for the key, a description of the cell type, and the decoded logical value using WiredTiger’s order‑preserving integer encoding (packed int encoding). In this example, the raw byte 0x81 decodes to record ID 1:

0:                                            28: 05 81
  desc: 0x5 short key 1 bytes:
  <packed 1 (0x1)>

Here is the branch page (page type: 6 (WT_PAGE_ROW_INT)) that references it:

wt_binary_decode.py --offset 8192 --page 1 --verbose --split --bson /data/db/collection-0-6917019827977430149.wt

/data/db/collection-0-6917019827977430149.wt, position 0x2000/0x5000, pagelimit 1
Decode at 8192 (0x2000)
                                               0: 00 00 00 00 00 00 00 00 20 0f 00 00 00 00 00 00 34 00 00 00
                                                  02 00 00 00 06 00 00 01 00 10 00 00 21 df 20 d6 01 00 00 00
Page Header:
  recno: 0
  writegen: 3872
  memsize: 52
  ncells (oflow len): 2
  page type: 6 (WT_PAGE_ROW_INT)
  page flags: 0x0
  version: 1
Block Header:
  disk_size: 4096
  checksum: 0xd620df21
  block flags: 0x1
0:                                            28: 05 00
  desc: 0x5 short key 1 bytes:
  ""
1:                                            2a: 38 00 87 80 81 e4 4b eb ea 24
  desc: 0x38 addr (leaf no-overflow) 7 bytes:
  <packed 0 (0x0)> <packed 1 (0x1)> <packed 1273760356 (0x4bec0a64)>

As we have seen in the previous blog post, the pointer includes the checksum of the page it references (0x4bec0a64) to detect disc corruption.

Another utility, bsondump, can be used to display the output of wt dump -x as JSON, like wt_to_mdb_bson.py, but requires some filtering to get the BSON content:

wt -h /data/db dump -x table:collection-0-6917019827977430149 | # dump in hexa
 egrep '025f696400' | # all documents have an "_id " field
 xxd -r -p | # gets the plain binary data
 bsondump --type=json  # display BSON it as JSON

{"_id":"aaa","val1":"xxx","val2":"yyy","val3":"zzz","msg":"hello world"}
{"_id":"bbb","val1":"xxx","val2":"yyy","val3":"zzz","msg":["hello","world"]}
{"_id":"ccc","val1":"xxx","val2":"yyy","val3":"zzz","msg":["hello","world","hello","again"]}
2025-09-14T08:57:36.182+0000    3 objects found

It also provides a debug type output that gives more insights into how it is stored internally, especially for documents with arrays:

wt -h /data/db dump -x table:collection-0-6917019827977430149 | # dump in hexa
 egrep '025f696400' | # all documents have an "_id " field
 xxd -r -p | # gets the plain binary data
 bsondump --type=debug  # display BSON as it is stored

--- new object ---
        size : 81
                _id
                        type:    2 size: 13
                val1
                        type:    2 size: 14
                val2
                        type:    2 size: 14
                val3
                        type:    2 size: 14
                msg
                        type:    2 size: 21
--- new object ---
        size : 96
                _id
                        type:    2 size: 13
                val1
                        type:    2 size: 14
                val2
                        type:    2 size: 14
                val3
                        type:    2 size: 14
                msg
                        type:    4 size: 36
                        --- new object ---
                                size : 31
                                        0
                                                type:    2 size: 13
                                        1
                                                type:    2 size: 13
--- new object ---
        size : 122
                _id
                        type:    2 size: 13
                val1
                        type:    2 size: 14
                val2
                        type:    2 size: 14
                val3
                        type:    2 size: 14
                msg
                        type:    4 size: 62
                        --- new object ---
                                size : 57
                                        0
                                                type:    2 size: 13
                                        1
                                                type:    2 size: 13
                                        2
                                                type:    2 size: 13
                                        3
                                                type:    2 size: 13
2025-09-14T08:59:15.268+0000    3 objects found

Arrays in BSON are just sub-objects with the array position as a field name.

Primary index

RecordId is an internal, logical key used in the BTree to store the collection. It allows documents to be physically moved without fragmentation when they're updated. All indexes reference documents by recordId, not their physical location. Access by "_id" requires a unique index created automatically with the collection and stored as another WiredTiger table. Here is the content:

wt -h /data/db dump -p table:index-1-6917019827977430149 

WiredTiger Dump (WiredTiger Version 12.0.0)
Format=print
Header
table:index-1-6917019827977430149
access_pattern_hint=none,allocation_size=4KB,app_metadata=(formatVersion=8),assert=(commit_timestamp=none,durable_timestamp=none,read_timestamp=none,write_timestamp=off),block_allocation=best,block_compressor=,block_manager=default,cache_resident=false,checksum=on,colgroups=,collator=,columns=,dictionary=0,disaggregated=(page_log=),encryption=(keyid=,name=),exclusive=false,extractor=,format=btree,huffman_key=,huffman_value=,ignore_in_memory_cache_size=false,immutable=false,import=(compare_timestamp=oldest_timestamp,enabled=false,file_metadata=,metadata_file=,panic_corrupt=true,repair=false),in_memory=false,ingest=,internal_item_max=0,internal_key_max=0,internal_key_truncate=true,internal_page_max=16k,key_format=u,key_gap=10,leaf_item_max=0,leaf_key_max=0,leaf_page_max=16k,leaf_value_max=0,log=(enabled=true),lsm=(auto_throttle=,bloom=,bloom_bit_count=,bloom_config=,bloom_hash_count=,bloom_oldest=,chunk_count_limit=,chunk_max=,chunk_size=,merge_max=,merge_min=),memory_page_image_max=0,memory_page_max=5MB,os_cache_dirty_max=0,os_cache_max=0,prefix_compression=true,prefix_compression_min=4,source="file:index-1-6917019827977430149.wt",split_deepen_min_child=0,split_deepen_per_child=0,split_pct=90,stable=,tiered_storage=(auth_token=,bucket=,bucket_prefix=,cache_directory=,local_retention=300,name=,object_target_size=0),type=file,value_format=u,verbose=[],write_timestamp_usage=none
Data
<aaa\00\04
\00\08
<bbb\00\04
\00\10
<ccc\00\04
\00\18

There are three entries, one for each document, with the "_id" value (aaa,bbb,ccc) as the key, and the recordId as the value. The values are packed (see documentation), for example < prefixes a little-endian value.
In MongoDB’s KeyString format, the recordId is stored in a special packed encoding where three bits are added to the right of the big-endian value, to be able to store the length at the end of the key. The same is used when it is in the value part of the index entry, in a unique index. To decode it, you need to shift the last byte right by three bits. Here, 0x08 >> 3 = 1, 0x10 >> 3 = 2, and 0x18 >> 3 = 3, which are the recordId of my documents.

I decode the page that contains those index entries:

wt_binary_decode.py --offset 4096 --page 1 --verbose --split /data/db/index-1-6917019827977430149.wt

/data/db/index-1-6917019827977430149.wt, position 0x1000/0x5000, pagelimit 1
Decode at 4096 (0x1000)
                                               0: 00 00 00 00 00 00 00 00 1f 0f 00 00 00 00 00 00 46 00 00 00
                                                  06 00 00 00 07 04 00 01 00 10 00 00 7c d3 87 60 01 00 00 00
Page Header:
  recno: 0
  writegen: 3871
  memsize: 70
  ncells (oflow len): 6
  page type: 7 (WT_PAGE_ROW_LEAF)
  page flags: 0x4
  version: 1
Block Header:
  disk_size: 4096
  checksum: 0x6087d37c
  block flags: 0x1
0:                                            28: 19 3c 61 61 61 00 04
  desc: 0x19 short key 6 bytes:
  "<aaa"
1:                                            2f: 0b 00 08
  desc: 0xb short val 2 bytes:
  "
2:                                            32: 19 3c 62 62 62 00 04
  desc: 0x19 short key 6 bytes:
  "<bbb"
3:                                            39: 0b 00 10
  desc: 0xb short val 2 bytes:
  ""
4:                                            3c: 19 3c 63 63 63 00 04
  desc: 0x19 short key 6 bytes:
  "<ccc"
5:                                            43: 0b 00 18
  desc: 0xb short val 2 bytes:
  ""

This utility doesn't decode the recordId, we need to shift it. There's no BSON to decode in the indexes.

Secondary index

Secondary indexes are similar, except that they can be composed of multiple fields, and any indexed field can contain an array, which may result in multiple index entries for a single document, like an inverted index.

MongoDB tracks which indexed fields contain arrays to improve query planning. A multikey index creates an entry for each array element, and if multiple fields are multikey, it stores entries for all combinations of their values. By knowing exactly which fields are multikey, the query planner can apply tighter index bounds when only one field is involved. This information is stored in the catalog as a "multikey" flag along with the specific "multikeyPaths":


wt -h /data/db dump -x table:_mdb_catalog |
 wt_to_mdb_bson.py -m dump -j |
 jq 'select(.value.ns == "test.franck") | 
     .value.md.indexes[] | 
     {name: .spec.name, key: .spec.key, multikey: .multikey, multikeyPaths: .multikeyPaths | keys}
'

{
  "name": "_id_",
  "key": {
    "_id": { "$numberInt": "1" },
  },
  "multikey": false,
  "multikeyPaths": [
    "_id"
  ]
}
{
  "name": "_id_1_val1_1_val2_1_val3_1_msg_1",
  "key": {
    "_id": { "$numberInt": "1" },
    "val1": { "$numberInt": "1" },
    "val2": { "$numberInt": "1" },
    "val3": { "$numberInt": "1" },
    "msg": { "$numberInt": "1" },
  },
  "multikey": true,
  "multikeyPaths": [
    "_id",
    "msg",
    "val1",
    "val2",
    "val3"
  ]
}

Here is the dump of my index on {_id:1,val1:1,val2:1,val3:1,msg:1}:

wt -h /data/db dump -p table:index-2-6917019827977430149 

WiredTiger Dump (WiredTiger Version 12.0.0)
Format=print
Header
table:index-2-6917019827977430149
access_pattern_hint=none,allocation_size=4KB,app_metadata=(formatVersion=8),assert=(commit_timestamp=none,durable_timestamp=none,read_timestamp=none,write_timestamp=off),block_allocation=best,block_compressor=,block_manager=default,cache_resident=false,checksum=on,colgroups=,collator=,columns=,dictionary=0,disaggregated=(page_log=),encryption=(keyid=,name=),exclusive=false,extractor=,format=btree,huffman_key=,huffman_value=,ignore_in_memory_cache_size=false,immutable=false,import=(compare_timestamp=oldest_timestamp,enabled=false,file_metadata=,metadata_file=,panic_corrupt=true,repair=false),in_memory=false,ingest=,internal_item_max=0,internal_key_max=0,internal_key_truncate=true,internal_page_max=16k,key_format=u,key_gap=10,leaf_item_max=0,leaf_key_max=0,leaf_page_max=16k,leaf_value_max=0,log=(enabled=true),lsm=(auto_throttle=,bloom=,bloom_bit_count=,bloom_config=,bloom_hash_count=,bloom_oldest=,chunk_count_limit=,chunk_max=,chunk_size=,merge_max=,merge_min=),memory_page_image_max=0,memory_page_max=5MB,os_cache_dirty_max=0,os_cache_max=0,prefix_compression=true,prefix_compression_min=4,source="file:index-2-6917019827977430149.wt",split_deepen_min_child=0,split_deepen_per_child=0,split_pct=90,stable=,tiered_storage=(auth_token=,bucket=,bucket_prefix=,cache_directory=,local_retention=300,name=,object_target_size=0),type=file,value_format=u,verbose=[],write_timestamp_usage=none
Data
<aaa\00<xxx\00<yyy\00<zzz\00<hello world\00\04\00\08
(null)
<bbb\00<xxx\00<yyy\00<zzz\00<hello\00\04\00\10
(null)
<bbb\00<xxx\00<yyy\00<zzz
                                    
                                    
                                    

                                        by Franck Pachot
Franck Pachot

MongoDB Internals: How Collections and Indexes Are Stored in WiredTiger

WiredTiger is MongoDB’s default storage engine, but what really occurs behind the scenes when collections and indexes are saved to disk? In this short deep dive, we’ll explore the internals of WiredTiger data files, covering everything from _mdb_catalog metadata and B-tree page layouts to BSON storage, primary and secondary indexes, and multi-key array handling. The goal is to introduce useful low-level tools like wt and other utilities.

I ran this experiment in a Docker container, set up as described in a previous blog post:

docker run --rm -it --cap-add=SYS_PTRACE mongo bash

# install required packages
apt-get update && apt-get install -y git xxd strace curl jq python3 python3-dev python3-pip python3-venv python3-pymongo python3-bson build-essential cmake gcc g++ libstdc++-12-dev libtool autoconf automake swig liblz4-dev zlib1g-dev libmemkind-dev libsnappy-dev libsodium-dev libzstd-dev

# get WiredTiger main branch
curl -L $(curl -s https://api.github.com/repos/wiredtiger/wiredtiger/releases/latest | jq -r '.tarball_url') -o wiredtiger.tar.gz

git clone https://github.com/wiredtiger/wiredtiger.git
cd wiredtiger

# Compile
mkdir build && cmake -S /wiredtiger -B /wiredtiger/build \
        -DCMAKE_C_FLAGS="-O0 -Wno-error -Wno-format-overflow -Wno-error=array-bounds -Wno-error=format-overflow -Wno-error=nonnull" \
        -DHAVE_BUILTIN_EXTENSION_SNAPPY=1 \
        -DCMAKE_BUILD_TYPE=Release
cmake --build /wiredtiger/build

# add `wt` binaries and other tools in the PATH
export PATH=$PATH:/wiredtiger/build:/wiredtiger/tools

# Start mongodb
mongod &

I use the mongo image, add the WiredTiger sources from the main branch, compile it to get wt, and start mongod.

I create a small collection with three documents and an index, and stop mongod:

mongosh <<'JS'
db.franck.insertMany([
 {_id:"aaa",val1:"xxx",val2:"yyy",val3:"zzz",msg:"hello world"},
 {_id:"bbb",val1:"xxx",val2:"yyy",val3:"zzz",msg:["hello","world"]},
 {_id:"ccc",val1:"xxx",val2:"yyy",val3:"zzz",msg:["hello","world","hello","again"]}
]);
db.franck.createIndex({_id:1,val1:1,val2:1,val3:1,msg:1});
db.franck.find().showRecordId();
use admin;
db.shutdownServer();
JS

I stop MongoDB so that I can access the WiredTiger files with wt without them being opened and locked by another program. Before stopping, I displayed the documents:

[
  {
    _id: 'aaa',
    val1: 'xxx',
    val2: 'yyy',
    val3: 'zzz',
    msg: 'hello world',
    '$recordId': Long('1')
  },
  {
    _id: 'bbb',
    val1: 'xxx',
    val2: 'yyy',
    val3: 'zzz',
    msg: [ 'hello', 'world' ],
    '$recordId': Long('2')
  },
  {
    _id: 'ccc',
    val1: 'xxx',
    val2: 'yyy',
    val3: 'zzz',
    msg: [ 'hello', 'world', 'hello', 'again' ],
    '$recordId': Long('3')
  }
]

The files are stored in the default WiredTiger directory /data/db. MongoDB catalog, which maps the MongoDB collections to their storage attributes, is stored in a WiredTiger table _mdb_catalog. The default WiredTiger directory is /data/db:

root@72cf410c04cb:/wiredtiger# ls -altU /data/db

drwxr-xr-x. 4 root    root       32 Sep  1 23:10 ..
-rw-------. 1 root    root        0 Sep 13 20:33 mongod.lock
drwx------. 2 root    root       74 Sep 13 20:29 journal
-rw-------. 1 root    root       21 Sep 12 22:47 WiredTiger.lock
-rw-------. 1 root    root       50 Sep 12 22:47 WiredTiger
-rw-------. 1 root    root    73728 Sep 13 20:33 WiredTiger.wt
-rw-r--r--. 1 root    root     1504 Sep 13 20:33 WiredTiger.turtle
-rw-------. 1 root    root     4096 Sep 13 20:33 WiredTigerHS.wt
-rw-------. 1 root    root    36864 Sep 13 20:33 sizeStorer.wt
-rw-------. 1 root    root    36864 Sep 13 20:33 _mdb_catalog.wt
-rw-------. 1 root    root      114 Sep 12 22:47 storage.bson
-rw-------. 1 root    root    20480 Sep 13 20:33 collection-0-3767590060964183367.wt
-rw-------. 1 root    root    20480 Sep 13 20:33 index-1-3767590060964183367.wt
-rw-------. 1 root    root    36864 Sep 13 20:33 collection-2-3767590060964183367.wt
-rw-------. 1 root    root    36864 Sep 13 20:33 index-3-3767590060964183367.wt
-rw-------. 1 root    root    20480 Sep 13 20:20 collection-4-3767590060964183367.wt
-rw-------. 1 root    root    20480 Sep 13 20:20 index-5-3767590060964183367.wt
-rw-------. 1 root    root    20480 Sep 13 20:33 index-6-3767590060964183367.wt
drwx------. 2 root    root     4096 Sep 13 20:33 diagnostic.data
drwx------. 3 root    root       21 Sep 13 20:17 .mongodb
-rw-------. 1 root    root    20480 Sep 13 20:33 collection-0-6917019827977430149.wt
-rw-------. 1 root    root    20480 Sep 13 20:23 index-1-6917019827977430149.wt
-rw-------. 1 root    root    20480 Sep 13 20:25 index-2-6917019827977430149.wt

Catalog

_mdb_catalog maps MongoDB names to WiredTiger table names. wt lists the key (recordId) and value (BSON):

root@72cf410c04cb:~# wt -h /data/db dump table:_mdb_catalog

WiredTiger Dump (WiredTiger Version 12.0.0)
Format=print
Header
table:_mdb_catalog
access_pattern_hint=none,allocation_size=4KB,app_metadata=(formatVersion=1),assert=(commit_timestamp=none,durable_timestamp=none,read_timestamp=none,write_timestamp=off),block_allocation=best,block_compressor=snappy,block_manager=default,cache_resident=false,checksum=on,colgroups=,collator=,columns=,dictionary=0,disaggregated=(page_log=),encryption=(keyid=,name=),exclusive=false,extractor=,format=btree,huffman_key=,huffman_value=,ignore_in_memory_cache_size=false,immutable=false,import=(compare_timestamp=oldest_timestamp,enabled=false,file_metadata=,metadata_file=,panic_corrupt=true,repair=false),in_memory=false,ingest=,internal_item_max=0,internal_key_max=0,internal_key_truncate=true,internal_page_max=4KB,key_format=q,key_gap=10,leaf_item_max=0,leaf_key_max=0,leaf_page_max=32KB,leaf_value_max=64MB,log=(enabled=true),lsm=(auto_throttle=,bloom=,bloom_bit_count=,bloom_config=,bloom_hash_count=,bloom_oldest=,chunk_count_limit=,chunk_max=,chunk_size=,merge_max=,merge_min=),memory_page_image_max=0,memory_page_max=10m,os_cache_dirty_max=0,os_cache_max=0,prefix_compression=false,prefix_compression_min=4,source="file:_mdb_catalog.wt",split_deepen_min_child=0,split_deepen_per_child=0,split_pct=90,stable=,tiered_storage=(auth_token=,bucket=,bucket_prefix=,cache_directory=,local_retention=300,name=,object_target_size=0),type=file,value_format=u,verbose=[],write_timestamp_usage=none
Data
\81
r\01\00\00\03md\00\eb\00\00\00\02ns\00\15\00\00\00admin.system.version\00\03options\00 \00\00\00\05uuid\00\10\00\00\00\04\ba\fc\c2\a9;EC\94\9d\a1\df(\c9\87\eaW\00\04indexes\00\97\00\00\00\030\00\8f\00\00\00\03spec\00.\00\00\00\10v\00\02\00\00\00\03key\00\0e\00\00\00\10_id\00\01\00\00\00\00\02name\00\05\00\00\00_id_\00\00\08ready\00\01\08multikey\00\00\03multikeyPaths\00\10\00\00\00\05_id\00\01\00\00\00\00\00\00\12head\00\00\00\00\00\00\00\00\00\08backgroundSecondary\00\00\00\00\00\03idxIdent\00+\00\00\00\02_id_\00\1c\00\00\00index-1-3767590060964183367\00\00\02ns\00\15\00\00\00admin.system.version\00\02ident\00!\00\00\00collection-0-3767590060964183367\00\00
\82
\7f\01\00\00\03md\00\fb\00\00\00\02ns\00\12\00\00\00local.startup_log\00\03options\003\00\00\00\05uuid\00\10\00\00\00\042}_\a9\16,L\13\aa*\09\b5<\ea\aa\d6\08capped\00\01\10size\00\00\00\a0\00\00\04indexes\00\97\00\00\00\030\00\8f\00\00\00\03spec\00.\00\00\00\10v\00\02\00\00\00\03key\00\0e\00\00\00\10_id\00\01\00\00\00\00\02name\00\05\00\00\00_id_\00\00\08ready\00\01\08multikey\00\00\03multikeyPaths\00\10\00\00\00\05_id\00\01\00\00\00\00\00\00\12head\00\00\00\00\00\00\00\00\00\08backgroundSecondary\00\00\00\00\00\03idxIdent\00+\00\00\00\02_id_\00\1c\00\00\00index-3-3767590060964183367\00\00\02ns\00\12\00\00\00local.startup_log\00\02ident\00!\00\00\00collection-2-3767590060964183367\00\00
\83
^\02\00\00\03md\00\a7\01\00\00\02ns\00\17\00\00\00config.system.sessions\00\03options\00 \00\00\00\05uuid\00\10\00\00\00\04D\09],\c6\15FG\b6\e2m!\ba\c4j<\00\04indexes\00Q\01\00\00\030\00\8f\00\00\00\03spec\00.\00\00\00\10v\00\02\00\00\00\03key\00\0e\00\00\00\10_id\00\01\00\00\00\00\02name\00\05\00\00\00_id_\00\00\08ready\00\01\08multikey\00\00\03multikeyPaths\00\10\00\00\00\05_id\00\01\00\00\00\00\00\00\12head\00\00\00\00\00\00\00\00\00\08backgroundSecondary\00\00\00\031\00\b7\00\00\00\03spec\00R\00\00\00\10v\00\02\00\00\00\03key\00\12\00\00\00\10lastUse\00\01\00\00\00\00\02name\00\0d\00\00\00lsidTTLIndex\00\10expireAfterSeconds\00\08\07\00\00\00\08ready\00\01\08multikey\00\00\03multikeyPaths\00\14\00\00\00\05lastUse\00\01\00\00\00\00\00\00\12head\00\00\00\00\00\00\00\00\00\08backgroundSecondary\00\00\00\00\00\03idxIdent\00Y\00\00\00\02_id_\00\1c\00\00\00index-5-3767590060964183367\00\02lsidTTLIndex\00\1c\00\00\00index-6-3767590060964183367\00\00\02ns\00\17\00\00\00config.system.sessions\00\02ident\00!\00\00\00collection-4-3767590060964183367\00\00
\84
\a6\02\00\00\03md\00\e6\01\00\00\02ns\00\0c\00\00\00test.franck\00\03options\00 \00\00\00\05uuid\00\10\00\00\00\04>\04\ec\e2SUK\ca\98\e8\bf\fe\0eu\81L\00\04indexes\00\9b\01\00\00\030\00\8f\00\00\00\03spec\00.\00\00\00\10v\00\02\00\00\00\03key\00\0e\00\00\00\10_id\00\01\00\00\00\00\02name\00\05\00\00\00_id_\00\00\08ready\00\01\08multikey\00\00\03multikeyPaths\00\10\00\00\00\05_id\00\01\00\00\00\00\00\00\12head\00\00\00\00\00\00\00\00\00\08backgroundSecondary\00\00\00\031\00\01\01\00\00\03spec\00q\00\00\00\10v\00\02\00\00\00\03key\005\00\00\00\10_id\00\01\00\00\00\10val1\00\01\00\00\00\10val2\00\01\00\00\00\10val3\00\01\00\00\00\10msg\00\01\00\00\00\00\02name\00!\00\00\00_id_1_val1_1_val2_1_val3_1_msg_1\00\00\08ready\00\01\08multikey\00\01\03multikeyPaths\00?\00\00\00\05_id\00\01\00\00\00\00\00\05val1\00\01\00\00\00\00\00\05val2\00\01\00\00\00\00\00\05val3\00\01\00\00\00\00\00\05msg\00\01\00\00\00\00\01\00\12head\00\00\00\00\00\00\00\00\00\08backgroundSecondary\00\00\00\00\00\03idxIdent\00m\00\00\00\02_id_\00\1c\00\00\00index-1-6917019827977430149\00\02_id_1_val1_1_val2_1_val3_1_msg_1\00\1c\00\00\00index-2-6917019827977430149\00\00\02ns\00\0c\00\00\00test.franck\00\02ident\00!\00\00\00collection-0-6917019827977430149\00\00

I can decode the BSON value with wt_to_mdb_bson.py to display it as JSON, and use jq to filter the file information about the collection I've created:

wt -h /data/db dump -x table:_mdb_catalog |
 wt_to_mdb_bson.py -m dump -j |
 jq 'select(.value.ns == "test.franck") |
     {ns: .value.ns, ident: .value.ident, idxIdent: .value.idxIdent}
'

{
  "ns": "test.franck",
  "ident": "collection-0-6917019827977430149",
  "idxIdent": {
    "_id_": "index-1-6917019827977430149",
    "_id_1_val1_1_val2_1_val3_1_msg_1": "index-2-6917019827977430149"
  }
}

ident is the WiredTiger table name (collection-...) for the collection documents. All collections have a primary key index on "_id" and additional secondary indexes, stored in WiredTiger tables (index-...). These indexes are stored as .wt files in the data directory.

Collection

Using the WiredTiger table name for the collection, I dump the content, keys, and values, and decode it as JSON:

wt -h /data/db dump -x table:collection-0-6917019827977430149 |
 wt_to_mdb_bson.py -m dump -j 

{"key": "81", "value": {"_id": "aaa", "val1": "xxx", "val2": "yyy", "val3": "zzz", "msg": "hello world"}}
{"key": "82", "value": {"_id": "bbb", "val1": "xxx", "val2": "yyy", "val3": "zzz", "msg": ["hello", "world"]}}
{"key": "83", "value": {"_id": "ccc", "val1": "xxx", "val2": "yyy", "val3": "zzz", "msg": ["hello", "world", "hello", "again"]}}

The "key" here is the recordId—an internal, unsigned 64-bit integer MongoDB uses (when not using clustered collections) to order documents in the collection table. The 0x80 offset is because the storage key is stored as a signed 8‑bit integer, but encoded in an order-preserving way.

I can also use wt_binary_decode.py to look at the file pages. Here is the leaf page (page type: 7 (WT_PAGE_ROW_LEAF)) that contains my three documents as six key and value cells (cells (oflow len): 6) :

wt_binary_decode.py --offset 4096 --page 1 --verbose --split --bson /data/db/collection-0-6917019827977430149.wt

/data/db/collection-0-6917019827977430149.wt, position 0x1000/0x5000, pagelimit 1
Decode at 4096 (0x1000)
                                               0: 00 00 00 00 00 00 00 00 1f 0f 00 00 00 00 00 00 5f 01 00 00
                                                  06 00 00 00 07 04 00 01 00 10 00 00 64 0a ec 4b 01 00 00 00
Page Header:
  recno: 0
  writegen: 3871
  memsize: 351
  ncells (oflow len): 6
  page type: 7 (WT_PAGE_ROW_LEAF)
  page flags: 0x4
  version: 1
Block Header:
  disk_size: 4096
  checksum: 0x4bec0a64
  block flags: 0x1
0:                                            28: 05 81
  desc: 0x5 short key 1 bytes:
  <packed 1 (0x1)>
1:                                            2a: 80 91 51 00 00 00 02 5f 69 64 00 04 00 00 00 61 61 61 00 02
                                                  76 61 6c 31 00 04 00 00 00 78 78 78 00 02 76 61 6c 32 00 04
                                                  00 00 00 79 79 79 00 02 76 61 6c 33 00 04 00 00 00 7a 7a 7a
                                                  00 02 6d 73 67 00 0c 00 00 00 68 65 6c 6c 6f 20 77 6f 72 6c
                                                  64 00 00
  cell is valid BSON
  { '_id': 'aaa',
  'msg': 'hello world',
  'val1': 'xxx',
  'val2': 'yyy',
  'val3': 'zzz'}
2:                                            7d: 05 82
  desc: 0x5 short key 1 bytes:
  <packed 2 (0x2)>
3:                                            7f: 80 a0 60 00 00 00 02 5f 69 64 00 04 00 00 00 62 62 62 00 02
                                                  76 61 6c 31 00 04 00 00 00 78 78 78 00 02 76 61 6c 32 00 04
                                                  00 00 00 79 79 79 00 02 76 61 6c 33 00 04 00 00 00 7a 7a 7a
                                                  00 04 6d 73 67 00 1f 00 00 00 02 30 00 06 00 00 00 68 65 6c
                                                  6c 6f 00 02 31 00 06 00 00 00 77 6f 72 6c 64 00 00 00
  cell is valid BSON
  { '_id': 'bbb',
  'msg': ['hello', 'world'],
  'val1': 'xxx',
  'val2': 'yyy',
  'val3': 'zzz'}
4:                                            e1: 05 83
  desc: 0x5 short key 1 bytes:
  <packed 3 (0x3)>
5:                                            e3: 80 ba 7a 00 00 00 02 5f 69 64 00 04 00 00 00 63 63 63 00 02
                                                  76 61 6c 31 00 04 00 00 00 78 78 78 00 02 76 61 6c 32 00 04
                                                  00 00 00 79 79 79 00 02 76 61 6c 33 00 04 00 00 00 7a 7a 7a
                                                  00 04 6d 73 67 00 39 00 00 00 02 30 00 06 00 00 00 68 65 6c
                                                  6c 6f 00 02 31 00 06 00 00 00 77 6f 72 6c 64 00 02 32 00 06
                                                  00 00 00 68 65 6c 6c 6f 00 02 33 00 06 00 00 00 61 67 61 69
                                                  6e 00 00 00
  cell is valid BSON
  { '_id': 'ccc',
  'msg': ['hello', 'world', 'hello', 'again'],
  'val1': 'xxx',
  'val2': 'yyy',
  'val3': 'zzz'}

The script shows the raw hexadecimal bytes for the key, a description of the cell type, and the decoded logical value using WiredTiger’s order‑preserving integer encoding (packed int encoding). In this example, the raw byte 0x81 decodes to record ID 1:

0:                                            28: 05 81
  desc: 0x5 short key 1 bytes:
  <packed 1 (0x1)>

Here is the branch page (page type: 6 (WT_PAGE_ROW_INT)) that references it:

wt_binary_decode.py --offset 8192 --page 1 --verbose --split --bson /data/db/collection-0-6917019827977430149.wt

/data/db/collection-0-6917019827977430149.wt, position 0x2000/0x5000, pagelimit 1
Decode at 8192 (0x2000)
                                               0: 00 00 00 00 00 00 00 00 20 0f 00 00 00 00 00 00 34 00 00 00
                                                  02 00 00 00 06 00 00 01 00 10 00 00 21 df 20 d6 01 00 00 00
Page Header:
  recno: 0
  writegen: 3872
  memsize: 52
  ncells (oflow len): 2
  page type: 6 (WT_PAGE_ROW_INT)
  page flags: 0x0
  version: 1
Block Header:
  disk_size: 4096
  checksum: 0xd620df21
  block flags: 0x1
0:                                            28: 05 00
  desc: 0x5 short key 1 bytes:
  ""
1:                                            2a: 38 00 87 80 81 e4 4b eb ea 24
  desc: 0x38 addr (leaf no-overflow) 7 bytes:
  <packed 0 (0x0)> <packed 1 (0x1)> <packed 1273760356 (0x4bec0a64)>

As we have seen in the previous blog post, the pointer includes the checksum of the page it references (0x4bec0a64) to detect disc corruption.

Another utility, bsondump, can be used to display the output of wt dump -x as JSON, like wt_to_mdb_bson.py, but requires some filtering to get the BSON content:

wt -h /data/db dump -x table:collection-0-6917019827977430149 | # dump in hexa
 egrep '025f696400' | # all documents have an "_id " field
 xxd -r -p | # gets the plain binary data
 bsondump --type=json  # display BSON it as JSON

{"_id":"aaa","val1":"xxx","val2":"yyy","val3":"zzz","msg":"hello world"}
{"_id":"bbb","val1":"xxx","val2":"yyy","val3":"zzz","msg":["hello","world"]}
{"_id":"ccc","val1":"xxx","val2":"yyy","val3":"zzz","msg":["hello","world","hello","again"]}
2025-09-14T08:57:36.182+0000    3 objects found

It also provides a debug type output that gives more insights into how it is stored internally, especially for documents with arrays:

wt -h /data/db dump -x table:collection-0-6917019827977430149 | # dump in hexa
 egrep '025f696400' | # all documents have an "_id " field
 xxd -r -p | # gets the plain binary data
 bsondump --type=debug  # display BSON as it is stored

--- new object ---
        size : 81
                _id
                        type:    2 size: 13
                val1
                        type:    2 size: 14
                val2
                        type:    2 size: 14
                val3
                        type:    2 size: 14
                msg
                        type:    2 size: 21
--- new object ---
        size : 96
                _id
                        type:    2 size: 13
                val1
                        type:    2 size: 14
                val2
                        type:    2 size: 14
                val3
                        type:    2 size: 14
                msg
                        type:    4 size: 36
                        --- new object ---
                                size : 31
                                        0
                                                type:    2 size: 13
                                        1
                                                type:    2 size: 13
--- new object ---
        size : 122
                _id
                        type:    2 size: 13
                val1
                        type:    2 size: 14
                val2
                        type:    2 size: 14
                val3
                        type:    2 size: 14
                msg
                        type:    4 size: 62
                        --- new object ---
                                size : 57
                                        0
                                                type:    2 size: 13
                                        1
                                                type:    2 size: 13
                                        2
                                                type:    2 size: 13
                                        3
                                                type:    2 size: 13
2025-09-14T08:59:15.268+0000    3 objects found

Arrays in BSON are just sub-objects with the array position as a field name.

Primary index

RecordId is an internal, logical key used in the B-tree to store the collection. It allows documents to be physically moved without fragmentation when they're updated. All indexes reference documents by recordId, not their physical location. Access by "_id" requires a unique index created automatically with the collection and stored as another WiredTiger table. Here is the content:

wt -h /data/db dump -p table:index-1-6917019827977430149 

WiredTiger Dump (WiredTiger Version 12.0.0)
Format=print
Header
table:index-1-6917019827977430149
access_pattern_hint=none,allocation_size=4KB,app_metadata=(formatVersion=8),assert=(commit_timestamp=none,durable_timestamp=none,read_timestamp=none,write_timestamp=off),block_allocation=best,block_compressor=,block_manager=default,cache_resident=false,checksum=on,colgroups=,collator=,columns=,dictionary=0,disaggregated=(page_log=),encryption=(keyid=,name=),exclusive=false,extractor=,format=btree,huffman_key=,huffman_value=,ignore_in_memory_cache_size=false,immutable=false,import=(compare_timestamp=oldest_timestamp,enabled=false,file_metadata=,metadata_file=,panic_corrupt=true,repair=false),in_memory=false,ingest=,internal_item_max=0,internal_key_max=0,internal_key_truncate=true,internal_page_max=16k,key_format=u,key_gap=10,leaf_item_max=0,leaf_key_max=0,leaf_page_max=16k,leaf_value_max=0,log=(enabled=true),lsm=(auto_throttle=,bloom=,bloom_bit_count=,bloom_config=,bloom_hash_count=,bloom_oldest=,chunk_count_limit=,chunk_max=,chunk_size=,merge_max=,merge_min=),memory_page_image_max=0,memory_page_max=5MB,os_cache_dirty_max=0,os_cache_max=0,prefix_compression=true,prefix_compression_min=4,source="file:index-1-6917019827977430149.wt",split_deepen_min_child=0,split_deepen_per_child=0,split_pct=90,stable=,tiered_storage=(auth_token=,bucket=,bucket_prefix=,cache_directory=,local_retention=300,name=,object_target_size=0),type=file,value_format=u,verbose=[],write_timestamp_usage=none
Data
<aaa\00\04
\00\08
<bbb\00\04
\00\10
<ccc\00\04
\00\18

There are three entries, one for each document, with the "_id" value (aaa,bbb,ccc) as the key, and the recordId as the value. The values are packed (see documentation)—for example, < prefixes a little-endian value.

In MongoDB’s KeyString format, the recordId is stored in a special packed encoding where three bits are added to the right of the big-endian value, to be able to store the length at the end of the key. The same is used when it is in the value part of the index entry, in a unique index. To decode it, you need to shift the last byte right by three bits. Here, 0x08 >> 3 = 1, 0x10 >> 3 = 2, and 0x18 >> 3 = 3, which are the recordId of my documents.

I decode the page that contains those index entries:

wt_binary_decode.py --offset 4096 --page 1 --verbose --split /data/db/index-1-6917019827977430149.wt

/data/db/index-1-6917019827977430149.wt, position 0x1000/0x5000, pagelimit 1
Decode at 4096 (0x1000)
                                               0: 00 00 00 00 00 00 00 00 1f 0f 00 00 00 00 00 00 46 00 00 00
                                                  06 00 00 00 07 04 00 01 00 10 00 00 7c d3 87 60 01 00 00 00
Page Header:
  recno: 0
  writegen: 3871
  memsize: 70
  ncells (oflow len): 6
  page type: 7 (WT_PAGE_ROW_LEAF)
  page flags: 0x4
  version: 1
Block Header:
  disk_size: 4096
  checksum: 0x6087d37c
  block flags: 0x1
0:                                            28: 19 3c 61 61 61 00 04
  desc: 0x19 short key 6 bytes:
  "<aaa"
1:                                            2f: 0b 00 08
  desc: 0xb short val 2 bytes:
  "
2:                                            32: 19 3c 62 62 62 00 04
  desc: 0x19 short key 6 bytes:
  "<bbb"
3:                                            39: 0b 00 10
  desc: 0xb short val 2 bytes:
  ""
4:                                            3c: 19 3c 63 63 63 00 04
  desc: 0x19 short key 6 bytes:
  "<ccc"
5:                                            43: 0b 00 18
  desc: 0xb short val 2 bytes:
  ""

This utility doesn't decode the recordId—we need to shift it. There's no BSON to decode in the indexes.

Secondary index

Secondary indexes are similar, except that they can be composed of multiple fields, and any indexed field can contain an array, which may result in multiple index entries for a single document, like an inverted index.

MongoDB tracks which indexed fields contain arrays to improve query planning. A multikey index creates an entry for each array element, and if multiple fields are multikey, it stores entries for all combinations of their values. By knowing exactly which fields are multikey, the query planner can apply tighter index bounds when only one field is involved. This information is stored in the catalog as a "multikey" flag along with the specific "multikeyPaths":


wt -h /data/db dump -x table:_mdb_catalog |
 wt_to_mdb_bson.py -m dump -j |
 jq 'select(.value.ns == "test.franck") | 
     .value.md.indexes[] | 
     {name: .spec.name, key: .spec.key, multikey: .multikey, multikeyPaths: .multikeyPaths | keys}
'

{
  "name": "_id_",
  "key": {
    "_id": { "$numberInt": "1" },
  },
  "multikey": false,
  "multikeyPaths": [
    "_id"
  ]
}
{
  "name": "_id_1_val1_1_val2_1_val3_1_msg_1",
  "key": {
    "_id": { "$numberInt": "1" },
    "val1": { "$numberInt": "1" },
    "val2": { "$numberInt": "1" },
    "val3": { "$numberInt": "1" },
    "msg": { "$numberInt": "1" },
  },
  "multikey": true,
  "multikeyPaths": [
    "_id",
    "msg",
    "val1",
    "val2",
    "val3"
  ]
}

Here is the dump of my index on {_id:1,val1:1,val2:1,val3:1,msg:1}:

wt -h /data/db dump -p table:index-2-6917019827977430149 

WiredTiger Dump (WiredTiger Version 12.0.0)
Format=print
Header
table:index-2-6917019827977430149
access_pattern_hint=none,allocation_size=4KB,app_metadata=(formatVersion=8),assert=(commit_timestamp=none,durable_timestamp=none,read_timestamp=none,write_timestamp=off),block_allocation=best,block_compressor=,block_manager=default,cache_resident=false,checksum=on,colgroups=,collator=,columns=,dictionary=0,disaggregated=(page_log=),encryption=(keyid=,name=),exclusive=false,extractor=,format=btree,huffman_key=,huffman_value=,ignore_in_memory_cache_size=false,immutable=false,import=(compare_timestamp=oldest_timestamp,enabled=false,file_metadata=,metadata_file=,panic_corrupt=true,repair=false),in_memory=false,ingest=,internal_item_max=0,internal_key_max=0,internal_key_truncate=true,internal_page_max=16k,key_format=u,key_gap=10,leaf_item_max=0,leaf_key_max=0,leaf_page_max=16k,leaf_value_max=0,log=(enabled=true),lsm=(auto_throttle=,bloom=,bloom_bit_count=,bloom_config=,bloom_hash_count=,bloom_oldest=,chunk_count_limit=,chunk_max=,chunk_size=,merge_max=,merge_min=),memory_page_image_max=0,memory_page_max=5MB,os_cache_dirty_max=0,os_cache_max=0,prefix_compression=true,prefix_compression_min=4,source="file:index-2-6917019827977430149.wt",split_deepen_min_child=0,split_deepen_per_child=0,split_pct=90,stable=,tiered_storage=(auth_token=,bucket=,bucket_prefix=,cache_directory=,local_retention=300,name=,object_target_size=0),type=file,value_format=u,verbose=[],write_timestamp_usage=none
Data
<aaa\00<xxx\00<yyy\00<zzz\00<hello world\00\04\00\08
(null)
<bbb\00<xxx\00<yyy\00<zzz\00<hello\00\04\00\10
(null)
<bbb\00<xxx\00<yyy\00<zzz
                                    
                                    
                                    

                                        by Franck Pachot