Database Architecture and Partitioning Questions

Design database architecture and partitioning strategies appropriate to workload and access patterns. Evaluate database types including relational and various NoSQL models, schema design and indexing strategies, and when to use a monolithic database versus sharding. Cover sharding approaches such as range based, hash based, consistent hashing, and directory based sharding, as well as replica topologies, read replicas, replication lag, and handling cross shard queries. Address operational concerns at scale: resharding, mitigating hot partitions, balancing data distribution, transactional and consistency guarantees, and the trade offs between availability, consistency, and partition tolerance. Include monitoring, migration strategies, and impact on application logic and joins.

MediumSystem Design

59 practiced

Design a partitioning scheme for an IoT telemetry table that receives writes from 10 million devices, each sending data every minute. Requirements: support time-range queries per device for the last 90 days, high write throughput, and efficient daily batch aggregation for model training. Provide schema, partitioning key choices, and rationale.

Sample Answer

Requirements recap:- 10M devices × 1 write/min → ~10M rows/min ≈ 14.4B rows/day. Need high ingest, time-range per-device for last 90 days, and efficient daily batch aggregation.

Schema (wide, append-only):- telemetry(device_id UUID, event_time TIMESTAMP, ts_bucket DATE, sensor_type TEXT, value DOUBLE, status SMALLINT, partition_id INT, ingestion_time TIMESTAMP, raw_payload JSONB)- Primary index on (device_id, event_time) for device time-range scans (or clustered index if DB supports).

Partitioning strategy:1. Hierarchical partitioning: first by ts_bucket = date(event_time) (daily partitions) to support efficient pruning and daily batch jobs.2. Sub-partition each daily partition by hash(device_id) into N buckets (N configurable, e.g., 256 or 1024) to spread writes and avoid hot partitions.3. Retention: drop partitions older than 90 days automatically.

