SELECT ... FOR UPDATE SKIP LOCKED is a vendor-specific SQL feature available in several relational databases (e.g., PostgreSQL, Oracle, MySQL). It helps parallel workers avoid waiting on locked rows. MongoDB’s concurrency model uses optimistic concurrency: reads don’t block writes, and writes don’t block reads. To coordinate parallel processing, you can reserve a document by writing a lock field so other workers skip it.

I'll use an example discussed in the Reddit question "ACID read then write – Python":

Client in python, multi process. Each process picks and reads one document, calls some public APIs, and add data to the document and save it. Then next document. What is written can depend on the read data. Question is: in python, how can I create and configure transactions in the code to make sure no other process can read or write its current document from the moment a process starts reading it until done writing its additional data? This means concurrent reads should not happen…

In this example, I'll process messages based on their originating IP address. Multiple threads will enrich them with location data fetched from the public API at https://ip-api.com/.

Here is an example of an initial document:

{
  _id: ObjectId('6956e772baea71e37a818e73'),
  originatingIp: '1.1.1.1',
  location: null
}

Here is the document while it is being processed:

{
  _id: ObjectId('6956e772baea71e37a818e73'),
  originatingIp: '1.1.1.1',
  location: null,
  lock: {
    by: 'franck',
    until: datetime.datetime(2026, 1, 1, 22, 33, 10, 833000)
  }
}

Here is the same document after processing:

{
  _id: ObjectId('6956e772baea71e37a818e73'),
  originatingIp: '1.1.1.1',
  location: {
    status: 'success',
    country: 'Hong Kong',
    countryCode: 'HK',
    region: 'HCW',
    regionName: 'Central and Western District',
    city: 'Hong Kong',
    zip: '',
    lat: 22.3193,
    lon: 114.1693,
    timezone: 'Asia/Hong_Kong',
    isp: 'Cloudflare, Inc',
    org: 'APNIC and Cloudflare DNS Resolver project',
    as: 'AS13335 Cloudflare, Inc.',
    query: '1.1.1.1'
  }
}

Storing in-process information avoids long transactions that hide the current status and make troubleshooting difficult when the public API is slow.

Design

This script is designed as a complete, runnable demonstration of how to implement SELECT ... FOR UPDATE SKIP LOCKED-style parallel job claiming in MongoDB. The script will generate everything it needs, process it, and show the end state.

• insert_test_docs() inserts test data with random IP addresses in a new collection "message", and creates a partial index to get the message to process ({location: null}).
• claim_document() updates a message to process, adding lock information so that another thread will not pick the same, and fetches the document. The criteria are that it must be processed ({location: null}) and not locked, or the lock must have expired (with a 1s grace to account for clock skew).
• fetch_location() is the call to the public API, here getting location information for an IP address.
• process_document() calls claim_document() to get a message to process, with a lock. It calls fetch_location() and updates the document with the location. It ensures the lock is still in place before the update, then unsets it. Each thread runs in a loop, claiming and processing documents until the timeout.
• main() calls those in sequence and displays the final documents.

This solution avoids explicit transactions, which is preferable because they would include a call to a public API with unpredictable response time. It also avoids using findOneAndUpdate, whose higher overhead comes from storing full pre- and post-images of documents for retryable operations. For large documents—possible in real workloads, even if not shown in this demo—this would lead to significant write amplification. Finally, setting an expiration timestamp allows automatic re-processing if a message fails.

Code

Below is the complete Python program, which you can test using different numbers of documents and threads:

import os
import random
import socket
import threading
import time
from datetime import datetime, timedelta
import requests
from pymongo import MongoClient


# Mongo connection and collection
client = MongoClient("mongodb://127.0.0.1:27017/?directConnection=true")
db = client.test
messages = db.message

#  Test settings (the test inserts documents, then runs the processing threads for some duration)
DOCUMENTS = 10 # number of documents created initially
THREADS = 5    # number of threads that loop to claim a document
SECONDS = 15   # thread stops looping on claim

# Worker identity (to identify the thread, and set an expiration on the lock)
WORKER_ID = f"{socket.gethostname()}-{os.getpid()}"
LOCK_DURATION = timedelta(seconds=60) # assumes processing completes within that duration, if not, it will be claimed by another, and this one will not update it

# Get the time
def utcnow(): return datetime.utcnow()
MAX_CLOCK_SKEW=timedelta(seconds=1) # used as a grace period when lock is expired

# --- Prepare test messages (with random generated IP) ---
def insert_test_docs():
    # Drop the collection completely (removes data + indexes)
    messages.drop()
    # Create the partial index for unprocessed docs  (they have location = null )
    messages.create_index(  [("lock.until", 1)],  partialFilterExpression={"location": None}  )
    # Generate random IPs for the test
    ips = [
        ".".join(str(random.randint(1, 255)) for _ in range(4))
        for _ in range(DOCUMENTS)
    ]
    # Explicitly set location=None to match the partial index filter
    docs = [
        { "originatingIp": ip, "location": None  } # A null location is the marker to process it
        for ip in ips
    ]
    messages.insert_many(docs)
    print(f"[STARTUP] Inserted {DOCUMENTS} test docs into 'message'")
    for doc in messages.find({}, {"_id": 0, "originatingIp": 1, "location": 1}):
        print(doc)


