Database Selection and Trade-offs Questions

Clarifying assumptions

• Bursts up to 1M points/min (~16.6k pts/s), variable. Each point small JSON (~200B). AWS-focused managed services but ideas are cloud‑agnostic.

High-level architecture

• Front door: API Gateway / ALB + Lambda (or autoscaling fleet of ingress services) with TLS, authentication (IAM/Cognito/MTLS) and per-device rate limits.
• Buffer/queueing: Managed streaming — Amazon Kinesis Data Streams or MSK (Kafka). Streams smooth bursts, provide retention for replays.
• Hot-path writes: Consumer group(s) read from stream, batch and write to a purpose-built TSDB for low-latency reads: Amazon Timestream or InfluxDB Cloud. Use batched writes, schema with device_id, metric, timestamp, tags.
• Cold storage: Consumers also compact and bulk-write compressed columnar Parquet to S3 (partitioned by date/device/metric) using Glue jobs or Kinesis Data Firehose with buffering.
• Analytics/query:
  • Hot queries (recent minutes/hours): query TSDB/Influx via HTTP APIs for dashboards/alerts.
  • Historical/aggregations: Athena/Redshift Spectrum over S3 Parquet, or run Spark on EMR for heavy ML.
  • Nearline rollups: periodic jobs compute downsampled metrics into cheaper store (DynamoDB/ElastiCache) for common queries.
• Observability & ops: CloudWatch/Prometheus, tracing, DLQs from stream to S3 for failed messages.

Cost control & optimizations

• Buffer to absorb peaks and autoscale consumers; throttle device-side using jitter/backoff to avoid synchronized spikes.
• Batch writes to TSDB; tune write/ingest units (Kinesis shards/MSK partitions) to match throughput.
• Tier data: keep hot in TSDB for short window (days/weeks), then expire/ingest to S3 for long-term.
• Use Parquet/ORC compression and partitioning to reduce Athena scan costs.
• Sample or aggregate device-side when acceptable; apply TTLs, lifecycle policies, and downsampling.
• Monitor cost metrics and set alerts; use spot/compute-savings for batch analytics.

Trade-offs

• Timestream gives managed TS capabilities but costlier per-write than S3+Athena; Kafka offers more control than Kinesis but higher management (use MSK).
• Design favors resilience and replayability via streams, low-latency via TSDB, and low-cost long-term storage via S3.

Goal & scope
Compare relational DB (e.g., PostgreSQL) vs document store (e.g., MongoDB) for a latency-sensitive API; measure tail latency, throughput, and operational behavior under realistic load.

Workloads to simulate

• Read-heavy: 95% GETs, simple primary-key lookups and small joins/aggregations.
• Write-heavy: 70% INSERT/UPDATE mix, with different document sizes.
• Mixed: 80/20 read/write with bursty traffic and connection reuse.
• Complex queries: multi-table joins, indexes, and text search for relational; multi-field projections and aggregation pipelines for document store.
• Failure modes: node restart, network partition, replica lag.

Key metrics

• Latencies: p50, p95, p99, max, and histogram
• Throughput: ops/sec
• Error rates and timeouts
• Resource metrics: CPU, memory, disk I/O, network, lock/contention
• Recovery metrics: failover time, replication lag

Ensure realistic results

• Use production-like dataset size and index distribution
• Run tests from app-tier clients (connection pools, prepared statements)
• Use realistic concurrency and think-time distributions (Poisson/bursty)
• Warm caches, run sustained steady-state before measurement
• Isolate background noise (dedicated test environment or controlled cloud instances)
• Run multiple iterations at different times of day

Interpreting variability

• Use multiple runs, report confidence intervals and coefficient of variation
• Investigate causes when p99 flares: GC pauses, long queries, locks
• Compare shape of latency histograms, not just averages
• Consider operational trade-offs (consistency, schema evolution, ease of scaling) alongside numbers
• Make recommendation based on SLA targets (e.g., p99 < 100 ms) and operational fit.

Brief framing
As a backend developer I weigh trade-offs across operational burden, cost, scalability, and team skills. I use a checklist to decide managed vs self‑managed.

Operational

• Managed: automated backups, patching, high availability, automated failover, built‑in monitoring.
• Self‑managed: full control over OS, extensions, custom backup/restore, but you own patching, HA, DR, and runbooks.

Cost

