Database Architecture Interview Guide: MySQL, PostgreSQL, MongoDB, Redis & Scaling

Why Database Interviews Should Be Architecture Interviews

A production database is not simply a place where an application stores rows or documents. It is part of the consistency model, latency budget, failure model, scalability strategy and recovery plan of the system. At senior level I expect engineers to reason about access patterns, transactions, indexes, contention, replication and operational limits.

1. Start With the Access Pattern, Not the Database Brand

Before choosing MySQL, PostgreSQL, MongoDB or Redis I ask what the system actually does. What are the dominant reads? What writes must be atomic? How large is the working set? How quickly does data change? What are the retention requirements? Do queries need joins, geospatial operators, full text, document traversal or key-value access?

Business operation
      ↓
Data model
      ↓
Read/write patterns
      ↓
Consistency requirement
      ↓
Scale + failure model
      ↓
Database choice

2. What Is an Index Really Buying You?

An index reduces the amount of data the database must scan, but it costs storage and write work. Every additional index must be maintained during inserts and updates. The right question is not “should this column be indexed?” but “which query pattern does this index support?”

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at DESC);

The column order matters. A composite index designed for customer_id + created_at is not automatically optimal for every query involving those columns.

3. Why Can an Indexed Query Still Be Slow?

Common reasons include low selectivity, a function preventing index use, reading too many rows, sorting large intermediate sets, poor join order, stale statistics, random I/O or returning huge result payloads. I inspect the execution plan rather than guessing.

4. What Does ACID Mean in Practice?

Atomicity protects all-or-nothing work, consistency protects invariants, isolation controls interaction between concurrent transactions, and durability means committed data survives expected failures. The useful interview discussion is how these guarantees map to a real workflow such as moving money, reserving inventory or creating an order.

5. Which Isolation Level Should You Use?

There is no universally best isolation level. Stronger isolation can prevent anomalies but may reduce concurrency. The correct choice depends on business invariants and database behavior. For critical workflows I identify the anomaly we must prevent, then design the transaction explicitly.

6. What Is a Lost Update?

Two callers read the same value and both write based on that old state. The later write silently overwrites the earlier one. Optimistic locking with a version column is one common solution.

UPDATE inventory
SET quantity = quantity - 1,
    version = version + 1
WHERE product_id = 42
  AND version = 7
  AND quantity > 0;

If no row is updated, the caller knows the state changed concurrently.

7. Pessimistic or Optimistic Locking?

Pessimistic locking is useful when conflicts are likely and must be serialized. Optimistic locking works well when conflicts are uncommon and retries are acceptable. I also consider lock duration, deadlock risk and user-facing latency.

8. Why Do Deadlocks Happen?

Two transactions acquire resources in different orders and each waits for the other. Databases detect and abort one transaction. Applications should keep transactions short, access resources in consistent order where possible, and treat deadlock errors as a retryable database condition when the operation is safe to retry.

9. How Do You Size a Connection Pool?

At system level. If a Kubernetes deployment has 30 pods and every pod opens 40 connections, the theoretical total is 1,200 connections. The database may perform better with far fewer. Pool sizing should reflect database capacity, query time, concurrency and replica count.

10. Why Is “More Connections” Often Worse?

Connections consume database memory and create scheduling overhead. Too many concurrent queries can increase contention and latency. The pool should create backpressure instead of allowing unlimited demand to hit the database.

11. MySQL vs PostgreSQL

Both are production-grade relational databases. I often favor PostgreSQL when advanced SQL, PostGIS, complex data types or certain analytical capabilities are central. MySQL remains a very strong choice for high-volume transactional applications and may have significant operational advantages in organizations that already know how to run it well.

12. When Does MongoDB Fit Better?

MongoDB fits when the aggregate is naturally document-shaped, the schema evolves, and the dominant reads usually retrieve the document as a unit. It is not a replacement for relational modeling discipline. Large unbounded arrays, uncontrolled duplication and missing indexes can create serious problems.

13. When Does Redis Fit?

Redis is excellent for low-latency ephemeral data, caching, distributed counters and some coordination patterns. I do not make it the system of record merely because it is fast. Persistence, failover, memory pressure and cache invalidation need explicit design.

14. What Is a Read Replica?

A read replica receives changes from a primary and serves reads. It can scale read-heavy workloads and isolate reporting traffic, but replication lag means some reads may be stale. A workflow that writes an order and immediately reads from a lagging replica may not see its own write.

