Choosing the Right Database Architecture for Scale

PostgreSQL vs MongoDB vs DynamoDB — and when to use each. An architect's no-nonsense guide to selecting your data layer.

By Arsalan Khalid, Lead Engineer. Published 2024-07-22. Architecture.

The database choice is the most consequential and hardest-to-reverse architectural decision you'll make. Pick the wrong database and you'll spend the next three years fighting it. This guide is the distillation of what we've learned from 50+ production database deployments at RoboSoft Works.

We'll cover PostgreSQL, MongoDB, and DynamoDB — the three most common choices we encounter — with a clear decision framework for choosing between them.

PostgreSQL: The Default Right Answer

For most applications, PostgreSQL is the correct choice. It's not a fallback or a safe choice — it's genuinely exceptional. Modern PostgreSQL (v15+) can handle:

  • Transactional workloads with ACID guarantees (financial systems, e-commerce, user accounts)
  • JSON/JSONB columns for semi-structured data — blending relational and document models
  • Full-text search with trigram indexes — often eliminating the need for Elasticsearch for moderate scale
  • Time-series data with TimescaleDB extension
  • Geospatial queries with PostGIS — handling geographic data without a specialized database
  • Horizontal read scaling with read replicas (Citus for write scaling)

The engineering talent market strongly favors PostgreSQL — every senior engineer knows it. Managed options (Supabase, Neon, AWS RDS, Railway) make operations straightforward. Use Postgres unless you have a specific, compelling reason not to.

When PostgreSQL Struggles

Postgres isn't perfect. It struggles with: highly variable schema requirements that change multiple times per week, extremely high write throughput (millions of writes/second) without connection pooling and careful query optimization, and multi-region active-active writes (it's primarily a single-primary model, though logical replication helps).

MongoDB: Document Flexibility at a Cost

MongoDB's core value proposition is schema flexibility — you can store any shape of JSON document without defining a schema upfront. This sounds appealing, but in practice it's a double-edged sword.

When MongoDB is genuinely the right choice:

  • Content management systems with truly heterogeneous content types (a blog post, a product, and an event all have radically different shapes)
  • Rapid prototyping where the schema will change frequently in early development
  • Hierarchical data that would require multiple joins in a relational model
  • Teams with strong MongoDB expertise and little Postgres experience (expertise often matters more than the 'objectively better' tool)

What we've seen go wrong with MongoDB: teams use it because 'JSON is flexible,' then spend years trying to enforce schema integrity at the application layer. MongoDB does have schema validation, but most teams don't use it from the start. The result is production databases with wildly inconsistent document shapes that are difficult to query reliably.

DynamoDB: Hyper-Scale with Painful Trade-offs

DynamoDB is a remarkable piece of infrastructure. It scales to literally any throughput, delivers consistent single-digit millisecond latency, and requires zero database administration. But it comes with profound limitations that make it the wrong choice for most applications.

DynamoDB is the right choice when:

  • You genuinely need massive scale (tens of thousands of reads/writes per second) and have optimized for it
  • Your access patterns are known and fixed — DynamoDB requires you to design your schema around your queries, not the other way around
  • You're deeply in the AWS ecosystem and are comfortable with vendor lock-in
  • The workload is simple key-value or single-table design patterns

DynamoDB is brilliant for the specific problems it solves. It's catastrophic when misapplied. The single most common mistake we see: teams choosing DynamoDB because it 'scales infinitely,' then struggling with query limitations that make simple features hard to build.

The Decision Framework

  1. Start with PostgreSQL — it handles 90% of use cases excellently and is operationally well understood
  2. Choose MongoDB if your data is genuinely document-centric with frequent structural variation AND you don't need cross-document transactions
  3. Choose DynamoDB only if: you've proven you need >10k writes/second, your access patterns are well-defined and unlikely to change, and you're committed to AWS long-term
  4. Consider a polyglot architecture for complex systems — Postgres for transactions, Redis for caching, Elasticsearch for search, DynamoDB for high-throughput event logs

The Hidden Factor: Team Expertise

The best database is often the one your team knows best. A PostgreSQL system built by engineers with deep Postgres expertise will outperform a DynamoDB system built by engineers unfamiliar with single-table design. Factor expertise and learning curve into your decision — especially if you're hiring in a competitive talent market where Postgres expertise is far more common than DynamoDB expertise.

Database architecture is one of the few places where the default choice (PostgreSQL) is usually the right choice. Resist the urge to innovate here unless you have concrete requirements that Postgres genuinely can't meet.