# --- Claim a message ---
def claim_document():
    now = utcnow()
    lock_expiry = now + LOCK_DURATION
    token = random.randint(1000, 9999)  # unique lock token for extra safety
    # Atomic lock claim: match unlocked or steal locks expired
    result = messages.update_one(
        {
          "$and": [
            # the location is not set
            { "location": None },
            # the document is not locked, or locked expired including grace period
            {  "$or": [  { "lock": { "$exists": False } },  { "lock.until": { "$lt": now - MAX_CLOCK_SKEW } }  ]  }
          ]
        },
        { "$set": {  "lock": {  "by": WORKER_ID,  "until": lock_expiry,  "token": token  }  }}
    )
    if result.modified_count == 0:
        return None
    # Fetch exactly the doc we locked — match by worker, expiry, AND token
    doc = messages.find_one({  "lock.by": WORKER_ID,  "lock.until": lock_expiry,  "lock.token": token  })
    if doc:
        print(f"[{WORKER_ID}] {threading.current_thread().name} claimed IP {doc['originatingIp']} with token={token}")
    else:
        print(f"[{WORKER_ID}] {threading.current_thread().name} claim succeeded but fetch failed — possible race?")
    return doc

# --- Call the public API ---
def fetch_location(ip):
    url = f"http://ip-api.com/json/{ip}"
    try:
        resp = requests.get(url, timeout=30)
        if resp.status_code == 200:
            return resp.json()
        print(f"[API] Error: HTTP {resp.status_code} for {ip}")
        return None
    except Exception as e:
        print(f"[API] Exception for {ip}: {e}")
        return None

# --- Process messages in a loop ---
def process_document():
    start_time = time.time()
    timeout = SECONDS  # seconds
    thread_name = threading.current_thread().name
    while True:
        # Try to claim a doc
        doc = claim_document()
        if doc:
            # We successfully claimed a doc — process it
            ip = doc["originatingIp"]
            location_data = fetch_location(ip)
            if not location_data:
                print(f"[{WORKER_ID}] {thread_name} failed to fetch location for {ip}")
                return
            # Final update only if lock is still valid
            now = utcnow()
            result = messages.update_one(
                {
                    "_id": doc["_id"],
                    "lock.by": WORKER_ID,
                    "lock.until": {"$gte": now},
                    "lock.token": doc["lock"]["token"]
                },
                {
                    "$set": {"location": location_data},
                    "$unset": {"lock": ""}
                }
            )
        # No doc claimed — check elapsed time before wait and retry
        elapsed = time.time() - start_time
        if elapsed >= timeout:
            print(f"[{WORKER_ID}] {thread_name} exiting after {elapsed:.2f}s")
            return
        time.sleep(5)  # avoid hammering DB and the public API

# --- Initialize and run multiple processing threads ---
def main():
    print(f"\nInserting documents")
    insert_test_docs()
    print(f"\nStarting threads")
    threads = []
    for i in range(THREADS):
        tname = f"T{i}"
        t = threading.Thread(target=process_document, name=tname)
        t.start()
        threads.append(t)
    for t in threads:
        t.join()
    print(f"\n[{WORKER_ID}] Check final documents:")
    for doc in messages.find({}, {"originatingIp": 1, "location.query": 1, "location.country": 1, "location.message": 1, "lock.by": 1, "lock.until": 1}):
        print(doc)

if __name__ == "__main__":
    main()

Technical Insights

MongoDB’s storage engine guarantees atomicity for each update_one through its WriteUnitOfWork and RecoveryUnit mechanisms. However, maintaining read consistency across multiple operations requires application-level coordination. In this implementation, that coordination is provided by an atomic claim with conditional criteria, ensuring that only one worker can lock an unprocessed or expired document at a time.

Several safeguards mitigate race conditions. The claim step narrows matches using the worker ID, lock expiry, and a random token. The final update then re-verifies all these fields before committing changes, preventing stale or stolen locks from being applied. Lock expiration enables automatic recovery from failures, and a small grace window accounts for clock skew in distributed systems.

Write conflicts during concurrent updates are automatically resolved at the storage layer via optimistic concurrency control. This ensures correctness without blocking other operations. The result is a robust, non-blocking parallel processing workflow that preserves document-level ACID guarantees while scaling effectively in shared or cloud environments.

In this design, each thread processes one message at a time, in index order. Enforcing strict global message ordering would be more complex. The primary goal here is the scalability of the parallel processing.

Final Recommendation

When migrating from PostgreSQL to MongoDB—like between any two databases—avoid a direct feature-by-feature mapping, because the systems are fundamentally different. SKIP LOCKED works around blocking FOR UPDATE reads in PostgreSQL, while reads and writes do not block in MongoDB. Instead of replicating another database behavior, clarify the business requirement and design the most appropriate solution. In this example, rather than relying on generic transaction control like SQL, we modeled object states—such as claim acquisition and expiration—and store that state directly in the documents.

TL;DR: If you’re used to traditional SQL databases and synchronous request–response flows—where you read your writes in the same transaction or session—use the "majority" read concern in MongoDB and you will have the highest isolation and durability you can expect from a database. It’s not the default, but it’s safe to change it for your connection. The default is optimized for event-driven, microservice architectures with asynchronous communication, where lower latency is preferred even if it means sometimes reading a state that may later be rolled back.

PostgreSQL users typically expect writes to become visible to other sessions only after they’re acknowledged, either via auto-commit DML or an explicit COMMIT. By contrast, in MongoDB, you must enable the "majority" read concern to achieve similar ACID guarantees, and this is not the default. It may seem surprising that MongoDB offers the strongest consistency option—full ACID semantics in a distributed database—yet doesn’t enable it by default, despite seemingly no significant performance impact. This caught my attention and made me want to understand the reasoning behind it. NoSQL and SQL now address similar use cases, but their origins are fundamentally different. Let’s explore that.

Non-blocking read and write concerns

In the SQL standard, isolation levels were first defined by the anomalies (phenomena) that can occur when concurrent sessions read and write the same data. But these definitions were tied to a specific lock-based implementation rather than an abstract model: they assumed that reads and writes use locks and that active transactions share and modify a single current database state.

In reality, many databases chose different designs for scalability:

