I’m often asked, “Which database is better: Postgres or Mongo?” In my opinion, that’s the wrong question. The right question is, “Which database is better for our needs?”

In this two-part series, we’ll examine key decision points that might sway you toward either PostgreSQL or MongoDB, based on real-world use cases and my personal experience. Keep in mind, these are just my views—you won’t find TPC benchmarks or performance charts here. I also acknowledge I might sound like a Postgres fan (guilty!), but I’ve tried my best to be objective.

In the opening segment, let's think about the following:

1. ACID vs. BASE
2. Data Types
3. JSON Support
4. Speed for Small Updates/Queries (OLTP)
5. Speed for Batch Processing (Large Updates/Queries)
6. Database Design (ERD)
7. SQL Support
8. Database Limitations

A Matter of ACID vs. BASE

One of the biggest contrasts between Postgres and Mongo lies in how each handles transactions. PostgreSQL is an ACID (Atomicity, Consistency, Isolation, Durability) database, meaning transactions can be rolled back cleanly—even if you create or alter a table within that transaction. This is quite powerful: you can do something like:

BEGIN;

CREATE TABLE managers AS 

   SELECT * FROM emp 

   WHERE manager = 'YES';

DELETE FROM emp 

   WHERE manager = 'YES';

COMMIT;

If the CREATE TABLE statement fails, the entire transaction is rolled back, leaving the database unchanged. Oracle, which is often considered a gold standard for relational databases, auto-commits its DDL statements, making Postgres, in some ways, even more “ACID-compliant” than Oracle.

Both databases claim to handle JSON well, but it’s fair to say Mongo is built around JSON at its core. When you run an update in Mongo, you might see commands like:

MongoDB, on the other hand, is generally described as BASE (Basically Available, Soft state, Eventually consistent). It has made strides toward ACID-like transactions—Mongo does now allow multi-document transactions—but these have limits, like a recommended cap of around 1,000 documents per transaction. Also, certain operations, such as creating or dropping a collection, do not fit neatly into a Mongo transaction.

When is this difference most important? If your application requires absolute accuracy—like in banking, trading, or healthcare—Postgres’s strong ACID compliance can be a lifesaver. If, however, you’re dealing with extremely high data volumes where some level of “eventual” consistency is acceptable (like real-time ad tech or certain fraud detection systems), Mongo’s performance and simplicity might prove advantageous.

A common counterexample is indeed fraud detection, where one might argue that any financial system needs strict ACID guarantees. But in fraud detection, there is often a need to handle a massive stream of incoming transactions and run multiple checks on each one. Mongo can ingest data at high speed, letting you run many “mostly accurate” checks, whereas Postgres might handle fewer, fully consistent checks at the same throughput. In the end, it’s a trade-off: high velocity and some eventual consistency, or fewer transactions per second and guaranteed consistency.

Differences in Data Types

Another key difference is how each database handles data types. MongoDB “speaks” JSON, so every record you store is essentially a JSON document. While Mongo does allow certain data-type hints—like integers and dates—they function more like markers within the JSON itself rather than strict definitions. Because Mongo is schema-less, you can’t simply declare a column of type DATE or INT; you just insert JSON, and Mongo does its best to handle whatever you send.

Postgres, being a relational database, supports a wide array of data types out of the box: integers, text, dates, binary fields (bytea), and more specialized types such as chkpass for encrypted passwords or IP-related types (like inet or cidr) for storing and validating IP addresses. With a type like chkpass, for instance, any password you insert is automatically encrypted, and retrieving it only shows an encrypted placeholder—making it useless to anyone trying to read the raw value.

On top of that, Postgres supports JSON and JSONB fields, but you’re not forced to store data as JSON. This design philosophy helps future-proof your schema if JSON falls out of favor, or if you just decide a different approach works better in certain parts of your application. Mongo, by comparison, is deeply committed to JSON—great if that’s exactly what you need, but you’re locked into that model unless you implement your own transformations.

Working with JSON

db.toys.updateOne(

  { toy_name: "Buzz Lightyear" },

  { $set: { "can_do": "fly" } }

);

Here, both the “filter” parameter ({ toy_name: "Buzz Lightyear" }) and the “update” parameter ({ $set: { "can_do": "fly" } }) are JSON documents. Mongo also has an extensive array of operators, such as $inc, $pop, $push, $pull, $addToSet, and so on. This makes updating partial fields of a JSON object extremely straightforward.

Postgres can absolutely store JSON, but it uses functions like jsonb_set or jsonb_insert to modify JSON columns. For instance, you might do:

UPDATE toys

  SET json_col = jsonb_insert(json_col, '{can do, -1}', '"fly"'::jsonb, true)

  WHERE toy_name = 'Buzz Lightyear';

While this is powerful, it’s less direct than Mongo’s $push or $pop. Removing items from a JSON array in Postgres often requires a subselect or other workaround, especially if the database needs to reconstruct the array minus the unwanted element. Mongo’s JSON-based updates feel more natural if you’re making constant, partial changes to a document.

If you live and breathe JSON, especially in Node.js or a similarly document-focused environment, Mongo will likely feel more “at home.” Postgres can handle JSON, but it’s not the full foundation of the database.

