Design, tune, operate and secure production databases: normalisation, indexing, backups, replication, access control, monitoring and safe migrations.

Database Administration

## Why it matters

The single most common cause of a slow database is a query that reads the whole table to find a handful of rows. An index turns that full scan into a direct lookup, often making a query hundreds of times faster. But every index also slows down inserts and updates and takes disk space, so a DBA's job is not "add indexes" but "add the **right** indexes and remove the useless ones".

## The core idea: a sorted copy with pointers

Most indexes are **B-trees**: a sorted, balanced structure of the indexed column values, each pointing to the table row. Because it is sorted, the database can jump straight to `customer_id = 42` and walk forward, instead of checking every row. Think of the index at the back of a textbook.

An index helps when a query:

- filters with `=`, `IN`, `<`, `>`, `BETWEEN` or `LIKE 'abc%'` on the indexed columns,
- joins on them,
- sorts by them (`ORDER BY` can read the index in order and skip a sort step).

An index rarely helps when the filter matches a large share of the table (low **selectivity**, such as `status = 'active'` when 95% of rows are active) or when the column is wrapped in a function (`WHERE lower(email) = ...`) unless you create an index on that expression.

## Composite indexes and the leftmost prefix

A composite index covers several columns **in order**: `(customer_id, status, ordered_at)` is sorted by customer, then status within each customer, then date. It can serve:

- `WHERE customer_id = ?`
- `WHERE customer_id = ? AND status = ?`
- `WHERE customer_id = ? AND status = ? ORDER BY ordered_at`

It cannot efficiently serve `WHERE status = ?` alone, because status is not the first column. Rule of thumb: put **equality** columns first, then the **range or sort** column last.

A **covering index** contains every column a query needs, so the database never touches the table. In PostgreSQL you can add extra columns with `INCLUDE`; in SQLite you simply add them to the end of the index.

## Worked example: reading the plan

SQLite shows its plan with `EXPLAIN QUERY PLAN`:

```sql
EXPLAIN QUERY PLAN
SELECT id, ordered_at, total_cents
FROM orders
WHERE customer_id = 3 AND status = 'shipped'
ORDER BY ordered_at DESC;
```

Without an index, the detail column reads something like:

```text
SCAN orders
USE TEMP B-TREE FOR ORDER BY
```

`SCAN` means every row is read; the temp B-tree is an extra sort. Now add a composite index and ask again:

```sql
CREATE INDEX idx_orders_customer_status_date
  ON orders (customer_id, status, ordered_at);
```

```text
SEARCH orders USING INDEX idx_orders_customer_status_date (customer_id=? AND status=?)
```

`SEARCH ... USING INDEX` is a direct lookup, and the sort step has gone because the index is already in date order (SQLite reads it backwards for `DESC`). Exact wording varies a little between SQLite versions.

In PostgreSQL the equivalent is `EXPLAIN (ANALYZE, BUFFERS) SELECT ...`, which runs the query and shows real timings. Look for `Seq Scan` on big tables versus `Index Scan` or `Index Only Scan`. MySQL uses `EXPLAIN` and `EXPLAIN ANALYZE` (8.0.18+).

You can also inspect an index's columns. In SQLite:

```sql
SELECT seqno, name FROM pragma_index_info('idx_orders_customer_status_date');
```

## Other index types worth knowing

- **Unique indexes** enforce uniqueness (created automatically for `UNIQUE` and primary keys).
- **Partial indexes** index only some rows: `CREATE INDEX ... ON orders (ordered_at) WHERE status = 'pending';` Small and fast when queries always use that filter (PostgreSQL and SQLite both support them).
- **Expression indexes:** `CREATE INDEX ... ON users (lower(email));`
- PostgreSQL also has **GIN** (JSONB, arrays, full-text), **GiST** and **BRIN** (very large, naturally ordered tables such as time series).

## The cost side