• Managed: higher unit cost but fewer operational FTE hours and predictable billing.
• Self‑managed: lower instance licensing on paper, but hidden ops cost (SRE time, backups, downtime impact).

Scaling

• Managed: vertical/horizontal autoscaling, read replicas, serverless options (Aurora Serverless, DynamoDB autoscale).
• Self‑managed: fine‑grained tuning, custom sharding, but scaling often manual and slower.

Team-skill

• Managed: suitable if team lacks DBAs or wants fast delivery.
• Self‑managed: requires DB expertise (tuning, backups, HA), security ops, capacity planning.

Example where self‑managed is preferable
When you need a PostgreSQL build with custom C extensions (PostGIS + proprietary extension), specific kernel tunings, strict I/O control, and can allocate a dedicated DBA/SRE — self‑managed gives the necessary control and predictable performance.

Situation
I joined a team maintaining a monolithic backend on a single RDBMS (Postgres) that required quarterly maintenance windows and limited scalability.

Task
Deliver a 12‑month incremental modernization roadmap to improve availability and scalability while keeping risk low.

Action — 12‑Month Roadmap (quarterly milestones)

• Q1 — Stabilize & Measure
  • Add structured monitoring (Prometheus/Grafana), slow‑query logging, connection pool tuning.
  • Introduce blue/green deploys and automated backups with point‑in‑time recovery.
  • Metrics: mean time to recovery (MTTR), query p95 latency, DB lock counts.
• Q2 — Read Scalability & Cache
  • Add a managed Redis layer for caching read‑heavy endpoints; implement cache invalidation patterns.
  • Implement read replicas for Postgres; route non‑critical reads.
  • Metrics: cache hit ratio, read replica lag, decrease in primary DB CPU.
• Q3 — Service Boundaries & CQRS Pilot
  • Extract 1–2 stable read/write bounded contexts into small services (e.g., user profiles, product catalog).
  • Use event-driven sync (Kafka or SNS) where eventual consistency is acceptable.
  • Introduce a document store (MongoDB) for flexible schema use cases if needed.
  • Metrics: percent traffic served by services, deployment success rate, incident count.
• Q4 — Scale & Remove Maintenance Windows
  • Migrate critical workloads to horizontally scalable services; employ multi‑AZ managed databases.
  • Automate failover, enable rolling maintenance with zero downtime.
  • Run chaos experiments to validate resilience.
  • Metrics: downtime per month, RPS handled per instance, cost per request, recovery time objective (RTO)/recovery point objective (RPO).

Result / Risk Management

• Mitigate risk with feature flags, canary releases, and backout plans. Each extraction is reversible and covered by tests. Expect progressive reductions in scheduled maintenance and measurable improvements in availability and scalability within 12 months.

Approach summary
Use change-data-capture (CDC) → durable streaming buffer → parallel indexer consumers with idempotent upserts to the search index so updates appear <5s.

Architecture

• CDC from primary DB (Debezium/Postgres logical decoding) → Kafka topic(s).
• Kafka serves as durable buffer and backpressure surface; partition by sharding key (e.g., tenant or hash of PK) for parallelism.
• Consumer group of indexers (Kubernetes pods) read with low max.poll.interval and use small batch windows (e.g., 100–500ms) to keep end-to-end latency low.
• Index into Elasticsearch/OpenSearch using bulk API with upsert (doc_id = PK) and doc version or sequence number.

Ingestion & buffering details

• Set Kafka retention short for hot topics; increase partitions to scale consumers.
• Tune Elasticsearch refresh_interval (e.g., 1s or explicit refresh after bulk) to ensure visibility <5s.
• Provide a fast-path for critical objects: lightweight async write to a “hot” index or direct write to index in the write transaction using outbox pattern if strong freshness needed.

Idempotency & correctness

• Include event metadata: source lsn/txid, op type, timestamp.
• Use upsert (index with same doc_id) and optimistic versioning: only apply events with higher sequence/version.
• Support tombstone messages for deletes.
• Deduplication store optional: a small Redis/leveldb cache of recent event ids for at-least-once consumers.

Reliability & observability

• Retries with exponential backoff and DLQ for poison messages.
• Metrics: lag (Kafka consumer lag), end-to-end latency, indexing failures.
• Chaos test: simulate peak writes to validate <5s SLA.

Why this works
Kafka decouples spikes, parallel consumers increase throughput, upsert+versioning ensures idempotency and correctness, and tuning refresh/explicit refresh keeps results visible within seconds.