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

Database Administration

## Why it matters

Most data breaches do not need a clever exploit: they use credentials that had far more access than they needed. A reporting tool that can `DELETE`, an application account that owns the schema, a former contractor who still has a login. **Least privilege** means every account gets exactly the rights its job requires and nothing more, so that a bug, a leaked password or a compromised server can do limited damage. Access control is also what auditors check first.

## The building blocks (PostgreSQL)

- **Roles** are users (with `LOGIN`) and groups (without). Grant privileges to **group roles**, then grant group membership to people and services. When someone leaves, you drop one membership, not fifty grants.
- **Privileges** are granted on objects: `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `REFERENCES`, `TRIGGER` on tables; `USAGE` and `CREATE` on schemas; `CONNECT` on databases; `EXECUTE` on functions.
- **Owners** can do anything to their objects, including dropping them. Applications should not own the tables they use.
- **PUBLIC** is a pseudo-role meaning "everyone". Check what it has; for example, `CONNECT` on a database is granted to PUBLIC by default.

```sql
-- Group roles
CREATE ROLE app_rw NOLOGIN;
CREATE ROLE reporting_ro NOLOGIN;

GRANT CONNECT ON DATABASE shop TO app_rw, reporting_ro;
GRANT USAGE ON SCHEMA app TO app_rw, reporting_ro;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app TO app_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO reporting_ro;

-- Tables created later get the same rights automatically
ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting_ro;

-- Members
GRANT app_rw TO shop_app;
GRANT reporting_ro TO analyst_ana;

-- Take a right away
REVOKE DELETE ON app.payments FROM app_rw;
REVOKE CONNECT ON DATABASE shop FROM PUBLIC;
```

`GRANT ... ON ALL TABLES` only affects tables that exist now. `ALTER DEFAULT PRIVILEGES` covers future ones, which is the step people most often forget. Column-level grants (`GRANT SELECT (id, email) ON app.users TO support`) and **row-level security** (`ALTER TABLE ... ENABLE ROW LEVEL SECURITY` plus `CREATE POLICY`) restrict access further when needed.

MySQL and SQL Server use the same `GRANT`/`REVOKE` vocabulary with different details (MySQL grants to `'user'@'host'`; SQL Server separates logins, users and schemas, and adds `DENY`). SQLite has no users at all: access is whoever can read the file, which is why it is not used as a shared multi-user server.

## Beyond grants

- **Authentication:** `scram-sha-256` passwords or certificates in `pg_hba.conf`, and limit which addresses may connect. Single sign-on through your identity provider is common for people.
- **Encryption in transit:** require TLS (`hostssl` lines, `sslmode=verify-full` on clients).
- **Secrets:** application passwords live in a secrets manager or the platform's environment settings, never in source code. Rotate them.
- **Separation of duties:** DBAs use personal admin accounts, not a shared `postgres` login, so actions are attributable. Break-glass superuser access is logged.
- **Auditing:** log connections and DDL (`log_connections`, `log_statement = 'ddl'`), or use the pgAudit extension for detailed logs.

## Worked example: auditing grants

In PostgreSQL you can list actual table privileges from the catalog:

```sql
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'app'
ORDER BY grantee, table_name, privilege_type;
```

In `psql`, `\dp app.*` shows the same as access-control lists. An audit compares this list with an **approved policy**: anything granted but not approved is a candidate to revoke. That comparison is a plain SQL anti-join, which you can practise in any database:

```sql
SELECT g.role_name, g.table_name, g.privilege
FROM grants g
LEFT JOIN approved_policy p
  ON p.role_name = g.role_name
 AND p.table_name = g.table_name
 AND p.privilege = g.privilege
