Relational vs document stores

Why it matters

"Should we use Postgres or MongoDB?" is one of the questions a DBA hears most. The honest answer is "it depends on the access patterns", and your job is to make that answer concrete. Picking the wrong store does not fail on day one; it fails a year later when the data has grown and the queries the business needs are slow or impossible.

The main families

Relational (PostgreSQL, MySQL, SQL Server, Oracle, SQLite). Data lives in tables with a fixed schema. Joins combine tables at query time, constraints enforce rules, and transactions are ACID across many rows and tables. Relational stores are the default choice for business records: orders, accounts, inventory, billing.

Document (MongoDB, Couchbase, Firestore). Data is stored as JSON-like documents. A document can nest related data, for example a blog post with its tags and comments inside it. Schemas are flexible: two documents in one collection can have different fields. Reads that fetch "one thing and everything inside it" are fast because no join is needed. Modern document stores also support indexes, multi-document transactions and schema validation, but cross-collection joins and reporting are less natural than in SQL.

Key-value (Redis, Amazon DynamoDB in its simplest use, Memcached). A key maps to a value. Extremely fast lookups by key, often in memory. Great for caches, sessions, rate-limit counters and queues. Poor for ad hoc questions ("which users signed up last week?") unless you design for them up front.

Wide-column (Apache Cassandra, ScyllaDB, HBase). Rows are grouped by a partition key and spread across many machines. Built for huge write volumes and predictable queries by key, such as time-series sensor readings. You design one table per query, and joins do not exist.

Graph (Neo4j, Amazon Neptune). Nodes and relationships are first-class. Good for questions about connections: "friends of friends who bought this", fraud rings, network dependencies.

The core idea: model for how you read

Relational design starts from the data (normalise it, then query it any way you like). Most NoSQL design starts from the queries (store data in the shape the application reads it). That is the trade-off:

Worked example: three workloads

1. A payments ledger. Money moves between accounts; every transfer must debit one row and credit another, or neither. Auditors run ad hoc queries. Choose relational: ACID transactions and constraints are the point.
2. A product catalogue where a laptop has RAM and screen size, a T-shirt has size and colour, and pages load one product with all its attributes. A document store fits well, and so does PostgreSQL with a JSONB column for the varying attributes plus normal columns for price and stock.
3. Login sessions and an API rate limiter that need sub-millisecond lookups and expire automatically. Use a key-value store such as Redis, with a time-to-live on each key.

Notice option 2: the line between families is blurry. PostgreSQL's JSONB type (with GIN indexes) and MySQL's JSON type give you documents inside a relational database. Many teams start with one relational database and add a specialised store only when a measured need appears.

Questions to ask before choosing

• What are the top queries, and do they look up by key or explore the data?
• Do several records have to change together atomically?
• How big will it get, and how fast does it grow? Does it need to be spread across regions?
• Who will run it at 3 a.m.? Operational familiarity matters as much as features.
• What do reporting and analytics need? A flexible store may still need a pipeline into a warehouse.

Common mistakes

• Choosing by hype. "NoSQL scales" is not a requirement. Most applications fit comfortably on one well-tuned relational server with replicas.
• "Schemaless" meaning "no schema". The schema still exists; it just lives in application code, where it is harder to see and enforce.
• Using a relational database like a key-value store (one table of key, json_blob) and losing its strengths.
• Forgetting consistency. Distributed stores often default to eventual consistency for reads; know what your product promises.

Quick recap

• Relational: fixed schema, joins, ACID, great for business records and reporting.
• Document: nested, flexible records read as a whole. Key-value: fast lookups and caches. Wide-column: huge, key-partitioned writes. Graph: relationships.
• Choose from the access patterns, consistency needs, scale and the team's operational skills, and remember that hybrids such as JSONB exist.

Try it

The Do tab has a short quiz that asks you to match workloads to stores.