Data Infrastructure Technology Selection Questions

Deep understanding of specific technologies relevant to complex system design. Master databases (PostgreSQL, Cassandra, DynamoDB, Elasticsearch), message queues (Kafka, RabbitMQ), caching systems (Redis), search engines, and frameworks. Understand their strengths, weaknesses, trade-offs, operational characteristics, scaling patterns, and common pitfalls. Be able to justify technology choices based on specific system requirements.

HardTechnical

44 practiced

Design Elasticsearch mapping and analyzer strategy for multilingual search (50 languages) with 50M documents requiring per-document term vectors, relevance tuning, and support for synonyms and diacritics. Discuss index-time vs query-time decisions and compute/memory sizing implications.

Sample Answer

Clarify requirements- Full-text search across 50 languages, per-document term vectors (for highlighting, ML features), relevance tuning, synonym support, diacritic-insensitive matching, 50M docs, low-latency queries, reasonable ingestion throughput.

High-level strategy- Store language code per document (lang field) by upstream detection (fasttext or langdetect) so we can apply per-language analyzers when useful.- Use a hybrid analyzer approach: a language-agnostic normalized field for cross-language matching + per-language analyzed fields for language-specific morphology and relevance tuning.- Use multi-field mapping: raw (keyword), normalized (ICU folding, lowercase, ASCII folding), and lang-specific analyzed fields. Enable term_vectors at index-time for the fields that require highlighting / ML.

Sample mapping (key parts)

json

{
  "mappings": {
    "properties": {
      "lang": { "type": "keyword" },
      "text": {
        "type": "text",
        "fields": {
          "raw": { "type": "keyword" },
          "norm": {
            "type": "text",
            "analyzer": "icu_normalizer",
            "term_vector": "with_positions_offsets"
          },
          "lang_analyzed": {
            "type": "text",
            "term_vector": "with_positions_offsets",
            "analyzer": "language_router" 
          }
        }
      }
    }
  },
  "settings": {
    "analysis": {
      "analyzer": {
        "icu_normalizer": {
          "tokenizer": "icu_tokenizer",
          "filter": ["icu_folding","lowercase"]
        },
        "language_router": {
          "tokenizer": "icu_tokenizer",
          "filter": ["lowercase","stop","my_synonym"]
        }
      },
      "filter": {
        "my_synonym": {
          "type": "synonym_graph",
          "synonyms_path": "analysis/synonyms.txt"
        }
      }
    }
  }
}

Notes:- Use ICU plugin for robust Unicode tokenization and diacritic folding (icu_folding) across scripts.- Use synonym_graph for query-time expansion where possible; keep heavy synonym sets at query-time when they cause combinatorial explosion.- Route queries to the appropriate lang_analyzed subfield using lang value (multi-match with field alias e.g., text.lang_en).

Index-time vs Query-time decisions- Index-time: - Pros: faster queries, synonyms folded into index (but increases index size and search time for updates), normalization (lowercase, diacritic folding), per-doc term_vectors (required for fast high-quality highlighting and ML). - Cons: heavier index size, slower ingestion, harder to change synonym rules.- Query-time: - Pros: flexible synonym rules, easier to A/B test relevance changes, smaller index. - Cons: more CPU at query time, can increase latency for heavy expansions, synonyms may produce complex parse graphs (use synonym_graph).

Recommendation: Normalize (lowercase, folding) at index-time; perform synonyms at query-time for large/volatile synonym sets, unless synonyms are small/stable — then index-time synonym_graph gives faster queries.

Relevance tuning- Base on BM25; tune k1/b per language when necessary.- Use per-language boosts and field-level boosts (lang_analyzed > norm).- Add function score: recency, click signals.- Consider Learning-to-Rank (LTR) or ML rerank using term vectors + features from stored term vectors for highest-value queries.

Term vectors implications- Use "term_vector": "with_positions_offsets" to support accurate highlighting and to derive features (tf, positions). This typically increases index size by ~20–60% depending on average doc length.- For 50M docs, estimate average doc length to compute size: if base index ~100GB, expect term vectors add 20–50GB. Measure empirically.

