🗄️ Section 1 · 21 questions

Databases SQL PostgreSQL

21 interview questions and answers in the Databases SQL PostgreSQL section.

📄 Source Markdown

Questions in this section

  1. What are indexes and why are they needed
  2. How does B-tree index work
  3. What is a composite index
  4. When should you create an index
  5. What are the disadvantages of indexes
  6. What is index cardinality
  7. What JOIN types exist
  8. Difference between INNER JOIN and LEFT JOIN
  9. What is better JOIN or subquery
  10. What is a correlated subquery
  11. Difference between WHERE and HAVING
  12. What does GROUP BY do
  13. When to use HAVING
  14. What are Window Functions
  15. What does ROW_NUMBER() do
  16. What does RANK() and DENSE_RANK() do
  17. How does MVCC work in PostgreSQL
  18. What is VACUUM in PostgreSQL
  19. Why is ANALYZE needed
  20. What is Explain plan
  21. How to optimize slow queries

Study navigator

21 core interview questions for Junior, Middle, and Senior Java Developer positions.

📋 All Questions

# Question Difficulty Level
1 What are indexes and why are they needed ⭐⭐
2 How does B-tree index work ⭐⭐⭐
3 What is a composite index ⭐⭐
4 When should you create an index ⭐⭐
5 What are the disadvantages of indexes ⭐⭐
6 What is index cardinality ⭐⭐⭐
7 What JOIN types exist ⭐⭐⭐
8 Difference between INNER JOIN and LEFT JOIN ⭐⭐
9 What is better JOIN or subquery ⭐⭐⭐
10 What is a correlated subquery ⭐⭐⭐
11 Difference between WHERE and HAVING ⭐
12 What does GROUP BY do ⭐⭐
13 When to use HAVING ⭐
14 What are Window Functions ⭐⭐⭐
15 What does ROW_NUMBER() do ⭐⭐
16 What does RANK() and DENSE_RANK() do ⭐⭐
17 How does MVCC work in PostgreSQL ⭐⭐⭐
18 What is VACUUM in PostgreSQL ⭐⭐⭐
19 Why is ANALYZE needed ⭐⭐⭐
20 What is Explain plan ⭐⭐
21 How to optimize slow queries ⭐⭐⭐

🗺️ Topic Dependency Map

                    ┌─────────────────────────────────────────┐
                    │   21. Query Optimization (Central)      │
                    │   pg_stat_statements → EXPLAIN → Fix    │
                    └──────┬──────────┬──────────┬────────────┘
                           │          │          │
              ┌────────────┘          │          └────────────┐
              ▼                       ▼                       ▼
    ┌─────────────────┐    ┌─────────────────────┐    ┌──────────────────┐
    │ INDEXES (1-6)   │    │ JOINs (7-10)        │    │ PLANNER          │
    │ 1. Why needed   │    │ 7. JOIN types       │    │ 19. ANALYZE      │
    │ 2. B-tree       │    │ 8. INNER vs LEFT    │    │ 20. EXPLAIN      │
    │ 3. Composite    │    │ 9. JOIN vs subquery │    │                  │
    │ 4. When create  │    │ 10. Correlated      │    └────────┬─────────┘
    │ 5. Disadvantages│    │     subquery        │             │
    │ 6. Cardinality  │   └─────────────────────┘             │
    └────────┬────────┘                                       │
             │                                                │
             ▼                                                ▼
    ┌──────────────────────────────────────────────────────────────────┐
    │                MVCC and MAINTENANCE (17-18)                      │
    │  17. MVCC (xmin/xmax, HOT, Hint Bits, XID Wraparound)           │
    │  18. VACUUM (dead tuples, Bloat, pg_repack, autovacuum)         │
    └──────────────────────────────────────────────────────────────────┘

    ┌──────────────────────────────────────────────────────────────────┐
    │                GROUPING and WINDOW FUNCTIONS (11-16)             │
    │  11. WHERE vs HAVING                                             │
    │  12. GROUP BY (HashAggregate, GroupAggregate)                    │
    │  13. When to use HAVING                                          │
    │  14. Window Functions (ROWS, RANGE, FILTER)                      │
    │  15. ROW_NUMBER()                                                │
    │  16. RANK() / DENSE_RANK()                                       │
    └──────────────────────────────────────────────────────────────────┘

