# PostgreSQL Glossary

The canonical vocabulary for this workspace. Lessons use *these* words. Definitions are
tight (what the term **is**), opinionated, and reuse other glossary terms on purpose.

## Relational model

**Relation / Table**:
A named set of rows with a fixed set of typed columns. The core object you store data in.
_Avoid_: entity (that's our Go struct), collection

**Row / Tuple**:
A single record in a table — one value per column. Postgres calls the physical on-disk
record a *tuple* (and MVCC creates multiple tuple *versions* of one logical row).
_Avoid_: record (ok informally), entry

**Column / Attribute**:
A named, typed field of a table. Every value in a column shares one data type.
_Avoid_: field (that's the Go side), property

**Schema**:
Two meanings: (1) the *structure* of tables/columns/constraints; (2) a Postgres
**namespace** grouping objects (default `public`). Context disambiguates.
_Avoid_: database (a schema lives inside a database)

**Primary key**:
The column(s) that uniquely identify each row; enforced unique + not-null. In our repo,
a ULID `TEXT` named `pk__<table>`.
_Avoid_: unique id, row id

**Foreign key**:
A column referencing another table's primary key, enforcing referential integrity (you
can't reference a row that doesn't exist).
_Avoid_: relation, link

**Constraint**:
A rule the database enforces on data — `NOT NULL`, `UNIQUE`, `CHECK`, `PRIMARY KEY`,
`FOREIGN KEY`. The database, not the app, is the last line of defence.
_Avoid_: validation, rule

**NULL**:
The absence of a value — not zero, not empty string. Three-valued logic: comparisons with
NULL yield NULL (not true/false). In Go this is `pgtype`'s `Null` status.
_Avoid_: nil, empty, missing

## Storage & internals

**Page (block)**:
The fixed-size unit (8 KB) Postgres reads/writes to disk. A table is a sequence of pages;
each page holds many tuples.
_Avoid_: block (ok), sector

**Heap**:
The unordered table storage where the actual row data lives (as opposed to an index).
A "heap fetch" reads the row from the heap.
_Avoid_: table file, data file

**MVCC (Multi-Version Concurrency Control)**:
Postgres's concurrency model: an `UPDATE`/`DELETE` doesn't overwrite — it writes a new row
version and marks the old one dead, so readers see a consistent snapshot without blocking
writers.
_Avoid_: versioning, snapshotting

**Dead tuple**:
An old row version no longer visible to any transaction, left behind by MVCC. Removed by
VACUUM; accumulation is "bloat".
_Avoid_: garbage row, stale row

**VACUUM**:
The process that reclaims space from dead tuples and updates visibility/statistics.
Autovacuum runs it automatically. MVCC's necessary housekeeping.
_Avoid_: cleanup, gc

**WAL (Write-Ahead Log)**:
The append-only log of every change, written *before* the change reaches the data files —
the basis of durability and crash recovery.
_Avoid_: transaction log (ok), journal, redo log

**TOAST**:
The mechanism that stores oversized column values (big text/JSONB/bytea) out-of-line in a
separate table, transparently. "The Oversized-Attribute Storage Technique."
_Avoid_: blob storage, overflow

## Indexes

**Index**:
An auxiliary data structure that lets Postgres find rows without scanning the whole table —
at the cost of extra storage and slower writes.
_Avoid_: key (that's a constraint), lookup

**B-tree**:
The default index type — a balanced tree keeping keys sorted. Serves equality and range
(`=`, `<`, `>`, `BETWEEN`, `ORDER BY`, prefix `LIKE`). The one you'll use ~90% of the time.
_Avoid_: binary tree, btree index (say "B-tree")

**GIN / GiST / BRIN / Hash**:
Specialised index types: **GIN** for multi-value columns (arrays, JSONB, full-text —
our trigram search); **GiST** for geometric/range; **BRIN** for huge naturally-ordered
tables; **Hash** for equality only.
_Avoid_: inverted index (GIN specifically)

**Composite index**:
An index on multiple columns in order; usable for queries filtering on a *leading prefix*
of those columns. Column order matters.
_Avoid_: multi-index, combined key

**Partial index**:
An index with a `WHERE` clause — it indexes only the rows matching it (e.g. active rows),
saving space and write cost.
_Avoid_: filtered index (that's SQL Server), conditional index

**Covering index / index-only scan**:
An index that contains every column a query needs (via `INCLUDE` or being on those
columns), so Postgres answers from the index without touching the heap.
_Avoid_: included index

## Query execution

**Query planner (optimizer)**:
The component that turns SQL into an execution plan, choosing scans and joins by estimating
cost from table **statistics**. You describe *what*; it decides *how*.
_Avoid_: optimizer (ok), query engine

**EXPLAIN / EXPLAIN ANALYZE**:
`EXPLAIN` shows the planner's chosen plan + cost estimates; `EXPLAIN ANALYZE` actually runs
it and shows real times and row counts. The single most useful tuning tool.
_Avoid_: query plan viewer

**Sequential scan (seq scan)**:
Reading every row of a table's heap. Cheap for small/most-of-table reads; the thing an
index avoids for selective queries.
_Avoid_: full scan (ok), table scan

**Join strategy**:
How two tables are combined: **nested loop** (small inputs / indexed), **hash join**
(large, equality), **merge join** (both sorted). The planner picks one.
_Avoid_: join algorithm (ok)

## Transactions

**Transaction**:
A unit of work that is all-or-nothing — `BEGIN` … `COMMIT`/`ROLLBACK`.
_Avoid_: unit of work (ok), batch

**ACID**:
The guarantees a transaction provides: **A**tomicity, **C**onsistency, **I**solation,
**D**urability.
_Avoid_: transaction guarantees

**Isolation level**:
How much concurrent transactions can see of each other — `READ COMMITTED` (Postgres
default), `REPEATABLE READ`, `SERIALIZABLE`. Higher = fewer anomalies, more conflicts.
_Avoid_: consistency level

## Repo-specific

**RLS (Row-Level Security)**:
Postgres policies that filter which rows a session can see/modify. Our multi-tenancy: every
tenant table checks `resource_path = current_setting('permission.resource_path')`.
_Avoid_: row security (say "RLS")

**`resource_path`**:
The tenant identifier column on every tenant-scoped table; set on the session by the pool's
`BeforeAcquire` hook from the caller's JWT, and enforced by RLS.
_Avoid_: tenant id (it IS the tenant id, but say "resource_path")

**`pgx` / `pgtype`**:
The Go driver (`jackc/pgx`, v4 here) our repo talks to Postgres with, and its type package
mapping Go values ↔ Postgres types (`pgtype.Text`, `JSONB`, `Timestamptz`).
_Avoid_: database/sql (we don't use it), the driver

**Soft delete**:
Marking a row deleted with `deleted_at = now()` instead of removing it; every read filters
`deleted_at IS NULL`. Repo-wide convention.
_Avoid_: logical delete, archive