Foundations
What is a relational database?
A relational database stores data in tables of rows and columns that follow a declared schema, links tables through keys, and is queried with Structured Query Language (SQL). Constraints such as NOT NULL, UNIQUE, CHECK and foreign keys let the database refuse bad data instead of trusting every application (PostgreSQL 18 constraints). The schema is the contract.
Why it is asked: it checks that you can define the basic term without jargon. Read the full explanation: how the system design round works.
What is the difference between a primary key and a foreign key?
A primary key identifies each row of its own table: the values must be unique and not null, and the database builds a unique B-tree index for it. A foreign key is a column whose values must match a row in another table, which maintains referential integrity. The PostgreSQL documentation adds that declaring a foreign key does not automatically index the referencing column.
Why it is asked: the missing index is a classic follow-up. Read the full explanation: data modeling interview questions.
Can a unique column hold two NULL values?
Usually yes. By default two NULLs are not considered equal in a uniqueness check, so a UNIQUE column accepts several of them; PostgreSQL 15 added NULLS NOT DISTINCT to change that. A test in an in-memory SQLite database stored two NULLs in a UNIQUE column without error.1 Behaviour differs between products, so check the one you use.
Why it is asked: it separates memorised definitions from tested ones. Read the full explanation: data modeling interview questions.
What is normalisation, and when would you denormalise?
Normalisation splits data so each fact is stored once, which prevents update anomalies where two copies disagree. Denormalising copies data back together so a read needs fewer joins. MongoDB's documentation says embedded documents and arrays often make multi-document transactions unnecessary, and that a denormalised model remains optimal for many scenarios (MongoDB manual). Denormalise for a measured read cost, not by habit.
Why it is asked: it tests whether you can argue both sides. Read the full explanation: data modeling interview questions.
What are the main types of join?
An inner join keeps rows that match in both tables. A left join keeps every row of the left table and fills the right side with NULLs where nothing matches; a right join mirrors it; a full join keeps unmatched rows from both. A cross join pairs every row with every row, so its size is the product of the two tables.
Why it is asked: joins are the core of relational querying. Read the full explanation: the SQL join diagram explained.
How do you choose between SQL and NoSQL?
Choose by access pattern and consistency need, not fashion. Relational stores suit ad hoc queries, joins and multi-row transactions. Key-value and document stores suit known-key lookups at large scale, often with weaker default consistency: DynamoDB reads are eventually consistent unless you ask otherwise (Amazon Web Services documentation). Many systems use both, each for what it does best.
Why it is asked: it tests judgment over loyalty. Read the full explanation: system design round.
Indexes and query plans
What is a database index and why does it speed up a query?
An index is a separate, sorted structure over one or more columns that lets the database find matching rows without reading the whole table. SQLite's documentation contrasts a full scan, which costs time proportional to the row count N, with a binary search that costs time proportional to log N (SQLite query planning). The price is extra storage and slower writes.
Why it is asked: it is the first performance question. Read the full explanation: SQL interview questions: the spoken round.
How many disk reads does a B-tree lookup need on a billion rows?
Few, because a B-tree node holds many keys, so the tree is shallow. As an illustration, if each node branches about 100 ways, a billion rows need a tree five levels deep; at about 300 ways, four levels.1 Real fanout depends on key size and page size, so quote the idea, not the digits.
Why it is asked: it tests whether you understand why indexes scale. Read the full explanation: database indexing in the SQL hub.
What is the difference between a B-tree index and a hash index?
A B-tree keeps keys in sorted order, so it handles equality and range queries and can return rows already sorted. A hash index stores a 32-bit hash of the value in PostgreSQL, so it handles only simple equality (PostgreSQL 18 index types). B-tree is the default because most queries need ranges or ordering.
Why it is asked: it checks that you know what each structure cannot do. Read the full explanation: design a distributed cache.
Why might the database ignore your index?
Several reasons. The query may not match the index: a pattern that starts with a wildcard cannot use a B-tree seek, and PostgreSQL allows it only for patterns anchored at the start. The table may be tiny, so a scan is cheaper. Or the condition matches so many rows that a sequential scan wins. In the SQLite test a leading-wildcard LIKE scanned the whole index instead of searching it.1
Why it is asked: "I added an index and nothing changed" is a real incident. Read the full explanation: SQL interview questions: the spoken round.
What is a composite index, and does column order matter?
A composite index covers several columns in a fixed order. Order matters because the index is sorted by the first column, then the second within it: SQLite's example index on (fruit, state) answers fruit = 'Orange' AND state = 'CA' with one binary search of the index and one of the table, and the documentation advises dropping a shorter index that is a prefix of another (SQLite query planning). Put the equality column first.
Why it is asked: it separates memorising from understanding sorted structure. Read the full explanation: SQL interview questions: the spoken round.
What is a covering index?
A covering index contains every column a query needs, so the database answers from the index and never reads the table. SQLite says this roughly halves the binary searches, a constant-factor gain instead of the order-of-magnitude gain of adding the first index (SQLite query planning). In the test, the plan read SEARCH ... USING COVERING INDEX.1
Why it is asked: it tests the next step after "add an index". Read the full explanation: SQL interview questions: the spoken round.
How do you read a query plan?
Run EXPLAIN and read the tree from the innermost node outward: each node shows its scan type, estimated cost and estimated rows. EXPLAIN ANALYZE also executes the query and reports real row counts and run times, so any side effects happen; wrap an update in a transaction and roll it back (PostgreSQL 18 query-plan documentation). Compare estimates with actuals to find bad statistics.
Why it is asked: tuning without a plan is guessing. Read the full explanation: SQL interview questions: the spoken round.
Knowing it is half the job. Saying it is the other half.
Interview Clozo is an AI interview copilot for real interview rounds: it hears the question and puts a strong answer on your screen, and you say it in your own words. See the features or how it works.
Start Free Trial →Transactions
What does ACID mean?
Atomicity, Consistency, Isolation and Durability (ACID) are the promises of a transactional database. Atomicity means the steps of a transaction happen completely or not at all. Durability means a committed transaction is logged to permanent storage before it is reported complete (PostgreSQL 18 transactions tutorial). Isolation is how much concurrent transactions see of each other; consistency means constraints still hold.
Why it is asked: it is the standard opener for payments and ledgers. Read the full explanation: design a payment system.
What happens when a money transfer fails halfway?
If the debit and the credit sit in one transaction and the code issues ROLLBACK on any error, both are undone and no money is lost or created. The error alone may not do that: by default SQLite, following the SQL standard, undoes only the failing statement and keeps the transaction open (SQLite conflict resolution). In a test, the debit broke a non-negative balance CHECK; after ROLLBACK the balances stayed 100 and 50, but a COMMIT kept the credit and made 250.1
Why it is asked: it is the plain-language test of atomicity. Read the full explanation: design a payment system.
What are the transaction isolation levels?
The SQL standard defines four: read uncommitted, read committed, repeatable read and serializable, each blocking more anomalies such as dirty reads, nonrepeatable reads and phantoms. PostgreSQL implements three, treating read uncommitted as read committed, with read committed as the default; repeatable read is snapshot isolation, and serializable adds detection of serialization anomalies (PostgreSQL 18 isolation). Higher levels need retries.
Why it is asked: it tests precision about what a level really prevents. Read the full explanation: concurrency interview questions.
What is multiversion concurrency control?
Multiversion concurrency control (MVCC) keeps several versions of a row so readers see a consistent snapshot while writers create new versions. The PostgreSQL documentation states that reading never blocks writing and writing never blocks reading (PostgreSQL 18 MVCC). Explicit locks can still make sessions wait, and old versions must be cleaned up later.
Why it is asked: it explains how a database stays fast under mixed load. Read the full explanation: concurrency interview questions.
What is a deadlock and how do you avoid it?
A deadlock is two or more transactions each holding a lock the other wants. PostgreSQL detects it and aborts one transaction, though which one is hard to predict. The documentation's best defence is to acquire locks on multiple objects in a consistent order, with retries as a fallback, and to avoid holding transactions open while waiting for user input (PostgreSQL 18 deadlocks).
Why it is asked: it shows whether you have run real concurrent writes. Read the full explanation: concurrency interview questions.
Consistency and scale
What does the CAP theorem say?
The consistency, availability and partition tolerance (CAP) theorem says that when a network partition separates replicas, a system cannot keep both perfect consistency and perfect availability. Brewer later called the "pick two of three" slogan misleading: the theorem forbids only perfect availability and consistency during partitions, which are rare, and the choice can change per operation or per user (Brewer, 2012).
Why it is asked: it tests nuance behind a famous slogan. Read the full explanation: design object, key-value and blob storage.
What is eventual consistency?
Eventual consistency means that if no new writes arrive, all replicas converge to the same value, but a read right after a write may return old data. DynamoDB reads are eventually consistent by default; a strongly consistent read returns the latest write, and eventually consistent reads cost half as much (Amazon Web Services documentation). Amazon's Dynamo paper accepts weaker consistency during failures to stay available.
Why it is asked: it checks that you know the cost of availability. Read the full explanation: design object, key-value and blob storage.
What is partitioning, and when does it help?
Partitioning splits one large table into smaller pieces by range, list or hash of a key. PostgreSQL's documentation lists faster queries when hot rows sit in few partitions, cheap bulk loads and deletes by adding or dropping partitions, and tiered storage. It says the benefit is normally worthwhile only for very large tables, with a rule of thumb that the table exceeds server memory (PostgreSQL 18 partitioning).
Why it is asked: it is the step before sharding. Read the full explanation: system design round.
Is an in-memory database durable?
Only if configured to be. Redis offers snapshots at intervals, an append-only log of every write, both, or neither. Snapshots can lose the latest minutes of writes; with the default append-only policy of flushing every second, you can lose about one second (Redis persistence). Caches often turn persistence off.
Why it is asked: it tests the speed-versus-durability trade-off. Read the full explanation: design a distributed cache.
Which database is best for big data?
None is best in general; match the store to the access pattern. Known-key reads and writes at very large scale suit a partitioned key-value store of the kind described in the Dynamo paper. Joins and multi-row transactions suit a relational database, partitioned when the table outgrows memory. Analytical scans over huge data suit column stores, which read only the columns a query needs. Name the access pattern first.
Why it is asked: "best" is a trap, and the honest answer is a question. Read the full explanation: backend interview questions.
Text description of the diagram
- Joins and multi-row transactions suit a relational database.
- Known-key reads and writes at very large scale suit a partitioned key-value store.
- Analytical scans over huge data suit column stores, which read only the columns a query needs.
- Define each term in one sentence before adding detail: key, index, transaction, isolation.
- An index trades storage and write cost for fast reads, and the query must match its order.
- Read the plan instead of guessing;
EXPLAIN ANALYZEruns the statement. - Transactions protect multi-step changes, and higher isolation levels need retries.
- Choose a database from the access pattern and the consistency you need, not from habit.
Sources
- PostgreSQL 18 documentation: Transaction Isolation (13.2) — PostgreSQL Global Development Group (page shows version 18.6 only; read 2026-10-24), 24 Oct 2026
- PostgreSQL 18 documentation: Index Types (11.2) — PostgreSQL Global Development Group (version 18.6; read 2026-10-24), 24 Oct 2026
- PostgreSQL 18 documentation: Using EXPLAIN (14.1) — PostgreSQL Global Development Group (version 18.6; read 2026-10-24), 24 Oct 2026
- PostgreSQL 18 documentation: Explicit Locking, Deadlocks (13.3.4) — PostgreSQL Global Development Group (version 18.6; read 2026-10-24), 24 Oct 2026
- PostgreSQL 18 documentation: Table Partitioning (5.12) — PostgreSQL Global Development Group (version 18.6; read 2026-10-24), 24 Oct 2026
- PostgreSQL 18 documentation: Constraints (5.5) — PostgreSQL Global Development Group (version 18.6; read 2026-10-24), 24 Oct 2026
- PostgreSQL 18 documentation: Transactions tutorial (3.4) — PostgreSQL Global Development Group (version 18.6; read 2026-10-24), 24 Oct 2026
- PostgreSQL 18 documentation: MVCC Introduction (13.1) — PostgreSQL Global Development Group (version 18.6; read 2026-10-24), 24 Oct 2026
- SQLite: The ON CONFLICT clause — SQLite project (page footer: last updated 2025-11-22), 22 Nov 2025
- Query Planning (how SQLite uses indexes) — SQLite project (page footer: last updated 2026-03-22), 22 Mar 2026
- DynamoDB read consistency — Amazon Web Services (page undated; read 2026-10-24), 24 Oct 2026
- Dynamo: Amazon's Highly Available Key-value Store (SOSP 2007) — Amazon.com (DeCandia et al.; year only), 1 Jan 2007
- CAP Twelve Years Later: How the 'Rules' Have Changed (Brewer) — InfoQ (first appeared in Computer magazine), 30 May 2012
- Transactions, MongoDB manual — MongoDB (page undated; read 2026-10-24), 24 Oct 2026
- Redis persistence — Redis (page undated; read 2026-10-24), 24 Oct 2026