Every insert, update or delete must also update each index on the table. Ten indexes on a busy table can make writes several times slower. In PostgreSQL, `pg_stat_user_indexes` shows `idx_scan` per index; an index with zero scans after weeks of normal traffic is a candidate to drop (check replicas and monthly jobs first). In production PostgreSQL, build indexes with `CREATE INDEX CONCURRENTLY` so writes are not blocked.

## Common mistakes

- Indexing every column "just in case".
- Composite indexes in the wrong order (range column first).
- Duplicate indexes: `(a)` is redundant when `(a, b)` exists.
- Trusting a tiny test table: on 50 rows the planner may scan anyway, because a scan is cheaper. Test with realistic volumes.
- Forgetting to refresh statistics (`ANALYZE`) after a big load, so the planner misjudges row counts.

## Quick recap

- A B-tree index turns scans into lookups for selective filters, joins and sorts.
- Composite indexes follow the leftmost-prefix rule: equality columns first, range or sort column last.
- Read plans before and after (`EXPLAIN QUERY PLAN`, `EXPLAIN ANALYZE`) and remember the write cost.

## Try it

In the **Do** tab, design the composite index for a customer order-history query and compare the plans.


Design single-column and composite indexes, read query plans before and after, and weigh the write cost.

Indexing strategy

Database Design 39 - Indexes (Clustered, Nonclustered, Composite Index)

SQL indexing best practices | How to make your database FASTER!

## Why it matters

Indexes fix many slow queries, but not all. Some queries are written so the database **cannot** use an index, or so that it repeats work for every row. Query tuning is the craft of finding the expensive part of a query and rewriting it to return the same answer with less work. As a DBA you will often do this together with the developer who owns the code.

## A repeatable process

1. **Find the queries that matter.** Sort by total time, not just the slowest single run: a 20 ms query called a million times a day costs more than a 5 s report run once. In PostgreSQL, `pg_stat_statements` gives you this list (covered in the Monitoring lesson); MySQL has the slow query log and the Performance Schema.
2. **Read the plan.** `EXPLAIN (ANALYZE, BUFFERS)` in PostgreSQL, `EXPLAIN ANALYZE` in MySQL, `EXPLAIN QUERY PLAN` in SQLite. Look for full scans on large tables, sorts that spill to disk, nested loops over many rows, and big gaps between **estimated** and **actual** row counts.
3. **Form one hypothesis and change one thing.** An index, a rewrite, fresh statistics.
4. **Measure again with realistic data** and confirm the results are identical.

## Rewrites that often help

**Make filters sargable.** A predicate is *sargable* (Search ARGument ABLE) when the database can use an index for it. Wrapping the column in a function usually breaks that:

```sql
-- Not sargable: the function runs on every row's ordered_at
WHERE strftime('%Y', ordered_at) = '2025'

-- Sargable: a plain range on the column
WHERE ordered_at >= '2025-01-01' AND ordered_at < '2026-01-01'
```

The same applies to `WHERE price * 1.2 > 100` (move the math to the constant side) and `WHERE lower(email) = ?` (use an expression index or store a normalised column).

**Replace per-row subqueries with joins and grouping.** A correlated subquery in the `SELECT` list can run once per outer row:

```sql
SELECT c.name,
       (SELECT COUNT(*) FROM orders o
         WHERE o.customer_id = c.id AND o.status = 'shipped') AS shipped
FROM customers c;
```

Planners are good at some of these, but a join with `GROUP BY` states the intent once and is easy to index:

```sql
SELECT c.name, COUNT(*) AS shipped
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped'
GROUP BY c.id, c.name;
```

Careful: the inner join drops customers with zero matches. Use `LEFT JOIN` plus `COUNT(o.id)` if you need them, and filter in the `ON` clause, not in `WHERE`.

**Return less.** `SELECT *` drags every column over the network and prevents index-only scans. Select the columns you need, and aggregate in the database rather than in application loops.

**Kill N+1 queries.** An application that runs one query for a list and then one more query per item ("N+1") makes hundreds of round trips. Fetch the related rows in one query with a join or `WHERE id IN (...)`.

