This article evaluates the performance of MongoDB, PostgreSQL (two versions), and CockroachDB in handling data insertion and querying tasks. The comparison is based on how these databases manage workloads, emphasizing time taken for data insertion and query execution.
Reference GitHub Repository: For further insights into BSON and JSON performance, refer to the BSON-JSON Bakeoff repository.
Test Overview
Insertion Test:
- 10,000 documents with a 4,000-byte (4KB) payload were inserted into an indexed field.
- Two scenarios were tested:
- Payload stored in a single attribute.
- Payload distributed across 200 attributes.
Query Test:
- Queried 10,000 IDs from an indexed array.
Metrics Measured:
- Time taken for each operation.
- Total items retrieved during queries.
Results Summary
| Database | Insert Time (1 Attribute) | Insert Time (200 Attributes) | Query Time | Items Found |
|---|---|---|---|---|
| MongoDB | 2052ms | 1351ms | 10169ms | 99941 |
| PostgreSQL 16.3 | 16786ms | 17861ms | 29485ms | 99939 |
| PostgreSQL 11.2-YB | 14075ms | 14820ms | 15900ms | 99959 |
| CockroachDB | 37648ms | 42455ms | 155116ms | 99963 |
Database-Specific Insights
1. MongoDB
- Strengths:
- Fastest insertion times for both single-attribute and multi-attribute payloads.
- Efficient querying performance with the shortest query time across all databases.
- Weaknesses:
- Slight discrepancies in total items found, likely due to edge-case handling.
- Best Fit:
- Ideal for real-time analytics or IoT applications requiring high throughput and flexible schema designs.
2. PostgreSQL 16.3
- Strengths:
- Reliable performance for transactional workloads.
- Weaknesses:
- Slower insertion and query times compared to MongoDB.
- Best Fit:
- Suitable for enterprise applications requiring strong ACID compliance, such as financial or ERP systems.
3. PostgreSQL 11.2-YB (YugabyteDB Fork)
- Strengths:
- Improved performance compared to PostgreSQL 16.3.
- Balances traditional RDBMS features with better scalability.
- Weaknesses:
- Still slower than MongoDB for high-throughput tasks.
- Best Fit:
- Works well for distributed transactional workloads in multi-node environments.
4. CockroachDB
- Strengths:
- High reliability and consistency in distributed systems.
- Slightly higher total items found, reflecting robust data handling.
- Weaknesses:
- Significantly slower insertion and query times, making it less suitable for high-speed operations.
- Best Fit:
- Preferred for geo-distributed systems requiring fault tolerance and high availability.
Key Observations
Insertion Times:
- MongoDB demonstrates exceptional performance for both single-attribute and multi-attribute insertions.
- CockroachDB lags far behind, with insertion times nearly 18 times that of MongoDB for single-attribute payloads.
Query Times:
- MongoDB outperforms other databases in querying, completing the task in ~10 seconds.
- CockroachDB's querying is the slowest, taking over 2.5 minutes.
Items Found:
- Minor differences in total items found are observed, with CockroachDB slightly ahead.
Recommendations Based on Use Cases
| Use Case | Recommended Database | Reason |
|---|---|---|
| High Write/Read Throughput | MongoDB | Unmatched speed for insertions and queries. |
| Transactional Workloads | PostgreSQL 16.3 or 11.2-YB | Strong ACID compliance for transactional systems. |
| Scalable Distributed Transactions | PostgreSQL 11.2-YB | Combines ACID compliance with better scalability. |
| Geo-Distributed Systems | CockroachDB | High availability and fault tolerance. |
Conclusion
MongoDB is the clear winner for high-throughput, schema-flexible applications, while PostgreSQL variants are better suited for transactional and enterprise applications. CockroachDB excels in distributed systems with strong consistency needs.
Reference: For additional insights on BSON and JSON performance differences, visit the BSON-JSON Bakeoff repository.