Compute / Memory / Sharding sizing guidance (starting point)- Storage: SSD NVMe storage, provision for 2× primary + replicas; plan headroom (50–100%).- Nodes: prefer many medium nodes vs few large ones. Example starter: 6 data nodes of r5.xlarge-equivalent (4 cores, 32–64GB) scaling to 12–24 for production.- Heap: don’t exceed ~31GB JVM heap per node (use rest for OS page cache and file system cache). Prefer 50–128GB RAM physical and 30–31GB heap.- CPUs: many cores for concurrent queries and analysis — 8–16 vCPU per node preferred for heavy query loads.- Shards: target shard size 20–50GB. With 50M docs, choose primary shard count to keep each shard in that target; use replicas for HA (replica=1).- Ingest throughput: if indexing CPU-heavy analysis/synonym expansion at index-time, add dedicated ingest nodes or increase CPU.- Coordinating nodes for heavy aggregation and routing.- Monitoring: track GC, heap usage, file system cache hit, query latency, and refresh/merge times.

Trade-offs & best practices- Use language detection upstream to avoid per-document multiple analyzers at query time.- Prefer query-time synonyms for large/volatile synonym sets; index-time normalization for diacritics and casing.- Use analysis-templates per language to tune stopwords/stemmers.- Consider storing sparse term vectors only for fields that need them; avoid enabling term_vectors on large nonessential fields.- Pre-warm caches for hot queries; set refresh interval according to SLA to reduce segment churn.- Run load testing with representative docs to refine shard sizing, node count, and memory.

Wrap-up- Use ICU normalization for diacritics, multi-field mapping with per-language analyzers, index-term_vectors where required, and a mix of index/query-time synonym strategies. Right-size node count, heap (~30GB), CPU, and SSD storage with shard sizing 20–50GB and monitor to tune.

HardTechnical

80 practiced

Postgres query planner chooses a sequential scan even when an index exists; prepared statement parameterization appears to change the chosen plan (parameter sniffing). How do you diagnose, reproduce, and fix planner misestimates? Consider statistics, extended statistics, query rewriting, and planner configuration options.

Sample Answer

Start by reproducing and collecting evidence — you want deterministic steps and metrics before changing anything.

Diagnosis & repro- Capture the plan with real parameters: - Use EXPLAIN (ANALYZE, BUFFERS, VERBOSE) for both the prepared/parameterized execution and the same query with literals. Compare estimated vs actual rows and timing.- Check if a generic (cached) plan is used for prepared statements: in older PG versions a prepared statement may use a generic plan that reflects average selectivity; new versions distinguish custom vs generic plans. Reproduce by PREPARE/EXECUTE and by running the literal version.- Inspect catalog stats: - Query pg_stats for column histogram, n_distinct, most_common_vals, correlation. - Check recent ANALYZE time and table bloat (VACUUM/ANALYZE status).- Collect workload-level info (pg_stat_statements) to see frequency and variance of parameter values.

Reproduction commands (examples)

sql

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM orders WHERE customer_id = 123;
PREPARE q(int) AS SELECT * FROM orders WHERE customer_id = $1;
EXPLAIN (ANALYZE) EXECUTE q(123);

Why planner misestimates happen- Outdated or coarse statistics (histogram too coarse, wrong n_distinct).- Correlation between columns not captured (multi-column selectivity wrong).- Parameter sniffing: first plan chosen is generic and suboptimal for skewed distributions.- Functions/implicit casts hide indexable expressions.

Fixes and mitigations (prioritized, with trade-offs)

1) Fix statistics- Run ANALYZE or increase per-column statistics target:

sql

ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders;

- When columns interact, create extended statistics so planner can estimate joint distributions:

sql

CREATE STATISTICS st1 (dependencies, mcv) ON col_a, col_b FROM my_table;
ANALYZE my_table;

Use extended stats if predicates combine columns (AND, OR, expression) and current estimates are off.

Trade-off: higher STATISTICS increases memory and ANALYZE work.

2) Query rewriting- Replace parameterized form by literalization when call-site values vary widely and the query is run with a single hot value (application-side prepared statements can inline literals).- Use explicit casts that allow index usage (avoid implicit casts).- Rewrite to make selectivity explicit (e.g., split into separate queries for common rare cases).

3) Planner configuration (use cautiously)- For temporary debugging, toggle enable_seqscan/enable_indexscan to force index use, but don’t leave in production:

sql

SET LOCAL enable_seqscan = off;
EXPLAIN (ANALYZE) ...;

- Use plan_cache_mode (Postgres 12+): force custom plans to be generated per execution when generic plans hurt:

sql

SET plan_cache_mode = force_custom_plan; -- or per-session

