An index is a separate data structure, usually a B-tree, that lets a database find matching rows without scanning the whole table, turning slow full scans into fast lookups at the cost of extra storage and slower writes. A transaction groups operations so they succeed or fail together, and ACID guarantees (atomicity, consistency, isolation, durability) keep data correct even when many users change it at once or the server crashes.

Why do indexes matter so much?

Without an index, finding a learner by email in a table of ten million rows means checking every row: a full table scan. With a B-tree index on email, the database walks a shallow tree and finds the row in a handful of steps. Most slow-application complaints trace back to a missing or unused index, which makes indexing the single most valuable database performance skill.

Rows examined to find one row among 10 million
Full table scan (worst case)10,000,000
B-tree index lookup≈4 page reads

A B-tree with a few hundred keys per page needs only about 3–4 levels for 10 million rows (log base ~300 of 10⁷ ≈ 3); exact numbers depend on the database.

How does a B-tree index work?

A B-tree keeps keys sorted in a balanced tree of pages. Each page holds many keys and pointers, so the tree is wide and shallow. Searching starts at the root and follows pointers down to a leaf that points to the row (or, in some databases, contains it). Because keys are sorted, B-trees also speed up range queries (WHERE created_at BETWEEN …), sorting (ORDER BY) and prefix searches. Inserts and deletes keep the tree balanced automatically, which is why lookups stay fast as data grows. See Big O notation for why O(log n) matters.

Which kinds of indexes exist?

IndexUse
Primary key / uniqueIdentify rows; enforce no duplicates (emails, phone numbers)
Composite (multi-column)Queries filtering on several columns together
CoveringIndex contains all needed columns, so the table isn't read
PartialIndex only some rows (for example, active users)
HashExact-match lookups
Full-text (GIN)Searching words in text; JSONB containment in PostgreSQL
Vector (HNSW, IVF)Similarity search on embeddings for AI applications

How should you design composite indexes?

Column order matters. An index on (city, created_at) helps queries filtering by city, or by city and a date range, but not queries filtering only by date, because the index is sorted by city first (the "leftmost prefix" rule). Put columns used for equality filters first and range or sort columns after. Build indexes from your actual slowest and most frequent queries rather than guessing.

How do you know whether an index is used?

Run EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to see the query plan: sequential scan or index scan, estimated versus actual rows, and join methods. Indexes are skipped when queries wrap the column in a function (WHERE LOWER(email) = …, unless you create an expression index), compare mismatched types, use a leading wildcard (LIKE '%kumar'), or when the condition matches so many rows that a scan is cheaper. Keep table statistics up to date so the planner estimates well.

What does ACID mean, concretely?

PropertyGuaranteeExample
AtomicityAll operations happen or none doMarking a payment paid and activating the membership both succeed, or both roll back
ConsistencyConstraints always holdNo enrolment points to a non-existent learner
IsolationConcurrent transactions don't corrupt each otherTwo people can't both book the last seat
DurabilityCommitted data survives crashesA confirmed payment isn't lost after a power cut

What are isolation levels?

Full isolation is expensive, so databases offer levels that trade protection for concurrency:

  • Read committed (the default in PostgreSQL): you never see uncommitted data, but repeated reads in one transaction may see changes others committed.
  • Repeatable read: rows you've read don't change under you during the transaction.
  • Serializable: the result is as if transactions ran one at a time; the database may abort transactions that conflict, which the application must retry.

Anomalies to know by name: dirty reads, non-repeatable reads, phantom reads and lost updates. Choose the level per operation: most reads are fine at read committed; financial invariants may need serializable or explicit locking.

How do you avoid race conditions in practice?

Classic bug: two requests read a seat count of 1, both see a free seat and both book it. Fixes include an atomic conditional update (UPDATE … SET seats = seats − 1 WHERE id = … AND seats > 0, then check the affected row count), SELECT … FOR UPDATE to lock the row, unique constraints that make duplicates impossible, or serializable transactions with retries. Payment systems commonly use an atomic update on status (only change "created" to "paid" once) so duplicate confirmations can't issue two receipts.

What causes deadlocks, and how do you handle them?

Transaction A locks row 1 and waits for row 2; transaction B locks row 2 and waits for row 1. The database detects the cycle and aborts one. Reduce deadlocks by touching rows in a consistent order, keeping transactions short, and avoiding user interaction inside a transaction; and always handle the error by retrying the aborted transaction.

What indexing mistakes are most common?

  • No index on foreign-key columns used in joins.
  • Indexing every column "just in case", slowing writes.
  • Composite indexes in the wrong column order.
  • Long-running transactions that hold locks and block others.
  • Ignoring the query plan and guessing.

What does a slow-query investigation look like?

A dashboard listing a learner's recent submissions takes four seconds. You run EXPLAIN ANALYZE on its query and see a sequential scan over three million submission rows, filtered by learner ID and sorted by date. Adding a composite index on (learner_id, submitted_at DESC) turns it into an index scan that reads only that learner's latest rows: the query drops to a few milliseconds. You then check that writes to the submissions table are still fast enough, because every new submission now updates one more index. This sequence (measure, read the plan, add the right index, check the cost) solves the large majority of database performance problems.

How do transactions look in application code?

In most languages you begin a transaction, run several statements, and commit; if anything fails, you roll back. Frameworks and ORMs wrap this in helpers so that an exception automatically rolls back. Keep transactions short, never wait for user input or a slow external API inside one, and design operations to be safe to retry, because a transaction aborted by a deadlock or a serialization conflict should simply run again. When an external call is part of the workflow, such as sending a receipt email after a payment, commit the database change first and then send the email (or queue it), so a slow email server can't hold database locks.

How should you practise?

Load a table with a million rows in PostgreSQL, time a lookup with and without an index, read both query plans, then simulate two concurrent bookings and fix the race with an atomic update. Indexing, query optimisation and transactions are part of Program Zero's five-week database course; see also SQL interview questions and MongoDB vs PostgreSQL.