Question 1

An orders table has a customer_id column that references customers.id. What is the primary purpose of declaring this as a foreign key constraint, rather than just storing the value with no constraint?

Accepted Answer

A foreign key constraint's job is referential integrity: the database rejects any insert or update that would leave customer_id pointing at a row that does not exist. It is not, by itself, an indexing mechanism (many databases benefit from separately indexing foreign key columns, since they're not always indexed automatically), and it does not speed up joins on its own. Cascading behaviour on delete is opt-in — you have to declare it (for example ON DELETE CASCADE), it does not happen by default.

Question 2

You create a composite index on (last_name, first_name). Which of the following queries can that index be used to efficiently narrow down, rather than scan in full?

Accepted Answer

Composite indexes are ordered by their columns left to right, so they're only useful for seeking when the query filters on a prefix of that column list starting from the leftmost column. Filtering on last_name alone matches that leftmost-prefix rule. Filtering on first_name alone skips the leading column, so the index can't be used to seek. Wrapping last_name in UPPER() means the index (built on the raw values) no longer matches what's being compared, unless there's a separate function-based index for it. A leading wildcard in LIKE '%Ana%' also prevents an index seek, since the database can't know where in the string to start matching.

Question 3

A table stores one row per order line item, with columns order_id, product_name, product_price, customer_email, and customer_phone. A customer's email is repeated on every one of their orders, and when they change their phone number some rows get updated and others don't, leaving contradictory values. What is this problem called, and what fixes it?

Accepted Answer

This is a textbook update anomaly: because customer attributes are repeated across many rows instead of living in one place, an update has to be applied consistently everywhere it's duplicated, and if it isn't, the data becomes contradictory. Normalizing — giving customers their own table keyed by id, and referencing that id from orders — means each customer's email and phone are stored exactly once, so there's nothing to fall out of sync. Deadlocks, dirty reads, and isolation levels are transaction-concurrency concepts and don't describe or fix this particular problem.

Question 4

Which transaction isolation level is the minimum standard level that guarantees a transaction reading the same row twice will see the same value both times, assuming no explicit locking hints are used?

Accepted Answer

Read Uncommitted permits dirty reads, so it obviously doesn't guarantee this. Read Committed only guarantees that each individual read sees committed data at the moment it runs — another transaction can still commit a change to that same row between your two reads, producing a non-repeatable read. Repeatable Read is defined specifically to prevent that: once a transaction has read a row, it will see the same value for that row for the rest of the transaction (implementations commonly achieve this through snapshotting rather than holding a lock the whole time). Serializable also provides this guarantee, but the question asks for the minimum level that does.

Question 5

Which scenario is generally the best fit for a schema-flexible document database over a relational database?

Accepted Answer

Document databases are a strong fit when each record is naturally self-contained, the shape of that record varies a lot between items, and it's read and written as a unit — a product catalog with wildly different attributes per category is a classic case. Multi-account transactional consistency, heavy ad hoc joins across many entities, and strict multi-table referential integrity are all things relational databases are built for and document databases typically handle more awkwardly, if at all.

Question 6

A team proposes adding an index to every column of a heavily-written orders table, reasoning that a query might need it someday. What is the main downside of this approach?

Accepted Answer

Indexes speed up reads that use them, but that benefit isn't free: every write to an indexed column has to update the index too, so a heavily-written table with many unused indexes pays that write cost repeatedly for indexes nothing actually queries against. They also consume storage proportional to the data they index. The right approach is indexing based on real, known query patterns, not indexing everything preemptively. Databases don't silently cap or ignore indexes, and indexes have nothing to do with whether a primary key exists.

Question 7

When would you denormalize a schema, and why?

Accepted Answer

Denormalization means deliberately reintroducing redundancy that normalization would remove, usually by duplicating a value or precomputing an aggregate so a read doesn't have to join or recompute it every time. It's worth doing when a specific, known query is both frequent and expensive under the normalized design, and the cost of that repeated work outweighs the cost of keeping duplicated data in sync.

