Question 1

What does ACID stand for and what does each property guarantee?

Accepted Answer

ACID is an acronym for four properties that guarantee reliable processing of database transactions: Atomicity, Consistency, Isolation, and Durability.

Atomicity means a transaction is all-or-nothing. Every operation either completes successfully, or the entire transaction is rolled back. Consider a bank transfer: debiting one account and crediting another must both succeed or both fail. Without atomicity, money disappears from one account without appearing in the other.

Consistency ensures that a transaction brings the database from one valid state to another, respecting all defined rules such as constraints, cascades, and triggers. Violating consistency means your data integrity rules are broken.

Isolation means concurrent transactions execute as if they were serial. Without proper isolation, you encounter anomalies like dirty reads, non-repeatable reads, and phantom reads. Databases offer isolation levels — Read Uncommitted, Read Committed, Repeatable Read, and Serializable — each trading off correctness for concurrency.

Durability guarantees that once a transaction is committed, it survives system failures. This is typically achieved through write-ahead logging (WAL).

The key tradeoff is performance versus correctness. Full ACID compliance is expensive. Strict isolation requires locking or MVCC. Durability requires fsyncing to disk on every commit. This is why NoSQL databases often relax ACID — offering eventual consistency — to achieve higher throughput. The BASE model represents the other end of this spectrum.

In practice, the choice depends on the domain. Financial systems demand strong ACID. Analytics pipelines can tolerate relaxed guarantees.

Question 2

How do database indexes work and when should you use them?

Accepted Answer

A database index is an auxiliary data structure, maintained separately from the table, that allows the query engine to locate rows matching a predicate without scanning every row. The canonical implementation is a B-tree (balanced tree), which keeps keys in sorted order and allows O(log n) lookups, range scans, and ordered traversal. Some databases also support hash indexes (O(1) equality lookups, no range scans), bitmap indexes (efficient for low-cardinality columns in analytical queries), and full-text indexes.

A B-tree index on a column stores the indexed values as keys in the tree, with each leaf node containing a pointer to the physical row (a row ID or clustered key). When the query engine evaluates a WHERE clause or JOIN condition, it can traverse the B-tree in O(log n) rather than performing a full table scan in O(n). On tables with millions of rows, this is the difference between a millisecond and minutes.

When to add an index: index columns that appear frequently in WHERE clauses, JOIN ON conditions, or ORDER BY/GROUP BY clauses. Foreign key columns are almost always worth indexing because JOINs on them are common. Columns with high cardinality (many distinct values) benefit most -- an index on a boolean column with only two distinct values is rarely useful.

When not to add an index: indexes consume disk space and must be updated on every INSERT, UPDATE, and DELETE. Tables that are write-heavy and read-seldom may be hurt more than helped. Very small tables often run faster with a sequential scan than index traversal due to the overhead of random I/O.

Composite indexes -- indexes on multiple columns -- are worth understanding specifically. A composite index on (a, b) supports queries filtering on a, or a and b together, but not b alone (due to the leftmost prefix rule). Column order in composite indexes matters significantly for which queries benefit.

Question 3

What is the difference between clustered and non-clustered indexes?

Accepted Answer

A clustered index determines the physical storage order of rows in the table. The leaf nodes of a clustered B-tree are the actual data rows themselves -- the index and the table are one structure. Because there is only one physical ordering, a table can have only one clustered index. In SQL Server and MySQL (InnoDB), the primary key is the clustered index by default. Range queries and ORDER BY on the clustered key are extremely efficient because the rows are already sorted on disk -- sequential I/O rather than random I/O.

A non-clustered index is a separate B-tree structure that stores copies of the indexed column values as keys and, in each leaf node, a pointer back to the actual row. In InnoDB this pointer is the primary key value (not a direct disk address); in SQL Server it is a row identifier (RID) for heap tables. A table can have many non-clustered indexes. When the query engine uses a non-clustered index, it first traverses the index B-tree to find the matching keys, then follows the row pointers to fetch the full rows -- a two-step lookup called a key lookup or bookmark lookup. This extra hop adds cost for non-covering queries.

The performance implications are concrete. A clustered index lookup is a single B-tree traversal ending at the data row. A non-clustered index lookup is a B-tree traversal plus an additional random I/O to fetch each row -- costly when many rows match. A non-clustered index that covers a query (contains all requested columns) eliminates the second lookup, performing as well as a clustered lookup for that specific query.

Design guidance: choose the clustered key carefully -- once set, reordering physical rows is expensive. High-cardinality, ever-increasing keys (like auto-increment IDs) minimize page splits and fragmentation. UUIDs as clustered keys cause fragmentation because random inserts scatter across the entire B-tree.

Question 4

What are the database isolation levels and what anomalies does each prevent?

Accepted Answer

Database isolation levels define how and when the changes made by one transaction become visible to other concurrent transactions. SQL defines four standard levels, each preventing an additional set of read anomalies at the cost of greater contention.

Read Uncommitted is the weakest level. A transaction can read changes made by other transactions that have not yet committed. This allows dirty reads -- reading data that may be rolled back, producing a view of the database that never actually existed. Almost never used in production.

Read Committed prevents dirty reads -- a transaction only sees data that has been committed. However, it allows non-repeatable reads: if transaction A reads a row, transaction B updates and commits that row, and A reads it again, A sees different values for the same row within the same transaction. This is the default in PostgreSQL and Oracle.

Repeatable Read prevents dirty reads and non-repeatable reads. Once a transaction reads a row, that row will return the same data for the duration of the transaction, even if another transaction modifies it. MySQL InnoDB uses MVCC (Multi-Version Concurrency Control) to implement this efficiently. However, phantom reads are still possible: a transaction running the same range query twice may see different sets of rows because another transaction inserted or deleted rows in that range. Repeatable Read is the default in MySQL InnoDB.

Serializable is the strongest level. Transactions execute as if they were run serially, one after another. This prevents all three anomalies. Implementations typically use range locks or predicate locks. PostgreSQL implements Serializable Snapshot Isolation (SSI), which uses MVCC with conflict detection rather than blocking locks, achieving full serializability with better concurrency than traditional lock-based approaches.

In practice, most applications use Read Committed or Repeatable Read. Choose Serializable only when correctness absolutely requires it -- for example, financial transactions where a race condition would produce double-spending.

Question 5

What is database normalization and what are the normal forms?

Accepted Answer

Database normalization is the process of structuring a relational schema to reduce data redundancy and eliminate update anomalies. The motivation: storing the same fact in multiple places means updates must happen in multiple places -- miss one, and the database becomes inconsistent. Normalization progressively decomposes tables based on functional dependencies.

First Normal Form (1NF) requires that each column contain only atomic (indivisible) values and that each row be uniquely identifiable. A column storing comma-separated tags or a repeating group of columns (phone1, phone2, phone3) violates 1NF. Fix: extract multi-valued attributes into a separate table with a foreign key.

Second Normal Form (2NF) requires 1NF plus that every non-key column is fully functionally dependent on the entire primary key -- no partial dependencies. This applies only to tables with composite primary keys. If a table (OrderID, ProductID, ProductName) has ProductName depending on ProductID alone (not the full composite key), ProductName is a partial dependency. Fix: move ProductName to a Products table keyed by ProductID.

Third Normal Form (3NF) requires 2NF plus no transitive dependencies -- no non-key column should depend on another non-key column. If a table has (EmployeeID, DepartmentID, DepartmentName) and DepartmentName depends on DepartmentID (not EmployeeID directly), that is a transitive dependency. Fix: move DepartmentName to a Departments table. 3NF is the practical target for most OLTP schemas.

Boyce-Codd Normal Form (BCNF) strengthens 3NF by requiring that every determinant is a candidate key. Some 3NF tables with overlapping candidate keys still have anomalies; BCNF eliminates them.

Normalization improves write integrity and reduces storage but can hurt read performance by requiring joins. OLAP systems often deliberately denormalize (star/snowflake schemas) to reduce join complexity for analytical queries.

Question 6

Explain PostgreSQL's MVCC, WAL, and VACUUM.

Accepted Answer

PostgreSQL's MVCC, WAL, and VACUUM form an interlocking system. Understanding how they interact is essential for diagnosing performance problems in production.

**MVCC (Multi-Version Concurrency Control)**

PostgreSQL never overwrites tuples in place. Instead, each `UPDATE` or `DELETE` creates a new version of the tuple and marks the old version as dead. Each tuple has hidden system columns: `xmin` (transaction ID that created it) and `xmax` (transaction ID that deleted/updated it, or 0 if live).

When a query runs, PostgreSQL determines its **snapshot** — the set of transaction IDs that are committed and visible at that point in time. A tuple is visible if `xmin` is committed and in the snapshot, and `xmax` is either 0 or not yet committed. This gives each transaction a consistent view of the data without taking read locks.

Consequences: readers never block writers, writers never block readers. This is PostgreSQL's concurrency strength. Isolation levels (Read Committed, Repeatable Read, Serializable) differ in when snapshots are taken.

**WAL (Write-Ahead Log)**

Before any data page change is written to disk, PostgreSQL writes a record to the WAL. This ensures that on crash, the WAL can be replayed to recover committed transactions that hadn't been flushed to the main data files. `fsync` on WAL commits guarantees durability (the D in ACID).

WAL also enables **streaming replication** — standby servers replay the WAL stream to stay in sync. `wal_level` controls how much information is logged (minimal, replica, logical). Logical replication uses logical decoding of the WAL to replicate data changes at a higher abstraction level.

**VACUUM**

Because MVCC leaves dead tuple versions on disk, PostgreSQL needs a process to reclaim that space. `VACUUM` scans tables for dead tuples (where `xmax` is a committed, no-longer-visible transaction) and marks their space as reusable. `VACUUM FULL` rewrites the table to disk, returning space to the OS — but it takes an exclusive lock.

`autovacuum` runs automatically based on the fraction of dead tuples (`autovacuum_vacuum_scale_factor`). **Transaction ID wraparound** is the critical reason VACUUM must not be disabled: PostgreSQL uses 32-bit transaction IDs. After ~2 billion transactions, IDs wrap around. VACUUM freezes old tuples (setting `xmin` to a special frozen transaction ID) to prevent this. Tables that aren't vacuumed will eventually trigger `autovacuum` in an emergency freeze mode, and if ignored, PostgreSQL will refuse writes to protect data integrity.

`pg_stat_user_tables` tracks `n_dead_tup` and `last_vacuum` — monitor these to catch tables where autovacuum isn't keeping up (common on high-write tables).

Question 7

What is database sharding and what are its trade-offs?

Accepted Answer

Database sharding is horizontal partitioning of data across multiple independent database nodes (shards), each owning a subset of the dataset. Unlike vertical scaling (bigger hardware) or read replicas (copies), sharding distributes both reads and writes, enabling scale beyond what a single node can handle.

There are three main sharding strategies. **Range sharding** assigns contiguous key ranges to shards (e.g., user IDs 1–1M on shard 1). It supports efficient range queries but creates hot spots when access patterns are skewed (e.g., recent records are always on the latest shard). **Hash sharding** applies a hash function to the shard key (e.g., `hash(user_id) % N`) to distribute data uniformly. It eliminates hot spots but makes range queries expensive—they must scatter to all shards. **Directory sharding** maintains an explicit mapping table from key to shard. It's the most flexible (arbitrary assignment, easy rebalancing) but adds a lookup hop and makes the directory a potential bottleneck and SPOF.

Choosing the shard key is the most consequential decision. A good shard key distributes data and queries evenly, is immutable (changing it requires migrating the row), and aligns with the primary query pattern. Bad shard keys cause hot shards that bottleneck the entire system while other shards sit idle.

The major trade-offs: **Cross-shard queries** are expensive. A query that filters on a non-shard-key column must scatter-gather across all shards and merge results in application code or a query router. **Cross-shard transactions** require distributed coordination (2PC or Saga). **Joins** across shards are generally not supported at the database layer and must be done in application code. **Rebalancing** when adding shards is complex—consistent hashing minimizes data movement, but it's still operationally heavy.

Sharding adds significant operational complexity: connection pooling per shard, schema migrations must run on all shards, backups across N nodes, and monitoring per shard. Many teams delay sharding by exhausting vertical scaling, read replicas, caching, and query optimization first. Managed solutions like CockroachDB, Vitess, or DynamoDB handle sharding transparently and are worth evaluating before rolling a bespoke sharding layer.

Question 8

What are read replicas and when would you use them?

Accepted Answer

A read replica is a database instance that maintains a continuously updated copy of a primary (writer) instance and serves read-only queries. The primary streams its write-ahead log (WAL) or binlog to replicas, which apply the changes asynchronously — or synchronously in the case of synchronous replication.

The primary use case is **read scaling**. Most web applications are read-heavy (80–95% reads). A single primary can become a bottleneck. Adding read replicas distributes SELECT load horizontally. Replicas can also run expensive analytics or reporting queries without affecting primary performance.

**Replication lag** is the central trade-off. Asynchronous replicas apply changes after the primary commits — typically milliseconds behind, but potentially seconds during high write load or network issues. This means a client that reads from a replica immediately after a write may see stale data. Common mitigation: route the user's own read-after-write to the primary (read-your-writes consistency), or use a short delay before reading from a replica.

**When to use read replicas:**
- Analytics or reporting queries that would saturate the primary
- High read/write ratio with reads exceeding primary capacity
- Geographic distribution — place replicas closer to users in different regions
- Offloading backups — take snapshots from a replica to avoid locking the primary

**When not to use read replicas:**
- Write-heavy workloads — replicas don't help; consider sharding instead
- Strict read-after-write consistency requirements — either route all reads to primary or use synchronous replication (which adds write latency)
- As a replacement for proper indexing — a poorly indexed query is slow on a replica too

Most cloud databases (AWS RDS, Google Cloud SQL, PlanetScale) support read replicas with simple configuration. Connection poolers like PgBouncer or application-level routing (separate read/write connection strings) handle traffic distribution. Multi-region replication (e.g., Aurora Global Database) uses the same model with higher lag due to cross-region network latency.

Promotion: if the primary fails, a replica can be promoted to primary. Failover time depends on replication mode — synchronous replicas have zero data loss; async replicas may lose the last few seconds of commits.

Question 9

What is a covering index?

Accepted Answer

A covering index is an index that contains every column referenced in a query — in the SELECT list, WHERE clause, ORDER BY, and JOIN conditions — so the database engine can satisfy the entire query by reading the index alone, without accessing the underlying table (heap) pages. This access pattern is called an **index-only scan**.

To understand why this matters, consider how a standard B-tree index works. The index stores the indexed columns plus the row's physical location (heap pointer). For a query like `SELECT name, email FROM users WHERE email = 'x@y.com'`, if only `email` is indexed, the engine finds the matching rows in the index, then follows heap pointers to fetch `name` from the table pages — two I/O operations per row.

If you create `CREATE INDEX idx ON users(email, name)`, the index now contains both `email` and `name`. The engine finds the matching `email` entry in the index and reads `name` directly from the index leaf node — zero heap I/O. For high-cardinality filtered queries returning many rows, this difference is dramatic.

**When to use covering indexes:**
- High-frequency read queries with stable column sets (dashboards, hot API endpoints)
- Queries on large tables where heap I/O is the bottleneck
- Sorting: if the covering index matches the ORDER BY, you avoid a sort step
- COUNT/aggregate queries over filtered sets

**Trade-offs:**
- Write overhead: every INSERT, UPDATE, or DELETE must update the index as well as the heap. Wider indexes mean more index maintenance.
- Storage: covering indexes can be large — you're duplicating data into the index
- Column order matters: the leftmost columns must match the WHERE clause for the index to be used at all. The covering columns (SELECT list) typically go at the end.

In PostgreSQL, `EXPLAIN (ANALYZE, BUFFERS)` shows `Index Only Scan` and `Heap Fetches = 0` when a covering index is fully effective. In MySQL InnoDB, secondary indexes already include the primary key, so covering is simpler to achieve — include all needed non-PK columns.

A practical pattern: identify your slowest N queries, check their execution plans for `Seq Scan` or `Heap Fetches > 0`, and add covering indexes targeting the specific column set of each hot query.

Question 10

What is the difference between master-slave and master-master replication?

Accepted Answer

Database replication strategies fundamentally differ in where writes are accepted, which drives everything from consistency guarantees to operational complexity.

**Master-slave replication** (also called primary-replica) designates a single node as the write target. All writes go to the master; changes are replicated asynchronously (or semi-synchronously) to one or more slaves. Slaves serve read traffic, providing horizontal read scaling. Failover requires promoting a slave to master — a non-trivial operation that must ensure the promoted slave has applied all pending replication events to avoid data loss. This topology is simple to reason about: there is exactly one authoritative state at any point in time, so there are no write conflicts.

The downsides are write throughput ceiling (single write node), and if the master fails before replication completes, the acknowledged writes on the master but not yet replicated are lost — this is the **replication lag** window of data loss. Semi-synchronous replication (MySQL's `rpl_semi_sync`) waits for at least one slave to acknowledge before committing, reducing but not eliminating this window.

**Master-master replication** (multi-primary) allows writes to be accepted by multiple nodes simultaneously. Each node replicates its writes to all other masters. This provides write fault tolerance (any master can fail and writes continue) and potentially lower write latency (clients can write to the nearest node). These benefits come with a fundamental challenge: **write conflicts**. If two masters concurrently update the same row, the system must detect and resolve the conflict — either with last-write-wins (risky), application-level conflict resolution, or CRDTs for specific data types.

In practice, true multi-primary systems are difficult to operate correctly. Tools like MySQL Group Replication and Galera Cluster implement synchronous multi-primary with a distributed consensus protocol (Paxos / Totem) to prevent conflicts at commit time, at the cost of cross-node coordination latency on every write. CockroachDB and Vitess extend this to globally distributed deployments.

For most OLTP workloads, master-slave with read replicas and a robust failover tool (Patroni for PostgreSQL, Orchestrator for MySQL) is the right choice. Master-master is warranted for active-active geo-distribution where write latency to a single region is unacceptable.

Question 11

How does database replication failover work?

Accepted Answer

**Replication** is the process of copying data from a primary (master) database to one or more replicas (secondaries). It serves two purposes: durability (data survives primary failure) and read scaling (queries can be distributed across replicas).

**Replication modes:**

- **Synchronous (semi-sync)**: the primary waits for at least one replica to acknowledge the write before confirming to the client. Zero data loss on failover, but higher write latency. PostgreSQL's synchronous_commit, MySQL semi-sync.
- **Asynchronous**: the primary confirms immediately and replicates in the background. Lower write latency, but a brief failover window exists where the primary committed data that has not yet reached replicas — this is called **replication lag**. On failover, the promoted replica may be missing the last few transactions.

**Failover process:**

1. **Detection**: monitoring detects primary unavailability. Must distinguish crash from network partition — a partitioned primary is still running and accepting writes (split-brain risk).
2. **Promotion**: the most up-to-date replica is elected primary. In asynchronous setups this may involve data loss if lag existed.
3. **Fencing (STONITH)**: the old primary must be reliably killed or isolated before the new one accepts writes. Without fencing, two primaries can write simultaneously, creating diverging data.
4. **Redirect**: clients or the load balancer update their connection to point to the new primary. Connection poolers like PgBouncer or ProxySQL handle this transparently.
5. **Re-add old primary as replica**: after recovery, the old primary rejoins as a replica and catches up via WAL shipping (Postgres) or binlog (MySQL).

**Automated failover tools**: Patroni (Postgres + etcd/Consul/ZooKeeper for consensus), MySQL InnoDB Cluster (built-in group replication), AWS RDS Multi-AZ (managed, ~60s failover).

**RPO and RTO**: Recovery Point Objective is how much data you can afford to lose (0 for synchronous, seconds for async). Recovery Time Objective is how long the system can be down. These are business requirements that drive the replication mode choice.

The interview-critical point: failover is not automatic unless you build it. Naive failover without fencing causes split-brain. Always ask 'what kills the old primary before the new one starts writing?'

Question 12

What techniques do you use to optimize database queries?

Accepted Answer

Query optimization is the discipline of reducing query latency and server load without changing correctness. It spans schema design, index strategy, query rewriting, and infrastructure choices.

**EXPLAIN / query plan analysis** is always the first step. Before optimising, measure. `EXPLAIN ANALYZE` (Postgres) or `EXPLAIN` (MySQL) shows whether the planner is performing a sequential scan, index scan, or nested loop join, and at what row estimate. Optimizing without looking at the query plan is guesswork.

**Indexing** is the highest-leverage change. Create indexes on columns used in `WHERE`, `JOIN ON`, and `ORDER BY` clauses. Composite indexes must match the query's column order—a composite index on `(user_id, created_at)` supports `WHERE user_id = ? ORDER BY created_at` but not `WHERE created_at = ?` alone. Partial indexes (Postgres) index only rows matching a condition, reducing index size for sparse queries. Covering indexes include all projected columns in the index itself, eliminating the heap fetch entirely.

**Avoiding N+1 queries**: N+1 occurs when code issues one query to fetch N parent records and then N individual queries for child records. Fix with JOIN or ORM eager loading (`SELECT IN` batch). Tools like Django Debug Toolbar or SQLAlchemy's echo mode surface N+1 in development.

**Query rewriting**: Rewrite correlated subqueries as JOINs or CTEs. Avoid `SELECT *`—projecting only needed columns reduces I/O and network transfer. Avoid functions on indexed columns in WHERE clauses (`WHERE YEAR(created_at) = 2024` prevents index use; `WHERE created_at >= '2024-01-01'` uses it).

**Pagination**: Offset pagination on large tables is expensive—the database must scan and discard all preceding rows. Keyset (cursor) pagination (`WHERE id > last_seen_id LIMIT 20`) is O(1) per page.

**Partitioning and sharding**: For very large tables, range or list partitioning prunes partitions at query time, reducing scanned data. Sharding distributes data across nodes but increases query complexity.

**Connection pooling**: Avoid opening a new database connection per request—connection setup is expensive. Use PgBouncer (Postgres) or application-level pools (SQLAlchemy pool). Pool exhaustion is a common performance cliff in high-concurrency applications.

Question 13

What does SELECT FOR UPDATE do and when would you use it?

Accepted Answer

`SELECT FOR UPDATE` is a SQL statement that reads rows and immediately acquires an exclusive row-level lock on them within the current transaction. Other transactions attempting to modify those rows — or acquire their own `SELECT FOR UPDATE` locks — will block until your transaction completes. Regular reads (without `FOR UPDATE` or `FOR SHARE`) are typically not blocked, depending on the isolation level and database engine.

The primary use case is pessimistic concurrency control: you want to read a value, make a decision based on it, and update it — guaranteeing that no other transaction can change those rows between your read and your write. A classic example is an inventory system: `SELECT quantity FROM inventory WHERE product_id = 42 FOR UPDATE`, followed by a check and `UPDATE inventory SET quantity = quantity - 1`. Without the lock, two concurrent transactions could both read quantity=1, both decide to decrement, and both commit — leaving quantity at -1.

Another common pattern is job queue processing: workers select a batch of pending jobs with `FOR UPDATE SKIP LOCKED` (supported in PostgreSQL, MySQL 8+, Oracle). `SKIP LOCKED` causes the query to skip rows already locked by other workers, enabling efficient parallel queue draining without contention or blocking.

Key operational considerations: `SELECT FOR UPDATE` escalates lock scope to all rows returned, so a poorly written predicate can lock large swaths of the table and cause cascading blocking. Lock duration is tied to transaction duration — long-running transactions holding these locks are a common source of production latency spikes. Deadlocks are also possible when two transactions lock rows in opposite orders; applications must handle deadlock errors with retry logic.

In PostgreSQL, `FOR UPDATE` can be qualified with `OF table_name` in joins to specify which table's rows to lock, avoiding unintended wide locks in complex queries.

Question 14

How do you check for date range overlaps in SQL?

Accepted Answer

Checking for date range overlap is a deceptively simple problem that many engineers get wrong by trying to enumerate overlap cases. The correct approach is to use the complement: two ranges do NOT overlap when one ends before the other starts. Negating that gives the universal overlap condition. For ranges [A.start, A.end) and [B.start, B.end), the overlap condition is: `A.start < B.end AND A.end > B.start` This single expression handles all overlap geometries: partial overlap from either side, one range fully contained within the other, and identical ranges. The boundary behavior depends on whether your ranges are inclusive or exclusive on the endpoints. For closed intervals [A.start, A.end], use `<=` instead of `<`. In SQL, this is expressed as a WHERE clause predicate. To find all bookings that overlap with a candidate booking ('2024-01-10', '2024-01-15'): ```sql SELECT * FROM bookings WHERE start_date < '2024-01-15' AND end_date > '2024-01-10'; ``` For self-joins (finding all overlapping pairs within a table): ```sql SELECT a.id, b.id FROM bookings a JOIN bookings b ON a.id < b.id AND a.start_date < b.end_date AND a.end_date > b.start_date; ``` The `a.id < b.id` condition prevents returning both (A,B) and (B,A). Some databases provide native range types. PostgreSQL has the `tsrange` / `daterange` types with the `&&` overlap operator, which is both more readable and index-optimizable via GiST indexes. For high-volume overlap queries (scheduling systems, calendar applications), a plain B-tree index on start_date and end_date is insufficient — a GiST index on a range column, or application-level interval tree structures, are needed for sub-linear performance.

Question 15

How does optimistic locking with a version column work?

Accepted Answer

Optimistic locking is a concurrency control strategy that avoids holding database locks during reads. Instead of locking a row when you read it, you record its current version, perform your computation, and then verify the version hasn't changed at write time. If another transaction modified the row between your read and your write, the version check fails and your update is rejected — you then retry with fresh data.

The implementation adds a `version` column (integer or timestamp) to the table. The pattern is:

1. Read: `SELECT id, balance, version FROM accounts WHERE id = 42` — returns version = 7.
2. Process: compute the new balance in application code.
3. Write: `UPDATE accounts SET balance = 950, version = 8 WHERE id = 42 AND version = 7`.
4. Check rows affected: if 0 rows updated, a concurrent modification occurred — retry from step 1.

The `WHERE version = 7` clause is the guard. If another transaction incremented the version to 8 before your update, your WHERE clause matches 0 rows, the update silently does nothing, and your application detects the conflict via `affected rows = 0`.

Optimistic locking is well-suited for low-contention scenarios where conflicts are rare. The read path has no overhead — no locks held, no blocking. Under high contention, retry storms can develop: many concurrent writers all reading the same version, all but one failing, all retrying — creating thundering herd behavior that can be worse than pessimistic locking.

ORMs like Hibernate and JPA support optimistic locking natively via `@Version` annotations. The framework handles version increment and conflict detection automatically, throwing `OptimisticLockException` on conflict.

Timestamp-based versions (using `updated_at`) are less reliable than integer counters because two updates within the same millisecond can produce identical timestamps, creating a false sense of no conflict.

Question 16

What is a partial index and when would you use one?

Accepted Answer

A partial index (also called a filtered index in SQL Server) is an index built only over the rows that satisfy a specified WHERE condition. Instead of indexing every row in a table, the index stores only entries for the rows matching the predicate, making it smaller, faster to scan, and cheaper to maintain on writes.

The canonical use case is a status column with extreme skew. Consider a `jobs` table with 10 million rows where 9.9 million have `status = 'completed'` and 100,000 have `status = 'pending'`. A full index on `status` is large and most of it is useless — queries almost always filter for `pending` jobs. A partial index `CREATE INDEX idx_jobs_pending ON jobs(created_at) WHERE status = 'pending'` covers only 100,000 rows. The index is 100x smaller, fits in cache, and queries for pending jobs execute dramatically faster.

Partial indexes also enforce conditional uniqueness. In PostgreSQL, you can create `CREATE UNIQUE INDEX idx_users_active_email ON users(email) WHERE deleted_at IS NULL` to enforce unique emails only among non-deleted users, allowing multiple soft-deleted rows with the same email without violating the constraint.

For the query optimizer to use a partial index, the query's WHERE clause must be logically compatible with the index predicate — the query's filter must be at least as restrictive. A query with `WHERE status = 'pending' AND created_at > now() - interval '1 day'` would use the partial index above. A query with `WHERE created_at > now() - interval '1 day'` (without the status filter) would not.

Write performance benefits from partial indexes: INSERT/UPDATE/DELETE only update the index when the row satisfies the index predicate. Rows outside the filter incur zero index maintenance cost. This makes partial indexes particularly valuable on append-heavy tables where new rows frequently fall outside the filtered subset.

Question 17

What is a composite index and how does the prefix rule apply?

Accepted Answer

A composite index (also called a multi-column index) is an index built on two or more columns in a defined order. The key principle governing its use is the leftmost prefix rule: the database can use the index only when the query's predicates include the leading columns of the index in order, without skipping.

For a composite index on `(last_name, first_name, birth_year)`, the following queries can use the index: filtering on `last_name` alone, `last_name + first_name`, or `last_name + first_name + birth_year`. Filtering on `first_name` alone or `birth_year` alone cannot use the index because the leftmost column (`last_name`) is absent from the predicate.

The prefix rule exists because composite indexes are sorted first by the leftmost column, then by the next column within each group of equal leftmost values, and so on. Scanning the index without the leftmost column would require a full index scan — at which point a full table scan may be comparable or cheaper.

Range predicates on an intermediate column stop the index from being used for filtering on subsequent columns. For `WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND birth_year = 1985`, the index is used for `last_name` (equality) and `first_name` (range), but `birth_year` cannot be satisfied from the index — it requires a post-filter on qualifying rows.

Composite index column order should reflect your most selective and most commonly used filter first. Equality predicates should come before range predicates in the index definition, as range predicates terminate prefix utilization. Covering indexes extend this concept: by including all columns referenced in the query (SELECT + WHERE + ORDER BY), the database can satisfy the query entirely from the index without touching the heap table, eliminating a second I/O lookup.

Databases Interview Questions — What Senior Engineers Need to Know

All 17 Questions

How to Prepare

Related Topics

Test Your Knowledge