# PostgreSQL Cheat Sheet

Dense quick-reference for revision and interviews. Terms in [GLOSSARY.md](./GLOSSARY.md);
our code specifics in [repo-postgres-map.md](./repo-postgres-map.md).

## The model in five lines
1. Data lives in **tables** (relations): rows × typed columns, with **constraints** the DB enforces.
2. You describe **what** you want in SQL; the **planner** decides **how** to get it.
3. Physically: tables are **heap** pages (8 KB); **indexes** are side structures to find rows fast.
4. **MVCC** gives each transaction a consistent snapshot without blocking — at the cost of **dead tuples** that **VACUUM** cleans up.
5. **WAL** is written before data files → durability + crash recovery.

## SQL essentials
```sql
SELECT col1, col2 FROM t WHERE x = $1 AND deleted_at IS NULL ORDER BY created_at DESC LIMIT 20;
SELECT e.*, r.address FROM emails e JOIN email_recipients r ON r.email_id = e.email_id;  -- INNER JOIN
SELECT status, count(*) FROM emails GROUP BY status HAVING count(*) > 5;                  -- aggregate
INSERT INTO emails (email_id, subject) VALUES ($1, $2);
UPDATE emails SET status = $2 WHERE email_id = $1;
DELETE FROM emails WHERE email_id = $1;   -- (repo prefers soft delete: SET deleted_at = now())
INSERT INTO emails (...) VALUES (...) ON CONFLICT ON CONSTRAINT pk__emails DO UPDATE SET subject = EXCLUDED.subject;  -- UPSERT
```
JOIN types: `INNER` (matches only), `LEFT` (all left + matched right), `RIGHT`, `FULL`, `CROSS`.

## Data types (common)
`text` · `varchar(n)` · `int`/`bigint` · `numeric` · `boolean` · `timestamptz` · `jsonb` · `text[]` (array) · `uuid`.
Our repo: ULID **`text`** PKs, `jsonb` for structured blobs, `text[]` for lists, `timestamptz` for times.

## Constraints
`PRIMARY KEY` · `FOREIGN KEY … REFERENCES` · `UNIQUE` · `NOT NULL` · `CHECK (col = ANY(ARRAY[...]))` (enum-ish) · `DEFAULT`.

## Index types — what to reach for
| Type | Serves | Our use |
|---|---|---|
| **B-tree** (default) | `=`, `<`, `>`, `BETWEEN`, `ORDER BY`, prefix `LIKE` | most indexes; `resource_path`, FKs, composite |
| **GIN** | arrays, JSONB, full-text | trigram text search (`gin_trgm_ops`), array membership |
| **GiST** | ranges, geometry, nearest-neighbour | — |
| **BRIN** | huge, naturally-ordered tables | — |
| **Hash** | equality only | rare |
Variants: **composite** (leading-prefix rule), **partial** (`WHERE`), **covering** (`INCLUDE` → index-only scan), **expression** (`gin(nospace(x))`).

## Index rules of thumb
- Index columns in `WHERE` / `JOIN` / `ORDER BY` that are **selective**.
- Composite index order: most-selective / equality columns first; a query can use a **leading prefix**.
- Every index **slows writes** and costs storage — don't index everything.
- An index may be **ignored** if: the query returns most of the table, stats are stale, a function wraps the column, or types don't match.

## MVCC & VACUUM (interview core)
- `UPDATE`/`DELETE` never overwrite — they write a **new row version** and mark the old **dead**.
- Readers see a **snapshot**; readers don't block writers, writers don't block readers.
- Dead tuples accumulate → **bloat** → **VACUUM** reclaims them (autovacuum runs it). `VACUUM` also updates planner stats (`ANALYZE`).

## Isolation levels
| Level | Prevents | Postgres note |
|---|---|---|
| Read Committed | dirty reads | **default**; each statement sees a fresh snapshot |
| Repeatable Read | + non-repeatable/phantom reads | snapshot fixed for the whole tx |
| Serializable | + write skew (all anomalies) | via SSI; may abort with `40001` (retry) |

## Reading EXPLAIN
```
EXPLAIN ANALYZE SELECT ... ;
```
- Read **bottom-up, inside-out**. Each node: estimated `cost`, `rows`, and (with ANALYZE) actual `time`/`rows`/`loops`.
- Watch for: **Seq Scan** on a big table with a selective filter (missing index), estimated-vs-actual **row misestimates** (stale stats), **Nested Loop** over many rows (should be hash), high **loops**.
- Scans: Seq / Index / Index-Only / Bitmap Heap. Joins: Nested Loop / Hash / Merge.

## Our repo — quick facts
```
Driver        jackc/pgx v4.18 (some v5); pgtype v1.14; pgxpool; golang-migrate v4
DB ownership  conversationmgmt→tom · spike→notificationmgmt · notification→bob + notificationmgmt
Queries       hand-written raw SQL + database.* helpers (NO sqlc); values via $1..$N always
Layer         Ext / QueryExecer interfaces; ExecInTx(ctx, db, func(ctx, tx){...}); Select().ScanAll(&ents)
Entities      pgtype fields; FieldMap()+TableName(); NULL via .Set(nil) / Status; ULID text PKs
Migrations    golang-migrate, FORWARD-ONLY (no *.down.sql); <NNNN>_migrate.up.sql; make/run.bash
Transactions  default READ COMMITTED; ExecInTxWithRetry on 40001; FOR UPDATE/SHARE hints
RLS           resource_path + permission_check(); set per-connection by pool BeforeAcquire hook
Soft delete   deleted_at IS NULL everywhere; never hard-delete
Indexes       B-tree (resource_path/FK/composite), GIN trigram text search, GIN array; CONCURRENTLY
EXPLAIN       not used in app code; investigate via /query-db skill; activity logs in zeus DB
Hasura        read path; re-implements tenant filter in metadata (not Postgres RLS)
```

## Interview one-liners
- *What's an index?* A sorted side-structure that finds rows without scanning the table — faster reads, slower writes.
- *B-tree vs GIN?* B-tree for scalar equality/range/order; GIN for multi-value columns (arrays, JSONB, full-text).
- *What is MVCC?* Writers create new row versions instead of overwriting, so readers get a consistent snapshot without locks — and VACUUM later removes the dead versions.
- *Why is my index not used?* Query isn't selective enough, stale stats, a function/expression wraps the column, or a type mismatch.
- *Isolation levels?* Read Committed (default) → Repeatable Read → Serializable; higher stops more anomalies but conflicts more.
- *How does multi-tenancy work here?* Every tenant table has `resource_path` + an RLS policy; the pool sets `permission.resource_path` per connection from the JWT.
- *Soft delete?* Set `deleted_at` and filter `deleted_at IS NULL` — never physically remove.