🎯 Recommended Study Order

🟢 Junior Level (Weeks 1-2)

Step Topic Files Goal
1 Index fundamentals Q1, Q4, Q5 Why needed, when to create, write overhead
2 JOIN fundamentals Q7, Q8 JOIN types, INNER vs LEFT semantics
3 WHERE / HAVING / GROUP BY Q11, Q12, Q13 Execution order, aggregation basics
4 MVCC basics Q17 (Junior) Non-blocking reads, UPDATE = DELETE + INSERT
5 VACUUM basics Q18 (Junior) Dead tuples, standard VACUUM vs FULL
6 EXPLAIN basics Q20 (Junior) Reading query plans, scan types
7 ROW_NUMBER, RANK Q15, Q16 (Junior) Row numbering and ranking

🟡 Middle Level (Weeks 3-4)

Step Topic Files Goal
1 B-tree internals Q2, Q3, Q6 Tree structure, leftmost prefix, cardinality
2 Physical JOIN nodes Q7 (Middle/Senior) Nested Loop, Hash Join, Merge Join
3 Subqueries & CTEs Q9, Q10 Semi-Join, SubPlan vs InitPlan, decorrelation
4 Window functions Q14, Q15, Q16 Framing (ROWS vs RANGE), FILTER, indexing
5 VACUUM details Q18 (Middle) Autovacuum thresholds, scale factors, costing
6 ANALYZE & Stats Q19 pg_statistic, n_distinct, MCV, correlation
7 Query optimization Q21 Systematic tuning workflow, common pitfalls

🔴 Senior Level (Weeks 5-6)

Step Topic Files Goal
1 B-tree deep dive Q2 (Senior) Lehman-Yao, High Key, deduplication (PG 13+)
2 Advanced JOIN tuning Q7 (Senior) GEQO, Join Reordering, Memoize (PG 14+)
3 LATERAL & Memoization Q9, Q10 (Senior) Lateral joins, Memoize hit/miss, CTE inlining
4 MVCC architecture Q17 (Senior) Hint Bits, HOT, SSI, CLOG, XID Wraparound
5 Engine maintenance Q18 (Senior) Visibility Map, Parallel VACUUM, pg_repack
6 Statistics deep dive Q19 (Senior) Extended statistics, reservoir sampling
7 EXPLAIN deep dive Q20 (Senior) Generic vs Custom plans, plan cache, JIT, WAL
8 System architecture Q21 (Senior) Connection pooling, partitioning, denorm

🔗 Key Connections Between Topics

Topic: Indexes

Q1 (Why needed) → Q2 (B-tree) → Q3 (Composite) → Q4 (When) → Q5 (Disadvantages) → Q6 (Cardinality)
                                              ↓
                                    Q21 (Query Optimization)

Key connections:

  • Q1 ↔ Q17 (MVCC): HOT updates are impossible on indexed columns
  • Q2 ↔ Q18 (VACUUM): B-tree bloat → REINDEX CONCURRENTLY
  • Q3 ↔ Q6 (Cardinality): Column ordering determined by selectivity and query predicates
  • Q4 ↔ Q19 (ANALYZE): Table statistics determine whether the planner chooses an index
  • Q5 ↔ Q21 (Optimization): Write amplification and balancing index overhead

Topic: JOINs and Subqueries

Q7 (JOIN types) → Q8 (INNER vs LEFT) → Q9 (JOIN vs subquery) → Q10 (Correlated)
                                                    ↓
                                          Q20 (EXPLAIN: check for SubPlan)

Key connections:

  • Q7 ↔ Q20 (EXPLAIN): Physical execution strategies (Nested Loop, Hash, Merge)
  • Q8 ↔ Q11 (WHERE vs HAVING): Filtering right-side outer tables in ON vs WHERE
  • Q9 ↔ Q10: Semi-Join transformations, decorrelation, SubPlan vs InitPlan
  • Q10 ↔ Q14 (Window Functions): Window functions as an optimized replacement for correlated subqueries