**Paginate with keys, not big offsets.** `LIMIT 50 OFFSET 100000` still reads and discards 100,000 rows. Keyset pagination (`WHERE id > :last_seen_id ORDER BY id LIMIT 50`) stays fast on every page.

**Keep statistics fresh.** The planner chooses plans from table statistics. After a bulk load, run `ANALYZE` (PostgreSQL, SQLite) or `ANALYZE TABLE` (MySQL); stale statistics are a classic cause of a sudden bad plan.

## Worked example

A dashboard query counts each customer's 2025 shipped orders:

```sql
SELECT c.name,
       (SELECT COUNT(*) FROM orders o
         WHERE o.customer_id = c.id AND o.status = 'shipped'
           AND strftime('%Y', o.ordered_at) = '2025') AS orders_2025
FROM customers c
WHERE (SELECT COUNT(*) FROM orders o
        WHERE o.customer_id = c.id AND o.status = 'shipped'
          AND strftime('%Y', o.ordered_at) = '2025') > 0
ORDER BY orders_2025 DESC, c.name;
```

Two problems: the subquery appears twice (and runs per customer), and the year filter is not sargable. The rewrite:

```sql
SELECT c.name, COUNT(*) AS orders_2025
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped'
  AND o.ordered_at >= '2025-01-01' AND o.ordered_at < '2026-01-01'
GROUP BY c.id, c.name
ORDER BY orders_2025 DESC, c.name;
```

The inner join naturally drops customers with no 2025 orders, replacing the `> 0` filter. Same rows, one pass, and an index on `(status, ordered_at)` or `(customer_id, status, ordered_at)` can now be used.

## Common mistakes

- Tuning the query that *feels* slow instead of the one that costs the most in total.
- Changing several things at once, so you cannot tell what helped.
- Testing on a laptop with 100 rows; plans change with data size.
- Forgetting to compare results: a "faster" query that returns different rows is a bug.
- Adding query hints or forcing plans before fixing statistics and indexes.

## Quick recap

- Measure, read the plan, change one thing, measure again.
- Keep filters sargable, prefer joins and grouping to repeated per-row subqueries, select only what you need.
- Watch for N+1 patterns, deep offsets and stale statistics.

## Try it

In the **Do** tab, rewrite the dashboard query above so it returns exactly the same rows with sargable filters and a single join.


Find why a query is slow, then rewrite it with sargable filters, joins instead of per-row subqueries and leaner result sets.

Query tuning

Secret To Optimizing SQL Queries - Understand The SQL Execution Order

## Why it matters

A database serves many users at once. Two people buy the last ticket, two jobs update the same account balance, a report reads a table while it is being changed. Transactions and locks decide what each of them sees and who waits. When they are misunderstood you get lost updates, impossible report totals, lock pile-ups and deadlock errors, which are some of the hardest production incidents to explain.

## Transactions and ACID

A **transaction** groups statements into one unit:

```sql
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK; to undo both
```

**ACID** describes the guarantees:

- **Atomicity:** all statements take effect, or none do.
- **Consistency:** constraints hold before and after.
- **Isolation:** concurrent transactions do not see each other's half-finished work (to a configurable degree).
- **Durability:** once committed, the change survives a crash (it has been written to the write-ahead log).

## Isolation levels and anomalies

Full isolation is expensive, so SQL defines levels that allow some **anomalies**:

- **Dirty read:** you see another transaction's uncommitted change, which may be rolled back.
- **Non-repeatable read:** you read a row twice in one transaction and get different values, because someone committed in between.
- **Phantom read:** you run the same `WHERE` twice and new rows appear.
- **Lost update / write skew:** two transactions read, then both write based on what they read, and one effect is lost or a rule across rows is broken.

