Persistence with PostgreSQL
Indexes and Query Performance
আপনি একটি free preview lesson দেখছেন।
আমাদের database schema এখন functional:
products
inventory
orders
order_items
আমরা primary keys, foreign keys, constraints, JPA mappings, repositories, এবং Flyway migrations define করেছি।
এখন next question:
PostgreSQL কীভাবে efficiently সেই rows খুঁজে বের করবে যেগুলো আমাদের API চায়?
একটি ছোট database-এ:
100 Products
500 Orders
প্রায় যেকোনো query fast মনে হতে পারে।
কিন্তু data grow করলে:
100,000 Products
10,000,000 Orders
50,000,000 OrderItems
একই query design dramatically different behaviour করতে পারে।
এই জায়গায় আসে:
Indexes
কিন্তু indexes নিয়ে common mistake হলো:
Query slow? Add more indexes.
এটি incomplete thinking।
Index read performance improve করতে পারে, কিন্তু তার cost আছে:
additional storage
slower writes
more maintenance
more complex query-planning choices
এই lesson-এর goal:
আমাদের actual API access patterns থেকে useful indexes derive করা, composite index ordering বুঝা, PostgreSQL query plans inspect করা, এবং unnecessary indexing avoid করা।
Start With Queries, Not Columns
Index design-এর সবচেয়ে গুরুত্বপূর্ণ rule:
Don't ask which columns can be indexed. Ask which queries must be efficient.
আমাদের existing API থেকে query patterns already জানা।
For example:
GET /api/v1/products?page=0&size=20
needs:
active Products
with Inventory > 0
deterministic ordering
bounded pagination
Customer Order history:
GET /api/v1/orders?page=0&size=20
needs:
customer_id = authenticated Customer
ORDER BY created_at DESC, id DESC
LIMIT / OFFSET
Order detail:
GET /api/v1/orders/{orderId}
needs:
find Order by primary key
load OrderItems by order_id
These access patterns should drive index decisions।
What Is an Index?
Conceptually, without an index PostgreSQL may need to inspect rows one by one:
row 1
row 2
row 3
...
row 10,000,000
to find matching data।
This is similar to looking for a topic in a book without an index:
start at page 1
scan everything
A database index maintains an additional structure that helps PostgreSQL locate rows more efficiently for supported access patterns।
Conceptually:
customer_id
↓
matching row locations
Index Does Not Replace the Table
The table remains the authoritative data।
Index is an additional structure derived from table values।
That means when data changes:
INSERT
UPDATE
DELETE
relevant indexes may also need updating।
This is why indexes are not free।
Primary Keys Already Have Index Support
Our schema has:
products.id PRIMARY KEY
orders.id PRIMARY KEY
inventory.product_id PRIMARY KEY
order_items(order_id, product_id) PRIMARY KEY
Primary-key uniqueness requires PostgreSQL to maintain supporting index structures।
Therefore we normally do not add another:
CREATE INDEX
ON products(id);
That would duplicate existing support।
Unique Constraints Also Matter
Likewise, a UNIQUE constraint generally requires supporting index semantics।
Before creating an index, always ask:
Does a primary key or unique constraint
already provide an index useful for this query?
Otherwise we may create redundant indexes।
Query 1 — Find Product by ID
Application:
ProductRepository.findById(ProductId)
SQL conceptually:
SELECT
id,
name,
price,
active
FROM products
WHERE id = ?;
We already have:
PRIMARY KEY(id)
So no additional Product ID index is needed।
Query 2 — Find Inventory by ProductId
SELECT
product_id,
available_quantity
FROM inventory
WHERE product_id = ?;
inventory.product_id is already:
PRIMARY KEY
Again, no additional index needed।
Query 3 — Find Order by ID
SELECT
id,
customer_id,
status,
created_at
FROM orders
WHERE id = ?;
orders.id is already the primary key।
No additional index needed।
Query 4 — Load OrderItems for an Order
Conceptually:
SELECT
product_id,
quantity,
unit_price
FROM order_items
WHERE order_id = ?;
Our composite primary key is:
(order_id, product_id)
This is important।
Because:
order_id
is the first column of the composite key, the index can naturally support lookups beginning with:
order_id = ?
So loading all items for one Order is already well aligned with our key design।
Composite Index Ordering
Consider an index:
(order_id, product_id)
It is naturally useful for queries like:
WHERE order_id = ?
and:
WHERE order_id = ?
AND product_id = ?
But it is generally not equally useful for a query that only asks:
WHERE product_id = ?
because product_id is not the leading part of the index।
This is why column order in a composite index matters।
Think Left to Right
A practical mental model:
INDEX (A, B, C)
is naturally aligned with queries beginning from:
A
then perhaps:
A + B
then:
A + B + C
It should not be treated as three independent single-column indexes।
Does order_items.product_id Need Its Own Index?
Current application requirements do not include:
find every Order containing Product X
Our main OrderItem query is:
find all items for Order
already supported by:
(order_id, product_id)
So initially:
no separate product_id index
is justified।
If a future feature needs:
show all Orders containing Product 101
and measurements show that query needs support, then:
CREATE INDEX ...
ON order_items(product_id);
may become justified।
Indexes should follow actual access patterns।
Customer Order History
Our customer Order history query is one of the most important collection queries:
SELECT
id,
customer_id,
status,
created_at
FROM orders
WHERE customer_id = ?
ORDER BY created_at DESC, id DESC
LIMIT ?
OFFSET ?;
What does it need?
filter by customer_id
then order by created_at
then id as deterministic tie-breaker
This suggests a composite index।
A Useful Order History Index
Conceptually:
CREATE INDEX orders_customer_history_idx
ON orders (
customer_id,
created_at DESC,
id DESC
);
Why this order?
Because query begins with:
customer_id = ?
then requires:
created_at DESC
id DESC
The index structure aligns closely with the query।
Why Not Just Index customer_id?
This:
CREATE INDEX
ON orders(customer_id);
can help locate customer Orders।
But PostgreSQL may still need additional sorting for:
created_at DESC, id DESC
A composite index can support both:
filtering
+
desired order
for this high-value query pattern।
Why id Is Included
We deliberately defined Order pagination order as:
created_at DESC
id DESC
because timestamps may tie।
If the index were only:
(customer_id, created_at DESC)
the database still has to handle the secondary ordering requirement։
Including:
id DESC
aligns the index with the deterministic ordering contract।
Index Design and API Design Are Connected
Notice the sequence:
API pagination contract
↓
stable sort requirement
↓
Repository query
↓
index design
Indexes are not an isolated DBA exercise।
Earlier API decisions affect persistence performance later।
Admin Order Queries
We know admin can:
view all Orders
but we have not yet committed to a specific admin filtering/sorting API।
Therefore don't immediately create:
status index
created_at index
customer_id + status index
status + created_at index
for every imaginable admin query।
When the actual admin query contract becomes concrete, we can index it based on real access patterns।
Query 5 — Customer Product Browsing
Our customer Product query is conceptually:
SELECT
p.id,
p.name,
p.price
FROM products p
JOIN inventory i
ON i.product_id = p.id
WHERE p.active = TRUE
AND i.available_quantity > 0
ORDER BY ...
LIMIT ?
OFFSET ?;
Several things matter here।
Inventory Join Is Already Supported
The join:
inventory.product_id = products.id
uses:
inventory.product_id PRIMARY KEY
and:
products.id PRIMARY KEY
So both sides already have useful indexed identity support।
We do not need a separate index simply to make the foreign-key equality possible।
What About products.active?
Should we add:
CREATE INDEX
ON products(active);
?
Maybe not।
active is a boolean:
true
false
Low-cardinality columns often don't automatically benefit from a standalone index, especially if a large percentage of rows share the same value।
If:
95% Products are active
then an active = TRUE query still needs most Product rows।
A standalone boolean index may provide little value।
Index Selectivity
A useful concept is:
selectivity
An index tends to be more useful when a condition narrows the dataset significantly।
Example:
id = 12345
is extremely selective।
Maybe one row।
But:
active = TRUE
could match nearly every row।
So:
"column is frequently filtered"
does not automatically mean:
"single-column index is useful"
What About Inventory Quantity?
Should we add:
CREATE INDEX
ON inventory(available_quantity);
because query uses:
available_quantity > 0
?
Again, not automatically।
If most Products are in stock:
available_quantity > 0
matches most Inventory rows।
An index may not save much work।
Partial Indexes
PostgreSQL can support indexes containing only rows satisfying a condition।
Conceptually, if Product browsing becomes a major high-volume query and measurements justify it, a partial index might be considered for a condition such as:
active = TRUE
or another stable predicate।
But our browse query also depends on another table:
inventory
so no single partial index magically solves the whole query।
The key lesson:
Advanced indexes should follow evidence, not excitement.
We Need a Product Ordering Contract First
Our API deliberately required:
deterministic sorting
but we have not yet fixed the exact public Product browse ordering।
Therefore we should not create an index based on an invented sort such as:
name ASC
or:
price ASC
unless the actual API contract chooses it।
This is why index design sometimes waits for a product/API decision।
Query 6 — Order Ownership Lookup
For customer-specific Order access, we may choose a query:
SELECT ...
FROM orders
WHERE id = ?
AND customer_id = ?;
Do we need:
(id, customer_id)
index?
Probably not initially।
Why?
id is already unique through the primary key।
PostgreSQL can find the single row by:
id
then evaluate:
customer_id
on that row।
Adding another composite index for this exact query would likely duplicate unnecessary work।
Unique Leading Predicate Changes Things
When one condition already narrows to at most one row:
id = ?
adding more columns to another index often provides little value।
Always understand what the existing key already guarantees।
Foreign Keys and Indexes
We have foreign keys:
inventory.product_id
→ products.id
order_items.order_id
→ orders.id
order_items.product_id
→ products.id
Foreign keys protect integrity।
But:
A foreign key and an index are not the same concept.
The referenced parent key is indexed through its primary/unique constraint।
The referencing child column may need an index based on:
query patterns
parent updates/deletes
join requirements
not merely because a foreign key exists।
Our order_items.order_id Case
We already have:
PRIMARY KEY(order_id, product_id)
so the foreign-key column:
order_id
is covered as the leading key column।
No separate:
order_items(order_id)
index is needed।
Our order_items.product_id Case
It is a foreign key but appears second in:
(order_id, product_id)
If Product-based lookup or certain parent-side operations become important at scale, a separate index could become useful।
Current application query patterns do not justify it yet।
Indexes Have Write Cost
Suppose orders has five indexes।
Every new Order insert may need:
insert row into table
update index 1
update index 2
update index 3
update index 4
update index 5
More indexes mean more work for writes।
Likewise status updates such as:
UNPAID → PAID
may require index maintenance if status participates in indexes।
Inventory Is Write-Heavy
Inventory changes during:
Order creation
Order cancellation
admin quantity changes
This table may experience frequent writes।
Adding unnecessary indexes can make those updates more expensive।
So avoid:
index every Inventory column
without strong query benefit।
Indexes Consume Storage
An index is an additional database structure।
For millions of rows, index size can become substantial।
Multiple overlapping indexes may consume significant disk and cache space।
This can reduce effective memory available for useful table/index pages।
Again:
Indexes trade additional write/storage cost for potentially faster reads.
Over-Indexing Can Hurt
Imagine:
orders
has:
index(customer_id)
index(created_at)
index(status)
index(customer_id, created_at)
index(customer_id, status)
index(status, created_at)
index(customer_id, status, created_at)
This may look "optimized."
But perhaps only two queries actually exist।
Now we have:
extra write cost
extra disk usage
redundant structures
more migration/maintenance complexity
This is not thoughtful optimization।
Prefer a Small Index Portfolio
For each index, we should be able to answer:
Which query does this support?
Why are existing indexes insufficient?
Is this query important enough?
What is the write/storage cost?
If nobody can answer those questions, the index may not be justified।
Query Planner
PostgreSQL does not blindly use an index whenever one exists।
It has a:
query planner
that estimates different ways to execute a query।
Possible choices include:
Sequential Scan
Index Scan
Bitmap Scan
joins using different algorithms
The planner chooses what it estimates to be the cheapest plan।
Sequential Scan Is Not Automatically Bad
Developers sometimes panic when they see:
Seq Scan
A sequential scan can be exactly the right plan when:
table is small
query returns most rows
index lookup would cost more
Example:
products has 50 rows
Scanning all 50 rows may be faster than consulting an index।
Performance engineering is contextual।
Index Exists, but PostgreSQL Ignores It
Suppose we add:
INDEX(active)
and query:
WHERE active = TRUE
If 98% of rows are active, PostgreSQL may reasonably choose:
Sequential Scan
because almost the whole table is needed anyway।
That does not necessarily mean PostgreSQL is broken or index configuration failed।
EXPLAIN
To understand a query plan, PostgreSQL provides:
EXPLAIN
Conceptually:
EXPLAIN
SELECT
id,
status,
created_at
FROM orders
WHERE customer_id = 'C-10'
ORDER BY created_at DESC, id DESC
LIMIT 20;
This lets us inspect the planner's chosen execution strategy without guessing solely from Java code।
EXPLAIN ANALYZE
For real execution measurement, we can use:
EXPLAIN ANALYZE
...
This executes the query and reports actual runtime/execution information in addition to planner estimates।
Important:
EXPLAIN ANALYZEactually runs the statement.
For read-only SELECT queries this is usually straightforward।
For modifying statements, be careful because the database operation can actually occur unless wrapped/managed appropriately।
Measure With Realistic Data
A query against:
10 Orders
does not tell us how it behaves against:
10 million Orders
Index usefulness depends on:
row count
data distribution
query frequency
returned rows
concurrency
hardware/cache
Performance testing should use data volumes/distributions reasonably representative of the system being analyzed।
Don't Optimize a Table With Five Rows
During course development, our local database will be tiny।
That doesn't mean we should create speculative indexes for hypothetical future scale।
Instead:
- identify obvious high-value access patterns,
- add indexes directly justified by them,
- measure real performance as data grows.
Our First Clearly Justified Additional Index
Order history has a strong, explicit access pattern:
customer_id
+
created_at DESC
+
id DESC
So this is a reasonable initial index:
CREATE INDEX orders_customer_history_idx
ON orders (
customer_id,
created_at DESC,
id DESC
);
Unlike speculative indexes, we can clearly explain its purpose।
Put Indexes in Flyway
Never manually create the index only on your machine।
Add a migration:
V5__add_order_history_index.sql
containing:
CREATE INDEX orders_customer_history_idx
ON orders (
customer_id,
created_at DESC,
id DESC
);
Now index creation follows the same migration history as the schema।
Why a New Migration?
Even if we "forgot" the index when creating orders, once the original migration is shared/applied we don't edit:
V3__create_orders.sql
We move forward:
V5__add_order_history_index.sql
Schema evolution remains append-only and reproducible।
Index Naming
Good:
orders_customer_history_idx
It communicates:
table
+
purpose
Other naming conventions are possible।
The important thing is consistency and readability।
Avoid:
idx1
which provides little value during debugging or migration review।
Should Index Name List Every Column?
Something like:
orders_customer_id_created_at_id_idx
is technically descriptive।
But:
orders_customer_history_idx
can communicate the actual access pattern more clearly।
Choose a team convention and remain consistent।
Indexing Status
Suppose admin later needs:
GET /orders?status=UNPAID
Should we immediately create:
INDEX(status)
?
Not necessarily।
We need to know:
How many Orders are UNPAID?
How often is this query executed?
Does it also filter/sort by other fields?
If:
60% Orders are UNPAID
a standalone status index may be weak।
Perhaps the real query is:
status = UNPAID
ORDER BY created_at DESC
Then a composite index might be more appropriate।
But we don't design that until the endpoint exists।
Indexing CustomerId Alone
Our composite:
(customer_id, created_at DESC, id DESC)
can also support queries beginning with:
customer_id
So adding another:
orders(customer_id)
index would probably be redundant for our current patterns।
Composite index design can sometimes replace several narrower indexes।
But Bigger Composite Indexes Are Not Always Better
Don't turn that into:
always add every possible column to one huge index
A wider index:
uses more storage
costs more to maintain
may not match other query patterns
Choose columns because they serve a real query।
Covering Queries
Sometimes an index contains enough columns for PostgreSQL to answer much of a query without visiting every table row.
This can be useful, but deliberately engineering covering indexes is an optimization technique。
Our current goal is not to cram every response column into indexes।
Start with:
filtering
joining
ordering
requirements।
Only optimize further after measurement։
SELECT * and Query Cost
Repository queries often default conceptually to:
select whole Entity
But collection endpoints may not need every column or relationship।
For Order history, a projection can reduce:
data loaded from PostgreSQL
entity materialization
relationship risk
Indexing and projections work together as query-performance tools।
Indexes Cannot Fix Bad Query Shape
Suppose we do:
load every Order
→ Java filters by CustomerId
Adding an index to customer_id does nothing if our query never sends:
WHERE customer_id = ?
to PostgreSQL।
The application must express the filter in SQL/JPA query for the database to use relevant indexes effectively।
Pagination and Offset Cost
Our v1 pagination uses:
page
size
which maps conceptually to:
LIMIT
OFFSET
Example:
page = 0
size = 20
→ OFFSET 0
But:
page = 100000
size = 20
→ OFFSET 2,000,000
Deep offset pagination can become expensive because the database may still need to traverse/skip a large number of entries before returning the requested rows।
Index Helps, But Doesn't Remove Deep Offset Cost
Our customer-history index can make ordering/filtering much more efficient।
But extremely deep pages still have inherent offset costs।
This is one reason cursor/keyset pagination can become attractive at high scale।
However our API intentionally chose:
page-based pagination
for v1।
We do not change architecture prematurely।
Performance Trade-off Is Accepted
Our current position:
page-based pagination
→ simpler public contract
→ good enough for v1
If actual usage later shows:
deep pagination is materially slow
we can evaluate:
cursor/keyset pagination
as a measured evolution।
Sorting Must Match Index Direction?
PostgreSQL indexes can support ordered scans, and direction can matter especially for multicolumn ordering patterns।
Our explicit:
created_at DESC
id DESC
in the index documents its alignment with the Order-history query।
The larger principle:
When ordering is part of an important bounded query, design the index with that ordering in mind.
Joins and Indexes
For relational joins:
orders
JOIN order_items
indexes on the relevant relationship keys can reduce lookup cost।
Our existing:
orders.id PK
and:
order_items(order_id, product_id) PK
already align well with:
load OrderItems for one Order
This is another reason good primary-key design can also improve common query access।
Product Browse Join
For:
products p
JOIN inventory i
ON i.product_id = p.id
both join keys are already primary-key-backed।
Before adding new Product/Inventory indexes, we should inspect the actual query plan and data distribution।
Don't assume the join itself is the problem।
Indexes and Updates
Consider:
UPDATE inventory
SET available_quantity = ...
WHERE product_id = ?;
product_id primary key makes row lookup efficient।
If we add:
available_quantity index
then every Inventory quantity change also updates that index։
Since quantity changes frequently, this write cost matters।
Index a Frequently Changing Column Carefully
Columns such as:
Inventory.availableQuantity
Order.status
may change over time।
Indexing them can still be correct when read benefits justify it।
But changing values means additional index maintenance।
This reinforces:
Index based on workload, not static schema appearance.
Query Performance Is More Than Indexes
Slow queries can come from:
N+1 queries
loading too many rows
unbounded collections
bad joins
unnecessary entity graphs
poor pagination
missing indexes
bad index choices
too many queries
database contention
Indexes solve only part of the performance problem।
N+1 Can Beat Any Index Optimization
Suppose Order history causes:
1 query for 20 Orders
20 queries for OrderItems
100 queries for Products
Even perfect individual indexes may still leave the endpoint inefficient due to excessive query count।
Always reason about:
number of round trips
+
cost of each query
not only single-query speed।
Log/Observe SQL When Needed
During persistence development, it can be useful to inspect generated SQL to answer:
How many queries ran?
Did Hibernate join what I expected?
Was pagination applied in PostgreSQL?
Did loading a relationship trigger extra queries?
But don't leave extremely verbose SQL logging enabled indiscriminately in production।
Production observability will be covered later।
Database Query Metrics
In a production environment, useful signals may include:
slow query duration
query frequency
database CPU
connection saturation
lock waits
The specific monitoring stack is outside this lesson।
The principle is:
Performance decisions should eventually use production evidence.
Indexes Don't Guarantee Use
Even a theoretically appropriate index may not be selected due to:
table size
statistics
data distribution
query shape
estimated cost
So after adding an important index:
inspect the query plan
rather than merely assuming the index solved the problem।
Statistics Matter
PostgreSQL's planner makes estimates based partly on database statistics।
If data distribution changes significantly, planner choices may change too।
Normal PostgreSQL maintenance handles much of this operationally, but backend engineers should understand that:
index exists
does not mean:
planner must use it
Don't Force Indexes From Application Code by Default
Some databases/tools provide ways to influence planner behaviour։
Avoid trying to force a specific index before understanding why the planner chose differently।
Often the real problem is:
bad query
weak selectivity
stale assumptions
wrong index shape
rather than planner incompetence।
Query Performance Workflow
A practical workflow:
1. Identify important query.
2. Understand required filtering/order/join.
3. Check existing keys/indexes.
4. Run with realistic data.
5. Inspect EXPLAIN / EXPLAIN ANALYZE.
6. Identify actual bottleneck.
7. Add/change index if justified.
8. Measure again.
This is much stronger than:
query feels slow
→ add random index
Example: Order History Before Index
Imagine:
10 million Orders
and query:
SELECT
id,
status,
created_at
FROM orders
WHERE customer_id = ?
ORDER BY created_at DESC, id DESC
LIMIT 20;
Without a useful customer-history index, PostgreSQL may need substantial scanning/filtering/sorting work।
After the Composite Index
With:
CREATE INDEX orders_customer_history_idx
ON orders (
customer_id,
created_at DESC,
id DESC
);
the database has a structure aligned with:
Customer scope
→ chronological traversal
This can dramatically reduce work for this access pattern at scale।
The exact benefit still depends on actual data distribution and planner decisions।
Example: Index That Looks Useful but Isn't
Suppose:
99.5% Products active
We add:
CREATE INDEX products_active_idx
ON products(active);
Then:
WHERE active = TRUE
still needs almost every Product।
The planner may prefer a sequential scan।
The index exists, consumes storage, adds write cost—and may provide almost no benefit for the main query।
This is why selectivity matters։
Example: Redundant Index
Existing:
orders_customer_history_idx
(customer_id, created_at, id)
Then someone adds:
orders_customer_idx
(customer_id)
for the same customer-history endpoint।
The broader composite index already begins with:
customer_id
and may serve that lookup।
The second index may simply add maintenance cost।
Always review overlap।
Example: Future Product Order Lookup
Suppose later requirement:
Find all historical Orders containing Product X
Query:
SELECT ...
FROM order_items
WHERE product_id = ?;
Current primary key:
(order_id, product_id)
is not optimized for Product-only lookup।
At that point a real index:
CREATE INDEX order_items_product_idx
ON order_items(product_id);
would have a clear reason।
Before that requirement, it is speculative।
Index Review in Pull Requests
When a PR adds an index, reviewer should ask:
Which endpoint/query requires this?
What is the query shape?
What index currently exists?
Why is this index not redundant?
How often is the table written?
How large is the table expected to be?
Was the query plan checked?
Could a better query eliminate the need?
An index should have an engineering explanation।
Migration Review Matters Too
Adding an index to a large production table can itself consume:
CPU
I/O
time
locking/resources
Index creation strategy during production deployment may require operational planning।
Our initial database is new and small, so a normal migration is fine।
Later large-table index changes should be treated as production changes, not trivial DDL।
Don't Build for Imaginary Billion-Row Scale
Our course teaches production thinking, not speculative architecture।
We should recognize:
deep pagination
large index builds
table growth
without immediately adding:
partitioning
sharding
read replicas
None of those are current requirements।
First build the correct relational application।
Scale when evidence demands it।
Current Index Plan
From our current confirmed queries:
Already provided by keys
products.id
→ Product lookup
inventory.product_id
→ Inventory lookup + Product join
orders.id
→ Order lookup
order_items(order_id, product_id)
→ OrderItem uniqueness
→ load items by Order
Additional Index We Can Justify
CREATE INDEX orders_customer_history_idx
ON orders (
customer_id,
created_at DESC,
id DESC
);
Purpose:
customer-scoped Order history
deterministic newest-first pagination
Indexes We Do Not Add Yet
No confirmed need yet for:
products(active)
products(price)
inventory(available_quantity)
orders(status)
orders(created_at)
order_items(product_id)
This does not mean those indexes are always bad।
It means current requirements do not justify them independently yet।
Product Browse Index Is Deferred
We know Product browse needs:
active + available Inventory
but exact Product sort and actual scale/distribution are not yet established।
Existing PK indexes already support the join keys।
So we implement the query correctly first, measure it, and add an additional browse-oriented index only if evidence justifies it।
Repository Responsibility
Repository/persistence layer should ensure:
filters reach PostgreSQL
ordering reaches PostgreSQL
pagination reaches PostgreSQL
joins are intentional
It should not expose:
"which index should Hibernate use?"
to UseCases।
Indexes are infrastructure optimization behind the Repository contract।
UseCase Should Not Know Index Names
Avoid:
createOrderUseCase.executeUsing(
"orders_customer_history_idx"
);
Obviously wrong।
Business logic knows:
find customer's Order history
Persistence/database decide how to execute that efficiently।
Indexes Should Not Change Business Semantics
Adding:
orders_customer_history_idx
should change:
performance
not:
results
If adding/removing an index changes which Orders the client sees, our query lacks deterministic semantics or contains a bug।
Indexes optimize contracts—they do not define them।
Performance Tests vs Functional Tests
Functional integration test:
Customer A receives only Customer A's Orders.
Performance investigation asks:
Does this remain efficient at realistic scale?
Both matter but solve different problems।
Do not make normal unit tests assert:
query must finish in 7 milliseconds
on arbitrary CI hardware।
Performance evaluation needs appropriate methodology।
Repository Integration Tests Can Still Protect Query Shape
We can test:
pagination returns bounded data
ordering is deterministic
ownership scope works
Product browse excludes inactive/out-of-stock Products
Then separately inspect query performance/plans where needed।
Correctness first, optimization second।
Common Mistake 1 — Index Every Foreign Key Automatically
Foreign-key indexing decisions should consider actual query/access needs and existing composite indexes।
Common Mistake 2 — Index Every Filter Column
Low-selectivity filters such as booleans may not benefit from standalone indexes।
Common Mistake 3 — Duplicate Primary-Key Index
Primary keys already provide indexed identity support।
Common Mistake 4 — Ignore Composite Index Order
(A, B) is not equivalent to separate indexes on A and B।
Common Mistake 5 — Add customer_id Index Beside a Better Customer-History Composite Index
Overlapping indexes create unnecessary maintenance।
Common Mistake 6 — Indexes Used to Compensate for In-Memory Filtering
The query must push filtering/pagination into PostgreSQL first।
Common Mistake 7 — Everything Made EAGER Then "Optimized" With Indexes
Excessive query volume/data loading is often an ORM/query-shape problem, not an index problem।
Common Mistake 8 — Sequential Scan Treated as Always Bad
For small tables or broad queries, it may be the correct plan।
Common Mistake 9 — Index Added Without EXPLAIN
Measure the plan instead of assuming the optimizer will use what you created।
Common Mistake 10 — Read Performance Optimized While Ignoring Write Cost
Every index has storage and maintenance overhead।
Index Design Checklist
For every candidate index, ask:
Which exact query needs this?
What does WHERE filter by?
What does JOIN use?
What does ORDER BY require?
Does a primary/unique/composite key already help?
How selective are the leading columns?
Does composite column order match the query?
Could this index be redundant?
How often does this table change?
How much data is returned?
Has the query plan been measured?
Does the public API actually require this query?
Query Performance Checklist
When an endpoint is slow:
How many SQL queries does one request execute?
Is there an N+1 problem?
Are rows filtered in PostgreSQL or Java?
Is pagination applied in PostgreSQL?
Are we selecting unnecessary columns?
Are relationships loaded unnecessarily?
Does the query use deterministic ordering?
What does EXPLAIN show?
Would an index help this specific plan?
Did performance improve after the change?
Our First Index Migration
After the initial schema migrations:
V1__create_products.sql
V2__create_inventory.sql
V3__create_orders.sql
V4__create_order_items.sql
we can add:
V5__add_order_history_index.sql
with:
CREATE INDEX orders_customer_history_idx
ON orders (
customer_id,
created_at DESC,
id DESC
);
This migration has a clear connection to:
GET /api/v1/orders?page=0&size=20
for the authenticated Customer।
Engineering Principle
The core principle:
Indexes should be designed from real query patterns, not from a list of columns.
Another:
An index trades write cost, storage, and maintenance for potentially faster reads; more indexes are not automatically better.
And:
Before optimizing with indexes, make sure the application sends the right bounded, filtered, and ordered query to PostgreSQL in the first place.
Summary
In this lesson, we learned that:
- Index design begins with actual application queries rather than database columns.
- Primary keys and unique constraints already provide useful index structures, so duplicate indexes should be avoided.
- Product, Inventory, and Order primary-key lookups already have appropriate indexed identity support.
- The composite OrderItem primary key
(order_id, product_id)naturally supports loading all OrderItems for a specific Order. - Composite-index column order matters;
(order_id, product_id)is not equivalent to a Product-first index. - We do not currently need a separate
order_items.product_idindex because Product-based Order lookup is not a current requirement. - Customer Order history is a clear high-value query requiring
customer_idfiltering pluscreated_at DESC, id DESCordering. - A composite
orders(customer_id, created_at DESC, id DESC)index is justified by that query. - A separate
orders(customer_id)index would likely be redundant for the current access pattern. - Boolean or otherwise low-selectivity fields should not be indexed blindly.
products.activeandinventory.available_quantitydo not automatically deserve standalone indexes just because Product browsing filters on them.- Product browse indexing remains dependent on its final ordering, real dataset, and measured query plan.
- Foreign keys and indexes solve different problems.
- Referencing foreign-key columns may need indexes depending on actual queries and relationship operations, but not simply because the foreign key exists.
- Indexes consume storage and add work to
INSERT,UPDATE, andDELETE. - Frequently changing Inventory and Order state makes unnecessary indexes particularly costly.
- PostgreSQL's query planner chooses execution strategies based on estimated cost; the existence of an index does not force its use.
- Sequential scans are not inherently bad.
EXPLAINhelps inspect the planned query strategy, whileEXPLAIN ANALYZEexecutes the query and reports actual execution behaviour.- Performance should be evaluated using realistic data volumes and distributions.
- Indexes cannot fix application-level problems such as N+1 queries, unbounded reads, in-memory filtering, or unnecessary entity loading.
- Page-based offset pagination remains our v1 contract, while extremely deep offsets may eventually justify cursor/keyset pagination if real measurements demand it.
- Indexes belong in Flyway migrations and should evolve through normal schema history.
- Every proposed index should have a specific query and measurable reason behind it.
Next lesson:
Pagination at the Database Layer
There we will connect our API's page and size contract to actual PostgreSQL queries, examine LIMIT/OFFSET, stable ordering, count queries, Spring Data Pageable, and ensure we never load the full dataset into Java before paginating.