A common example is storing a running total or count — like an order's item_count or a post's like_count — directly on the parent row instead of computing it with a COUNT() join every time the row is displayed. Another is copying a small, rarely-changing attribute (like a product's name and price at the time of purchase) onto an order_items row, so historical orders don't change retroactively if the product is later renamed or repriced, and so displaying order history doesn't require joining back to the product table at all.

The trade-off is real: duplicated data can drift out of sync if every write path that should update it doesn't, so denormalization usually comes with extra responsibility — updating the copy in the same transaction as the source of truth, using a trigger, or accepting eventual consistency via an asynchronous job, and being explicit about which one you're choosing. It also makes the schema harder to reason about for anyone who doesn't know a value is duplicated. Because of that cost, I'd reach for denormalization only after normalizing first and measuring that a specific read path is actually a bottleneck — not as a starting design choice. Reporting and analytics tables are the other common case: they're often deliberately denormalized (wide tables, precomputed rollups) because they're read far more than the transactional schema they're derived from, and consistency requirements are looser than the source system.

Question 8

Explain the ACID properties of a database transaction using a concrete example.

Accepted Answer

Consider placing an order: the transaction needs to insert a new row into orders and decrement the matching row's stock in inventory. ACID describes the guarantees the database gives around that pair of operations.

Atomicity means the two writes succeed or fail together. If the stock decrement fails — say inventory would go negative — the order insert is rolled back too, so you never end up with an order that was charged but never decremented stock, or vice versa. There's no partial state where only one of the two writes landed.

Consistency means the transaction moves the database from one valid state to another, respecting whatever rules are declared — constraints, foreign keys, application invariants like 'stock cannot go below zero'. If the decrement would violate that invariant, the transaction is rejected rather than leaving the data in a state that breaks the rule.

Isolation means concurrent transactions don't see each other's half-finished work. If two customers buy the last unit of the same product at the same moment, isolation (enforced through locking or a multi-version snapshot, depending on the isolation level) ensures one transaction's decrement is fully applied — or the row is locked — before the other reads the stock count, so both orders can't succeed against a single remaining unit.

Durability means once the transaction commits and the database confirms it, the order and the updated stock count survive a crash immediately afterward — they've been written to persistent storage (and typically a write-ahead log), not just held in memory. A power failure a second after commit doesn't lose the order.

Together these four properties are why 'place an order and decrement stock' can be written as a single transaction and trusted not to corrupt data under failures or concurrent access, without the application having to manually reason about every interleaving.

Question 9

How would you index this table to serve both query patterns well, and what trade-offs would you weigh?

Accepted Answer

The two queries have different shapes, so I'd treat them as two separate indexing decisions rather than trying to serve both from one index.

For the login lookup, I'd put a unique index directly on email. It's an equality lookup on a column that should be unique anyway, so the index does double duty: it enforces the uniqueness constraint at the database level and makes the lookup a fast, direct seek instead of a table scan. Since logins happen constantly, this index earns its write overhead many times over.

For the reporting query, I'd add a composite index on (country, signup_date). Country is filtered by equality and should be the leading column, per the leftmost-prefix rule, so the database can jump straight to that country's rows; signup_date is a range filter and comes second, which also lets the database satisfy the 'sorted by signup date' requirement directly from the index for a given country, avoiding a separate sort step. If the report only ever needs a handful of columns (say user id and signup date), I'd consider making it a covering index by including those columns, so the query can be answered from the index alone without touching the table rows at all.

The trade-off to weigh is write cost: this table gets five million rows' worth of inserts and updates over time, and every index added means every insert and every update to an indexed column has to maintain that index too. Two well-chosen indexes matching real query patterns is a reasonable cost; I would not add further indexes speculatively. I'd also periodically check the query planner's actual index usage (most databases expose this) to confirm both indexes are being used as expected and that neither query is silently falling back to a full scan, and drop anything that isn't earning its write cost.

Question 10

What's the difference between optimistic and pessimistic concurrency control, and when would you choose each?

Accepted Answer

Both are strategies for handling the case where two transactions might try to modify the same row at roughly the same time, but they make opposite bets about how likely that collision actually is.

Pessimistic concurrency control assumes conflicts are likely, so it prevents them up front: a transaction acquires a lock on the row before reading it with intent to modify (for example, a SELECT ... FOR UPDATE), and any other transaction that wants to touch that row has to wait until the lock is released. This guarantees no conflicting write can slip in, at the cost of making other transactions block, and — under enough contention or careless lock ordering — a real risk of deadlocks that the database has to detect and resolve by aborting one side. It fits situations where conflicts are both likely and costly to redo, such as decrementing limited inventory or finalizing a seat booking, where letting two requests race and discovering the conflict after the fact would mean real overselling.

Optimistic concurrency control assumes conflicts are rare, so it doesn't lock anything up front. A transaction reads a row (often along with a version number or last-updated timestamp), does its work, and on write checks whether that version is still current — typically with an UPDATE ... WHERE id = ? AND version = ? that only succeeds if nothing else changed the row in between. If it fails, the application detects zero rows affected and retries or surfaces a conflict rather than blocking anyone. This fits situations where contention is genuinely low, like a user editing their own profile, where the throughput cost of taking a lock for every read-then-write would be wasted effort almost all the time.

In practice I'd default to optimistic for most application-level updates, since it doesn't cost anything when there's no conflict, and reach for pessimistic locking specifically around the narrow, high-contention operations — like the last few units of stock — where a failed optimistic write would be common and expensive to keep retrying.

Question 11

How would you decide between a relational database and a document database when starting a new service?

Accepted Answer

I'd start from the shape of the data and how it's actually accessed, rather than a general preference for one model.

The strongest signal toward relational is when the data is naturally made of related entities that need to be queried and joined in varied, sometimes unanticipated ways, or when correctness depends on coordinated changes across multiple rows or tables — an order system that has to update an order, decrement inventory, and record a payment consistently is a good example. Relational databases give you a fixed schema with constraints that catch bad data early, mature support for multi-row transactions, and a query language built for combining data across tables, which is exactly what that kind of system leans on constantly.

The strongest signal toward a document database is when each record is naturally self-contained and read or written as a whole, the shape of records varies significantly between instances or is expected to evolve frequently, and cross-record joins are rare. A content-management service storing pages built from very different combinations of blocks (text, image, embedded video, custom widgets) is a good fit — forcing every possible block type into a fixed relational schema tends to produce either a sprawling number of nullable columns or an awkward join across many tables just to render one page.

I'd also weigh two practical factors beyond the data shape itself: how the schema is expected to change over time (frequent, unpredictable shape changes favor documents; a stable, well-understood domain favors relational), and the team's operational familiarity, since running either system well in production is its own cost. In practice, most systems of any size end up using more than one datastore for different services or subsystems — the decision is rarely 'pick one database for the whole company,' it's 'pick the right model for this particular service's access pattern,' and it's fine for that answer to differ between two services in the same system.

Question 12

How would you partition or shard this table to address the slowdown, and what trade-offs come with your approach?

Accepted Answer

Given that access is heavily skewed toward recent data, I'd reach for table partitioning by date range before considering sharding across multiple database instances — they solve different problems, and partitioning is the simpler, lower-risk move.

Partitioning splits one logical table into multiple physical segments within the same database instance, commonly by a range on created_at (for example, one partition per day or per week). This helps directly here: queries scoped to the last 30 days only need to touch the last handful of partitions instead of scanning the whole table (partition pruning), which speeds up both the analytical queries and, indirectly, writes, since indexes per partition stay smaller and cheaper to maintain than one index over the full history. It also makes archiving simple — old partitions can be detached and moved to cheaper storage or dropped entirely on a schedule, rather than running a slow DELETE over hundreds of millions of rows.

Sharding is a different lever: splitting data across multiple separate database instances, each holding a subset of the rows, so that no single instance has to hold or serve the whole dataset. That's the right tool when a single instance's total capacity — CPU, memory, disk IOPS — is the actual bottleneck, not just query efficiency within one instance. I'd only reach for it if partitioning plus indexing plus archiving old data turned out not to be enough, because sharding adds real operational complexity: you need a shard key, and choosing it badly (for example, sharding by a user id where a small number of users generate most events) can create hot shards that defeat the purpose. Queries that need to span shards — anything not scoped to a single shard key — become significantly harder, often requiring a fan-out and merge at the application layer instead of a single query.

Given the description here — recent-data-heavy access on one table with old data barely touched — I'd expect date-range partitioning combined with an archival policy for anything past a few months to resolve most of the slowdown, and would only escalate to sharding if a single instance's hardware capacity, not query shape, turned out to be the actual ceiling.

Databases Interview Questions

Put this into practice