Katkat
Back

Defend a Redis Choice and Explain Database Index Trade-offs

SquarepointSoftware Engineer · HR Screen
MediumSoftware EngineerHR ScreenSoftware Engineering Fundamentals

Last updated

This question evaluates architectural decision-making around data access patterns, comparing the introduction of an in-memory cache like Redis against optimizing relational database indexing and query plans.

Scenario

You are a backend engineer on a trading infrastructure team maintaining an Order State Service. The service processes incoming trade orders and provides real-time lookups for order state, execution summaries, and trader positions via REST and WebSocket endpoints.

Under high market volatility, read latencies on the primary relational database (PostgreSQL) spike significantly. A junior engineer suggests placing Redis in front of the database to cache every order state query. You must evaluate this proposal, identify when adding Redis is justified versus when it introduces unnecessary operational complexity, and demonstrate how deep database index tuning could solve the problem without adding architectural state.

Discussion Requirements

Your response should systematically cover three core areas:

1. Redis Caching: Justification vs. Operational Risk

  • When Redis is justified: Identify workload properties (e.g., read-to-write ratios, latency tolerances, computational cost of aggregations) that make an external in-memory store the right tool.
  • Operational pitfalls: Discuss consistency hazards (dual-write failures, race conditions, stale reads), cache invalidation strategies (TTL vs. event-driven purge), and cache stampede/cold-start behavior during failovers.

2. Database Index Mechanics & Trade-offs

  • Index design: Explain how composite B-Tree indexes work, the significance of the "leftmost prefix" rule, and how column ordering relates to equality vs. range predicates (e.g., filtering on account_id, status, and created_at).
  • The cost of indexing: Explain the mechanical impact of secondary indexes on write amplification, buffer pool churn, and maintenance overhead (vacuuming/page splits).
  • Query plan diagnosis: How would you use EXPLAIN (ANALYZE, BUFFERS) to determine whether the database is I/O-bound, memory-bound, or suffering from index bloat (e.g., Index Scan vs. Index Only Scan vs. Bitmap Heap Scan)?

3. Concrete Recommendation

  • Outline a step-by-step decision framework for determining whether to optimize the relational database or deploy a dedicated Redis tier for this workload.

Hints

Hint 1

Consider that relational databases already have an in-memory cache (the buffer pool) that caches index and data pages before disk I/O occurs.

Hint 2

For composite indexes, remember that equality conditions can narrow down candidate ranges, but a range or inequality condition limits the utility of subsequent columns in the B-Tree index.

Hint 3

Contrast the failure modes of database read replicas with the failure modes of an external cache requiring application-level synchronization.

Follow-up questions

  • If your query uses 'WHERE trader_id = ? AND status IN (?, ?) AND created_at > ?', what is the exact index column ordering you would test first, and why?
  • How does the concept of write amplification differ between B-Tree-based relational databases and LSM-tree-based stores when secondary indexes are introduced?
  • What specific architectural pattern would you implement to prevent cache dogpiling (stampedes) if a high-frequency trading desk's portfolio snapshot cache entry expires?