a curated list of database news from authoritative sources

July 22, 2025

July 21, 2025

Using replicaSetHorizons in MongoDB

When running MongoDB replica sets in containerized environments like Docker or Kubernetes, making nodes reachable from inside the cluster as well as from external clients can be a challenge. To solve this problem, this post will explain the Horizons feature of Percona Server for MongoDB. Let’s start by looking at what happens behind the scenes […]

Database latency with PostgreSQL and MongoDB: it's the data model that makes it fast

A business transaction ideally involves a single roundtrip to the database. MongoDB allows a single document to hold all transaction data, simplifying sharding and scaling. In contrast, SQL normalization spreads data across multiple tables, necessitating multi-statement transactions and multiple roundtrips, unless using stored procedures, which are often less preferred due to language and deployment differences from application code. This results in higher latency when the application is closer to the user and farther from the database.

PostgreSQL transaction with pgbench

I start a lab with a PostgreSQL database server running in the background and a container for the client application:

docker rm -f app db
docker run --name db -d -e POSTGRES_PASSWORD=x postgres
docker run --link db:db --rm -it --privileged postgres bash

I add a 50 millisecond latency from the application container to simulate a deployement where the application is in an edge location:

apt-get update && apt-get install -y iproute2
tc qdisc add dev eth0 root netem delay 50ms

I initialize and run pgbench with the default workload TCPB-like displaying the statements:

pgbench -i postgres://postgres:x@db
pgbench -r postgres://postgres:x@db

Here is the output:

root@e18764bd2f77:/# pgbench -i postgres://postgres:x@db

dropping old tables...
creating tables...
generating data (client-side)...
vacuuming...
creating primary keys...
done in 1.75 s (drop tables 0.05 s, create tables 0.21 s, client-side generate 0.94 s, vacuum 0.24 s, primary keys 0.31 s).

root@e18764bd2f77:/# pgbench -r postgres://postgres:x@db

