Databases Middle interview questions and answers

75 questions for a Databases (Middle) interview - with correct answers, explanations and links to the docs.

Take this quiz
  1. 1.What data structure does a default PostgreSQL index use?
  2. 2.There's an index on (last_name, first_name). Which query can use it efficiently?
  3. 3.There's an index on created_at. Why might WHERE date(created_at) = '2026-11-01' ignore it?
  4. 4.Why can't a normal B-tree index speed up WHERE name LIKE '%son'?
  5. 5.What is a partial index?
  6. 6.What is a covering index and an index-only scan?
  7. 7.Why might PostgreSQL choose a sequential scan even though an index exists?
  8. 8.What does CREATE INDEX CONCURRENTLY give you?
  9. 9.Which index type fits searching inside a jsonb column, like WHERE data @> '{"tags": ["sale"]}'?
  10. 10.Why can random UUIDs (v4) as primary keys slow down inserts on large tables?
  11. 11.What is the difference between EXPLAIN and EXPLAIN ANALYZE?
  12. 12.In EXPLAIN ANALYZE the planner estimated 10 rows but got 500,000. What does it suggest?
  13. 13.Which join algorithms can PostgreSQL use?
  14. 14.Why can SELECT COUNT(*) FROM big_table be slow in PostgreSQL?
  15. 15.How do you find the queries that consume the most total time in production?
  16. 16.What is PostgreSQL's default transaction isolation level?
  17. 17.What is a 'non-repeatable read'?
  18. 18.What is a 'lost update', and how can you prevent it?
  19. 19.What does SELECT ... FOR UPDATE do?
  20. 20.What is a deadlock, and what does PostgreSQL do about it?
  21. 21.How does optimistic locking work?
  22. 22.What does INSERT ... ON CONFLICT (email) DO UPDATE do?
  23. 23.Why are long-running transactions harmful in PostgreSQL?
  24. 24.What is a savepoint?
  25. 25.What does a window function like ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) do?
  26. 26.What is the difference between RANK() and DENSE_RANK()?
  27. 27.How do you compute a running total of payments per user?
  28. 28.What is a CTE (WITH ...) good for?
  29. 29.What does WITH RECURSIVE let you do?
  30. 30.Why can WHERE id NOT IN (SELECT user_id FROM bans) return no rows at all?
  31. 31.What does LEFT JOIN LATERAL allow?
  32. 32.What is COUNT(*) FILTER (WHERE status = 'paid') used for?
  33. 33.When should you use jsonb columns in PostgreSQL?
  34. 34.What does PostgreSQL full-text search give you compared with ILIKE '%word%'?
  35. 35.What is the N+1 query problem?
  36. 36.Why is keyset (cursor) pagination faster than OFFSET for deep pages?
  37. 37.Why do applications use a connection pool?
  38. 38.How do you add a NOT NULL column with a default to a huge table without long locks in modern PostgreSQL?
  39. 39.What is a safe way to rename a column used by a running application?
  40. 40.What is a materialized view?
  41. 41.What is a common downside of putting business logic in triggers?
  42. 42.What does Row-Level Security (RLS) in PostgreSQL do?
  43. 43.Where should a 'unique email' rule be enforced?
  44. 44.How do you insert 10,000 rows efficiently?
  45. 45.What does MVCC mean in PostgreSQL?
  46. 46.What does VACUUM do?
  47. 47.Why does an UPDATE in PostgreSQL write a whole new row version?
  48. 48.In MongoDB, when should related data be embedded in one document rather than referenced?
  49. 49.What is MongoDB's aggregation pipeline?
  50. 50.Does MongoDB support multi-document transactions?
  51. 51.Which Redis data type fits a leaderboard?
  52. 52.What is the hardest part of caching database results?
  53. 53.What happens when Redis reaches its maxmemory limit?
  54. 54.What is a key difference between SQL and NoSQL scaling, as usually described?
  55. 55.What is third normal form (3NF), roughly?
  56. 56.How would you store a user's changing subscription plans so you can see the history?
  57. 57.What is polymorphic association, and what's its problem?
  58. 58.How should tags on articles usually be stored in a relational database?
  59. 59.Why store an order's price at purchase time instead of always joining to the current product price?
  60. 60.What is a read replica used for?
  61. 61.What is point-in-time recovery (PITR)?
  62. 62.Why can ALTER TABLE on a busy table freeze the whole application?
  63. 63.What is a database seed used for?
  64. 64.Why are integration tests against a real database (e.g. in Docker) often preferred over mocking the database?
  65. 65.What is a 'phantom read'?
  66. 66.What must an application do when using the Serializable isolation level in PostgreSQL?
  67. 67.What does SELECT DISTINCT ON (user_id) * FROM orders ORDER BY user_id, created_at DESC return?
  68. 68.What does GROUP BY ROLLUP (country, city) add to the result?
  69. 69.What is a generated column?
  70. 70.What are trade-offs of using a PostgreSQL ENUM type for a status column?
  71. 71.Why can ORDER BY created_at DESC LIMIT 10 be very fast on a huge table?
  72. 72.What is a 'Bitmap Heap Scan' in an EXPLAIN plan?
  73. 73.What does statement_timeout protect against?
  74. 74.Why is Redis INCR a good fit for a simple rate limiter?
  75. 75.What does a foreign key with ON DELETE RESTRICT (or the default NO ACTION) do when you delete a referenced row?