Part 4: Indexing, Transactions & DML

1. Physical Storage & Index Design

An index is a dedicated, separate storage structure designed to reduce disk I/O. Without it, the engine must perform a full table scan, reading every block from disk.

Clustered vs. Non-Clustered Indexes

• Clustered Index: Dictates the literal physical storage order of the rows on disk. Because rows can only be sorted in one way, you are allowed exactly one clustered index per table (typically bound to the Primary Key).
• Non-Clustered Index: A separate look-up structure containing ordered keys and physical pointers that route back to the raw row pages. You can build multiple non-clustered indexes per table.

Advanced Indexing Strategies

• Composite Index & The Left-Prefix Rule: An index built on multiple columns, like idx_user_org (user_id, org_id). The engine can use this index for WHERE user_id = X or WHERE user_id = X AND org_id = Y. However, it cannot use it for WHERE org_id = Y because the left-most column must be included in the search criteria.
• Covering Index: A non-clustered index that includes all columns requested by a query. When this happens, the engine reads data directly from the index tree and skips the secondary lookup step to read the actual row page, speeding up execution.
• B-Tree vs. Bitmap Indexes:
• B-Tree: The standard choice for high-cardinality data (highly unique columns like email or uuid).
• Bitmap: Perfect for low-cardinality data (highly repetitive categories like status_code or gender). It uses bits to track matches, though it can slow down under high-concurrency write operations.

---

2. Performance Diagnostic Engine

EXPLAIN vs. EXPLAIN ANALYZE

• EXPLAIN: Shows the database optimizer’s planned execution path and estimated costs without actually running the query.
• EXPLAIN ANALYZE: Runs the query completely, measuring real-world wall-clock times and actual row counts across execution nodes.

Diagnostic Targets: Index Seek vs. Index Scan

• Index Seek: The engine navigates a clear hierarchical path down the B-Tree index to pull specific matching records. This is highly efficient.
• Index Scan: The engine scans the entire index structure from start to finish. While sometimes faster than a full table scan, it often points to a missing condition or optimization opportunity.

SARGable Queries vs. Anti-Patterns

To make a query SARGable (Search Argument Able), you must avoid applying functions or modifications to indexed columns.

-- UNOPTIMIZED (Non-SARGable): Ignores the index, forces a full scan
WHERE YEAR(created_at) = 2026;

-- OPTIMIZED (SARGable): Leverages the index cleanly
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

---

3. Transaction Mechanics & Concurrency

A transaction groups multiple statements into a single unit governed by the ACID model: Atomicity (all-or-nothing), Consistency (valid schema transitions), Isolation (safe concurrency), and Durability (crash-resilient storage).

Isolation Levels & Concurrency Anomalies

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads
Read Uncommitted	Allowed	Allowed	Allowed
Read Committed	Prevented	Allowed	Allowed
Repeatable Read	Prevented	Prevented	Allowed*
Serializable	Prevented	Prevented	Prevented

*Note: In modern engines like PostgreSQL, the Repeatable Read isolation level prevents Phantom Reads as well using advanced multi-version engines.

Deadlocks vs. Livelocks

• Deadlock: Transaction A holds Lock 1 and waits for Lock 2. Transaction B holds Lock 2 and waits for Lock 1. Both processes are permanently stuck until the database engine terminates one of them.
• Livelock: Two processes continually alter their internal states in response to each other without doing any actual work, remaining active but making no progress.

---

4. Systems Architecture: Scaling & Programmability

Data Warehouse Models: OLTP vs. OLAP

• OLTP (Transactional): Handles thousands of fast, concurrent operations (INSERT, UPDATE). It relies on highly normalized schemas (up to 3NF) to eliminate redundant data and speed up writes.
• OLAP (Analytical): Processes large-scale aggregation queries across massive historical datasets. It utilizes denormalized Star or Snowflake structures to prioritize fast read times over clean write operations.

Partitioning vs. Sharding

