Database performance and data integrity depend on proper use of transactions, indexes, and query optimization. Understanding EXPLAIN/EXPLAIN ANALYZE output is the key skill for diagnosing slow queries.
| Statement / Feature | Syntax | Description |
|---|---|---|
| BEGIN / START TRANSACTION | BEGIN; / START TRANSACTION; | Start a transaction block |
| COMMIT | COMMIT; | Save all changes in transaction |
| ROLLBACK | ROLLBACK; | Undo all changes in transaction |
| SAVEPOINT | SAVEPOINT name; | Create a named rollback point |
| ROLLBACK TO | ROLLBACK TO SAVEPOINT name; | Rollback to savepoint only |
| RELEASE SAVEPOINT | RELEASE SAVEPOINT name; | Release a savepoint |
| ACID | Atomicity Consistency Isolation Durability | Core transaction guarantees |
| READ COMMITTED | SET TRANSACTION ISOLATION LEVEL READ COMMITTED | Default in most DBs — no dirty reads |
| REPEATABLE READ | ISOLATION LEVEL REPEATABLE READ | No phantom reads in same transaction |
| SERIALIZABLE | ISOLATION LEVEL SERIALIZABLE | Strictest — transactions appear sequential |
| Dirty read | Reading uncommitted data from another transaction | Prevented by READ COMMITTED+ |
| Phantom read | New rows appear in re-read query | Prevented by SERIALIZABLE |
| Deadlock | Two transactions each wait for other to release lock | DB detects and kills one transaction |
| SELECT FOR UPDATE | SELECT ... FOR UPDATE | Lock selected rows for update |
| SELECT FOR SHARE | SELECT ... FOR SHARE | Lock rows — allow other reads, not writes |
| EXPLAIN | EXPLAIN SELECT ... | Show query plan without executing |
| EXPLAIN ANALYZE | EXPLAIN ANALYZE SELECT ... | Execute + show actual timing |
| Seq Scan | Sequential scan — reads all rows | Slow for large tables without index |
| Index Scan | Uses index to find rows | Fast for selective queries |
| Index Only Scan | Answer from index alone — no table access | Fastest — covering index |
| Hash Join | Build hash table of smaller table, probe with larger | Good for large unsorted joins |
| Nested Loop | For each row in outer, scan inner | Good when inner is small or indexed |
| Merge Join | Sort both sides then merge | Good when both sides already sorted |
| Index selectivity | % of rows returned by index scan | Low selectivity = many rows = index may not help |
| Covering index | Index contains all columns needed by query | Enables Index Only Scan |
| Partial index | CREATE INDEX idx ON t(col) WHERE active=TRUE | Index only subset of rows — smaller, faster |
| Query hints | USE INDEX (idx) / /*+ INDEX(t idx) */ | Force specific index (MySQL / Oracle) |
-- Transactions & Performance
-- ── ACID Transactions ────────────────────────────────────────
-- Transfer money between accounts (classic example)
BEGIN;
-- Lock both accounts to prevent concurrent modification
SELECT id, balance FROM accounts WHERE id IN (1001, 1002) FOR UPDATE;
-- Debit source
UPDATE accounts SET balance = balance - 500.00
WHERE id = 1001 AND balance >= 500.00;
-- Abort if insufficient funds
DO $$
BEGIN
IF NOT FOUND THEN
RAISE EXCEPTION 'Insufficient funds';
END IF;
END $$;
-- Credit destination
UPDATE accounts SET balance = balance + 500.00 WHERE id = 1002;
-- Log transaction
INSERT INTO transactions (from_id, to_id, amount, tx_date)
VALUES (1001, 1002, 500.00, NOW());
COMMIT; -- or ROLLBACK on error
-- ── SAVEPOINT ────────────────────────────────────────────────
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (1, 250.00);
SAVEPOINT order_placed;
INSERT INTO order_items (order_id, product, qty, price) VALUES
(LASTVAL(), 'Python Course', 1, 150.00);
SAVEPOINT items_added;
-- If shipping fails, roll back only shipping, keep order
INSERT INTO shipments (order_id, address) VALUES (LASTVAL(), '123 Main St');
-- Suppose this fails:
ROLLBACK TO SAVEPOINT items_added; -- keep order + items, undo shipment
COMMIT;
-- ── EXPLAIN ANALYZE ──────────────────────────────────────────
-- PostgreSQL
EXPLAIN ANALYZE
SELECT e.name, d.dept_name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 80000
ORDER BY e.salary DESC;
-- Look for: Seq Scan (bad on large tables), missing indexes
-- Add index if needed:
CREATE INDEX idx_emp_salary ON employees(salary) WHERE salary > 50000;
CREATE INDEX idx_emp_dept ON employees(dept_id);
-- ── Query optimization patterns ───────────────────────────────
-- BAD: function on indexed column defeats index
SELECT * FROM employees WHERE YEAR(hire_date) = 2022; -- MySQL, no index use
-- GOOD: range condition uses index
SELECT * FROM employees
WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';
-- BAD: leading wildcard
SELECT * FROM employees WHERE email LIKE '%@gmail.com'; -- full scan
-- GOOD: trailing wildcard only
SELECT * FROM employees WHERE email LIKE 'alice%'; -- uses index
-- BAD: OR can prevent index use
SELECT * FROM employees WHERE salary = 95000 OR dept_id = 1;
-- GOOD: UNION of indexed queries
SELECT * FROM employees WHERE salary = 95000
UNION
SELECT * FROM employees WHERE dept_id = 1;
-- Covering index for frequent query
CREATE INDEX idx_cov ON employees(dept_id, salary, name);
-- This query hits index only (no table access):
SELECT name, salary FROM employees WHERE dept_id = 1 ORDER BY salary;
-- ── Batch operations ─────────────────────────────────────────
-- BAD: 10,000 individual inserts
-- GOOD: bulk insert in one statement
INSERT INTO log_entries (user_id, action, created_at)
SELECT user_id, 'daily_check', NOW()
FROM users WHERE active = TRUE; -- insert from select — very fast
-- Batch update
UPDATE employees
SET salary = salary * 1.05
FROM (SELECT id FROM employees WHERE dept_id = 1 LIMIT 1000) sub
WHERE employees.id = sub.id;