Speed for Small Updates and Queries (OLTP)

Many of our daily activities—like withdrawing money at an ATM or buying a movie ticket—involve OLTP (Online Transaction Processing). OLTP systems typically have frequent, small reads and writes, often using primary keys or basic indexes to fetch small sets of rows.

Mongo has strong indexing capabilities, letting you index almost any field inside your JSON documents. This means small queries and updates can be very quick. Postgres also has robust indexing and, importantly, ACID transaction management. Both can reach high throughput, but if you need absolute consistency—where a single transaction can’t be partially applied—Postgres is typically the safer choice because it guarantees your data is correct end-to-end.

Handling Large Workloads and Batch Processing

When we talk about data warehouses (DWH), big data systems, or large-scale reporting, we think about huge batch inserts, massive queries, and possibly real-time analytics. Postgres has several tools that help in these scenarios:

• Sharding using an extension like Citus DB, which allows you to distribute data across multiple nodes.
• Standby instances that can be queried, offloading reads from the primary.
• Materialized views to refresh data from staging tables.
• Foreign Data Wrappers (FDW), letting you query remote databases (not only Postgres) as though they were local tables.
• Table partitioning, which can be combined with FDWs to create something akin to a “poor man’s sharding” if you don’t want a full extension like Citus.

Mongo also shines for big data use cases, offering features such as:

• Sharding (known simply as a shard cluster).
• TTL (Time To Live) collections that can automatically delete outdated data.
• Capped collections, which stop growing once they reach a set size (handy for logs).
• Replica sets for scaling reads, with “read concerns” letting you direct certain queries to secondary nodes.

For large-scale analytics, Postgres’s SQL compliance can be a huge advantage if you rely on traditional BI tools or do a lot of ad hoc queries that join multiple tables or use window functions. Mongo handles constantly shifting schemas very well and can still be efficient with large volumes, but it’s not using SQL natively, so the approach is different.

Database Design and ERDs

Postgres is a relational database with optional JSON columns, so you can have a typical table structure (with fixed columns like id, name, category) plus a flexible JSONB column for extra data. If you have a products table, you might store universal attributes—like ID, Name, Category—in regular columns, and any product-specific attributes (maybe “RAM size” for a laptop or “color” for a T-shirt) in a JSON column. This “hybrid” approach keeps your schema clear while letting you store variable data without overhauling the entire design.

Mongo, by contrast, is truly schema-less. You don’t have a table structure, so each document can hold whatever fields you insert, and the database won’t complain—even if you make a typo like ExtandedData instead of Extanded_data. This flexibility can accelerate initial development but can also lead to inconsistent data. Another side effect is the need to replicate or embed data in multiple documents for performance, since there are no foreign keys. Over time, this can create duplications or conflicts if your application logic isn’t careful.

Living in a SQL World

SQL is the go-to language for query operations in many organizations. Postgres is fully ANSI SQL–compliant, meaning it works well with virtually any SQL-based tool, whether that’s a reporting engine like Tableau or a data processing framework like Apache Spark (via JDBC).

Mongo doesn’t speak SQL by default. Some third-party tools, like Studio3T, can provide a limited SQL interface, but it’s often restricted to basic SELECT–style queries. You can’t always do complex window functions, CTEs, or the type of advanced joins you might do in Postgres. If you rely heavily on these features or already have a data infrastructure built around SQL, Postgres is usually a more natural fit.

Potential Limitations

Every database has practical limits, but there are two major considerations with Mongo:

1. Connections: Mongo doesn’t provide a built-in server-side connection pool like Postgres does with pgbouncer or pgpool. This means you could more quickly run out of connections if your application spawns too many. Postgres, by contrast, offers mature server-side pooling solutions to handle large numbers of clients more gracefully.
2. Document Size: Mongo caps individual document size at 16 MB. Postgres can store JSON up to 1 GB in a single JSONB field, which is significantly higher. If you’re appending new data to the same document in Mongo—for instance, pushing customer activity logs into one doc—the document can blow past 16 MB relatively quickly. By then, you’d have to split it into multiple documents, which complicates your design. Postgres is generally more forgiving if your data can balloon over time.

Closing Thoughts

Deciding between Postgres and Mongo isn’t just about picking “the best” database in absolute terms—it’s about matching the database’s strengths to your application’s needs. Postgres gives you strict ACID compliance, a rich set of data types, hybrid JSON+relational models, and full SQL support, making it a robust all-around choice. Mongo excels at flexible document schemas, high-speed ingestion, and deep JSON updates, which can be a lifesaver in certain fast-evolving or high-volume scenarios.

Neither database is automatically “better” than the other. It’s all about weighing your priorities—do you need unbreakable transactions, or can you accept eventual consistency for the sake of performance? Do you want strict schemas, or do you prefer to store and evolve data with fewer constraints? By considering these points in detail, you’ll have a clearer picture of which path makes the most sense for your project.

The conversation doesn’t end here, of course. There are still many advanced topics—like indexing strategies, backup and recovery options, or how both databases scale beyond a single node. I’ll explore those in the second part of this discussion. In the meantime, I’d love to hear your thoughts, especially if you’ve used both Postgres and Mongo in real-world scenarios. Let me know what you think!