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.
MediumTechnical
69 practiced
You need to select a search solution for product search requiring fuzzy matching, typographic tolerance, faceting, and multi-language support. Compare Elasticsearch/OpenSearch, Algolia, and a database full-text search option from scalability, relevancy tuning, latency, and operational cost perspectives as SRE.
Sample Answer
Situation/goal: Choose a product search backend with fuzzy/typo tolerance, faceting, multi-language support. As the SRE, I compare Elasticsearch/OpenSearch, Algolia, and a DB full‑text search across scalability, relevancy tuning, latency, and operational cost.Elasticsearch / OpenSearch- Scalability: Highly scalable horizontally (shards/replicas). Needs capacity planning for shard count, cluster balancing, and JVM/heap tuning. Handles large indexes and high write throughput if properly sharded.- Relevancy tuning: Very flexible—custom analyzers, tokenizers, BM25, query DSL, boosting, script scoring. Great for complex business rules.- Latency: Typically low read latency if warmed and cached, but can spike during heavy merges or GC. Needs careful resource and JVM tuning to meet SLOs.- Operational cost: Higher operational overhead—cluster management, upgrades, monitoring, node sizing, backups. Self-hosted or managed (Elastic Cloud/Opensearch Service) increases monetary cost but reduces human ops.Algolia- Scalability: Fully managed and auto-scaled. Offloads operational complexity.- Relevancy tuning: Excellent out-of-the-box typo tolerance, synonyms, and ranking formula; simpler UI for tuneable rules but less raw flexibility than ES for bespoke scoring.- Latency: Very low and consistent (CDN + optimized infra). Good for tight latency SLOs globally.- Operational cost: Higher monetary cost (per record, per query) but minimal ops burden—good trade when team wants reliability with low operational overhead.Database full-text search (Postgres tsvector, MySQL/InnoDB FTS)- Scalability: Limited horizontal scaling; vertical scale or read replicas. Not ideal for very large indexes or heavy write/query mixes.- Relevancy tuning: Basic (stemming, weights); limited fuzzy/typo support without extensions (pg_trgm). Harder to implement advanced ranking.- Latency: Acceptable for low QPS and smaller datasets; can suffer under complex faceting or high concurrency.- Operational cost: Lowest infrastructure cost and simplest stack if already using DB; lower ops complexity but more developer effort to approximate advanced features.Recommendation (SRE perspective)- If strict latency SLOs, global consistency, and minimal ops team: Algolia.- If you need deep custom relevancy, complex queries, and control over infra: Elasticsearch/OpenSearch, but invest in automation (Helm/Operators), observability (Prometheus/Grafana), autoscaling policies, and runbook for merges/GC.- If budget-constrained and dataset modest with low QPS: DB FTS + pg_trgm, but expect trade-offs in relevancy and scale.Operational actions I'd take- Define SLOs (p99 latency, availability, freshness), simulate load, and benchmark candidate for your dataset.- Automate deployment, backups, alerting for cluster health, and document scaling/runbooks.- Start with a proof-of-concept indexing representative data and measuring relevancy and tail latency before full rollout.
HardSystem Design
43 practiced
Design a backup and restore approach to meet RTO = 15 minutes and RPO = 0 for a small subset of critical tables in a PostgreSQL cluster, while minimizing performance impact on the primary. Include replication, WAL streaming, point-in-time recovery, and how to test restores regularly without affecting production performance.
Sample Answer
Requirements & constraints:- RTO ≤ 15 minutes for critical tables- RPO = 0 for that subset (no data loss)- Minimize performance impact on primary- Use replication, WAL streaming, PITR, and test restores regularly without affecting productionHigh-level approach (summary):- Move "critical tables" into a small dedicated database/schema on a separate PostgreSQL cluster (or separate primary DB instance) so we can apply stronger durability guarantees only where needed.- Synchronous replication for that dedicated critical-db (to ensure RPO=0).- Asynchronous physical replica + continuous WAL archiving (to object storage) for full cluster to support PITR.- Use pg_basebackup + pgbackrest or WAL-G to manage base backups and continuous WAL streaming/archive.- Maintain a warm standby for quick promotion (RTO target) and automated runbooks for failover.- Regular automated restore tests in isolated environments using archived WALs and base backups.Architecture & components:1. Critical DB instance: - Runs the schema/tables that require RPO=0. - synchronous_commit = on for transactions that update critical tables (or use per-transaction synchronous_commit='on'). - synchronous replica (hot standby) configured in synchronous_standby_names. Replica kept ready for fast promotion. - pg_basebackup used to create standby; replication via streaming replication (physical).2. Main cluster: - Asynchronous streaming replicas for read-scaling and disaster recovery. - WAL archiving enabled: archive_command uploads WAL segments to S3-compatible storage via pgbackrest/WAL-G. - Regular full/incremental backups via pgbackrest (retention policies).3. PITR: - Continuous WAL archive + base backups allow recovery to any WAL position. - For critical-db we still rely on synchronous replica for zero-loss; PITR used for logical corruption recovery.Data flow:- Primary writes -> WAL records streamed to synchronous standby (critical-db) and archived to object storage.- For non-critical data, asynchronous replicas receive WAL; archive persists WAL segments for PITR.Why separate DB/schema:- PostgreSQL cannot natively enforce "synchronous replication only for some tables" — replication is at cluster/instance level. Isolating critical tables into a dedicated DB enables applying synchronous replication only where needed, minimizing latency impact on the rest of the workload.Backup & WAL settings (practical configuration highlights):- wal_level = replica or logical (if logical replication used elsewhere)- max_wal_senders >= number_of_replicas- archive_mode = on; archive_command uploads WAL to object store (pgbackrest/WAL-G)- Use pgbackrest for base backups + retention/compression + integrity checks- For critical-db: synchronous_commit = on, synchronous_standby_names = 'replica_name' (or quorum)Recovery & RTO plan:- Keep warm standby on same region (low latency) and automated failover tool (Patroni/pg_auto_failover/repmgr) configured to promote standby within <2–5 minutes.- After promotion, applications must re-route connections (DNS with low TTL / VIP / service mesh).- If standby is unavailable, restore from latest base backup + replay WAL up to last segment (if WAL archive is local cloud S3, replay time depends on transfer speed; automate prefetching/replay to target to meet 15min).Testing restores without affecting production:- Automated weekly restore drills: - Restore latest base backup + WAL replay into an isolated test environment (separate VPC/namespace) using infrastructure-as-code (Ansible/Terraform) and pgbackrest restore commands. Validate application-readiness by running smoke tests and checksum compares for critical tables. - For PITR scenarios: restore to multiple recovery_target_time points and run integrity queries. - For logical corruption: periodically promote standby and run compare-checks between primary and standby (row counts, checksums).- Use filesystem-level snapshots of standby replicas (or physical base backup copies) to create fast isolated clones for testing (copy-on-write snapshots reduce impact).- Use replica offloading: take a copy from a replica rather than the primary to avoid load on primary.Monitoring & verification:- Monitor replication lag (pg_stat_replication / replay_lag); set alert if > 100 ms for critical-db.- Track WAL archive health (last archived segment, pending archival).- Alerts for backup success/failure, recovery tests, checksums mismatches.- Periodic backup integrity verification via pgbackrest check/verify.Scalability & trade-offs:- Trade-off: Synchronous replication adds commit latency. By isolating critical tables into a separate DB, only those transactions pay the latency cost.- Alternative: If schema split is not feasible, use synchronous replication for whole cluster (higher latency) or application-level dual writes—higher complexity and risk of divergence.- Using logical replication could replicate only specific tables but cannot guarantee RPO=0 because apply latency may introduce loss; logical replication also lacks transactional atomicity cross-cluster.Failure scenarios & runbooks:- Primary crash (critical-db): auto-promote synchronous standby, update DNS, route traffic. Rollback: resynchronize old primary as replica.- Corruption: use PITR from WAL archive to restore to safe time; use point-in-time to remove corruption.- WAL archive loss: ensure multi-region/object-store replication and lifecycle retention.Summary checklist to meet RTO=15m / RPO=0:- Isolate critical tables into dedicated DB/instance.- Use synchronous physical streaming replication for that instance (hot standby) and automated promotion.- Continuous WAL archiving + regular base backups (pgbackrest/WAL-G) for PITR and disaster recovery.- Regular automated restore drills in isolated envs and monitoring of replication lag and archive health.- Use standby snapshots/clones and replication copies to keep production performance unaffected.
HardSystem Design
48 practiced
Design an analytical data platform for 100 TB of stored data, 1 TB/day ingest, and support for 5k concurrent BI users with sub-minute freshness on recent data. Provide architecture covering ingestion, storage tiering, query engine(s), caching, metadata/catalog, security, and cost-control measures from an SRE operational view.
Sample Answer
Requirements clarified:- 100 TB existing, +1 TB/day, sub-minute freshness for recent data, 5k concurrent BI users, SRE focus on reliability/ops.High-level architecture:- Ingest layer -> Real-time store (hot) + Object lake (cold) -> Query layer (interactive + analytical) -> Cache/materialized layer -> Metadata/catalog + Security + Ops.Ingestion:- Hybrid: Kafka (or Confluent Cloud) for event streaming, CDC with Debezium for DBs; streaming jobs in Flink/Beam for enrichment, schema validation, dedupe. Backfill/batch via Airflow triggering Spark jobs.- Guarantees: exactly-once where needed (Kafka+Flink), schema registry (Confluent/Apicurio).Storage / Tiering:- Hot: columnar OLAP store for recent minutes/hours (Apache Pinot or ClickHouse) kept for last 24–72 hrs to meet sub-minute freshness.- Warm: Partitioned Parquet on S3 (or GCS/Azure Blob) with daily/hourly partitions for last 30 days.- Cold: Compressed Parquet/ORC with long-term retention on cheaper storage class (S3 Glacier) and compaction jobs.- Use Hive/Glue metastore for table definitions.Query Engines:- Interactive BI: Trino/Presto federated to S3 + Hive metastore for historical queries.- Real-time low-latency: Pinot for dashboards requiring sub-second to sub-minute freshness.- Push-down predicates and column pruning enabled.Caching & Aggregations:- Materialized views in Pinot/ClickHouse for high-cardinality aggregates.- Distributed cache (Redis/ElastiCache) for heavy repeated dashboard panels; CDN for exported assets.- Query result cache in Trino coordinator with TTL.Metadata & Catalog:- Glue/Hive metastore + Apache Atlas for lineage, schema evolution.- Schema registry for event formats; Data Quality metrics stored in monitoring DB.Security:- Network: VPCs, subnets, private endpoints to object storage.- Authz/authn: Central IAM (AWS IAM/Keycloak) with RBAC for datasets, fine-grained column-level masking via Ranger.- Encryption: TLS in transit, SSE-KMS for at-rest.- Audit logging: all access logged to immutable storage and forwarded to SIEM.SRE/Operational Controls:- SLOs: query p95 latency, availability, freshness SLA (e.g., 1 min for hot tier). Define error budgets and automated rollbacks.- Autoscaling: node groups with HPA/Cluster Autoscaler for compute (Presto/Pinot) using metrics (QPS, CPU, queue depth). Use spot instances for batch worker pools; on-demand reserved for control plane/critical services.- Monitoring/Alerting: Prometheus + Grafana for system metrics, OpenTelemetry traces for queries, alert on ingestion lag, compaction backlog, coordinator memory pressure, cache hit-rate.- Runbooks & Chaos: runbooks for common incidents, automated remediation (circuit breakers, fallback to cached results), periodic chaos tests.Cost-control measures:- Lifecycle policies to tier older partitions to cheaper storage automatically.- Compaction and partition pruning to reduce query scan volume.- Query governance: per-user/resource pools, query concurrency limits, cost-based query rejection, sampled dry-run billing alerts.- Use spot/commit discounts and right-sizing recommendations; cold data compressed and infrequently scanned.- Metering + chargeback for BI teams.Trade-offs:- Pinot adds operational complexity but vital for sub-minute freshness; Trino gives flexible ad-hoc analytics over lake.- Emphasize automation (deployments with IaC, blue/green), thorough monitoring, and SRE playbooks to keep 5k concurrent users reliable.
HardSystem Design
39 practiced
Design a zero-downtime schema migration process for a globally distributed, sharded database where the new schema must be available in all regions within one hour, and rollbacks must be safe and quick. Include coordination mechanisms, backward-compatible schema patterns, feature flags, data copy strategies, and rollback criteria.
Sample Answer
Requirements & constraints:- Zero downtime across global regions; new schema live in all regions within 1 hour.- Safe, quick rollback capability.- Sharded DB (per-customer or range-based), multi-region replication.- Backward compatibility so old and new code can co-exist during rollout.High-level approach:1. Clarify: read/write patterns, shard map, replication lag SLO, migration scope (DDL only vs. data transformation).2. Use a phased, online-compatible migration: expand schema → backfill/copy data → cutover via feature flags → retire old schema.Architecture & components:- Orchestrator service (runs migration plan, per-shard state machine) with Paxos/RAFT-backed coordination (etcd/consul) to ensure single leader and idempotent steps.- Per-region migration agents that perform local DDL and backfill; report progress to orchestrator.- Global coordination via central control plane that maintains migration version, target timestamp, and per-shard status.Backward-compatible schema patterns:- Additive DDL only: add columns/tables/indexes; avoid destructive changes.- Use shadow tables/columns for transformed data: - New column col_v2 NULLABLE, populate progressively. - For table splits, create new_table and populate with consistent keys; keep original table until cutover.- Dual-write: application writes both old and new schema during backfill phase (idempotent).Data copy strategies:- Online backfill per shard with rate limiting and resumable workers.- Use change data capture (CDC) (e.g., Debezium or DB native) to capture writes while backfill runs; apply to target columns/tables to ensure no loss.- Validate with checksums/hashes per shard and low-latency verification queries.- Prioritize critical shards and parallelize per-shard to meet 1-hour target; pre-calc estimated time and scale workers accordingly.Feature flags & rollout:- Central feature-flag service controlling read/write behavior per region and per-shard.- Phases: 1. Prep: create additive schema everywhere (DDL online where supported, or rolling DDL per region). 2. Backfill + CDC: run backfill; enable dual-write via feature-flag (app writes both). 3. Read switch: enable reads to prefer new schema behind feature-flag for a small % of traffic (canary by region/shard). 4. Full cutover: flip feature flag globally to read new schema and stop reading old. 5. Cleanup: after stability window, drop old columns/tables.Coordination & timing to meet 1-hour:- Run DDL in parallel per region; additive changes usually fast.- Backfill time is main constraint — estimate per-shard size; scale worker pool and use parallel per-shard backfills to finish within window.- If some shards lag, hold cutover for those shards behind flag; cutover per-shard as they finish.Rollback & safety:- Rollback criteria (automatic + manual): - Error rate increase beyond SLO (e.g., 5xx > threshold). - Read/write latency spike beyond threshold. - Data inconsistency detected by verification checksums. - CDC lag exceeds safe window.- Rollback actions: - Use feature flags to instantly revert reads/writes to old schema (fast). - Stop backfill/CDC workers; run compensating sync if partial writes occurred. - Orchestrator marks shards as failed; initiate remediation (replay CDC or re-run backfill).- Fast rollback is achieved because we kept old schema intact and dual-write enabled—flipping flags reverts behavior with no DDL needed.Observability & testing:- Preflight tests: dry-run on shadow traffic, execute on canary shards.- Metrics: per-shard progress, CDC lag, application errors, latencies, checksum mismatches.- Alerts: automated playbook in runbooks tied to thresholds to trigger rollback.- Post-migration validation: run end-to-end tests, randomized reads across old/new schema, and data integrity checks.Trade-offs:- Dual-write increases write path complexity and slight overhead.- Keeping old schema longer increases storage but enables instant rollback.- If DDL is not online in some DB engines, schedule rolling schema changes with careful lock mitigation.This plan ensures zero-downtime, per-shard controlled rollout with rapid rollback using feature flags, CDC-backed synchronization, orchestrated coordination, and strong observability to meet the 1-hour global availability and fast safe rollback requirements.
HardTechnical
45 practiced
You're oncall for a cloud data warehouse that suddenly shows increased storage IO and degraded query times. Walk through an incident response: key telemetry to collect (query patterns, concurrency, scan sizes), immediate mitigations to reduce user impact, root cause hypotheses, and long-term remediation steps you would take.
Sample Answer
Situation: Alerts show sustained spike in storage I/O and query latency across the cloud data warehouse during my on-call shift.1) Key telemetry to collect (first 10–30 minutes)- Query-level: top-N slow queries, recent query text/plans, user/role, start time, duration, retries.- Concurrency: number of active queries, queued queries, connection counts per workspace.- Scan/IO metrics: bytes scanned per query, read amplification, random vs sequential IO, per-disk/volume IOPS and latency, queue depth.- Resource metrics: CPU, memory, network, storage node utilization, GC/paging, per-shard hot spots.- Storage system: cache hit ratio, eviction rate, compaction/merge activity, background maintenance jobs.- Metadata: schema/table-level access patterns, recent deployments/config changes, ACLs, scheduled jobs (ETL).- Correlate with alerts, recent commits, and user tickets.2) Immediate mitigations (reduce user impact)- Throttle/pausing: identify and pause or rate-limit largest scanners (ad-hoc heavy queries, long-running ETLs).- Kill runaway queries: terminate top offenders after notifying owners.- Move heavy jobs to backfill window: reschedule batch jobs.- Apply query-level resource governor: lower concurrency or CPU/IO limits.- Enable or increase caching (if safe) and reduce compaction intensity.- Scale horizontally if cloud permits: add read replicas or scale storage IOPS temporarily.- Communication: status page update and targeted DMs to critical customers.3) Root-cause hypotheses (how to validate)- Sudden high-cardinality scan: new ad-hoc query or dashboard change causing full-table scans — validate by query texts & scan bytes.- Hot partition or skew: a small subset of shards receiving disproportionate reads — validate per-shard IOPS and latency.- Background maintenance: compactions/repairs causing IO storm — check maintenance logs and compaction schedules.- Code/config regression: recent deploy changed planner or disabled predicate pushdown — check deploy history and A/B diffs.- Resource exhaustion: cache misses due to eviction (memory pressure) — validate cache hit/miss trends.- Network/storage degradation: underlying cloud volume performance degradation — validate cloud provider metrics and incidents.4) Long-term remediation- Query governance: enforce resource limits, cost-based alerts for high-scan queries, and automated query kill policies for >X bytes scanned.- Observability improvements: add per-query byte-scan histograms, per-shard IO dashboards, and anomaly detection for scan patterns.- Capacity planning: increase baseline IOPS, implement autoscaling for storage layer, and pro-active load tests.- Optimizations: encourage partitioning/column pruning, materialized views, and result caching for dashboards.- Resiliency: isolate noisy tenants via resource pools, fair-scheduler, and per-tenant quotas.- Process: run a blameless postmortem with RCA, publish runbook updates, add runbook automation to throttle known patterns.- CI/CD guardrails: require performance regression tests for planner/optimizer changes and canary rollouts.Result: Short-term actions limit customer impact; telemetry+postmortem prevent recurrence via governance, scaling, and engineering fixes.
Unlock Full Question Bank
Get access to hundreds of Database and Data Platform Selection interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.