• Partitioning: Breaks a massive table into smaller logical sub-tables within the same database instance based on a key range (e.g., partitioning by year).
• Sharding: Horizontally distributes table rows across completely separate database servers and hardware nodes, scaling out storage and compute resources globally.

Programmability Trade-offs

Component	Architecture & Lifecycle	Performance & Overhead
Standard View	A virtual reference query; executes its definition against base tables in real time.	Low storage cost; carries high runtime compute overhead on complex logic.
Materialized View	Physically computes and caches the result set directly onto physical disk blocks.	Blazing fast reads; requires regular maintenance via REFRESH commands.
Stored Procedure	A compiled routine that can modify database states and can manage transactions (COMMIT).	Heavy database layer logic; reduces network chatter by grouping statements.
User Function (UDF)	A computational block that must return a value and cannot modify transaction states.	Cleanly embeds inside SELECT filters; can degrade performance if run row-by-row.

---

5. Data Manipulation (DML): INSERT / UPDATE / DELETE

The revision guide so far is SELECT-heavy — but placement rounds test writing data too.

-- INSERT: single row / multiple rows / from a query
INSERT INTO users (name, email) VALUES ('A', 'a@x.com');
INSERT INTO users (name, email) VALUES ('B','b@x.com'), ('C','c@x.com');
INSERT INTO archive SELECT * FROM users WHERE created < '2023-01-01';

-- UPDATE: with WHERE (never update everything by accident)
UPDATE users SET status = 'active' WHERE last_login > '2024-01-01';

-- DELETE: rows only (filtered)
DELETE FROM users WHERE status = 'inactive';

UPDATE with a JOIN — modify rows based on another table (e.g., promote gold customers):

-- PostgreSQL / SQL Server
UPDATE orders o SET o.status = 'priority'
FROM customers c WHERE o.customer_id = c.id AND c.tier = 'gold';
-- MySQL
UPDATE orders o JOIN customers c ON o.customer_id = c.id
SET o.status = 'priority' WHERE c.tier = 'gold';

UPSERT — update if exists, insert if not (idempotent):

-- PostgreSQL
INSERT INTO users (id, name, email) VALUES (1, 'A', 'a@x.com')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
-- MySQL
INSERT INTO users (id, name, email) VALUES (1, 'A', 'a@x.com')
ON DUPLICATE KEY UPDATE name = VALUES(name);

• Hard vs soft delete: hard delete removes rows permanently; soft delete sets a flag (e.g. is_deleted = 1) for audit/recovery.
• Identity / auto-increment / sequence: DBs auto-generate keys — AUTO_INCREMENT (MySQL), IDENTITY (SQL Server), SERIAL/sequence (Postgres).

6. DELETE vs TRUNCATE vs DROP (Recap)

	DELETE	TRUNCATE	DROP
Category	DML	DDL	DDL
Scope	Rows (can WHERE)	All rows	Whole table
Rollback	Yes (logged per-row)	No (in most DBs)	No
Triggers	Fire	Don’t fire	Don’t fire
Speed	Slow (per-row)	Fast	Instant
Identity	Keeps counter	Resets counter	Removes table

Pick: DELETE when you need to filter and roll back; TRUNCATE to quickly clear a table you’ll keep; DROP to remove the table structure entirely.

7. Optimistic vs Pessimistic Locking

• Pessimistic locking: lock the row before reading (SELECT ... FOR UPDATE) so nobody else touches it until you commit. Safer, but blocks others and can deadlock.
• Optimistic locking: don’t lock during the transaction; at commit, check a version column (or timestamp) hasn’t changed since you read it. If it changed → abort and retry. Higher concurrency, best for read-heavy systems with rare conflicts.

-- optimistic: bump a version on update, fail if it moved
UPDATE accounts SET balance = ?, version = version + 1
WHERE id = ? AND version = ?;   -- 0 rows updated → someone else wrote first

Rule of thumb: pessimistic for high-contention writes; optimistic for read-mostly workloads where conflicts are rare (e.g., web apps using a version column).