| Level | Dirty read | Non-repeatable read | Phantom |
|---|---|---|---|
| Read uncommitted | possible | possible | possible |
| Read committed | prevented | possible | possible |
| Repeatable read | prevented | prevented | possible in the standard |
| Serializable | prevented | prevented | prevented |

Real engines differ from the table. **PostgreSQL** defaults to *read committed*, treats read uncommitted as read committed, and its repeatable read (snapshot isolation) also prevents phantoms; *serializable* adds checks that abort one transaction with SQLSTATE `40001` if the result could not have happened serially. **MySQL InnoDB** defaults to *repeatable read*. **SQL Server** defaults to read committed with locking unless snapshot options are enabled. Always check the documentation for your engine and version.

## How it works: MVCC and locks

PostgreSQL, MySQL InnoDB and Oracle use **MVCC** (multi-version concurrency control): an update writes a new version of the row, and each transaction reads the versions visible to its snapshot. The big benefit is that **readers do not block writers and writers do not block readers**.

Writers still block other writers on the same row. When transaction A updates row 1, transaction B's update of row 1 waits until A commits or rolls back. You can take the lock deliberately when you read:

```sql
BEGIN;
SELECT stock FROM tickets WHERE id = 7 FOR UPDATE;  -- lock the row
-- check stock > 0 in the app, then:
UPDATE tickets SET stock = stock - 1 WHERE id = 7;
COMMIT;
```

Without `FOR UPDATE` (or a single atomic `UPDATE ... SET stock = stock - 1 WHERE id = 7 AND stock > 0`), two buyers at read committed can both read `stock = 1` and both sell the last ticket. Schema changes take stronger table-level locks; that is covered in the migrations lesson.

## Deadlocks

A **deadlock** happens when two transactions each hold a lock the other needs:

```text
T1: UPDATE accounts ... WHERE id = 1;   -- T1 locks row 1
T2: UPDATE accounts ... WHERE id = 2;   -- T2 locks row 2
T1: UPDATE accounts ... WHERE id = 2;   -- T1 waits for T2
T2: UPDATE accounts ... WHERE id = 1;   -- T2 waits for T1: deadlock
```

Neither can proceed, so the database detects the cycle and **aborts one** of them. PostgreSQL checks after `deadlock_timeout` (1 second by default) and raises SQLSTATE `40P01`; MySQL reports error 1213. The survivor continues normally.

How to reduce deadlocks:

- **Lock in a consistent order** everywhere, for example always update the lower account id first.
- **Keep transactions short**: no user think-time or network calls inside `BEGIN ... COMMIT`.
- Index the columns used in `UPDATE ... WHERE` so fewer rows are locked.
- **Retry** the aborted transaction in application code. Deadlocks and serialization failures are expected events, not bugs, when retried safely.

## What a DBA watches

Long-running and "idle in transaction" sessions hold locks and stop cleanup. In PostgreSQL, `pg_stat_activity` shows them and `pg_locks` with `pg_blocking_pids(pid)` shows who blocks whom. Settings such as `idle_in_transaction_session_timeout`, `lock_timeout` and `statement_timeout` put limits on the damage.

## Common mistakes

- Assuming the default isolation level is serializable.
- Read-then-write logic without a lock or an atomic update (lost updates).
- Long transactions that wrap slow work, holding locks for minutes.
- Not retrying on `40001` / `40P01`.

## Quick recap

- Transactions give ACID; isolation levels trade anomalies for concurrency, and engines implement them differently.
- MVCC lets readers and writers avoid blocking each other; writers on the same row still queue.
- Deadlocks are detected and one transaction is aborted. Consistent lock order, short transactions and retries keep them rare and harmless.

## Try it

The **Do** tab quiz walks through isolation and deadlock scenarios.


Explain ACID, the four isolation levels and their anomalies, how row locks work and how databases detect and break deadlocks.

Transactions and locking

Transaction Isolation Levels With PostgreSQL as an example

Database Deadlocks Easily Explained

Design indexes, read query plans, tune slow queries and understand how transactions, isolation levels and locks behave under concurrency.