15. How Do You Handle Read-After-Write Requirements?

Options include reading critical follow-up requests from the primary, using session stickiness, tracking replication positions, or designing the UI to tolerate eventual visibility. The right answer depends on user expectation and platform capability.

16. What Is Sharding?

Sharding partitions data across multiple database nodes. It can increase scale but introduces routing, resharding, cross-shard query and transaction complexity. I do not shard before exhausting simpler options such as indexing, query optimization, vertical scaling, archiving and read replicas.

17. How Do You Pick a Shard Key?

A good key distributes workload, avoids hot partitions and aligns with common queries. A poor key can make one shard overloaded while others are idle. Tenant ID is sometimes useful in multi-tenant systems, but very large tenants may require an additional strategy.

18. What Is CQRS and When Is It Useful?

CQRS separates write and read models when the two have very different requirements. It can simplify complex read projections or independent scaling, but it adds eventual consistency and operational components. I avoid CQRS when ordinary relational queries are sufficient.

19. What Is the Outbox Pattern?

When a service must update a database and publish Kafka, two independent writes create failure windows. The transactional outbox stores the business change and event record in one local transaction. A publisher sends events asynchronously.

20. How Do You Guarantee Exactly-Once Business Effect?

Usually by combining at-least-once delivery with idempotent processing, unique constraints and transaction boundaries. “Exactly once” at one messaging layer does not automatically guarantee exactly one business side effect across databases and external systems.

21. How Would You Model Multi-Tenancy?

Common options include shared tables with a tenant column, schema per tenant or database per tenant. The choice affects isolation, cost, compliance, migrations and operational complexity. Row-level security can be useful in PostgreSQL, but application-level tenant isolation must still be carefully designed.

22. Why Are Unique Constraints Architecture Tools?

They are one of the strongest ways to enforce invariants under concurrency. If an email, external transaction ID or event ID must be unique, a database constraint is more reliable than “check then insert” code executed by many concurrent processes.

23. Why Should Transactions Be Short?

Long transactions hold locks, retain versions, increase contention and make failures more expensive. External HTTP calls should usually not happen inside a database transaction. Persist local state, commit, then coordinate external work using appropriate patterns.

24. What Causes Table Bloat or Storage Growth?

Updates, deleted data, indexes, retained history, transaction versions, logs and unbounded audit tables all matter. Data lifecycle and archival should be architecture decisions, not emergency cleanup projects.

25. How Do You Design Pagination?

Offset pagination is easy but can become slow and unstable on large, changing datasets. Keyset or cursor pagination uses the last seen sort key and often performs better for deep navigation.

SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ('2026-08-20 10:00:00', 9000)
ORDER BY created_at DESC, id DESC
LIMIT 50;

26. How Do You Approach a Slow Query Incident?

I look at business impact, recent deployments, query latency distribution, lock waits, pool saturation, CPU/I/O, execution plans and changes in data volume. I do not immediately add an index without validating the actual bottleneck.

27. Backup Is Not the Same as Recovery

A backup that has never been restored is only a hope. I define RPO and RTO, automate backups, test restores, protect credentials, and understand how point-in-time recovery works for the chosen database.

28. What Is RPO?

Recovery Point Objective describes how much data loss the business can tolerate. A five-minute RPO requires a different replication and backup strategy from a 24-hour RPO.

29. What Is RTO?

Recovery Time Objective describes how long the service can remain unavailable. RTO affects automation, standby architecture, operational runbooks and testing.

30. What Should a Senior Engineer Monitor?

Query latency, transaction rate, connections, lock waits, replication lag, cache hit ratio where relevant, storage growth, CPU, memory, I/O, slow queries, errors, backup status and business outcomes. Monitoring only infrastructure is not enough.

Architect's Decision Matrix

FAQ

Which database is best for microservices?

There is no universal database for microservices. Each service should use a data technology that matches its access patterns and operational environment, while the organization avoids unnecessary technology sprawl.

Is MongoDB faster than MySQL?

That comparison is too broad. Performance depends on data model, indexes, workload, consistency requirements and hardware. The correct comparison starts with a specific query and business requirement.

Should every service own its database?

Independent data ownership helps service autonomy, but database-per-service adds operational complexity. The principle is to avoid uncontrolled cross-service coupling, not to create databases mechanically.

Related Guides

• Kafka Backpressure in Production NestJS Systems
• Circuit Breaker, Retry and Timeout Patterns
• Production RAG Architecture