API And Database Ingestion Patterns

What's being tested

Interviewers are probing your ability to design reliable, scalable ingestion for APIs that persist data into databases while handling retries, ordering, and failure modes. They want concrete tradeoffs: synchronous vs asynchronous ingestion, idempotency, deduplication, batching, and how to meet latency and throughput targets. Expect to justify storage choices, transactional guarantees, and runtime behaviors under partial failure.

Core knowledge

Synchronous vs asynchronous ingestion — synchronous (HTTP/gRPC) is simple and low-latency but ties API availability to DB throughput; asynchronous (publish to Kafka/queue) decouples write spikes and enables smoothing via consumers.
Idempotency keys — store a client-provided idempotency-key (or server-generated token) and the operation result; use a unique constraint to guarantee one effective write per key and return cached result on retries.
At-least-once vs exactly-once semantics — most practical systems use at-least-once with deduplication; exactly-once across distributed components is expensive and typically approximated via idempotent operations or idempotency tables.
Upsert / conflict handling — INSERT ... ON CONFLICT (Postgres) or REPLACE/INSERT IGNORE patterns prevent duplicate rows; watch uniqueness constraints and race conditions under concurrent writers.
Transactional boundaries — prefer single-node DB transactions for atomicity; cross-service ACID (2PC) is brittle — prefer idempotency and compensating actions over distributed transactions.
Batching and aggregation — batch writes (size and interval) reduce DB overhead and increase throughput; choose batch size by latency SLOs and DB write amplification; batching reduces per-write tx overhead.
Backpressure and flow-control — expose 429/Retry-After or use per-client quotas; in async flows, monitor queue lag and throttle producers or shed load gracefully.
Ordering guarantees — if ordering matters, partition by ordering key (Kafka partition key) and ensure single consumer per partition; otherwise design operations to be order-independent (commutative).
Deduplication strategies — short-term in-memory caches (Redis), persistent dedup tables keyed by event-id, or idempotent writes using natural keys; consider TTLs and storage costs.
Schema evolution & validation — validate payloads at the API boundary; evolve DB schemas with additive changes and use a schema registry or documented versioned contracts for consumers.
Monitoring & SLAs — track p99 write latency, queue lag, error rates, and idempotency-hit ratio; set alerts on retry storms and duplicate-write increases.
Throughput sizing heuristics — a single Postgres primary can often handle a few thousand small writes/sec; beyond ~10k writes/sec, introduce batching, horizontal sharding, or a write-optimized store (Cassandra, ClickHouse or partitioned ingestion pipeline).

Worked example

Example interview prompt: "Design an API that ingests user events into a database with at-least-once delivery and idempotency."

Frame the problem: clarify expected latency SLO, peak throughput, and whether ordering per user is required; ask if clients can attach an event-id or if the server must generate one. A strong answer organizes around (1) ingestion API contract and validation, (2) transport model (sync vs async), (3) deduplication/idempotency mechanism, and (4) persistence schema and failure handling. Propose an HTTP POST /events that requires a client event-id (idempotency key). Synchronously validate and enqueue to Kafka for durable buffering; consumer does batched INSERT ... ON CONFLICT into Postgres, writing to an events table with a unique constraint on (user_id, event_id). Tradeoff to flag: synchronous-only design simplifies ordering but risks high latency under DB load; the async Kafka buffer increases end-to-end time but improves availability and smoothing. Close by saying: "If I had more time I'd prototype the idempotency cache eviction policy, add client-side exponential backoff guidance, and sketch monitoring dashboards and chaos tests for partial failures."

A second angle

Alternate prompt: "Expose a low-latency API that writes critical transactions (money transfers) directly to the primary database with strict consistency."

Same core concepts apply but constraints change: you likely must use synchronous ingestion with immediate DB transaction commit and stronger isolation (SERIALIZABLE or explicit locking) to prevent double-spend. Idempotency remains essential: require a monotonic transaction-id and persist it in a transactions table with uniqueness, performing balance updates within the same DB transaction. Discuss latency vs throughput tradeoffs: stronger isolation increases contention; to scale, vertical-scaling of DB or sharding accounts by range/hashed key may be necessary. Also mention compensating transactions and audit trails as practical fallbacks if cross-shard transfers are required.

Common pitfalls

Pitfall: Assuming retries won’t arrive — many clients will retry automatically; failing to design idempotency/dedup leads to duplicate writes and data corruption. Always assume duplicate requests.

Pitfall: Choosing SERIALIZABLE as a first resort — it prevents anomalies but dramatically increases aborts under contention; prefer application-level idempotency and careful schema design before stronger isolation.

Pitfall: Ignoring operational signals — not surfacing queue lag, idempotency-hit rate, or duplicate counts will let production retry storms silently degrade systems; define meaningful metrics and run injection tests.

Connections

These designs frequently pivot into adjacent topics: stream processing (exactly-once semantics in Kafka Streams / Flink), data modeling for high-throughput writes (Cassandra, append-only event stores), and API rate limiting / quota enforcement. Interviewers may ask to shift into those areas.