Indexes & Transactions (Intermediate)

Theory

1) Indexes: what they actually optimize

An index is a data structure that helps the database avoid scanning too much data.

Common index use-cases:

• WHERE filters on indexed columns
• joins on foreign keys
• ORDER BY / GROUP BY when indexes match the sort/group pattern

Index types (conceptual):

• B-Tree: best default for range queries and equality
• Hash: equality lookups
• GIN/GiST: specialized for arrays/text search (DB-dependent)

2) Learn to read query plans (without panic)

Even if you don’t master the whole optimizer, look for these signals:

• Is it using an index scan vs full scan?
• How many rows does it estimate?
• Are there large nested loop joins that might explode?
• Are filters applied early or late?

3) Transactions: ACID in practice

• Atomicity: all-or-nothing changes
• Consistency: invariants enforced by constraints
• Isolation: concurrent transactions shouldn’t corrupt each other
• Durability: committed changes survive failures

4) Isolation levels (practical meaning)

Higher isolation usually means fewer anomalies but can reduce concurrency.

Common anomalies:

• Dirty read: read uncommitted data
• Non-repeatable read: same query reads different values later in same txn
• Phantom read: new rows appear in a repeated predicate query

5) Deadlocks and how to avoid them

Deadlocks happen when two transactions lock resources in opposite order.

Mitigations:

• access tables/rows in a consistent order
• keep transactions short (don’t do long computations inside)
• use timeouts/backoff for retries

Code Example (SQL: transaction + upsert pattern)

This pattern keeps updates atomic and safe:

BEGIN;

-- Example: create or update an account balance atomically
INSERT INTO accounts(account_id, balance)
VALUES ($1, $2)
ON CONFLICT (account_id)
DO UPDATE SET balance = accounts.balance + EXCLUDED.balance;

-- Optional: verify invariants
-- e.g. ensure balance doesn't go negative
SELECT balance FROM accounts WHERE account_id = $1;

COMMIT;

Practice

1. Create an index candidate:

   • Pick one query you run frequently
   • Identify the exact columns used in WHERE and joins
   • Add an index and compare performance expectations (mentally or via EXPLAIN)

2. Design a small transactional operation:

   • “Place an order”: insert order row + decrement inventory (two changes)
   • Wrap them in a single transaction boundary

Common pitfalls

• Indexing columns without matching query patterns (or without checking plans)
• Long-running transactions that hold locks too long
• Retrying blindly on deadlocks without safe idempotency