Week 5 — PostgreSQL: Beyond SELECT *

The textbook for this week

The deep version of this week lives in the SQL & Databases track. Day 1 (indexes) = SQL 06, Day 2 (transactions) = SQL 07, Day 3 (N+1 from the SQL side) = SQL 03 — Joins, Day 4 (query optimization) = SQL 06 + SQL 08 — the capstone. Work the modules; use this page as the weekly checklist.

Why This Week Matters Most

Every production performance issue I’ve seen in fintech traces back to the database. Slow queries, missing indexes, N+1 problems, transaction deadlocks. The developer who understands their database is worth 3x one who treats it as a black box.

Daily Breakdown

Day 1: How Indexes Actually Work

Not this: “Indexes make queries faster.”

This: “A B-tree index on email means PostgreSQL can find a user in O(log n) instead of scanning every row.”

Understand:

-- Without index: Sequential scan (reads EVERY row)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'darshan@example.com';
-- Seq Scan on users  (cost=0.00..25.00 rows=1 width=540)
-- Planning Time: 0.1ms  Execution Time: 2.5ms

-- Add index
CREATE INDEX idx_users_email ON users(email);

-- With index: Index scan (reads only the matching row)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'darshan@example.com';
-- Index Scan using idx_users_email  (cost=0.14..8.16 rows=1 width=540)
-- Planning Time: 0.1ms  Execution Time: 0.05ms

Run EXPLAIN ANALYZE on every query in your project today. Find the slow ones.

Index types you need to know:

Critical rule: The order of columns in a composite index matters. (group_id, created_at) helps WHERE group_id = 5 AND created_at > ... but NOT WHERE created_at > ... alone.

Day 2: Transactions — Money Requires ACID

In fintech, this is non-negotiable. If you transfer Rs. 500 from A to B:

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE user_id = 1;  -- Debit A
-- If THIS fails, the debit MUST be rolled back
UPDATE accounts SET balance = balance + 500 WHERE user_id = 2;  -- Credit B
COMMIT;

ACID properties (you WILL be asked in interviews):

Isolation levels (know at least these two):

Exercise: Create a scenario where two concurrent transactions cause a problem with READ COMMITTED, then fix it with proper locking.

Day 3: N+1 Problem — The Silent Killer

// This looks innocent
List<Group> groups = groupRepo.findAll();  // 1 query
for (Group g : groups) {
    g.getMembers().size();  // N queries (one per group!)
}
// Total: 1 + N queries. With 100 groups = 101 queries.

Fix with JOIN FETCH:

@Query("SELECT g FROM Group g JOIN FETCH g.members")
List<Group> findAllWithMembers();  // 1 query

Find and fix every N+1 in your project. Enable SQL logging and count the queries.

Day 4: Query Optimization

Build a practice dataset:

-- Insert 100K rows into your expenses table
-- Then practice optimizing real queries:

-- Slow: Full table scan
SELECT * FROM expenses WHERE group_id = 5 ORDER BY created_at DESC;

-- Fast: Composite index
CREATE INDEX idx_expenses_group_date ON expenses(group_id, created_at DESC);

-- Slow: Calculating balances with subqueries
-- Fast: Using window functions or materialized views

Learn to read query plans. EXPLAIN ANALYZE is your best friend.

Day 5: Apply to Your Project

Add to your project:

• Proper indexes on all foreign keys and frequently queried columns
• @Transactional on all service methods that modify data
• Fix any N+1 queries (check SQL logs)
• Add EXPLAIN ANALYZE results to your notes for key queries
• Add a balance calculation query that’s optimized

Weekend: DSA Practice

This weekend’s pattern: DSA 03 — Two Pointers — work the module (visualizer, quiz, Build This), then 5 problems from its list.

The Arrays & Hashing problems that used to live here (Two Sum, Group Anagrams, Top K Frequent Elements, Valid Anagram, Contains Duplicate) are now the problem list inside DSA 02 — Arrays & Hashing, which you did in Week 4. Behind schedule? Catch up there first — the module order matters.

One connection worth noticing: Day 1’s B-tree index doing O(log n) lookups is the same logarithm you measured in DSA 01. EXPLAIN ANALYZE is Big-O made visible.

Resources