Trade-off: increased planning CPU.

4) Extensions and advanced options- pg_hint_plan: apply hints to force index usage (use only when other fixes not feasible).- Partitioning or functional indexes: if predicates are on expressions, create an index on the expression.

5) Long-term solutions- If data is highly skewed, consider maintaining summary tables or caching hot results.- Tune autovacuum/analyze thresholds to keep stats up-to-date on volatile tables.- Add multi-column or expression indexes matching queries shown by EXPLAIN.

Verification- After any change, rerun EXPLAIN ANALYZE to confirm estimates converge to actuals and latency improves.- Monitor pg_stat_statements to ensure changes reduce overall cost.

Summary checklist- Reproduce with EXPLAIN ANALYZE (literal vs prepared).- Update ANALYZE / increase STATISTICS.- Add extended statistics for correlated columns.- Consider query rewriting or literalization.- Use plan_cache_mode or pg_hint_plan as last-resort controls.- Measure and iterate — always verify with real workloads.

This approach balances short-term mitigations (planner toggles, query rewrites) with robust long-term fixes (better stats, extended statistics, indexes, and schema changes) appropriate for production client systems.

MediumSystem Design

42 practiced

Design a microservices pipeline that requires exactly-once semantics for inventory updates. Compare Kafka transactional producers, idempotent consumers, the outbox pattern, and external transactional stores. Which design achieves end-to-end once semantics and what are operational trade-offs?

Sample Answer

Requirements & constraints:- Inventory updates must be applied exactly once across services (no double-apply or lost updates).- System is microservices-based, uses Kafka for async comms, services may fail/retry, and DBs are independent (no global transactions).- Need durability, ordering per aggregate (SKU), and reasonable latency.

High-level options compared

1) Kafka transactional producers- How: Use Kafka transactions to atomically write to multiple partitions/topics and produce+commit consumer offsets in the same transaction (producer + consumer via Kafka transactional API / consume-transform-produce pattern).- Guarantees: Strong end-to-end once for the Kafka workflow segment when using read-process-write with transactionally committing offsets — prevents duplicates caused by retries between Kafka and producer.- Limits: Only covers Kafka <-> consumer-producer boundary. If downstream update to an external DB (inventory store) is outside Kafka transaction, once semantics break.- Ops trade-offs: Complex to operate (idempotent producers, transaction timeout management), increased latency (transactions), careful partitioning required, limited to Kafka ecosystem versions supporting transactions.

2) Idempotent consumers- How: Consumers process messages and write inventory changes in an idempotent way (e.g., upsert with operation-id / dedupe table / use version numbers).- Guarantees: Once semantics achievable at application level if idempotency key and dedupe store are correct; handles retries and duplicate deliveries.- Limits: Requires designing idempotent writes for every mutation; race conditions need compare-and-set or DB constraints to prevent lost updates.- Ops trade-offs: Simpler infra, more application complexity and storage (dedupe state), must manage TTL of dedupe keys, careful schema/transaction design.

3) Outbox pattern (transactional outbox)- How: Service writes state change and an outbound message to an outbox table in the same DB transaction; a reliable process (poller/binlog connector like Debezium) publishes outbox rows to Kafka.- Guarantees: Achieves end-to-end once semantics when combined with idempotent consumers or transactional publishing that ensures each outbox row is published exactly once. Ensures no loss between state change and event emission.- Limits: Need to ensure the publisher is exactly-once (use Kafka producer idempotence or transactionally write offsets). Consumer side still needs idempotency if applying to another DB.- Ops trade-offs: Requires DB-side changes, outbox table maintenance, additional connectors, added latency from polling/CDC, simpler failure semantics and easier reasoning than distributed transactions.

4) External transactional stores / distributed transactions (e.g., two-phase commit, XA, or distributed transactional DB)- How: Use a centralized transactional system to commit both inventory DB and message store atomically (e.g., Kafka as a transactional sink or use a distributed DB that supports change streams atomically).- Guarantees: Can provide strict end-to-end once if truly transactional across all resources.- Limits: Complex, brittle across heterogeneous systems, poor scalability, vendor lock-in, increased latency.- Ops trade-offs: Hard to operate, difficult across cloud-native microservices, failure modes complex, often not recommended.