• Non-blocking reads with MVCC (e.g., PostgreSQL or MongoDB) show anomalies not covered by the standard—"write skew," for instance—and support isolation levels like Snapshot Isolation (SI), which differs from the SQL definitions, even though PostgreSQL uses the name Repeatable Read to match the SQL standard.
• Non-blocking writes (e.g., in MongoDB) detect write conflicts immediately and raise a retryable exception instead of waiting for lock acquisition, also known as optimistic concurrency control.

To understand isolation and durability in MongoDB, we must first consider read and write concerns independently, especially in a replicated, distributed setup where reads and writes can hit different servers. Then we can examine how they interact when we read after writing.

Isolation and durability

First, let’s distinguish isolation and durability — the I and D in ACID:

• Isolation defines how reads and writes from different sessions are visible to one another. To preserve atomicity, it must hide intermediate states of uncommitted writes until the transaction completes and should also prevent stale reads that miss previously committed writes.
• Durability ensures that once data is written, it remains persistent and is not lost after a failure. Similarly, to prevent dirty reads that might later be rolled back during failure recovery, data that has already been read should also be guaranteed to remain persistent.

Initially, these definitions assumed a single-node database. In modern systems, durability must also handle network and data center failures, so data is persisted across multiple nodes rather than just on a local disk.

A commit, whether in an explicit transaction or implicit in a write operation, typically proceeds as follows:

1. Commit is initiated.
2. The write-ahead log is flushed to local disk (local durability).
3. The write-ahead log is flushed to the remote disk (global durability).
4. Changes become visible (end of isolation) to other sessions.
5. The commit is acknowledged in the session.

Durability and isolation each involve multiple operations, and their order can vary. The sequence above matches PostgreSQL with synchronous_commit = on, or MongoDB with w:majority and a majority read concern in other sessions.

Other configurations are possible. For example, Oracle Database uses a different order for durability and isolation, making changes visible before the redo log is flushed (except when paranoid_concurrency_mode is set). With PostgreSQL synchronous_commit = local or MongoDB w:1, acknowledgment occurs before global durability. With MongoDB’s local read concern, data becomes visible before it is durable.

Why isn’t the above sequence—which seems to offer the strongest isolation and durability—the default in MongoDB?

Read after a write with asynchronous calls

There is another anomaly not described by the SQL standard, which assumes that read and write locks on a single database state are mutually exclusive. With MVCC, a transaction instead works with two states:

• Read time is the start of the transaction (or the start of the statement in Read Committed transactions). All reads use a snapshot from this time.
• Write time is the end of the transaction, since all writes must appear to occur atomically at commit.

Because the read time is earlier than the write time, another anomaly can occur:

1. Microservice A writes an event, assumes it will be persisted and visible, and notifies microservice B.
2. Microservice B receives the notification and reads the event, assuming it is visible.
3. Microservice A receives the write acknowledgment a few milliseconds later, especially if global durability must be confirmed.

In a non-MVCC database with blocking reads, this preserves causality because, in step 2, microservice B requires a share lock and waits on an exclusive lock acquired by A and released at step 3, so B sees the write only after it acquires the share lock, after step 3. Non-MVCC is rare (e.g., DB2 or SQL Server without RCSI isolation level), but SQL isolation levels were defined based on it, and didn't mention causality.

Keep in mind that in this example, the application doesn’t wait for the write acknowledgment before telling the other service to read, yet it still expects the write to be complete when the read occurs. Read-after-write causality was guaranteed with read locks in the non-MVCC database.

However, in an MVCC database, as in most modern systems, microservice B may read a state from before a write is visible, causing a read-after-write anomaly. If the write is acknowledged only locally—for example, PostgreSQL with synchronous_commit = local or MongoDB with w:1—it will likely be visible by the time B receives the notification, because the write usually completes faster than the notification is delivered.

By contrast, PostgreSQL with synchronous_commit = on, or MongoDB with majority read concern, may not see the write yet if it has not been replicated to a majority. Thus, when using w:1, users should select the local read concern to avoid read-after-write anomalies. w:1 is not the default. Still, it can be chosen to reduce latency, at the risk of losing events on failure—something event-driven architectures can often tolerate.

With PostgreSQL synchronous_commit = on or MongoDB w:majority (the default), writes incur extra network latency because they must wait for remote acknowledgment. In this case, the scenario can still show a read-after-write anomaly if the majority has not yet acknowledged microservice A's write when microservice B reads. Using MongoDB local read concern avoids this anomaly, but risks reading data that might later be rolled back on failure.

"local" is the default, but use "majority"

The default read concern is well-suited to event-driven architectures. As event-driven systems were a primary use case for NoSQL databases like MongoDB, retaining this default makes sense, at least for backward compatibility. Users also often expect reads to return the latest changes, even if those changes have not yet been acknowledged in the thread that performed the write operation.

Today, MongoDB is also used with traditional architectures, where it’s reasonable to prefer durability over fast visibility and use the "majority" read concern. This adds no performance penalty, because you already paid the synchronization latency when waiting for the write acknowledgment. "Majority" read concern sets the read time to the last commit time, while keeping reads local. It can wait in rare cases, such as during instance startup or rollback, until it can obtain a committed timestamp snapshot, or when secondaries are unavailable or lagging. But generally, there's no performance impact.

Unlike SQL databases—which must guarantee consistency for any DML executed by any user, including non-programmers at the command line—MongoDB shifts more responsibility to developers. Instead of relying on a one-size-fits-all default, developers must configure their session or connection by choosing:

• the write concern (for example, w:majority for durability over network or data center failures),
• the read concern (such as majority, or snapshot for stronger consistency in multi-shard transactions), and
• the read preference (to scale reads across replicas when some staleness is acceptable). This configuration lets MongoDB adapt to different consistency and performance expectations.

This post has results for RocksDB performance using db_bench on 8-core and 48-core servers. I previously shared results for RocksDB performance using gcc and clang and then for RocksDB on a small Arm server. 

tl;dr

