Databases & Caching Interview Prep

SQL interview questions are unavoidable in any backend interview — joins (inner, outer, cross, self), subqueries vs joins, window functions, indexes (B-tree vs hash, when an index hurts), the EXPLAIN plan, transaction isolation levels, ACID, normalization, sharding, partitioning, replication, connection pooling, and migration tools (Flyway, Liquibase). This page covers cross-database SQL fundamentals plus the operational questions that decide a senior interview. For Postgres-specific deep dives (window functions, JSONB, MVCC, partitioning), use the dedicated PostgreSQL page; for MongoDB, see the MongoDB page; for Redis, see the Redis page.

Three lenses senior interviews probe:

• Query model — joins, subqueries, CTEs, window functions.
• Storage / index model — B-tree default, hash for equality, partial / covering indexes.
• Concurrency model — ACID + isolation levels (read-committed default in PG, repeatable-read default in MySQL InnoDB).

EXPLAIN is your evidence. Always read the plan, not the SQL.

The interview surface for this module breaks into these focus areas:

• Advanced sql features — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Backup recovery — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Comparisons — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Connection pooling — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Database migrations — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Indexes and performance — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Joins and subqueries — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Partitioning and sharding — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Query optimization — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Replication — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Sql fundamentals — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Transactions and acid — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Scenario-based — applied questions tying multiple topics together. Drill these last, after the fundamentals are sharp.

• Index doesn't help when — WHERE func(col), leading wildcard LIKE '%x', mismatched types, low selectivity.
• N+1 at the SQL layer is solved with JOIN or `IN (subquery)` — or aggregation in DB, not app.
• Phantom reads / lost updates — name them with the isolation level that prevents each.
• Sharding is a reorg, not an optimisation; mention it when read replicas + indexes have run out.
• Connection pooling — HikariCP defaults are fine; size = connections × instances against DB max.

"I'd write the query, run `EXPLAIN ANALYZE`, look for on the hot path, fix it with , and only after the query is tight do I think about ."

PostgreSQL interview questions go beyond generic SQL — MVCC and the PostgreSQL transaction model, JSONB indexing strategies, partitioning (declarative + inheritance), the rich window-function vocabulary, replication (streaming + logical), backup and PITR strategy, query planning with EXPLAIN ANALYZE, and the Spring Boot + HikariCP tuning patterns that surface in real interviews. This page is the one to use when the job description specifies Postgres (which is most of them in 2026).

Postgres = relational core + power-user extras.

• MVCC — readers don't block writers; vacuum reclaims dead tuples.
• JSONB — first-class document storage, indexable with GIN.
• Window functions, CTEs, recursive queries — keep complex logic in SQL.
• Partitioning — declarative since 10; range, list, hash.
• Replication — streaming (physical) + logical (selective tables).

The interview surface for this module breaks into these focus areas:

• Postgresql features — drill the named patterns, the failure modes, and the recommended Java / framework idiom.

• VACUUM is mandatory — long transactions block it; bloat kills performance.
• JSONB indexing — GIN with jsonb_ops vs jsonb_path_ops.
• `SERIAL` vs `IDENTITY` — IDENTITY is the modern, spec-compliant default.
• `SELECT FOR UPDATE SKIP LOCKED` — the classic queue-in-Postgres pattern.
• Hot standby — read-only replica; mention pg_basebackup and walsender.

"For Postgres I'd lean on because , watch for , and tune for the workload before reaching for sharding."

MongoDB interview questions cover the document data model in interview depth — schema design (embedded vs referenced), indexes (compound, multikey, text, geo), the aggregation pipeline, sharding and zone sharding, replica sets and read concerns, consistency trade-offs (eventual, causal, linearizable reads), and integration with Spring Data MongoDB. Expect senior interviews to compare MongoDB to a relational store and probe on operational concerns (rebalancing, backup, election).

MongoDB is a document store with a strong query language.

• Schema = embedded vs referenced — embed for read locality, reference for write boundaries.
• Aggregation pipeline = SQL's GROUP BY + window + projection, all in one.
• Indexes — single, compound, multikey (arrays), text, geo.
• Replica set — primary + secondaries; sharding for horizontal scale.
• Read concerns — local / majority / linearizable; pick by consistency need.

The interview surface for this module breaks into these focus areas:

• Comparisons — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Document model — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Elasticsearch basics — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Mongodb core — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Mongodb indexes — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Mongodb with spring — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Nosql fundamentals — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Nosql patterns — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Scenario-based — applied questions tying multiple topics together. Drill these last, after the fundamentals are sharp.

• The 16 MB document limit — rare in practice but interviewers love it.
• Compound index ordering matters — leftmost prefix wins.
• No joins — denormalise, or use $lookup knowing it's the perf cost.
• Multi-document transactions — supported in 4+ replica sets, but design for atomic per-document.
• Sharding key is permanent — picking it badly costs a re-sharding.

"I'd model the entity as because , index it on , run reads against secondaries with read concern , and shard on if writes outgrow a single replica set."

Redis and caching interview questions test whether you understand caching as a system, not just a library — Redis data structures (strings, hashes, lists, sets, sorted sets, streams, HyperLogLog), eviction policies (LRU, LFU, allkeys vs volatile), persistence (RDB, AOF), replication and Sentinel/Cluster modes, cache-aside vs read-through vs write-back, distributed locking (Redlock and its critics), and the common pitfalls (cache stampede, hot keys, big keys). This page is the one to use for any role that mentions Redis or distributed caching.

Redis is a single-threaded in-memory data structure server — speed comes from O(1) ops + simple ops.

• Data structures: strings / hashes / lists / sets / sorted sets / streams / HyperLogLog / bitmaps.
• Persistence: RDB (snapshot) + AOF (log) — combine for durability + recovery speed.
• HA: Sentinel for failover, Cluster for sharding.
• Cache patterns — cache-aside (default), read-through, write-back; pick by tolerance for stale data.

The interview surface for this module breaks into these focus areas:

• Cache invalidation — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Cache patterns — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Caching strategies — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Caffeine cache — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Comparisons — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Distributed caching — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Performance optimization — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Redis advanced — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Redis data structures — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Redis persistence — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Spring data redis — drill the named patterns, the failure modes, and the recommended Java / framework idiom.
• Scenario-based — applied questions tying multiple topics together. Drill these last, after the fundamentals are sharp.

• Cache stampede — solve with mutex / probabilistic early-expiry / request coalescing.
• Hot keys — split with hash tags or local read-replicas.
• Big keys are the silent latency killer — set a max size in code.
• Eviction — LRU/LFU per-key; volatile-* only on TTL-set keys.
• Distributed locking with Redlock — Martin Kleppmann's critique is fair-game knowledge.

"I'd cache via cache-aside with TTL , tolerate eventual consistency on writes via , pick the data structure as , and guard against stampede with ."