Database Fundamentals for API Engineers: Indexes, Transactions, Isolation

A database is a program that stores data and lets you search and change it safely. A query is a request to the database, written in SQL (the standard language for talking to relational databases). A query plan is the list of steps the database picks to run a query.

You do not need to be a database expert. But if you own an API, you must be able to read a query plan (the steps the database takes to run a query) and think about requests that arrive at the same time. The examples use PostgreSQL, which is often used with Prisma. The ideas work in other databases too.

Words used in this lesson

• An index is an extra data structure that the database keeps beside a table, so it can find rows without reading the whole table. It speeds up reads and slows down writes.
• A transaction is a group of database changes that either all happen or none happen.
• An isolation level is a setting that says how much a transaction can see of other transactions that run at the same time.
• A row is one record in a table. A column is one field of every row. A primary key is the column (or columns) that identifies each row. A foreign key is a column that points to the primary key of another table (for example order_items.order_id points to orders.id).
• A constraint is a rule the database enforces, such as UNIQUE or CHECK (stock >= 0).
• Selectivity describes how few rows a condition matches. A condition that matches 1 row in a million is very selective.
• A lock is a mark that stops other transactions from changing the same row until the owner is done. (In PostgreSQL, plain reads do not wait for row locks.)
• A race condition is a bug where the result depends on which of two actions happens first.
• Atomic means "all in one step that cannot be cut in half". An atomic update checks and changes the value in a single statement.
• MVCC (multi-version concurrency control) is how PostgreSQL lets readers and writers work at once: each change creates a new version of the row, and old versions stay until a cleanup job removes them.
• Vacuum is the PostgreSQL cleanup job that removes old row versions. Autovacuum runs it automatically.
• An ORM (object-relational mapper) such as Prisma is a library that lets you use database tables as objects in your code.
• Throughput is how much work a system finishes per second.
• A replica is a read-only copy of the database that follows the main one.

Difference in one line: an index makes one read faster, a transaction makes a group of writes safe.

How an index works

Think of a book. To find one topic, you can read every page, or you can look in the index at the back. A database index works the same way.

Without an index, SELECT * FROM orders WHERE user_id = 42 makes the database read every row. This is called a sequential scan. The bigger the table, the slower the query.

A B-tree index is the default index type in PostgreSQL. It keeps the column values sorted in a tree shape. The database can then jump straight to the matching rows. The time grows very slowly as the table grows (this is called logarithmic time: a table 1,000 times bigger needs only a few more steps).

CREATE INDEX idx_orders_user_id ON orders (user_id);

A B-tree index helps with:

• equality (=)
• ranges (<, >, BETWEEN)
• ORDER BY on the indexed columns
• LIKE 'abc%' (the text starts with "abc"). This works only if the index uses the C collation or the text_pattern_ops operator class. Otherwise, create the index with text_pattern_ops.
• MIN and MAX
• joins on the indexed column

Costs

• Every INSERT, UPDATE (of indexed columns) and DELETE must also update each index. So more indexes means slower writes and more storage.
• Indexes also use memory when they are cached.
• An index that nobody uses is pure cost. Add indexes for real query patterns, not for every column.

Composite indexes and column order

A composite index (also called a multicolumn index) covers more than one column. It sorts by the first column. Inside that, it sorts by the second column, and so on. It can help queries that use a leftmost prefix of its columns. This means the first column, or the first two columns, and so on, but not a later column alone.

CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC);

Order the columns by how you query. Put the columns you compare with = first. Then put the column you sort by, then the column you filter by range. This rule of thumb is called "equality, sort, range" (ESR). It is a guide, not a law: always check the plan.

Other useful index types and features

• Unique indexes (made by UNIQUE constraints) make sure a value appears only once. The database does this check in one safe step (atomically).
• Partial indexes (CREATE INDEX ... WHERE status = 'pending') index only the rows you query. They are smaller and faster.
• Expression indexes (CREATE INDEX ON users (lower(email))) help queries that search on a computed value.
• Covering indexes (CREATE INDEX ... INCLUDE (total)) store extra columns. The database can then answer from the index alone, without reading the table. This is called an index-only scan.
• GIN indexes (Generalized Inverted Index) are for values that contain many parts: jsonb (JSON data), arrays and full-text search. GiST indexes are for ranges and geometry (shapes and map data). BRIN indexes are tiny indexes for huge tables where the values grow in step with the row order, such as a time column in a log table.

