Franck Pachot

I'm curious to find out whether Oracle still uses DerbyDB in their Oracle Database Appliances to store the metadata, and what they will replace it with

If the banner has AI in lower and upper case, it must be great

First/Last per Group: PostgreSQL DISTINCT ON and MongoDB DISTINCT_SCAN Performance On Stack Overflow, the most frequent question for PostgreSQL is: "Select first row in each GROUP BY...

SELECT DISTINCT ON ... ORDER BY equivalent in MongoDB (and faster)

dev.to/franckpachot...

Was just trying to be funny 😭 AI and vibe coding, resulting in too many buffer pins

I can think of 100s of reasons but you said 23ai, so... vibe pinning?

WiredTigerHS.wt: MongoDB MVCC Durable History Store MongoDB uses the WiredTiger storage engine, which implements Multi‑Version Concurrency Control (MVCC)...

More WiredTiger internals: persisting MongoDB's committed MVCC versions through checkpoints and restarts dev.to/franckpachot...

Trust me I was so against it when I arrived at spark I’ve used it in the past and hated it.
but the lack of relations is actually amazing for backfill. it just makes it so simple and painless that I’m willing to live with the mongo of it all

and store the buffer in aifiedt.buf to be more AI friendly

Blog post for September 2025

SQL Developer isn't entirely CBO-friendly.
jonathanlewis.wordpress.com/2025/09/24/s...

MongoDB Search Index Internals with Luke (Lucene Toolbox GUI tool) Previously, I demonstrated MongoDB text search scoring with a simple example, creating a dynamic...

A closer look at MongoDB Text Search: which terms are indexed for a string, how they're stored, and what formula is used to calculate the query's matching score. Since it's based on Lucene, let's use Lucene tools. dev.to/franckpachot...

Text Search with MongoDB and PostgreSQL MongoDB Search Indexes provide full‑text search capabilities directly within MongoDB, allowing...

Comparing 🍏 to 🍊, and MongoDB to PostgreSQL, with full-text search indexes

dev.to/franckpachot...

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...

An example of MongoDB aggregation pipeline compared to the equivalent in SQL/JSON with common table expressions (CTE), from a StackOverflow question: dev.to/franckpachot...

MongoDB Multikey Indexes and Index Bound Optimization Previously, I discussed how MongoDB keeps track of whether indexed fields contain arrays. This...

A flexible schema permits arrays in fields without upfront declaration, and filters and indexes (multiple keys per document) use it automatically. MongoDB tracks multikey paths in indexed fields to optimize index scans:

dev.to/franckpachot...

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...

MongoDB’s strength is not only in its flexible, document-oriented API, but also in its ability to store documents to disk without random I/O slicing or vacuuming later. Here's how MongoDB persists collections and indexes in WiredTiger:

dev.to/franckpachot...

Encryption is not yet there in PostgreSQL

Queryable Encryption - Database Manual - MongoDB Docs

As that's on disk and malicious users with host access may access memory, the only full protection is encryption from the client. MongoDB has such queryable encryption:
www.mongodb.com/docs/manual/...

Yes, more difficult as you have to calculate checksum and propagate to parent. Encryption protects from reading/changing block content

Resilience of MongoDB's WiredTiger Storage Engine to Disk Failure Compared to PostgreSQL and Oracle There have been jokes that have contributed to persistent myths about MongoDB's durability. The...

For nearly a decade, MongoDB provided reliable persistence with one of the most robust storage engines. Yet somehow, the oldest jokes keep persisting, too, so here are some facts:

dev.to/franckpachot...

DocumentDB: Comparing Emulation Internals with MongoDB MongoDB is the leading database for document data modeling, with its Atlas service available on AWS,...

MongoDB is popular, inspiring databases to mimic its features on top of RDBMS. Examining execution plans can provide insight into how things work—such as how to simulate multi-key indexes in a database engine designed for single-key indexes on normalized schemas
dev.to/aws-heroes/d...

Updates to the Same Value: MongoDB Optimization In MongoDB, when you update a field to the same value it already holds, the database optimizes the...

If you update a row/document with the same values, is it still an update, or should the database skip the write? SQL vs NoSQL.
dev.to/franckpachot...

Embedding into JSONB still feels like a JOIN for large documents Think PostgreSQL with JSONB can replace a document database? It’s a tempting idea: embed your related...

Embedding a One-to-Many relationship into JSONB to avoid joins, if over 2KB, hides the join, but does not prevent it. Here's how PostgreSQL TOAST works:
dev.to/franckpachot...

SQL or NoSQL?
@franckpachot.bsky.social explains how to pick the right model for your app in this #IntelliJIDEAConf session 👉 youtu.be/CuA84bJEjDo

MongoDB arrays: sort order and comparison Arrays in MongoDB are ordered lists of values, and querying them can be confusing because a field...

New blog post about comparing and sorting arrays in @MongoDB 👇🏼

It's a long story with a short conclusion to help remind it.

dev.to/franckpachot...

Why doesn't Oracle Multi-Value Index optimize .sort() like MongoDB does with its multi-key index? Until recently, Oracle Database offered very limited options for creating inverted indexes, primarily...

🤓 Some geekery (for my #POUG2025 talk) on index dumps and internals to understand how Oracle Database emulates MongoDB multi-key indexes, and where the limitations come from.

dev.to/franckpachot...

Does PostgreSQL support as much "schema flexibility" as MongoDB? Not for indexing! Another day, another myth. Someone on Medium claims PostgreSQL offers as much "schema flexibility" as...

PostgreSQL makes it easy to fact-check internet myths: read documentation + test with explain analyze 👉🏻 an example: dev.to/mongodb/does... 🤔

MongoDB indexing internals: showRecordId() and hint({$natural:1}) You can understand how MongoDB stores documents internally with simple queries that rely on the...

Is MongoDB storing documents like heap tables or index-organized tables?

👉🏻 kind of both, a B+Tree optimized for flexible size documents

dev.to/franckpachot...

I made it more clear in the article.Thanks for the feedback. It's OCC in WiredTiger, per document, and MongoDB built fail-on-conflict on top, for transactions. Commit should not fail because of data, and could be retried without rollback what was done. Some drivers do that to be resilient to failure

Per statement. I should use fail-on-conflict rather than OCC which is generally used for detection at commit

Transaction performance 👉🏻 retry with backoff A benchmark sponsored by EDB, a PostgreSQL company, in 2019 contributed to the myth that MongoDB...

Benchmarks from DbX's vendor showing it's faster than DbY are mainly marketing and serve little purpose in promoting DbX. However, they can help DbY's users spot poor design choices and anti-patterns that made DbY slower than carefully-tuned DbX
dev.to/franckpachot...

Why MongoDB skips indexes when flattening or renaming sub-document fields in $project before $match aggregation pipeline MongoDB is a general-purpose database that offers more than just a JSON datastore. It is recognized...

Query planner transformations on MongoDB documents with flexible schemas can surprise users familiar with SQL databases, which typically have fixed tabular schemas. An example of these subtle differences is explained in a new blog post. dev.to/franckpachot...