Relational (SQL)

Rows in typed tables with declarative joins, ACID transactions, and a schema enforced by the engine.

What it is

Relational databases store data as rows in tables with a fixed schema, related through foreign keys. You query with SQL (Structured Query Language) — a declarative language where the query planner decides how to execute joins, filters, and aggregations using statistics and indexes. Relational engines (Postgres, MySQL, SQL Server, Oracle) provide full ACID (Atomicity, Consistency, Isolation, Durability) transactions, mature query optimizers, and decades of operational tooling. The data model is normalized by default to eliminate redundancy.

Why senior interviewers ask

'Why SQL?' is the single most common database interview question. The wrong answer is 'because I know it'. The right answer enumerates: ACID, joins, mature tooling, query flexibility for unknown access patterns.

Key points

Schema-on-write: the engine rejects rows that violate types, NULLs, or constraints.
Joins are first-class and cheap on indexed columns; the planner picks hash/merge/nested-loop.
Full ACID transactions across multiple rows and tables.
Secondary indexes: B-tree by default, plus partial, expression, covering, GIN/GIST in Postgres.
Vertical scaling is mature (read replicas, partitioning); horizontal scaling requires app-level sharding or Citus/Vitess.
Replication lag on read replicas is a constant operational concern.
Query planner can produce wildly different plans on similar queries; EXPLAIN is mandatory reading.

Pros

ACID across multi-row writes with no app-side gymnastics.
SQL is the most expressive query language; ad-hoc analytics are trivial.
Decades of tooling: ORMs (Object-Relational Mappers), migrations, dashboards, query analyzers, hosted services everywhere.
Schema enforcement catches bugs at write time instead of debugging garbage data later.
Mature replication and backup story; PITR (point-in-time recovery) is standard.

Cons

Horizontal write scaling is painful — sharding is bolted on, not native.
Schema migrations on large tables require care (online DDL tools like gh-ost, pt-osc).
Connection-per-query model exhausts connections fast under high concurrency.
JSON columns work but aren't as ergonomic as native document stores.
Cross-region writes are slow and require careful failover planning.

When to choose

Access patterns are unknown or will evolve — SQL's flexibility pays off.
Multi-entity transactions are required (orders + inventory + payments).
Reporting and analytics matter — joins, GROUP BY, window functions.
Strong constraints (FKs, uniqueness) are part of the domain (finance, healthcare).
Team is small and you don't want to operate a polyglot persistence stack.

When to avoid

Write throughput exceeds what a single primary can handle (~tens of thousands of writes/sec) and you can't shard cleanly.
Document-shaped data with deeply nested structures and no joins needed.
Massive timeseries or event logs where Cassandra/ClickHouse fits the workload better.

Real systems

PostgreSQL — Default choice for new services — MVCC, rich indexes, JSONB, extensions (PostGIS, pgvector).

MySQL / MariaDB — InnoDB engine; dominant in legacy LAMP stacks and AWS Aurora.

Microsoft SQL Server — Enterprise workloads; tight integration with .NET and Windows ecosystems.

CockroachDB / Spanner — Distributed SQL — Postgres/MySQL wire protocol over a sharded, Raft-replicated storage layer.

Interview probe

'Why Postgres over DynamoDB for this?' Answer: name the access patterns. If you have ad-hoc joins, multi-row transactions, or evolving queries, SQL wins. If you have one or two fixed key-based access patterns at massive scale, DynamoDB wins. Don't say 'because I know it'.

All database concepts