Databases Senior interview questions and answers

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

Take this quiz
  1. 1.How does PostgreSQL decide whether a row version is visible to a transaction?
  2. 2.What is transaction ID wraparound, and why is it dangerous?
  3. 3.What is table bloat and what usually causes it?
  4. 4.What is the purpose of the write-ahead log (WAL)?
  5. 5.What does synchronous_commit = off trade away?
  6. 6.How do B-tree storage engines and LSM-tree storage engines differ for writes?
  7. 7.Why do row-oriented databases struggle with analytics queries over billions of rows?
  8. 8.Why can too many indexes hurt a write-heavy table beyond disk space?
  9. 9.What is write skew?
  10. 10.How does PostgreSQL implement the Serializable level?
  11. 11.How can you build a job queue on PostgreSQL that many workers poll safely?
  12. 12.What are advisory locks in PostgreSQL?
  13. 13.Many requests update the same counter row (UPDATE stats SET views = views + 1 WHERE id = 1). What's the problem and a fix?
  14. 14.Why should the uniqueness of an idempotency key be enforced with a unique constraint rather than a 'check, then insert'?
  15. 15.Why is a distributed lock in Redis (e.g. Redlock) not enough on its own to protect data correctness?
  16. 16.What is the trade-off between synchronous and asynchronous replication?
  17. 17.A user updates their profile and immediately sees the old data. Reads go to replicas. What's the issue and fix?
  18. 18.What does logical replication allow that physical streaming replication doesn't?
  19. 19.What is change data capture (CDC)?
  20. 20.What is 'split brain' in a failover setup?
  21. 21.What do RPO and RTO mean?
  22. 22.When does table partitioning in PostgreSQL help most?
  23. 23.What makes a good shard key?
  24. 24.Why is consistent hashing used in distributed data stores?
  25. 25.What problems appear once data is sharded across databases?
  26. 26.How can you generate unique IDs across shards without a central sequence?
  27. 27.What does the CAP theorem say?
  28. 28.What does 'eventual consistency' mean?
  29. 29.What is linearizability?
  30. 30.In a quorum system with N replicas, why do R + W > N reads and writes give up-to-date reads?
  31. 31.A query is fast in development but slow in production with the same indexes. What should you check first?
  32. 32.What problem can prepared statements with generic plans cause?
  33. 33.Which pitfalls come with PgBouncer in transaction pooling mode?
  34. 34.How do you safely backfill a new column on a table with 500 million rows?
  35. 35.How do you add a foreign key or NOT NULL check to a big table without blocking writes for a long time?
  36. 36.How can you count unique visitors over huge data sets cheaply when approximate results are fine?
  37. 37.What's a typical reason to cap max_connections in PostgreSQL and pool instead of raising it to thousands?
  38. 38.What are the trade-offs of 'shared tables with tenant_id' versus 'a schema or database per tenant' in a SaaS app?
  39. 39.What is event sourcing?
  40. 40.What is CQRS in the context of data storage?
  41. 41.Why is a 'dual write' (write to the database, then publish to Kafka) risky, and what's the alternative?
  42. 42.How would you model time-series data, like metrics every second from 10,000 devices, in PostgreSQL?
  43. 43.What is DynamoDB's single-table design?
  44. 44.Why is using Redis as the only store for important business data risky?
  45. 45.When does a search engine like Elasticsearch or OpenSearch make sense next to PostgreSQL?
  46. 46.What is a star schema used for?
  47. 47.How can you do a major PostgreSQL version upgrade with minimal downtime?
  48. 48.Why are logical dumps (pg_dump) alone not enough as a backup strategy for a large, busy database?
  49. 49.Which measures protect sensitive columns (e.g. personal data) in a database?
  50. 50.What does pg_stat_activity help you diagnose during an incident?
  51. 51.How should a team approach choosing a database for a new product?
  52. 52.What is the role of shared_buffers and the OS page cache in PostgreSQL performance?
  53. 53.Why might a query using OR across different columns be slow, and what can help?
  54. 54.What does the fillfactor storage setting do for a heavily updated table?
  55. 55.What are extended statistics (CREATE STATISTICS) for?
  56. 56.Why can a LIMIT query sometimes be slower than the same query without LIMIT?
  57. 57.What is a wide-column store like Cassandra designed for?
  58. 58.When does a graph database (like Neo4j) fit better than a relational one?
  59. 59.How do MongoDB write concerns and read concerns relate to consistency?
  60. 60.What is a vector database (or the pgvector extension) used for?
  61. 61.Why is it risky to rely on database-generated timestamps from different servers to order events?
  62. 62.What is the main risk of ON DELETE CASCADE chains in a large schema?
  63. 63.For WHERE tenant_id = $1 AND created_at > $2 ORDER BY created_at, which composite index order is best?
  64. 64.How can PostgreSQL prevent two overlapping bookings of the same room at the database level?
  65. 65.When is hash partitioning useful?
  66. 66.An order flow spans the orders, payments and shipping services, each with its own database. How do you keep them consistent without distributed transactions?
  67. 67.Long analytics queries on a streaming replica get cancelled with 'conflict with recovery'. Why?
  68. 68.What is a safe sequence for dropping a column in a system with rolling deploys?
  69. 69.Right after a database failover, latency spikes even though the new primary has the same hardware. What's a likely cause?
  70. 70.A user asks to delete all their personal data (GDPR). What's often overlooked?
  71. 71.Why should migrations be tested against a production-sized copy of the data?
  72. 72.After a deploy, hundreds of new app instances open connections at once and the database becomes unresponsive. What helps?
  73. 73.What is TOAST in PostgreSQL?
  74. 74.How do you keep a denormalized value like posts.comments_count correct under concurrency?
  75. 75.Why might a unique index on email be unsuitable once you add soft deletes?