Database and Data Platform Selection Questions

Evaluation and selection of database and data platform technologies to meet analytical and operational needs. Covers assessment of relational, non relational, columnar, and specialized systems such as time series and search engines; data warehouse platforms and cloud analytics platforms; query patterns and workload characteristics; consistency and transactional guarantees; partitioning and clustering strategies; storage formats and compression; performance and scalability trade offs; operational complexity and administration overhead; data ingestion and incremental loading patterns; pricing and cloud platform considerations; and how to choose the right solution based on data volume, concurrency, latency requirements, and total cost of ownership.

MediumSystem Design

52 practiced

Design a partitioning scheme for a multi-tenant SaaS application's events table where tenants vary from tiny to very large. Requirements: per-tenant queries must be fast, cross-tenant analytics must be supported, and tenant-onboarding must be low friction. Discuss shard strategy, isolation (per-tenant schemas vs shared schema), and how to handle tenant hotspots and rebalancing.

Sample Answer

Requirements (clarify): fast per-tenant queries (point lookups, time-range scans), support cross-tenant analytics (aggregations), low-friction onboarding (no heavy manual ops), support tenants from tiny → huge, handle hotspots and rebalancing.

High-level approach:- Use logical tenant-aware partitioning on a shared schema by default, with optional dedicated shards for very large tenants.- Store events in a single table with columns: tenant_id, event_time, event_type, payload, plus a computed partition_key.

Shard strategy:1. Hierarchical partitioning: - First level: range or hash partition by tenant_id (group tenants into buckets of size N) to limit number of partitions. - Second level: time-based partitioning (e.g., monthly) within each tenant-bucket to support efficient time-range queries and retention.2. For very large tenants, provision dedicated shard(s)/database instances (vertical isolation) and route their traffic directly.

Isolation: Shared schema by default- Pros: simple onboarding (single DDL), easy cross-tenant queries, efficient resource utilization.- Cons: noisy neighbor risk mitigated by resource limits, tenant quotas, and ability to move heavy tenants to dedicated shards.- Provide logical row-level tenant isolation enforced at application/read-layer and via DB policies.

Handling hotspots & rebalancing:- Detect hotspots using metrics (QPS, storage growth) and TTL/retention patterns.- For write hotspots within shared shards, apply: - Write-side sharding: add sub-shard key (tenant_id + hash(event_time) or sequence) to distribute writes across partitions. - Temporarily throttle or use write queues/batching for bursty tenants.- Rebalancing: - Move tenant bucket partitions between physical nodes using online table copy or DB-native tools (pg_repack/partition attach-detach) during low load. - For huge tenants, promote to dedicated shard: create new DB, copy partitions incrementally, switch routing with feature flag.- Ensure strong routing layer (service or proxy) that maps tenant -> shard, supports atomic switch, and records metadata in a registry.

Cross-tenant analytics:- Maintain a periodically ETLed analytics store (columnar DB or data lake) that ingests partitioned data from all shards to avoid impacting OLTP.- Use change-data-capture (CDC) or scheduled batch exports for near-real-time analytics.

Trade-offs:- Shared schema eases onboarding and analytics but requires careful resource governance.- Dedicated shards increase isolation and performance but add operational cost.- Complexity in partitioning vs. simplicity; prefer incremental sophistication: start with shared multi-level partitions + monitoring, then isolate heavy tenants as needed.

Operational considerations:- Automate routing registry, onboarding flows, and rebalancing playbooks.- Monitor per-tenant metrics and set automated alerts/thresholds for promotion/demotion.- Test rebalancing on staging, ensure zero-downtime routing cutover, and document rollback steps.

MediumTechnical

36 practiced

You are given a simple SQL table schema and asked to optimize common queries. Table `orders(order_id PK, customer_id, created_at, status, total_cents)`. Typical queries: fetch recent orders for a customer, compute daily sales, and get order by id. Propose indexes and partitioning strategies to optimize these queries and explain write amplification or maintenance costs those choices introduce.

Sample Answer

Approach summary: pick a small set of targeted indexes that support the three hot queries, and use range partitioning by time to speed aggregates and retention. Balance read speed vs write amplification (every index + partition metadata increases write cost and maintenance).

Recommended indexes and SQL (examples for PostgreSQL/MySQL equivalents):

- Get order by id - Rely on PK(order_id) — no extra index required.

- Fetch recent orders for a customer (sorted by created_at DESC, often only recent rows) - Composite covering index: (customer_id, created_at DESC) INCLUDE (status, total_cents) - SQL (Postgres): CREATE INDEX idx_orders_customer_created_desc ON orders (customer_id, created_at DESC) INCLUDE (status, total_cents); - Optional partial index for active statuses to reduce size: CREATE INDEX idx_orders_customer_active ON orders (customer_id, created_at DESC) WHERE status = 'completed';