Which achieves end-to-end once semantics?- The robust practical design is: Outbox pattern at the service boundary + exactly-once publishing to Kafka (Kafka transactions or idempotent producers with careful offset handling) + idempotent consumers on the inventory update side. This combination provides end-to-end once delivery: the outbox makes state change and event emission atomic; transactional/ idempotent publishing prevents duplicates into Kafka; idempotent consumer ensures applying events to inventory DB is safe against duplicate deliveries or retries.- Pure Kafka transactions alone only cover Kafka-level guarantees; without outbox you risk losing events if the service fails after DB commit but before producing. Pure idempotent consumers require every producer to emit reliably — risky without outbox.

Operational trade-offs summary- Complexity vs safety: Outbox + transactional publish + idempotent consumer adds code and infra but gives strong guarantees and is widely used.- Latency: Transactions & CDC/polling add latency; tune for acceptable SLAs.- Operational burden: Kafka transactions require careful config (transaction timeouts, broker versions); outbox requires DB ops and connector management; idempotency requires dedupe storage and lifecycle management.- Scalability: Outbox scales with DB write volume and connector throughput; distributed transactions scale poorly.- Observability & troubleshooting: Outbox and idempotency provide clearer audit trails (outbox rows, dedupe logs) vs opaque distributed tx failures.

Recommendation (Solutions Architect)- Use the transactional outbox pattern in each service, publish to Kafka via a CDC/connector that uses idempotent/transactional producers, and implement idempotent application-side consumers that apply inventory updates with a dedupe key or optimistic concurrency control. This balances operational feasibility, reliability, and scalability while achieving practical end-to-end exactly-once semantics.

MediumTechnical

54 practiced

Compare Postgres full-text search and Elasticsearch for a product search system requiring relevance ranking, typo tolerance, analytics, and metrics. Discuss feature differences, scaling, indexing complexity, developer velocity, and operational cost trade-offs.

Sample Answer

Situation: A product-search requirement asks for relevance ranking, typo tolerance, analytics, and operational efficiency. Below I compare PostgreSQL full-text search (FTS) and Elasticsearch (ES) across features, scaling, indexing, developer velocity, and operational cost, then give recommendation options.

Feature differences:- Relevance ranking: Postgres FTS uses tsvector/tsquery with ranking functions (ts_rank, ts_rank_cd). Good for simple relevance but limited tunability (field boosts, custom scoring pipelines). ES provides full BM25 scoring, per-field boosts, function_score, custom scripts — far more flexible for modern relevance tuning.- Typo tolerance: Postgres supports trigram extension (pg_trgm) for similarity and fuzzy matches but is less performant for large datasets. ES has native fuzzy queries, phonetic plugins, and analyzers (edge n-gram) optimized for fuzzy matching and autocomplete.- Analytics & metrics: ES includes aggregations, metrics, and time-series-friendly features out of the box. Postgres can do analytics via SQL, materialized views, and extensions, but complex real-time aggregations over high QPS are heavier.- Secondary features: ES supports geo, percolator, highlighting, and relevance explainability; Postgres covers basics but requires more manual work.

Scaling:- Postgres: Vertical scaling works well; horizontal sharding is possible but operationally heavier (logical sharding, Citus). Index sizes for FTS (tsvector + GIN/GiST) can be large; update-heavy workloads cause bloat needing VACUUM/REINDEX.- ES: Built for horizontal scale, automatic shard/replica distribution, near real-time indexing. Better for large indices and high read/write throughput at scale.

Indexing complexity:- Postgres: Simple to implement for small-medium catalogs: create tsvector columns, GIN indexes, triggers or generated columns. Fine for batch updates. Complex pipelines (per-field analyzers, custom token filters) are limited.- ES: Index mapping design, analyzers, tokenizers, and ingest pipelines add complexity but enable powerful tokenization (synonyms, stemmers, char filters). Index maintenance (reindexing after mapping changes) adds operational steps.

Developer velocity:- Postgres: Faster to prototype if product data already in Postgres—use SQL, fewer moving parts. Lower cognitive overhead for engineers familiar with relational DBs.- ES: Faster to iterate on relevance tuning and search features due to rich APIs and query DSL, but requires learning ES mapping/analysis and integration patterns (near-real-time sync from Postgres).

Operational cost trade-offs:- Postgres: Lower infra complexity and cost for small to medium workloads (single DB). Maintenance overhead increases with scale (sharding, VACUUM). Fewer services to operate reduces SRE burden.- ES: Higher operational cost (cluster management, JVM tuning, storage). Managed services (Elastic Cloud, AWS OpenSearch) raise direct cost but reduce operational burden. For large scale and advanced search features, ES can be more cost-effective due to better performance per feature.

Recommendation (Solutions-Architect view):- Small-medium catalog, tight timeline, limited budget: Start with Postgres FTS + pg_trgm for fuzzy; profile and only move to ES if requirements outgrow Postgres (monitor QPS, latency, index size).- Large catalog, heavy read/write, advanced relevance, typo tolerance, analytics needs: Use Elasticsearch/OpenSearch. Build an ETL/CDC pipeline (e.g., Debezium or logical replication) to sync Postgres -> ES; adopt index versioning and A/B relevance experiments.- Middle ground: Hybrid — keep authoritative data and analytics in Postgres; push denormalized search index to ES. This gives developer velocity for data changes and ES for search features.

Trade-offs summary:- Postgres = simpler, cheaper initially, good for modest relevance and fuzzy needs.- Elasticsearch = feature-rich, scalable, higher operational cost but better for advanced ranking, typo tolerance, and analytics at scale.

Implementation note: Instrument search metrics (latency, click-through, zero-result rates) and expose relevance telemetry to iterate. Use feature flags and staged rollouts when switching or tuning search backends.

EasyTechnical

40 practiced

You are designing an e-commerce product catalog that must support 50M products, very high read throughput, occasional updates, and complex faceted search. Compare PostgreSQL, Cassandra, and DynamoDB for this use case and recommend one. Explain justification: data modeling, indexing, query patterns, operational concerns, and estimated cost drivers.

Sample Answer

Recommendation: DynamoDB with a dedicated search service (OpenSearch) for faceted search.

Reasoning and comparison:

PostgreSQL- Data modeling: Relational with rich joins; works for product metadata and transactions.- Indexing: B-tree / GIN for JSONB; supports complex queries.- Query patterns: Good for ad-hoc analytics, but full-text + multi-dimensional faceting at scale is heavy.- Operational: Single-node limits or complex sharding (Citus) required for 50M+ items and very high reads.- Cost drivers: Large compute instances, I/O, replication, and operational overhead for scaling and HA.- Verdict: Strong for consistency and analytics, but higher ops and less suited for massive read-scale + faceted search.

Cassandra- Data modeling: Wide-column, tuned for high write/read throughput using primary-key design; denormalization required.- Indexing: Limited secondary index support; materialized views and manual denormalized tables for query patterns.- Query patterns: Excellent for predictable key-based reads at very high throughput; harder for arbitrary faceted search.- Operational: Self-managed clusters, careful compaction/tuning, intensive ops.- Cost drivers: Cluster nodes, cross-datacenter replication, ops labor.- Verdict: Great for scale and low-latency reads, but building complex faceted search on top is cumbersome.

DynamoDB + OpenSearch (recommended)- Data modeling: Primary store in DynamoDB using access-pattern-driven design (product by id, category hot lists, SKU lookups). Use GSIs sparingly for additional read patterns.- Indexing & query patterns: Offload faceted search and full-text to OpenSearch (near real-time sync via DynamoDB Streams + Lambda). DynamoDB handles extremely high consistent reads (DAX for caching).- Operational concerns: Fully managed, auto-scaling, multi-AZ -> low ops burden. OpenSearch is managed (AWS OpenSearch Service) but requires tuning for shards and memory.- Cost drivers: DynamoDB provisioned RCUs/WCUs or On-Demand, storage, DAX/backup; OpenSearch instance fleet, EBS, data transfer, Lambda stream processing.- Trade-offs: Best combination of scale, low ops, and search capability. Slightly higher cost due to dual systems but balanced by operational simplicity and meeting requirements (50M products, very high reads, occasional writes, complex faceted queries).

Implementation notes:- Model denormalized read patterns in DynamoDB; keep product master, inventory, pricing separate to minimize writes.- Stream changes to OpenSearch for faceted indexes; keep eventual consistency acceptable for search.- Use CDN + caching layer (CloudFront, DAX) to reduce read RCU costs.- Monitor hot partitions, provisioned capacity, and OpenSearch query latency; implement lifecycle policies to manage index size.

Unlock Full Question Bank

Get access to hundreds of Data Infrastructure Technology Selection interview questions and detailed answers.

Join thousands of developers preparing for their dream job.