Interview questions
Databases Interview Questions
100 database and SQL interview questions, answered using a four-part structure: the concept in plain terms, what happens inside the storage engine or query engine underneath it, the trade-off it accepts, and the concrete production rule or fix that follows from it. Covers SQL fundamentals, schema design, indexing and query optimization, transactions and concurrency, storage engines, replication and distributed scaling, NoSQL, and practical troubleshooting.
Jump to a level
- Level 1: SQL fundamentals — 6 questions
- Level 2: Advanced SQL — 9 questions
- Level 3: Data modeling and design — 9 questions
- Level 4: Storage, the physical layer — 9 questions
- Level 5: Indexing — 10 questions
- Level 6: Query processing and optimization — 6 questions
- Level 7: Transactions and concurrency control — 12 questions
- Level 8: Recovery and durability — 2 questions
- Level 9: Replication — 3 questions
- Level 10: Partitioning and distributed transactions — 13 questions
- Level 11: NoSQL and alternative data models — 8 questions
- Level 12: Big data and analytics — 3 questions
- Level 13: Practical performance, operations and interviews — 10 questions
Level 1: SQL fundamentals
SQL fundamentals — interview questions
Answer in your head first, then open it.
What is the exact logical order of execution of a SQL query (FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT)?
Concept: SQL is written in one order but the engine evaluates clauses in a different, fixed logical order: FROM and JOIN first, then WHERE, then GROUP BY, then HAVING, then SELECT, then DISTINCT, then ORDER BY, then LIMIT. Engine Mechanics: The query planner builds a logical plan that assembles rows from FROM/JOIN, discards rows failing WHERE before any grouping happens, forms groups and computes aggregates, filters those groups with HAVING, projects the SELECT list, removes duplicates for DISTINCT, sorts for ORDER BY, and finally truncates for LIMIT; this is why a column alias defined in SELECT cannot be referenced in WHERE, since WHERE runs before SELECT is evaluated. Trade-off: Fixing this order gives predictable semantics everywhere, but it means filtering as early as possible, in WHERE rather than HAVING, is almost always cheaper, since HAVING runs after the expensive grouping work is already done. Production Rule: I push every filter that does not depend on an aggregate into WHERE, and reserve HAVING strictly for conditions on aggregated values, since that ordering difference is a direct and measurable cost on large tables.
Differentiate between WHERE and HAVING. When can you filter in WHERE vs. HAVING?
Concept: WHERE filters individual rows before any grouping occurs; HAVING filters groups after aggregation has already produced them. Engine Mechanics: The engine applies WHERE during the row-scan stage, discarding non-matching rows before they are ever grouped, then computes GROUP BY aggregates over whatever rows survived, and only then evaluates HAVING against those aggregate results, since a condition like SUM(amount) > 100 has no meaning until the sum exists. Trade-off: A condition that could be expressed in WHERE but is instead written in HAVING forces the engine to aggregate rows it would otherwise have discarded, wasting work on rows that never belonged in the result. Production Rule: Any condition on a raw column, such as status = 'active', belongs in WHERE; only a condition on an aggregate function's result, such as COUNT(*) > 5, belongs in HAVING.
Compare INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN.
Concept: These describe which rows survive when combining two tables: INNER keeps only matching pairs, LEFT and RIGHT keep every row from one side even without a match, FULL OUTER keeps every row from both sides, CROSS pairs every row with every row, and SELF simply joins a table to itself. Engine Mechanics: For INNER and OUTER joins the engine evaluates the join condition and, for the outer variants, pads the side with no matching row using NULLs in place of that row's columns; CROSS JOIN skips the condition entirely and produces the full Cartesian product, with no filtering at all; a SELF JOIN uses the same table twice under two aliases, so the engine treats it as an ordinary two-table join with no special-case logic. Trade-off: Outer joins preserve information that an inner join would silently drop, at the cost of introducing NULLs that every downstream calculation, such as a SUM or an average, must account for, or risk silently undercounting. Production Rule: I default to INNER JOIN unless I specifically need unmatched rows preserved, and I always wrap outer-joined columns feeding into aggregates with COALESCE so a missing match does not quietly skew a total.
What is the difference between UNION and UNION ALL in terms of behavior and performance?
Concept: UNION combines two result sets and removes duplicate rows; UNION ALL combines them and keeps every row, including duplicates. Engine Mechanics: UNION ALL simply concatenates the two input streams with no extra work; UNION must additionally sort or hash the combined rows to detect and discard duplicates, an extra pass over the full result set that UNION ALL never performs. Trade-off: Deduplication is exactly the behavior some queries need, but paying for a sort or hash step on a result set that is already known to be duplicate-free is pure wasted cost. Production Rule: I use UNION ALL by default and only reach for UNION when duplicates are actually possible and actually unwanted, since the performance gap grows directly with result size.
Compare DELETE, TRUNCATE, and DROP (transaction logs, triggers, rollback, auto-increment reset).
Concept: DELETE removes rows one at a time and can be filtered; TRUNCATE empties an entire table in one operation; DROP removes the table's structure itself, not just its data. Engine Mechanics: DELETE is logged row by row in the transaction log, fires any row-level triggers, and can be rolled back; TRUNCATE deallocates the table's data pages directly rather than logging individual row removals, which is why it is far faster but, depending on the engine, may not fire triggers and may reset an auto-increment counter back to its starting value; DROP removes the table definition and its data entirely from the catalog. Trade-off: TRUNCATE's speed comes directly from skipping the per-row logging and trigger overhead that DELETE pays for, but that same shortcut is why TRUNCATE's rollback behavior and auto-increment reset vary by engine and must be checked rather than assumed. Production Rule: For clearing a large table I use TRUNCATE when triggers and exact rollback semantics are not required, and DELETE with a WHERE clause, often batched, whenever only a subset of rows should go or row-level triggers must fire.
Explain NULL handling in SQL—why does col = NULL fail, and how do COALESCE(), NULLIF(), and IS NULL work?
Concept: NULL represents an unknown value, not a value equal to anything, including itself, which is why standard SQL's three-valued logic makes col = NULL evaluate to unknown rather than true or false. Engine Mechanics: The engine's comparison operators are defined to return UNKNOWN whenever either operand is NULL, and a WHERE clause only keeps rows where the condition evaluates to TRUE, so an UNKNOWN result is silently discarded just like FALSE; IS NULL is a special predicate the engine evaluates directly against a row's null bitmap rather than as a value comparison; COALESCE scans its argument list and returns the first non-NULL value; NULLIF returns NULL if its two arguments are equal, otherwise the first argument. Trade-off: Three-valued logic correctly models genuine unknowns, but it is a frequent source of bugs when a developer assumes <> will catch NULL rows the way it catches every other mismatched value. Production Rule: I never compare against NULL with = or <>; I use IS NULL or IS NOT NULL for existence checks, and COALESCE whenever a NULL needs to be treated as a specific default value in an expression.
Level 2: Advanced SQL
Advanced SQL — interview questions
Answer in your head first, then open it.
What are Window Functions (ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD()), and how do they differ from GROUP BY?
Concept: A window function computes a value across a set of related rows while still returning one output row per input row, unlike GROUP BY, which collapses many rows into one row per group. Engine Mechanics: The engine evaluates a window function by partitioning the result set using PARTITION BY, ordering each partition using ORDER BY, and then computing the function, a running rank, a lag to the previous row, a lead to the next, over that ordered partition, attaching the result back onto every original row rather than discarding any of them. Trade-off: Keeping every row intact makes window functions far more flexible for per-row context such as "this row's rank within its group," but that same row-preserving behavior means a window function cannot reduce row count the way GROUP BY can, so the two are not interchangeable. Production Rule: I reach for a window function whenever the output needs to stay at the original row grain with extra per-row context attached, and for GROUP BY only when the desired output is genuinely one row per group.
Compare ROW_NUMBER(), RANK(), and DENSE_RANK() when there are duplicate values (ties).
Concept: All three number rows within an ordered partition, but they disagree specifically on how ties are handled. Engine Mechanics: ROW_NUMBER() assigns a strictly increasing integer to every row regardless of ties, arbitrarily breaking any tie; RANK() gives tied rows the same rank number but then skips the next rank number by the size of the tie group, leaving a gap; DENSE_RANK() also gives tied rows the same number but never leaves a gap, incrementing by exactly one for the next distinct value. Trade-off: ROW_NUMBER()'s arbitrary tie-breaking guarantees a unique number per row, which is essential for deduplication, but it is the wrong choice whenever tied values genuinely deserve the same rank. Production Rule: I use ROW_NUMBER() when I need exactly one row kept per group, such as deduplication, RANK() when I want ties to share a rank with a visible gap afterward, and DENSE_RANK() when I want a compact, gapless ranking, such as "2nd highest salary" where ties should share 2nd place.
What is a CTE (Common Table Expression) vs. a Subquery vs. a Temp Table? When does a CTE get materialized?
Concept: All three hold an intermediate result for reuse within a query, but they differ in scope, persistence, and how the engine treats them internally. Engine Mechanics: A subquery is inlined directly into the surrounding query's plan and typically re-evaluated wherever it is referenced; a CTE is named and can be referenced multiple times within the same query, and depending on the engine and query, may either be inlined like a subquery or materialized into a temporary result once and reused, a choice the optimizer makes based on cost or, in some engines, an explicit hint; a temp table is an actual physical table written to disk or temporary storage, persisting across multiple statements in the same session until dropped. Trade-off: Materializing a CTE avoids recomputing an expensive subquery multiple times, but a materialized CTE also loses the chance for the optimizer to push filters from the outer query down into it, which an inlined version would allow. Production Rule: I use a CTE purely for readability when a subquery would be repeated or deeply nested, a temp table when intermediate results need to survive across multiple separate statements, and I check the actual execution plan rather than assuming materialization either way.
How do Recursive CTEs work, and where are they used (e.g., org charts, bill of materials, graph traversal)?
Concept: A recursive CTE repeatedly applies a query to its own previous output until no new rows are produced, which is how SQL expresses traversal over a hierarchy or graph without knowing its depth in advance. Engine Mechanics: The engine first runs the CTE's base case to produce an initial row set, then repeatedly runs the recursive term, joining the CTE to itself on a parent-child or edge relationship, feeding each round's new rows back in as input to the next round, and stops once a round produces zero new rows; most engines enforce a maximum recursion depth as a safety limit against an accidental infinite loop, such as a cycle in the data. Trade-off: Recursive CTEs let a single SQL statement express traversal that would otherwise require iterative application code, but they can scan proportionally to the depth of the hierarchy, and an undetected cycle without a depth guard can spin until the engine's recursion limit kills the query. Production Rule: I always include an explicit depth counter and a WHERE guard against runaway recursion, and for an org chart or bill of materials I keep an index on the parent-key column, since every recursive step re-joins on it.
What is the difference between a Correlated Subquery and a Non-Correlated Subquery?
Concept: A non-correlated subquery runs independently of the outer query and produces one result reused for every outer row; a correlated subquery references a column from the outer query and must be logically re-evaluated for each outer row. Engine Mechanics: The engine can usually run a non-correlated subquery exactly once and treat its result as a constant or a small set for the rest of the query; a correlated subquery's dependency on the outer row means the naive execution plan re-runs it once per outer row, though a capable optimizer will often rewrite it into a join or a semi-join to avoid literally repeating the work row by row. Trade-off: Correlated subqueries are often the most natural way to express "for each outer row, check something about related rows," but on an optimizer that cannot rewrite them, they scale far worse than an equivalent join. Production Rule: I check the execution plan for any correlated subquery on a large table, and rewrite it as an explicit JOIN or EXISTS clause if the plan shows it being re-evaluated per row rather than rewritten into a set-based operation.
Compare EXISTS vs. IN vs. JOIN for checking existence—when is each faster?
Concept: All three can check whether a related row exists, but they differ in how they handle NULLs and how the optimizer is able to execute them. Engine Mechanics: EXISTS stops as soon as it finds one matching row and is commonly rewritten by the optimizer into a semi-join that short-circuits per outer row; IN evaluates against a list or subquery result and, critically, behaves unexpectedly if that list contains NULL, since comparing against a NULL in the list can make the whole IN evaluate to unknown; a plain JOIN used for existence checking risks producing duplicate outer rows if the inner side has multiple matches, unless explicitly deduplicated. Trade-off: EXISTS is generally the safest and most efficient choice for a pure existence check because it short-circuits and ignores row multiplicity on the inner side, while IN's NULL-handling pitfall and JOIN's duplication risk both require extra care to use correctly. Production Rule: I default to EXISTS (or NOT EXISTS) for existence checks against another table, and reserve IN for small, NULL-free literal lists, checking the plan whenever a subquery list could contain NULLs.
How do you write an UPSERT in PostgreSQL (ON CONFLICT) and MySQL (ON DUPLICATE KEY UPDATE)?
Concept: An upsert inserts a new row, or updates the existing row instead, if a conflicting unique key already exists, avoiding the race condition of a separate check-then-insert. Engine Mechanics: PostgreSQL's INSERT ... ON CONFLICT (key) DO UPDATE SET ... attempts the insert, and if it collides with a unique or primary key constraint, atomically switches to the specified UPDATE within the same statement rather than failing; MySQL's INSERT ... ON DUPLICATE KEY UPDATE ... works the same way, detecting the duplicate key violation and applying the update clause instead of raising an error. Trade-off: Doing this atomically inside one statement avoids the check-then-insert race where two concurrent clients could both see "no existing row" and then both try to insert, but it ties the application's conflict-handling logic to syntax that differs by database engine, complicating any cross-engine portability. Production Rule: I use the engine's native upsert syntax for any insert-or-update pattern driven by a unique key, rather than a separate SELECT-then-INSERT-or-UPDATE in application code, specifically to close that race window.
What are Views vs. Materialized Views, and how do you handle stale data in Materialized Views?
Concept: A view is a stored query definition that is re-executed every time it is referenced; a materialized view stores the query's actual result physically, so reading it does not re-run the underlying query. Engine Mechanics: Querying a plain view causes the engine to substitute the view's definition into the surrounding query and plan it as if the underlying tables were queried directly, always reflecting current data; a materialized view is instead computed once and persisted like a table, and must be explicitly refreshed, fully or incrementally, to pick up changes made to the underlying tables since it was last computed. Trade-off: A materialized view trades data freshness for read speed, since reading it skips the real computation entirely, but that speed only holds between refreshes, during which the data can be arbitrarily stale. Production Rule: I use a materialized view specifically when a query is expensive and some staleness is acceptable, pairing it with either a scheduled refresh or an incremental refresh strategy sized to how fresh the data actually needs to be.
What are Stored Procedures, Functions, and Triggers, and why do many modern engineering teams avoid heavy business logic in triggers?
Concept: A stored procedure is a named, callable block of procedural logic stored in the database; a function is similar but returns a value and can typically be used inside a query; a trigger is procedural code the engine runs automatically in response to a specific table event such as an insert or update. Engine Mechanics: Procedures and functions execute only when explicitly called, giving the caller direct visibility into when that logic runs; a trigger instead fires implicitly as a side effect of a DML statement, inside the same transaction as the statement that caused it, which means a single innocuous-looking UPDATE can silently cascade into arbitrary additional work the caller never directly invoked. Trade-off: Triggers centralize business rules close to the data, guaranteeing they fire no matter which application touches the table, but that same implicitness makes behavior hard to trace, hard to test in isolation, and easy to accidentally chain into unexpected cascades or performance surprises. Production Rule: I keep triggers narrow, for things like audit columns or simple invariant checks, and push substantive business logic into application code or explicitly called procedures, where it stays visible in a stack trace instead of hiding behind an ordinary-looking write.
Level 3: Data modeling and design
Data modeling and design — interview questions
Answer in your head first, then open it.
Explain 1NF, 2NF, 3NF, and BCNF (Boyce-Codd Normal Form) with practical examples.
Concept: Each normal form is a progressively stricter rule for eliminating a specific kind of redundancy and the update anomaly it causes. Engine Mechanics: 1NF requires every column to hold a single atomic value, no repeating groups, such as splitting a comma-separated list of phone numbers into its own table; 2NF additionally requires every non-key column to depend on the whole primary key, not just part of a composite key, which rules out, for example, storing a customer's name in a table keyed by (order_id, product_id); 3NF further requires every non-key column to depend only on the key, not on another non-key column, eliminating transitive dependencies such as storing a city's zip code depending on a city column that itself depends on the key; BCNF tightens 3NF further by requiring every determinant, anything a column depends on, to itself be a candidate key. Trade-off: Each stricter level removes another category of redundancy and the anomaly it enables, an inconsistent update in one copy but not another, but each also typically requires another join to reassemble the same information at read time. Production Rule: I normalize to at least 3NF as the schema's baseline design, and only denormalize deliberately afterward, in specific, measured places where read performance has been shown to need it.
What is Denormalization, and when is it worth sacrificing data integrity/storage for read speed?
Concept: Denormalization intentionally reintroduces redundancy that normalization removed, storing the same fact in more than one place, to make reads faster at the cost of more complex writes. Engine Mechanics: A denormalized design might copy a customer's name directly onto every order row instead of joining to a customers table, so a read query avoids the join entirely, but any write that changes that customer's name must now update every order row that carries a copy, or accept that older rows show a stale value. Trade-off: Denormalization buys read speed and simpler read queries at the direct cost of write complexity and the risk of the copies drifting out of sync if an update misses one of them. Production Rule: I denormalize only after profiling shows a specific join is a genuine bottleneck on a hot read path, and I pair the denormalized copy with an explicit, tested mechanism, a trigger, an application-level write path, or an event, that keeps every copy in sync.
Compare Primary Key, Foreign Key, Unique Key, Candidate Key, and Surrogate Key.
Concept: These describe different roles a column or set of columns can play in identifying rows and relationships. Engine Mechanics: A candidate key is any column or column set that could uniquely identify a row; a primary key is the one candidate key actually chosen to identify rows, and the engine builds a unique index to enforce it and typically clusters storage around it; a unique key enforces uniqueness the same way but is not the chosen identifier, so a table can have several; a foreign key constrains a column to only hold values that exist as a primary or unique key value in another table, which the engine checks on every insert or update to that column; a surrogate key is an artificial identifier, typically an auto-incrementing integer or a generated ID, with no business meaning, chosen as the primary key instead of a natural but unstable business attribute. Trade-off: A surrogate key stays stable even if a natural attribute like an email address changes, but it adds an extra column with no inherent meaning that every join must now carry. Production Rule: I default to a surrogate key as the primary key whenever any candidate natural key could plausibly change, and I enforce every meaningful relationship explicitly with a foreign key rather than relying on application code to maintain it.
Compare Auto-Increment Integers, UUIDv4, and UUIDv7 / ULID / Snowflake IDs as primary keys (index fragmentation & B-tree page splits).
Concept: These are different strategies for generating unique primary key values, and they differ sharply in whether new values arrive in sorted order. Engine Mechanics: An auto-increment integer always grows monotonically, so new rows append to the end of the primary key's B+ tree with no disruption to existing pages; a random UUIDv4 has no ordering relationship to insertion time, so each insert lands at an essentially random point in the B+ tree, forcing page splits scattered throughout the index and poor cache locality; UUIDv7, ULID, and Snowflake IDs embed a timestamp in their high-order bits specifically so that, despite being globally unique and generatable without central coordination, they still sort roughly in insertion order like an auto-increment integer. Trade-off: A purely random UUID avoids any central coordination and hides sequential information, which can matter for security, but it pays for that with real index fragmentation and bloat under high insert volume; a time-ordered ID keeps the insert-locality benefit of an auto-increment key while still allowing decentralized generation. Production Rule: For a distributed system that cannot use a central auto-increment sequence, I use a time-ordered identifier like UUIDv7 or a Snowflake ID rather than a random UUIDv4, specifically to avoid the index fragmentation cost on a high-throughput insert-heavy table.
How do you model One-to-One, One-to-Many, and Many-to-Many relationships in relational tables?
Concept: These three relationship types require three different structural solutions in a relational schema. Engine Mechanics: A one-to-one relationship is modeled by placing a unique foreign key in one of the two tables, pointing at the other's primary key, with the uniqueness constraint preventing a second match; a one-to-many relationship places a plain, non-unique foreign key on the "many" side's table, pointing back at the "one" side's primary key; a many-to-many relationship requires a separate junction table holding two foreign keys, one to each side, with the pair of foreign keys together typically forming that junction table's own primary key. Trade-off: The junction table needed for many-to-many adds an extra join to every query that needs to traverse the relationship, but it is the only way to represent the relationship without duplicating rows on either side. Production Rule: I always add a composite unique constraint (or primary key) on the junction table's two foreign key columns together, which both documents the many-to-many intent and prevents the same pair from being inserted twice.
How do you model hierarchical or tree data in SQL (Adjacency List, Nested Sets, Materialized Path, Closure Table)?
Concept: Each of these four patterns trades off differently between how cheap it is to insert a node, query its ancestors, or query its full subtree. Engine Mechanics: An adjacency list stores just a parent_id foreign key on each row, making inserts and single-level lookups trivial but requiring a recursive query to find an entire subtree; nested sets store a left and right boundary number per node so an entire subtree can be found with one range query, but inserting a node requires renumbering a potentially large span of existing rows; a materialized path stores the full chain of ancestor IDs as a string or array on each row, making subtree queries a simple prefix match at the cost of having to update that path string on every row if an ancestor ever moves; a closure table stores every ancestor-descendant pair explicitly in its own table, making any ancestor or descendant query a direct lookup at the cost of a larger supporting table and more write amplification per insert. Trade-off: Every pattern that makes subtree reads cheap, nested sets, materialized path, closure table, does so by paying more at write time, while the adjacency list keeps writes cheap and pays for it with a recursive query on every subtree read. Production Rule: I default to the adjacency list unless subtree reads are frequent and the tree is read far more often than it is restructured, in which case a closure table is usually the safest of the write-heavier options to reason about and maintain.
Compare Soft Deletes (deleted_at timestamp) vs. Hard Deletes (unique constraints, index bloat, GDPR compliance).
Concept: A soft delete marks a row as deleted with a flag or timestamp while leaving it physically in the table; a hard delete physically removes the row. Engine Mechanics: A soft-deleted row still occupies table and index space and still participates in unique constraints unless those constraints are specifically scoped to exclude deleted rows, which is why a naive soft-delete design can block re-inserting a "new" row with a value that collides with an old soft-deleted one; a hard delete actually frees that row's space, subject to the engine's own reclamation mechanics, and permanently removes it from any constraint checking. Trade-off: Soft deletes preserve history and make an accidental "delete" trivially reversible, but they accumulate rows indefinitely unless purged, add a WHERE deleted_at IS NULL to every query that must exclude them, and complicate uniqueness; hard deletes keep tables lean and satisfy strict erasure requirements like GDPR's right to be forgotten, but they are irreversible. Production Rule: I use soft deletes for data where recoverability or audit history matters, with a partial unique index that explicitly excludes soft-deleted rows, and hard delete whenever a regulatory erasure requirement, like GDPR, actually demands the data be gone.
How do you store semi-structured data (JSON vs. JSONB in Postgres), and when should you extract fields into dedicated columns?
Concept: JSON and JSONB both store semi-structured documents inside a relational column, but JSON stores an exact text representation while JSONB stores a parsed, binary form. Engine Mechanics: A plain JSON column keeps the original text byte for byte, including whitespace and key order, and must be re-parsed on every access; JSONB parses the document once at write time into a binary structure that supports efficient indexed lookups, such as a GIN index on specific keys, at the cost of a small amount of extra processing on write and the loss of exact formatting and key order. Trade-off: JSONB is faster to query and can be indexed, but a field accessed and filtered on constantly inside a JSONB blob still costs more per access than a genuine dedicated column with its own ordinary index. Production Rule: I use JSONB by default for genuinely variable or sparse attributes, but once a specific field inside it becomes something every query filters or joins on, I promote that field out into its own typed, indexed column.
Compare Star Schema and Snowflake Schema in data warehousing (OLAP).
Concept: A star schema joins a central fact table directly to a flat set of dimension tables; a snowflake schema further normalizes those dimension tables into additional levels. Engine Mechanics: In a star schema, an analytical query joins the fact table to each dimension table exactly once; in a snowflake schema, some of those dimension tables are themselves split into further related tables, so the same query must traverse additional joins to reach the same descriptive attribute. Trade-off: Snowflaking reduces redundant storage inside dimension tables, but it adds extra joins to nearly every analytical query, and on large fact tables that join cost usually outweighs the comparatively cheap storage saved. Production Rule: I default to a star schema for analytical workloads and only snowflake a specific dimension when that dimension's redundancy is unusually large and the extra join cost has been measured and accepted.
Level 4: Storage, the physical layer
Storage, the physical layer — interview questions
Answer in your head first, then open it.
Compare B-Tree Storage Engines (InnoDB, Postgres) vs. LSM-Tree (Log-Structured Merge-Tree) Storage Engines (RocksDB, Cassandra, ScyllaDB).
Concept: A B-tree engine updates data in place on disk, directly modifying the page a row lives on; an LSM-tree engine never modifies existing data in place, instead buffering writes in memory and flushing them as new, immutable sorted files. Engine Mechanics: A B-tree write locates the exact page containing the row and rewrites that page, which for a random write pattern means random disk access; an LSM-tree write appends to an in-memory structure and a write-ahead log, which is always a sequential write, and only later merges and compacts those immutable files in the background. Trade-off: The B-tree's in-place updates keep reads simple, one lookup finds the current value directly, but random writes are expensive on spinning or even flash storage; the LSM-tree's append-only writes are fast, but a read may need to check several layers of files before finding the most recent value, and background compaction consumes real disk and CPU. Production Rule: I pick a B-tree engine for workloads with heavy point lookups and moderate write rates, and an LSM-tree engine for write-heavy workloads where ingest throughput matters more than the absolute fastest point read.
How do MemTables, SSTables (Sorted String Tables), and Compaction work in an LSM-Tree database?
Concept: A memtable is the in-memory structure absorbing new writes; an SSTable is an immutable, sorted file on disk that a memtable eventually becomes; compaction is the background process that merges SSTables together over time. Engine Mechanics: Writes land first in the memtable, kept sorted in memory; once the memtable reaches a size threshold, it is flushed to disk as a new SSTable and a fresh, empty memtable takes over; because SSTables accumulate over time and the same key can appear, stale, in several of them, compaction periodically merges multiple SSTables into fewer, larger ones, discarding overwritten or deleted entries along the way. Trade-off: This design makes every write cheap and sequential, but a read for a single key may have to check the memtable and multiple SSTables before finding the latest value, and compaction itself consumes disk I/O and CPU that competes with foreground traffic. Production Rule: I monitor compaction backlog as a leading indicator of trouble, since a system falling behind on compaction will see read latency climb as more and more SSTables accumulate per key before compaction catches up.
What is a Bloom Filter, and how does it prevent unnecessary disk reads in LSM-Tree engines?
Concept: A bloom filter is a compact, probabilistic structure that can say "this key is definitely not here" with certainty, or "this key might be here" without certainty, never the reverse. Engine Mechanics: Each SSTable keeps a small bloom filter summarizing which keys it might contain; before reading an SSTable's data from disk to check for a key, the engine first checks that SSTable's bloom filter in memory, and if the filter says the key is definitely absent, the engine skips reading that SSTable from disk entirely. Trade-off: A bloom filter can produce false positives, wrongly saying a key might be present when it is not, costing one wasted disk read, but it can never produce a false negative, so it never causes a real key to be missed; this asymmetry is deliberate, trading a small, tunable false-positive rate for a filter that is far smaller than storing the actual keys. Production Rule: I size a bloom filter's false-positive rate against the actual cost of a wasted disk read for that workload, since a lower false-positive rate means a larger filter held in memory, a direct trade between memory use and avoided disk I/O.
Explain Read Amplification, Write Amplification, and Space Amplification in storage engines.
Concept: These describe three different ways an engine ends up doing more physical work or using more physical space than the logical operation alone would require. Engine Mechanics: Read amplification happens when answering one logical read requires checking multiple physical structures, such as an LSM-tree read checking several SSTables for one key; write amplification happens when one logical write causes multiple physical writes, such as a single row update in an LSM-tree eventually being rewritten several more times as compaction merges it through successive levels; space amplification happens when stale or deleted data still occupies disk space because it has not yet been reclaimed, such as an overwritten key's old value sitting in an older SSTable until compaction removes it. Trade-off: An engine design that minimizes one kind of amplification often increases another; LSM-trees accept higher read amplification and temporary space amplification specifically to minimize write amplification on the ingest path, while a B-tree accepts higher random-write cost to keep read amplification at essentially one lookup. Production Rule: When diagnosing unexpectedly high disk usage or I/O on an LSM-based system, I check compaction state first, since lagging compaction directly shows up as elevated read and space amplification simultaneously.
What is a Database Page / Block (e.g., 8KB in Postgres, 16KB in InnoDB), and how does the Buffer Pool manage dirty pages?
Concept: A page, or block, is the fixed-size unit the storage engine reads from and writes to disk in, never a smaller fraction of it; the buffer pool is an in-memory cache of these pages. Engine Mechanics: Every row lives inside some page, and reading even a single row requires loading its entire containing page into the buffer pool if it is not already cached; modifying a row marks its in-memory page as dirty, meaning it now differs from the on-disk copy, and the engine defers actually writing that dirty page back to disk until a checkpoint or until the buffer pool needs to evict it to make room for another page. Trade-off: Deferring dirty-page writes lets the engine batch and reorder physical writes for efficiency, but it means a crash before a dirty page is flushed would lose that change if the write-ahead log did not already durably record it first, which is exactly why the log, not the page itself, is what guarantees durability. Production Rule: I size the buffer pool as large as memory reasonably allows for a working set, since a cache miss on a page means a full disk read, and I treat checkpoint frequency as a direct trade between recovery time after a crash and steady-state write overhead.
What is TOAST (The Oversized-Attribute Storage Technique) in PostgreSQL?
Concept: TOAST is PostgreSQL's mechanism for storing a column value too large to fit comfortably inside a normal page, such as a long text or JSON value, outside the main row. Engine Mechanics: When a row's total size would exceed a threshold relative to the page size, PostgreSQL moves its largest variable-length column out into a separate TOAST table, optionally compressing it first, and leaves only a small pointer to that out-of-line value in the main row; reading that column then requires an additional lookup into the TOAST table, while reading only the row's other, smaller columns does not. Trade-off: TOASTing keeps the main table's rows small and fast to scan even when a table has occasional very large values, but any query that does need the large column pays for an extra lookup it would not need if the value were stored inline. Production Rule: I avoid SELECT * on tables with large TOASTed columns when only the smaller columns are actually needed, since skipping the TOASTed column entirely avoids triggering its extra out-of-line fetch.
What is Checksumming and Torn Page Protection (doublewrite buffer in InnoDB)?
Concept: Checksumming detects silent data corruption by storing a computed checksum alongside each page and verifying it on every read; torn-page protection guards specifically against a page write that was interrupted partway through, leaving it half-old and half-new. Engine Mechanics: On every page write, the engine computes a checksum over the page's contents and stores it with the page; on every read, it recomputes the checksum and compares, flagging a mismatch as corruption; because a physical disk page write can be interrupted mid-write by a power loss, writing only part of the new page over the old one, InnoDB's doublewrite buffer first writes the full page to a separate, contiguous staging area before writing it to its real location, so that after a crash the engine can detect and repair a torn page from the intact doublewrite copy rather than from a page that is neither fully old nor fully new. Trade-off: The doublewrite buffer doubles the physical write work for every page flush, but it closes a correctness gap that checksumming alone can detect but not actually fix. Production Rule: I leave checksumming and doublewrite buffering enabled in production despite their overhead, since the alternative is undetected or unrecoverable corruption after exactly the kind of crash that is otherwise hardest to reproduce and debug.
How does Partitioning (Range, List, Hash partitioning) differ from Sharding?
Concept: Partitioning splits one logical table into multiple physical pieces that still live on the same database instance; sharding splits data across multiple separate database instances, typically on different machines. Engine Mechanics: Range partitioning assigns rows to a partition based on where a column's value falls within defined boundaries, such as a date range; list partitioning assigns rows based on matching a value against an explicit list, such as a specific region code; hash partitioning assigns rows based on a hash of a column, spreading them evenly without any meaningful ordering; in every case, the engine running on one machine knows about and can query across all partitions transparently. Trade-off: Partitioning keeps the simplicity of a single database instance, one place to query, one set of locks and transactions, while still getting some of sharding's benefits, such as pruning irrelevant partitions from a scan, but it does not solve the problem of a single machine's storage or throughput ceiling the way true sharding across separate machines does. Production Rule: I reach for partitioning first, specifically for its query-pruning and maintenance benefits on a very large single table, and only move to true cross-machine sharding once a single instance's own capacity, not just its table size, becomes the actual bottleneck.
When should you partition a single large table (e.g., time-series logs), and what is Partition Pruning?
Concept: Partitioning a large table makes sense when queries predictably filter on the same column used to split it, most commonly a date or timestamp for time-series data; partition pruning is the optimizer's ability to skip scanning partitions it can prove cannot contain matching rows. Engine Mechanics: When a query's WHERE clause filters on the partitioning column, such as event_date >= '2026-11-01', the planner can determine from each partition's defined boundaries which partitions could possibly satisfy that condition and exclude every other partition from the scan entirely, often before even touching their indexes. Trade-off: Partitioning makes queries that naturally filter on the partition key dramatically cheaper by pruning away irrelevant partitions, but a query that does not filter on that column at all gains nothing and still has to scan every partition, and partitioning adds real maintenance overhead for creating and retiring partitions over time. Production Rule: I partition a large table only when the dominant query pattern genuinely filters on a single, stable column such as a date, and I automate partition creation and old-partition cleanup rather than managing it by hand.
Level 5: Indexing
Indexing — interview questions
Answer in your head first, then open it.
How does a B+ Tree Index work under the hood, and why is it preferred over binary search trees or hash indexes for disk storage?
Concept: A B+ tree is a balanced, multi-way search tree whose leaf nodes hold the actual indexed values in sorted order, and whose internal nodes exist purely to route a search down to the right leaf quickly. Engine Mechanics: Each node holds many keys, not just one or two, sized to fit exactly one disk page, so a lookup that needs log-base-fanout(n) node visits translates to very few actual disk reads even for a huge index, since the fanout per page is large; the sorted leaf level additionally supports efficient range scans by simply walking sideways through leaves, something a hash index structurally cannot do since it has no concept of order. Trade-off: A binary search tree's low fanout means far more levels, and therefore far more disk reads, for the same number of keys, while a hash index gives faster exact-match lookups than a B+ tree but cannot support range queries or sorted output at all. Production Rule: I use a B+ tree index, the default for most relational engines, whenever range queries, sorting, or ordered scans matter, and only consider a hash index for a workload that is purely exact-match lookups and never needs ordering.
Compare Clustered Indexes and Non-Clustered (Secondary) Indexes (and how InnoDB vs. PostgreSQL implement them differently).
Concept: A clustered index determines the physical order rows are actually stored in on disk; a non-clustered index is a separate structure that points back to wherever the row actually lives. Engine Mechanics: InnoDB clusters every table's data physically by its primary key, so a primary-key lookup finds the row directly in the clustered structure, while every secondary index stores the primary key value as its pointer, meaning a secondary-index lookup requires a second trip into the clustered primary-key structure to fetch the full row; PostgreSQL has no true clustered storage by default, every index, including the primary key's, is a separate structure pointing at a row's physical location in the heap, so even a primary-key lookup in Postgres involves a pointer-following step into the heap. Trade-off: InnoDB's clustering makes primary-key lookups and primary-key-ordered range scans especially fast, but it means a secondary index lookup always pays for an extra indirection back through the primary key; Postgres treats every index uniformly, which is simpler but gives up that specific primary-key locality advantage. Production Rule: On InnoDB, I design the primary key itself to match the table's dominant access pattern wherever possible, since that key's ordering directly determines physical storage locality.
What is a Composite (Multi-Column) Index, and how does the Leftmost Prefix Rule work?
Concept: A composite index is built over more than one column together, in a specific declared order, rather than one column alone; the leftmost prefix rule says the index can only be used efficiently for queries that filter starting from its leftmost column onward, in order. Engine Mechanics: The B+ tree for a composite index on (a, b, c) is sorted first by a, then by b within each a, then by c within each (a, b), so a query filtering on a alone, or on a and b together, can use the index efficiently by walking directly to the matching range, but a query filtering only on b or only on c, skipping a, cannot use this index to jump directly to matching rows, since those values are scattered throughout the tree rather than grouped together. Trade-off: A well-ordered composite index can serve several different query patterns that share a common leftmost prefix, avoiding the need for several separate single-column indexes, but getting the column order wrong relative to actual query patterns makes the index far less useful than intended. Production Rule: I order a composite index's columns to match the most selective and most frequently filtered leftmost columns first, and I check that every important query actually filters starting from that leftmost column before relying on the index to serve it.
What is a Covering Index (Index-Only Scan), and how does INCLUDE improve query speed?
Concept: A covering index is an index that contains every column a specific query needs, both the filtered columns and the selected columns, so the engine never has to visit the main table at all. Engine Mechanics: A normal index scan finds matching keys in the index's leaf nodes and then performs a separate lookup into the main table's heap or clustered structure to fetch any column not present in the index itself; a covering index, built using INCLUDE in Postgres or SQL Server to add extra columns to the index's leaves without making them part of the search key, lets the engine satisfy the entire query directly from the index leaves in what is called an index-only scan, skipping the heap lookup entirely. Trade-off: A covering index eliminates expensive random lookups into the main table on read, but every included column also has to be stored and kept updated inside the index, enlarging it and slowing down every insert, update, or delete that touches those columns. Production Rule: I add INCLUDE columns only for high-frequency, latency-sensitive queries where the execution plan shows real heap or table lookups being eliminated, and I avoid including large or rarely-needed columns that would bloat the index for little benefit.
Compare B-Tree, Hash, GIN (Inverted), GiST, and BRIN (Block Range) indexes—when do you use each?
Concept: Each index type is built around a different kind of query it is meant to answer efficiently. Engine Mechanics: A B-tree index supports equality, range, and sorted-order queries on ordinary scalar values; a hash index supports only exact equality lookups, with no ordering; a GIN, generalized inverted index, maps individual elements inside a composite value, such as words in a document or elements in an array, back to the rows containing them, which is what powers full-text search and array containment queries; a GiST, generalized search tree, supports more flexible, often geometric or range-overlap queries where a strict sort order does not naturally exist, such as finding all rectangles overlapping a given point; a BRIN, block range index, stores only a small summary, such as a min/max, per block of physically contiguous pages, trading precision for a tiny index size, useful specifically when a column's values correlate strongly with physical storage order, like an ever-increasing timestamp. Trade-off: The more specialized index types, GIN, GiST, BRIN, are each dramatically better than a B-tree for their specific query shape, but each is also largely useless or even misleading outside that shape, so choosing the wrong one wastes space without helping any real query. Production Rule: I pick the index type by matching it to the actual query predicate, equality and range go to B-tree, full-text or array containment to GIN, geometric overlap to GiST, and a huge, naturally time-ordered append-only table to BRIN for its tiny footprint.
What is a Partial (Filtered) Index, and how does it shrink index size?
Concept: A partial index only indexes the subset of rows that satisfy a specified WHERE condition, rather than every row in the table. Engine Mechanics: When building the index, the engine evaluates the partial index's condition for every row and includes only matching rows' entries in the index structure, so the resulting index physically contains fewer entries than a full index over the same column would, and the optimizer only considers using it for queries whose own WHERE clause logically implies the index's condition. Trade-off: A partial index can be dramatically smaller and faster to maintain than a full index when the indexed condition only matters for a small slice of rows, such as active or unprocessed records, but it is useless for any query that does not match its specific condition, and it only helps if that exact slice is genuinely what most queries filter on. Production Rule: I use a partial index specifically for columns where queries overwhelmingly care about a small, well-defined subset of rows, such as WHERE status = 'pending' on a table where most rows are not pending, rather than indexing every row's status value.
Why might a database optimizer choose a Full Table Scan even when an index exists on the filtered column?
Concept: The optimizer picks the plan it estimates will be cheapest overall, and an index is not automatically cheaper than scanning the whole table. Engine Mechanics: The optimizer uses stored statistics about the column's data distribution to estimate how many rows a given filter will match; if the filter is expected to match a large fraction of the table, following the index means paying for many scattered row lookups back into the table in addition to the index traversal itself, which can cost more in total disk I/O than simply reading the table sequentially from start to finish. Trade-off: Favoring a sequential scan for a low-selectivity filter avoids the random-access penalty of jumping all over the table via an index, but it means a seemingly "indexed" query can still be slow if the filter is not actually selective, surprising a developer who assumed the index would always be used. Production Rule: I check the actual selectivity of a filter, not just whether an index exists, before expecting index usage, and I keep table statistics up to date, since stale statistics are a common cause of the optimizer wrongly estimating selectivity and picking the wrong plan.
Which query patterns break index usage (e.g., functions on indexed columns LOWER(email), leading wildcards LIKE '%abc', implicit type casts)?
Concept: An index is only useful for a query predicate that matches the exact values physically stored in it, so any transformation applied to the column at query time, rather than at index-build time, typically prevents the index from being used. Engine Mechanics: WHERE LOWER(email) = 'x' cannot use a plain index on email, because the index stores the original-case values, not their lowercased form, forcing a full scan with the function applied to every row instead; LIKE '%abc', with a leading wildcard, cannot use a standard B-tree index either, since the index is sorted by the string's beginning, and a search with an unknown prefix has nothing to anchor a tree traversal to; an implicit type cast, comparing an indexed integer column against a string literal, forces the engine to cast every stored value before comparing, again defeating the index's sorted structure. Trade-off: Avoiding these patterns usually means restructuring either the query or the schema, such as storing a precomputed lowercase column or using a trigram index for wildcard search, and both cost extra storage or write overhead to set up. Production Rule: I build a functional index matching the exact expression used in the query, such as an index on LOWER(email) itself, whenever a query predicate transforms a column, rather than hoping a plain index on the raw column will still be used.
What is the N+1 Query Problem, and how do you detect and fix it?
Concept: The N+1 problem happens when code fetches a list of N parent records with one query, then issues one additional query per parent to fetch each one's related child records, turning what could be a handful of queries into N+1 round trips. Engine Mechanics: Each of those N extra queries pays its own full network round trip and planning overhead even though it is retrieving only a small amount of data, so total latency scales linearly with the number of parent rows rather than staying roughly constant; this typically surfaces from an ORM that lazily loads a related object the first time it is accessed inside a loop over the parent list. Trade-off: Fixing it by eagerly joining or batching into one or two queries trades a slightly larger single result set, and sometimes a less normalized shape to parse in application code, for eliminating the per-row round-trip cost entirely. Production Rule: I detect N+1 patterns by watching query counts per request, not just query latency, and fix them with an explicit JOIN, an ORM's eager-loading option, or a single batched WHERE id IN (...) query in place of a query inside a loop.
How do Database Statistics and Histograms help the Cost-Based Optimizer (CBO) pick execution plans?
Concept: A cost-based optimizer chooses between candidate execution plans by estimating each plan's cost, and those estimates depend entirely on knowing how data is actually distributed, which is exactly what statistics and histograms provide. Engine Mechanics: The engine periodically samples a table's data to build statistics, such as the number of distinct values in a column and a histogram describing how values are distributed across buckets, and the optimizer uses those numbers to estimate how many rows a given WHERE condition will match, which then determines whether an index lookup or a sequential scan is predicted to be cheaper. Trade-off: Keeping statistics current costs time and I/O to recompute, especially on a large or rapidly changing table, but statistics that have gone stale relative to the table's actual current data lead the optimizer to misjudge selectivity and pick a genuinely worse plan. Production Rule: I make sure statistics-gathering, ANALYZE in Postgres, runs regularly and especially after a bulk load or a major data shift, since a sudden plan regression after a large data change is a common symptom of statistics that no longer reflect reality.
Level 6: Query processing and optimization
Query processing and optimization — interview questions
Answer in your head first, then open it.
What is Index Bloat and Table Bloat, and how do VACUUM / ANALYZE (Postgres) or OPTIMIZE TABLE (MySQL) handle it?
Concept: Bloat is disk space consumed by dead or obsolete row and index entries, left behind by updates and deletes, that has not yet been reclaimed for reuse. Engine Mechanics: In Postgres, an UPDATE does not modify a row in place; it writes a new row version and marks the old one dead, and a DELETE simply marks a row dead, so both tables and their indexes accumulate dead entries over time until VACUUM scans and reclaims that dead space for reuse by future writes, while ANALYZE separately refreshes the planner's statistics; MySQL's OPTIMIZE TABLE similarly rebuilds a table to reclaim space fragmented by updates and deletes. Trade-off: Running these maintenance operations consumes I/O and, in some modes, briefly locks the table, but skipping them lets dead space accumulate indefinitely, bloating both storage and the time spent scanning past dead entries on every query. Production Rule: I rely on autovacuum running continuously in the background in Postgres rather than disabling it, and I specifically investigate vacuum lag as a prime suspect whenever a table's on-disk size grows noticeably faster than its actual row count would explain.
How do you read and interpret EXPLAIN and EXPLAIN ANALYZE output (Seq Scan, Index Scan, Bitmap Heap Scan, Nested Loop vs. Hash Join vs. Merge Join)?
Concept: EXPLAIN shows the planner's chosen execution plan and its cost estimates without actually running the query; EXPLAIN ANALYZE actually executes the query and reports real measured timings and row counts alongside the plan. Engine Mechanics: A Seq Scan reads every row in table order; an Index Scan walks an index and fetches matching rows directly from the table one at a time; a Bitmap Heap Scan instead first builds an in-memory bitmap of matching row locations from the index, then fetches them from the table in physical order, reducing random I/O for queries matching many scattered rows; among join strategies, a Nested Loop re-scans the inner table once per outer row, cheap when the outer side is small, a Hash Join builds an in-memory hash table from one side and probes it with the other, efficient for larger unsorted inputs, and a Merge Join requires both inputs already sorted on the join key and walks them in lockstep. Trade-off: Reading only EXPLAIN's estimates can mislead when the planner's row-count estimates are wrong, which is exactly what EXPLAIN ANALYZE's actual measured rows exposes by comparison; a large gap between estimated and actual rows is itself a strong diagnostic signal. Production Rule: I always run EXPLAIN ANALYZE, not just EXPLAIN, when diagnosing a real slow query, and I specifically compare estimated versus actual row counts at each plan node to spot where the optimizer's assumptions diverge from reality.
Compare Nested Loop Join, Hash Join, and Merge Join—when does the query planner choose each?
Concept: These are the three classic physical algorithms for executing a join, and the planner picks among them based on the estimated size and sortedness of the two inputs. Engine Mechanics: A Nested Loop Join iterates the outer input and, for each row, searches the inner input, which is cheap overall only if the outer side is small or the inner side has a usable index to avoid a full re-scan each time; a Hash Join builds an in-memory hash table keyed on the join column from the smaller input, then streams the larger input through, probing the hash table once per row, with cost roughly proportional to the sum of both input sizes rather than their product; a Merge Join requires both inputs to already be sorted, or sorts them first, on the join key, then walks both sorted streams together in a single pass, which is especially efficient when the inputs are already naturally sorted, such as both coming from an index scan on the join column. Trade-off: The planner's choice reflects a genuine trade-off: Nested Loop avoids any upfront setup cost but scales poorly as both sides grow, Hash Join scales well for large unsorted inputs but needs memory for the hash table, and Merge Join is excellent when sortedness is already available but pays an explicit sort cost when it is not. Production Rule: I watch for a Nested Loop Join appearing where the planner badly underestimated the inner side's row count, since that combination, small expected, large actual, is one of the most common real-world causes of a query that is fast in testing but catastrophically slow in production.
What is Pagination performance (OFFSET / LIMIT vs. Keyset / Cursor-based pagination), and why does OFFSET 1000000 get slow?
Concept: OFFSET/LIMIT pagination asks the engine to skip a given number of rows before returning the next page; keyset, or cursor-based, pagination instead asks for rows strictly after the last row's known key value. Engine Mechanics: OFFSET 1000000 LIMIT 20 forces the engine to actually generate and then discard the first million matching rows in sorted order before it can return the next twenty, work that grows linearly with the offset itself; WHERE id > :last_seen_id ORDER BY id LIMIT 20 instead lets an index on id seek directly to the right starting point in roughly constant time, regardless of how deep into the result set that page is. Trade-off: Keyset pagination scales to arbitrarily deep pages at constant cost, but it does not support jumping directly to an arbitrary page number the way OFFSET nominally allows, since it only knows how to move forward or backward from a known position. Production Rule: For any UI or API that scrolls through a large or unbounded result set, I use keyset pagination keyed on an indexed, unique, ordered column, and reserve OFFSET/LIMIT for small, bounded result sets where deep pages are never actually reached.
What are the downsides of adding too many indexes on a write-heavy table?
Concept: Every index added to a table is not free; it must be updated on every insert, update, and delete that touches an indexed column, so indexes trade read speed for write cost. Engine Mechanics: An INSERT into a table with five indexes does not just write one row; it also inserts a corresponding entry into each of the five index structures, and an UPDATE that changes an indexed column must remove the old index entry and insert a new one in the same transaction, multiplying the write work by roughly the number of affected indexes. Trade-off: More indexes mean more query patterns can be served quickly, but each additional index adds write latency, additional WAL volume, and more pages competing for buffer pool space, and on a sufficiently write-heavy table this overhead can dominate overall throughput. Production Rule: I periodically audit indexes for actual usage, since an index that no query plan ever selects is pure write-path overhead with zero read-side benefit, and I remove it.
How do Vector Indexes (HNSW, IVFFlat) in pgvector work compared to standard B-Trees?
Concept: A vector index finds approximate nearest neighbors in a high-dimensional embedding space, a fundamentally different problem from a B-tree's exact or range match on a single sortable value. Engine Mechanics: HNSW, hierarchical navigable small world, builds a multi-layer graph of vectors where each layer lets a search take increasingly large, then increasingly fine, hops toward the query vector's nearest neighbors, giving good recall at speed but at real memory and build-time cost; IVFFlat first clusters all vectors into a fixed number of coarse buckets, and a query only searches within the handful of buckets nearest the query vector rather than scanning everything, which is cheaper to build but generally returns an approximate, not exact, set of nearest neighbors since it never searches outside its chosen buckets. Trade-off: Both approaches deliberately sacrifice guaranteed-correct results, true exhaustive nearest-neighbor search, for speed, since exhaustive search over a large, high-dimensional vector set is too slow to be practical. Production Rule: I tune a vector index's accuracy-versus-speed knobs, such as HNSW's search breadth or IVFFlat's bucket count, against measured recall on a real validation set, rather than assuming default settings fit a specific embedding dimensionality and dataset size.
Level 7: Transactions and concurrency control
Transactions and concurrency control — interview questions
Answer in your head first, then open it.
Explain the four ACID properties with concrete database failure scenarios.
Concept: ACID names the four guarantees a transaction is supposed to provide: atomicity, consistency, isolation, and durability. Engine Mechanics: Atomicity means a transaction's writes either all happen or none do, which the engine enforces by being able to undo a partially applied transaction if it fails partway, say, after debiting one account but before crediting another; consistency means a transaction can only move the database from one valid state to another, enforced by constraints the engine checks before allowing a commit; isolation means concurrent transactions do not see each other's uncommitted intermediate states, enforced through locking or multi-version concurrency control; durability means once a transaction is committed, it survives a crash, enforced by writing its changes to a write-ahead log before acknowledging the commit. Trade-off: Each guarantee has a real cost: strict isolation can serialize concurrent work and reduce throughput, and durability requires a disk flush on every commit, so some systems deliberately relax one of these properties, such as weaker isolation, in exchange for more concurrency. Production Rule: I pick isolation level and durability settings deliberately per workload rather than accepting engine defaults blindly, since a workload tolerant of slightly stale reads can often gain real throughput by relaxing isolation, while a financial ledger cannot.
Compare the four SQL Transaction Isolation Levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
Concept: Each isolation level permits progressively fewer kinds of interference between concurrent transactions, trading some concurrency for stronger guarantees. Engine Mechanics: Read Uncommitted lets a transaction see another transaction's uncommitted changes directly; Read Committed only lets a transaction see changes that have already been committed, but a value it reads twice within the same transaction can still change between reads if another transaction commits in between; Repeatable Read guarantees that re-reading the same row within a transaction always returns the same value, typically by taking a consistent snapshot at the transaction's start; Serializable adds protection even against anomalies from rows that did not exist yet, enforcing that the full set of concurrent transactions behaves as if they had run one at a time in some order. Trade-off: Each stronger level closes off a specific category of concurrency anomaly, but does so by requiring more coordination, extra locking, abort-and-retry logic, or validation, among concurrent transactions, which can reduce overall throughput. Production Rule: I default to Read Committed, the common engine default, unless a specific workload has a documented need for repeatable reads or full serializability, and I design any logic needing stronger guarantees to handle the possibility of a transaction abort and retry under Serializable.
Define the concurrency anomalies: Dirty Read, Non-Repeatable Read, Phantom Read, and Write Skew.
Concept: Each anomaly names a specific way concurrent transactions can produce an incorrect or surprising result if isolation is too weak. Engine Mechanics: A dirty read happens when a transaction reads another transaction's uncommitted change, which can vanish if that other transaction later rolls back; a non-repeatable read happens when a transaction reads the same row twice and gets two different values because another transaction committed a change in between; a phantom read happens when a transaction re-runs the same range query twice and sees a different set of rows, because another transaction inserted or deleted a row matching that range in between; write skew happens when two transactions each read overlapping data, each make a decision based on what they read, and each write a disjoint piece of data based on that decision, such that the combined result violates an invariant neither transaction's own write actually appeared to violate alone. Trade-off: Preventing more of these anomalies requires a stronger isolation level, which in turn reduces how much concurrent work the engine can safely interleave. Production Rule: I treat write skew as the hardest of these to catch in testing, since it requires no obviously conflicting write, only an invariant that spans rows read by one transaction and written by another, so I specifically design multi-row invariants with explicit locking or serializable isolation rather than assuming Repeatable Read alone is enough.
How does MVCC (Multi-Version Concurrency Control) allow readers not to block writers and writers not to block readers?
Concept: MVCC keeps multiple versions of a row simultaneously, so a reader can see a consistent older version while a writer is concurrently creating a newer one, without either blocking the other. Engine Mechanics: When a transaction updates a row, the engine does not overwrite the existing version in place; it creates a new version tagged with the writing transaction's identifier, while the old version remains visible to any transaction whose snapshot predates the write; each transaction is given a notion of which versions are visible to it based on when it started, so a long-running read transaction continues to see a stable, consistent snapshot even as writers keep producing newer versions underneath it. Trade-off: This avoids the throughput cost of readers and writers blocking each other, but it means multiple versions of the same row can coexist for a while, consuming extra storage until old versions are no longer needed by any active transaction and can be cleaned up. Production Rule: I watch for long-running transactions specifically because they hold open an old snapshot, preventing the engine from reclaiming old row versions that a shorter transaction would have let go of much sooner, a common cause of bloat.
How do PostgreSQL and MySQL (InnoDB) implement MVCC differently (tuple versioning in heap vs. undo logs)?
Concept: Both use MVCC, but they store old row versions in structurally different places. Engine Mechanics: PostgreSQL stores every row version directly in the table's heap itself, with each version carrying the transaction IDs that created and, if applicable, deleted it, so old versions physically accumulate in the main table until vacuum reclaims them; InnoDB instead keeps only the current version of a row in its main clustered structure and reconstructs an older version on demand by applying undo log records backwards from the current version, with those undo logs living in a separate rollback segment. Trade-off: Postgres's approach keeps reconstruction of an old version simple, it is already sitting there, but at the cost of needing vacuum to clean up dead versions from the main table; InnoDB keeps the main table leaner since it holds only current versions, but reconstructing an old snapshot costs CPU work to replay undo records, and a very long-running transaction can cause its undo log segment to grow large. Production Rule: On InnoDB specifically, I watch for history list length growing under a long-running transaction, since that directly signals undo log buildup that will need to be replayed for any snapshot read still referencing that old point in time.
Compare Optimistic Locking (version numbers/timestamps) vs. Pessimistic Locking (SELECT ... FOR UPDATE).
Concept: Pessimistic locking locks rows upfront, assuming conflicts will happen, while optimistic locking lets transactions proceed without locks and checks at commit time whether another transaction has modified the data. Engine Mechanics: Pessimistic locking uses SELECT ... FOR UPDATE to acquire exclusive row locks directly in the engine, forcing any other transaction trying to touch those same rows to wait in a lock queue until the first transaction finishes; optimistic locking instead adds a version column and performs the update as UPDATE ... SET val = X, version = version + 1 WHERE id = 1 AND version = old_version, checking in application code whether zero rows were affected, which would mean another transaction already changed that row first, and retrying or failing accordingly. Trade-off: Pessimistic locking avoids wasted work from conflicting transactions colliding, but it reduces throughput under high contention and introduces real deadlock risk; optimistic locking maximizes concurrency when conflicts are genuinely rare, but it wastes CPU on retries if contention spikes higher than expected. Production Rule: I use optimistic locking for low-conflict operations like ordinary profile or cart edits, and pessimistic locking, or an atomic conditional update like UPDATE ... WHERE stock >= qty, for high-contention cases like flash-sale inventory or wallet balances where conflicts are effectively guaranteed.
What is SELECT ... FOR UPDATE SKIP LOCKED, and why is it ideal for building database-backed job queues?
Concept: SKIP LOCKED tells the engine, when selecting rows for update, to simply skip over any row that is already locked by another transaction rather than waiting for it. Engine Mechanics: Without SKIP LOCKED, several worker processes concurrently trying to claim the next available job row with SELECT ... FOR UPDATE would queue up waiting on each other's locks, serializing what should be independent work; with SKIP LOCKED, each worker instead immediately moves past any row another worker already has locked and claims the next free, unlocked row that matches, letting multiple workers claim distinct jobs in parallel with no blocking between them. Trade-off: This eliminates lock contention between workers entirely, but it means the engine is deliberately ignoring some rows that match the query's criteria simply because they happen to be locked at that instant, which is exactly the right behavior for a job queue but would be wrong for a query that needs to see every matching row. Production Rule: I use FOR UPDATE SKIP LOCKED specifically for a job-queue claim pattern, pairing it with a small batch size per claim and a reasonable job visibility timeout so a crashed worker's claimed-but-unfinished job eventually becomes claimable again.
Compare Shared Locks (S), Exclusive Locks (X), Intention Locks, and Gap / Next-Key Locks (InnoDB).
Concept: These are different granularities and purposes of locking that InnoDB uses to balance correctness against concurrency. Engine Mechanics: A shared lock allows multiple concurrent readers but blocks any writer; an exclusive lock allows only the one transaction holding it to read or write that resource, blocking everyone else; an intention lock is held at the table level to signal that a transaction intends to take row-level locks inside it, letting the engine quickly check for table-versus-row lock conflicts without having to inspect every individual row lock; a gap lock locks the space between index records, not any actual row, specifically to prevent another transaction from inserting a new row into that gap, and a next-key lock combines a row lock with the gap immediately before it, which together is how InnoDB prevents phantom reads under Repeatable Read without needing full table-level locking. Trade-off: Gap and next-key locks close the phantom-read gap that plain row locking would leave open, but they can block inserts into a range that no existing row actually occupies, sometimes surprising a developer who expected only the specific rows a query touched to be locked. Production Rule: Under InnoDB's Repeatable Read, I expect range-based inserts near a locked range to potentially block on a gap lock, and I design high-concurrency insert patterns, such as sequential ID generation, with that in mind rather than assuming only literal row collisions can cause contention.
How do Database Deadlocks happen between two transactions, and how does the DB engine detect and resolve them?
Concept: A deadlock happens when two transactions each hold a lock the other one needs, so neither can proceed and both would wait forever without intervention. Engine Mechanics: The classic case is transaction A locking row 1 then waiting on row 2, while transaction B has already locked row 2 and is waiting on row 1; the engine maintains a wait-for graph tracking which transactions are waiting on which others, and periodically checks it for cycles, and upon detecting one, picks a victim, typically the transaction that has done the least work or holds the fewest locks, and forcibly rolls it back, releasing its locks so the other transaction can proceed. Trade-off: Automatic deadlock detection and rollback keeps the system from hanging forever, but the chosen victim transaction fails and must be retried by the application, so application code touching multiple rows across transactions must be prepared to handle a deadlock error and retry rather than treating it as a hard failure. Production Rule: I reduce deadlock frequency by having all transactions that touch the same set of rows acquire their locks in a consistent order, and I always wrap multi-row transactional code in retry logic that specifically catches a deadlock error and retries the whole transaction.
What is Snapshot Isolation, and why is it still vulnerable to Write Skew?
Concept: Snapshot isolation gives each transaction a consistent view of the database as it existed at the moment the transaction started, so it never sees any other transaction's changes made after that point. Engine Mechanics: The engine implements this by letting each transaction read only row versions that were already committed before its own start time, via MVCC, which is enough to prevent dirty reads, non-repeatable reads, and phantom reads entirely, but each transaction independently checking and writing based on its own unchanging snapshot means two transactions can each read overlapping data, see no conflict because the other's write has not happened in their snapshot yet, and both commit writes that together violate an invariant that spans both their reads. Trade-off: Snapshot isolation gives strong, useful guarantees at a much lower coordination cost than full serializability, but it is specifically this write-skew gap that means it is not equivalent to true serializable execution, despite often being mistaken for it. Production Rule: For any invariant that spans multiple rows read by one transaction and written by another, such as "the sum of two accounts must stay non-negative," I use explicit locking or genuinely serializable isolation rather than relying on snapshot isolation alone.
How do Connection Pools (like PgBouncer or HikariCP) work, and what is the difference between Session, Transaction, and Statement pooling?
Concept: A connection pool maintains a small set of already-open database connections and hands them out to application requests on demand, rather than opening and closing a new physical connection for every request. Engine Mechanics: In session pooling, a client keeps the same physical connection for its entire session, giving it the fewest restrictions but the least sharing; in transaction pooling, the pool only assigns a physical connection to a client for the duration of one transaction, returning it to the pool immediately after commit or rollback so many more logical clients can share a smaller number of physical connections, at the cost of certain session-level features, like session-scoped temp tables or prepared statements, no longer working reliably across transactions; statement pooling returns the connection after every single statement, maximizing sharing but imposing the tightest restrictions, incompatible with multi-statement transactions. Trade-off: Tighter pooling modes let far more logical clients share far fewer physical connections, which is essential at scale, but they progressively restrict which session-level or multi-statement features an application can rely on. Production Rule: I use transaction pooling as the default for most web application workloads, specifically because it balances connection efficiency against compatibility, and I explicitly avoid session-level features incompatible with it, such as advisory locks held across statements, when running behind a transaction-pooling proxy.
Why does opening 10,000 direct database connections degrade throughput instead of increasing it?
Concept: Each connection carries real fixed costs, memory, and scheduling overhead inside the database engine, so beyond the point where the engine's actual CPU cores can be kept busy, adding more connections adds contention rather than more useful parallel work. Engine Mechanics: Every open connection typically reserves its own backend process or thread along with per-connection memory structures inside the engine, and with a number of connections far exceeding the number of CPU cores, those backends spend an increasing share of time being scheduled in and out of the CPU and contending for shared internal locks, such as on the buffer pool, rather than doing useful query work. Trade-off: More connections can increase a specific client's apparent concurrency, but past the point of saturating the machine's actual cores, every additional connection subtracts from total throughput by adding scheduling and lock-contention overhead rather than genuine parallelism. Production Rule: I size the connection pool to roughly the database server's available CPU cores, times a small multiplier for typical I/O wait, rather than to the number of application instances or threads that want a connection, and let a pooling proxy, not every application process, hold the larger number of logical client requests.
Level 8: Recovery and durability
Recovery and durability — interview questions
Answer in your head first, then open it.
What is Write-Ahead Logging (WAL), and how does it guarantee Atomicity and Durability on a crash?
Concept: Write-ahead logging requires that a record describing a change be written to a durable log before the change itself is applied to the actual data pages, so the log always has enough information to redo or undo any in-flight change after a crash. Engine Mechanics: Before a transaction's change is applied to its data page in the buffer pool, the engine first writes a log record describing that change to the WAL and flushes it to durable storage; on commit, the engine only needs to guarantee the WAL records for that transaction are durably flushed, not that the data pages themselves are yet written back, since the data pages can always be reconstructed later from the log; after a crash, the engine replays the WAL from the last checkpoint forward, redoing committed transactions' changes and undoing any changes from transactions that never committed. Trade-off: WAL turns durability into a sequential log append, which is far cheaper than flushing scattered data pages on every commit, but it means recovery after a crash requires replaying log records, and the log itself must be retained at least back to the oldest still-needed checkpoint. Production Rule: I monitor WAL volume and checkpoint frequency together, since infrequent checkpoints shorten steady-state overhead but lengthen crash recovery time by leaving more log to replay, a direct dial between the two.
Explain the ARIES Recovery Algorithm (Analysis, Redo, Undo phases) and Checkpointing.
Concept: ARIES is the classic three-phase recovery algorithm that restores a database to a correct state after a crash using the write-ahead log, and checkpointing is the periodic bookkeeping that bounds how far back recovery needs to look. Engine Mechanics: The analysis phase scans the log forward from the last checkpoint to determine which transactions were in progress at crash time and which data pages were dirty; the redo phase then replays every logged change from that point forward, even for transactions that eventually aborted, to bring the data pages back to exactly the state they were in right before the crash; the undo phase then rolls back any changes belonging to transactions that had not committed, using the log's undo information, restoring the database to a state containing only committed work; a checkpoint periodically records which transactions and dirty pages existed at that moment, so analysis never needs to scan further back than the most recent checkpoint. Trade-off: Redoing even uncommitted transactions' changes before undoing them seems wasteful but actually simplifies the algorithm by letting redo be a single uniform pass regardless of each transaction's eventual outcome; more frequent checkpoints shorten this recovery scan but add steady-state overhead from the checkpoint operation itself. Production Rule: I treat checkpoint interval as a direct trade between acceptable crash-recovery time and steady-state checkpoint overhead, tuning it tighter for systems where fast recovery after an outage matters more than squeezing out marginal steady-state throughput.
Level 9: Replication
Replication — interview questions
Answer in your head first, then open it.
Compare Single-Leader (Master-Slave), Multi-Leader, and Leaderless (Dynamo-style) replication.
Concept: These describe who is allowed to accept writes in a replicated system. Engine Mechanics: Single-leader replication designates exactly one node to accept all writes, which it then streams to every follower, making write conflicts impossible since there is only ever one writer; multi-leader replication allows writes at more than one node, typically in different datacenters, with each leader asynchronously propagating its writes to the others, which means two leaders can accept conflicting writes to the same data and the system must reconcile them after the fact; leaderless, Dynamo-style replication accepts writes at any replica and relies on the client or a coordinator to write to several replicas directly and read from several replicas, resolving inconsistency through techniques like read-repair and quorums rather than ever designating a single authority. Trade-off: Single-leader avoids write conflicts entirely but makes the leader a single point of contention and failure for writes; multi-leader and leaderless designs allow writes to continue even if one region or node is unreachable, at the direct cost of having to detect and resolve conflicting writes after they happen. Production Rule: I choose single-leader by default for workloads where conflict avoidance matters more than write availability during a partition, and reach for multi-leader or leaderless designs specifically when writes must keep succeeding even when an entire region goes dark, accepting the conflict-resolution complexity that comes with it.
Compare Synchronous, Asynchronous, and Semi-Synchronous replication (RPO, RTO, and write latency trade-offs).
Concept: These describe when a leader considers a write durable enough to acknowledge to the client, relative to whether followers have actually received it yet. Engine Mechanics: Synchronous replication waits for at least one follower to confirm it has durably received the write before the leader acknowledges it to the client, guaranteeing zero data loss if the leader then fails, at the cost of adding that follower's round-trip latency to every write; asynchronous replication acknowledges the write immediately after the leader itself persists it, streaming it to followers in the background, which keeps write latency low but risks losing any writes that had not yet reached a follower if the leader crashes; semi-synchronous replication waits for confirmation from some but not all followers, balancing the two. Trade-off: This directly trades recovery point objective, how much data could be lost in a failure, against write latency, with fully synchronous replication minimizing RPO at the cost of latency and full async minimizing latency at the cost of RPO. Production Rule: I pick synchronous replication specifically for data where any loss is unacceptable, such as financial transactions, and asynchronous for workloads where the latency cost of waiting on every follower would be unacceptable and brief, bounded data loss on a rare leader failure is tolerable.
What is Replication Lag, and how do you solve Read-Your-Own-Writes and Monotonic Reads anomalies?
Concept: Replication lag is the delay between a write landing on the leader and that same write becoming visible on a follower; read-your-own-writes and monotonic reads name specific user-visible problems that lag can cause when reads are routed to followers. Engine Mechanics: A follower applies the leader's replication stream with some delay, so a read directed at a follower immediately after a write to the leader can return stale data that does not yet reflect that write, breaking read-your-own-writes; if a user's successive reads happen to land on different followers with different amounts of lag, they can even see data appear to go backwards in time, a monotonic-reads violation. Trade-off: Routing all reads to the leader avoids both anomalies entirely but defeats the purpose of having read replicas for load distribution in the first place; solving them while still using followers requires extra bookkeeping, such as tracking which replication position a user's own writes landed at and routing their subsequent reads only to a follower that has caught up at least that far. Production Rule: For read-your-own-writes, I route a user's reads to the leader for a short window right after they write, or pin them to a follower known to have caught up past their write's position; for monotonic reads, I pin a given user's session consistently to the same follower rather than load-balancing their reads across different followers with potentially different lag.
Level 10: Partitioning and distributed transactions
Partitioning and distributed transactions — interview questions
Answer in your head first, then open it.
What is 2PC (Two-Phase Commit) in distributed transactions, and why is it a blocking protocol?
Concept: Two-phase commit is a protocol for getting multiple independent systems to agree on committing or aborting a single transaction together, as an all-or-nothing unit, across machines that cannot share one local transaction. Engine Mechanics: In the prepare phase, a coordinator asks every participant to get ready to commit and durably record that it can do so without yet actually committing; only once every participant confirms it is prepared does the coordinator move to the commit phase and tell every participant to actually commit; if any participant cannot prepare, the coordinator tells everyone to abort instead. Trade-off: This guarantees all participants reach the same outcome, but if the coordinator crashes after participants have prepared but before it sends the final commit or abort decision, every participant is stuck holding its locks, unable to safely decide on its own whether to commit or abort, which is exactly why 2PC is called a blocking protocol. Production Rule: I avoid 2PC across services wherever possible given that blocking risk, and when distributed atomicity is genuinely required, I favor a coordinator design with its own durable, replicated log rather than a single point of failure that can leave every participant stuck.
How does the Saga Pattern (Choreography vs. Orchestration) replace 2PC across microservices?
Concept: A saga breaks a distributed transaction into a sequence of local transactions, each with its own compensating action that can undo it, rather than trying to commit everything atomically together like 2PC. Engine Mechanics: In choreography, each service completes its own local transaction and publishes an event, and other services react to that event by running their own next step, with no central coordinator; in orchestration, a central orchestrator explicitly calls each service's step in order and, if a later step fails, calls the compensating action for each already-completed earlier step to undo it. Trade-off: Sagas avoid 2PC's blocking risk and let each service commit independently and quickly, but they give up true atomicity, there is a real window where some steps have committed and others have not, and every step needs a correctly designed compensating action, which is often harder to get right than the original action itself. Production Rule: I design every saga step's compensating action at the same time as the step itself, not as an afterthought, and I make each step idempotent, since a saga step or its compensation may need to be retried after a partial failure.
Explain Horizontal Sharding vs. Vertical Scaling vs. Functional Partitioning.
Concept: These are three different strategies for scaling a database beyond what one machine or one simple design can handle. Engine Mechanics: Vertical scaling simply moves the same single database to a more powerful machine, more CPU, RAM, and faster disks, with no change to the data's structure or distribution; horizontal sharding splits the same table's rows across multiple separate database instances, typically by a shard key, so each machine holds only a subset of the overall data; functional partitioning instead splits different tables or services onto different databases based on what they are used for, such as putting the orders table on one database and the analytics tables on another, without necessarily splitting any single table's rows. Trade-off: Vertical scaling is the simplest, requiring no application changes, but it eventually hits a hard ceiling on what a single machine can provide; horizontal sharding removes that ceiling but introduces genuinely hard problems like cross-shard joins and rebalancing; functional partitioning is often the easiest first step, separating concerns cleanly, but it does not by itself solve the problem of one specific table outgrowing one machine. Production Rule: I exhaust vertical scaling and functional partitioning first, since both are comparatively simple, and reach for horizontal sharding of a specific table only once that table's own size or throughput, not the system as a whole, has become the genuine bottleneck.
Compare sharding strategies: Range-based, Hash-based, Directory-based, and Consistent Hashing.
Concept: Each strategy answers the same question, which shard does a given key belong on, with different trade-offs between even distribution, range-query support, and rebalancing cost. Engine Mechanics: Range-based sharding assigns contiguous ranges of a key to each shard, which supports efficient range queries but risks uneven load if writes concentrate in one range, such as an ever-increasing timestamp always hitting the newest shard; hash-based sharding distributes keys evenly by hashing them, avoiding that hotspot risk but destroying any locality, so a range query must now hit every shard; directory-based sharding keeps an explicit lookup table mapping each key, or each range of keys, to its shard, which is flexible and supports arbitrary rebalancing but adds a lookup step, and that directory itself becomes a critical shared dependency; consistent hashing arranges shards and keys together on a conceptual ring so that adding or removing one shard only requires reassigning the small slice of keys adjacent to it on the ring, rather than reshuffling everything. Trade-off: The strategies that distribute load most evenly, hash-based and consistent hashing, give up the range-query locality that range-based sharding naturally provides, while directory-based sharding buys maximum flexibility at the cost of an extra indirection and a new critical dependency. Production Rule: I use consistent hashing specifically when the shard count is expected to change over the system's life, since it minimizes the data movement that rebalancing requires compared to a naive hash-mod-N scheme, where changing N relocates almost every key.
What is a Hotspot (Celebrity Key) problem in sharding, and how do you mitigate it (key salting)?
Concept: A hotspot happens when one specific key, or a narrow range of keys, receives disproportionately more traffic than others, overloading whichever single shard that key happens to live on no matter how evenly the overall keyspace is distributed. Engine Mechanics: Because a sharding scheme places any one key's data on exactly one shard, an unusually popular key, a celebrity account, a viral post, concentrates all of its traffic onto that one shard's resources, while every other shard sits comparatively idle; key salting addresses this by appending a random or round-robin suffix to the hot key, spreading what used to be one logical key's writes across several physical sub-keys on different shards, and merging results back together at read time. Trade-off: Salting spreads a hot key's write load evenly, but it complicates every read that needs that key's data, since the read must now know to fan out across all the salted sub-keys and combine their results rather than doing one simple lookup. Production Rule: I apply salting selectively, only to keys actually identified as hot through real traffic monitoring, rather than salting every key by default, since the added read-side complexity is only worth paying where a genuine hotspot exists.
What are the biggest pain points of sharded SQL databases (cross-shard joins, distributed transactions, resharding)?
Concept: Sharding solves the single-machine capacity problem but reintroduces, at the application level, several things a single-machine relational database used to handle transparently. Engine Mechanics: A join between two tables that happen to be sharded differently, or a join across rows that land on different shards even within the same table, can no longer be executed as a single local join; the database must either fetch data from multiple shards and join it in application code, or avoid the join by denormalizing data onto a single shard; a transaction touching rows on more than one shard loses the simple, single-machine atomicity guarantee and needs something like 2PC or a saga instead; and resharding, changing the number of shards or the sharding key later, requires physically moving large amounts of data between machines while the system keeps running. Trade-off: Each of these problems has a workable solution, but every one of them is work the application or platform now has to do explicitly that a single unsharded database handled for free, which is exactly why sharding is usually deferred until it is genuinely necessary. Production Rule: Before sharding a table, I specifically choose a shard key that keeps the most common queries and transactions confined to a single shard, since a shard key chosen purely for even distribution, ignoring query patterns, is what turns cross-shard joins and transactions from a rare exception into the common case.
Explain the CAP Theorem and the PACELC Theorem with real database examples (Postgres, Cassandra, DynamoDB, Spanner).
Concept: The CAP theorem states that during a network partition, a distributed system must choose between consistency, every node seeing the same data, and availability, every node still responding to requests; PACELC extends this by also addressing the trade-off that exists even when there is no partition at all, between latency and consistency. Engine Mechanics: A single-node Postgres instance is not meaningfully distributed in the CAP sense, but a Postgres cluster configured for synchronous replication behaves as consistency-favoring under a partition, refusing to serve a write it cannot confirm is durably replicated; Cassandra and DynamoDB are tunable, leaderless systems that by default favor availability during a partition, accepting a write even if it has not yet reached every replica, and resolving divergence afterward; Spanner is explicitly built to provide strong, externally consistent behavior even across a globally distributed deployment, accepting the added latency its synchronized clock infrastructure and consensus protocol require in order to do so. Trade-off: A system cannot have perfect consistency, perfect availability, and partition tolerance all at once, and even without a partition, pushing for stronger consistency generally costs more latency, since more nodes must coordinate before a read or write can be confirmed. Production Rule: I choose a system's consistency posture based on what an actual partition should do to the specific workload, favoring availability for something like a shopping cart where a brief inconsistency is recoverable, and favoring consistency for something like a unique username reservation where it is not.
What are Quorum Reads and Writes (R + W > N) in leaderless databases like Cassandra/DynamoDB?
Concept: In a leaderless system with N replicas for a piece of data, a quorum requires a write to be acknowledged by W replicas and a read to be confirmed by R replicas, and choosing R and W so that R + W is greater than N guarantees that any read quorum and any write quorum must share at least one replica in common. Engine Mechanics: Because every possible set of W replicas and every possible set of R replicas must overlap by at least one node when R + W > N, that overlapping replica is guaranteed to have seen the most recent write, so a quorum read is guaranteed to observe the latest acknowledged write, even though no single replica individually holds the full picture of every write. Trade-off: Larger R and W values give stronger consistency guarantees but require more replicas to respond before a read or write can complete, directly increasing latency and reducing availability if too many replicas are unreachable; smaller values favor speed and availability but weaken the consistency guarantee. Production Rule: I tune R and W per workload, often using a strict quorum, R = W = (N/2)+1, for data needing strong consistency, and smaller values favoring latency for data where eventual consistency is an acceptable trade.
How do Vector Clocks and Last-Write-Wins (LWW) resolve write conflicts in multi-leader/leaderless databases?
Concept: Both are ways to decide what happens when two replicas have each accepted a conflicting write to the same key, but they make very different trade-offs about which write wins. Engine Mechanics: Last-write-wins simply attaches a timestamp to every write and, when two conflicting versions are compared, keeps whichever has the later timestamp, discarding the other entirely; a vector clock instead tracks, for each replica, how many writes it has seen from every other replica, which lets the system determine whether one write causally happened before another, or whether the two writes were truly concurrent and neither causally depends on the other, in which case the conflict must be surfaced rather than silently resolved by timestamp alone. Trade-off: Last-write-wins is simple and requires no extra bookkeeping, but it can silently and permanently discard a write that a user would have considered just as valid as the one that "won," purely because of clock skew or timing; vector clocks correctly detect true concurrency without needing synchronized clocks, but they require real bookkeeping overhead and still leave the application to decide how to merge or present a genuinely concurrent conflict. Production Rule: I use last-write-wins only where silently dropping a losing write is genuinely acceptable, such as a simple cache or a "last viewed" timestamp, and use vector clocks, or an application-level merge function, whenever a dropped write would mean real, unacceptable data loss.
How do distributed SQL databases (NewSQL like CockroachDB, TiDB, Google Spanner) achieve global serializability?
Concept: NewSQL systems aim to provide the familiar guarantees of a traditional single-node relational database, strict transactional isolation up to full serializability, while the data itself is actually partitioned and replicated across many machines, often across regions. Engine Mechanics: These systems typically combine a consensus protocol, such as Raft, to keep each shard's replicas in agreement on the order of writes, with a globally meaningful way of ordering transactions across shards, Spanner's approach uses tightly synchronized clocks with bounded uncertainty to assign globally comparable commit timestamps, while others use a distributed transaction protocol layered on top of per-shard consensus, so that a transaction spanning multiple shards can still be assigned a single, globally consistent position in the overall transaction order. Trade-off: Achieving this strong, global guarantee requires real coordination overhead on any transaction that spans more than one shard, directly increasing that transaction's latency compared to a transaction confined to a single shard, which is exactly the latency cost PACELC predicts for choosing strong consistency. Production Rule: I design a NewSQL schema's sharding key to keep the overwhelming majority of transactions confined to a single shard wherever possible, since cross-shard transactions, while supported, still pay a real coordination latency cost that single-shard transactions avoid.
What is Change Data Capture (CDC) (e.g., Debezium reading WAL/binlog), and how does it power event-driven architectures?
Concept: Change data capture reads a database's own internal replication stream, the same write-ahead log or binlog used for replication, and turns every row-level insert, update, and delete into a stream of events other systems can consume. Engine Mechanics: A CDC tool like Debezium connects to the database as if it were a replica, parsing the WAL or binlog stream directly rather than querying the tables, and converts each logged change into a structured event published to a message broker, capturing changes with essentially the same low latency and completeness as true replication, since it is reading the exact same source of truth a replica would. Trade-off: Reading directly from the replication log captures every change reliably and with minimal impact on the source database's query load, but it ties the CDC pipeline closely to that specific database engine's log format, and a schema change on the source table requires the downstream consumers of the event stream to handle the new shape. Production Rule: I prefer log-based CDC over having application code dual-write to both the database and a message queue, specifically because log-based capture cannot miss a write that actually committed, while a dual-write can silently drop an event if the second write fails after the first succeeds.
Explain the Transactional Outbox Pattern and how it guarantees reliable event publishing alongside DB writes.
Concept: The transactional outbox pattern guarantees that a database write and the event announcing that write are never split apart, by writing the event itself into an ordinary table inside the very same local transaction as the business write. Engine Mechanics: Instead of writing to the database and then separately publishing to a message broker, two operations that can fail independently of each other, the application writes both its business row and a corresponding event row into an "outbox" table in one atomic local transaction, which the database's own atomicity already guarantees happen together or not at all; a separate background process then reads unpublished rows from the outbox table and actually publishes them to the message broker, marking them published once that succeeds, retrying if it does not. Trade-off: This fully closes the gap where a direct dual-write could succeed in the database but fail to reach the broker, but it adds the overhead and latency of a separate polling or log-reading process to actually move events out of the outbox table and onto the broker. Production Rule: I use the transactional outbox pattern, often paired with CDC reading the outbox table itself for the actual publish step, for any event that must reliably correspond one-to-one with a committed database write, rather than trusting a direct dual-write.
How do you use Read Replicas effectively without breaking transactional consistency?
Concept: A read replica offloads read traffic from the primary database, but because it lags behind the primary by some amount of replication delay, using it effectively means being deliberate about which reads can tolerate that staleness and which cannot. Engine Mechanics: Reads that are purely informational and tolerant of being slightly behind, a dashboard, an analytics report, a search index, can be routed to a replica with no special handling; reads that are part of the same logical flow as a recent write, checking a value the user just updated, need either to be routed to the primary, or the application needs to track how far behind a given replica is and avoid routing that specific read to a replica that has not yet caught up past the write in question. Trade-off: Routing more reads to replicas reduces load on the primary and improves overall read throughput, but doing so indiscriminately risks exactly the read-your-own-writes and monotonic-read anomalies that replication lag can introduce. Production Rule: I classify read paths explicitly as replica-safe or primary-required at design time, rather than deciding ad hoc per query, so a developer adding a new read later has a clear rule to follow instead of guessing whether staleness is acceptable.
Level 11: NoSQL and alternative data models
NoSQL and alternative data models — interview questions
Answer in your head first, then open it.
When should you choose a Relational DB (Postgres) vs. Document DB (MongoDB) vs. Key-Value Store (Redis) vs. Wide-Column DB (Cassandra)?
Concept: Each of these four families is built around a different access pattern, and the right choice follows from how the application actually needs to read and write its data, not from which is newest or most popular. Engine Mechanics: A relational database enforces a fixed schema and strong multi-row transactional guarantees, which suits data with many genuine relationships queried in flexible, ad hoc ways; a document database stores flexible, often nested JSON-like documents, suiting data that is naturally self-contained per entity and read or written as a whole unit; a key-value store, like Redis, gives extremely fast lookup by a single exact key with little structure, suiting caching, session state, or counters; a wide-column store, like Cassandra, is built for very high write throughput across a cluster with data modeled around specific known query patterns, trading flexible ad hoc querying for horizontal write scale. Trade-off: Picking a more specialized store than a relational database buys real performance or scale for its specific access pattern, but it generally gives up some combination of flexible querying, strong transactional guarantees, or both, outside that pattern. Production Rule: I pick the data store by writing down the application's actual dominant queries first, then matching the store whose native access pattern fits those queries, rather than defaulting to whichever store is already running elsewhere in the stack.
How does Redis persist data (RDB snapshots vs. AOF append-only file), and why is single-threaded Redis so fast?
Concept: Redis offers two different approaches to surviving a restart without losing its in-memory data, and its speed comes from a deliberately single-threaded execution model for command processing. Engine Mechanics: RDB persistence periodically writes a full point-in-time snapshot of the entire dataset to disk, which is compact and fast to reload on restart but loses any writes made since the last snapshot; AOF persistence instead logs every write command as it happens, appending to a file, which can be replayed on restart to reconstruct the exact dataset with much less potential data loss, at the cost of a larger log and slower restart as it replays; Redis processes commands on a single thread specifically to avoid all locking overhead for in-memory data access, relying on its data structures all living in RAM, with no disk-seek latency, to make that single thread extremely fast per command. Trade-off: RDB's smaller files and faster restarts trade away durability since the last snapshot, while AOF's stronger durability trades away restart speed and disk space; Redis's single-threaded model avoids lock contention entirely but means one slow command can briefly block every other client, so command cost must be kept predictable. Production Rule: I combine RDB and AOF where durability matters, using RDB for fast full backups and AOF for minimizing data loss between them, and I specifically avoid Redis commands with unbounded cost, like scanning a huge collection, that would stall the single processing thread for other clients.
Explain caching patterns: Cache-Aside (Lazy Loading), Write-Through, Write-Behind (Write-Back), and Read-Through.
Concept: These four patterns differ in exactly when data moves between the cache and the underlying database, on read, on write, or in the background. Engine Mechanics: Cache-aside has the application check the cache first on a read, and on a miss, load from the database and populate the cache itself, with the cache staying entirely passive; read-through is similar but the cache itself, not the application, is responsible for loading from the database on a miss; write-through writes synchronously to both the cache and the database together on every write, keeping them always in sync at the cost of paying both write costs on every operation; write-behind, or write-back, writes to the cache immediately and acknowledges the client right away, asynchronously flushing to the database afterward, which is fast but risks losing that write if the cache fails before the flush happens. Trade-off: Patterns that keep cache and database writes synchronous, write-through, are safer against loss but slower per write; write-behind is fast but accepts a real durability gap; cache-aside is simple and puts the application fully in control but leaves a window where a cache miss pays the full database cost. Production Rule: I use cache-aside as the default for most read-heavy workloads for its simplicity, and reserve write-behind specifically for very high write-throughput paths where the brief durability risk has been explicitly accepted, never as a silent default.
How do you prevent Cache Stampede (Thundering Herd), Cache Penetration, and Cache Avalanche?
Concept: These name three distinct ways a cache layer can fail to protect the database behind it under the wrong conditions. Engine Mechanics: A cache stampede happens when one popular key expires and a flood of concurrent requests all miss at once and all hammer the database simultaneously to recompute the same value; cache penetration happens when requests repeatedly query a key that genuinely does not exist in either the cache or the database, so every request bypasses the cache entirely and hits the database every single time; a cache avalanche happens when a large number of different keys all expire at approximately the same time, causing a broad simultaneous wave of database load rather than a load concentrated on just one key. Trade-off: Each requires a different specific mitigation: a stampede is addressed by having only one request recompute a missing value while others wait or get a stale value; penetration is addressed by caching the "not found" result itself for a short time so repeated misses do not reach the database; an avalanche is addressed by staggering expiration times with random jitter rather than one fixed TTL for everything. Production Rule: I jitter TTLs by default to avoid avalanche risk, use a lock or "only one recomputes" pattern for genuinely hot keys to avoid stampede, and explicitly cache negative results with a short TTL to close off penetration.
When is a Graph Database (Neo4j) significantly faster than recursive SQL joins?
Concept: A graph database stores relationships as first-class, directly traversable pointers between nodes, which becomes dramatically faster than a relational database specifically when a query needs to traverse many hops of relationship, rather than a fixed, small number of joins. Engine Mechanics: In a relational database, traversing N hops of a relationship means N successive joins, or a recursive CTE re-querying and re-joining at every level, with cost that tends to grow rapidly as hop count increases; a graph database instead stores each relationship as a direct, already-materialized pointer from one node to its neighbors, so traversing from a node to its neighbors, and their neighbors, and so on, is a sequence of cheap pointer-following steps whose cost grows with the actual size of the traversed subgraph rather than with how the relational joins happen to be planned. Trade-off: This traversal speed is specifically valuable for deep, variable-depth relationship queries, but a graph database generally gives up the mature, standardized aggregate and set-based query tooling a relational engine offers for simpler, shallow queries that do not need deep traversal at all. Production Rule: I reach for a graph database specifically when the dominant query pattern is "find everything connected to this node within some number of hops" and that hop count is often more than two or three, since that is exactly where relational joins start costing noticeably more than native graph traversal.
What makes Time-Series Databases (TimescaleDB, InfluxDB) specialized for high-ingestion metrics?
Concept: A time-series database is built around the specific assumption that almost every row is a new, append-only, timestamped measurement, and almost every query filters or aggregates over a time range, which lets it make storage and query trade-offs a general-purpose database would not. Engine Mechanics: Data is physically organized and compressed by time, often automatically partitioned into time-based chunks, so both ingestion, which is almost always a write at or near the current time, and range queries, which almost always filter on a time window, benefit directly from that physical locality, and older data can be transparently rolled up into coarser aggregates or more aggressively compressed once it is no longer being actively queried at full resolution. Trade-off: This specialization gives much higher sustained ingest rates and much cheaper range-and-aggregate queries over time than a general-purpose relational table would achieve for the same workload, but it assumes the access pattern genuinely is time-oriented; a time-series database offers little advantage, and sometimes real disadvantage, for data that is not dominated by append-only, time-ordered writes and time-range reads. Production Rule: I reach for a dedicated time-series database specifically once metric or event ingestion volume and retention-driven rollup requirements outgrow what a well-partitioned ordinary table can comfortably handle, not simply because the data happens to have a timestamp column.
How do Vector Databases (Pinecone, Qdrant, Milvus, pgvector) store embeddings and balance recall vs. latency?
Concept: A vector database stores high-dimensional numeric embeddings and is built specifically to answer approximate nearest-neighbor queries, finding the stored vectors most similar to a given query vector, quickly even at large scale. Engine Mechanics: Rather than exhaustively comparing a query vector against every stored vector, which scales poorly, these systems build an approximate index, such as a graph-based structure like HNSW or a clustering-based structure like IVF, that lets a query examine only a small, promising subset of all stored vectors while still returning results that are usually, though not provably always, the true nearest neighbors. Trade-off: Accepting approximate rather than exact results is what makes nearest-neighbor search over millions of vectors fast enough to be useful at all, but every one of these systems exposes tunable parameters that directly trade recall, how often the true nearest neighbors are actually found, against query latency and index memory footprint. Production Rule: I tune a vector index's recall-latency trade-off against a measured validation set representative of the real query distribution, rather than accepting default settings, since the right balance point is genuinely different depending on embedding dimensionality, dataset size, and how costly a missed true neighbor actually is for the application.
Why is PostgreSQL increasingly used as a "multi-model" database (relational + JSONB + full-text search + pgvector + queues)?
Concept: PostgreSQL has steadily absorbed capabilities that used to require separate specialized systems, letting a single relational database also handle semi-structured documents, full-text search, vector similarity, and simple queueing, all inside the same transactional engine. Engine Mechanics: JSONB gives it document-style flexible storage with indexable queries; built-in full-text search provides tokenization, ranking, and indexing for text search without a separate search engine; the pgvector extension adds vector columns and approximate nearest-neighbor indexes directly inside Postgres; and patterns like SELECT ... FOR UPDATE SKIP LOCKED on an ordinary table let it serve as a lightweight job queue, all of this within Postgres's existing transactional and consistency guarantees, rather than each capability living in its own separately consistent system. Trade-off: Consolidating onto one multi-model database means fewer moving systems to operate, deploy, and keep consistent with each other, since everything shares one transaction boundary, but each individual capability inside Postgres is typically less specialized and less performant at extreme scale than a dedicated system built around that one capability alone, such as a purpose-built vector database or search engine. Production Rule: I default to Postgres's built-in capability for a secondary need, like search or vector similarity, as long as the scale and latency requirements for that specific feature stay moderate, and only split it out to a dedicated specialized system once that one capability's own scale genuinely outgrows what Postgres can comfortably serve alongside everything else.
Level 12: Big data and analytics
Big data and analytics — interview questions
Answer in your head first, then open it.
Compare Star Schema and Snowflake Schema in data warehousing (OLAP).
Concept: A star schema joins one central fact table directly to a flat set of dimension tables with a single join each; a snowflake schema further splits those dimension tables into additional normalized levels. Engine Mechanics: In a star schema, an analytical query reaches every dimension's descriptive attributes with exactly one join from the fact table; in a snowflake schema, some dimension tables are themselves broken into further related tables, such as splitting a product dimension's category into its own table, so reaching that attribute now requires an additional join beyond the first. Trade-off: Snowflaking reduces redundant text stored in dimension tables, but it adds extra joins to nearly every analytical query touching that dimension, and on a warehouse's typically very large fact table, that repeated join cost usually outweighs the comparatively cheap storage saved by normalizing the dimension further. Production Rule: I default to a star schema for analytical workloads, since read speed on large fact-table scans matters far more than the storage saved by further normalizing dimension tables, and I only snowflake a specific dimension when its redundancy is unusually large and the added join cost has been explicitly measured and accepted.
What is OLTP vs. OLAP, and why do they use row-oriented vs. column-oriented storage?
Concept: OLTP, online transaction processing, serves many small, individual read and write operations on a few rows at a time; OLAP, online analytical processing, serves large aggregate queries scanning many rows but typically only a few columns at once, and each pattern favors a different physical storage layout. Engine Mechanics: Row-oriented storage keeps all of one row's columns physically together, which is efficient for OLTP's pattern of reading or writing one entire row at a time; column-oriented storage instead keeps all values for one column physically together across every row, which is efficient for OLAP's pattern of scanning one or two columns across millions of rows, since the engine never has to read past columns it does not need, and similar values stored contiguously also compress far better. Trade-off: Column-oriented storage makes a full-row read or write, exactly OLTP's dominant operation, comparatively expensive, since a single row's data is now scattered across many separate column files, while row-oriented storage makes a wide aggregate scan over many rows but few columns comparatively expensive, since the engine must read every column of every row even to use just one or two of them. Production Rule: I keep OLTP workloads on row-oriented storage and route analytical workloads to a column-oriented warehouse or columnar extension, rather than running both patterns against the same row-oriented operational database, which is exactly the contention problem a warehouse exists to avoid.
How do Columnar Databases (ClickHouse, Parquet, BigQuery) achieve 10x–100x compression and faster analytical aggregations?
Concept: Columnar databases achieve both their large compression ratios and their fast aggregate query speed from the same underlying decision: storing each column's values contiguously, separately from every other column. Engine Mechanics: Because a single column's stored values are usually far more similar to each other than values across different columns are, such as a status column with only a handful of distinct repeated values, storing them contiguously lets the engine apply much more effective compression, such as run-length or dictionary encoding, than would be possible on a row that interleaves many different, unrelated columns together; at query time, an aggregate query that only needs a couple of columns can read just those columns' files directly off disk, skipping every other column entirely, and the same compression that saved space also reduces the actual bytes that must be read from disk for the columns it does scan. Trade-off: This layout is extremely efficient for scanning few columns across many rows, exactly the analytical query pattern, but reconstructing one single complete row, exactly the OLTP query pattern, now requires reading from and reassembling many separate column files instead of one contiguous location. Production Rule: I route workloads dominated by wide aggregate scans over a handful of columns to a columnar store, and keep workloads dominated by whole-row reads and writes on a row-oriented store, matching each engine's physical layout to the query pattern it was actually built for.
Level 13: Practical performance, operations and interviews
Practical performance, operations and interviews — interview questions
Answer in your head first, then open it.
SQL Query: Find the 2nd highest salary per department (handle ties and departments with only 1 employee).
Concept: This requires ranking salaries within each department while explicitly deciding how ties and single-employee departments should behave, rather than assuming a single obvious answer. Engine Mechanics: Using DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) inside a CTE assigns rank 1 to the highest salary in each department, and, critically, assigns the same rank to any tied salaries, with the next distinct salary getting the next rank number with no gap, so filtering WHERE salary_rank = 2 correctly returns the second distinct salary level per department; a department with only one employee simply never produces a rank 2 row and is naturally excluded without any extra logic. Trade-off: Using DENSE_RANK() specifically means two employees tied for first place in a department both appear at rank 1, and the row at rank 2 is the next distinct salary value, not literally the third-ranked individual person, which is the right behavior only if ties are meant to share a rank rather than being broken arbitrarily. Production Rule: Before writing the query, I always clarify out loud whether ties should share a rank or be broken by some tiebreaker like employee ID, since that single clarification decides whether DENSE_RANK(), RANK(), or ROW_NUMBER() is the correct function to use.
SQL Query: Find consecutive active days (gaps and islands problem) for each user from a login events table.
Concept: This is the classic gaps-and-islands problem, finding maximal runs of consecutive dates per user, solved by assigning each login a group identifier that stays constant across a consecutive run and changes whenever a gap occurs. Engine Mechanics: Taking each login date, subtracting its own row number (ordered by date, partitioned by user) in days from the date itself produces an identical resulting value for every date inside one unbroken consecutive run, since both the date and the row number increase by exactly one together across a run, but that value shifts whenever a gap breaks the sequence; grouping by that computed value, together with user_id, then reveals each consecutive island, with its MIN and MAX date giving the run's start and end. Trade-off: This row-number-minus-date technique is elegant and avoids a procedural loop entirely, but it depends on dates being genuinely one full calendar day apart with no duplicates per user, so duplicate login rows on the same day need to be deduplicated first or the arithmetic breaks. Production Rule: I deduplicate to one row per user per day before applying this technique, and I always sanity-check the result against a small hand-traced example with a known gap before trusting it on the full dataset.
SQL Query: Calculate a 7-day rolling average of daily revenue, including days with zero orders.
Concept: A correct 7-day rolling average must first guarantee every calendar day in the range actually has a row, including days with genuinely zero revenue, before any averaging window is applied, since a day missing from the source table entirely is different from a day present with a zero value. Engine Mechanics: Generating a complete calendar spine, for example with generate_series over the full date range, and LEFT JOINing the actual orders aggregated by day onto that spine, with COALESCE turning any unmatched day's revenue into 0, guarantees a dense, gap-free daily series; a window function, AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), then computes the correct trailing 7-day average over that complete series. Trade-off: Generating the full calendar spine adds an extra step and a join compared to naively averaging only days that happen to have rows, but skipping it would silently stretch the rolling window across a wider actual time span than 7 days whenever a zero-order day was simply missing rather than present with a zero. Production Rule: I always materialize a complete date spine before computing any day-based rolling metric, specifically because a missing row and a zero-value row are silently treated the same way by a naive window function, which quietly produces a wrong average.
SQL Query: Find and delete duplicate rows while keeping the row with the earliest created_at timestamp.
Concept: Deleting duplicates while keeping a specific one, here the earliest, requires first identifying exactly which rows are the extras to remove, rather than deleting based on the duplicated value alone. Engine Mechanics: Using ROW_NUMBER() OVER (PARTITION BY dedup_key ORDER BY created_at ASC) inside a CTE assigns row number 1 to the earliest row within each duplicate group and 2, 3, and so on to every later duplicate, so deleting every row where that row number is greater than 1 removes exactly the duplicates while leaving precisely the single earliest row per group untouched. Trade-off: This is safe and precise because it explicitly identifies individual rows to delete by their own identity, but it requires a unique way to reference each specific physical row, such as a primary key, in the final DELETE, since row numbers computed in a CTE cannot be deleted by directly, only joined back against. Production Rule: I run the identification query alone first and inspect its output before ever attaching a DELETE to it, and I wrap the actual delete in a transaction so it can be rolled back if the identified set turns out to be wrong.
SQL Query: Compute Month-over-Month (MoM) retention or user churn cohorts from an events table.
Concept: Cohort retention asks, of the users who were active in a given starting month, what fraction were still active N months later, which requires first establishing each user's cohort month and then measuring their activity relative to it. Engine Mechanics: The first step computes each user's cohort month as the earliest month they appear in the events table; a second step computes, for every user and every month they were actually active, how many months that activity month is after their own cohort month; grouping by cohort month and that month-offset, then counting distinct active users in each combination and dividing by the original cohort's total size produces the retention percentage for each offset. Trade-off: Computing this correctly as a genuine cohort analysis, anchored to each user's own first-seen month, is more work than a naive month-over-month total user count, but a naive total active-user count conflates new user growth with actual retention of existing users, which answers a fundamentally different question. Production Rule: I keep the cohort-assignment step and the activity-measurement step as clearly separate stages, usually separate CTEs, so the logic stays auditable rather than collapsing both into one dense, hard-to-verify query.
Troubleshooting: A query that usually runs in 5ms suddenly takes 30 seconds in production. Walk through your debugging steps.
Concept: A sudden, dramatic regression on a previously fast query almost always means the execution plan itself changed, not that the underlying hardware or data simply got proportionally slower. Engine Mechanics: The first step is running EXPLAIN ANALYZE on the actual slow query right now to see the current real plan and compare its estimated versus actual row counts at each node, since a big divergence there points directly at stale statistics; the next checks are whether a recent bulk load or delete shifted the data's distribution enough to invalidate the old plan, whether an index was dropped, disabled, or became invalid, whether the table has grown enough to cross a planner threshold that changes its preferred strategy, and whether lock contention from another concurrent process is the real cause rather than the plan itself. Trade-off: Jumping straight to adding an index or rewriting the query without first confirming the actual current plan risks fixing a symptom that was never the real cause, wasting time while the actual regression continues. Production Rule: I always start with EXPLAIN ANALYZE on the live slow case before changing anything, and I specifically check whether ANALYZE statistics were refreshed recently, since stale statistics after a data shift are one of the most common causes of exactly this kind of sudden regression.
Troubleshooting: Your primary database CPU spikes to 100% due to lock contention during a flash sale. How do you diagnose and mitigate it?
Concept: Lock contention under a traffic spike means many transactions are competing to update the same rows, and CPU saturation here is usually a symptom of that contention, transactions spinning or queuing, rather than genuine computational work. Engine Mechanics: Diagnosing it means querying the engine's own lock and active-session views to see which specific rows or tables many transactions are waiting on simultaneously, which for a flash sale is almost always the small number of inventory rows for the items on sale; once identified, mitigations include moving simple decrement-style updates to an atomic conditional statement that avoids holding a lock across a round trip, such as UPDATE inventory SET stock = stock - 1 WHERE item_id = ? AND stock > 0, or using SELECT ... FOR UPDATE SKIP LOCKED to let independent reservation attempts proceed without queuing behind each other. Trade-off: These mitigations reduce lock hold time and contention dramatically, but they generally require restructuring the specific hot code path rather than simply adding hardware, since more CPU alone does not relieve genuine lock-queue contention. Production Rule: For any known high-contention event like a flash sale, I pre-emptively design the hot inventory update path around a single atomic conditional statement well before the event, rather than discovering the contention live under load.
Troubleshooting: Replication lag between your primary DB and read replica keeps growing from 100ms to 10 minutes. What are the common causes?
Concept: Growing, rather than stable, replication lag means the replica is consistently falling further behind the rate at which the primary is generating changes, which points at either the replica's own apply capacity or an upstream bottleneck in delivering changes to it. Engine Mechanics: Common causes include the replica's hardware, especially disk I/O, being slower than the primary's, so it cannot apply incoming changes as fast as they arrive; a single large, long-running transaction on the primary that must be applied as one unit on the replica, blocking it from processing anything else until that transaction finishes replaying; the replica also serving heavy read traffic that competes for the same I/O and CPU the replication apply process needs; and network bandwidth or connectivity issues between primary and replica slowing the delivery of the replication stream itself. Trade-off: Each cause demands a different fix, so treating all replication lag as one generic problem and just scaling the replica's hardware may do nothing if the actual cause is a single long transaction on the primary or saturated network bandwidth. Production Rule: I check, in order, the replica's own resource utilization during the lag, whether any recent long-running transaction landed on the primary, and the replication stream's actual network throughput, before assuming a hardware upgrade is the fix.
Troubleshooting: Your PostgreSQL database stops accepting writes with a Transaction ID Wraparound warning. What is happening and how do you fix it?
Concept: PostgreSQL's transaction IDs are a finite, wrapping counter, and if autovacuum has fallen far enough behind for long enough, the engine reaches a point where it must halt writes entirely to avoid the catastrophic correctness failure of transaction IDs wrapping around and making old, committed data look like it is from the future relative to new transactions. Engine Mechanics: Every row carries the transaction ID of whichever transaction created it, and Postgres compares transaction IDs to decide visibility; if the counter were allowed to wrap past its maximum value without old rows being frozen first, a wrapped ID could appear to precede rows it actually postdates, corrupting visibility logic, so Postgres proactively enters a protective, writes-disabled state once the counter's remaining headroom before wraparound becomes critically small, specifically to force immediate attention before actual corruption could occur. Trade-off: This protective shutdown is deliberately disruptive, since the alternative, letting it actually wrap, risks silent, severe data corruption that would be far worse and far harder to detect than a halted write path. Production Rule: The fix is running VACUUM, often an aggressive manual VACUUM FREEZE, immediately to advance the oldest unfrozen transaction ID and free up headroom, and the real long-term prevention is making sure autovacuum is never disabled and is tuned aggressively enough on high-write tables that it never falls this far behind in the first place.
Troubleshooting: Deadlocks are occurring frequently when two batch workers update rows in different orders. How do you eliminate them permanently?
Concept: This is a textbook lock-ordering deadlock, where two workers each need both of two locks but acquire them in opposite order, and the permanent fix is removing that ordering inconsistency rather than just retrying around it. Engine Mechanics: If worker A updates row 1 then row 2, while worker B updates row 2 then row 1, there is always some unlucky interleaving where A holds row 1 and waits on row 2 while B holds row 2 and waits on row 1, a genuine cycle the engine's deadlock detector will eventually catch and break by aborting one side; simply catching and retrying the resulting deadlock error reduces its impact but does nothing to reduce how often it actually happens, since the underlying ordering conflict is still there on every run. Trade-off: Enforcing a single, consistent lock acquisition order, such as always sorting the rows to update by primary key before touching them, eliminates the cycle structurally and permanently, but it requires every piece of code touching those rows, not just the two workers currently colliding, to follow the same ordering convention, or a new code path can reintroduce the same deadlock. Production Rule: I standardize a single, documented row-locking order for any set of rows that multiple code paths update together, and treat any new code that updates those same rows in a different order as a bug to fix, not a deadlock to retry around.