Databases
229 bites tagged Databases — interview questions with model answers, and 60-second explainers.
Blue-green deploys with schema migrations
The shared database means both versions hit one schema, so breaking changes must be split into backward-compatible steps via expand-and-contract. Stateful deploy safety.
Cutting managed database costs without breaking SLOs
Pool connections, prune and tune indexes, offload reads, tier or partition cold data, right-size storage IOPS. workload-aware cost optimization. only shrinking the instance and ignoring what drives the spend.
Designing an HA/DR strategy for an OLTP database
Sync standby in-region for zero data loss, async cross-region for DR, automated failover with a quorum. tying replication choices to RPO and RTO. claiming sync replication is free of latency cost.
Diagnosing and optimizing a slow production query
Read the EXPLAIN ANALYZE plan, find the costly node, then fix via indexing, rewrite, or stats. methodical query-performance debugging. guessing at indexes before reading the actual execution plan.
Optimizing queries on a billion-row fact table
Partition to prune scans, index for selective lookups, materialize views to precompute aggregates; each adds write or maintenance cost. large-table query strategies.
Logical vs physical query plans and the optimizer
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. query-planning stages.
How databases implement GROUP BY aggregation
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. aggregation strategies.
The Volcano iterator model of query execution
Each operator exposes open/next/close, parents pull tuples from children, uniform composable interface, pipelined low memory. pull-based tuple-at-a-time execution.
Phantom reads and how serializable prevents them
New rows matching a predicate appear between reads; classic Repeatable Read locks existing rows not ranges; Serializable uses range or predicate locks. range-based anomaly understanding.
Choosing a time-series database for metrics
High-ingest timestamped writes, time-window queries, retention and downsampling, time-optimized compression. fit of TSDBs to append-heavy time data.
Diagnosing database latency layer by layer
Split total time into pool-wait, query execution, and ORM-generated query patterns; use metrics at each layer. structured latency diagnosis.
Vectorized query execution and its speedups
Process column batches per operator call, amortize per-tuple overhead, use cache locality and SIMD. batch-at-a-time processing benefits.
The buffer pool's role in database IO
Caches pages, serves reads from RAM, buffers dirty writes flushed later, uses eviction like LRU. in-memory page caching of disk data.
Predicate pushdown and why it speeds queries
Apply WHERE conditions at the scan or remote source, prune partitions and rows early, shrink data movement. moving filters close to the data.
Denormalization: trading write cost for read speed
Duplicate or precompute data to avoid joins, accept harder writes and consistency risk, justify by read-heavy access. deliberate redundancy for read performance.
Tuning a database connection pool
Max size, min idle, connection and max-lifetime timeouts; size from cores and latency, not guesswork. connection-pool sizing intuition.
The N+1 query problem and how to fix it
One query for a list plus one per item for its relation, fix with eager loading or a batched join. recognizing ORM lazy-loading waste. blaming the database, or fixing it by caching instead of reducing round trips.
Synchronous vs asynchronous replication trade-offs
Sync waits for replica ack giving zero data loss but higher latency; async acks immediately, faster but risks losing recent writes on failover. durability versus latency tradeoff.
Choosing a good shard key and avoiding hot spots
High cardinality, even write distribution, query alignment; monotonic keys send all writes to one shard. shard-key design tradeoffs. optimizing only for cardinality while ignoring write skew, or claiming any unique key works.
When a graph database beats relational or document stores
Deeply connected data, variable-depth traversals, fraud or recommendation paths, index-free adjacency. fit between graph data and traversal queries.
SQL isolation levels and the anomalies they prevent
Read Uncommitted allows dirty reads; Read Committed blocks them; Repeatable Read blocks non-repeatable reads; Serializable blocks phantoms. the isolation-anomaly mapping.
Zero-downtime schema migration on a hot table
Expand-migrate-contract phases, dual-write and backfill, decouple deploys from migrations. safe online schema evolution. a single blocking ALTER plus drop-old-column in one release, breaking running code.
Cache-aside pattern with Redis and RDS
App checks cache, on miss reads DB and populates, writes invalidate the key, and consistency is eventual. knowing the lazy-loading cache pattern and its costs.
Near-zero-downtime database migration to cloud
Assess and provision, do a full load then continuous CDC replication with a tool like DMS, validate, then cut over with a rollback plan. planning a low-downtime migration. a one-shot dump-and-restore with a long outage.
Get Databases bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.