WHERE p.role_name IS NULL;
```

You can even generate the fix: `'REVOKE ' || privilege || ' ON ' || table_name || ' FROM ' || role_name || ';'`. Review generated statements before running them, and run them in a change window.

## Common mistakes

- Applications connecting as the schema owner or a superuser.
- Granting to individual users instead of group roles.
- Forgetting `ALTER DEFAULT PRIVILEGES`, so new tables are unreadable (or someone "fixes" it with a superuser).
- Leaving `PUBLIC` privileges and stale accounts in place.
- Shared admin passwords with no audit trail.

## Quick recap

- Least privilege: group roles with only the needed privileges, membership for people and services.
- `GRANT`/`REVOKE`, `ALTER DEFAULT PRIVILEGES`, column grants and row-level security shape access.
- Authenticate strongly, encrypt connections, keep secrets out of code, and audit grants against a policy.

## Try it

In the **Do** tab, audit a grants table against an approved policy and generate the `REVOKE` statements for every excess privilege.


Apply least privilege with roles, GRANT and REVOKE, default privileges and regular audits of who can do what.

Access control

For Your Eyes Only: Roles, Privileges, and Security in PostgreSQL | Citus Con 2023

SQL DCL: Granting and Revoking Privileges

Recreate the container with the extension preloaded (your `shop` data stays in the volume): `docker rm -f pg-practice` then `docker run --name pg-practice -e POSTGRES_PASSWORD=change-me-practice -p 5432:5432 -v pg-practice-data:/var/lib/postgresql/data -d postgres:17 -c shared_preload_libraries=pg_stat_statements`. Native install: add `shared_preload_libraries = 'pg_stat_statements'` to postgresql.conf and restart.

Create a load-test database and enable the extension: `docker exec pg-practice createdb -U postgres bench`, then `docker exec -it pg-practice psql -U postgres -d bench -c 'CREATE EXTENSION pg_stat_statements;'`.

Initialise pgbench's tables at a small scale: `docker exec pg-practice pgbench -U postgres -i -s 5 bench`.

Generate load for one minute with 4 clients: `docker exec pg-practice pgbench -U postgres -c 4 -T 60 bench`. Note the reported transactions per second (tps).

In psql on `bench`, run the top-10 query from the lesson (`SELECT calls, round(total_exec_time::numeric,1) AS total_ms, round(mean_exec_time::numeric,2) AS mean_ms, left(query,70) FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;`). Write down the top three query patterns and their call counts.

Check the cache hit ratio with the `pg_stat_database` query from the lesson and record the percentage.

Check dead tuples: `SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;`. Which table has the most dead rows, and why would pgbench's updates cause that?

Simulate a problem session: in a second psql window run `BEGIN; SELECT * FROM pgbench_accounts LIMIT 1;` and leave it open. In the first window, run the `pg_stat_activity` query from the lesson and find the `idle in transaction` session and its age. Then type `ROLLBACK;` in the second window.

Run `VACUUM (VERBOSE) pgbench_accounts;` and re-check `n_dead_tup`. Clean up when done: `docker exec pg-practice dropdb -U postgres bench`.

## Why it matters

Databases rarely fail without warning. Disks fill a little every day, a new feature adds a query that runs a million times, a forgotten session holds a transaction open and cleanup stops. Monitoring turns those slow trends into alerts before users notice. It also gives you the evidence for every tuning decision: you fix what the numbers say, not what feels slow.

## What to watch

A short list covers most incidents:

- **Availability:** can a test client connect and run `SELECT 1`?
- **Connections:** active vs idle vs `idle in transaction`, compared with `max_connections`.
- **Query performance:** total and mean time per query pattern, slow-query log entries.
- **Cache hit ratio:** how often reads are served from memory rather than disk.
- **Vacuum and bloat:** dead rows waiting to be cleaned, and when autovacuum last ran.
- **Replication:** lag per replica, and replication slots that retain WAL.
- **Resources:** disk space (data and WAL), CPU, memory, I/O latency.
- **Errors:** deadlocks, failed logins, out-of-disk, checkpoint warnings in the server log.

## PostgreSQL's built-in views

**Who is connected and what are they doing?**

```sql
SELECT pid, usename, state, wait_event_type,
       now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY xact_age DESC NULLS LAST;
