Glossary

->> and ->: -> returns a field as jsonb, ->> returns it as text. A plain GIN index on the column does not help a filter on doc->>'key'.; Documents and JSONB
2NF: Second normal form: with a composite key, no column is a fact about only part of the key.; Normalization
3NF: Third normal form: no column that is not part of the key is a fact about another such column.; Normalization
Aborted transaction: After an error inside a transaction, PostgreSQL refuses every further statement until you ROLLBACK, or ROLLBACK TO a savepoint.; Transactions and savepoints
ACCESS EXCLUSIVE: The strongest lock mode. It conflicts with every other mode, plain reads included. Most ALTER TABLE forms take it unless the docs say otherwise.; Migrations that lock the table
Aggregate: A function that turns many rows into one value: count, sum, min, max, avg. Except for count, they return NULL when no rows come in.; Counting with GROUP BY
ANALYZE: Reads a random sample of the table and stores statistics per column in pg_statistic. Autovacuum runs it in the background. The sample is 300 rows per point of statistics target: 30,000 rows by default.; Planner statistics: how PostgreSQL estimates rows
Anomaly: A change that the table shape makes hard or wrong: a fact that must be updated in many rows (update), cannot be stored yet (insert), or disappears with another row (delete).; Normalization
Anti-join: Rows of A with no match in B: NOT EXISTS (...), or a left join followed by WHERE b.id IS NULL.; Joins
AOF: The append-only file: every write command is logged and replayed at startup to rebuild the dataset. Redis ships with it turned off.; Key-value stores
Asynchronous replication: PostgreSQL's default: a commit returns once the primary has flushed its own WAL. Replicas catch up a little later.; Replication and lag
Atomic commit: A group of changes lands completely or not at all. A crash halfway leaves the old state, never a mix.; Why use a database instead of a file
Autocommit: With no BEGIN, PostgreSQL wraps each statement in its own transaction and commits it if it succeeds. Django runs in this mode by default.; Transactions and savepoints
Autovacuum: A launcher process plus worker processes that check each table and run VACUUM and ANALYZE when enough rows have changed.; Dead rows and VACUUM
B-tree: A balanced tree of pages. Every path from the root to a leaf has the same length, so every lookup costs the same number of page reads.; B-tree indexes
Backend process: The server process PostgreSQL starts for one connection. It lives as long as the connection and holds that session's state.; Connections and pooling
Base backup: A copy of the whole data directory, taken while the server runs (pg_basebackup, or a tool like pgBackRest). The starting point for replaying WAL.; Backups you can restore
BRIN: Block Range Index. Stores a summary, such as min and max, for each group of 128 table pages by default. It can return rows that do not match, so the executor rechecks each row of a matching range.; Index types: GIN, GiST, BRIN and Hash
Buffers: Pages of 8 kB the node touched: hit in PostgreSQL's cache, read from the OS or disk. Included by default with ANALYZE since PostgreSQL 18.; Reading EXPLAIN
Bypass: Superusers and roles with BYPASSRLS skip all policies. The table owner does too, unless the table has FORCE ROW LEVEL SECURITY.; Roles, grants and row-level security
CHECK: A boolean expression on one row, tested on every insert and update: price > 0, ends_at > starts_at.; Constraints: UNIQUE, CHECK and exclusion
Check-then-act race: Code reads to decide ("is this email free?"), then writes. Another writer can change the answer between the read and the write.; Constraints: UNIQUE, CHECK and exclusion
Checkout transaction: One transaction that creates the order, its lines, the stock change and the confirmation job. All of it commits, or none of it (day 17).; Capstone: the data of a shop
Checkpoint: A point where every dirty page in memory has been written to the data files. Recovery starts from the last one, and older WAL can be recycled. Every 5 minutes or 1 GB of WAL by default.; The write-ahead log
Claim: Moving a job from available to executing and recording who took it, in one short transaction, so no other worker picks it.; A job queue in the database
Co-location: Tables sharded on the same key and the same ranges keep matching rows on the same node, so joins and transactions on one key stay local.; Sharding
Column store: Each column is stored on its own. A query reads only the columns it names. DuckDB and ClickHouse work this way.; Columns for analytics
Commit: An entry of the log is committed once a quorum has stored it. Only then is it applied, and it is never lost after that.; How replicas agree
Compare-and-set: An UPDATE whose WHERE clause includes the value you expect. It writes only if the row still holds that value, in one atomic statement.; Optimistic locking
Composite index: An index on several columns, sorted by the first, then by the second within equal firsts, and so on. Like a phone book sorted by last name, then first name.; Composite, partial and covering indexes
Concurrent access: Several readers and writers use the same data at once without corrupting it or silently losing writes.; Why use a database instead of a file
Conflict target: The columns (and optional WHERE) in ON CONFLICT (...) that tell PostgreSQL which unique index decides what a duplicate is.; Upserts and safe retries
Containment (@>): a @> b is true when every key and value of b is found in a, at the same place. The main operator a GIN index on jsonb can answer.; Documents and JSONB
Counter cache: A count stored on the parent row, like tags.public_topic_count, updated each time a child row is added or removed.; Denormalization: copying data on purpose
Covering index: An index that holds every column a query needs. With INCLUDE, extra columns are stored in the leaf entries as payload, not as search keys.; Composite, partial and covering indexes
CREATE INDEX CONCURRENTLY: Builds an index without blocking writes. It costs two table scans, more time, and it cannot run inside a transaction block.; Migrations that lock the table
DCS: Distributed configuration store: etcd, Consul, ZooKeeper or the Kubernetes API. A small cluster that agrees on a few keys, even when some of its own nodes fail.; Failover without split brain
Dead row: An old row version left by an UPDATE or a DELETE. It still takes space in its page (day 9) until VACUUM removes it.; Dead rows and VACUUM
Deadlock: Two or more transactions each hold a lock that another one wants. None can proceed until one is aborted.; Locks and deadlocks
deadlock_timeout: How long a waiting transaction sleeps before it checks for a deadlock. One second by default.; Locks and deadlocks
Default partition: A catch-all for rows that fit no other partition. Without one, PostgreSQL rejects such an insert with an error.; Partitioning big tables
Denormalization: Storing a fact a second time, on purpose, so that reads are cheaper. The original stays the source of truth.; Denormalization: copying data on purpose
Drift: The copy no longer matches its source, because some write changed one and not the other.; Denormalization: copying data on purpose
Durability: Once the database says "committed", the change survives a crash or a power cut. It is on disk, not only in a cache.; Why use a database instead of a file
Eviction policy: What Redis does when it reaches maxmemory: drop keys by LRU, LFU, TTL or at random, or refuse writes with noeviction, the default.; Key-value stores
Exclusion constraint: A generalised UNIQUE: no two rows may match on all the listed operators. With && on a range, it means "no two bookings overlap".; Constraints: UNIQUE, CHECK and exclusion
EXPLAIN ANALYZE: Runs the query for real and prints, next to each estimate, the actual time in milliseconds, actual rows and loops. It really executes: an UPDATE really updates.; Reading EXPLAIN
Extended statistics: Statistics across several columns, created with CREATE STATISTICS and filled by ANALYZE: functional dependencies, distinct counts and MCV lists over column groups.; Planner statistics: how PostgreSQL estimates rows
Fan-out: One row of A that matches 5 rows of B gives 5 rows in the result. Join two such tables and the counts multiply.; Joins
Fan-out: How many children one internal page points to. With small keys it is in the hundreds, which keeps the tree only a few levels deep.; B-tree indexes
Fan-out: A query that cannot be routed by the key, so it runs on every shard and the results are merged.; Sharding
Foreign key: A column whose value must match a key in another table. The database rejects an order that points at a customer who does not exist.; Tables, rows and keys
Full-page write: The first change to a page after a checkpoint logs a copy of the whole 8 kB page, so a page torn by a crash mid-write can be rebuilt.; The write-ahead log
Functional dependency: Column B depends on column A when one value of A always goes with one value of B. A warehouse has one address: warehouse determines address.; Normalization
GIN: Generalized Inverted Index. One entry per element (array item, JSONB key, word) pointing at the rows that contain it. Made for "does this value contain that?".; Index types: GIN, GiST, BRIN and Hash
GiST: Generalized Search Tree. A balanced tree of bounding boxes or ranges. Answers overlap, containment and nearest neighbour for geometry, ranges and IP networks.; Index types: GIN, GiST, BRIN and Hash
GROUP BY: Splits the rows into groups that share the same values, then returns one row per group. Every other column in the SELECT list must be inside an aggregate, unless it depends on a grouped primary key.; Counting with GROUP BY
Guarded update: An UPDATE whose WHERE also checks the rule, like stock >= 1. If it touches no row, the rule failed and the transaction rolls back.; Capstone: the data of a shop
HAVING: A filter on groups, after the aggregates are computed. WHERE filters rows before grouping and cannot use aggregates.; Counting with GROUP BY
Idempotency key: A unique value the client sends with a request (often a UUID) so the server can recognise a retry of the same request.; Upserts and safe retries
Idempotency key: A value the client sends with a request so a retry is recognized. A unique constraint on it turns the second try into a no-op (day 23).; Capstone: the data of a shop
Idempotent: Running the operation twice leaves the system in the same state as running it once.; Upserts and safe retries
Inner join: A JOIN B ON ... keeps only the pairs where the condition is true. A row of A with no match in B disappears.; Joins
IS DISTINCT FROM: A comparison that treats NULL like an ordinary value. It always returns true or false, never NULL.; NULL in SQL: comparisons, NOT IN and unique keys
Join-based eager load: One query with a LEFT OUTER JOIN that brings the related columns along. Rails eager_load, Django select_related.; N+1 and the ORM
jsonb: Stores a parsed, binary form. Drops white space and duplicate keys, does not keep key order, and can be indexed. The json type keeps the exact input text instead.; Documents and JSONB
jsonb_path_ops: A smaller GIN operator class. One hashed entry per path to a value. Supports @>, @? and @@, but not ?. The default class, jsonb_ops, also supports ?.; Documents and JSONB
Keyset pagination: Asking for "the next rows after the last one I saw" with a WHERE on the sort key, instead of skipping rows with OFFSET.; Asking with SELECT
Lazy loading: The ORM loads an association only when the code first reads it. Rails and Django do this by default. In a loop over N rows, it runs N queries.; N+1 and the ORM
Leader key: A key in the DCS naming the primary, attached to a lease with a TTL. Whoever holds it may run as primary; if nobody renews it, it disappears.; Failover without split brain
Leader, follower, candidate: The three roles a node can have. Only the leader accepts writes, and followers copy its log. A follower that stops hearing from the leader turns candidate and asks for votes.; How replicas agree
Left join: A LEFT JOIN B ON ... keeps every row of A. When B has no match, the B columns are NULL.; Joins
Leftmost prefix: The leading columns of a composite index. A B-tree on (a, b, c) works best when the query constrains a, or a and b. It can serve (a) alone, so a separate index on a is redundant.; Composite, partial and covering indexes
Lexeme: A word after normalization: lowercased, stemmed, stop words removed. With the english configuration, "restored" and "restore" both give restor.; Full-text search
Line pointer: A 4-byte slot at the top of the page that holds the offset and length of one tuple. A row's address names this slot, and the byte position can change.; Pages and rows on disk
Lock queue: Requests that cannot be granted wait in line. A new request waits if it conflicts with a lock already held, or with one still waiting.; Migrations that lock the table
lock_timeout: Aborts a statement that waits longer than this for any lock. Off (zero) by default, so a wait can last forever.; Locks and deadlocks
lock_timeout: A setting that aborts a statement once it has waited longer than the limit for a lock. The default is 0, and 0 means the statement waits with no limit.; Migrations that lock the table
log_min_duration_statement: Logs every statement that ran at least this long, with its duration and text. -1, the default, logs nothing.; Finding slow queries
Logical dump: What pg_dump makes: SQL or an archive that rebuilds the tables. A consistent snapshot of one moment, portable across versions, but you cannot replay WAL on top of it.; Backups you can restore
Logical order: The order in which the clauses take effect, which the SELECT docs list step by step. It decides what each clause can see.; Asking with SELECT
loops: How many times the node ran. Actual time and rows are averages per loop: multiply by loops to get the total.; Reading EXPLAIN
Lost update: Two transactions read a value, compute a new one in the app, and write it back. The second write silently erases the first.; Isolation levels
Lost update: Two writers read the same row, both change it, and the later write overwrites the earlier one as if it never happened.; Optimistic locking
LSN: Log sequence number: a position in the WAL. Each data page remembers the LSN of the last WAL record that changed it.; The write-ahead log
MCV list: Most common values: up to 100 values of the column (the default statistics target) with the share of rows each one has. For an = on one of them, the planner knows its share directly.; Planner statistics: how PostgreSQL estimates rows
MVCC: Multiversion concurrency control. The database keeps several versions of a row, and each transaction reads the one that matches its snapshot.; MVCC: row versions and snapshots
N+1 queries: One query loads N parent rows, then the code runs one more query per row to load a related object. N+1 queries where 1 or 2 would do.; N+1 and the ORM
Natural key: A key taken from the real world: an email, an ISBN, "issue 42 of this repo". People can change it, like when a customer changes their email.; Tables, rows and keys
Normalization: Constants in a query are replaced by $1, $2, so id = 42 and id = 43 share one row, keyed by a queryid hash.; Finding slow queries
NULL: A marker for a missing or unknown value. It is not 0, the empty string or false.; NULL in SQL: comparisons, NOT IN and unique keys
NULLS NOT DISTINCT: A clause on a unique index or constraint (PostgreSQL 15 and later) that makes two NULLs count as equal, so only one is allowed.; NULL in SQL: comparisons, NOT IN and unique keys
Operator class: The set of operators an index can serve for a column type. An index helps a WHERE clause only if the clause uses one of those operators on the indexed column.; Index types: GIN, GiST, BRIN and Hash
ORDER BY: The clause that sorts the result. Without it, PostgreSQL returns rows "in whatever order the system finds fastest to produce".; Asking with SELECT
Outbox job: A row in a jobs table, written in the same transaction as the order. A worker picks it up later, so an email never goes out for an order that rolled back (day 24).; Capstone: the data of a shop
Page: A fixed block of 8 KB (by default) in a table or index file. PostgreSQL reads, caches and writes whole pages.; Pages and rows on disk
Page split: When an insert does not fit on a leaf, part of its entries move to a new page and the parent gets a new separator. Splits can climb up to the root.; B-tree indexes
Partial index: An index with a WHERE clause: it holds only the rows that match. Smaller, cheaper to update, and usable only by queries whose condition implies that clause.; Composite, partial and covering indexes
Partition key: The column (or columns) that decides which partition a row goes to. Often a timestamp for range, a region or tenant for list.; Partitioning big tables
Partition pruning: The planner proves from the WHERE clause that some partitions cannot hold matching rows, and leaves them out of the plan.; Partitioning big tables
pg_stat_statements: A module shipped with PostgreSQL that counts calls, time, rows and blocks for every statement the server runs, and shows the totals as a view.; Finding slow queries
Plan node: One step of the plan: a scan, a join, a sort, an aggregate. Nodes form a tree. The innermost ones run first and pass rows up to their parent.; Reading EXPLAIN
Point-in-time recovery: PITR for short: you restore a base backup, replay WAL, and stop at a chosen moment, the recovery target, such as recovery_target_time.; Backups you can restore
Preload (separate query): A second query, WHERE id IN (...) with the ids from the first one, then matching in memory. Rails preload, Django prefetch_related.; N+1 and the ORM
Primary key: The column or columns that identify a row. PostgreSQL requires them to be unique and not null, and builds a unique B-tree index on them.; Tables, rows and keys
Quorum: A majority of voting members: (n/2)+1, rounded down in the division. 2 of 3, 3 of 4, 3 of 5.; How replicas agree
RDB snapshot: A point-in-time copy of the whole dataset in one compact file, written by a forked child at configured save points.; Key-value stores
Read your own writes: A promise to one user: after they write, their next read sees it. Replicas alone do not give it; the app has to route for it.; Replication and lag
Replication lag: How far a replica is behind, in bytes of WAL or in seconds. Shown per replica in pg_stat_replication on the primary.; Replication and lag
Rescue: Putting a job back to available when its worker died while it was executing. Found by age: it has been running far too long.; A job queue in the database
Retention: The rule that drops or detaches partitions once all their data is older than a limit. pg_partman runs it for you.; Partitioning big tables
Role: A database identity. It can log in (a user), or hold privileges for others (a group). Roles can be members of other roles.; Roles, grants and row-level security
Rollback journal: A side file where SQLite copies the original pages before it changes the database. Deleting the side file commits the change.; Why use a database instead of a file
Root, internal, leaf: The root is the top page. Internal pages hold separator keys and links to the level below. Leaf pages hold every key with the ctid of its row.; B-tree indexes
Row group: A horizontal slice of a table, stored column by column. In DuckDB, 122,880 rows by default. Compression and parallelism work per row group.; Columns for analytics
Row lock: Taken by UPDATE, DELETE and SELECT FOR UPDATE on each row they touch. It blocks other writers and lockers of that row, never plain readers. Released when the transaction ends.; Locks and deadlocks
Row store: All the columns of a row sit next to each other on disk. Fast to read or change one whole row. PostgreSQL's heap stores rows this way.; Columns for analytics
Row-level security (RLS): Per-table policies that filter which rows a query may read or write. Enabled with ALTER TABLE ... ENABLE ROW LEVEL SECURITY.; Roles, grants and row-level security
Savepoint: A marker inside a transaction. ROLLBACK TO undoes what came after it and keeps what came before, without ending the transaction.; Transactions and savepoints
SELECT list: The expressions after SELECT: the columns and computed values each output row will have. AS gives one a name, an alias.; Asking with SELECT
Selectivity: The fraction of rows a condition keeps, from 0 to 1. Estimated rows = rows in the table × selectivity.; Planner statistics: how PostgreSQL estimates rows
Serialization failure: The error PostgreSQL raises to stop an anomaly, always with SQLSTATE 40001. The fix is to roll back and run the whole transaction again.; Isolation levels
Session pooling: PgBouncer gives a client one server connection until the client disconnects. Everything works, but idle clients still hold a connection.; Connections and pooling
Session state: Anything that lives on a connection between transactions: SET values, LISTEN, session advisory locks, SQL PREPARE, temp tables.; Connections and pooling
Shard: One piece of a sharded table, a normal table on one server. Citus creates 32 per table by default and spreads them over the worker nodes.; Sharding
Shard key: The column that decides a row's shard. Citus calls it the distribution column. Usually a tenant or customer id.; Sharding
SKIP LOCKED: A locking option for SELECT: rows that another transaction has locked are left out of the result instead of waited for.; A job queue in the database
Snapshot: Three things: xmin (every older transaction is finished), xmax (every transaction from here on is invisible), and the list of transactions still running in between.; MVCC: row versions and snapshots
Source of truth: The data a copy is computed from. If the two disagree, the source wins and the copy is rebuilt.; Denormalization: copying data on purpose
Split brain: Two servers both accept writes as primary. Their histories fork, and the commits on the losing side are lost or merged by hand.; Failover without split brain
Stale object: A copy of a row in memory whose version no longer matches the database. Saving it must fail rather than overwrite.; Optimistic locking
Surrogate key: A key with no meaning, made by the database: 1, 2, 3 from a sequence, or a random UUID. It never needs to change.; Tables, rows and keys
Synchronous replication: A commit also waits for named standbys. synchronous_commit chooses how far: written (remote_write), flushed (on), or replayed and visible (remote_apply).; Replication and lag
Term: A number that grows with each election. Each term has at most one leader. A node that sees a higher term steps down to follower.; How replicas agree
Three-valued logic: SQL conditions return true, false or NULL (unknown). NULL AND false is false, NULL OR true is true, almost everything else with a NULL is NULL.; NULL in SQL: comparisons, NOT IN and unique keys
TOAST: The storage for large values. A row wider than about 2 kB has its big fields compressed and, if needed, moved to a side table in chunks.; Pages and rows on disk
total_exec_time: The sum of execution time over all calls. Sort by it to see where the server spends its time; mean_exec_time alone hides fast queries that run often.; Finding slow queries
Transaction: A group of statements the database treats as one unit. It ends with COMMIT (keep everything) or ROLLBACK (keep nothing).; Transactions and savepoints
Transaction ID (XID): A 32-bit number every writing transaction gets. Row versions record the XID that created them, and visibility compares XIDs.; Dead rows and VACUUM
Transaction pooling: PgBouncer gives a client a server connection only for the length of one transaction, then hands it to someone else.; Connections and pooling
Transactional enqueue: Inserting the job in the same transaction as the data it is about. If the transaction rolls back, the job never existed.; A job queue in the database
tsquery: Lexemes joined by &, |, ! and <->. @@ tests whether a tsvector matches it. :* asks for a prefix match.; Full-text search
tsvector: A sorted list of lexemes, each with its positions in the document and an optional weight. What you store and index.; Full-text search
TTL: A timeout on a key, set with EXPIRE or SET ... EX. Redis calls a key with one "volatile". When the time is up, the key is deleted.; Key-value stores
Tuple: One stored version of a row, with its header. An UPDATE writes a new tuple, so one row can have several.; Pages and rows on disk
UNIQUE: No two rows share the same value in these columns. PostgreSQL enforces it with a unique B-tree index.; Constraints: UNIQUE, CHECK and exclusion
Upsert: Insert a row, or update it if one with the same key exists. In PostgreSQL: INSERT ... ON CONFLICT ... DO UPDATE.; Upserts and safe retries
USING / WITH CHECK: USING filters the existing rows a command sees. WITH CHECK tests the new rows it tries to write. A failed check aborts the command.; Roles, grants and row-level security
VACUUM: Removes dead row versions that no transaction can see and marks the space free for new rows in the same table. It does not lock out reads or writes.; Dead rows and VACUUM
Version column: An integer stored on the row (Rails calls it lock_version). Every successful update adds one to it.; Optimistic locking
WAL: Write-ahead log: an append-only record of every change to tables and indexes, in the files under pg_wal. A change is logged before the data page it describes may reach the disk.; The write-ahead log
WAL archive: Every finished WAL segment, copied somewhere safe by archive_command. Together with a base backup, it holds every change since that backup.; Backups you can restore
Watchdog: A kernel or hardware timer that resets the machine unless it gets a keepalive in time. Patroni relies on it when the node cannot demote itself.; Failover without split brain
Weight: A label A, B, C or D on each position, set with setweight. Used to rank a title hit above a body hit.; Full-text search
Window function: An aggregate-like calculation with OVER (...) that does not fold rows: each row keeps its identity and gets a value computed from related rows.; Counting with GROUP BY
Write skew: Two transactions read the same rows, check a rule, and each writes a different row. Each write is fine alone. Together they break the rule.; Isolation levels
xmax: The ID of the transaction that deleted or replaced this version, or zero. A nonzero xmax on a visible row usually means that transaction has not committed, or rolled back.; MVCC: row versions and snapshots
xmin: A hidden column on every row version: the ID of the transaction that inserted it. You can select it: SELECT xmin, xmax, * FROM orders.; MVCC: row versions and snapshots
Zonemap: The min and max of a column in a block. A filter that falls outside the range skips the block without reading it. Also called a min-max index.; Columns for analytics