pgbench (17.5 (Debian 17.5-1.pgdg120+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
number of failed transactions: 0 (0.000%)
latency average = 353.092 ms
initial connection time = 263.115 ms
tps = 2.832121 (without initial connection time)

statement latencies in milliseconds and failures:
         0.004           0  \set aid random(1, 100000 * :scale)
         0.001           0  \set bid random(1, 1 * :scale)
         0.001           0  \set tid random(1, 10 * :scale)
         0.001           0  \set delta random(-5000, 5000)
        50.226           0  BEGIN;
        50.470           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
        50.344           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
        50.350           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
        50.416           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
        50.292           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
        50.981           0  END;

This has run ten transactions from one client. With the artificial 50 ms latency, the connection has taken 263 ms and the transactions 352 ms on average. This is 7 times the network roundtrip latency. The reason is visible thanks to the -r option showing the per-statement response times: 7 statements have been run to start the transaction, run DML statements, and commit the transaction.

The default PgBench script is:

root@e18764bd2f77:/# pgbench --show-script=tpcb-like

-- tpcb-like: <builtin: TPC-B (sort of)>
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;

BEGIN; starts a transaction and END; commits its.

This method is inefficient due to multiple network roundtrips between the application and database, which increase latency. Each client-server roundtrip is a system call, which increases CPU usage due to context switching, and leaves the database backend in the worst state for performance and scalability: idle yet locking resources. All operations for a business transaction should ideally be sent in a single request, either as an anonymous block or stored procedure, to minimize roundtrips, reduce network overhead and leave a stateless connection.

PostgreSQL single auto-commit call

I can run the same in one block with a DO command in PostgreSQL:

DO '
 begin
  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
  PERFORM abalance FROM pgbench_accounts WHERE aid = :aid;
  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
 end;
';

Note that begin and end have different meanings here, marking the boundaries of a PL/pgSQL block. Without explicitly starting a transaction, the call operates in autocommit mode. One transaction per call is the default in PostgreSQL.

I changed SELECT to PERFORM because such call cannot return the result. In practice, you must deploy this as a stored procedure, with exception handling, and all business logic in it, and that's the reason it is not used in modern application. I use the DO block to show that this requires a single roundtrip to the database:

root@e18764bd2f77:/# pgbench --show-script=tpcb-like 2>&1 \
  | sed \
    -e "s/^BEGIN;/DO ' begin/" \
    -e "s/^SELECT/PERFORM/" \
    -e "s/^END;/end; ';/" \
  | pgbench -r -f /dev/stdin postgres://postgres:x@db

pgbench (17.5 (Debian 17.5-1.pgdg120+1))
starting vacuum...end.
transaction type: /dev/stdin
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
number of failed transactions: 0 (0.000%)
latency average = 51.763 ms
initial connection time = 263.673 ms
tps = 19.318893 (without initial connection time)

statement latencies in milliseconds and failures:
         0.002           0  \set aid random(1, 100000 * :scale)
         0.001           0  \set bid random(1, 1 * :scale)
         0.000           0  \set tid random(1, 10 * :scale)
         0.000           0  \set delta random(-5000, 5000)
        51.755           0  DO ' begin

The transaction took 50 ms to complete. It was started and committed on the database side, which is why all business logic and error handling must occur there. Using stored procedures adds complexity to development, testing, and deployment. A DO block doesn't have to be deployed to the database first, but complicates the process of returning information. Both stored procedures and DO blocks in PL/SQL are sent as PL/pgSQL code in a character string, interpreted at runtime, which poses a risk for runtime errors. Developers prefer to keep code in their application, using their language of choice, ensuring it is compiled, tested, packaged, and deployed consistently.

Multi-statement transactions in SQL databases struggle to scale due to increased client-server roundtrips when all logic resides in the applications. Normalization was developed when applications were deployed on database servers, utilizing embedded SQL or stored procedures. This allowed transactions to execute multiple statements and acquire locks using two-phase locking, without waiting in between. However, with the rise of client-server and three-tier architectures, this didn't scale.

Document databases utilize a different data modeling strategy, where a single document can contain all relevant transaction information. This approach allows the business logic to reside in the application code, enabling an entire business transaction to fit into a single atomic call.

MongoDB multi-document transaction

I ran the same workload on MongoDB, using one collection per table and a multi-document transaction. Since the issue lies in the data model rather than the database engine, the response time remains unchanged. Document databases only demonstrate their advantages when the document model aligns with business transactions. Normalization undermines this alignment. This is why benchmarks from PostgreSQL vendors can be misleading: they apply a normalized model to a database built for unnormalized models.

Still, I've done it to prove the point, doing the same as pgbench from mongosh.

I start a lab with a MongoDB database server running in the background and a container for the client application:

docker rm -f app db
docker run --name db --hostname db -d mongo mongod --replSet rs0
docker run --link db:db --rm -it --privileged mongo bash

I add a 50 millisecond latency from the application container:

apt-get update && apt-get install -y iproute2
tc qdisc add dev eth0 root netem delay 50ms

I define it as a single node replica set and start MongoDB shell:

mongosh "mongodb://db" --eval '
rs.initiate( {_id: "rs0", members: [
          {_id: 0, priority: 1, host: "db:27017"},
         ]});
'

mongosh "mongodb://db?replicaSet=rs0"

Here is my equivalent to pgbench -i:

// mongosh equivalent to pgbench -i

db.accounts.drop();  db.branches.drop();  db.tellers.drop();  db.history.drop(); 

db.branches.insertOne({ _id: 1, bbalance: 0 });

let tellerDocs = [];  for (let i = 1; i <= 10; ++i)  {
  tellerDocs.push({ _id: i, bid: 1, tbalance: 0 });
} ;
db.tellers.insertMany(tellerDocs);

const nAccounts = 100000;  const bulk = db.accounts.initializeUnorderedBulkOp();
for (let i = 1; i <= nAccounts; ++i) {
  bulk.insert({ _id: i, bid: 1, abalance: 0 });
  if (i % 10000 === 0) print(`inserted ${i} accounts`);
}  ; 
bulk.execute();

This has created three collections, and initialized it with data, "history" is empty and will be created when used:

rs0 [primary] test> show collections
accounts
branches
tellers

Here is my equivalent to pgbench with all default options:

// mongosh equivalent to pgbench with all default options

// Measure connection time
let t0 = Date.now();
let session = db.getMongo().startSession()
let sess_db = session.getDatabase(db.getName());
let connTime = Date.now() - t0;

// Run 10 transactions
let fail = 0;
let totalTime = 0;
let nTx = 10
for (let i = 0; i < nTx; ++i) {
    let t1 = Date.now();
    let aid = Math.floor(Math.random() * (100000)) + 1;
    let bid = Math.floor(Math.random() * (1)) + 1;
    let tid = Math.floor(Math.random() * (10)) + 1;
    let delta = Math.floor(Math.random() * 10001) - 5000;
    // BEGIN;
    session.startTransaction();
    // UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
    sess_db.accounts.updateOne({_id: aid}, {$inc: {abalance: delta}});
    // SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
    let acc = sess_db.accounts.findOne({_id: aid}, {abalance: 1, _id: 0});
    // UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
    sess_db.tellers.updateOne({_id: tid}, {$inc: {tbalance: delta}});
    // UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
    sess_db.branches.updateOne({_id: bid}, {$inc: {bbalance: delta}});
    // INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
    sess_db.history.insertOne({tid, bid, aid, delta, mtime: new Date()});
    // END;
    session.commitTransaction();
    totalTime += Date.now() - t1;
}
session.endSession();

// display timings
const avgLat = totalTime / nTx;
const tps = nTx / (totalTime / 1000);
print("latency average =", avgLat.toFixed(3), "ms");
print("initial connection time =", connTime.toFixed(3), "ms");
print("tps =", tps.toFixed(6), "(without initial connection time)");

Here is the output:

...
... print("latency average =", avgLat.toFixed(3), "ms");
... print("initial connection time =", connTime.toFixed(3), "ms");
... print("tps =", tps.toFixed(6), "(without initial connection time)");
latency average = 319.100 ms
initial connection time = 1.000 ms
tps = 3.133814 (without initial connection time)

One difference with PostgreSQL is that the start of a transaction doesn't need a roundtrip to the database in MongoDB, saving 50ms latency in this lab. Each statements takes 2ms more, but that's not comparable because PostgreSQL deffers lots of work to later with vacuum and the consequence is not visible when running ten transactions. MongoDB throughput is a bit higher than PostgreSQL for multi-table/collection transactions but not tremendeously because the real benefit comes from the document model, which is not used here.

I check that the ten transactions have been recorded in "history":

rs0 [primary] test> db.history.find().sort({mtime:1}).forEach(
                     doc => print(JSON.stringify(doc))
);

{"_id":"687c0863fafd81ddbcbaa8b9","tid":9,"bid":1,"aid":79275,"delta":2113,"mtime":"2025-07-19T21:04:35.438Z"}
{"_id":"687c0863fafd81ddbcbaa8ba","tid":10,"bid":1,"aid":12931,"delta":-5,"mtime":"2025-07-19T21:04:35.767Z"}
{"_id":"687c0864fafd81ddbcbaa8bb","tid":7,"bid":1,"aid":73292,"delta":-2319,"mtime":"2025-07-19T21:04:36.084Z"}
{"_id":"687c0864fafd81ddbcbaa8bc","tid":2,"bid":1,"aid":74453,"delta":-2909,"mtime":"2025-07-19T21:04:36.402Z"}
{"_id":"687c0864fafd81ddbcbaa8bd","tid":8,"bid":1,"aid":25159,"delta":-1522,"mtime":"2025-07-19T21:04:36.721Z"}
{"_id":"687c0865fafd81ddbcbaa8be","tid":5,"bid":1,"aid":21455,"delta":-2985,"mtime":"2025-07-19T21:04:37.036Z"}
{"_id":"687c0865fafd81ddbcbaa8bf","tid":8,"bid":1,"aid":66059,"delta":328,"mtime":"2025-07-19T21:04:37.353Z"}
{"_id":"687c0865fafd81ddbcbaa8c0","tid":8,"bid":1,"aid":58666,"delta":-4803,"mtime":"2025-07-19T21:04:37.668Z"}
{"_id":"687c0865fafd81ddbcbaa8c1","tid":1,"bid":1,"aid":99695,"delta":-4717,"mtime":"2025-07-19T21:04:37.987Z"}
{"_id":"687c0866fafd81ddbcbaa8c2","tid":9,"bid":1,"aid":15122,"delta":-20,"mtime":"2025-07-19T21:04:38.304Z"}

My business transactions, including deposits and withdrawals, are fully recorded in this collection. In contrast, other collections only maintain the current balance to avoid aggregating all historical operations. While this approach is valid, should the client application, which is close to the user and awaits completion, be responsible for such optimization?

MongoDB single-document transaction (auto-commit)

In MongoDB, achieving the same result in a single call is done not through interpreted procedural code or stored procedures, but by employing a proper document design. The TCPB-like benchmark records a transaction that modifies an account balance and updates some summaries per teller and branches. This workload was designed to stress the database in a non-scalable manner: the teller and branch summaries are hotspots.

In a proper application, the business transaction is recorded in the "history" collection. Summaries can be updated asynchronously by applying the transaction information, and a view can do the same in real-time if there's a need to see the current summary before it is applied to the account, teller, or branch collections. In this case, the workload on which the latency must be measured is a single-document insert into "history", with an additional field to flag what is applied to summaries.

Here is the code which records transactions in one atomic call to the database service:

let t0 = Date.now();
// No explicit session needed
let connTime = Date.now() - t0;
let totalTime = 0;
let nTx = 10;
for (let i = 0; i < nTx; ++i) {
    let t1 = Date.now();
    let aid = Math.floor(Math.random() * 100000) + 1;
    let bid = 1; // for scale 1
    let tid = Math.floor(Math.random() * 10) + 1;
    let delta = Math.floor(Math.random() * 10001) - 5000;
    db.history.insertOne({
        tid: tid,
        bid: bid,
        aid: aid,
        delta: delta,
        mtime: new Date(),
        to_apply: true // pending for background applier
    });
    t... (truncated)
                                    

Database latency with PostgreSQL and MongoDB: it's the data model that makes it fast

A business transaction ideally involves a single roundtrip to the database. MongoDB allows a single document to hold all transaction data, simplifying sharding and scaling. In contrast, SQL normalization spreads data across multiple tables, necessitating multi-statement transactions and multiple roundtrips, unless using stored procedures, which are often less preferred due to language and deployment differences from application code. This results in higher latency when the application is closer to the user and farther from the database.

  • PostgreSQL transaction with pgbench
  • PostgreSQL single auto-commit call
  • MongoDB multi-document transaction
  • MongoDB single-document transaction
  • Summary: where is your business logic

PostgreSQL transaction with pgbench

I start a lab with a PostgreSQL database server running in the background and a container for the client application:

docker rm -f app db
docker run --name db -d -e POSTGRES_PASSWORD=x postgres
docker run --link db:db --rm -it --privileged postgres bash

I add a 50 millisecond latency from the application container to simulate a deployement where the application is in an edge location:

apt-get update && apt-get install -y iproute2
tc qdisc add dev eth0 root netem delay 50ms

I initialize and run pgbench with the default workload TCPB-like displaying the statements:

pgbench -i postgres://postgres:x@db
pgbench -r postgres://postgres:x@db

Here is the output:

root@e18764bd2f77:/# pgbench -i postgres://postgres:x@db

dropping old tables...
creating tables...
generating data (client-side)...
vacuuming...
creating primary keys...
done in 1.75 s (drop tables 0.05 s, create tables 0.21 s, client-side generate 0.94 s, vacuum 0.24 s, primary keys 0.31 s).

root@e18764bd2f77:/# pgbench -r postgres://postgres:x@db

pgbench (17.5 (Debian 17.5-1.pgdg120+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
number of failed transactions: 0 (0.000%)
latency average = 353.092 ms
initial connection time = 263.115 ms
tps = 2.832121 (without initial connection time)

statement latencies in milliseconds and failures:
         0.004           0  \set aid random(1, 100000 * :scale)
         0.001           0  \set bid random(1, 1 * :scale)
         0.001           0  \set tid random(1, 10 * :scale)
         0.001           0  \set delta random(-5000, 5000)
        50.226           0  BEGIN;
        50.470           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
        50.344           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
        50.350           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
        50.416           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
        50.292           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
        50.981           0  END;

This has run ten transactions from one client. With the artificial 50 ms latency, the connection has taken 263 ms and the transactions 352 ms on average. This is 7 times the network roundtrip latency. The reason is visible thanks to the -r option showing the per-statement response times: 7 statements have been run to start the transaction, run DML statements, and commit the transaction.

The default PgBench script is:

root@e18764bd2f77:/# pgbench --show-script=tpcb-like

-- tpcb-like: <builtin: TPC-B (sort of)>
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;

In PostgreSQL, BEGIN; is like START TRANSACTION; and initiates a transaction, disabling auto-commit. END; is equivalent to COMMIT; and commits the current transaction.

This method is inefficient due to multiple network roundtrips between the application and database, which increase latency. Each client-server roundtrip is a system call, which increases CPU usage due to context switching, and leaves the database backend in the worst state for performance and scalability: idle yet locking resources. All operations for a business transaction should ideally be sent in a single request, either as an anonymous block or stored procedure, to minimize roundtrips, reduce network overhead and leave a stateless connection.

PostgreSQL single auto-commit call

I can run the same in one block with a DO command in PostgreSQL:

DO '
 begin
  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
  PERFORM abalance FROM pgbench_accounts WHERE aid = :aid;
  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
 end;
';

Note that begin and end have different meanings here, marking the boundaries of a PL/pgSQL block. Without explicitly starting a transaction, the call operates in autocommit mode. One transaction per call is the default in PostgreSQL.

I changed SELECT to PERFORM because such call cannot return the result. In practice, you must deploy this as a stored procedure, with exception handling, and all business logic in it, and that's the reason it is not used in modern application. I use the DO block to show that this requires a single roundtrip to the database:

root@e18764bd2f77:/# pgbench --show-script=tpcb-like 2>&1 \
  | sed \
    -e "s/^BEGIN;/DO ' begin/" \
    -e "s/^SELECT/PERFORM/" \
    -e "s/^END;/end; ';/" \
  | pgbench -r -f /dev/stdin postgres://postgres:x@db

pgbench (17.5 (Debian 17.5-1.pgdg120+1))
starting vacuum...end.
transaction type: /dev/stdin
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
number of failed transactions: 0 (0.000%)
latency average = 51.763 ms
initial connection time = 263.673 ms
tps = 19.318893 (without initial connection time)

statement latencies in milliseconds and failures:
         0.002           0  \set aid random(1, 100000 * :scale)
         0.001           0  \set bid random(1, 1 * :scale)
         0.000           0  \set tid random(1, 10 * :scale)
         0.000           0  \set delta random(-5000, 5000)
        51.755           0  DO ' begin

The transaction took 50 ms to complete. It was started and committed on the database side, which is why all business logic and error handling must occur there. Using stored procedures adds complexity to development, testing, and deployment. A DO block doesn't have to be deployed to the database first, but complicates the process of returning information. Both stored procedures and DO blocks in PL/SQL are sent as PL/pgSQL code in a character string, interpreted at runtime, which poses a risk for runtime errors. Developers prefer to keep code in their application, using their language of choice, ensuring it is compiled, tested, packaged, and deployed consistently.

Multi-statement transactions in SQL databases struggle to scale due to increased client-server roundtrips when all logic resides in the applications. Normalization was developed when applications were deployed on database servers, utilizing embedded SQL or stored procedures. This allowed transactions to execute multiple statements and acquire locks using two-phase locking, without waiting in between. However, with the rise of client-server and three-tier architectures, this didn't scale.

Document databases utilize a different data modeling strategy, where a single document can contain all relevant transaction information. This approach allows the business logic to reside in the application code, enabling an entire business transaction to fit into a single atomic call.

MongoDB multi-document transaction

I ran the same workload on MongoDB, using one collection per table and a multi-document transaction. Since the issue lies in the data model rather than the database engine, the response time remains unchanged. Document databases only demonstrate their advantages when the document model aligns with business transactions. Normalization undermines this alignment. This is why benchmarks from PostgreSQL vendors can be misleading: they apply a normalized model to a database built for unnormalized models.

Still, I've done it to prove the point, doing the same as pgbench from mongosh.

I start a lab with a MongoDB database server running in the background and a container for the client application:

docker rm -f app db
docker run --name db --hostname db -d mongo mongod --replSet rs0
docker run --link db:db --rm -it --privileged mongo bash

I add a 50 millisecond latency from the application container:

apt-get update && apt-get install -y iproute2
tc qdisc add dev eth0 root netem delay 50ms

I define it as a single node replica set and start MongoDB shell:

mongosh "mongodb://db" --eval '
rs.initiate( {_id: "rs0", members: [
          {_id: 0, priority: 1, host: "db:27017"},
         ]});
'

mongosh "mongodb://db?replicaSet=rs0"

Here is my equivalent to pgbench -i:

// mongosh equivalent to pgbench -i

db.accounts.drop();  db.branches.drop();  db.tellers.drop();  db.history.drop(); 

db.branches.insertOne({ _id: 1, bbalance: 0 });

let tellerDocs = [];  for (let i = 1; i <= 10; ++i)  {
  tellerDocs.push({ _id: i, bid: 1, tbalance: 0 });
} ;
db.tellers.insertMany(tellerDocs);

const nAccounts = 100000;  const bulk = db.accounts.initializeUnorderedBulkOp();
for (let i = 1; i <= nAccounts; ++i) {
  bulk.insert({ _id: i, bid: 1, abalance: 0 });
  if (i % 10000 === 0) print(`inserted ${i} accounts`);
}  ; 
bulk.execute();

This has created three collections, and initialized it with data, "history" is empty and will be created when used:

rs0 [primary] test> show collections
accounts
branches
tellers

Here is my equivalent to pgbench with all default options:

// mongosh equivalent to pgbench with all default options

// Measure connection time
let t0 = Date.now();
let session = db.getMongo().startSession()
let sess_db = session.getDatabase(db.getName());
let connTime = Date.now() - t0;

// Run 10 transactions
let fail = 0;
let totalTime = 0;
let nTx = 10
for (let i = 0; i < nTx; ++i) {
    let t1 = Date.now();
    let aid = Math.floor(Math.random() * (100000)) + 1;
    let bid = Math.floor(Math.random() * (1)) + 1;
    let tid = Math.floor(Math.random() * (10)) + 1;
    let delta = Math.floor(Math.random() * 10001) - 5000;
    // BEGIN;
    session.startTransaction();
    // UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
    sess_db.accounts.updateOne({_id: aid}, {$inc: {abalance: delta}});
    // SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
    let acc = sess_db.accounts.findOne({_id: aid}, {abalance: 1, _id: 0});
    // UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
    sess_db.tellers.updateOne({_id: tid}, {$inc: {tbalance: delta}});
    // UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
    sess_db.branches.updateOne({_id: bid}, {$inc: {bbalance: delta}});
    // INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
    sess_db.history.insertOne({tid, bid, aid, delta, mtime: new Date()});
    // END;
    session.commitTransaction();
    totalTime += Date.now() - t1;
}
session.endSession();

// display timings
const avgLat = totalTime / nTx;
const tps = nTx / (totalTime / 1000);
print("latency average =", avgLat.toFixed(3), "ms");
print("initial connection time =", connTime.toFixed(3), "ms");
print("tps =", tps.toFixed(6), "(without initial connection time)");

Here is the output:

...
... print("latency average =", avgLat.toFixed(3), "ms");
... print("initial connection time =", connTime.toFixed(3), "ms");
... print("tps =", tps.toFixed(6), "(without initial connection time)");
latency average = 319.100 ms
initial connection time = 1.000 ms
tps = 3.133814 (without initial connection time)

One difference with PostgreSQL is that the start of a transaction doesn't need a roundtrip to the database in MongoDB, saving 50ms latency in this lab. Each statements takes 2ms more, but that's not comparable because PostgreSQL deffers lots of work to later with vacuum and the consequence is not visible when running ten transactions. MongoDB throughput is a bit higher than PostgreSQL for multi-table/collection transactions but not tremendeously because the real benefit comes from the document model, which is not used here.

I check that the ten transactions have been recorded in "history":

rs0 [primary] test> db.history.find().sort({mtime:1}).forEach(
                     doc => print(JSON.stringify(doc))
);

{"_id":"687c0863fafd81ddbcbaa8b9","tid":9,"bid":1,"aid":79275,"delta":2113,"mtime":"2025-07-19T21:04:35.438Z"}
{"_id":"687c0863fafd81ddbcbaa8ba","tid":10,"bid":1,"aid":12931,"delta":-5,"mtime":"2025-07-19T21:04:35.767Z"}
{"_id":"687c0864fafd81ddbcbaa8bb","tid":7,"bid":1,"aid":73292,"delta":-2319,"mtime":"2025-07-19T21:04:36.084Z"}
{"_id":"687c0864fafd81ddbcbaa8bc","tid":2,"bid":1,"aid":74453,"delta":-2909,"mtime":"2025-07-19T21:04:36.402Z"}
{"_id":"687c0864fafd81ddbcbaa8bd","tid":8,"bid":1,"aid":25159,"delta":-1522,"mtime":"2025-07-19T21:04:36.721Z"}
{"_id":"687c0865fafd81ddbcbaa8be","tid":5,"bid":1,"aid":21455,"delta":-2985,"mtime":"2025-07-19T21:04:37.036Z"}
{"_id":"687c0865fafd81ddbcbaa8bf","tid":8,"bid":1,"aid":66059,"delta":328,"mtime":"2025-07-19T21:04:37.353Z"}
{"_id":"687c0865fafd81ddbcbaa8c0","tid":8,"bid":1,"aid":58666,"delta":-4803,"mtime":"2025-07-19T21:04:37.668Z"}
{"_id":"687c0865fafd81ddbcbaa8c1","tid":1,"bid":1,"aid":99695,"delta":-4717,"mtime":"2025-07-19T21:04:37.987Z"}
{"_id":"687c0866fafd81ddbcbaa8c2","tid":9,"bid":1,"aid":15122,"delta":-20,"mtime":"2025-07-19T21:04:38.304Z"}

My business transactions, including deposits and withdrawals, are fully recorded in this collection. In contrast, other collections only maintain the current balance to avoid aggregating all historical operations. While this approach is valid, should the client application, which is close to the user and awaits completion, be responsible for such optimization?

MongoDB single-document transaction

In MongoDB, achieving the same result in a single call is done not through interpreted procedural code or stored procedures, but by employing a proper document design. The TCPB-like benchmark records a transaction that modifies an account balance and updates some summaries per teller and branches. This workload was designed to stress the database in a non-scalable manner: the teller and branch summaries are hotspots.

In a proper application, the business transaction is recorded in the "history" collection. Summaries can be updated asynchronously by applying the transaction information, and a view can do the same in real-time if there's a need to see the current summary before it is applied to the account, teller, or branch collections. In this case, the workload on which the latency must be measured is a single-document insert into "history", with an additional field to flag what is applied to summaries.

Here is the code which records transactions in one atomic call to the database service:

let t0 = Date.now();
// No explicit session needed
let connTime = Date.now() - t0;
let totalTime = 0;
let nTx = 10;
for (let i = 0; i < nTx; ++i) {
    let t1 = Date.now();
    let aid = Math.floor(Math.random() * 100000) + 1;
    let bid = 1; // for scale 1
    let tid = Math.floor(Math.random() * 10) + 1;
    let delta = Math.floor(Math.random() * 10001) - 5000;
    db.history.insertOne({
        tid: tid,
        bid: bid,
        aid: aid,
        delta: delta
                                    
                                    
                                    
                                    
                                

July 20, 2025

Rickrolling Turso DB (SQLite rewrite in Rust)

This is a beginner’s guide to hacking into Turso DB (formerly known as Limbo), the SQLite rewrite in Rust. I will explore how to get familiar with Turso’s codebase, tooling and tests

$isArray: [💬,💬,💬] ❌ - Arrays are Argument Lists in MongoDB Aggregation Pipeline

If you test $isArray: [42] in MongoDB, it returns false and that's the right answer. If you test $isArray: [42,42,42] you get an error telling you that Expression $isArray takes exactly 1 arguments, but 3 were passed in. Documentation about $isArray explains that the input is interpreted as multiple arguments rather than a single argument that is an array:

Aggregation expressions accept a variable number of arguments. These arguments are normally passed as an array. However, when the argument is a single value, you can simplify your code by passing the argument directly without wrapping it in an array.

The documentation about the Expression Operator clarifies that when passing a single argument that is an array, you must use $literal or wrap the array in an additional array that will be interpreted by the language:

Here are a few reproducible examples (also available here) to explain it.

Expression Operator with literals

I use a collection with one document to run my expressions in an aggregation pipeline:

db.dual.insertOne( { "dummy": "x" } )

{
  acknowledged: true,
  insertedId: ObjectId('687d09913b111f172ebaa8ba')
}

$isNumber takes one argument and returns true if it is a number:

db.dual.aggregate([
{
    $project: {
      " is 42 a number?": {
        $isNumber: [ 42 ]       // one argument -> number
      }, "_id": 0
    }
  }
])

[ { ' is 42 a number?': true } ]

The array that you see in $isNumber: [ 42 ] is interpreted as a list of arguments for the expression operator. Text-based languages would use isNumber(42) but MongoDB query language is structured into BSON to better integrate with application languages and be easily parsed by the drivers.

Trying to pass two arguments raises an error:

db.dual.aggregate([
{
    $project: {
      " is 42 a number?": {
        $isNumber: [ 42 , 42 ]  // two arguments -> error
      }, "_id": 0
    }
  }
])

MongoServerError[Location16020]: Invalid $project :: caused by :: Expression $isNumber takes exactly 1 arguments. 2 were passed in.

For expression operators that take one argument, you can pass it as a value rather than as an array, but this is just a syntactic shortcut and doesn't change the data type of the argument:

db.dual.aggregate([
{
    $project: {
      " is 42 a number?": {
        $isNumber: 42           // one argument -> number    
      }, "_id": 0
    }
  }
])

[ { ' is 42 a number?': true } ]

$isArray takes a single argument and returns true if that argument is an array. However, in the examples above, the array syntax is used to structure the list of arguments rather than define a literal array data type:

db.dual.aggregate([
{
    $project: {
      " is 42 an array?": {
        $isArray: [ 42 ]       // one argument -> number
      }, "_id": 0
    }
  }
])

[ { ' is 42 an array?': false } ]


db.dual.aggregate([
{
    $project: {
      " is 42 an array?": {
        $isArray: [ 42 , 42 ]  // two arguments -> error
      }, "_id": 0
    }
  }
])

MongoServerError[Location16020]: Invalid $project :: caused by :: Expression $isArray takes exactly 1 arguments. 2 were passed in.

db.dual.aggregate([
{
    $project: {
      " is 42 an array?": {
        $isArray: 42           // one argument -> number
      }, "_id": 0
    }
  }
])

[ { ' is 42 an array?': false } ]

If you want to pass an array as an argument, you must nest the data array inside another array, which structures the list of arguments:

db.dual.aggregate([
{
    $project: {
      " is 42 an array?": {
        $isArray: [ [ 42 ] ]   // one argument -> array
      }, "_id": 0
    }
  }
])

[ { ' is 42 an array?': true } ]

db.dual.aggregate([
{
    $project: {
      " is 42 a number?": {
        $isNumber: [ [ 42 ] ]  // one argument -> array
      }, "_id": 0
    }
  }
])

[ { ' is 42 a number?': false } ]

Another possibility is using $literal, which doesn't take a list of arguments, but rather avoids parsing an array as a list of arguments:


db.dual.aggregate([
{
    $project: {
      " is 42 an array?": {
        $isArray: { $literal: [ 42 ] }  // one argument -> array
      }, "_id": 0
    }
  }
])

[ { ' is 42 an array?': true } ]

db.dual.aggregate([
{
    $project: {
      " is 42 a number?": {
        $isNumber: { $literal: [ 42 ] }  // one argument -> array
      }, "_id": 0
    }
  }
])

[ { ' is 42 a number?': false } ]

This can be confusing. I wrote this while answering a question on Bluesky:

#MongoDB seriously, why? $isNumber: 5 → true $isArray: [] → false $isArrau: [[5]] → true

Ivan “CLOVIS” Canet (@ivcanet.bsky.social) 2025-07-08T18:54:24.735Z

All languages exhibit certain idiosyncrasies where specific elements must be escaped to be interpreted literally rather than as language tokens. For instance, in PostgreSQL, a literal array must follow a specific syntax:

postgres=> SELECT pg_typeof(42) AS "is 42 a number?";

 is 42 a number?
-----------------
 integer

postgres=> SELECT pg_typeof(42, 42);

ERROR:  function pg_typeof(integer, integer) does not exist
LINE 1: SELECT pg_typeof(42, 42);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

postgres=> SELECT pg_typeof(ARRAY[42]);

 pg_typeof
-----------
 integer[]

postgres=> SELECT pg_typeof('{42}'::int[]);

 pg_typeof
-----------
 integer[]

main=> SELECT pg_typeof('{''42'',''42''}'::text[]);
 pg_typeof
-----------
 text[]

Additionally, in SQL, using double quotes serves to escape language elements, so that during parsing, they are interpreted as data or as part of the language syntax. In PostgreSQL the ambiguity is between text and array, in MongoDB it is between arguments and arrays.

You typically shouldn't encounter issues with MongoDB in common situations because expression operators are designed for expressions rather than literals. For instance, you don't need to call the database to know that 42 is a number and [] is an array. If this was generated by a framework, it likely uses $literal for clarity. If it is coded for expressions, there's no ambiguity.

Expression Operator with expressions

I use a collection containing one document with a field that is an array:

db.arrays.insertOne( { "field": [42] } )

{
  acknowledged: true,
  insertedId: ObjectId('687d1c413e05815622d4b0c2')
}

I can pass the "$field" expression argument to the operator and it remains an array:

db.arrays.aggregate([
{
    $project: {
      field: 1,
      " is $field a number?": {
        $isNumber: "$field"        // one argument -> expression    
      },
      " is $field an array?": {
        $isArray: "$field"         // one argument -> expression    
      },
      "_id": 0
    }
  }
])

[
  {
    field: [ 42 ],
    ' is $field a number?': false,
    ' is $field an array?': true
  }
]

In this case, using the one argument shortcut ("$field") or an array (["$field"]) doesn't matter because there's a clear distinction between language elements and data.

In short, with expressions that return arrays when executed, there's no problem, and for array literal, use $literal.

$isArray: [💬,💬,💬] ❌ - Arrays are Argument Lists in MongoDB Aggregation Pipeline

If you test $isArray: [42] in MongoDB, it returns false and that's the right answer. If you test $isArray: [42,42,42] you get an error telling you that Expression $isArray takes exactly 1 arguments, but 3 were passed in. Documentation about $isArray explains that the input is interpreted as multiple arguments rather than a single argument that is an array:

Aggregation expressions accept a variable number of arguments. These arguments are normally passed as an array. However, when the argument is a single value, you can simplify your code by passing the argument directly without wrapping it in an array.

The documentation about the Expression Operator clarifies that when passing a single argument that is an array, you must use $literal or wrap the array in an additional array that will be interpreted by the language:

Here are a few reproducible examples (also available here) to explain it.

Expression Operator with literals

I use a collection with one document to run my expressions in an aggregation pipeline:

db.dual.insertOne( { "dummy": "x" } )

{
  acknowledged: true,
  insertedId: ObjectId('687d09913b111f172ebaa8ba')
}

$isNumber takes one argument and returns true if it is a number:

db.dual.aggregate([
{
    $project: {
      " is 42 a number?": {
        $isNumber: [ 42 ]       // one argument -> number
      }, "_id": 0
    }
  }
])

[ { ' is 42 a number?': true } ]

The array that you see in $isNumber: [ 42 ] is interpreted as a list of arguments for the expression operator. Text-based languages would use isNumber(42) but MongoDB query language is structured into BSON to better integrate with application languages and be easily parsed by the drivers.

Trying to pass two arguments raises an error:

db.dual.aggregate([
{
    $project: {
      " is 42 a number?": {
        $isNumber: [ 42 , 42 ]  // two arguments -> error
      }, "_id": 0
    }
  }
])

MongoServerError[Location16020]: Invalid $project :: caused by :: Expression $isNumber takes exactly 1 arguments. 2 were passed in.

For expression operators that take one argument, you can pass it as a value rather than as an array, but this is just a syntactic shortcut and doesn't change the data type of the argument:

db.dual.aggregate([
{
    $project: {
      " is 42 a number?": {
        $isNumber: 42           // one argument -> number    
      }, "_id": 0
    }
  }
])

[ { ' is 42 a number?': true } ]

$isArray takes a single argument and returns true if that argument is an array. However, in the examples above, the array syntax is used to structure the list of arguments rather than define a literal array data type:

db.dual.aggregate([
{
    $project: {
      " is 42 an array?": {
        $isArray: [ 42 ]       // one argument -> number
      }, "_id": 0
    }
  }
])

[ { ' is 42 an array?': false } ]


db.dual.aggregate([
{
    $project: {
      " is 42 an array?": {
        $isArray: [ 42 , 42 ]  // two arguments -> error
      }, "_id": 0
    }
  }
])

MongoServerError[Location16020]: Invalid $project :: caused by :: Expression $isArray takes exactly 1 arguments. 2 were passed in.

db.dual.aggregate([
{
    $project: {
      " is 42 an array?": {
        $isArray: 42           // one argument -> number
      }, "_id": 0
    }
  }
])

[ { ' is 42 an array?': false } ]

If you want to pass an array as an argument, you must nest the data array inside another array, which structures the list of arguments:

db.dual.aggregate([
{
    $project: {
      " is 42 an array?": {
        $isArray: [ [ 42 ] ]   // one argument -> array
      }, "_id": 0
    }
  }
])

[ { ' is 42 an array?': true } ]

db.dual.aggregate([
{
    $project: {
      " is 42 a number?": {
        $isNumber: [ [ 42 ] ]  // one argument -> array
      }, "_id": 0
    }
  }
])

[ { ' is 42 a number?': false } ]

Another possibility is using $literal, which doesn't take a list of arguments, but rather avoids parsing an array as a list of arguments:


db.dual.aggregate([
{
    $project: {
      " is 42 an array?": {
        $isArray: { $literal: [ 42 ] }  // one argument -> array
      }, "_id": 0
    }
  }
])

[ { ' is 42 an array?': true } ]

db.dual.aggregate([
{
    $project: {
      " is 42 a number?": {
        $isNumber: { $literal: [ 42 ] }  // one argument -> array
      }, "_id": 0
    }
  }
])

[ { ' is 42 a number?': false } ]

This can be confusing. I wrote this while answering a question on Bluesky:

#MongoDB seriously, why? $isNumber: 5 → true $isArray: [] → false $isArrau: [[5]] → true

Ivan “CLOVIS” Canet (@ivcanet.bsky.social) 2025-07-08T18:54:24.735Z

All languages exhibit certain idiosyncrasies where specific elements must be escaped to be interpreted literally rather than as language tokens. For instance, in PostgreSQL, a literal array must follow a specific syntax:

postgres=> SELECT pg_typeof(42) AS "is 42 a number?";

 is 42 a number?
-----------------
 integer

postgres=> SELECT pg_typeof(42, 42);

ERROR:  function pg_typeof(integer, integer) does not exist
LINE 1: SELECT pg_typeof(42, 42);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

postgres=> SELECT pg_typeof(ARRAY[42]);

 pg_typeof
-----------
 integer[]

postgres=> SELECT pg_typeof('{42}'::int[]);

 pg_typeof
-----------
 integer[]

main=> SELECT pg_typeof('{''42'',''42''}'::text[]);
 pg_typeof
-----------
 text[]

Additionally, in SQL, using double quotes serves to escape language elements, so that during parsing, they are interpreted as data or as part of the language syntax. In PostgreSQL the ambiguity is between text and array, in MongoDB it is between arguments and arrays.

You typically shouldn't encounter issues with MongoDB in common situations because expression operators are designed for expressions rather than literals. For instance, you don't need to call the database to know that 42 is a number and [] is an array. If this was generated by a framework, it likely uses $literal for clarity. If it is coded for expressions, there's no ambiguity.

Expression Operator with expressions

I use a collection containing one document with a field that is an array:

db.arrays.insertOne( { "field": [42] } )

{
  acknowledged: true,
  insertedId: ObjectId('687d1c413e05815622d4b0c2')
}

I can pass the "$field" expression argument to the operator and it remains an array:

db.arrays.aggregate([
{
    $project: {
      field: 1,
      " is $field a number?": {
        $isNumber: "$field"        // one argument -> expression    
      },
      " is $field an array?": {
        $isArray: "$field"         // one argument -> expression    
      },
      "_id": 0
    }
  }
])

[
  {
    field: [ 42 ],
    ' is $field a number?': false,
    ' is $field an array?': true
  }
]

In this case, using the one argument shortcut ("$field") or an array (["$field"]) doesn't matter because there's a clear distinction between language elements and data.

In short, with expressions that return arrays when executed, there's no problem, and for array literal, use $literal.

July 18, 2025

Sequences in MongoDB

In a previous post about No-gap sequence in PostgreSQL and YugabyteDB, I mentioned that sequences in SQL databases are not transactional, which can lead to gaps. MongoDB does not require a special sequence object. A collection can be used thanks to incremental update ($inc) and returning the updated value in a single atomic operation with findOneAndUpdate().

Here is an example. I create a "sequence" collection to hold sequence numbers and a "demo" collection to insert values with an auto incremented identifier:

db.createCollection("demo");
db.createCollection("sequence");

An insert can simply fetch the next value while incrementing it:

db.demo.insertOne({
 _id: db.sequences.findOneAndUpdate(         
  { _id: "demo_id" },
  { $inc: { seq: 1 } },
  { upsert: true, returnDocument: "after" }
),
 value: "I'm the first"
});

db.demo.find();

[ { _id: { _id: 'demo_id', seq: 1 }, value: "I'm the first" } ]

I start two transactions:

sessionA = db.getMongo().startSession();
sessionA.startTransaction();
dbA = sessionA.getDatabase(db.getName());

sessionB = db.getMongo().startSession();
sessionB.startTransaction();
dbB = sessionB.getDatabase(db.getName());

Scalable sequence (accepting gaps on rollback)

The two transactions insert a document into "demo" with an "_id" fetched from the "sequences":

dbA.demo.insertOne({
 _id: db.sequences.findOneAndUpdate(         // non-transactional
  { _id: "demo_id" },
  { $inc: { seq: 1 } },
  { upsert: true, returnDocument: "after" }
),
 value: "I'll abort"
});

dbB.demo.insertOne({
 _id: db.sequences.findOneAndUpdate(         // non-transactional
  { _id: "demo_id" },
  { $inc: { seq: 1 } },
  { upsert: true, returnDocument: "after" }
),
 value: "I'll commit"
});

It is important to note that I increment ($inc) and fetch the value (returnDocument: "after") with db, out of the dbA or dbB transactions. The sequence operation is atomic, but non-transactional (not part of a multi-document transaction). This simulates the behavior of sequences in SQL databases.

The first transaction aborts (rollback) and the second one commits:


sessionA.abortTransaction();
sessionA.endSession();

sessionB.commitTransaction();
sessionB.endSession();

I check the result:

db.demo.find()

[
  { _id: { _id: 'demo_id', seq: 1 }, value: "I'm the first" },
  { _id: { _id: 'demo_id', seq: 3 }, value: "I'll commit" }
]

I have a gap in the numbers because "_id: 1" has been used by a transaction that aborted. The transaction has been rolled back, but because I incremented the sequence out of the transaction (using db instead of dbA) the incrementat was not rolled back.

Note that all updates to a single document are atomic, but I used findOneAndUpdate() so that it returns the updated document in the same atomic operation that updated it. It can return the before or after value and I used returnDocument: "after" to get the next value. I used upsert: true to initialize the sequence if no value exists, and $inc sets the field to the specified value when it doesn't exist.

No-gap Sequences (and optimistic locking)

If you want a no-gap sequence, you can fetch the sequence number in the multi-document transaction:


sessionA = db.getMongo().startSession();
sessionA.startTransaction();
dbA = sessionA.getDatabase(db.getName());

sessionB = db.getMongo().startSession();
sessionB.startTransaction();
dbB = sessionB.getDatabase(db.getName());

dbA.demo.insertOne({
 _id: dbA.sequences.findOneAndUpdate(      // part of the transaction
  { _id: "demo_id" },
  { $inc: { seq: 1 } },
  { upsert: true, returnDocument: "after" }
),
 value: "I'll abort"
});

dbB.demo.insertOne({
 _id: dbB.sequences.findOneAndUpdate(      // part of the transaction
  { _id: "demo_id" },
  { $inc: { seq: 1 } },
  { upsert: true, returnDocument: "after" }
),
 value: "I'll commit"
});

When two transactions try to increment the same sequence, an optimistic locking error is raised:


MongoServerError[WriteConflict]: Caused by :: Write conflict during plan execution and yielding is disabled. :: Please retry your operation or multi-document transaction.

This is a retriable error and the application should have implemented a retry logic:

sessionA.abortTransaction();


// retry the insert
sessionB.abortTransaction();
sessionB.startTransaction();
dbB.demo.insertOne({
 _id: dbB.sequences.findOneAndUpdate(      // part of the transaction
  { _id: "demo_id" },
  { $inc: { seq: 1 } },
  { upsert: true, returnDocument: "after" }
),
 value: "I'll commit"
});

sessionB.commitTransaction();
sessionB.endSession();
sessionA.endSession();

I check the result:

db.demo.find()

[
  { _id: { _id: 'demo_id', seq: 1 }, value: "I'm the first" },
  { _id: { _id: 'demo_id', seq: 3 }, value: "I'll commit" },
  { _id: { _id: 'demo_id', seq: 4 }, value: "I'll commit" }
]


Here, the first session rollback didn't introduce another gap because the sequence increment was part of the insert transaction. To achieve the same in a SQL database, you must use a table and UPDATE ... SET seq=seq+1 RETURNING seq;. With pessimistic locking, it acquires a lock and waits for the other transaction to complete. To be scalable, SQL databases provide a non-transactional SEQUENCE that does the same without waiting, but with gaps. It still has some scalability issues, and distributed databases may discourage (CockroachDB raises a warning) or even not support sequences (like Google Spanner or Amazon Aurora DSQL).

Incrementing identifiers for the primary key

MongoDB provides developers with greater control, allowing them to apply the same logic to a collection while deciding whether to include it in a transaction. It also supports optimized atomic operations. You can also use Atlas triggers to deploy the logic into the managed database, like demonstrated in MongoDB Auto-Increment

It's important to note that generating an incremented sequence is typically rare, primarily occurring during migrations from MySQL's AUTO_INCREMENT or PostgreSQL's BIGSERIAL. The default "_id" field is a globally unique and scalable ObjectId. You can also use a UUID generated by the application and choose the format (UUIDv4 or UUIDv7) to distribute or collocate the documents inserted at the same time.

Sequences in MongoDB

In a previous post about No-gap sequence in PostgreSQL and YugabyteDB, I mentioned that sequences in SQL databases are not transactional, which can lead to gaps. MongoDB does not require a special sequence object. A collection can be used thanks to incremental update ($inc) and returning the updated value in a single atomic operation with findOneAndUpdate().

Here is an example. I create a "sequence" collection to hold sequence numbers and a "demo" collection to insert values with an auto incremented identifier:

db.createCollection("demo");
db.createCollection("sequence");

An insert can simply fetch the next value while incrementing it:

db.demo.insertOne({
 _id: db.sequences.findOneAndUpdate(         
  { _id: "demo_id" },
  { $inc: { seq: 1 } },
  { upsert: true, returnDocument: "after" }
),
 value: "I'm the first"
});

db.demo.find();

[ { _id: { _id: 'demo_id', seq: 1 }, value: "I'm the first" } ]

I start two transactions:

sessionA = db.getMongo().startSession();
sessionA.startTransaction();
dbA = sessionA.getDatabase(db.getName());

sessionB = db.getMongo().startSession();
sessionB.startTransaction();
dbB = sessionB.getDatabase(db.getName());

Scalable sequence (accepting gaps on rollback)

The two transactions insert a document into "demo" with an "_id" fetched from the "sequences":

dbA.demo.insertOne({
 _id: db.sequences.findOneAndUpdate(         // non-transactional
  { _id: "demo_id" },
  { $inc: { seq: 1 } },
  { upsert: true, returnDocument: "after" }
),
 value: "I'll abort"
});

dbB.demo.insertOne({
 _id: db.sequences.findOneAndUpdate(         // non-transactional
  { _id: "demo_id" },
  { $inc: { seq: 1 } },
  { upsert: true, returnDocument: "after" }
),
 value: "I'll commit"
});

It is important to note that I increment ($inc) and fetch the value (returnDocument: "after") with db, out of the dbA or dbB transactions. The sequence operation is atomic, but non-transactional (not part of a multi-document transaction). This simulates the behavior of sequences in SQL databases.

The first transaction aborts (rollback) and the second one commits:


sessionA.abortTransaction();
sessionA.endSession();

sessionB.commitTransaction();
sessionB.endSession();

I check the result:

db.demo.find()

[
  { _id: { _id: 'demo_id', seq: 1 }, value: "I'm the first" },
  { _id: { _id: 'demo_id', seq: 3 }, value: "I'll commit" }
]

I have a gap in the numbers because {_id: 2} has been used by a transaction that aborted. The transaction has been rolled back, but because I incremented the sequence out of the transaction (using db instead of dbA) the increment was not rolled back.

Note that all updates to a single document are atomic, but I used findOneAndUpdate() so that it returns the updated document in the same atomic operation that updated it. It can return the before or after value and I used returnDocument: "after" to get the next value. I used upsert: true to initialize the sequence if no value exists, and $inc sets the field to the specified value when it doesn't exist.

No-gap Sequences (and optimistic locking)

If you want a no-gap sequence, you can fetch the sequence number in the multi-document transaction:


sessionA = db.getMongo().startSession();
sessionA.startTransaction();
dbA = sessionA.getDatabase(db.getName());

sessionB = db.getMongo().startSession();
sessionB.startTransaction();
dbB = sessionB.getDatabase(db.getName());

dbA.demo.insertOne({
 _id: dbA.sequences.findOneAndUpdate(      // part of the transaction
  { _id: "demo_id" },
  { $inc: { seq: 1 } },
  { upsert: true, returnDocument: "after" }
),
 value: "I'll abort"
});

dbB.demo.insertOne({
 _id: dbB.sequences.findOneAndUpdate(      // part of the transaction
  { _id: "demo_id" },
  { $inc: { seq: 1 } },
  { upsert: true, returnDocument: "after" }
),
 value: "I'll commit"
});

When two transactions try to increment the same sequence, an optimistic locking error is raised:


MongoServerError[WriteConflict]: Caused by :: Write conflict during plan execution and yielding is disabled. :: Please retry your operation or multi-document transaction.

This is a retriable error and the application should have implemented a retry logic:

sessionA.abortTransaction();


// retry the insert
sessionB.abortTransaction();
sessionB.startTransaction();
dbB.demo.insertOne({
 _id: dbB.sequences.findOneAndUpdate(      // part of the transaction
  { _id: "demo_id" },
  { $inc: { seq: 1 } },
  { upsert: true, returnDocument: "after" }
),
 value: "I'll commit"
});

sessionB.commitTransaction();
sessionB.endSession();
sessionA.endSession();

I check the result:

db.demo.find()

[
  { _id: { _id: 'demo_id', seq: 1 }, value: "I'm the first" },
  { _id: { _id: 'demo_id', seq: 3 }, value: "I'll commit" },
  { _id: { _id: 'demo_id', seq: 4 }, value: "I'll commit" }
]


Here, the first session rollback didn't introduce another gap because the sequence increment was part of the insert transaction. To achieve the same in a SQL database, you must use a table and UPDATE ... SET seq=seq+1 RETURNING seq;. With pessimistic locking, it acquires a lock and waits for the other transaction to complete. To be scalable, SQL databases provide a non-transactional SEQUENCE that does the same without waiting, but with gaps. It still has some scalability issues, and distributed databases may discourage (CockroachDB raises a warning) or even not support sequences (like Google Spanner or Amazon Aurora DSQL).

Incrementing identifiers for the primary key

MongoDB provides developers with greater control, allowing them to apply the same logic to a collection while deciding whether to include it in a transaction. It also supports optimized atomic operations. You can also use Atlas triggers to deploy the logic into the managed database, like demonstrated in MongoDB Auto-Increment

It's important to note that generating an incremented sequence is typically rare, primarily occurring during migrations from MySQL's AUTO_INCREMENT or PostgreSQL's BIGSERIAL. The default "_id" field is a globally unique and scalable ObjectId. You can also use a UUID generated by the application and choose the format (UUIDv4 or UUIDv7) to distribute or collocate the documents inserted at the same time.

How Can AI Talk to My Database Part Two: MySQL and Gemini

My first experiments creating an MCP Server to provide AI access to a PostgreSQL database using the FastMCP Python framework and Anthropic’s and OpenAI’s APIs highlighted an important requirement: for now, these two APIs can only communicate with an MCP server through HTTPS over a public URL. While researching how to make this work (which […]

July 17, 2025

How Can AI Talk to My (PostgreSQL) Database?

I admittedly have some work to do to catch up with the AI “trend”. It’s been around (as in, easily accessible) for a few years now, but I can probably still count on my fingers the number of times I’ve used a prompt to ask it anything. That is, discounting the mostly frustrating and usually […]

July 16, 2025

July 15, 2025

Volatility classification in PostgreSQL

In this post, we discuss different ways you can use volatility classification with functions in PostgreSQL and provide best practices to help you keep your database optimized and develop efficient and reliable database applications.