Intermediate everything in Databases & Architecture, page 3
Pooled connection lifecycle and close() semantics
Borrow from pool, use, then close() returns it to the pool rather than tearing down the socket.
RBAC vs direct user grants
Direct grants tie rights to individuals; RBAC groups rights into roles users inherit, so changes happen once per role.
Point-in-Time Recovery (PITR)
Restore a base backup then replay archived write-ahead logs up to a chosen moment, enabling recovery to just before an error.
Connection pooling and its key parameters
Reuse open connections to skip costly handshakes; tune max pool size and connection timeout.
What is eventual consistency?
Replicas converge to the same value if writes stop, allowing temporary staleness for higher availability and lower latency.
Leader-follower vs multi-leader replication
Single-writer leader-follower is simple but a write bottleneck; multi-leader accepts writes in many regions for latency and availability.
Range-based vs hash-based sharding trade-offs?
Range sharding keeps ordered keys together, great for range scans but prone to hot spots on sequential keys; hash sharding spreads keys evenly, avoiding hot spots but killing efficient range…
Apply the CAP theorem to a real system
Define C, A, P; note partitions are unavoidable, so the real choice during one is consistency versus availability; then classify a system as CP or AP with reasoning.
What is a Type 2 slowly changing dimension?
An SCD handles dimension attributes that change over time; Type 2 inserts a new row per change with a surrogate key and validity dates, marking one current.
How does columnar storage speed up analytics?
Columnar stores each column contiguously, so aggregations read only needed columns, scan far less data, and compress better with vectorized execution.
Star schema vs snowflake schema trade-offs?
Star keeps dimensions denormalized for fewer joins and faster simpler queries; snowflake normalizes dimensions into sub-tables saving space and easing maintenance but adding joins.
Why fit Cassandra to a high-read, high-write workload?
Consistent-hash partitioning spreads load, replication and no single master give availability, log-structured writes are fast, tunable consistency balances per query.
What are eventual consistency and the BASE model?
Eventual consistency means replicas converge given no new writes; BASE is Basically Available, Soft state, Eventually consistent.
Embed or reference likes in a document database?
Embedding is fast for small bounded lists but unbounded likes hit document size limits; referencing scales for high-cardinality, write-heavy likes.
Sorting data larger than memory
External merge sort reads memory-sized chunks, sorts each in RAM and writes them as sorted runs to disk, then merges many runs together in passes until one sorted output remains.
Hash join versus sort-merge join
Hash join builds and probes a hash table, great for unsorted equality joins with enough memory; sort-merge sorts both inputs then merges, winning when inputs are already sorted or output must…
B+ Tree range queries across pages
Internal nodes are pages of keys guiding the search, all data sits in linked leaf pages; a range query descends to the start key then follows the leaf chain sequentially until the upper bound.
Lifecycle of a single row update
Buffer manager faults the page in, the row is modified in memory marking the page dirty, a WAL record is written, and commit fsyncs the log while the dirty page is flushed later by a checkpoint.
Heap file versus clustered index
A heap stores rows unordered with cheap inserts but no inherent ordering; a clustered or index-organized table stores rows in primary-key order, giving fast key range reads but costlier inserts and page…
Database checkpoints with WAL
A checkpoint flushes dirty pages and records a known-good point so recovery can start later in the log; frequent checkpoints shorten recovery but add I/O spikes, infrequent ones lengthen…
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles