Choosing PostgreSQL Over MongoDB for New Services

databasepostgresqlarchitecture

Context

We were designing a new service for managing customer subscriptions with complex billing logic. The team debated between PostgreSQL and MongoDB, which we had used for other services.

Decision

Use PostgreSQL for the subscription service and establish it as the default for new services with relational data

Alternatives Considered

Use MongoDB (our existing default)

Pros
  • Team already experienced with MongoDB
  • Flexible schema for evolving requirements
  • Good for document-oriented data
Cons
  • Transactions across collections are complex
  • Joins require application-level logic
  • Schema flexibility can lead to data inconsistencies

Use PostgreSQL

Pros
  • ACID transactions for billing accuracy
  • Powerful query capabilities with JOINs
  • Strong data integrity with constraints
  • JSONB for flexible fields when needed
Cons
  • Team needs to learn PostgreSQL
  • Schema migrations require more planning
  • Different operational model than MongoDB

Reasoning

Subscription billing requires strong consistency guarantees that are natural in PostgreSQL but require careful handling in MongoDB. The relational model fits our data (customers, plans, invoices, payments) better than documents. PostgreSQL's JSONB gives us schema flexibility where needed without sacrificing relational capabilities.

Why This Decision Mattered

Billing systems have zero tolerance for inconsistency. A customer being charged twice or not at all is unacceptable. We needed:

  • Atomic transactions across multiple tables
  • Strong referential integrity
  • Complex queries for reporting
  • Audit trail for compliance

The MongoDB Experience

We had used MongoDB for 3 years. It worked well for:

  • User profiles (document-oriented)
  • Product catalogs (flexible schema)
  • Activity logs (append-heavy)

But we struggled with:

  • Multi-document transactions (added in 4.0 but still awkward)
  • Reporting queries (aggregation pipeline is powerful but complex)
  • Data consistency (no foreign keys meant orphaned records)

PostgreSQL Evaluation

We ran a 2-week spike to evaluate PostgreSQL:

  1. Modeled the subscription domain relationally
  2. Implemented key billing operations
  3. Tested transaction behavior under failure
  4. Evaluated query performance for reporting

The results were compelling. Complex billing queries that took 50+ lines of aggregation pipeline became 10-line SQL queries.

Migration Path

We didn’t migrate existing MongoDB services. Instead:

  • New services with relational data use PostgreSQL
  • Existing MongoDB services remain (if working well)
  • Shared data accessed via APIs, not direct DB access

Team Learning

We invested in PostgreSQL training:

  • Internal workshops on SQL and PostgreSQL features
  • Pair programming during initial development
  • Documentation of patterns and best practices

After 6 months, the team is comfortable with both databases and can choose the right tool for each use case.

All decisions