Choosing the Right Database: MongoDB vs PostgreSQL for Your Project (Developer Guide)

Key Concepts

• Cloud Native Databases
• Kubernetes Operators (MongoDB Operator, Percona MongoDB Operator, Crunchy Data PGO, Stakater, Zalando PostgreSQL operator, Neon)
• Database Management Tools (MongoDB Compass, Robot 3T, PGAdmin, DBeaver)
• Resource Consumption and Performance
• SQL vs. NoSQL Feature Sets (JSONB data type, full-text search)
• Relational Data Storage (joins, data integrity)
• Documentation and Business Model
• PostgreSQL Ecosystem (Timescale, YugabyteDB, FireboltDB, AgensGraph, Apache AGE, Greenplum, CrateDB, ZomboDB, PostGIS, PostgreSQL ML)

Cloud Native Considerations

• Definition: Cloud native refers to applications designed to leverage cloud computing models, emphasizing scalability, resilience, and portability across different cloud providers or on-premise environments.
• Kubernetes Operators: Both MongoDB and PostgreSQL offer Kubernetes operators to automate database management tasks within Kubernetes clusters.
• MongoDB Operator:
  • Official Community version operator lacks Enterprise features like backups behind a paywall.
  • Features: Manages replica sets, rolling updates, TLS, user creation, Prometheus metrics export.
  • Installation: Supports Helm and Customize.
  • Simple resource definition in Kubernetes for deploying replica sets.
• Percona MongoDB Operator: An alternative operator provided by Percona.
• PostgreSQL Operators:
  • Multiple options due to PostgreSQL's open-source nature.
  • Crunchy Data PGO: Manages high-availability clusters, backups to various storage locations (S3, Google Cloud Storage, Azure), disaster recovery, user management, Prometheus metrics, connection pooler, PGAdmin deployment.
  • Stakater: Offers a UI for managing PostgreSQL clusters.
  • Zalando PostgreSQL operator: Open-sourced by an e-commerce company.
  • Neon: Serverless PostgreSQL with auto-scaling, branching, and "bottomless storage" using S3.
• Conclusion: PostgreSQL has a slight edge due to the number of options and innovation in its ecosystem.

Database Management Tools

• MongoDB:
  • MongoDB Compass: Official tool with queries tab, database overview (collections, indexes, size), performance tab, document viewing, query writing, aggregation pipelines, schema analytics, explain plan, index management, validation rules.
  • Robot 3T: A third-party alternative.
• PostgreSQL:
  • PGAdmin: Web-based tool, easily deployed with PGO, offers server statistics, database management, and SQL query execution.
  • DBeaver: Supports MongoDB (paid version) but excels with PostgreSQL (free version), providing advanced features for managing functions, indexes, views, and materialized views.
• Conclusion: Draw. MongoDB Compass is now feature-complete. PGAdmin offers a web-based solution.

Resource Consumption and Basic Performance Tests

• Scenario: Small business storing newsletter subscription data.
• Test Setup: Limited resources (0.2 CPU, 250MB memory). Ingest subscriber data and query indexed field.
• MongoDB:
  • 100,000 records: Insert 2.5 seconds, Find 50 milliseconds.
  • 1,000,000 records: Insert 26 seconds, Find 400 milliseconds.
  • 10,000,000 records: Insert 7 minutes, Find 5.5 seconds.
• PostgreSQL:
  • 100,000 records: Insert 1.3 seconds, Find 10 milliseconds.
  • 1,000,000 records: Insert 14 seconds, Find 72 milliseconds.
  • 10,000,000 records: Insert 2.5 minutes, Find 1.3 seconds.
• Conclusion: PostgreSQL is better optimized and more mature.

SQL vs. NoSQL Feature Sets

• Bridging the Gap: Both databases have incorporated features from the other paradigm.
• MongoDB: Implemented validation rules, transactions, and joins (using the lookup operator).
• PostgreSQL: Added JSON as a data type (JSONB), allowing deep querying and indexing of JSON documents.
• Scenario: CMS application storing articles as JSON documents.
• Schema: Article with embedded author document and an array of sections.
• Test: Find articles by author's first name and full-text search.
• MongoDB: Find by author name < 1 millisecond, full-text search 15 milliseconds.
• PostgreSQL: Find by author name < 1 millisecond, full-text search a couple of milliseconds. Supports searching by Lexemes.
• Conclusion: PostgreSQL wins. It offers full JSON capabilities, amazing full-text search performance, and advanced features.

Relational Data Storage

• Scenario: Web application for booking appointments and surgeries, requiring data integrity and consistency.
• Problem: MongoDB's relational capabilities are limited.
• Test: Joining multiple data sets (hospitals, rooms, staff, patients) to get surgery data.
• MongoDB: Join query takes around 50 seconds.
• PostgreSQL: Equivalent query takes around 5 seconds.
• Conclusion: PostgreSQL is significantly faster for relational data operations. "Always choose the right tool for the job."

Documentation and Business Model

• Documentation: Both have good documentation, but PostgreSQL's is more extensive.
• Business Model: MongoDB's open-source part seems designed to upsell the Enterprise Edition. PostgreSQL is truly open-source, fostering a massive ecosystem.
• PostgreSQL Ecosystem: Includes Timescale (time series), YugabyteDB (cloud native), FireboltDB (MongoDB alternative), AgensGraph (graph database), Apache AGE (graph extension), Greenplum (parallel analytics), CrateDB (storage engine), ZomboDB (Elasticsearch integration), PostGIS (geographic objects), PostgreSQL ML (machine learning).
• Conclusion: PostgreSQL's ecosystem is vast and diverse.

Conclusion

• Expected Outcomes: PostgreSQL leads in ecosystem, documentation, and business model. PostgreSQL is good at scaling.
• Unexpected Outcomes: PostgreSQL wins on performance metrics, even in NoSQL use cases. MongoDB's relational features are decent.
• Final Thoughts: Both have use cases. For most applications, PostgreSQL is the preferred choice due to its performance and capabilities with unstructured data.