Topic: GROUP BY and Window Functions

Q11 (WHERE vs HAVING) → Q12 (GROUP BY) → Q13 (When HAVING)
                                                ↓
                                    Q14 (Window Functions) → Q15 (ROW_NUMBER) → Q16 (RANK)

Key connections:

  • Q11 ↔ Q12: WHERE filters before GROUP BY, HAVING filters after aggregation
  • Q12 ↔ Q14: Window functions retain individual rows (unlike GROUP BY)
  • Q13 ↔ Q14: Window functions evaluate after HAVING → wrapping in a subquery/CTE is required to filter
  • Q14 ↔ Q15, Q16: ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK

Topic: MVCC and Engine Maintenance

Q17 (MVCC) → Q18 (VACUUM) → Q19 (ANALYZE) → Q20 (EXPLAIN) → Q21 (Optimization)

Key connections:

  • Q17 ↔ Q18: MVCC generates dead tuples upon UPDATE/DELETE → VACUUM reclaims them
  • Q18 ↔ Q19: Autovacuum daemon triggers ANALYZE when update thresholds are reached
  • Q19 ↔ Q20: Stale catalog statistics lead to suboptimal execution plans
  • Q20 ↔ Q21: EXPLAIN (ANALYZE, BUFFERS) is the primary tool for query bottleneck diagnosis

🎓 Cheat Sheet: What to Know for Each Level

🟢 Junior

  • What an index is and why it speeds up reads while penalizing writes
  • Logical JOIN types (INNER, LEFT, RIGHT, FULL, CROSS)
  • WHERE vs HAVING: when to filter individual rows vs aggregated groups
  • GROUP BY and standard aggregate functions (COUNT, SUM, AVG, MIN, MAX)
  • MVCC core principle: readers never block writers, writers never block readers
  • VACUUM: dead tuples cleanup, difference between standard non-blocking VACUUM and VACUUM FULL
  • EXPLAIN: how to identify Seq Scan vs Index Scan

🟡 Middle

  • B-tree page hierarchy, composite indexes, and the leftmost prefix rule
  • Physical JOIN algorithms: Nested Loop (index-driven), Hash Join (in-memory hash table), Merge Join (sorted inputs)
  • EXISTS vs IN vs JOIN, SubPlan vs InitPlan execution
  • Window functions: ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD, window framing (ROWS vs RANGE)
  • Autovacuum configuration: thresholds, scale factors, and cost-based throttling
  • ANALYZE: pg_statistic, n_distinct, Most Common Values (MCV) lists, histogram bounds
  • EXPLAIN (ANALYZE, BUFFERS): reading actual execution time, shared buffer hits/reads, filter loss

🔴 Senior

  • Lehman-Yao B-tree concurrency, High Key routing, deduplication (PG 13+), Skip Scan emulation via recursive CTEs
  • Join order search (Genetic Query Optimizer - GEQO), outer join removal, Memoize node (PG 14+)
  • MVCC internals: xmin/xmax/ctid, Hint Bits, Heap-Only Tuples (HOT), SSI (Serializable Snapshot Isolation), XID Wraparound freeze
  • Maintenance engineering: Visibility Map, index-only scans, parallel vacuuming, zero-downtime table packing (pg_repack)
  • Extended statistics (CREATE STATISTICS) for correlated multi-column distributions
  • Plan caching: Generic vs Custom plans, plan_cache_mode, JIT compilation overhead, WAL write amplification
  • System-level architecture: connection poolers (pgBouncer), declarative table partitioning, selective denormalization

📝 Answer File Architecture

Every answer file is strictly structured across 4 distinct sections:

  • 🟢 Junior Level — Core definitions, intuitive analogies, baseline SQL examples
  • 🟡 Middle Level — Physical internals, planner behavior, common pitfalls, and practical best practices
  • 🔴 Senior Level — Deep architectural mechanics, edge cases, benchmarks, production scenarios, and monitoring queries
  • 🎯 Interview Cheat Sheet — High-yield takeaways, common follow-up questions, red flag answers to avoid, and cross-referenced markdown links