Database Joins, Relationships, and Query Optimisations
On this page 18
Part V — Data · Interview reference
Relational literacy for seniors: model relationships correctly, choose join strategies knowingly, read query plans, and optimize with indexes and schema — not guesswork.
Relationships
| Relationship | Modeling | Notes |
|---|---|---|
| 1:1 | Shared PK or unique FK | Often merge tables unless lifecycle/security differs |
| 1:N | FK on the N side | Most common |
| M:N | Join (association) table | Two FKs; often has payload attrs (quantity, role) |
| Self-referential | FK to same table | Trees/org charts; watch recursive queries |
| Polymorphic | Type + id | Avoid if possible; hard to FK-constrain |
Integrity: PK, FK, UNIQUE, CHECK, NOT NULL — enforce in DB when invariants are real (don’t rely only on app).
Cardinality drives join cost estimates — wrong assumptions → bad plans.
Production case study (high volume)
Context: Multi-tenant SaaS billing schema: accounts 1:N subscriptions M:N features via join table; reporting joined incorrectly and exploded rows before DISTINCT.
Why seniors care: Wrong cardinality estimates → nested loop over millions; M:N without payload clarity causes double-counting revenue; integrity in DB beats app-only checks at scale.
Failure / symptom: EXPLAIN shows row estimates off by 100×; reporting CPU pegs primary; finance mismatches.
Resolution: Enforce FKs/UNIQUEs; rewrite with EXISTS/semi-joins; ANALYZE; materialize daily facts; separate OLTP vs OLAP.
Seen at / similar to: Stripe Billing-style schemas; Salesforce multi-tenant patterns; Shopify order line items.
Join types (logical)
| Join | Keeps |
|---|---|
| INNER | Matching rows only |
| LEFT/RIGHT OUTER | All from preserved side + matches |
| FULL OUTER | All from both |
| CROSS | Cartesian product |
SEMI (EXISTS) | Rows from A with match in B (no duplication from B) |
ANTI (NOT EXISTS) | Rows from A with no match in B |
Interview trap: NOT IN with NULLs ≠ anti-join you wanted. Prefer NOT EXISTS.
Physical join algorithms (optimizer)
| Algorithm | Character | Typical when |
|---|---|---|
| Nested loop | For each outer row, probe inner | Small outer; inner indexed |
| Hash join | Build hash on one side; probe | Larger equi-joins; memory for hash |
| Merge join | Both sides sorted on join key | Sorted inputs / ordered indexes |
Seniors: “I don’t pick the algorithm — I shape data/indexes so the optimizer can.”
Production case study (high volume)
Context: Ride-hailing trip history query joining trips ↔ riders ↔ payments on Postgres (~billions of trip rows, partitioned by time).
Why seniors care: Missing index on FK → nested loop disaster; hash join memory spills under peak analytics; seniors read EXPLAIN (ANALYZE, BUFFERS) not vibes.
Failure / symptom: p99 API cliffs for “trip receipt”; temp file spikes; seq scans on hot partitions.
Resolution: Index FKs used in joins; constrain time range for partition prune; raise stats targets; avoid functions on indexed cols; compare nested loop vs hash after rewrite.
Seen at / similar to: Uber schema/partition stories; Amazon order history; GitHub/GitLab large Postgres.
Indexes — decision criteria
| Index type | Use |
|---|---|
| B-Tree (default) | Equality + range; ORDER BY support |
| Hash | Equality only (engine-specific) |
| Composite | Leftmost prefix rule matters |
| Covering / INCLUDE | Index-only scans |
| Partial | Hot subset (WHERE active) |
| Unique | Correctness + lookup |
| GIN/GiST/etc. | JSON, arrays, full-text (Postgres) |
Write cost: every index slows inserts/updates; unused indexes are pure debt.
Selectivity: indexing low-cardinality columns alone (boolean) rarely helps unless composite/partial.
Query optimisation playbook
- Measure — slow query log, APM,
EXPLAIN (ANALYZE, BUFFERS) - Read the plan — seq scan vs index; join type; row estimate vs actual
- Fix estimates — ANALYZE stats; rewrite selective predicates; avoid wrapping indexed cols in functions
- Reduce work — selective filters early; avoid
SELECT *; paginate keyset notOFFSETlarge N - Shape schema — denormalize carefully; materialized views; partitioning
- Concurrency — isolation level; lock waits; HOT updates / fillfactor (engine-specific)
N+1 queries
ORM classic: 1 query for parents + N for children. Fix: join/fetch join / batch IN / DataLoader pattern.
Pagination
| Approach | Issue |
|---|---|
OFFSET/LIMIT | Deep pages scan/discard huge work |
Keyset (WHERE (created, id) < (?, ?) ORDER BY ... LIMIT) | Stable, scalable |
Sargability
Bad: WHERE YEAR(created) = 2024
Better: WHERE created >= '2024-01-01' AND created < '2025-01-01'
Production case study (high volume)
Context: E-commerce admin “orders list” used ORM N+1 (1 query per line item) and OFFSET 100000 for deep pages during peak support load.
Why seniors care: N+1 turns 1 user action into thousands of QPS against primary; OFFSET deep pagination burns IO; hot row counters (inventory) create lock contention.
Failure / symptom: DB CPU 100%; Hikari pool wait; APM shows repeated identical SELECTs; support UI timeouts.
Resolution: join fetch / batch IN; keyset pagination; covering indexes matching WHERE+ORDER BY; shard or async aggregate hot counters; EXPLAIN ANALYZE in CI for critical queries.
Seen at / similar to: Shopify/Magento ORM incidents; Twitter early RDBMS scaling; Instagram/Facebook keyset pagination lore; Amazon inventory hot SKUs.
Java under the hood
| Layer | What runs underneath |
|---|---|
| JDBC | PreparedStatement — parameterized SQL (injection-safe); drivers may cache server-side plans |
| Pooling | HikariCP: ConcurrentBag of connections; size must match app thread/virtual-thread concurrency |
| Transactions | Connection.setAutoCommit(false) / Spring @Transactional → begin/commit/rollback; isolation via Connection.setTransactionIsolation |
| Hibernate/JPA | 1st-level cache = identity Map in persistence context; N+1 from lazy collections — fix with join fetch, entity graphs, @BatchSize |
| jOOQ / MyBatis | Explicit SQL; still need indexes + EXPLAIN |
Physical join algorithms live in the database, not the JVM — Java shapes SQL, binds parameters, and avoids chatty ORM access patterns.
Transactions & isolation (join/optimisation adjacent)
| Level | Phenomena prevented (simplified) |
|---|---|
| Read uncommitted | Almost never use |
| Read committed | Dirty reads (default many DBs) |
| Repeatable read | Non-repeatable (engine nuances) |
| Serializable | Strongest; more aborts |
Know your engine (Postgres MVCC vs MySQL InnoDB gap locks). Long transactions → bloat / lock pain.
Production case study (high volume)
Context: Banking ledger transfer held open transactions while calling external KYC HTTP inside @Transactional.
Why seniors care: Long transactions block vacuums / hold locks; idle-in-transaction kills throughput; isolation choice changes phantom phenomena under concurrent settlements.
Failure / symptom: idle in transaction spikes; table bloat; lock waits; autovacuum falling behind.
Resolution: Short transactions; do IO outside; appropriate isolation; statement timeouts; monitor bloat and lock age.
Seen at / similar to: Banking cores; Square/Stripe ledger discussions; Postgres high-churn SaaS (Notion/Figma-scale public engineering themes).
Denormalization & tradeoffs
| Normalize | Denormalize |
|---|---|
| Integrity, less duplication | Read speed, fewer joins |
| Update in one place | Update anomalies; sync jobs |
Senior judgment: denormalize from measured join pain or clear read-model needs (CQRS), not by default.
What interviewers probe
- Draw a schema for a domain; justify FKs and M:N table.
- INNER vs LEFT — given a reporting requirement.
- Read an EXPLAIN sketch — why seq scan? missing index? low selectivity?
- Composite index order for
WHERE a=? AND b=? ORDER BY c. - Fix N+1 in an ORM discussion.
- Design pagination for infinite scroll at scale.
- When to denormalize / add cache / add read replica
- Hot row updates (counters) — mitigation (sharding counter, aggregate async).
- N+1 + OFFSET under production APM — what you change first.
Senior-level expectation: Plan-driven optimization; correctness of relationships; awareness of stats and write amplification.
Pitfalls
- Indexing every column “just in case”
- Implicit type casts defeating indexes (string vs int FK)
- Joining on non-unique keys → row explosion then DISTINCT hack
- Using
SELECT *through views obscuring cost - Ignoring vacuum/autovacuum / table bloat (Postgres)
- Cross-service joins via application loops without bounds
- HTTP/RPC inside open DB transactions
- Deep
OFFSETon multi-million-row tables
Cross-references
- Data structures — B-trees
- Algorithms
- Microservices rules — no cross-DB joins
- Kafka and event-driven — outbox/CDC
- AWS services — RDS, Aurora