• RocksDB is boring, there are few performance regressions. 
• There was a regression in write-heavy workloads with RocksDB 10.6.2. See bug 13996 for details. That has been fixed.
• I will repeat tests in a few weeks

Software

This has results for an IO-bound sysbench benchmark on a 48-core server for Postgres versions 12 through 18. Results from a CPU-bound sysbench benchmark on the 48-core server are here.

tl;dr - for Postgres 18.1 relative to 12.22

• QPS for IO-bound point-query tests is similar while there is a large improvement for the one CPU-bound test (hot-points)
• QPS for range queries without aggregation is similar
• QPS for range queries with aggregation is between 1.05X and 1.25X larger in 18.1
• QPS for writes show there might be a few large regressions in 18.1

This paper (HOTNETS'25) re-teaches a familiar systems lesson: caching is not just about reducing latency, it is also about saving CPU! The paper makes this point concrete by focusing on the second-order effect that often dominates in practice: the monetary cost of computation. The paper shows that caching --even after accounting for the cost of DRAM you use for caching-- still yields 3–4x better cost efficiency thanks to the reduction in CPU usage. In today's cloud pricing model, that CPU cost dominates. DRAM is cheap. Well, was cheap... I guess the joke is on them now, since right after this paper got presented, the DRAM prices jumped by 3-4x! Damn Machine Learning ruining everything since 2018!

Anyways, let's ignore that point conveniently to get back to the paper. Ok, so caches do help, but when do they help the most? Many database-centric or storage-side cache designs miss this point. Even when data is cached at the storage/database cache, an application read still needs to travel there, pay for RPCs, query planning, serialization, and coordination checks. 

The paper advocates for moving the caches as close to the application as possible to cut costs for CPU. The key argument is that application-level linked caches deliver far better cost savings than storage-layer caches. By caching fully materialized application objects and bypassing the storage/database read path entirely, linked caches eliminate query amplification and coordination overhead. Across production workloads, this yields 3–4x better cost efficiency than storage-layer caching, easily offsetting the additional DRAM cost. Remote caches help, but still burn CPU on RPCs and serialization. Storage-layer caches save disk I/O but leave most of the query and coordination path intact, delivering the weakest cost savings. The results are consistent across different access skews and read intensities, reinforcing that cache placement dominates cache size.

So that is the gist of the paper. The paper makes two adjacent points. Special cases of this observation, if you will. And let's cover them for completeness.

The first point is rich-object workloads, which is where the most striking evaluation results come from. For services where a single logical read expands into many database queries (e.g., metadata services and control planes), caching fully materialized objects at the application level avoids query amplification entirely. And this yields up to an order-of-magnitude cost reduction versus uncached reads and roughly 2x improvement over caching denormalized key-value representations.

The second result, a negative result, is also important. Adding even lightweight freshness or version checks largely erases these gains, because the check itself traverses most of the database stack. The experiments make clear that strong consistency remains fundamentally at odds with the cost benefits of application-level caching. The paper leaves this as an open challenge, saying that we still lack a clean, low-cost way to combine strong consistency with the economic benefits of application-level caching. I think it is possible to employ leases to trade off an increase in update latency with cost efficiency, and alleviate this problem. Or we could just say: Cache coherence is hard, let's go shopping for CXL!

Discussion

Overall, the paper quantifies something many practitioners intuit but rarely measure. If you care about cost (also monetary cost), move caching up the stack, cache rich objects, and trade memory against CPU burn.  

As usual Aleksey and I did a live-reading of the paper. And as usual we had a lot to argue and gripe about. Above is a recording of our discussion, and this links to my annotated paper.

Of course, Aleksey zeroed in on the metastability implications right from the abstract. And yes the metastability implications remained unaddressed in the paper. If you cut costs and operate at lower CPU provisioning (thanks to this cache assist), you are making yourself prone to failure by operating at maximum utilization, without any slack. That means, the moment the cache fails or becomes inaccessible, your application will also get overwhelmed by 2-3x more traffic than it can handle and suffer unavailability or metastability.

I had some reservations about application-level caches. They are undeniably effective, but they lack the reusability and black-box nature of storage-layer caching. Storage-side caching is largely free, transparent, and naturally shared across nodes and applications. Application-level caching, by contrast, requires careful design and nontrivial development effort. It also sacrifices reuse and sharing, since each application must manage its own cache semantics and lifecycle. I wish the paper could discuss these costs and tradeoffs.

Writing, after the introduction section, was repetitive and sub par. Sections 2 and 3 largely repeated the Introduction and wasted space. Then we only had 2 paragraphs of the Theoretical Analysis section, which we actually looked forward to reading. That section is effectively cropped out of the paper, when it makes the core of the arguments for the paper. 

The paper's subtitle (see headers on Page 3, 5, 7) is a copy-paste error from the authors' HotNets 2024 paper. There did not seem to be any camera-ready time checks on the paper. To motivate strong consistency, the paper drops several citations in Section 2.3, calling them as recent work. Only 2 out of 6 of these are after 2014. The figures were sloppy as well. Did you notice Figure 6 above? The y-axis are not covering the same ranges, which makes it very hard to compare about the subfigures. The y-axis in Figure 5 uses relative costs, which is also of not much use. It may be that in 2025 most people use LLMs to read papers, but one should still write papers as if humans will read them, past the introduction section, and line by line to understand and check the work.

Finally, here is an interesting question to ponder on. Does this paper conflict with storage disaggregation trend?

At first glance, the paper appears to push against the storage disaggregation trend by arguing for tighter coupling between computation and cached data to meet real-time freshness constraints. In reality, it does not reject disaggregation but warns that disaggregated designs require additional caching above the storage layer.  Just storage side caching would not be able to suffice from a latency as well as cost perspective! The paper also points to a hidden cost: freshness guarantees degrade when cache coherence is treated as a best-effort side effect of an eventually consistent pipeline. The paper's message is that disaggregation needs explicit freshness semantics and coordination mechanisms. So maybe a corollary here is that, we should expect disaggregated systems to inevitably grow "stateful edges" over time in order to recover performance and control.

I aspire to get bored in the new year

I've realized that chess has been eating my downtime. Because it lives on my phone (Lichess), it is frictionless to start a bullet game, and get a quick dopamine hit.

The problem is that I no longer get bored. That is bad. I need to get bored so I can start to imagine, daydream, think, self-reflect, plan, or even get mentally prepared for things (like the Stoics talked about). I badly need that empty space back.

So bye chess. Nothing personal. I will play only when teaching/playing with my daughters. I may occasionally cheat and play a bullet game on my wife's phone. But no more chess apps on my phone.

While I was at it, I installed the  Website Blocker extension for Chrome. I noticed my hands typing reddit or twitter at the first hint of boredom. The blocker is easy to disable, but that is fine. I only need that slight friction to catch myself before opening the site on autopilot.

I am disappointed by online discourse

In 2008, Reddit had a popular thread on Attacking Generals problem with about 300 confident and largely misguided comments. I shared it with two senior distributed researchers, with the subject line "Proof that distributed reasoning is really tricky".

One asked: "Who are these people and where do they come from?" The other mentioned this book: "The Dumbest Generation: How the Digital Age Stupefies Young Americans and Jeopardizes Our Future"

Today, Hacker News is at the same threshold. People with little understanding discuss opinions with other uninformed people back and forth.

I am aware this comes across cranky. I am not angry, and I am not trying to gatekeep or anything. I am glad to see interest in these topics. I just want to see a higher signal to noise ratio in these discussions. Right now, maybe less than 10% of the comments say something useful or substantial. 

Marc Brooker has a quadrant model to explain why this discourse happens. As he also admits, we can do better, and the forums can design incentives that reward insight instead of noise.

I like Trader Joe’s

A Trader Joe's opened nearby. Now we eat simit for breakfast. Trader Joe's simit is spot-on authentic. As a Turk, this is an amazing treat. Simit is deeply nostalgic. Every kid loves it. Adults too. It's simple, inexpensive, and perfect. 

They also had frozen baklava. It was very fresh, authentic, delicious, and inexpensive as well. Past tense because, it turns out this was seasonal. 

Acquired did an episode on Trader Joe's. Worth a listen. I am always impressed by how helpful the Trader's Joe associates are. Shopping should feel like this. Hey Trader Joe's, after this shout-out, you should sponsor me with unlimited simit. Or at least make baklava year-round. Please.

I also like Qamaria

A Yemeni coffee chain opened nearby. This appears to be the year of pistachio. First Dubai chocolate, now this. We tried pistachio lattes, hot and cold. The cold one was better, and looked better too. Both were very sweet. I had to return the hot one, and ask for a replacement with less Pistaschio paste, so I don't die of sweet Pistaschio overdose on the spot. The caramel tres leches was excellent. Really light, soft, and moist. 

Yemeni coffee tastes better. I am done with Starbucks. The Starbucks branch near us is consistently awful. Some locations are better, but overall, I am finished with it. I hear good things about Luckin Coffee. That is next on my list.

I have been watching things

Zootopia 1 was better.

What happened to scriptwriting? The industry no longer seems to try. No creativity. Not even basic logic. In most movies, there are plot holes large enough to drive a garbage truck through.

Wake Up Dead Man: A Knives Out Mystery was good, but not as engaging as the first.

I did like The Great Flood, a Korean movie on Netflix. At least it is an engaging machine learning movie.

No good Christmas movie this year? Time to watch Die Hard again.

Baby vs Man: Rowan Atkinson is brilliant. Unfortunately, I have a strong stress reaction to watching stupidity unfold slowly and inevitably. Atkinson is exceptionally good at this, which is exactly why I cannot bring myself to watch it.

For users used to SQL databases, MongoDB’s consistency model can be confusing. Typical SQL systems layer read replicas on top of data recovery, not into the query layer. MongoDB instead includes replication into its read and write paths, extending ACID guarantees across a horizontally scalable cluster.

The SQL world has similar misconceptions, especially the belief that read replicas can safely scale horizontally—until data is lost. This article follows the six myths from Alexander Kukushkin’s PGConf.DE talk, Myths and Truths about Synchronous Replication in PostgreSQL (slides). For each myth, I relate the idea to MongoDB’s write concern, read concern, and majority commit point.

MongoDB Write Concern Behaviour

In MongoDB, writeConcern: { w: 1 } acknowledges a write as soon as the primary accepts it, before replication. With writeConcern: { w: "majority" } — the default for most replica set configurations — the write is acknowledged only after it has been replicated to a majority of voting nodes.

PostgreSQL’s synchronous commit is similar to MongoDB’s majority write concern, except that in PostgreSQL, replicas push changes directly from the primary and the primary waits for synchronous network confirmation from each selected standby. MongoDB replicas pull changes from any peer, and send a commit progress report back to the primary.

Myth №1 – “PostgreSQL transaction is committed after confirmation from synchronous standby”

🐘 In PostgreSQL, commits are always local first: XLogFlush(XactLastRecEnd) runs before waiting for synchronous replication via SyncRepWaitForLSN(XactLastRecEnd, true).

The transaction is not visible until the synchronous standby has received, flushed, or applied the WAL because locks remain held. If that wait is interrupted — by cancellation, connection loss, or restart — locks may be released early. The transaction can then be visible but not yet durable and may be lost on failover.

These are “dirty reads” in terms of durability, not isolation — they read only committed local data, but that data can still be rolled back in case of failure.

🍃 MongoDB behaves similarly with { w: "majority" } (default in most configurations): it waits for acknowledgment after writing locally. But MongoDB does not hold locks. Visibility is controlled entirely by the read concern. With the default readConcern: "local", you see the change before it is committed to the quorum, and it is therefore subject to rollback on failover. 🌱 With readConcern: "majority", you only read from a point in time where all changes are quorum‑durable.

Myth №2 – “PostgreSQL synchronous replication guarantees Zero RPO / no data loss”

🐘 In PostgreSQL, if synchronous commit wait is cancelled — by query cancel, TCP connection reset, or server restart — the transaction becomes visible immediately. The application receives a warning:

The transaction has already committed locally, but might not have been replicated to the standby.

If the primary fails before standby confirmation, the promoted standby may miss the transaction, resulting in data loss if the application ignored the warning.

If the TCP connection between client and server is interrupted at the wrong moment, the application may not know whether the transaction committed after disconnect — and it will not have received a warning. To verify, you must query data back, or get the transaction ID (txid_current()) before committing, and check it after reconnect using txid_status().

🍃 MongoDB is similar: if the client loses its connection after sending the write but before receiving the acknowledgment for w: "majority", the commit status is unclear. 🌱 However, the driver can handle this with retryable writes — specifying retryWrites: true — for idempotent operations, and for writes with deterministic keys such as a driver‑generated ObjectId. Retrying with the same _id will either match the existing document or insert it once.

MongoDB Read Concern Behaviour

MongoDB uses MVCC and reads from a recent state of the database that excludes uncommitted changes. By default, readConcern: "local" returns the most recent node‑local commit, durable to that node’s own disk (fdatasync). A write with { w: "majority"} may be visible before it is acknowledged by the quorum, and can be rolled back in a failover.

To extend ACID beyond a single node, readConcern: "majority" guarantees that what you read has been acknowledged by a majority of voting members and is durable cluster‑wide. It does so by reading from the commit snapshot corresponding to the majority commit point.

Myth №3 – “Reading from PostgreSQL synchronous standby nodes is like reading from the primary”

🐘 In PostgreSQL, a standby can show a transaction before the primary finishes waiting for other standbys. The ACID properties in PostgreSQL are not automatically extended to read replicas.

🍃 In MongoDB you can read from any replica with readConcern: "majority" and guarantee that it reads the same durable state as the primary. Drivers can automatically distribute reads with readPreference: "secondary" or "secondaryPreferred". 🌱 Adding readConcern: "majority" ensures that all those reads see the majority‑committed snapshot. Replicas can lag a little, but that works in an MVCC database where reads do not acquire locks.

MongoDB Majority Commit Point

In MongoDB replica sets, the majority commit point is a logical timestamp indicating that all operations up to that point have been replicated and made durable on a majority of members. The primary advances this point when it observes a higher point on a majority of nodes. With majority write concern, MongoDB acknowledges a write only when it is at or before this point, guaranteeing survival across elections and failovers.

Myth №4 – “We just need to promote PostgreSQL synchronous replica to avoid data loss”

🐘 In PostgreSQL, setting a node to synchronous doesn’t synchronise it instantly. The standby must first reach the "streaming" state, with zero lag, before it can serve as a synchronous standby. Until then, there is a catch‑up window during which promotion can result in missing transactions.

🍃 MongoDB uses the majority commit point in elections. 🌱 If writes use w: "majority", the elected node always has those writes acknowledged, avoiding the catch‑up hazard.

Myth №5 – “With PostgreSQL synchronous replication we don’t need pg_rewind”

🐘 Even in synchronous mode, a PostgreSQL standby can miss certain WAL changes not generated by client transactions — for example, VACUUM — leading to divergence after failover. This cannot be fixed by simple roll‑forward. pg_rewind must identify and copy the differing blocks from the new primary to reinstate the old primary as a standby.

🍃 In MongoDB, rollback when a node rejoins can happen if the oplog has diverged, but normally only with w: 1. 🌱 With writeConcern: "majority" — default in most deployments — writes wait for the commit point to advance and are protected from rollback in the most common cases.

MongoDB Replication Performance Implications

With w: 1, MongoDB only performs a local disk write, so latency is minimal. With w: "majority", it waits for replication to enough nodes and their disk commits (j: true is the default), so each write can incur multiple intra‑cluster and cross‑region RTTs.

For reads, readConcern: "local" avoids extra RTTs by reading the latest local commit. readConcern: "majority" also does not require extra network hops. It reads from the local snapshot corresponding to the majority commit point.

Linearizable reads in MongoDB have the highest cost: they require a no-op write and wait for majority replication before returning, adding a full RTT to the read.

Myth №6 – “PostgreSQL Synchronous replication adds no noticeable latency”

🐘 In PostgreSQL, synchronous replication increases latency proportional to RTT between nodes. The push‑based approach also increases primary CPU and network overhead to transmit WAL to all standbys.

🍃 MongoDB behaves similarly with w: "majority", but the pull‑based replication puts less pressure on the primary. 🌱 Secondaries can fetch changes from other secondaries, reducing primary load.

Conclusion

Synchronous replication in PostgreSQL and majority write concern in MongoDB are aimed at the same goal: protecting committed data against failover. Both commit locally first, then wait for quorum confirmation, but PostgreSQL’s locking model delays visibility, whereas MongoDB lets you choose visibility via read concern.

Warnings in PostgreSQL about cancelled sync waits are critical to avoid silent data loss. In MongoDB, retryable writes (retryWrites: true) and idempotent operations solve similar problems of uncertainty after a disconnect.

Read replicas in PostgreSQL do not automatically carry the same durability as the primary. In MongoDB, majority read concern enforces that. PostgreSQL requires manual lag checks before safe promotion. MongoDB elections ensure the majority commit point is present. PostgreSQL can still diverge on non‑transactional WAL and require pg_rewind after a failover, while MongoDB avoids rollback for majority writes.

Performance costs rise in both systems when ensuring strongest guarantees, with PostgreSQL’s push model loading the primary and MongoDB’s pull model distributing replication work.

Knowing exactly what these guarantees mean—and when they don’t apply—is essential to using them safely. In short, MongoDB by default offers similar protection, along with additional operational benefits from its built-in orchestration and cluster-aware drivers. 🌱 By using a {w: "majority"} write concern, MongoDB can extend ACID properties to a horizontally scalable cluster.

In SQL databases, foreign keys act as immediate constraints that verify the correctness of relationships between tables before accepting a write. This was designed for scenarios in which end users can submit random queries directly to the database. As a result, the database is responsible for protecting the data model using normalization, integrity constraints, stored procedures, and triggers, rather than relying on validation performed before the application interacts with the database. When relational integrity is violated, an error occurs, preventing the user from making the changes, and the application rolls back and raises an exception.

MongoDB’s NoSQL approach differs from relational databases as it was designed for application developers. It relies on application code to enforce these rules. Use cases are clearly defined, validation occurs at the application level, and business logic takes precedence over foreign key verification. Eliminating the need for additional serializable reads associated with foreign keys can significantly boost write performance and scalability.

Referential integrity can be verified asynchronously. Instead of raising an exception—an unexpected event the application might not be ready for—MongoDB allows the write to proceed and offers tools like the aggregation framework and change streams to detect and log errors. This approach enables error analysis, data correction, and application fixes without affecting the application's availability and includes the business logic.

Let's go through a traditional example of departments and employees, where all employees must belong to a department.

Two collections with reference

Strong relationships, including one-to-many, don't necessarily require multiple collections with references, especially if they share the exact lifecycle. Depending on the domain's context, we can embed a list of employees within each department document to ensure referential integrity and prevent orphans. Alternatively, we might embed department information into each employee's document, particularly when department updates are infrequent—such as a simple multi-document change to a department description—or when department changes are usually part of larger enterprise reorganizations.

When both entities are not always accessed together, have unbounded cardinality, or are updated independently, you can choose to reference another document instead of embedding all details. For example, store a "deptno" for each employee and maintain a separate collection of departments, each with a unique "deptno". I insert such data:

// Reset
db.departments.drop();
db.employees.drop();

// Departments
db.departments.createIndex(  
  { deptno: 1 },   // deptno will be used as the referenced key 
  { unique: true } // it must be unique for many-to-one relationships
;
db.departments.insertMany([
  { deptno: 10, dname: "ACCOUNTING", loc: "NEW YORK" },
  { deptno: 20, dname: "RESEARCH", loc: "DALLAS" },
  { deptno: 30, dname: "SALES", loc: "CHICAGO" },
  { deptno: 40, dname: "OPERATIONS", loc: "BOSTON" }
]);

// Employees in departments 10, 20, and 30
db.departments.createIndex(  
  { deptno: 1 },   // reference to departments 
;
db.employees.insertMany([
  { empno: 7839, ename: "KING",    job: "PRESIDENT", deptno: 10 },
  { empno: 7698, ename: "BLAKE",   job: "MANAGER",   deptno: 30 },
  { empno: 7782, ename: "CLARK",   job: "MANAGER",   deptno: 10 },
  { empno: 7566, ename: "JONES",   job: "MANAGER",   deptno: 20 },
  { empno: 7788, ename: "SCOTT",   job: "ANALYST",   deptno: 20 },
  { empno: 7902, ename: "FORD",    job: "ANALYST",   deptno: 20 },
  { empno: 7844, ename: "TURNER",  job: "SALESMAN",  deptno: 30 },
  { empno: 7900, ename: "JAMES",   job: "CLERK",     deptno: 30 },
  { empno: 7654, ename: "MARTIN",  job: "SALESMAN",  deptno: 30 },
  { empno: 7499, ename: "ALLEN",   job: "SALESMAN",  deptno: 30 },
  { empno: 7521, ename: "WARD",    job: "SALESMAN",  deptno: 30 },
  { empno: 7934, ename: "MILLER",  job: "CLERK",     deptno: 10 },
  { empno: 7369, ename: "SMITH",   job: "CLERK",     deptno: 20 },
  { empno: 7876, ename: "ADAMS",   job: "CLERK",     deptno: 20 }
]);

I didn't declare the schema upfront, as it will come as-is from the application. For performance reasons, I declare indexes on both sides to enable fast navigation between employees and departments, and between departments and employees.

Query examples

This schema supports all cardinalities, including millions of employees per department—something you wouldn't embed—and is normalized to ensure that updates affect only a single document. It also allows for bidirectional querying.

Here's an example of a query that joins all department information to employees as if it were embedded, but evaluated at read time:

db.employees.aggregate([  
  {  
    $lookup: {                 // add all department info in an array  
      from: "departments",  
      localField: "deptno",  
      foreignField: "deptno",  // fast access by index on departments 
      as: "department"  
    }  
  },  
  {  
    $set: {  // get first (and only) match (guaranteed by unique index)  
      department: { $arrayElemAt: ["$department", 0] }  
    }  
  }  
]);

Here's an example of a query that joins all employee information to departments as if it were duplicated and embedded, but evaluated at read time:

db.departments.aggregate([  
  {  
    $lookup: {                 // add all employee info in an array
      from: "employees",          
      localField: "deptno",      
      foreignField: "deptno",  // fast access by index on employees
      as: "employees"             
    }  
  }  
]);

From a performance standpoint, performing a lookup is more costly than reading from a single embedded collection. However, this overhead isn't significant when browsing through tens or hundreds of documents. When choosing this model, because a department might have a million employees, you don't retrieve all the data at once. Instead, a $match will filter documents before the $lookup in the first query, or a filter will be applied within the $lookup pipeline in the second query.

I have covered those variations in a previous post:

What about referential integrity for these queries? If an employee is inserted with a deptno that does not exist in departments, the lookup finds no match. The first query omits the department information, and the second query doesn't show the new employee because it lists only the known department. This is expected behaviour for an application that didn't insert the referenced department.

Relational database administrators often overstate how serious this is, and even call it data corruption. Because SQL defaults to inner joins, that employee would be missing from the result of the first query, but with outer joins like $lookup in MongoDB, this does not happen. It’s more like a NULL in SQL: the information is not yet known, so it isn’t shown. You can add the department later, and the queries will reflect the information as it becomes available.

You may still want to detect when referenced items are not inserted after some time, for example, due to an application bug.

Foreign key definition as a $lookup stage

I define referential integrity using two stages: a lookup stage and a match stage that verify whether the referenced document exists:

const lookupStage = {
  $lookup: {
    from: "departments",
    localField: "deptno",
    foreignField: "deptno",
    as: "dept"
  }
};

const matchStage = { $match: { dept: { $size: 0 } } }
;

The definition is simple and similar to an SQL foreign key. In practice, it can be more complex and precise. Document databases are well-suited to well-understood applications where business logic extends beyond what can be defined by a foreign key. For example, some employees may temporarily have no department—such as new hires—or may belong to two departments during a transition. MongoDB’s flexible schema supports these cases, and you define referential integrity rules accordingly. You aren’t constrained to an application-unaware model as with SQL schemas. I'll keep it simple for this example.

One-time validation with an aggregation pipeline

I insert a new employee, Eliot, into dept 42, which doesn’t exist yet:

db.employees.insertOne({
  empno: 9002,
  ename: "Eliot",
  job: "CTO",
  deptno: 42       // Missing department
});

This doesn’t raise any errors. In all queries, the employee is visible only by department number, with no other information about that department.

If you decide that such a situation should not stay and must be detected, you can use an aggregation pipeline to list the violations, with the lookup and match stage defined earlier:

db.employees.aggregate([ lookupStage, matchStage ])
;

This shows the employees referencing a department that doesn't exist:

[
  {
    _id: ObjectId('694d8b6cd0e5c67212d4b14f'),
    empno: 9002,
    ename: 'Eliot',
    job: 'CTO',
    deptno: 42,
    dept: []
  }
]

We’ve caught the violation asynchronously and can decide what to do. Maybe the "deptno" was wrong, maybe we failed to insert the department, or someone deleted it, or we missed a business scenario where employees can be assigned to a department number without more information.

In SQL databases, the rules are basic and not driven by use cases. They check only for anomalies arising from normalization, and any violation is treated as an error without further consideration. However, in MongoDB, where you build a database for a known application, the integrity is part of the business logic.

Whether you should run this validation depends on your database's size and the risk of integrity issues. After major data refactoring, run it as an extra check. To avoid production impact, run it on a read replica—an advantage of asynchronous verification. You don't need a high isolation level, as, at worst, concurrent transactions may trigger a false warning, which can be checked later. If you restore backups for disaster recovery testing, it’s wise to run the validation on that copy to verify both the restore process and data integrity of the primary database.

Real-time watcher with change streams

You may also decide to perform validations in near real time, checking the changes shortly after they occur.

I start a change stream for employees, looking for inserts/updates,

and apply the same $lookup + $match to just the changed doc:

const cs = db.employees.watch([
  { $match: { operationType: { $in: ["insert", "update", "replace"] } } }
]);
print("👀 Watching employees for referential integrity violations...");

while (cs.hasNext()) {  
  const change = cs.next(); // Get the next change event  
  if (["insert", "update", "replace"].includes(change.operationType)) {  
    const result = db.employees.aggregate([  
      { $match: { _id: change.documentKey._id } }, // check the new document
      lookupStage, // lookup dept info by deptno  
      matchStage   // keep only docs with NO matching dept  
    ]).toArray();  

    if (result.length > 0) {  
      print("\n⚠ Real-time Referential Integrity Violation Detected:");  
      printjson(result[0]);  
    }  
  }  
}  

In another session, I insert another employee, Dwight, in department 42, which is still missing:

db.employees.insertOne({
  empno: 9001,
  ename: "Dwight",
  job: "CEO",
  deptno: 42 // 🚨 Still missing
});

The loop on the change stream get notified of the insert, applies the aggregation pipeline verification and returns the following output:

⚠ Real-time Referential Integrity Violation Detected:
{
  _id: ObjectId('694da3aa8cd2fa3fe4d4b0c2'),
  empno: 9001,
  ename: 'Dwight',
  job: 'CEO',
  deptno: 42,
  dept: []
}

Rather than an error that blocks the application, the application handles this as any application alert. It can either create a department automatically or have a user analyze the situation.

Fixing the Violations

I add the missing department:

db.departments.insertOne({
  deptno: 42,
  dname: "DEVELOPER EXPERIENCE",
  loc: "REMOTE"
});

I re-run the batch check:

db.employees.aggregate([ lookupStage, matchStage ]);

Conclusion

In SQL databases, foreign key constraints require reading the parent record before inserting or updating a child and applying locks if necessary. When deleting or updating referenced keys, the database checks for existing children and waits if a child is being inserted. If users manually perform these operations on production or if the database administrator doubts the development team, using a SQL database with declared foreign keys is advisable.

However, these operations generally originate from an application that has already conducted the necessary checks: it reads the referenced table to get the key, verifies no children exist before deleting a parent, and often prefers logical over physical deletions. Additional validations can identify bugs, but they can run asynchronously and integrate with business logic and alert systems.

In MongoDB, data integrity is implemented by developers across various development stages in a DevOps manner, rather than solely during write operations. This strategy relies on not altering the production database beyond structured development practices like peer reviews and testing. However, if the database administrator (DBA) doesn't control who accesses the database or doesn't trust the developers, they believe that all verifications should be carried out within the database before each commit.