When an index does NOT help

1. Tiny tables. Scanning a few hundred rows is faster than using an index. The planner (the part of the database that picks the plan) knows this.
2. Low selectivity. Say an index is on is_active and 95% of rows are true. A query for true returns most of the table anyway.
3. A function or cast on the column. WHERE lower(email) = 'a@b.com' cannot use a plain index on email. Create an expression index instead. WHERE created_at::date = ... has the same problem. Use a range instead.
4. Leading wildcards. LIKE '%abc' (the wildcard % comes first) cannot use a B-tree. Use a trigram index (from the pg_trgm extension, which splits text into 3-letter pieces) or full-text search.
5. Implicit type conversions. The column type and the parameter type do not match, so the database converts the values.
6. The wrong column order in a composite index.
7. Stale statistics. The planner keeps facts about your data (how many rows, how values are spread). If these are old, it chooses badly. Run ANALYZE to refresh them. (Autovacuum usually does this for you.)
8. Fetching a large part of the table. A sequential scan is the right choice here.
9. OR across different columns. It can stop the database from using an index. Consider UNION (joining the results of two queries) or a bitmap scan.
10. OFFSET with huge values. The index finds the start, but the database still skips all earlier rows (Practical Design Problems, Lesson 03).

Reading EXPLAIN ANALYZE

EXPLAIN is a PostgreSQL command that shows the plan without running the query. EXPLAIN ANALYZE actually runs the query and adds real timings and row counts. (Be careful with INSERT, UPDATE and DELETE: they really change data. Wrap them in a transaction and ROLLBACK.) Add BUFFERS to see how much data was read.

EXPLAIN (ANALYZE, BUFFERS)SELECT * FROM orders WHERE user_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

The numbers below are an example. Yours will differ.

Limit  (cost=0.43..45.2 rows=20 width=96) (actual time=0.05..0.31 rows=20 loops=1)  ->  Index Scan using idx_orders_user_status_created on orders        (cost=0.43..210.8 rows=94 width=96) (actual time=0.05..0.29 rows=20 loops=1)        Index Cond: ((user_id = 42) AND (status = 'paid'::text))        Buffers: shared hit=24Planning Time: 0.2 msExecution Time: 0.4 ms

What to look for:

Key numbers:

• cost is the planner's estimate in arbitrary units (not milliseconds). actual time is real milliseconds (first row..all rows).
• rows (estimated) vs actual rows. A big difference means bad statistics and probably a bad plan.
• loops says how many times a step ran. actual time is per loop, so multiply it by loops.
• Rows Removed by Filter being large means you read many rows to keep a few. This is a good place for an index.
• Buffers: shared read means data came from disk. hit means it came from cache.

Workflow:

1. Find the slow query (from logs or pg_stat_statements, a PostgreSQL extension that records how long each query takes in total).
2. Run EXPLAIN (ANALYZE, BUFFERS) on realistic data.
3. Spot the slow scan or sort.
4. Add or change an index.
5. Run it again and compare.

Test on data that is as big as production. A plan on 100 rows tells you very little.

Transactions and ACID

A transaction is a group of statements that succeed or fail together. Think of moving money between two bank accounts. Taking money out and putting money in must both happen, or neither.

BEGIN;UPDATE accounts SET balance = balance - 500 WHERE id = 1;UPDATE accounts SET balance = balance + 500 WHERE id = 2;COMMIT;      -- or ROLLBACK; to undo everything

ACID is the set of four guarantees a transaction gives you (the name is the first letters of the four words):

In Prisma:

