Context

Teams at companies like Notion often choose between PostgreSQL and a NoSQL store when designing a new product feature. Interviewers ask this to test whether you understand data modeling trade-offs, not just tool names.

Core question

Explain the trade-offs between using SQL and NoSQL databases. In your answer, discuss:

1. How their data models differ
2. When each is a better fit
3. Trade-offs around consistency, scalability, and query patterns
4. How schema design affects development speed and long-term maintenance

Scope guidance

Keep your answer practical and balanced. You do not need to argue that one is always better. A strong answer should compare common relational systems such as PostgreSQL with common NoSQL patterns such as document, key-value, or wide-column stores, and explain how workload requirements drive the choice.

Structured schema vs flexible schema

SQL databases typically enforce a predefined schema with typed columns, constraints, and relationships. NoSQL databases often allow more flexible or evolving record structures, which can speed up early development but may shift validation responsibility into application code.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE
);

Joins and relational querying

SQL databases are strong when data is highly relational and queries need joins, aggregations, and transactional consistency. Many NoSQL systems optimize for denormalized access patterns instead, which can improve read performance for specific workloads but may duplicate data.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Consistency and transactions

Relational databases usually provide strong ACID guarantees, which matter for financial records, inventory, and other correctness-sensitive workloads. NoSQL systems may relax consistency or transaction scope to improve availability and horizontal scalability, though capabilities vary by product.

BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
COMMIT;

Scaling patterns

SQL systems have historically scaled vertically more often, though PostgreSQL and related ecosystems can also scale horizontally with partitioning, replication, and distributed extensions. NoSQL databases are often designed around horizontal scaling and high write throughput across many nodes.

Modeling for access patterns

With SQL, teams often normalize data first and rely on query power to answer many questions later. With NoSQL, teams often model data around known access patterns, which can be efficient for production reads but less flexible for ad hoc analytics.