- Compute daily sales (GROUP BY date(created_at), SUM(total_cents)) - Functional/partial index on date(created_at) is possible, but better: store a derived column order_date (DATE) and index it: CREATE INDEX idx_orders_order_date ON orders(order_date); - Alternatively, cluster partitions by month to allow partition pruning and fast scans.

Partitioning strategy:- Range partition by created_at (or order_date) monthly or weekly depending on volume: - Pros: partition pruning makes daily aggregates and historical deletes fast; backups/archival per-partition easier; reduces IO for recent-data queries. - Cons: need to manage partition creation (automate), smaller partitions increase metadata overhead.- Keep recent N months in hot partitions, archive older partitions to cheaper storage.

Write amplification and maintenance costs:- Each additional index increases write latency and IOPS: INSERT/UPDATE must maintain every index — expect proportional increase in CPU and WAL size.- Covering indexes (INCLUDE) reduce read lookups but still increase write work.- Partitioning adds metadata overhead and potential hotspot on the current partition (if all writes go to latest partition); avoid tiny partitions to reduce planner overhead.- Partial indexes reduce index size but complicate maintenance and query planning.- Operations to plan for: index bloat / periodic REINDEX, VACUUM (Postgres), partition creation/drop, maintaining statistics, and changes to partitioning/key are expensive (require data movement).

Trade-offs and final recommendations:- Start with PK and the composite covering index on (customer_id, created_at DESC) including total_cents/status. Add an order_date column and index it for efficient daily aggregations rather than relying on expression indexes.- If throughput is high (millions of orders/day), add monthly range partitions and automate partition management; monitor write latency and WAL growth and tune number of indexes accordingly.- Use partial indexes for queries that target a small subset (e.g., recent/completed orders) to keep index size down.

EasyTechnical

74 practiced

Explain ACID and BASE consistency models. Give one concrete example of a system or database that prefers ACID and one that prefers BASE, and explain the trade-offs in terms of developer complexity, latency, and failure modes.

Sample Answer

ACID and BASE are two different approaches to consistency and transaction semantics in distributed/storage systems.

ACID (Atomicity, Consistency, Isolation, Durability)- Atomicity: a transaction is all-or-nothing.- Consistency: transactions move database from one valid state to another (enforced constraints).- Isolation: concurrent transactions behave as if serialized (various isolation levels).- Durability: once committed, changes survive crashes.Example: PostgreSQL (or MySQL/InnoDB) prefers ACID. Trade-offs: developer complexity is lower for correctness (you rely on DB to enforce invariants), reasoning about state is simpler. Latency can be higher for distributed ACID (synchronous replication, two-phase commit). Failure modes: strong guarantees reduce data corruption but can block/slow under partitions; availability may be sacrificed (CP in CAP).

BASE (Basically Available, Soft state, Eventual consistency)- Basically Available: the system aims for high availability.- Soft state: state may change without input, temporary inconsistency allowed.- Eventual consistency: replicas converge over time.Example: Apache Cassandra (or Dynamo, Riak) prefers BASE. Trade-offs: developer complexity increases—application must handle conflicts, retries, and reconciliation (e.g., vector clocks, CRDTs). Latency is lower because reads/writes can be served from local replicas without global coordination. Failure modes: tolerant to partitions and node failures (AP in CAP), but clients may observe stale or divergent data; you need careful design to avoid lost updates or user-facing inconsistencies.

Summary of trade-offs:- Developer complexity: ACID simpler correctness model; BASE requires conflict resolution logic.- Latency: ACID higher when strong coordination needed; BASE lower, faster local ops.- Failure modes: ACID may become unavailable under partitions; BASE remains available but yields temporary inconsistency. Choose based on domain: financial systems → ACID; high-throughput social feed or telemetry → BASE.

HardSystem Design

49 practiced

A legacy monolithic application uses a single Oracle instance. You're tasked with migrating to a microservices architecture and deciding data platforms per service. Outline a migration strategy that minimizes downtime, ensures data integrity, and prevents cascading failures. Discuss patterns (strangling, dual-write, anti-corruption), polyglot persistence choices by service, and rollback plans.

Sample Answer

Requirements & constraints:- Minimize downtime, preserve transactional integrity, prevent cascading failures, allow incremental cutover, support service-specific data stores (polyglot persistence), provide safe rollback.

High-level migration strategy (incremental strangler):1. Identify bounded contexts from domain analysis; group tables/logic per service.2. Create anti-corruption layers (ACL) around the monolith for each new service to translate/protect interfaces.3. Implement services one-by-one using strangler pattern: route a portion of traffic (canary) to new service while monolith remains authoritative.4. Keep single source of truth during transition via Change Data Capture (CDC) from Oracle (Debezium/Kafka Connect) to populate service stores and event streams.

