New blog post! Revisiting an old test shows that Extended RUM now overcomes the RUM/GIN index limitation, thanks to Microsoft for PostgreSQL’s work on the PostgreSQL extension. dev.to/franckpachot...

A new blog post before the weekend: testing pg_durable workflows (open-source extension by #Microsoft for #PostgreSQL) dev.to/franckpachot...

I'm curious if ADBA (Asynchronous Database Access) is still a thing. You submit an SQL query without waiting or blocking and then request the result later.

Welcome to #POUG2026! The fridge is full of beers. Help yourself and take as many as you like. But there’s one sacred rule: one beer must always remain. The beers & @franckpachot.bsky.social will be in Krakow on September 4-5th, but will you? www.poug.org

The real reason for "denormalization": using compound indexes across two entities in a one-to-many aggregate to cover filter predicates and pagination sort. Here are some PostgreSQL execution plans using B-tree, GIN, and RUM. dev.to/franckpachot...

At Cloud Toulouse, we presented tips on structuring your JSON documents. One of them is about the advantages of nesting. 🚩 1000 top-level fields is a red flag — don't do that! Neither in BSON nor in OSON - for different reasons. Here's why: dev.to/franckpachot...

To $exists or not to $exists — that is the question. A document’s flexible schema can tell missing fields apart from null values, but an index’s fixed key structure cannot distinguish between nonexistent and unknown fields: dev.to/franckpachot...

🍃 This is my last day at MongoDB! Next step: a blue database and a blue cloud 🐘🔹☁️, and see you next week at PG Day France 2026 www.linkedin.com/posts/franck...

🐘🌪️ Do you know your default autovacuum thresholds? ❄️🔥 How do they depend on the hot/cold data split? 👇🏼 I ran a small test to visualize the staleness accumulation over time: dev.to/franckpachot...

🐘🍸🍹 RUM indexes may be better than GIN when covering filters and order by pagination:

Not everything in SQL databases is ACID. For example, SQL sequences are non-transactional in order to scale. youtu.be/qB1E1UZ-dZs?...

A 30-year SQL issue: normalization requires joins across the relational schema, and for performance, those joins should run in the RDBMS, not in the application. But in-database joins do not raise NO_DATA_FOUND or TOO_MANY_ROWS... www.linkedin.com/posts/franck...

🐘 The risk had been there for years, with GitHub Insights showing only one contributor. Now, it says: “This repository was archived by the owner on Apr 27, 2026. It is now read-only.” www.linkedin.com/posts/franck...

⟕ Many think "SQL = normalization" and "NoSQL = data duplication", but it's a bit more complex than that. When you query a one-to-many relationship with filters on both sides, what you really want is a compound index that covers all selective filters... www.linkedin.com/posts/franck...

How do you anti-join (NOT EXISTS) in MongoDB? Ideally, both entities are embedded in the same aggregate (document). If not, use a $lookup with #$LIMIT:1 (like a semi-join) and keep only empty arrays: dev.to/franckpachot...

If you want to compare BSON with OSON, the Oracle Database datatype for JSON, you should compare the Mutable BSON Document, which is the structure MongoDB uses to access documents and read and update individual fields. dev.to/franckpachot...

I finally published my book about Postgres! Check it out here: lnkd.in/eakHPn6E Thanks @alexrios.me for the very good feedback, and @franckpachot.bsky.social for taking the time on reading the book and writing the foreword. More details here: www.linkedin.com/pulse/my-boo...

Many SQL DBAs argue: “I’ve never seen a production app change its database. Why make it database-agnostic?” www.linkedin.com/posts/franck...

Back then, I was in the SQL bubble and missed #MongoDB’s early days, so I revisited its early web pages on the Internet Archive to understand its origins better: dev.to/franckpachot...

Beyond Documents, Beyond Tables: SCDV™ – The Single-Cluster-Duality-View Revolution 💡 What if your architecture could add enough layers to normalize and denormalize at the same time? Discover this revolutionary design pattern. dev.to/franckpachot...

Read consistency in @MongoDB 🍃 Transaction-level consistency is ACID compliant 🍃 Statement-level consistency is achieved with the `snapshot` read concern 🍃 Document-level consistency is the default outside of an explicit transaction dev.to/franckpachot...

I'm a big fan of ANSI joins, but always keep in mind that if something doesn't work as expected, it can be a good idea to switch to the pre-9i syntax - just in case 🤓 dev.to/franckpachot...

Some fun with SQL consistency boundaries🤓 Two users received the same instruction: Add $42 to the salary or commission (choose one) for employee 99, who currently has a salary of $1000 and a commission of $100. Same tables, same rows. Only one should be able to update, right?

💡 When migrating from one platform to another, don’t just mimic what you had before. Revisit the requirements 🔦 www.linkedin.com/posts/franck...

I'm so used to starting software from Docker images that I didn't realize before that MongoDB database server is a single executable 😍 You start it (`mongod`), and it's a single process running threads to persist, process, and share your application data.

SQL databases rely on relational algebra and normal forms. Document databases use comparable math, just starting from non-1NF instead of 1NF: dev.to/franckpachot...

Encore une double-session au Ch'ti JUG ! Cette fois c'est MongoDB, avec @franckpachot.bsky.social, et hébergé chez Ippon ! On s'inscrit ici : www.meetup.com/chtijug/even...

You need to insert 1,000 documents into @MongoDB. You can either call insertMany() with the default session, which provides per-document ACID guarantees, or run it in a transaction so the entire batch is all-or-nothing. Which approach is faster? dev.to/franckpachot...

Entity ≠ Entity www.linkedin.com/posts/franck...

🍕 How do you prefer your pizza? Do you like your ingredients separated with references, or all together? www.youtube.com/watch?v=TKlS...

Le mois prochain à Lille

Because a cost-based optimizer needs accurate statistics, run ANALYZE on PostgreSQL partitioned tables to gather them, since auto-analyze only processes partitions. dev.to/aws-heroes/p...

I updated my SQL Assertion article with some maths and added the ALL ... SATISFY syntax that avoids double NOT EXISTS in Oracle Database SQL Assertions dev.to/franckpachot...

Relational algebra provides a logical model for reasoning about data independently of the domain, while document databases model data as applications do. A new blog to compare the relational selection (σ) on 1NF with its MongoDB equivalent: dev.to/franckpachot...

Trying a new format - short videos for beginners. That's the first one, all feedback welcome youtu.be/qJUe1WxsTDk?... A 3-step query tuning for MongoDB simple queries: Step ⓵: Data Modeling Step ⓶: Indexing for filters and order Step ⓷: Covering projection

In databases, the Cartesian product is used to generate all combinations. In MongoDB, you can get it either at read time using multiple queries or a $lookup aggregation stage, or at write time through embedding, if it makes sense within your domain. dev.to/franckpachot...

Top‑K BM25 range + text search on 100 million MongoDB documents in under two seconds (I reused the excellent ParadeDB example for this test) dev.to/franckpachot...

Reading E. F. Codd's "A Relational Model of Data for Large Shared Data Banks" without prior knowledge of pre-relational network databases may make the "connection trap" difficult to understand. I tried to explain it using modern database concepts: dev.to/franckpachot...

Post We saw enterprises shift from “one DB for everything in the company” to many purpose‑built cloud services. There’s a middle ground, but it isn’t just a DBA call—it depends on how applications are designed and built. Looking at it from a DDD perspective: www.linkedin.com/pulse/databa...

🌱 #MongoDB is not schemaless. In this “Hello World”, the first step is to declare a connection and schema to generate the #Prisma client. 📐There's a schema: it starts in the App, and the DB preserves it through memory and storage layers dev.to/franckpachot...

No rewind needed! MongoDB smoothly handles secondary nodes rejoining the replica set thanks to its recover-to-timestamp rollback in its Raft-inspired algorithm. Observe this in action with w:1 writes, where the rolled-back data isn’t lost but saved in a rollback directory: dev.to/franckpachot...

Wait, what? In 2023 I bought 64GB DDR5-5600 for 210€. Today, if I wanted to get 64GB DRAM (identical spec) I have to part with 879€ 🤯 <insert your favourite combination of swear words here>

Normal forms in a document database 🍕Tabular: One Pizza, One Area 📖1NF: More Menu Options 💶2NF: Pizza Pricing ☎️3NF: Manager's Contacts 🗺️BCNF: Per-Area Pricing 🚚4NF: Delivery Areas 📏5NF: Adding Pizza Sizes 🗓️ 6NF: Tracking Price History dev.to/franckpachot...

A good document model removes the need for explicit locks or serializable isolation to prevent write skew. Example in MongoDB with the well-known Doctor's On-Call Shift demo. dev.to/franckpachot...

Thats good throughput 🤓

If you declare your business logic in the database, the SQL-92 standard specifies SQL assertions, but no SQL databases have implemented them before 2026. Here is an example using @martin.kleppmann.com's example (Doctor's On-Call Shift) on Oracle 23.26.1 dev.to/franckpachot...

My first test of CloudNativePG: dev.to/franckpachot...