await prisma.$transaction(async (tx) => {  const from = await tx.account.update({ where: { id: 1 }, data: { balance: { decrement: 500 } } });  if (from.balance < 0) throw new Error("Insufficient funds"); // throwing rolls back everything  await tx.account.update({ where: { id: 2 }, data: { balance: { increment: 500 } } });});

In Prisma, an interactive transaction like this stops with an error after 5 seconds by default (you can change timeout). Keep transactions short. They hold locks and one connection from the pool. Never call slow outside services (payment providers, email) inside a transaction.

Isolation levels and what can go wrong

When transactions run at the same time, some strange things can happen. These are called anomalies.

PostgreSQL's levels:

PostgreSQL treats Read Uncommitted the same as Read Committed, so dirty reads never happen. A higher isolation level lowers throughput (how much work you finish per second). It also needs retry logic.

await prisma.$transaction(async (tx) => { /* ... */ }, { isolationLevel: "Serializable" });

Scenario: two users buy the last item at the same moment

Tx A: SELECT stock → 1          Tx B: SELECT stock → 1Tx A: stock = 1 - 1 → writes 0  Tx B: stock = 1 - 1 → writes 0Both orders succeed. Stock is 0 but two items were sold. ← overselling

The "read, check, then write" pattern is a race condition. This means the result depends on which request is faster. Here are ways to prevent it, starting with the simplest:

1. Atomic conditional update. Let the database do the check and the change in one step:

UPDATE products SET stock = stock - 1 WHERE id = $1 AND stock >= 1;-- check the affected row count: 1 = reserved, 0 = out of stock

const { count } = await prisma.product.updateMany({ where: { id, stock: { gte: 1 } }, data: { stock: { decrement: 1 } } });if (count === 0) throw new ConflictError("Out of stock", "OUT_OF_STOCK");

2. Pessimistic locking. This means locking first, because you expect a clash. SELECT ... FOR UPDATE locks the row until the transaction ends. The second buyer waits, then sees the new stock.
3. Optimistic locking. This means not locking, because you expect clashes to be rare, and detecting them at write time. Add a version column. Run UPDATE ... WHERE id = $1 AND version = $2. If no row matched, someone else changed it first, so retry or fail.
4. A database constraint. CHECK (stock >= 0) is a last safety net.
5. Serializable isolation with retries. Use this when the logic is too complex for one statement.
6. Reservation tables. Hold the stock for a short checkout time, then release it when the time runs out. Real shops often use this.

Also use unique constraints for "only one" rules. Examples are one active subscription per user, or one vote per user per poll. Do not check first and insert later.

Deadlocks

A deadlock happens when two transactions each hold a lock that the other one needs. Both wait forever. The database notices this and stops one of them. To reduce deadlocks:

• Lock rows in a consistent order.
• Keep transactions short.
• Retry the transaction that was stopped.

Connections matter too

A database connection is an open channel between your app and the database. Each one is expensive. In PostgreSQL, each one is a separate process on the server (a process is a running program). The server only supports a limited number of them (100 by default).

APIs use a connection pool. A pool is a small group of connections that requests share. If requests keep connections too long (long transactions, slow queries), the pool runs out. Then everything waits. This is why serverless deployments need a pooler (Lesson 08).

Common mistakes

1. Indexing every column, or none besides the primary key.
2. Forgetting indexes on foreign keys in PostgreSQL.
3. Functions on indexed columns in WHERE (lower(email), date(created_at)).
4. Read-check-write races. Use atomic updates, locks or constraints instead.
5. Long transactions that include network calls.
6. Trusting EXPLAIN on tiny dev data.
7. SELECT * and fetching more than you need. It also blocks index-only scans.
8. Thinking the default isolation level protects you from everything.

Interview questions

Key takeaways

• Indexes trade slower writes for faster reads. Design them from real queries, mind the column order, and index foreign keys yourself in PostgreSQL.
• Read EXPLAIN (ANALYZE, BUFFERS): scan type, estimated vs actual rows, loops, sorts.
• ACID transactions make multi-step changes all-or-nothing. Keep them short.
• Know the isolation levels and the anomalies. Prefer atomic updates, locks and constraints over read-check-write.
• Fix races in the database, not with if statements in application code.

What's next

Lesson 08: Prisma in Production. Now we connect this to the ORM you actually use: N+1 queries, joins, batching and connection pools.