Course: System Design — Zero to Hero
Level: Beginner → Advanced
Phase: Database Fundamentals
Status: Complete
In Chapter 09, we learned what databases are, how relational data is modeled, why indexes and transactions matter, and how SQL databases differ from other database families.
Now we will go deeper into SQL databases.
You will learn:
CREATE TABLEINSERTSELECTWHEREAND, OR, NOTIN, BETWEEN, LIKENULLORDER BYLIMIT and paginationUPDATEDELETEGROUP BYHAVINGJOINCOMMIT and ROLLBACKEXPLAINThe examples will use PostgreSQL-style SQL because it is widely used and gives us a strong foundation for system design.
Most concepts transfer to other relational databases, although exact syntax and behavior can differ.
A SQL database is a relational database that provides a structured way to store and query data, commonly using SQL — Structured Query Language.
Examples include:
A simplified architecture is:
Application
|
v
Database Driver / ORM
|
v
SQL Database
|
+---- Query Parser
+---- Query Planner
+---- Query Executor
+---- Buffer / Memory
+---- Storage
+---- Transaction SystemThe application sends a query.
The database:
receives query
↓
parses query
↓
plans execution
↓
executes operations
↓
returns resultOne of the most important ideas is that SQL is primarily declarative.
Consider:
SELECT name
FROM users
WHERE age >= 18;You tell the database:
Give me names of users whose age is at least 18.
You generally do not specify:
Open file
Read row 1
Check age
Read row 2
Check age
...The database determines an execution strategy.
This separation is powerful:
Application
|
| What data do I need?
v
SQL
|
v
Database optimizer
|
| How should I retrieve it?
v
Execution planA typical relational database can be visualized as:
Database
|
+-- Schema
|
+-- users
+-- products
+-- orders
+-- paymentsA table contains:
Columns
+
RowsExample:
users
id | name | email
---+---------+--------------------
1 | Anubhav | anubhav@example.com
2 | Rahul | rahul@example.com
3 | Priya | priya@example.comExample:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
age INTEGER,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);Let's understand it.
id#id BIGSERIAL PRIMARY KEYCreates an identifier.
name#name VARCHAR(100) NOT NULLA string with a maximum length of 100 characters.
email#email VARCHAR(255) NOT NULL UNIQUEThe email must:
age#age INTEGERAn integer.
created_at#created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMPStores creation time.
Common types include:
INTEGER
BIGINTUseful for counts and identifiers.
NUMERIC(12,2)Useful for exact decimal values such as monetary amounts.
For financial values, exact decimal/numeric types are generally preferred over floating-point types.
BOOLEANExample:
true
falseTEXT
VARCHAR(255)TIMESTAMP
TIMESTAMPTZThe exact choice depends on the application's time semantics and database conventions.
DATEPostgreSQL supports:
JSON
JSONBJSONB is commonly useful when you need JSON-shaped data with database-side operations and indexing.
To insert data:
INSERT INTO users (name, email, age)
VALUES ('Anubhav', 'anubhav@example.com', 28);Multiple rows:
INSERT INTO users (name, email, age)
VALUES
('Anubhav', 'anubhav@example.com', 28),
('Rahul', 'rahul@example.com', 30),
('Priya', 'priya@example.com', 26);Avoid relying on:
INSERT INTO users
VALUES (...);Prefer:
INSERT INTO users (name, email, age)
VALUES (...);Why?
Because the table schema may change.
Explicit columns make the query:
The most fundamental SQL operation is:
SELECTExample:
SELECT *
FROM users;This returns all columns.
But production applications often should select only what they need.
Prefer:
SELECT id, name, email
FROM users;instead of:
SELECT *
FROM users;when the application does not need every field.
SELECT * Can Be a Problem#Suppose a table contains:
id
name
email
profile_photo
large_metadata
created_at
updated_atIf you need only:
id
namethen:
SELECT *may retrieve unnecessary data.
Potential consequences:
It is not that SELECT * is always forbidden.
The important principle is:
Retrieve only what the application actually needs.
WHERE filters rows.
SELECT id, name
FROM users
WHERE age >= 18;Conceptually:
All users
|
v
Filter age >= 18
|
v
Matching usersCommon operators:
=
<>
!=
>
<
>=
<=Examples:
WHERE age = 25WHERE age > 18WHERE age <= 30SELECT *
FROM users
WHERE age >= 18
AND age <= 30;Both conditions must be true.
SELECT *
FROM users
WHERE city = 'Delhi'
OR city = 'Mumbai';Either condition may be true.
Use parentheses when combining complicated conditions.
WHERE
(city = 'Delhi' OR city = 'Mumbai')
AND age >= 18;SELECT *
FROM users
WHERE NOT is_blocked;This selects rows where is_blocked is not true.
Be careful with NULL, because SQL's three-valued logic means:
TRUE
FALSE
UNKNOWNnot simply true/false.
Instead of:
WHERE city = 'Delhi'
OR city = 'Mumbai'
OR city = 'Bangalore'you can write:
WHERE city IN ('Delhi', 'Mumbai', 'Bangalore');This is usually easier to read.
Example:
SELECT *
FROM products
WHERE price BETWEEN 100 AND 500;BETWEEN is inclusive at both boundaries in typical SQL semantics:
100 <= price <= 500Always understand the exact semantics when working with dates and timestamps.
Pattern matching:
SELECT *
FROM users
WHERE name LIKE 'Anu%';This can match values beginning with:
AnuFor example:
Anubhav
Anurag
AnujA leading wildcard:
WHERE name LIKE '%hav';can have different performance characteristics and may prevent efficient use of an ordinary B-tree index depending on the database and pattern.
NULL means:
Missing/unknown/not present value.
It is not:
0and not:
''and not necessarily:
falseIncorrect:
WHERE email = NULL;Correct:
WHERE email IS NULL;And:
WHERE email IS NOT NULL;This is a very common interview and practical mistake.
SQL expressions can evaluate to:
TRUE
FALSE
UNKNOWNFor example:
NULL = 5does not evaluate to ordinary FALSE; it produces UNKNOWN.
This is why NULL requires special handling.
Sort results:
SELECT id, name, created_at
FROM users
ORDER BY created_at DESC;Ascending:
ORDER BY created_at ASC;Descending:
ORDER BY created_at DESC;Return only a limited number of rows:
SELECT *
FROM users
ORDER BY created_at DESC
LIMIT 20;This is useful for:
But LIMIT alone does not guarantee stable pagination.
A basic pagination query:
SELECT *
FROM products
ORDER BY id
LIMIT 20
OFFSET 40;This means:
Skip 40
Return next 20Page 1:
LIMIT 20 OFFSET 0Page 2:
LIMIT 20 OFFSET 20Page 3:
LIMIT 20 OFFSET 40For very large offsets:
OFFSET 1000000the database may need to process/skip many rows.
This can become expensive.
Another problem is changing data.
If new rows are inserted between requests, users may see:
depending on the ordering and transaction behavior.
For large datasets, another approach is often better.
Suppose we order by:
ORDER BY idInstead of:
OFFSET 1000000we can use the last seen ID.
Example:
SELECT *
FROM products
WHERE id > 1000000
ORDER BY id
LIMIT 20;This is often called keyset pagination or cursor-based pagination.
Conceptually:
Page 1
IDs 1-20
|
v
last_id = 20
Page 2
WHERE id > 20This can scale much better for suitable ordered access patterns.
Update data:
UPDATE users
SET name = 'Anubhav Chaudhary'
WHERE id = 1;Important:
Always think carefully about the
WHEREclause.
This:
UPDATE users
SET name = 'Unknown';may update every row.
Example:
DELETE FROM users
WHERE id = 1;Again, without a suitable WHERE:
DELETE FROM users;can delete every row.
Production systems often use safeguards around destructive operations.
Instead of physically deleting a row:
DELETE FROM users
WHERE id = 1;some applications use:
deleted_atExample:
UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = 1;Then normal queries use:
WHERE deleted_at IS NULLAdvantages can include:
Trade-offs include:
Soft delete should be an explicit design decision, not a universal rule.
SQL provides aggregate functions.
Common examples:
COUNT()
SUM()
AVG()
MIN()
MAX()Example:
SELECT COUNT(*)
FROM users;Count users:
SELECT COUNT(*)
FROM users;Count users older than 18:
SELECT COUNT(*)
FROM users
WHERE age >= 18;Suppose:
orders
--------
amountThen:
SELECT SUM(amount)
FROM orders;can calculate the total.
SELECT AVG(price)
FROM products;Calculates the average price according to SQL's numeric semantics.
Suppose we want users grouped by city:
SELECT city, COUNT(*)
FROM users
GROUP BY city;Result:
city count
---------- -----
Delhi 1000
Mumbai 800
Bangalore 1200Conceptually:
Rows
|
v
Group by city
|
v
Aggregate each groupWHERE filters rows before grouping.
HAVING filters groups after aggregation.
Example:
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 1000;Meaning:
Show cities whose user count is greater than 1,000.
Remember:
WHERE
-> filters rows
GROUP BY
-> creates groups
HAVING
-> filters groupsExample:
SELECT city, COUNT(*)
FROM users
WHERE age >= 18
GROUP BY city
HAVING COUNT(*) > 100;Logical flow:
FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
ORDER BY
↓
LIMITThis is a useful conceptual model.
JOINs are one of the most important SQL concepts.
Suppose:
users
-----
id
nameand:
orders
------
id
user_id
amountWe want:
User name + order amountWe can join them.
SELECT
users.name,
orders.amount
FROM users
INNER JOIN orders
ON users.id = orders.user_id;An inner join returns matching rows from both sides.
Conceptually:
users orders
| |
+------JOIN-----+
|
v
matching dataSELECT
users.name,
orders.amount
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;A left join keeps rows from the left table even when there is no matching row on the right.
Example:
Users:
Anubhav
Rahul
Priya
Orders:
Anubhav -> ₹500
Priya -> ₹800A left join can still return Rahul with NULL order information.
| JOIN | Meaning |
|---|---|
| INNER JOIN | Return matching rows |
| LEFT JOIN | Keep all left-side rows |
| RIGHT JOIN | Keep all right-side rows |
| FULL OUTER JOIN | Keep rows from both sides where supported |
In practice, INNER JOIN and LEFT JOIN are especially common.
Find all orders for user 123:
SELECT
o.id,
o.amount,
o.created_at
FROM orders o
WHERE o.user_id = 123
ORDER BY o.created_at DESC;Now include user information:
SELECT
u.name,
o.id,
o.amount,
o.created_at
FROM users u
JOIN orders o
ON u.id = o.user_id
WHERE u.id = 123
ORDER BY o.created_at DESC;Instead of:
users.name
orders.amount
orders.created_atwe can use:
u.name
o.amount
o.created_atExample:
SELECT
u.name,
o.amount
FROM users AS u
JOIN orders AS o
ON u.id = o.user_id;Aliases make complex SQL much easier to read.
Suppose:
students
courses
student_coursesTo find courses for student 10:
SELECT c.id, c.name
FROM courses c
JOIN student_courses sc
ON c.id = sc.course_id
WHERE sc.student_id = 10;The junction table connects the two entities.
A table can reference itself.
Example:
employees
id
name
manager_idTo find employees and their managers:
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.id;This is a self join.
JOINs are powerful, but at scale we should understand:
For example:
orders.user_idshould often have an appropriate access path if queries frequently join/filter by it.
A join between huge tables can be expensive if the query is poorly designed.
A subquery is a query inside another query.
Example:
SELECT *
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);Meaning:
Find products whose price is above the average.
Suppose we want users who have at least one order:
SELECT *
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);EXISTS is often useful when we care about whether a matching row exists rather than retrieving the matching rows themselves.
Both can express certain membership queries.
Example:
WHERE user_id IN (...)or:
WHERE EXISTS (...)The best choice depends on:
Do not memorize:
EXISTS is always faster.
That is not a reliable rule.
Use EXPLAIN and measurements.
A Common Table Expression is written with:
WITHExample:
WITH high_value_orders AS (
SELECT *
FROM orders
WHERE amount > 10000
)
SELECT *
FROM high_value_orders;CTEs can make complex queries easier to structure.
Window functions are extremely useful for analytics.
Example:
SELECT
user_id,
amount,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY created_at DESC
) AS order_number
FROM orders;Unlike GROUP BY, window functions can calculate information across related rows while retaining individual rows.
Common window functions:
ROW_NUMBER()
RANK()
DENSE_RANK()
LAG()
LEAD()
SUM() OVER (...)
AVG() OVER (...)These become especially useful for reporting and analytical workloads.
SQL databases provide mechanisms for protecting data integrity.
Example:
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC(12,2) CHECK (price >= 0),
sku TEXT UNIQUE
);Here:
PRIMARY KEY
NOT NULL
CHECK
UNIQUEall enforce rules.
Example:
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
amount NUMERIC(12,2) NOT NULL,
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
);This prevents an order from referencing a nonexistent user, subject to the database's constraint behavior.
Foreign keys can define behavior when referenced rows are changed.
Examples include:
CASCADE
RESTRICT
SET NULLExample:
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADEThis can cause related rows to be deleted automatically.
Be careful.
Cascading deletes can be useful but can also produce unexpectedly large operations.
A basic transaction:
BEGIN;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;
COMMIT;If something fails:
ROLLBACK;The transaction defines a boundary around the operations.
Consider an order workflow:
Create order
Reduce inventory
Create payment recordIf these operations have a strict requirement to succeed together, we need to think carefully about transaction boundaries.
However, a transaction should not automatically span an entire distributed workflow.
For example:
Order Service
|
v
Payment Service
|
v
Notification ServiceA single database transaction may not safely or practically cover all of these independent services.
Later chapters will introduce distributed transactions and event-driven patterns.
Common SQL isolation levels:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLEThey represent different trade-offs between:
Concurrency
vs
Isolation guaranteesExact semantics differ between database engines.
For example, PostgreSQL's implementation and defaults should be understood independently from another database's behavior.
A simplified mental model:
A transaction sees data committed before each statement begins, plus its own changes.
This can still allow certain values to change between statements within the same transaction.
Provides stronger repeatability semantics for reads within a transaction.
The exact behavior depends on the database implementation.
The key idea:
A transaction gets a more stable view of data than under weaker isolation.
Serializable isolation aims to provide behavior equivalent to transactions executing serially, although the database may implement this using concurrency-control techniques rather than literally running one transaction at a time.
This can provide stronger correctness guarantees but may reduce concurrency or cause transactions to fail/retry.
Think:
Higher isolation
|
+--> stronger guarantees
|
+--> potentially more contention/retries
|
+--> potentially lower concurrencyThe right isolation level depends on the application's correctness requirements.
When you send:
SELECT *
FROM users
WHERE email = 'anubhav@example.com';the database does not simply "run the text."
A simplified pipeline is:
SQL text
|
v
Parser
|
v
Parsed representation
|
v
Query planner / optimizer
|
v
Execution plan
|
v
Executor
|
v
ResultThe optimizer considers possible execution strategies.
For example:
Option A:
Sequential scan
Option B:
Index scan
Option C:
Bitmap/index-based strategyThe optimizer estimates costs and chooses a plan.
This is why:
Having an index does not guarantee the database will use it.
A sequential scan reads rows from the table in sequence.
Conceptually:
Row 1
Row 2
Row 3
...
Row NThis may be appropriate when:
A sequential scan is not automatically bad.
An index scan uses an index to locate relevant rows.
Conceptually:
Query
|
v
Index
|
v
Relevant row locations
|
v
Table rowsThis can be efficient for selective queries.
Use:
EXPLAIN
SELECT *
FROM users
WHERE email = 'anubhav@example.com';This asks the database to show the planned execution strategy.
In PostgreSQL:
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'anubhav@example.com';This executes the query and provides actual execution information.
It can help compare:
Estimated rows
vs
Actual rows
Estimated cost
vs
Actual execution behaviorUse caution with EXPLAIN ANALYZE on queries that modify data, because it executes them unless wrapped appropriately or otherwise handled.
Suppose a query is slow.
Do not immediately say:
Add an index.
First investigate.
Possible causes:
Bad index
Missing index
Wrong index order
Large scan
Poor join strategy
Bad cardinality estimate
Outdated statistics
Too many rows returned
Lock contention
Disk I/O
CPU pressureEXPLAIN helps turn guesswork into evidence.
A common index:
CREATE INDEX idx_users_email
ON users(email);Now queries filtering by email may have an efficient access path.
A uniqueness requirement can be represented using:
CREATE UNIQUE INDEX idx_users_email_unique
ON users(email);But when the requirement is a data-integrity constraint, a UNIQUE constraint is often the clearer schema-level expression:
ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email);The database may implement the uniqueness enforcement using an index.
Suppose the application frequently queries:
SELECT *
FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;A candidate index could be:
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);This aligns the index with:
filter -> user_id
sort -> created_atThe exact usefulness should be validated with the query plan.
For an index:
(user_id, created_at)the first indexed column is particularly important.
Queries involving:
user_idor:
user_id + created_atmay benefit naturally.
A query filtering only on:
created_atmay not get the same benefit from this index.
This is a useful mental model, although exact optimizer behavior depends on the database engine and query.
Suppose:
SELECT user_id, created_at
FROM orders
WHERE user_id = 123
ORDER BY created_at DESC;If the index contains all information required for the query, the database may be able to answer more of the query from the index itself.
This is broadly called a covering-index optimization.
PostgreSQL also provides mechanisms such as included columns:
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC)
INCLUDE (amount, status);This is an advanced optimization and should be introduced only when measurements justify it.
Every index has costs.
Index
|
+--> storage
+--> memory/cache usage
+--> insert overhead
+--> update overhead
+--> delete overhead
+--> maintenanceSuppose:
Table has 15 indexesEvery insert may need to maintain many structures.
Therefore:
Index based on actual access patterns.
Imagine:
100,000 writes/secand:
20 indexesIndex maintenance can become significant.
Sometimes removing an unnecessary index can improve write performance.
This is why database design is a trade-off:
Read performance
vs
Write costA common application mistake:
1 query -> fetch users
Then:
1 query per user -> fetch ordersFor 1,000 users:
1 + 1,000 = 1,001 queriesThis is the N+1 problem.
A join or batch query may reduce this substantially.
Example:
SELECT
u.id,
u.name,
o.id AS order_id,
o.amount
FROM users u
LEFT JOIN orders o
ON o.user_id = u.id;The correct solution depends on result size and application requirements, but blindly issuing thousands of database round trips is often a problem.
Never construct SQL by concatenating untrusted user input.
Dangerous pattern:
const query =
"SELECT * FROM users WHERE email = '" +
email +
"'";If email is attacker-controlled, the query may be manipulated.
Use parameterized queries.
Example conceptually:
SELECT *
FROM users
WHERE email = $1;with:
$1 = user-provided emailThe database driver handles parameter binding.
Applications often use ORMs such as:
Prisma
Hibernate
Entity Framework
Sequelize
TypeORMORMs can improve:
But developers still need SQL knowledge.
An ORM can generate inefficient queries.
Therefore:
An ORM does not remove the need to understand databases.
Conceptually:
const users = await prisma.user.findMany({
where: {
age: {
gte: 18
}
}
});The ORM may generate SQL similar to:
SELECT ...
FROM users
WHERE age >= 18;When performance becomes a problem, you need to understand what SQL is actually being executed.
Modern ORMs usually support transactions.
Conceptually:
await db.transaction(async (tx) => {
// operation 1
// operation 2
// operation 3
});The important system-design question remains:
Which operations belong in the same transaction?
Not:
Does the ORM have a transaction API?
When a SQL query is slow:
Reproduce the query.
Measure latency.
Run:
EXPLAINor the database's equivalent.
Check:
Scan type
Rows estimated
Rows actual
Join strategy
Sort
Filter
Index usageCheck infrastructure:
CPU
Memory
Disk I/O
Connections
LocksOptimize.
Potential actions:
Add/change index
Rewrite query
Reduce returned columns
Reduce result set
Change pagination
Batch requests
Fix N+1
Denormalize where justifiedMeasure again.
Suppose an e-commerce application needs:
GET /products?category=phones&limit=20A query might be:
SELECT id, name, price
FROM products
WHERE category_id = 10
AND is_active = true
ORDER BY created_at DESC
LIMIT 20;Potential index:
(category_id, is_active, created_at)But don't blindly create it.
Test the actual workload and execution plan.
API:
GET /users/123/ordersQuery:
SELECT id, status, amount, created_at
FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;A candidate index:
(user_id, created_at DESC)This is a classic system-design access pattern.
Suppose:
events
------
id
user_id
event_type
created_at
payloadQuery:
SELECT id, event_type, created_at
FROM events
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 100;The index should be designed around:
user_id
created_atrather than indexing every column.
SQL knowledge becomes system-design knowledge when you connect queries to system behavior.
For example:
API endpoint
|
v
SQL query
|
v
Index
|
v
Database CPU / I/O
|
v
LatencyA bad query can become a system-wide bottleneck.
Suppose:
10,000 requests/secand every request executes:
3 SQL queriesThen:
10,000 × 3
=
30,000 SQL queries/secIf each request executes 20 queries:
10,000 × 20
=
200,000 SQL queries/secThis is why query count matters.
Reducing unnecessary round trips can be more important than micro-optimizing individual lines of application code.
Suppose:
Request rate = 1,000 requests/sec
Average database time per request = 20 msA rough concurrency relationship comes from Little's Law:
Concurrency ≈ Throughput × LatencySo:
1,000 × 0.020
=
20 concurrent database operationsThis does not mean:
Set the connection pool exactly to 20.
Real systems need to account for:
But it demonstrates why latency and concurrency are connected.
Suppose:
20 application serversand each has:
pool size = 50Potential maximum connections:
20 × 50
=
1,000 connectionsThis is a critical system-design calculation.
Developers sometimes increase each application's pool independently without realizing that the total connection count grows with the number of application instances.
Consider:
Traffic increases
|
v
Application servers scale
|
v
More DB connections
|
v
Database CPU increases
|
v
Queries become slower
|
v
Connections remain occupied longer
|
v
Connection pool fills
|
v
Requests time out
|
v
Clients retry
|
v
Traffic increases furtherThis is a classic cascading failure.
Possible defenses:
Connection limits
Timeouts
Backpressure
Rate limiting
Caching
Query optimization
Load shedding
Circuit breakers
Retry budgetsRemember:
Know what data you need.
Prevent SQL injection.
Not assumptions.
Use query plans and metrics.
Neither too broad nor unnecessarily fragmented.
More database concurrency is not automatically better.
A query that works on:
10,000 rowsmay fail at:
1 billion rowsTrack:
UPDATE users
SET status = 'inactive';Could update every user.
Wrong:
WHERE email = NULLCorrect:
WHERE email IS NULLSELECT *#Can retrieve unnecessary data.
The optimizer chooses a plan.
Indexes have write and storage costs.
Can generate huge numbers of database round trips.
Can become expensive at scale.
Creates SQL injection risk.
Long transactions can increase:
PostgreSQL, MySQL, SQL Server, and Oracle can differ in:
Always verify database-specific behavior.
A language used to define, query, manipulate, and manage data in relational database systems.
A structured collection of rows organized into columns.
A key that uniquely identifies a row.
A key that references a key in another table and can enforce referential integrity.
An operation that combines rows from multiple tables based on a relationship or condition.
WHERE -> filters rows
HAVING -> filters groups after aggregationINNER JOIN -> matching rows
LEFT JOIN -> all left rows + matching right rowsAn auxiliary data structure used to make certain access patterns more efficient.
Because inserts, updates, and deletes may need to maintain the index structures.
A mechanism for inspecting the database's planned query execution strategy.
Possible reasons:
A strong answer:
Measure
↓
EXPLAIN / EXPLAIN ANALYZE
↓
Inspect plan
↓
Check indexes
↓
Check row estimates
↓
Check joins/sorts/scans
↓
Check locks/I/O/CPU
↓
Rewrite or index
↓
Measure againFor suitable ordered access:
Avoid huge OFFSET
Use cursor/keyset pagination
Use indexed orderingExample:
SELECT *
FROM orders
WHERE id > $1
ORDER BY id
LIMIT 50;Use:
Parameterized queries
Prepared statements
Safe ORM query APIsNever rely on manually escaping strings as the primary defense.
Usually an application fetches a list and then performs another database query separately for each item.
Solutions can include:
Create:
userswith:
id
name
email
age
city
created_atWrite SQL to:
Create:
productswith:
id
name
category
price
stock
created_atWrite queries to:
Create:
orderswith:
id
user_id
amount
status
created_atWrite queries to:
Design these tables:
users
products
orders
order_items
paymentsA user can:
Create an account
Browse products
Place orders
Have multiple order items
Make payments
View order historyDefine:
Primary keys
Foreign keys
Constraints
Data typesInsert:
10 users
20 products
30 ordersImplement:
User by email
Product by ID
Products by category
User's orders
Order details
Order total
Recent orders
Top-selling productsIdentify indexes for:
users.email
orders.user_id
orders.created_at
order_items.order_id
order_items.product_idDo not assume every candidate must be indexed. Justify each index based on an access pattern.
Design a transaction for:
Create order
Create order items
Reduce inventory
Create payment recordThen discuss which parts should remain inside the transaction and which could be asynchronous in a production architecture.
At this point, SQL should no longer feel like just a programming language.
Think of the complete path:
Client
|
v
API
|
v
Application
|
v
Connection Pool
|
v
SQL Query
|
v
Query Planner
|
v
Index / Scan
|
v
Database StorageEvery layer can influence latency.
For example:
Poor query
↓
Large scan
↓
High CPU/I/O
↓
Higher query latency
↓
Connections stay occupied
↓
Pool saturation
↓
API latency
↓
User-visible slowdownThis is why SQL knowledge is directly relevant to system design.
You now understand the core SQL capabilities required for database-focused system design.
The major concepts are:
SQL
|
+-- DDL
| +-- CREATE
| +-- ALTER
| +-- DROP
|
+-- DML
| +-- INSERT
| +-- UPDATE
| +-- DELETE
|
+-- Queries
| +-- SELECT
| +-- WHERE
| +-- ORDER BY
| +-- LIMIT
|
+-- Aggregation
| +-- COUNT
| +-- SUM
| +-- AVG
| +-- GROUP BY
| +-- HAVING
|
+-- Relationships
| +-- JOIN
| +-- Subquery
| +-- CTE
|
+-- Transactions
| +-- BEGIN
| +-- COMMIT
| +-- ROLLBACK
|
+-- Performance
+-- Index
+-- EXPLAIN
+-- Query Plan
+-- Pagination
+-- N+1The most important system-design lesson is:
A SQL query is not just a piece of application code. At scale, its execution strategy, indexes, concurrency, connection usage, and data volume can determine the performance of the entire system.
Before moving forward, you should be able to explain:
Next we move beyond relational databases and study NoSQL systems in depth.
We will learn:
Why NoSQL exists
↓
NoSQL data models
↓
Document databases
↓
Key-value databases
↓
Wide-column databases
↓
Graph databases
↓
Schema design
↓
Access-pattern-first modeling
↓
Consistency models
↓
Partitioning
↓
Replication
↓
Distributed NoSQL architecture
↓
SQL vs NoSQL trade-offs
↓
Real-world database selectionWe will also examine when a system should use SQL, when NoSQL makes sense, and why large distributed systems sometimes use multiple database technologies together.
Mark it as complete to track your mastery.