```

A session that is `idle in transaction` for many minutes is holding locks and blocking vacuum.

**Which queries cost the most?** The `pg_stat_statements` extension groups queries by shape (constants replaced with `$1`) and records calls and timings. It must be loaded at server start (`shared_preload_libraries = 'pg_stat_statements'`, then a restart) and enabled per database with `CREATE EXTENSION pg_stat_statements;`.

```sql
SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       left(query, 70) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
```

(Column names are for PostgreSQL 13 and later.) Sort by total time to find the best tuning targets; `SELECT pg_stat_statements_reset();` starts a fresh measurement window.

**Is the cache working?**

```sql
SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();
```

On a busy OLTP database this is usually above 99%. A falling value suggests the working set no longer fits in memory.

**Dead tuples and bloat.** Because of MVCC, updates and deletes leave old row versions behind. **Autovacuum** cleans them up so the space can be reused; if it falls behind, tables and indexes grow ("bloat") and queries slow down.

```sql
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
```

Many dead tuples and an old `last_autovacuum` point to a vacuum problem, often caused by long-running transactions. `pg_size_pretty(pg_total_relation_size('table'))` shows how big a table and its indexes are. Plain `VACUUM` frees space for reuse without blocking; `VACUUM FULL` rewrites the table and locks it, so it is a planned maintenance action, not a routine one.

**Unused indexes:** `pg_stat_user_indexes` with `idx_scan = 0` over a representative period.

## Turning numbers into alerts

Collect metrics continuously with a tool rather than running queries by hand: Prometheus with `postgres_exporter` and Grafana dashboards, a cloud provider's monitoring (CloudWatch, Azure Monitor), or a commercial tool. Then alert on symptoms with sensible thresholds:

- disk above 80% (and the growth rate),
- connections above 80% of `max_connections`,
- replication lag above your RPO,
- any transaction open longer than, say, 15 minutes,
- the error rate or deadlock count jumping.

Every alert should have a short runbook: what it means, how to check, what to do. Alerts that fire constantly and are ignored are worse than none.

## Common mistakes

- Monitoring the server (CPU) but not the database (locks, vacuum, slow queries).
- Running `pg_stat_statements` once during an incident with no baseline to compare.
- Fixing bloat with `VACUUM FULL` at peak time and locking a busy table.
- Alerting on everything, so real alerts drown.

## Quick recap

- Watch availability, connections, query time, cache hit ratio, vacuum, replication, disk and errors.
- `pg_stat_activity`, `pg_stat_statements`, `pg_stat_database` and `pg_stat_user_tables` answer most questions.
- Collect continuously, alert on a few meaningful thresholds, and write a runbook for each.

## Try it

In the **Do** tab, enable `pg_stat_statements` on your practice server, generate load with `pgbench`, and find the most expensive queries and the table with the most dead rows.


Watch connections, slow queries, cache hit ratio, dead tuples and disk, and use pg_stat_statements to find what to fix first.

Monitoring

Postgres In Production | Special Series | Deep Dive into pg_stat_statements (Part 2)

In psql on `shop`, create practice data: `CREATE TABLE customers_mig (id bigint PRIMARY KEY, fullname text);` then `INSERT INTO customers_mig SELECT g, 'Customer ' || g FROM generate_series(1, 50000) g;`.

Write down the plan before touching anything: the four phases (expand, backfill, switch reads, contract), what the application does in each, and how you would roll back each phase.

Expand: run `SET lock_timeout = '5s';` then `ALTER TABLE customers_mig ADD COLUMN display_name text;`. Note how quickly it completes even with 50,000 rows.

Simulate the dual-writing app: in a second psql window insert a new row that writes both columns: `INSERT INTO customers_mig (id, fullname, display_name) VALUES (50001, 'New Customer', 'New Customer');`.

Backfill in batches: run `UPDATE customers_mig SET display_name = fullname WHERE id IN (SELECT id FROM customers_mig WHERE display_name IS NULL AND fullname IS NOT NULL LIMIT 5000);` repeatedly until psql reports `UPDATE 0`. Count how many batches it took.

Add the constraint safely: `ALTER TABLE customers_mig ADD CONSTRAINT display_name_not_null CHECK (display_name IS NOT NULL) NOT VALID;`, then `ALTER TABLE customers_mig VALIDATE CONSTRAINT display_name_not_null;`, then `ALTER TABLE customers_mig ALTER COLUMN display_name SET NOT NULL;`.

Add a supporting index without blocking writes: `CREATE INDEX CONCURRENTLY idx_customers_mig_display_name ON customers_mig (display_name);`. Then try the same command inside `BEGIN; ... ROLLBACK;` and read the error it gives.

Switch reads: confirm `SELECT COUNT(*) FROM customers_mig WHERE display_name IS DISTINCT FROM fullname;` returns 0, and imagine the app now reading display_name only.

Contract (a later release, once nothing uses the old column): `ALTER TABLE customers_mig DROP COLUMN fullname;`. Then clean up the practice table: `DROP TABLE customers_mig;`.

## Why it matters

Applications change, so schemas change: new columns, renamed fields, new indexes, split tables. On a small database you can change the schema and restart the app. On a busy production system, the wrong `ALTER TABLE` can lock a table for minutes, queue every request behind it and take the site down. Deploying new code while old code is still running adds a second problem: both versions must work against the same schema at the same time. Planning safe migrations is a core DBA skill.

## Migrations as code

A **migration** is a small, versioned script that moves the schema from one version to the next. Tools record which migrations have run in a table in the database and apply the rest in order: Flyway and Liquibase (any stack), Alembic (Python), Rails and Django migrations, Prisma Migrate, Knex and others.

Good habits:

- Every change is a migration in version control, reviewed like code. No hand edits in production.
- Migrations are small and do one thing.
- Each one is tested on a copy of production-sized data before release, so you know how long it takes and what it locks.
- You know how to roll back, or how to roll forward with a fix if a rollback would lose data.

## Why some changes hurt

In PostgreSQL most `ALTER TABLE` forms take an `ACCESS EXCLUSIVE` lock, which blocks reads and writes on that table. For many changes the lock is brief; the danger is changes that **rewrite or scan** the whole table while holding it, and the fact that a waiting `ALTER` also blocks every query queued behind it.

| Change | Typical impact (PostgreSQL 11+) |
|---|---|
| `ADD COLUMN` nullable, or with a constant default | Fast: catalog-only change |
| `ADD COLUMN ... DEFAULT now()` or other volatile default | Rewrites the table |
| `ALTER COLUMN ... TYPE` to an incompatible type | Usually rewrites the table |
| `SET NOT NULL` | Scans the table (skipped if a validated `CHECK (col IS NOT NULL)` exists, PG 12+) |
| `ADD FOREIGN KEY` / `CHECK` | Scans unless added `NOT VALID`, then `VALIDATE CONSTRAINT` later with a weaker lock |
| `CREATE INDEX` | Blocks writes for the build; use `CREATE INDEX CONCURRENTLY` |
| Rename or drop a column | Fast, but breaks any running code that still uses the old name |

MySQL has its own rules (`ALGORITHM=INSTANT/INPLACE`, and tools such as gh-ost or pt-online-schema-change), so always check your engine's documentation.

Set a short `lock_timeout` in migrations (`SET lock_timeout = '5s';`) so a migration that cannot get its lock fails fast and can be retried, instead of freezing traffic.

## Expand, migrate, contract

Zero-downtime changes are split across several deploys so that old and new code both work at every step. Renaming `customers.fullname` to `customers.display_name`:

1. **Expand:** add the new column (nullable, fast). Deploy code that **writes both** columns and still reads the old one.
2. **Backfill:** copy existing data in small batches to avoid long locks and huge transactions:

```sql
UPDATE customers SET display_name = fullname
WHERE id IN (
  SELECT id FROM customers
  WHERE display_name IS NULL AND fullname IS NOT NULL
  LIMIT 5000
);
-- repeat until 0 rows are updated
```

3. **Switch reads:** deploy code that reads the new column. Add constraints safely:

```sql
ALTER TABLE customers
  ADD CONSTRAINT display_name_not_null CHECK (display_name IS NOT NULL) NOT VALID;
