Though MongoDB has supported ACID transactions and complex aggregation options for years, sure publications nonetheless promote outdated misconceptions, claiming that solely SQL databases present strong knowledge consistency and highly effective querying capabilities. The “Advantages of Migrating” part in a spreadsheet firm’s article is a latest instance. It is one more probability to be taught from—and proper—deceptive claims.
The claims ignore MongoDB’s superior querying and multi-document transaction help. Written to market migration instruments, this overlooks that MongoDB’s easy CRUD API is environment friendly for single-document duties, and as a general-purpose database, it additionally presents specific transactions and powerful aggregation queries like SQL.
Enhanced Knowledge Consistency and Reliability
The migration software firm justifies migrating by stating:
PostgreSQL’s ACID compliance ensures that each one transactions are processed reliably, sustaining knowledge integrity even within the occasion of system failures. That is notably necessary for purposes that require robust consistency, resembling monetary methods or stock administration.
Sure, PostgreSQL does present ACID transactions and powerful consistency, however that is primarily true for single-node deployments. In high-availability and sharded settings, reaching robust consistency and ACID properties is extra sophisticated (see an instance, and one other instance).
Due to this fact, highlighting ACID compliance as a purpose emigrate from one other database—when that various additionally helps ACID transactions—shouldn’t be right. As an example, single-node MongoDB has provided ACID compliance for years, and since v4.2, it helps multi-document transactions throughout duplicate units and sharded clusters. Let’s present some syntax examples for the domains they talked about.
Instance: Monetary system
Switch $100 from Alice’s account to Bob’s account
// Initialize knowledge
db.accounts.insertMany([
{ account_id: "A123", name: "Alice", balance: 500 },
{ account_id: "B456", name: "Bob", balance: 300 }
]);
// Begin a transaciton in a session
const session = db.getMongo().startSession();
strive {
accounts = session.getDatabase(db.getName()).accounts
session.startTransaction();
// Deduct $100 from Alice
accounts.updateOne(
{ account_id: "A123" },
{ $inc: { steadiness: -100 } }
);
// Add $100 to Bob
accounts.updateOne(
{ account_id: "B456" },
{ $inc: { steadiness: 100 } }
);
session.commitTransaction();
} catch (error) {
session.abortTransaction();
console.error("Transaction aborted because of error:", error);
} lastly {
session.endSession();
}
Why ACID issues in MongoDB right here:
- Atomicity: Deduct and credit score, both each occur or neither occurs.
- Consistency: The entire steadiness throughout accounts stays correct.
- Isolation: Different concurrent transfers gained’t intrude mid-flight.
- Sturdiness: As soon as dedicated, adjustments survive crashes.
Instance: Stock administration
Promoting a product and recording that sale.
strive {
stock = session.getDatabase(db.getName()).stock
session.startTransaction();
// Scale back stock depend
stock.updateOne(
{ product_id: "P100" },
{ $inc: { amount: -1 } }
);
// Add a report of the sale
gross sales.insertOne(
{ product_id: "P100", sale_date: new Date(), amount: 1 }
);
session.commitTransaction();
} catch (error) {
session.abortTransaction();
console.error("Transaction aborted because of error:", error);
} lastly {
session.endSession();
}
ACID ensures in MongoDB:
- No partial updates
- Stock stays synchronized with gross sales data
- Secure for concurrent orders
- Sturdy as soon as dedicated
Superior Question Capabilities
The migration software vendor justifies migrating by stating:
PostgreSQL presents highly effective querying capabilities, together with:
- Advanced joins throughout a number of tables
- Superior aggregations and window features
- Full-text search with options like rating and highlighting
- Help for geospatial knowledge and queries
These permit for extra refined knowledge evaluation and reporting in comparison with MongoDB’s extra restricted querying capabilities.
This utterly overlooks MongoDB’s aggregation pipeline.
Advanced joins
MongoDB’s $lookup stage joins collections, even a number of instances if you would like.
Instance: Be part of orders with prospects to get buyer names.
db.orders.combination([
{
$lookup: {
from: "customers",
localField: "customer_id",
foreignField: "_id",
as: "customer_info"
}
},
{ $unwind: "$customer_info" },
{
$project: {
order_id: 1,
product: 1,
"customer_info.name": 1
}
}
]);
Superior aggregations
Operators like $group, $sum, $avg, $depend deal with numeric calculations with ease.
Instance: Whole gross sales quantity per product.
db.gross sales.combination([
{
$group: {
_id: "$product_id",
totalRevenue: { $sum: "$amount" },
avgRevenue: { $avg: "$amount" }
}
},
{ $sort: { totalRevenue: -1 } }
]);
Window-like features
MongoDB has $setWindowFields for operations akin to SQL window features.
Working complete of gross sales, sorted by date:
db.gross sales.combination([
{ $sort: { sale_date: 1 } },
{
$setWindowFields: {
sortBy: { sale_date: 1 },
output: {
runningTotal: {
$sum: "$amount",
window: { documents: ["unbounded", "current"] }
}
}
}
}
]);
Full-text search with rating & highlighting
MongoDB helps each easy textual content indexes and Atlas Search (powered by Apache Lucene).
Instance with Atlas Search: Search in articles and spotlight matches.
db.articles.combination([
{
$search: {
index: "default",
text: {
query: "machine learning",
path: ["title", "body"]
},
spotlight: { path: "physique" }
}
},
{
$mission: {
title: "1,"
rating: { $meta: "searchScore" },
highlights: { $meta: "searchHighlights" }
}
}
]);
Geospatial queries
Native geospatial indexing with operators like $close to.
Instance: Discover eating places inside 1 km of a degree.
db.eating places.createIndex({ location: "2dsphere" });
db.eating places.discover({
location: {
$close to: {
$geometry: { sort: "Level", coordinates: [-73.97, 40.77] },
$maxDistance: 1000
}
}
});
Conclusion
MongoDB and PostgreSQL have equal capabilities for ACID transactions and “superior” queries — the distinction lies in syntax and knowledge mannequin.
In contrast to methods that use two-phase locking, MongoDB transactions don’t block. They detect conflicts and let the applying deal with ready and retrying if wanted. This will likely impression situations requiring pessimistic locking (like SELECT FOR UPDATE SKIP LOCKED), however for common transactions, like these outlined within the SQL customary, MongoDB presents full help.
As an alternative of SQL in textual content strings despatched to the database server to be interpreted at runtime, MongoDB makes use of a staged aggregation pipeline, absolutely built-in in your utility language.
Migrating to PostgreSQL doesn’t magically grant you ACID or superior analytics — when you’re already utilizing MongoDB’s options, you have already got them.