Data consistency patterns:- Dual-write only in controlled cases with idempotent APIs and write ordering safeguards. Prefer event-driven propagation (CDC → Kafka → service) over synchronous dual-write to avoid partial failures.- Use Sagas for cross-service transactions (or compensating actions) to avoid distributed two-phase commit. Design compensations and timeouts explicitly.- Use anti-corruption layer to prevent domain leakage and translate legacy models to new domain models.

Polyglot persistence by service (examples):- User/profile: relational (Postgres) for strong consistency and complex joins.- Catalog/search: document store (Elasticsearch or MongoDB) for flexible schema and fast queries.- Billing/ledger: ledger DB (Postgres with immutability patterns or specialized ledger like Amazon QLDB) for auditability.- Sessions/cache: Redis for low-latency ephemeral data.Choose storage per service needs: consistency, query patterns, scalability, compliance.

Prevent cascading failures:- Circuit breakers, bulkheads, timeouts in service calls.- Degrade gracefully: read-through to monolith if new service unavailable.- Rate limiting on CDC/event consumers; backpressure on Kafka consumers.

Migration mechanics:- Start CDC to replicate data into service stores; run verification jobs to compare counts/checksums.- Switch read traffic incrementally (A/B or dark launch) to new service ACL. For writes, initially route to monolith; once verified, flip writes to service and emit events to update monolith or keep monolith in read-only mode.- Monitor data drift; use reconciliation jobs and alerts.

Rollback & recovery:- Keep monolith writable and able to resume full traffic until cutover completes.- Maintain idempotent operations so replaying events or reversing writes is safe.- For each cutover step have rollback plan: stop routing to service, re-enable monolith paths, resume CDC-driven sync to catch up, run reconciliation.- Automate backups, snapshot service stores before enabling writes, and keep schema/versioning in migrations.

Testing, observability & automation:- End-to-end tests including consumer-driven contract tests.- Canary deployments, feature flags to control traffic.- Observability: distributed tracing, metrics for data lag, mismatch counts, error rates.- Chaos testing of consumers and ACLs to validate resilience.

Trade-offs:- CDC + event-driven is higher initial complexity but minimizes downtime and avoids dual-write hazards.- Full transactional guarantees across services sacrificed for scalability — mitigate with Sagas and compensations.

This plan provides incremental safety: ACLs protect domain integrity, CDC prevents data loss, circuit breakers prevent cascades, and explicit rollback procedures minimize risk.

EasyTechnical

45 practiced

You need to store metrics from IoT sensors producing a measurement every second per device. Explain why a time-series database might be preferable to a general-purpose RDBMS. List two time-series engines and describe the storage/retention features they provide (compression, downsampling, rollups).

Sample Answer

Time-series DBs are preferable for per-second IoT metrics because they’re optimized for high-write throughput, efficient time-based queries, and automated retention. Key reasons:- Write/ingest: Designed for high-velocity, append-only writes and batched ingestion (lower write amplification than generic RDBMS).- Storage efficiency: Time-series engines use specialized on-disk formats and compression for numeric, timestamped data.- Time semantics: Built-in time-based indexing, range queries, and downsampling/rollup primitives simplify queries like “1-minute averages” or retention policies.- Retention/ops: Native TTL/retention policies, background compaction, and tiering reduce operational complexity for large historical datasets.- High-cardinality & tags: Many TSDBs handle tag-based indexing and cardinality patterns more efficiently than classic row-based RDBMS.

Two engines and their storage/retention features:

1) InfluxDB (OSS/Cloud)- Storage/compression: TSM (Time-Structured Merge tree) with efficient delta and Gorilla-like compression for timestamps and float compression, plus compaction.- Downsampling/rollups: Continuous Queries (CQ) or Tasks/Flux to compute and store aggregated series (e.g., 1m means).- Retention: Retention Policies that auto-expire old shards; shard group durations control compaction windows and retention granularity.

2) TimescaleDB (Postgres extension)- Storage/compression: Hypertables partitioned into time-based chunks; native columnar compression (since recent versions) compresses older chunks efficiently (dictionary, delta, etc.).- Downsampling/rollups: Continuous Aggregates create materialized rollups automatically refreshed on schedule.- Retention: Policies via background jobs (drop_chunks) to remove old data; chunking size controls retention performance and compaction.

Summary: For per-second IoT metrics, TSDBs reduce storage cost, simplify time-based queries/aggregations, and provide operational primitives (TTL, downsampling, compression) that general-purpose RDBMSs lack or require substantial custom work to replicate.

Unlock Full Question Bank

Get access to hundreds of Database and Data Platform Selection interview questions and detailed answers.

Join thousands of developers preparing for their dream job.