More in Backend Dev — page 9
Cutting managed database costs without breaking SLOs
WHAT IT TESTS: workload-aware cost optimization. OUTLINE: pool connections, prune and tune indexes, offload reads, tier or partition cold data, right-size storage IOPS. RED FLAG: only shrinking the instance and ignoring what drives the spend.
Designing an HA/DR strategy for an OLTP database
WHAT IT TESTS: tying replication choices to RPO and RTO. OUTLINE: sync standby in-region for zero data loss, async cross-region for DR, automated failover with a quorum. RED FLAG: claiming sync replication is free of latency cost.
Diagnosing and optimizing a slow production query
WHAT IT TESTS: methodical query-performance debugging. OUTLINE: read the EXPLAIN ANALYZE plan, find the costly node, then fix via indexing, rewrite, or stats. RED FLAG: guessing at indexes before reading the actual execution plan.
Optimizing queries on a billion-row fact table
WHAT IT TESTS: large-table query strategies. OUTLINE: partition to prune scans, index for selective lookups, materialize views to precompute aggregates; each adds write or maintenance cost.
Logical vs physical query plans and the optimizer
WHAT IT TESTS: query-planning stages. OUTLINE: logical plan says what (relational algebra, no algorithms); physical plan says how (specific operators); cost-based optimizer enumerates physical options and picks the cheapest using statistics.
How databases implement GROUP BY aggregation
WHAT IT TESTS: aggregation strategies. OUTLINE: hash aggregation builds a hash table keyed by group holding running aggregates; sort aggregation orders rows then aggregates adjacent groups; optimizer picks based on data and memory.
The Volcano iterator model of query execution
WHAT IT TESTS: pull-based tuple-at-a-time execution. OUTLINE: each operator exposes open/next/close, parents pull tuples from children, uniform composable interface, pipelined low memory.
Phantom reads and how serializable prevents them
WHAT IT TESTS: range-based anomaly understanding. OUTLINE: new rows matching a predicate appear between reads; classic Repeatable Read locks existing rows not ranges; Serializable uses range or predicate locks.
Choosing a time-series database for metrics
WHAT IT TESTS: fit of TSDBs to append-heavy time data. OUTLINE: high-ingest timestamped writes, time-window queries, retention and downsampling, time-optimized compression.
Diagnosing database latency layer by layer
WHAT IT TESTS: structured latency diagnosis. OUTLINE: split total time into pool-wait, query execution, and ORM-generated query patterns; use metrics at each layer.
Sessionizing clickstream events into sessions
WHAT IT TESTS: grouping events into sessions and grain choice. OUTLINE: order events per user, split on inactivity gap, assign session ids, pick event or session grain.
Vectorized query execution and its speedups
WHAT IT TESTS: batch-at-a-time processing benefits. OUTLINE: process column batches per operator call, amortize per-tuple overhead, use cache locality and SIMD.
The buffer pool's role in database IO
WHAT IT TESTS: in-memory page caching of disk data. OUTLINE: caches pages, serves reads from RAM, buffers dirty writes flushed later, uses eviction like LRU.
Predicate pushdown and why it speeds queries
WHAT IT TESTS: moving filters close to the data. OUTLINE: apply WHERE conditions at the scan or remote source, prune partitions and rows early, shrink data movement.
Denormalization: trading write cost for read speed
WHAT IT TESTS: deliberate redundancy for read performance. OUTLINE: duplicate or precompute data to avoid joins, accept harder writes and consistency risk, justify by read-heavy access.
Upgrading a stateful Flink job without losing state
WHAT IT TESTS: stateful stream-job upgrades. OUTLINE: take a savepoint, stop with drain, deploy new jar, restore from savepoint with matching operator UIDs.
Tuning a database connection pool
WHAT IT TESTS: connection-pool sizing intuition. OUTLINE: max size, min idle, connection and max-lifetime timeouts; size from cores and latency, not guesswork.
The N+1 query problem and how to fix it
WHAT IT TESTS: recognizing ORM lazy-loading waste. OUTLINE: one query for a list plus one per item for its relation, fix with eager loading or a batched join. RED FLAG: blaming the database, or fixing it by caching instead of reducing round trips.
Synchronous vs asynchronous replication trade-offs
WHAT IT TESTS: durability versus latency tradeoff. OUTLINE: sync waits for replica ack giving zero data loss but higher latency; async acks immediately, faster but risks losing recent writes on failover.
Choosing a good shard key and avoiding hot spots
WHAT IT TESTS: shard-key design tradeoffs. OUTLINE: high cardinality, even write distribution, query alignment; monotonic keys send all writes to one shard. RED FLAG: optimizing only for cardinality while ignoring write skew, or claiming any unique key works.