Glossary

Every core term from the course, one line each — click any to jump to the lesson that opens it up.

43 terms

page (block): The fixed 8KB unit Postgres reads and writes; a table is an array of them.
tuple: One stored row version, with a header (xmin/xmax) and column data.
ctid: A row's physical address: (block, line-pointer offset).
line pointer (ItemId): A 4-byte slot pointing at a tuple on a page, so it can move without changing its ctid.
TOAST: Compressing/off-lining oversized values so a row still fits on a page.
B-tree: The default index: a short, wide, balanced tree of sorted (key, ctid) pairs.
index scan: Descend an index to find matching rows, then fetch them from the heap.
bitmap heap scan: Collect matching ctids into a page-ordered bitmap, then read the heap in order.
index-only scan: Answer a query from the index alone, skipping the heap (helped by the visibility map).
HOT update: An update that changes no indexed column and fits on the page, skipping index writes.
MVCC: Multi-version concurrency control: keep row versions so readers never block writers.
xmin / xmax: Tuple-header fields: the transaction that created / deleted a version.
snapshot: The set of committed transactions a statement/transaction can see.
isolation level: When the snapshot is taken: read committed (per statement) vs repeatable read (per transaction).
bloat: Dead tuple space that's allocated but unusable until VACUUM reclaims it.
VACUUM: Marks dead-tuple space reusable (VACUUM FULL rewrites to shrink the file).
autovacuum: Background process that vacuums tables once enough dead tuples accumulate.
xid wraparound: 32-bit transaction ids wrap after ~4B; VACUUM freezes old rows to prevent it.
WAL: Write-ahead log: changes are logged and fsync'd before the data page, for durability.
LSN: Log sequence number: a byte position in the WAL.
checkpoint: Flushes dirty pages to disk and bounds how much WAL recovery must replay.
buffer pool: shared_buffers: the in-memory cache of 8KB pages all access goes through.
clock-sweep: The LRU-approximating eviction that decrements usage counts and evicts a page at 0.
dirty page: A buffer modified in memory but not yet written back to disk.
row lock: Taken by UPDATE/DELETE/SELECT FOR UPDATE, held until the transaction ends.
deadlock: A cycle of transactions each waiting on a lock the other holds; the detector aborts one.
advisory lock: An arbitrary integer mutex for application-level coordination.
backend: The per-connection OS process where your queries run.
work_mem: Per-operation memory a sort/hash can use, per connection.
connection pooler: PgBouncer/Supavisor: lends a few backends to many clients (transaction pooling).
query planner: Chooses the cheapest execution plan by estimating costs.
selectivity: The fraction of rows a predicate matches; drives the planner's scan choice.
cost: The planner's abstract estimate (seq_page_cost=1, random_page_cost=4, …).
executor: Runs a plan as a tree of iterators, pulling rows one at a time (volcano model).
pipelining: Pull-based execution lets a LIMIT stop early without materializing everything.
EXPLAIN ANALYZE: Runs a query and reports the plan with estimated vs actual rows and time (add BUFFERS for page hits/reads).
partition pruning: The planner skipping partitions whose bounds can't match a key predicate.
streaming replication: Shipping WAL to a standby that replays it into a live read-only copy.
replication slot: Makes the primary retain WAL until a standby confirms it (beware forgotten slots).
role: A login user or group; connections act as one, with GRANT-ed privileges.
row-level security (RLS): Per-row policies that filter every query by role — how multi-tenant apps isolate data.
policy (USING / WITH CHECK): USING filters which rows a role sees; WITH CHECK validates rows it writes.
BYPASSRLS / superuser: Roles that ignore RLS entirely — why an app must not connect as one.