Rationale:- Daily partitions enable fast pruning for time-range queries and efficient batch aggregation (scan just the day's partition files).- Hash sub-partitioning distributes 10M concurrent device writes across many small physical shards to avoid single hot files and enable parallel ingestion.- Clustered index (device_id,event_time) within sub-partitions makes per-device range scans (last 90 days) efficient by reading only the relevant partition files and then seeking by device.- Storing ts_bucket and partition_id simplifies routing in producers and batch jobs.

Batch aggregation:- Daily Spark job reads that day's partitions in parallel across hash buckets, aggregates per-device features (counts, means, windowed stats), writes feature tables partitioned by date for training consumption.- Use columnar storage (Parquet/ORC) for aggregated outputs and for long-term analytics.

Operational notes:- Tune N to match target write throughput and number of ingestion workers.- Use bulk-loading / streaming ingestion (Kafka → ingestion service → partitioned writes) and compaction jobs to optimize files.- Monitor partition sizes; rehash if device distribution skews.This design balances write throughput, query performance for per-device time ranges, and efficient daily batch aggregation.

HardTechnical

57 practiced

Create a monitoring plan with specific metrics, SLOs, and dashboard panels to ensure an online feature store meets a p95 read latency SLA of 30ms. Include how to differentiate between database, network, and application causes when p95 is breached and what automated mitigations you would apply.

Sample Answer

Monitoring plan (goal: p95 read latency ≤ 30ms for online feature store)

SLOs & Objectives- SLO: 99% of reads p95 ≤ 30ms per minute-long window.- Error budget: 1% monthly violation allowance.- Secondary SLOs: p50 ≤ 8ms, availability (successful reads) ≥ 99.95%.

Key metrics to collect (tagged by feature-set, model, region, replica)- Client-side latency histogram (percentiles: p50/p95/p99)- Server-side latency breakdown: total, DB query time, cache lookup time, serialization time- DB metrics: query latency distribution, QPS, connection pool usage, lock/wait times, CPU, I/O waits- Cache metrics (Redis/Memcached): hit rate, p95 fetch latency, evictions, memory usage- Network: packet loss, RTT, throughput between app and DB/cache, NIC errors- Application: request rate, concurrent requests, GC pauses, thread pool saturation, error rates, tail latencies- Resource: CPU, memory, disk IO on app and DB nodes; autoscale events- Traces: distributed tracing (span per component) for every read.

Dashboard panels (real-time + 1h/24h)- Top: p95 client latency heatmap by region/service- Latency waterfall: breakdown of p95 into cache + DB + app + network- Error budget burn chart- DB health: p95 query latency, slow queries list, connection pool usage- Cache health: hit rate, p95 latency, evictions- App health: GC pauses, thread pool, CPU, error rate- Network metrics: RTT & packet loss between app↔DB/cache- Recent traces table filtered by p95 breaches.

Attribution steps when p95 breached1. Auto-triage via traces: compare span durations and percentages. If cache span >50% → cache; DB span dominant → DB; network RTT spike or repeated retransmits → network; app GC/thread starvation → application.2. Correlate with metrics: cache hit-rate drop, DB slow-query count, NIC errors, CPU/GC spikes.3. Run targeted synthetic reads from multiple regions to differentiate client network vs infra.

Automated mitigations (prioritized, safe)- If cache hit-rate drops and cache latency rises: - Auto-scale cache cluster (shard/replica), increase client-side TTLs temporarily, fallback to precomputed in-memory feature copy.- If DB query latency / slow queries increase: - Read-only failover to read replicas, route heavy-read traffic to replicas, apply query rate-limiting per client, enable prepared statement caching, auto-promote hot replica if leader overloaded.- If network RTT/packet loss spike detected: - Reroute traffic to healthy AZ/region, switch to local replica, circuit-break requests from affected clients.- If app resource saturation (CPU/GC/thread pool): - Autoscale application pool, enable degraded mode returning cached features or subset, shed low-priority requests.- If unknown or persistent violation: open incident, attach top traces/metrics, rollback recent deploys automatically if correlated with deploy ID.

Operational practices- Run chaos tests (cache/DB/network) in staging, maintain playbooks mapping signature → mitigations.- Alerting: primary alert when p95>30ms for 3 consecutive 1-min windows plus automated triage payload (top spans, metrics).- Post-incident: store traces & slow-query samples, update feature engineering (e.g., pre-join hot features), tune SLOs.

This plan gives observability, clear attribution logic using traces + metrics, and safe automated mitigations to protect model-serving quality while preserving explainability for postmortems.

EasyTechnical

60 practiced

You're evaluating whether to keep a monolithic database or move to sharding for the company's training dataset used by ML models. The dataset is 5 TB and grows 50% annually; queries are mix of batch training and interactive feature exploration. As a data scientist, list the decision criteria and recommend which approach to take, explaining trade-offs for model development and serving.

Sample Answer

Decision criteria:- Data size & growth: current 5 TB, +50%/yr → capacity and I/O needs in 1–3 years.- Query patterns: mix of large sequential reads (batch training) and low-latency, selective queries (interactive exploration).- Read/write characteristics: frequency of writes/ingests and need for real-time updates.- Latency/SLOs: acceptable training window vs strict interactive latency.- Operational complexity: team expertise, deployment/monitoring overhead for sharding.- Cost: storage, compute, network egress, and engineering/maintenance costs.- Consistency and schema: need for strong joins, transactions, or complex feature joins.- Data locality: privacy/regulatory constraints or geography.- Tooling integration: compatibility with feature stores, training infra, and orchestration.

Recommendation:Keep a single monolithic store for now but introduce pragmatic partitioning (time-based or by customer/region) and a feature-store layer. Rationale:- At 5 TB with current growth, a well-provisioned monolith (columnar storage, compression, partitioning) supports efficient batch training and simpler joins, lowering engineering risk.- Add partitioning improves query performance for both batch and interactive workflows without full sharding complexity.- Implement a feature store or materialized views to serve low-latency features for online inference.

When to shard:- If ingestion or query latency degrades as growth continues, or single-node limits (CPU/memory/io) are reached, move to sharded architecture with clear shard key aligned to query patterns.

Trade-offs:- Monolith/partitioned: simpler joins, easier reproducibility, lower ops cost initially; may hit scale limits later.- Full sharding: better horizontal scalability and parallel training, but increases complexity for cross-shard joins, causal reproducibility, deployment, and higher operational costs—needs robust orchestration and feature aggregation layer.

Impact on model development and serving:- Monolith + feature store: faster iteration, reliable reproducibility, simpler offline→online parity.- Sharded: can speed large distributed training but requires additional engineering to ensure consistent feature materialization and to support cross-shard sampling for training; online serving may benefit from localized low-latency shards but needs unified lookup layer.

EasyTechnical

51 practiced

Explain the CAP theorem and discuss its practical implications for designing a distributed storage used for both training data and online serving of predictions. Provide one concrete example where you would prioritize availability over consistency and one where the reverse is true.

MediumTechnical

50 practiced

A table experiences hot partitions because recent timestamps concentrate writes into the newest partition, causing write throughput failure. Propose three strategies (at storage and application level) to alleviate hot partitions for write-heavy time-series data and discuss their impact on read queries used for model features.

Sample Answer

Approach: focus on sharding writes across more partitions while minimizing harm to feature reads. For each strategy I describe the mechanism, implementation notes, and the read-impact + mitigation.

1) Salted/time+shard partition key (storage-level)- What: Add a small hash/salt to the partition key: partition = date || (device_id % N) or date || shard_id where shard_id = hash(event_id) % N. Use N = 4–16 initially.- Why: Spreads writes for the “current” time bucket across N physical shards so no single partition is hot.- Read impact: Queries for recent time windows must scan N partitions instead of 1, increasing read fan-in and latency.- Mitigation: Read-side combine in the application or use parallel reads; maintain a small materialized view that pre-aggregates the features across shards for the latest window (updated frequently).

