MongoDB vs. PostgreSQL: Performance & Functionality

Summary

This video compares relational databases (PostgreSQL) and document-oriented databases (MongoDB), focusing on how they handle JSON data. It explores the strengths and weaknesses of using PostgreSQL as a document store with JSONB, including indexing and performance benchmarks. Through real-world tests, the video evaluates latency, CPU usage, and disk operations under heavy load, comparing PostgreSQL and MongoDB. Key differences are highlighted, such as MongoDB’s ability to join collections and PostgreSQL’s limited document storage. The conclusion suggests that while PostgreSQL can serve as a document store, MongoDB is better suited for handling JSON data at scale.

Takeaways

• 😀 Relational databases store data in structured tables with foreign and primary keys, optimizing storage and query performance through normalization.
• 😀 Document databases, like MongoDB, offer more flexible schemas, allowing data to be stored in documents without the need for predefined tables or fixed schemas.
• 😀 PostgreSQL can function as a document-oriented database by using JSON or JSONB data types, with JSONB offering better performance and smaller storage requirements compared to JSON.
• 😀 Indexing plays a crucial role in performance, especially when working with JSON documents. In PostgreSQL, GIN indexes are often used for efficient querying, while MongoDB automatically creates indexes for primary keys.
• 😀 MongoDB allows you to join multiple collections and perform aggregations on JSON documents, a feature not natively supported by PostgreSQL for JSON data types.
• 😀 Performance tests reveal that MongoDB can handle higher query loads (up to 13,000 queries per second) compared to PostgreSQL, which starts to experience significant latency and failure at around 6,000 queries per second.
• 😀 Latency for insert, update, delete, and select operations in PostgreSQL increases as load increases, particularly when disk writes become a bottleneck.
• 😀 PostgreSQL has higher disk write IOPS compared to other databases, which can lead to performance degradation under heavy load.
• 😀 PostgreSQL is suitable for temporary JSON storage but may struggle to scale as a document database, whereas MongoDB is better suited for applications heavily relying on JSON and scalable architecture.
• 😀 For e-commerce websites with diverse products, a document-oriented database like MongoDB offers more flexibility as products with different attributes don't require null values for irrelevant columns.
• 😀 Although both databases perform similarly under low loads, PostgreSQL's performance drops significantly under higher query volumes, making MongoDB a better choice for large-scale, high-performance applications.

Q & A

What is the main difference between relational and document databases?

Relational databases store data in structured tables with predefined schemas, using primary and foreign keys to establish relationships. Document databases, on the other hand, store data in flexible formats like JSON or BSON, allowing for schema-less or dynamic structures, making them more suitable for handling unstructured or semi-structured data.

Why would you choose a document database over a relational one?

You would choose a document database when dealing with data that requires flexibility in its structure, such as varying attributes across items (e.g., e-commerce products with different characteristics). Document databases like MongoDB allow for schema flexibility, while relational databases struggle with this flexibility.

How does Postgres handle JSON data, and what are the differences between JSON and JSONB?

Postgres supports two types for storing JSON data: JSON and JSONB. JSON is text-based and preserves whitespace and key order, making it more human-readable. JSONB, however, is binary-encoded, smaller in size, and supports indexing, making it more efficient for querying and updating JSON data.

When working with JSON in Postgres, what index should you use, and why?

When working with JSON in Postgres, the recommended index is the GIN (Generalized Inverted Index). This index is particularly useful for documents with many different keys, as it allows for efficient searching of specific keys within JSON documents.

What performance benefits does using JSONB in Postgres provide over using JSON?

JSONB in Postgres provides better performance than JSON because it is binary-encoded, smaller in size, and allows for indexing. This makes queries that search by specific keys or perform updates much more efficient in terms of both speed and resource usage.

What are some of the features unique to MongoDB when working with JSON documents?

MongoDB offers features like the ability to join multiple collections, similar to SQL's left outer joins, and aggregate data from these collections. This functionality is especially useful for working with JSON documents and allows for complex queries across different sets of data.

What is a typical use case where you might choose MongoDB over Postgres?

MongoDB is often the better choice when dealing with JSON-like documents that require flexible schemas, especially for applications like e-commerce websites where different products have different attributes. MongoDB also scales horizontally more easily and performs better when dealing with high loads of write-heavy or query-heavy workloads.

In the performance benchmark, how did Postgres and MongoDB compare in terms of queries per second?

In the benchmark, MongoDB outperformed Postgres, handling up to 13,000 queries per second, while Postgres began to struggle around 5,000 queries per second, experiencing significant latency spikes and eventually falling behind MongoDB as query load increased.

What was the primary reason for the performance drop in Postgres during the benchmark?

The primary reason for the performance drop in Postgres was its disk write operations. As the query load increased, Postgres' reliance on disk storage for data persistence led to latency spikes and eventually dropped requests, whereas MongoDB handled disk I/O more efficiently.

Why is it suggested to move to MongoDB if working primarily with JSON documents?

If you plan to work primarily with JSON documents, MongoDB is the recommended choice because it is specifically designed to handle such data structures. MongoDB supports efficient querying, aggregation, and indexing of JSON data, and offers better horizontal scalability than Postgres when dealing with large-scale applications.