Course: System Design — Zero to Hero
Level: Intermediate → Advanced
Phase: Database Fundamentals
Status: Complete
Indexes are one of the most important tools for database performance.
In Chapter 09 we introduced indexes.
In Chapter 10 we saw how SQL queries, query planners, and EXPLAIN interact with indexes.
Now we go much deeper.
You will learn:
EXPLAINEXPLAIN ANALYZEThe central idea is:
An index is an additional data structure designed around a query access pattern.
Suppose we have:
users
-----
100,000,000 rowsWe execute:
SELECT *
FROM users
WHERE email = 'anubhav@example.com';Without a suitable index, the database may need to inspect many rows.
Conceptually:
Row 1
|
v
Does email match?
Row 2
|
v
Does email match?
Row 3
|
v
Does email match?
...
Row 100,000,000This is essentially a search through the table.
An index provides an additional structure:
email
|
+---- anubhav@example.com -> row locationThe database can use this structure to find relevant data more efficiently.
Imagine a 2,000-page book.
Without an index:
Search every pageWith an index:
System Design .......... Page 120
Networking ............. Page 240
Databases .............. Page 510The index tells you where to look.
A database index provides a similar conceptual advantage.
But database indexes are more sophisticated because they must support:
An important mental model:
Table
|
+-- Actual rows/data
|
+-- Index 1
|
+-- Index 2
|
+-- Index 3An index is not simply a "flag" on a column.
It is an additional structure maintained by the database.
This explains why indexes consume:
Suppose:
CREATE TABLE users (
id BIGINT PRIMARY KEY,
email TEXT NOT NULL,
name TEXT
);Create an index:
CREATE INDEX idx_users_email
ON users(email);Now the database has an additional access path for:
emailA query such as:
SELECT id, name
FROM users
WHERE email = 'anubhav@example.com';may use the index.
This is one of the most important concepts.
Creating:
CREATE INDEX idx_users_email
ON users(email);does not guarantee:
every query involving emailwill become faster.
The database optimizer chooses an execution plan based on:
A sequential scan reads table rows sequentially.
Conceptually:
Table
|
+-- Row 1
+-- Row 2
+-- Row 3
+-- ...
+-- Row NIt can be surprisingly efficient.
For example:
SELECT *
FROM users;If you need almost every row, an index provides little benefit.
The database may correctly choose a sequential scan.
An index scan roughly works like:
Query
|
v
Index
|
v
Matching key
|
v
Row location
|
v
Table rowThis is useful when the query can efficiently narrow down the result.
Suppose:
Table = 1,000 rows
Query returns = 900 rowsUsing an index may require:
Index lookup
+
many row fetchesA sequential scan may simply read the table efficiently.
Therefore:
Index scans are not inherently better than sequential scans.
The goal is:
Choose the cheapest execution strategy for the workload.
B-Trees are one of the most important index structures.
They are designed to keep keys ordered and support efficient:
Conceptually:
[50]
/ \
/ \
[20] [80]
/ \ / \
[10] [30] [60] [90]Real database B-Trees are more complex and optimized for storage pages.
Suppose we need:
WHERE age = 30A B-Tree can efficiently navigate to the relevant key.
It can also support range queries:
WHERE age >= 30
AND age < 40And ordering:
ORDER BY agewhen the query and index structure align appropriately.
Suppose:
Index:
10
20
30
40
50
60
70
80Query:
WHERE age BETWEEN 30 AND 60The database can locate the beginning of the range and traverse the relevant portion.
This is one reason B-Trees are so widely useful.
A balanced tree can reduce search work dramatically.
A simplified intuition is:
Sequential scan:
O(N)
Tree lookup:
roughly O(log N)Real database performance is more complicated because of:
Do not treat Big-O as a complete performance model.
A database index is usually page-oriented.
A database does not necessarily build a tiny binary tree like:
50
/ \
20 80Instead, nodes can contain many keys.
Conceptually:
+--------------------------------+
| 10 | 20 | 30 | 40 | 50 | ... |
+--------------------------------+This reduces tree height and can make storage access efficient.
Think of a database as operating on pages.
Conceptually:
Index
|
+-- Page 1
+-- Page 2
+-- Page 3
+-- Page 4A B-Tree organizes these pages.
This is why database indexing is closely connected to:
Databases often keep frequently accessed pages in memory.
Conceptually:
Query
|
v
Buffer cache
|
+---- page already in memory -> fast
|
+---- page not cached -> storage accessTherefore, the same query can behave differently depending on whether relevant pages are cached.
This is another reason benchmark results must be interpreted carefully.
A hash index organizes keys around a hash function.
Conceptually:
key
|
v
hash(key)
|
v
bucket
|
v
value/locationThis can be useful for equality-style lookup:
WHERE user_id = 123But hash indexes generally do not provide the same natural ordering/range-query capabilities as B-Trees.
| Feature | B-Tree | Hash |
|---|---|---|
| Equality | Good | Good |
| Range queries | Good | Generally not suitable |
| Ordering | Good | No natural ordering |
| Prefix/range-style access | Often useful | Generally not |
| Common database default | Very common | More specialized |
Exact support and implementation depend on the database.
A composite index contains multiple columns.
Example:
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at);This is one index with two indexed columns.
Suppose the common query is:
SELECT *
FROM orders
WHERE user_id = 123
ORDER BY created_at DESC;An index on:
(user_id, created_at)matches the query shape much better than separate unrelated indexes in many cases.
The index provides:
user_id
+
created_at orderingin one access path.
Compare:
(user_id, created_at)with:
(created_at, user_id)They are not equivalent.
Suppose the query is:
WHERE user_id = 123
ORDER BY created_at DESCThe first index aligns naturally with:
user_id -> created_atThe second may not provide the same benefit.
Therefore:
Composite index design must consider query predicates and ordering.
For:
(user_id, created_at, status)the first column is especially important.
Potentially useful access patterns include:
user_idand:
user_id + created_atand:
user_id + created_at + statusA query using only:
created_atdoes not generally get the same benefit from this index.
This is called the leftmost-prefix intuition.
Exact optimizer behavior depends on the database.
Suppose:
SELECT id, amount
FROM orders
WHERE user_id = 123
AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;Potential candidate:
(user_id, status, created_at DESC)But the correct order depends on:
Do not blindly follow a single formula.
A common index-design heuristic is:
Equality filters
↓
Range/filtering column
↓
Ordering columnsFor example:
WHERE user_id = 123
AND created_at >= ...
ORDER BY created_atmay suggest:
(user_id, created_at)This is a heuristic, not a universal law.
Always verify using the actual database's query plan.
Selectivity describes how effectively a condition narrows down rows.
Suppose:
100 million usersand:
email = 'x@example.com'matches:
1 rowVery selective.
But:
country = 'India'might match:
20 million rowsMuch less selective.
Highly selective conditions are often good candidates for index-driven lookups.
Cardinality refers broadly to the number of distinct values in a column or result set.
Example:
gender:
Male
FemaleLow cardinality.
email:
almost every value uniqueHigh cardinality.
High cardinality often makes an equality index more selective, but index usefulness still depends on the complete query and workload.
They are related but not identical.
How many distinct values exist.
How much a particular predicate narrows the result.
Example:
emailmay have high cardinality and high selectivity for:
WHERE email = '...'But:
countrymay have relatively low cardinality and low selectivity for a common country.
A common question:
Should I index a boolean column?
Suppose:
is_activecontains:
true = 99%
false = 1%A normal index may not always be useful for:
WHERE is_active = truebecause most rows match.
But an index can sometimes be useful for the rare value.
The correct answer depends on:
Some databases support indexes over only rows satisfying a condition.
PostgreSQL example:
CREATE INDEX idx_active_users_email
ON users(email)
WHERE deleted_at IS NULL;This index contains only active/non-deleted users.
Benefits can include:
when the query matches the predicate.
Sometimes queries operate on expressions.
Example:
SELECT *
FROM users
WHERE LOWER(email) = 'anubhav@example.com';An ordinary index on:
emailmay not directly provide the desired access path.
A database supporting expression indexes can use:
CREATE INDEX idx_users_lower_email
ON users(LOWER(email));This aligns the index with the expression used by the query.
Suppose:
WHERE LOWER(name) = 'anubhav'while the index is:
(name)Depending on the database, this may prevent a normal index from being used effectively.
Potential solutions:
Again:
Inspect the actual query plan.
A covering index contains enough information to answer a query without needing to fetch the full table row, when the database can use that structure for an index-only access path.
Suppose:
SELECT user_id, created_at
FROM orders
WHERE user_id = 123
ORDER BY created_at DESC;An index on:
(user_id, created_at)may contain everything required for the query.
This can reduce table access.
Some databases allow non-key columns to be stored with an index.
PostgreSQL example:
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC)
INCLUDE (amount, status);The indexed columns determine the ordering/search structure.
Included columns can provide additional data for index-only access without being part of the search key.
A covering index can improve reads.
But it can also:
increase index size
increase write cost
increase storage
increase cache pressureTherefore:
Covering indexes are optimization tools, not defaults.
The terminology differs across database systems, so be careful.
In systems with a clustered-index concept, the table's data organization is closely tied to an index ordering.
Conceptually:
Clustered index
|
v
Data stored according to index organizationThis can make certain range access patterns efficient.
But the exact implementation differs by database.
A non-clustered index is conceptually separate from the underlying table storage.
Index
|
+-- key -> row locatorThe database can:
Find key in index
|
v
Locate table row
|
v
Read dataThis can involve additional table lookups.
PostgreSQL's common B-Tree indexes should not be explained as if they were identical to SQL Server's clustered/non-clustered index model.
PostgreSQL tables are heap-organized by default, and indexes point to table tuples.
This is an important interview nuance:
Do not memorize index terminology without understanding the database engine.
A primary key usually has an associated unique index or equivalent enforcement mechanism.
For example:
CREATE TABLE users (
id BIGINT PRIMARY KEY
);The database needs an efficient mechanism to enforce:
unique idand support primary-key lookup.
Example:
CREATE UNIQUE INDEX idx_users_email
ON users(email);This enforces uniqueness.
But when uniqueness is part of the logical schema, a constraint is often clearer:
email TEXT UNIQUEor:
ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email);Suppose:
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20;An appropriate index can sometimes help the database avoid sorting a huge number of rows.
For example:
created_at DESCcan provide data in the required order.
This is especially useful for:
latest records
recent events
leaderboards
time-based feedsConsider:
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20;If the database has a useful ordering index, it may only need to find the first relevant entries rather than sorting the entire table.
Conceptually:
Without useful index:
Read many rows
↓
Sort
↓
Take 20
With suitable index:
Index
↓
First 20The exact plan depends on filters and other query conditions.
Suppose:
SELECT *
FROM users u
JOIN orders o
ON o.user_id = u.id
WHERE u.id = 123;An index on:
orders.user_idcan be valuable because the database needs to locate orders belonging to user 123.
This is a common example of indexing a foreign-key column based on access patterns.
A foreign key constraint and an index are different concepts.
A foreign key enforces relationship integrity.
An index improves access patterns.
For example:
orders.user_idmay be worth indexing because queries frequently:
find orders for user
join orders to usersBut the decision should come from workload and database behavior.
Suppose we use keyset pagination:
SELECT *
FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 50;An index on:
idcan support this access pattern naturally.
For time-based pagination:
SELECT *
FROM events
WHERE created_at < $1
ORDER BY created_at DESC
LIMIT 50;an appropriate index on:
created_atmay be useful.
Suppose timestamps are not unique.
Using only:
created_atcan create ambiguity.
A more stable ordering might be:
(created_at, id)Query:
WHERE
(created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;The exact syntax and indexing strategy depend on the database.
The important principle is:
Use a deterministic ordering for cursor pagination.
Suppose:
events
------
id
service_id
created_at
payloadCommon query:
WHERE service_id = 123
AND created_at >= ...
ORDER BY created_at DESCA candidate composite index:
(service_id, created_at DESC)matches the access pattern.
This pattern appears frequently in:
Suppose:
1 billion rowsAdding an index can be expensive.
Potential concerns:
Index build time
Disk space
I/O
CPU
Locking/concurrency
Replication impact
Deployment durationProduction index creation must be planned carefully.
Some databases provide online/concurrent index creation mechanisms.
The exact commands differ by database.
For PostgreSQL, one option is:
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders(user_id, created_at DESC);This can reduce blocking of normal table operations compared with a standard index build, but it has its own trade-offs and operational requirements.
Always understand the database-specific behavior before using it in production.
Creating:
10 new indexeson a huge production table can generate substantial:
It can also affect application performance.
Index migrations should be treated as production changes.
Suppose:
Table
+
5 indexesAn insert may require maintaining:
Table data
Index 1
Index 2
Index 3
Index 4
Index 5Conceptually:
1 logical write
|
+---- table
+---- index 1
+---- index 2
+---- index 3
+---- index 4
+---- index 5This is write amplification.
Indexes create a fundamental trade-off:
More indexes
|
+---- Better read access
|
+---- More storage
|
+---- More write work
|
+---- More maintenanceSystem design requires balancing these costs.
Over-indexing means creating more indexes than the workload justifies.
Symptoms:
Many indexes
High write latency
Large storage usage
Slow writes
Index maintenance overheadA table with:
20 indexesis not automatically better optimized than one with:
5 carefully chosen indexesUnder-indexing can produce:
Large sequential scans
High CPU
High I/O
High latency
Database overloadThe goal is not:
minimum indexesor:
maximum indexesThe goal is:
The right indexes for the actual workload.
Suppose a table has:
INDEX(email)
INDEX(email)The second index may provide little or no benefit while consuming resources.
Another subtle case:
INDEX(user_id, created_at)
INDEX(user_id)The second index may or may not be necessary depending on workload and database behavior.
Review indexes periodically.
Suppose:
INDEX(user_id, created_at)already exists.
A separate:
INDEX(user_id)may be redundant for some workloads because the composite index can often support lookups beginning with user_id.
But this is not an automatic rule.
Consider:
Consider two queries:
WHERE user_id = 123and:
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20They may benefit from different index designs.
The second query may benefit strongly from:
(user_id, created_at DESC)because the index supports both filtering and ordering.
Suppose:
WHERE email = $1
OR phone = $2Possible strategies include separate indexes:
email
phoneThe optimizer may combine access paths or choose another plan.
Do not assume:
OR = slowor:
two indexes = automatically fastInspect the query plan.
Consider:
WHERE DATE(created_at) = '2026-10-01'Applying a function to every row can interfere with efficient use of a normal created_at index.
A range predicate is often more index-friendly:
WHERE created_at >= '2026-10-01'
AND created_at < '2026-10-02'This also avoids wrapping the indexed column in a function.
Consider:
WHERE name LIKE 'Anu%'This can be compatible with certain index strategies.
But:
WHERE name LIKE '%Anu%'requires finding a substring anywhere in the value and may not be efficiently handled by an ordinary B-Tree index.
For text search, specialized full-text or trigram indexes may be more appropriate depending on the database.
If the requirement is:
Search articles containing:
"distributed systems database"a normal equality index is not the right abstraction.
A search engine or full-text indexing mechanism may be appropriate.
Examples:
PostgreSQL full-text search
Elasticsearch
OpenSearchThe correct choice depends on the application's search requirements.
Do not confuse:
Database indexwith:
Search engine indexA database B-Tree is excellent for:
WHERE id = 123
WHERE created_at > ...
ORDER BY created_atA search engine is designed for richer text search:
relevance
tokenization
stemming
fuzzy matching
faceting
rankingLater chapters will explore search systems separately.
Query optimizers rely on statistics to estimate:
How many rows match?
How selective is this condition?
Which join strategy is likely cheaper?
Should we use an index?If statistics are stale, the optimizer may choose a poor plan.
This is one reason databases provide mechanisms for statistics collection and maintenance.
Suppose the optimizer estimates:
Expected rows = 10but actual result:
10,000,000 rowsThe optimizer's assumptions were badly wrong.
This can cause an inappropriate execution strategy.
For example:
Expected 10 rows
|
v
Index lookup seems cheap
Actual 10M rows
|
v
Index access becomes very expensiveThis is why:
estimated rows
vs
actual rowsis important when analyzing query plans.
Example:
EXPLAIN
SELECT *
FROM users
WHERE email = 'anubhav@example.com';The database may show a plan resembling:
Index Scan
Index Cond: email = ...or:
Seq Scan
Filter: email = ...The exact output depends on the database.
Example:
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'anubhav@example.com';This can show:
Planning Time
Execution Time
Estimated Rows
Actual Rows
LoopsIt lets us compare estimates with reality.
Suppose:
Estimated rows = 1
Actual rows = 1
Execution time = 2 msLooks healthy.
Now:
Estimated rows = 10
Actual rows = 2,000,000
Execution time = 2 secondsThe query planner's estimates are badly wrong.
Investigate:
Statistics
Data distribution
Query predicates
Indexes
CorrelationIn some databases, the query can be answered directly from index information.
Conceptually:
Query
|
v
Index
|
v
Resultinstead of:
Query
|
v
Index
|
v
Table
|
v
ResultThis can reduce table access.
But database-specific visibility or storage rules can affect whether a true index-only scan is possible.
Indexes that fit into memory or are frequently cached can perform very well.
Large indexes may cause:
Cache pressureand reduce the amount of memory available for:
table pages
other indexes
working dataTherefore:
Index size is also a memory/cache design consideration.
Suppose:
orders = 500 million rowsQueries:
orders by user
recent orders
orders by status
orders by IDPotential candidates:
PRIMARY KEY(id)
INDEX(user_id, created_at)
INDEX(status, created_at)But before adding all three:
A strong production workflow:
1. Identify important query
↓
2. Measure latency/frequency
↓
3. Inspect EXPLAIN
↓
4. Understand predicates
↓
5. Understand ordering
↓
6. Check existing indexes
↓
7. Design candidate index
↓
8. Test with representative data
↓
9. Compare query plans
↓
10. Measure production impactThis is far better than:
Add an index because the query is slow.
Ask:
WHERE columns?JOIN columns?ORDER BY columns?> < >= <= BETWEEN?LIMIT?
Cursor?How many rows match?How many rows are returned?How often does this query run?Suppose:
notifications
-------------
id
user_id
created_at
is_read
messageQuery:
SELECT id, message, created_at
FROM notifications
WHERE user_id = 123
AND is_read = false
ORDER BY created_at DESC
LIMIT 50;Candidate index:
(user_id, is_read, created_at DESC)But the best index depends on:
How many notifications are unread?
How frequently are reads/writes performed?
How many users?
How often does this endpoint run?Suppose:
urls
----
short_code
long_url
created_atCritical query:
SELECT long_url
FROM urls
WHERE short_code = $1;The index should make:
short_code lookupefficient.
Usually:
UNIQUE(short_code)is a natural schema constraint and provides an access path.
This is a classic high-read, low-latency lookup.
Schema:
messages
--------
conversation_id
message_id
created_at
sender_id
contentQuery:
SELECT message_id, sender_id, content, created_at
FROM messages
WHERE conversation_id = $1
ORDER BY created_at DESC
LIMIT 50;Candidate index:
(conversation_id, created_at DESC)This aligns:
partition/filter
+
ordering
+
limitwith the query.
Suppose:
Database query = 10 ms
Cache lookup = 1 msCaching can reduce repeated database work.
But an index may reduce:
10 ms -> 2 msfor a database lookup.
They solve different problems:
Index
-> make database access efficient
Cache
-> avoid database access for repeated dataProduction systems often use both.
Suppose a query requires expensive joins.
Two possible strategies:
Optimize indexes:
Table A
|
JOIN
|
Table BDenormalize:
Store required data togetherIndexes preserve more normalized data modeling.
Denormalization can simplify reads at the cost of duplication.
The correct choice depends on workload.
Indexes optimize access within a database/partition.
Sharding distributes data across multiple nodes.
Think:
Sharding
|
+--> Which node has the data?
Index
|
+--> Where inside that node can I find it efficiently?Large distributed systems often need both.
In distributed systems, an index may itself have distributed implications.
Questions include:
Where is the index stored?
How is it replicated?
How is it updated?
What happens during partition failure?
Can secondary indexes become hotspots?This is one reason distributed secondary indexes can be more complex than local indexes.
Index every column.
Create indexes without examining queries.
Ignore index write cost.
Use the wrong composite-column order.
Assume every index is used.
Ignore data distribution.
Ignore query result size.
Use a normal B-Tree for a full-text search problem.
Create indexes on expressions without understanding query shape.
Never remove obsolete indexes.
Useful database metrics include:
Query latency
Queries/sec
Slow queries
CPU
Memory
Disk I/O
Buffer/cache hit ratio
Connection count
Lock waits
Index size
Table size
Replication lagFor indexes specifically:
Index usage
Index scans
Unused indexes
Index size
Write overheadExact metrics vary by database.
Indexes should be treated as production assets.
Lifecycle:
Design
↓
Create
↓
Measure
↓
Monitor
↓
Tune
↓
Retain or removeAn index that was useful six months ago may become unnecessary after:
schema changes
query changes
traffic changes
data growth
application changesStrong answer:
Indexes improve certain reads, but they consume storage and increase the work required for inserts, updates, and deletes. They also create memory/cache pressure and maintenance overhead. Therefore indexes should be designed around important access patterns and validated with query plans and measurements.
A composite index is an index over multiple columns.
Example:
(user_id, created_at)It can support queries that use the leading columns and may also support ordering/range access depending on the query.
Column order matters.
Selectivity describes how strongly a predicate narrows the result set.
Example:
email = Xmay match one row among millions.
That is highly selective.
A condition matching most rows is less selective.
Because an index is not free.
If a query needs a large fraction of a table, using an index may require many random or scattered table accesses.
Reading the table sequentially can be cheaper.
The optimizer chooses based on cost estimates.
A strong system-design answer:
Measure
↓
EXPLAIN / EXPLAIN ANALYZE
↓
Inspect scan/join/sort
↓
Check row estimates
↓
Check indexes
↓
Check statistics
↓
Check CPU/I/O/locks
↓
Rewrite query or add/change index
↓
Measure againConsider:
Equality predicates
Range predicates
Ordering
Join conditions
Selectivity
Query frequency
Write costThen validate the design using the actual database query planner.
Table:
users
-----
id
email
name
country
created_atQueries:
SELECT id, name
FROM users
WHERE email = $1;SELECT id, name
FROM users
WHERE country = $1
ORDER BY created_at DESC
LIMIT 50;Determine candidate indexes.
Think about:
email
(country, created_at)Then explain why each index matches its query.
Table:
orders
------
id
user_id
status
amount
created_atQueries:
SELECT *
FROM orders
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20;SELECT COUNT(*)
FROM orders
WHERE status = 'PAID';SELECT *
FROM orders
WHERE created_at >= $1
AND created_at < $2;Design candidate indexes.
Then ask:
Are all three indexes actually necessary?
This is the important part.
Suppose:
Table:
100 million rows
Query:
SELECT *
FROM events
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 50;You observe:
Execution time = 2.5 secondsInvestigate:
Is there an index?
What is the index order?
How many rows are estimated?
How many rows are actually scanned?
Is a sort occurring?
Is the table heavily cached?
Is the query returning too many columns?Then design an optimization.
Use the e-commerce schema from previous chapters:
users
products
orders
order_items
paymentsAssume these API endpoints:
GET /users/:id/orders
GET /products/:id
GET /products?category=...
GET /orders/:id
GET /users/:id/payments
GET /orders/recentFor each endpoint:
Write the SQL query.
Identify:
WHERE
JOIN
ORDER BY
LIMITDesign candidate indexes.
Explain:
Why this index?
Why this column order?
What is the selectivity?
What is the write cost?Use:
EXPLAINto inspect the plan.
Determine whether the index is actually useful.
Suppose your database has:
500 million ordersTraffic:
100,000 reads/sec
20,000 writes/secYou discover:
GET /users/:id/ordersis slow.
Do not immediately shard.
First investigate:
Query
↓
Index
↓
Query plan
↓
Data distribution
↓
Database resourcesIf a correct composite index reduces the workload sufficiently, sharding may not yet be necessary.
This illustrates an important system-design principle:
Optimize the current bottleneck before adding architectural complexity.
Sometimes indexing cannot solve the problem.
Possible reasons:
Dataset too large for one node
Write throughput too high
Hot partition
Global traffic
Complex search
Large analytical workload
High availability requirementsThen we may need:
Caching
Read replicas
Partitioning
Sharding
Search infrastructure
Data warehouses
Streaming systemsThese will appear throughout later chapters.
Remember:
Query
|
v
Query Planner
|
+-----------+-----------+
| |
v v
Index Table
| |
v |
Candidate rows <-------------+
|
v
ResultThe optimizer decides whether an index helps.
There is no universal formula.
Instead remember:
Good Index
=
Query Pattern
+
Data Distribution
+
Selectivity
+
Ordering
+
Workload Frequency
-
Write Cost
-
Storage CostThis is a mental model, not a literal mathematical equation.
You now understand indexes at a much deeper level.
The important concepts are:
Index
|
+-- B-Tree
+-- Hash
+-- Composite
+-- Covering
+-- Partial
+-- Expression
|
+-- Selectivity
+-- Cardinality
+-- Query planner
+-- Sequential scan
+-- Index scan
+-- EXPLAIN
|
+-- Read optimization
+-- Write amplification
+-- Storage cost
+-- MaintenanceThe most important lesson is:
An index is not simply "something that makes a column faster." It is a data structure designed to accelerate specific access patterns.
A strong database engineer asks:
What query are we optimizing?
↓
How selective is it?
↓
How often does it run?
↓
What ordering does it need?
↓
What index supports it?
↓
What will that index cost on writes?
↓
What does EXPLAIN say?
↓
Did production latency actually improve?You should now be able to explain:
Next we move from optimizing a single database to distributing database data across multiple machines.
We will learn:
Why replication exists
↓
Primary / Replica architecture
↓
Leader / Follower
↓
Synchronous replication
↓
Asynchronous replication
↓
Replication lag
↓
Read replicas
↓
Read-after-write consistency
↓
Failover
↓
Leader election
↓
Split-brain
↓
Replication topologies
↓
Multi-region replication
↓
Conflict handling
↓
High availability
↓
Production replication architectureThis is where database fundamentals begin connecting directly to high availability, scalability, and distributed systems.
Mark it as complete to track your mastery.