# Mission: PostgreSQL

## Why
Walk into a PostgreSQL interview able to explain *how the database actually works* — from
the relational model down to pages, MVCC, indexes, and the query planner — with a mental
model solid enough to reason about a slow query or a schema decision I've never seen. I use
Postgres through pgx in this monorepo, but I'm newer to SQL and the internals, so the deep
concepts aren't yet mine. Every concept is anchored to real database code in my team's
services — **conversationmgmt**, **notification** (`internal/notification`), and **spike**
(`internal/spike`) — and the shared `internal/golibs/database` layer, so an abstract idea
lands on code and migrations I already touch.

## Success looks like
- I can explain the relational model and write correct SQL: SELECT/JOIN/aggregate,
  INSERT/UPDATE/DELETE, and `ON CONFLICT` upserts like the ones in our repos.
- I can describe how Postgres stores data (pages, heap, tuples) and explain **MVCC** — why
  it enables concurrency, and why it needs VACUUM.
- I can choose and justify an **index**: B-tree vs GIN/GiST/BRIN, partial, covering,
  composite — and say when *not* to add one.
- I can read an **EXPLAIN / EXPLAIN ANALYZE** plan, name the scan and join strategies, and
  diagnose why a query is slow.
- I can reason about **transactions**: ACID, isolation levels, locks, and the anomalies
  each level allows.
- I can explain how our Go code talks to Postgres: pgx, the connection pool, the
  `Ext`/`Tx` layer, `pgtype`, migrations, and **RLS multi-tenancy** via `resource_path`.
- I can answer the classic interview questions (index on what? why is it not used? MVCC vs
  locking, isolation levels, EXPLAIN, normalization) without hand-waving.

## Constraints
- **Newer to Postgres/SQL** — lessons start from the relational model and basic SQL, then
  build to internals. Assume general backend + Go maturity (finished the Go course); do NOT
  assume SQL fluency.
- **Format:** interactive HTML lessons (recall quizzes) + dense Markdown references — same
  format as the [Kafka](../kafka/index.html), [Go](../golang/index.html) &
  [gRPC](../grpc/index.html) courses. Lives in `docs/postgresql/`.
- **Broad topic, multi-session:** the roadmap ([index.html](./index.html)) spans six parts;
  lessons are short and arrive part by part.
- **Four pillars in scope:** internals & data structures · indexes · query performance ·
  Postgres in Go (this repo).

## Out of scope (for now)
- Replication, HA, and backups/PITR beyond a conceptual mention.
- Administration/ops (roles, tuning `postgresql.conf`, upgrades) beyond query-relevant bits.
- Non-Postgres SQL dialects and other databases.
- (Revisit if the mission shifts — confirm before changing.)