Everything in Databases & Architecture, page 5
What is the difference between OLTP and OLAP?
OLTP handles many short read-write transactions on normalized current data; OLAP runs few large analytical scans over denormalized historical data.
What consistency do you sacrifice in an AP system?
You give up linearizability and often sequential consistency, accepting stale reads and conflicts, then mitigate with quorums, vector clocks or CRDTs, and…
How do you keep consistency without multi-document transactions?
A Saga runs a sequence of local transactions, each with a compensating action to undo on failure, coordinated via choreography or orchestration.
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.
What is the CAP theorem?
Consistency, Availability, Partition tolerance; during a network partition you must choose between staying consistent or staying available.
How do you choose between relational and NoSQL databases?
Relational gives schema, joins, and ACID for structured related data; document gives flexible schema and horizontal scale for varied or denormalized data.
How does a hash join handle memory overflow?
The build table is partitioned by hash and spilled to disk, then probe rows are partitioned the same way, and pairs are joined per partition.
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…
Joining a large table with a small one
With a tiny table the optimizer often picks a hash join, building a hash table on the small side in memory, then probing it once per row of the large table in a single pass.
Why developers read query plans
The plan shows the operators the optimizer chose to run a query; developers read it to find why a query is slow; a common thing to look for is a full table scan where an index was expected.
Stages of executing a SELECT query
Parse the SQL into a tree, bind and validate against the catalog, optimize into a physical plan, then execute the plan operators fetching data and returning rows.
Using page LSN to decide redo
Each page stores the LSN of its last applied change; during redo the engine reapplies a log record only if its LSN exceeds the page's LSN, meaning the change is not yet reflected on disk.
Row-oriented versus columnar storage
Row stores keep whole rows together, ideal for point reads and writes; columnar stores keep each column contiguous, enabling reading only needed columns and strong compression, ideal for scans and…
The three phases of ARIES recovery
Analysis rebuilds dirty-page and transaction tables from the last checkpoint, Redo replays all logged changes to restore state, Undo rolls back losers; Redo is idempotent via per-page LSN comparison so…
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…
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