ALTER TABLE customers VALIDATE CONSTRAINT display_name_not_null;
ALTER TABLE customers ALTER COLUMN display_name SET NOT NULL;  -- uses the valid CHECK, no long scan
```

4. **Contract:** once no running code uses the old column (check logs and every service), stop writing it, then drop it in a later release.

The same pattern handles splitting tables, changing types (new column, backfill, switch) and moving data between services.

## Index changes

```sql
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
```

`CONCURRENTLY` cannot run inside a transaction block, and if it fails it leaves an `INVALID` index that must be dropped and retried. Most migration tools need a flag to run a migration outside their usual transaction.

## A release checklist

- Fresh backup (or a confirmed PITR window) before the change.
- Migration timed on realistic data; locks understood.
- `lock_timeout` and `statement_timeout` set.
- Run at a low-traffic time, with someone watching locks and errors.
- Rollback or roll-forward plan written down.

## Common mistakes

- Renaming or dropping a column in the same release as the code change.
- One giant `UPDATE` backfill that bloats the table and holds locks for an hour.
- Creating indexes without `CONCURRENTLY` on a busy table.
- Testing migrations only on an empty development database.

## Quick recap

- Migrations are versioned, reviewed, small scripts run by a tool.
- Know which changes lock, scan or rewrite; use `lock_timeout`, `NOT VALID` plus `VALIDATE`, and `CREATE INDEX CONCURRENTLY`.
- Expand, backfill, switch, contract: old and new code must both work at every step.

## Try it

In the **Do** tab, carry out a full expand/backfill/contract column rename on your practice server.


Plan schema changes as versioned migrations and roll them out without downtime using expand, backfill and contract.

Schema migrations

How do software projects achieve zero downtime database migrations?

Grant least privilege, monitor slow queries, connections and bloat, and roll out schema migrations without downtime.