2) Ingest buffering + write coalescing (application-level)- What: Buffer writes in memory or a lightweight queue (Kafka, Redis stream) and flush in batched, parallel writes across partitions or to multiple time-sliced partitions. Optionally assign writes to shards deterministically at flush time.- Why: Reduces per-write overhead, smooths spikes, and allows controlled distribution across partitions.- Read impact: Slightly increased write-to-read latency (buffering delay) and potential eventual consistency for freshest records.- Mitigation: For model features that need the freshest data, support a “fast-path” read that checks the ingestion buffer or a cache before hitting the DB; ensure flush frequency meets SLA for feature freshness.

3) Finer-grained time partitioning with rolling aggregation (storage+application)- What: Move from daily partitions to hourly/minute partitions for heavy windows and run background jobs that roll up older fine-grained partitions into coarser aggregates (daily/week) for historical queries.- Why: Writes target a smaller time slice but fewer writes per partition because many small partitions reduce contention on a single “current” partition namespace (especially when combined with multiple nodes).- Read impact: Queries spanning recent time may need to query many fine-grained partitions; however, older historical queries benefit from rollups.- Mitigation: Build a feature-store layer that queries recent fine partitions in parallel and reads rollups for older ranges. Precompute common features (sliding windows) for recent intervals to avoid scanning many partitions at query time.

Trade-offs and recommendation:- All strategies increase read complexity and operational overhead (more parallel reads, materialized views, caches). They trade write throughput for read fan-in and potentially eventual consistency.- For ML workflows, prefer combination: salted partitioning (low implementation effort) + ingest buffering + a feature-store layer that maintains near-real-time aggregates and a fast-path cache. Monitor write throughput, read latency, and feature freshness; tune shard count and buffer flush interval accordingly.

Unlock Full Question Bank

Get access to hundreds of Database Architecture and Partitioning interview questions and detailed answers.

Join thousands of developers preparing for their dream job.